how to update the database using SqlcommandBuilder

[mohanaranga]

Hi All,

I want to update the values into database.And i get these values
from a xml file.I have done the coding for that but its not getting
updated.Plz tell me whats wrong in my program.if possible provide me
some code for this......


DataSet XMLDataSet = new DataSet();
DataSet SqlDataSet = new DataSet();
DataTable XMLTable= new DataTable();
DataTable SQLTable= new DataTable();

SqlConnection Conn = new SqlConnection("Data source = RPriya; Initial
Catalog = Master; UID = sa; PWD = ");

SqlDataAdapter DataAdapt = new SqlDataAdapter();

DataAdapt.SelectCommand = new SqlCommand("Select * from persondetails",
Conn);

SqlCommandBuilder CommandBuild = new SqlCommandBuilder(DataAdapt);

Conn.Open();
XMLDataSet.ReadXml("d:\\myXmlDocument1.xml");
XMLTable= XMLDataSet.Tables[0];
XMLTable.TableName="persondetails";

DataAdapt.Fill(SqlDataSet, "persondetails");

SQLTable=SqlDataSet.Tables["persondetails"];

SQLTable=XMLTable.Copy();

DataAdapt.Update(SqlDataSet,"persondetails");

Conn.Close();

with regards,
Mohan

 

[Cor Ligthert [MVP]]

Moharanga,

Updating using the dataadapter means forever aspects as (just some).

See if a row in the dataset is in a changed or new state
Get the same row if it exist from the database
Check if it exist or not is updated meanwhile
Handle it accoording to that.

This part you have to overcome using the commandbuilder.

AFAIK goes this in only well with a complete empty database.

This means that probably reading first the accoording dataset and update
that from your xml file while give you a much better result.

Than you can use the commandbuilder again to set that database dataset back
again.

I hope this helps,

Cor

 

[W.G. Ryan eMVP]

Like Cor mentions, the key to this is rowstate. Right before you call
..Update, throw in a Debug.Assert(SqlDataSet.HasChanges,"There's nothing to
update");

If you have changed data though, then it should work.

Also, you have very limited ability to deal with concurrency using
commandbuilders so that may be an issue. Check out bill Vaughn's article at
www.betav.com "Weaning Developers from the CommandBuilder" for a read on
why.