reate table TestTable (id int primary key, fname nvarchar(50), lname nvarchar(50))
insert into TestTable values (1, 'Mike', 'Spike')
insert into TestTable values (2, 'John', 'Johnson')
insert into TestTable values (3, 'Pete', 'Peterson')
--drop table TestTable
Then create a new C# Console application and run the following code:
static void Main(string[] args)
{
string cs = @"Data Source=<your server>;Initial Catalog=<your database>;Integrated Security=SSPI";
string sql = "SELECT id, fname, lname FROM TestTable";
using (SqlConnection con = new SqlConnection(cs))
{
SqlDataAdapter da = new SqlDataAdapter(sql, con);
DataTable dt = new DataTable("Persons");
da.Fill(dt);
Console.WriteLine("Rows are untouched...");
ViewDataTableRowStatus(dt);
// Modify a row.
Console.WriteLine("Modifying the first row...");
dt.Rows[0][1] = "Jake";
ViewDataTableRowStatus(dt);
// Add a row.
Console.WriteLine("Adding a row");
dt.Rows.Add(new object[]{100, "Paul", "Paulson..."});
ViewDataTableRowStatus(dt);
// Now, if calling AcceptChanges() all rows will be set to Unchanged.
Console.WriteLine("Calling AcceptChanges()...");
dt.AcceptChanges();
ViewDataTableRowStatus(dt);
// This means that when we call update on the DataAdapter, it will return 0
// since no rows has actuall been sent to the database. (first we need to build the INSERT and UPDATE commands)
SqlCommandBuilder builder = new SqlCommandBuilder(da);
da.InsertCommand = builder.GetInsertCommand();
da.UpdateCommand = builder.GetUpdateCommand();
int rows = da.Update(dt);
Console.WriteLine("Updated rows: {0}", rows);
}
}
private static void ViewDataTableRowStatus(DataTable dt)
{
foreach (DataRow dr in dt.Rows)
{
Console.WriteLine("Id:{0}\tFirstName: {1}\tRowState: {2}", dr[0].ToString(), dr[1].ToString(), dr.RowState.ToString());
}
Console.WriteLine();
}