Store a DataTable from ASP.NET into Sql Server

Question

Hello Friends I've a GridView in which i'm inserting lots of records it may be 100 or 500. And holding those values in a DataTable and there is a Save button after clicking on that button those records of DataTable being saved in Sql Server 2005. Currently i'm taking that DataTable values in a variable and serperating them with Comma() . And at sql side i'm serperating them with comma and saving the records in Table in Sql Server. And that's working fine. But is it any other way to save or send the whole datatable in a single turn not concating in a string variable and save them at sql server.

Amit Choudhary · Answer

hi friend you can replace the command object that i have created in code by changing the command type property to stored procedure pass the paremeters if required and you are done.

Manoj Bisht · Answer

Hello Amit First of all thanks to reply. But where is the stored proc which which'll receive datatable from asp.net and update the table in sql server.

Amit Choudhary · Answer

hi friend... Following code will show you how to save complete table in database in one statement: public DataTable CreateCmdsAndUpdate(DataSet myDataSetstring myConnectionstring mySelectQuerystring myTableName) { OleDbConnection myConn = new OleDbConnection(myConnection); OleDbDataAdapter myDataAdapter = new OleDbDataAdapter(); myDataAdapter.SelectCommand = new OleDbCommand(mySelectQuery myConn); OleDbCommandBuilder custCB = new OleDbCommandBuilder(myDataAdapter); myConn.Open(); DataTable custDT = new DataTable(); myDataAdapter.Fill(custDT); //code to modify data in DataTable here myDataAdapter.Update(custDT); myConn.Close(); return custDT; } Don't forget to mark the answer.