DataSet updates to dataabase

[Vijay Balki]

This might be a dumb question to ask. Can someone answer me with patience..
When I have a typed or un-typed dataset, the only way I can update is like
below?
Dim con As New SqlConnection(ConnectionString)

con.Open()

Dim daProductsDS As New SqlDataAdapter("Select * from ProductsDS", con)

Dim oCommandBuilder As New SqlCommandBuilder(daProductsDS)

daProductsDS.DeleteCommand = oCommandBuilder.GetDeleteCommand

daProductsDS.InsertCommand = oCommandBuilder.GetInsertCommand

daProductsDS.UpdateCommand = oCommandBuilder.GetUpdateCommand

'Apply the updates

daProductsDS.Update(dsNorthwind, "ProductsDS")
Why do I have to delete and insert? Is the old fashion way of just updating
dead in DOT.NET? Please help me understand this. Why was this done away with
in dataset ? The other way is to manually use SQL statements to do
inserts/updates. But again I lose the power of using datasets.

Vijay

[Dino Chiesa [Microsoft]]

No, you do not need to delete and insert in order to update.
You can scan around for examples of how to do updates simply.
here's an example of a DataSet doing a Select from one database, and an
Update to another, using a single DataAdapter.
System.Data.SqlClient.SqlConnection c1= new
System.Data.SqlClient.SqlConnection(cstring1);
System.Data.SqlClient.SqlConnection c2= new
System.Data.SqlClient.SqlConnection(cstring2);

string strSelect="SELECT ix, Created, [Last Updated], Headline, Dept FROM
blogposts WHERE blog_ix=@blog_ix AND CONVERT(Char(10),Created,102) =
@targdate ";
string strInsert="INSERT INTO postbackups (ix, Saved, Headline, Dept)
VALUES (@ix, @Saved, @Headline, @Dept)";
System.Data.SqlClient.SqlDataAdapter da = new
System.Data.SqlClient.SqlDataAdapter();
da.SelectCommand= new System.Data.SqlClient.SqlCommand(strSelect);
da.SelectCommand.Connection= c1;

da.UpdateCommand= new System.Data.SqlClient.SqlCommand(strInsert);
da.UpdateCommand.Connection= c2;

da.SelectCommand.Parameters.Add("@blog_ix",
System.Data.SqlDbType.Int).Value= blog_ix;
da.SelectCommand.Parameters.Add("@targdate",
System.Data.SqlDbType.NVarChar).Value= targdate;
da.UpdateCommand.Parameters.Add(new
System.Data.SqlClient.SqlParameter("@ix", System.Data.SqlDbType.Int, 4,
"ix"));
da.UpdateCommand.Parameters.Add(new
System.Data.SqlClient.SqlParameter("@Saved", System.Data.SqlDbType.DateTime,
8, "Created"));
da.UpdateCommand.Parameters.Add(new
System.Data.SqlClient.SqlParameter("@Headline",
System.Data.SqlDbType.NVarChar, 50, "Headline"));
da.UpdateCommand.Parameters.Add(new
System.Data.SqlClient.SqlParameter("@Dept", System.Data.SqlDbType.NVarChar,
50, "Dept"));

da.TableMappings.AddRange(new System.Data.Common.DataTableMapping[] {
new System.Data.Common.DataTableMapping("Table", "blogposts", new
System.Data.Common.DataColumnMapping[] {
new System.Data.Common.DataColumnMapping("ix", "ix"),
new System.Data.Common.DataColumnMapping("blog_ix", "blog_ix"),
new System.Data.Common.DataColumnMapping("Created", "Created"),
new System.Data.Common.DataColumnMapping("Last Updated", "Last
Updated"),
new System.Data.Common.DataColumnMapping("Headline", "Headline"),
new System.Data.Common.DataColumnMapping("Dept", "Dept")})});

System.Data.DataSet ds= new System.Data.DataSet();
da.Fill(ds, "blogposts");

// need to update the row so the DA does the insert
foreach (System.Data.DataRow r in ds.Tables[0].Rows) {
r["Created"]= System.DateTime.Now; // update the row!
System.Console.WriteLine("rowstate: " + r.RowState);
}
da.Update(ds);

"Vijay Balki" <vi****@softech-usa.com> wrote in message
news:%2****************@tk2msftngp13.phx.gbl...

This might be a dumb question to ask. Can someone answer me with patience.. When I have a typed or un-typed dataset, the only way I can update is like
below?
Dim con As New SqlConnection(ConnectionString)

con.Open()

Dim daProductsDS As New SqlDataAdapter("Select * from ProductsDS", con)

Dim oCommandBuilder As New SqlCommandBuilder(daProductsDS)

daProductsDS.DeleteCommand = oCommandBuilder.GetDeleteCommand

daProductsDS.InsertCommand = oCommandBuilder.GetInsertCommand

daProductsDS.UpdateCommand = oCommandBuilder.GetUpdateCommand

'Apply the updates

daProductsDS.Update(dsNorthwind, "ProductsDS")
Why do I have to delete and insert? Is the old fashion way of just updating dead in DOT.NET? Please help me understand this. Why was this done away with in dataset ? The other way is to manually use SQL statements to do
inserts/updates. But again I lose the power of using datasets.

Vijay

[Ron Allen]

Vijay,
That depends on what you mean by 'update'. The DataAdapter uses the
Insert command to insert net records and the Delete to delete records as you
would expect. If you are only modifying records and not deleting or
inserting new ones you don't need these commands defined and can skip
assigning them. Note that the CommandBuilder uses optimistic concurrency
and thus the update will fail if any records are changed by another process
before you write them back.
Further questions would be more appropriate in
microsoft.public.dotnet.framework.adonet.

Ron Allen

"Vijay Balki" <vi****@softech-usa.com> wrote in message
news:%2****************@tk2msftngp13.phx.gbl...

This might be a dumb question to ask. Can someone answer me with patience.. When I have a typed or un-typed dataset, the only way I can update is like
below?
Dim con As New SqlConnection(ConnectionString)

con.Open()

Dim daProductsDS As New SqlDataAdapter("Select * from ProductsDS", con)

Dim oCommandBuilder As New SqlCommandBuilder(daProductsDS)

daProductsDS.DeleteCommand = oCommandBuilder.GetDeleteCommand

daProductsDS.InsertCommand = oCommandBuilder.GetInsertCommand

daProductsDS.UpdateCommand = oCommandBuilder.GetUpdateCommand

'Apply the updates

daProductsDS.Update(dsNorthwind, "ProductsDS")
Why do I have to delete and insert? Is the old fashion way of just updating dead in DOT.NET? Please help me understand this. Why was this done away with in dataset ? The other way is to manually use SQL statements to do
inserts/updates. But again I lose the power of using datasets.

Vijay

[Vijay Balki]

Thanks people for the replies. I got the answer from Dino's comments.
Appreciate it

Vijay

"Ron Allen" <ra****@src-us.com> wrote in message
news:uW**************@TK2MSFTNGP09.phx.gbl...

Vijay,
That depends on what you mean by 'update'. The DataAdapter uses the
Insert command to insert net records and the Delete to delete records as you would expect. If you are only modifying records and not deleting or
inserting new ones you don't need these commands defined and can skip
assigning them. Note that the CommandBuilder uses optimistic concurrency
and thus the update will fail if any records are changed by another process before you write them back.
Further questions would be more appropriate in
microsoft.public.dotnet.framework.adonet.

Ron Allen

"Vijay Balki" <vi****@softech-usa.com> wrote in message
news:%2****************@tk2msftngp13.phx.gbl...

This might be a dumb question to ask. Can someone answer me with

patience..

When I have a typed or un-typed dataset, the only way I can update is like below?
Dim con As New SqlConnection(ConnectionString)

con.Open()

Dim daProductsDS As New SqlDataAdapter("Select * from ProductsDS", con)

Dim oCommandBuilder As New SqlCommandBuilder(daProductsDS)

daProductsDS.DeleteCommand = oCommandBuilder.GetDeleteCommand

daProductsDS.InsertCommand = oCommandBuilder.GetInsertCommand

daProductsDS.UpdateCommand = oCommandBuilder.GetUpdateCommand

'Apply the updates

daProductsDS.Update(dsNorthwind, "ProductsDS")
Why do I have to delete and insert? Is the old fashion way of just

updating

dead in DOT.NET? Please help me understand this. Why was this done away

with

in dataset ? The other way is to manually use SQL statements to do
inserts/updates. But again I lose the power of using datasets.

Vijay