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
[code]...
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.
Is there option to convert from Linq.IQueryable to DataTable with some sort of CopyToDataTable() function. I am trying this.
View 2 RepliesI would like to get the distinct value form DataTabel using LINQ.
For example my sample table,
[code]....
1. How to get the distinct Country from Country you like column and the return records as Array or DataTable? (I want only India,USA,UK,Germany )
Is there any way to filter the record values using LINQ?
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?
how to convert a linq query to a datatable
this is my query
[Code]....
how can i delete a row in datatable using linq to dataset?
I have a dataset in my project, need to delete some rows filtered by ID
how can i do?
The current solution i implemented is awful!
I use a for... loop for inserting records from an ADO.NET data-table into an SQL table.
I would like to insert at once the data-table into the SQL table, without iterating...
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.
using linqs how to fill datatable using linq to sql in asp.net
i have one storedprocedure to select data from table
and one s.p for insert the data and one for update the data
how i use these s.p to insert and update the data using linq. to sql
I am gettin error in this code
[Code]....
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 call a stored procedure using linq. I want to convert the ISingleResult output to datatable. what can I do?
DataClassesDataContext Dac = new DataClassesDataContext();
var ExitResult = Dac.Retrieverelations(WorkshopCode);
now I'm using below code for converting but I need a better solution.
D
dataTable dt = new DataTable();
dt.Columns.Add("Id", typeof(Int64));[code]....
i just use this code to insert value in datatable and pass this table in method
look code---
public class RoomBO
{
DataTable dt;
RoomDal objRoom = new RoomDal();
public int insert(int room,string bedType, int bed, int charge)
{
//adds the details of the new room
dt = new DataTable();
dt.Columns.Add(new DataColumn("Column1"));
dt.Columns.Add(new DataColumn("Column2"));
dt.Columns.Add(new DataColumn("Column3"));
dt.Columns.Add(new DataColumn("Column4"));
DataRow dr;
dr = dt.NewRow();
dr["Column1"] = room;
dr["Column2"] = bedType;
dr["Column3"] = bed;
dr["Column4"] = charge;
objRoom.Save(dt);
return 1;
}
*on data access layer*
public int Save(DataTable dataTable) //adds the details of the new room
{
ada = new SqlDataAdapter("room_insert", con);
//DataRow r= dataTable.NewRow();
ada.SelectCommand.CommandType = CommandType.StoredProcedure;
ada.SelectCommand.Parameters.AddWithValue("@room_no",
Convert.ToInt32(dataTable.Rows[0][0]));
ada.SelectCommand.Parameters.AddWithValue("@room_type",
Convert.ToString(dataTable.Rows[0][1].ToString()));
ada.SelectCommand.Parameters.AddWithValue("@no_bed",
Convert.ToInt32(dataTable.Rows[0][2]));
ada.SelectCommand.Parameters.AddWithValue("@charge",
Convert.ToInt32(dataTable.Rows[0][3]));
ds = new DataSet();
ada.Fill(ds);
return -1;
}
i am not getting any error but value is not insert to my database
how to extract DataTable or DataSet from Linq Query or List. e.g I have linq query like this
MyDBpDataContext dcSp = new MyDBpDataContext();
dcSp.myProgrammSP().ToList();
I wrote an Sp which sends a Table as result and I have code which is already using DataTable So I want to convert this result in DataTable.
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()
[code]...
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.
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 have made a custom DataTable
Dim dt As New DataTable("RizPardakht")
Dim RowID As New DataColumn("tbz_GatiPardakhtID")
Dim FishNo As New DataColumn("tbz_FishNo")
[code]...
What I am trying to achieve here is, I am supplying xml as string (varchar) to my stored procedure, In stored procedure I want to parse this xml and fetch each Promotion record and insert into my table. Also once each record is inserted, I need to fetch the Identity of that record alongwith the AreaID attribute from the Parent node "Promotion" of each Promotion record and insert into second association table. how would I go about doing it, my generated xml is given as under.
[Code]....
i have to insert values in two tables on save button click.ne sipmle insert and return a order_id.that id is used for inserting order details information.i am using a datatable to store this information,then insert to table.i want use Transaction for both insert on save button click.
View 1 RepliesI have a text file coming from another system that is in fixed format with header and details information regarding a request of material, looks like:
HDR 1000 HeaderDescription
DTL ProductId Productdescription qty ...........
DTL ProductId Productdescription qty ...........
DTL ProductId Productdescription qty ...........
I can read the file using streamreader on a "continous way" inside a do while loop, and parse it to obtain th different fileds required.i want to read the file, identify header line and fill a datatable, then identify details lines and fill another datatable to be able to insert both tables on the same transaction scope, if something fails rollback the insert.Im using a access 2003 database as backend and custom DAL and Business objects.
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";
[CODE]...
