Click here to Skip to main content
15,066,326 members
Please Sign up or sign in to vote.
0.00/5 (No votes)
ivé been trying to delete a row from a table using dataset.tables.rows.remove(); specifying the row to be deleted in the remove function.what i did is exactly like this.

C#
SqlConnection con = new SqlConnection();
con.ConnectionString = "initial catalog=employee;data source=192.168.1.2;user id=sa;password=*********";
con.Open();
SqlDataAdapter da = new SqlDataAdapter("select *from dbo.employeeage", con);
SqlCommandBuilder cmdbuilder = new SqlCommandBuilder(da);
           
DataSet ds = new DataSet("employee");
da.Fill(ds, "employee");
DataRow row = ds.Tables["employee"].Rows[2];
            
ds.Tables["employee"].Rows.Remove(row);
da.Update(ds, "employee");
con.Close();


nothing gets changed in the database.i have like 30 records in the employeeage table.
i'm an absolute newbie to ado.net plzz correct me if i am wrong somewhere

[Edit]Added code block. [/Edit]
Posted
Updated 17-Jan-13 0:23am
v3

Instead of line
C#
ds.Tables["employee"].Rows.Remove(row);

just use
C#
row.Delete();

The logic behind is that DataTable is just an off-line in-memory object representing data from your real DB table. You filled real DB employee table into your off-line DataSet ds object by calling
C#
da.Fill(ds, "employee");

Then you reference the filled table, stored in ds DataSet, named "employee", by the code fragment
C#
ds.Tables["employee"]

This returns the object of type DataTable. Consequently, DataTable.Rows Property is just an off-line in-memory collection of DataRow objects filled from your real DB. You can manipulate each DataRow object by changing its value. Changes are tracked by DataRow.RowState Property and various versions of field values are accessible through DataRow.Item Property (String, DataRowVersion). Nothing reflects in real DB so far. Until you call
C#
da.Update(ds, "employee");

Performing DataAdapter.Update Method, data adapter walks through your off-line DataTable.Rows collection of "employee" table. For each row (of DataRow type), row.RowState is one of DataRowState Enum. If adapter finds row.RowState = DataRowState.Added then it tries to call Insert command on real DB, filling in values from this off-line row("current" version of each corresponding field). If row.RowState = DataRowState.Modified, data adapter tries to call Update command on real DB, matching the row.PrimaryKey with the real primary key of the real DB table. If row.RowState = DataRowState.Deleted, the data adapter tries to call Delete command on real DB, matching the row.PrimaryKey with the real primary key of the real DB table. If row.RowState = DataRowState.Unchanged or the row was removed from the Rows collection, then data adapter does nothing in real DB for matching (or missing) row..

So this is it. As you completely removed your off-line representation of your real DB row by calling
C#
ds.Tables["employee"].Rows.Remove(row);

then the data adapter da has no reference to that real DB row anymore.
On the other side, if you find your off-line DataRow row object and call
C#
row.Delete();

then the row.RowState is set to DataRowState.Deleted and the consequent
C#
da.Update(ds, "employee");

finds the row marked Deleted and calls the Delete command on real DB.

Hope this helps,
madvad
   
v2
Comments
Saleel Ahsan 11-Dec-20 5:06am
   
Thanks a ton for the detailed explanation.....!!!
Saleel Ahsan 11-Dec-20 5:07am
   
Thanks a ton for the detailed explanation...!!
You need to set the DeleteCommand property for the adapter.
   
Comments
rtz87 17-Jan-13 10:05am
   
thanx for the link but i'm still not clear on how to do this can u plz give me an example.
lw@zi 18-Jan-13 1:37am
   
Check out MSDN.
As Danish suggest you can use DeleteCommand of SQLDataAdapter
review this link it might helps you...

DeleteCommand[^]
   
Comments
rtz87 17-Jan-13 10:07am
   
thanx for the the link but i'm still not clear on how to do this can u plz give me an example.
Tejas Vaishnav 18-Jan-13 2:10am
   
There was and example below that article. you can go throw that.

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)




CodeProject, 20 Bay Street, 11th Floor Toronto, Ontario, Canada M5J 2N8 +1 (416) 849-8900