ADO.NET :: Moving Data From Datatable To XML Using Linq
Nov 19, 2010
I have a data table I need to convert to XML. I know I can use datable.WriteXML but I need to rename the elements as the column names coming from the Oracle db are ugly. I found this snippet somewhere:
[code]....
It looks like what I want but when I paste it in my code it shows an error on select Cannont convert lambda expression to type 'string' because it is not a delegate type.
I am new to linq and for that matter to C# (done a lot more vb). I am going to read the books but I need to get this done pretty quick for a project.
I wish to retrieve data from a DataTable using Linq, and I do this by first creating a DataTable with the data and then using Linq to query:
[Code]....
My problem is, that the column "enddatee" has 'holes', meaning that some rows does not have a value. This means that the convertion to DateTime will fail for some rows. How can I get around that?
I would like to implement LINQ IN ASP.NET for filter required data from datatable. I have doubt that what is right place. I have following options
At presentation layer *.aspx.cs
At our Business layer.
At our Data base layer, where query for database happen which is return as a datatable to business layer. Business layer then return the result to presentation layer.right place for LINQ because now i have to further filter data from datatable as per user selecter filters.
i included the table and its data as it would be impossible without that to understand. this is the meta scenario as you may notice that this was the query i wrote before when i was not checking the publish date
var postsidebar = from post in postrepository.GetAllPosts() join pstmt in postrepository.GetAllPostMetas() on post.int_PostId equals pstmt.int_PostId where (post.int_PostTypeId == 4 && post.int_PostStatusId == 2 && post.int_OrganizationId == layoutrep.GetSidebarDetailById(SidebarDetailsId).int_OrganizationId) && (pstmt.vcr_MetaKey.Contains(filter) && pstmt.vcr_MetaValue.Contains("true")) select post
how would i goint incoperate check for publish date in the query above( i want to bring all the records whose publish date is greater then todays date)
i could bind the data form tables to dropdownlist in asp .net using linq.
ex: var res=from n in dc. contacts where n.location==Hyd select new{n.Id,n.Name}; var res1=from n in dc. Morecontacts where n.location==Hyd select new{n.Id,n.Name}; dropdown1.Datasouce=res; dropdown1.DataTextField="Name"; dropdown1.DataValueField="Id";
now in above, if i want only those hotelid where amenityid is 7585 and amenityid is 65474 and not any else hotelid , then how to use select query in datatable in asp.net
Many times I need to manipulate with DataTable after I get data from database using adapter.Fill(table).
Usually I calculate data in DataTable by using loops. But I know that there is simpler way using LINQ. I know some basics about that just for strongly typed objects.I can't find how to do this with DataTable that is not strongly typed. How could I get all DataRow or Filtered DataTable (not just value for the field) from table for that conditions:
- where the specified field of the table contans MAX/MIN/AVG value, - where the specified field of the table equals to some value.
I got the following query to group the table. I am wondering how to add a subtotal record with anonymous type.
string prevOriginator = string.Empty; var oOriginator = (from I in dtIssue.AsEnumerable() group I by new {RaisedBy = I.Field<string>("RaisedBy"), ItemType = I.Field<string>("ItemType")} into grp
I have a datatable which has been dynamically generated from FoxPro tables using a UNION Select statement. e.g.
SELECT * FROM x UNION SELECT * FROM y UNION SELECT * FROM Z ORDER By v_alue1
This produces a datatable with about 100 rows, each containing many fields, one of which is c_olor. From this datatable, I would like to select the distinct colors and then output in a dropdown.
I have a public class Color which just has one property which I can then use as the DataTextField and DataValueField for the dropdownlist
[code]...
However this never results in the distinct colors.
I have searched and searched for what I am looking for, and this seems to be one of the methods to produce a distinct set of results, but this and the others do not work.
My reasoning behind getting the colors this way, is that I need to get various other distinct values from the same UNION SELECT datasource, so would just do one DB call, cache the results, and then just used this cached datasource to retrieve all my distinct values.
I'm having a spot of trouble figure out how to write my linq query so that is allows a decimal field in my datatable to be null
Right now I have this:
[Code]....
But it's not working. The error I am getting is: Cannot cast DBNull.Value to type 'System.Decimal'. Please use a nullable type.so how do I get it to allow program_ar_id to be null?
I am converting my datatable to LISt using LINQ how do I handle nulls coming from database
List<Port> portDetails = new List<Port>(); DataTable dt = ds.Tables[0]; portDetails = (from q in dt.AsEnumerable() select new Port { PortCode = q.Field<string>("Code"), ExtCode = q.Field<string>("Nb"), Name = q.Field<string>("Name")) }).ToList();
In the above query if Code is null I do not want property portcode to be set to the value it should only set if it is not null or not blank PortCode = q.Field<string>("Code"),
What should be syntax I was trying somethign like this which doesnt work Portcode = q.Field<bool>("Code") == null ? null : q.Field<bool>("Code")
Trying to pass in my DataTable to a stored proc using LINQ. Below is my code.
var sqlCommand = new System.Data.SqlClient.SqlCommand { CommandType = System.Data.CommandType.StoredProcedure, CommandText = "UserIdList" }; var dataTable = new System.Data.DataTable("IdList"); dataTable.Columns.Add("AttributeIds", typeof(Int32)); dataTable.Rows.Add(26); dataTable.Rows.Add(40); dataTable.Rows.Add(41); dataTable.Rows.Add(45); dataTable.Rows.Add(78); dataTable.Rows.Add(33); dataTable.Rows.Add(36); //The parameter for the SP must be of SqlDbType.Structured var parameter = new System.Data.SqlClient.SqlParameter { ParameterName = "@AttributeIds", SqlDbType = System.Data.SqlDbType.Structured, TypeName = "ecs.IDList", Value = dataTable, }; sqlCommand.Parameters.Add(parameter); var user = myDC.DC.ExecuteQuery("exec ecs.udpUserAttributeDetails {0}, {1}", sqlCommand, userId).SingleOrDefault();
I am trying to run a linq query but I need the result as a datatable as I am using that to store records from different queries in the same viewstate object.The 2 versions below compile, but return an empty set. The exact error is "Value cannot be null.Parameter name: source". (and yes I have checked there is data):
MyDatabaseDataContext db = new MyDatabaseDataContext(conn); IEnumerable<DataRow> queryProjects = (from DataRow p in db.STREAM_PROJECTs.AsEnumerable()
I have store procedure which return result set. I have used LINQ datacontext object to get the result set from this store procedure, which returns the result set in IsingleResult format.I bind this IsingleResult to my gridview.
I want to convert this IsingleResult into DataTable format so I can sort the gridview.
