DML Operation on DATA SET

Question

Hi Dudes Is it possible  i have two tables in data set and i want to perform DML operation on that tables like delete  update and insert  if it is possible how can any one help me...?

Vulpes · Answer

Try the following for System.Data.SqlClient (SQL Server only): String cs = connectionString; using (SqlConnection con = new SqlConnection(cs)) { SqlDataAdapter da = new SqlDataAdapter(SELECT * FROM Shippers con); DataSet ds = new DataSet(ShipperDataSet); da.Fill(ds); // Create Update and Insert commands SqlCommandBuilder cmdBuilder = new SqlCommandBuilder(da); da.UpdateCommand = cmdBuilder.GetUpdateCommand(); da.InsertCommand = cmdBuilder.GetInsertCommand(); // Insert row DataRow newRow = ds.Tables[0].NewRow(); newRow[ShipperID] = 100; newRow[CompanyName] = NewCompany; newRow[Phone] = 123456789; ds.Tables[0].Rows.Add(newRow); // Update row ds.Tables[0].Rows[0][1] = Changed CompanyName; // Update database using Dataset via DataAdapter da.Update(ds); } When you call da.Update(ds)then it should update the database with any insertions updates or deletions to rows in the DataTables within the DataSet that have not yet been committed.

Jiteendra Sampathirao · Answer

Yes it is possible..... String cs = String.Format(@Provider=SQLNCLI;Data Source= ;Initial Catalog=Northwind;Integrated Security=SSPI;); using (OleDbConnection con = new OleDbConnection(cs)) { OleDbDataAdapter da = new OleDbDataAdapter(SELECT * FROM Shippers con); DataSet ds = new DataSet(ShipperDataSet); da.Fill(ds); // Create Update and Insert commands OleDbCommandBuilder cmdBuilder = new OleDbCommandBuilder(da); da.UpdateCommand = cmdBuilder.GetUpdateCommand(); da.InsertCommand = cmdBuilder.GetInsertCommand(); // Insert row DataRow newRow = ds.Tables[0].NewRow(); newRow[ShipperID] = 100; newRow[CompanyName] = NewCompany; newRow[Phone] = 123456789; ds.Tables[0].Rows.Add(newRow); // Update row ds.Tables[0].Rows[0][1] = Changed CompanyName; // Update database using Dataset via DataAdapter da.Update(ds); } Go through this LiNK hope it help you.....

Vulpes · Answer

Smart Lucky Would you mind moving the 'Accepted Answer' back to Jitendra's post as all I've done is change 'OleDb' to 'Sql' in his code. Thanks :)

Smart Lucky · Answer

hi friends can you send this program in sqlclient...? and if i updates and delete in data set can these chages perform on database in actual data....?