Fighting with SqlDataAdapter.

Yesteray, I was relpying one forum on codeguru. That guys asked me the code. How to add a row to a datatable and how to reflect the changes back to datasource. I was trying a lot to get it done. Searched many sites but I was not able to do it. http://support.microsoft.com/default.aspx?scid=kb;EN-US;Q310347

One of my friend who is SQL MCP came for tea and he also got involved in the same. We tried with SQL profiler. Came to know that when I call dataAdapter.Update(ds); database was not getting hit. Again we searched a lot an then we followed these steps.

1. Remove all rows from table which is populated from dataset filled by adapter.
2. Add a row with values.
3. Call update.

This worked well but we were loosing all data. So AcceptChanges() method helped us. I was using this method after adding a row. This makes the impression that eveything is fine and no need to hit database. So what we did is create a new row, add values to respective columns, then call AccpetChanges() for dataset and datatable and then call AddRow() and then adapter.Update..

husssssh.. it worked at last.. Was happy to see that.

Here is the sample code.

string sqlQuery = "SELECT StatusId, ADUserId, Email, FirstName, LastName, JobTitle, DepartmentId, Phone, Fax, Mobile, UniqueKey, IsDeleted, CreatedByUserId, CreatedOn, ModifiedByUserId FROM [User]";

string connectionString = "Database=DbUsers;Server=localhost;User=user;Password=pwd;Enlist=false;";SqlCommand cmd;

SqlDataAdapter adpt;

DataSet ds = new DataSet();

using (SqlConnection conn = new SqlConnection(connectionString))

{

conn.Open();

cmd = new SqlCommand(sqlQuery, conn);

cmd.CommandType = CommandType.Text;

adpt = new SqlDataAdapter(cmd);

SqlCommandBuilder builder = new SqlCommandBuilder(adpt);

adpt.Fill(ds, "User");

if (ds.Tables[0].Rows.Count > 0)

{ DataRow row = ds.Tables["User"].NewRow();

row["StatusId"] = 1;

row["ADUserId"] = 5;

row["Email"] = jayantdotnet@gmail.com;

row["FirstName"] = "Jayant";

row["LastName"] = "Kulkarni";

row["JobTitle"] = "Chief Architect";

row["DepartmentId"] = 1;

row["Phone"] = "12334";

row["Fax"] = "122333";

row["Mobile"] = "22314234";

row["UniqueKey"] = 120;

row["IsDeleted"] = 0;

row["CreatedByUserId"] = 1;

row["CreatedOn"] = DateTime.Now;

row["ModifiedByUserId"] = 1;

ds.Tables["User"].AcceptChanges();

ds.AcceptChanges();

ds.Tables["User"].Rows.Add(row);

adpt.Update(ds.Tables["User"]);

}

}