Updating A DataSet Through A Web Service

[David P. Donahue]

My experience with databases using C# has been pretty limited thus far.
Mostly I just use a SELECT statement to populate a DataSet, which is
just read-only (mostly for display on a web page), or maybe go so far as
to build an UPDATE or INSERT query with a couple parameters and just
execute it against the database. Currently, this is all done within a
web service, which acts as a kind of protective barrier between the
actual database and the internet.

For the first time, I'm writing an actual forms application instead of a
web application. Now, this forms application will have a couple
instances in which we'll want editable DataGrids that post their changes
back to the database. However, every tutorial I see online explaining
this concept always takes the approach of a direct connection between
the application and the database.

Given the stateless web service connection, where the client application
just gets a DataSet and would then need to pass the updated DataSet
object back to another web service function, does anyone know of any
example code I could see that would show how the web service would then
use that DataSet to update the database?


Regards,
David P. Donahue
(e-mail address removed)
http://www.cyber0ne.com

 

[Vijay]

At a very high level.. that dataset you are receiving from the webservice,
you have to keep it locally in a file.. say maybe xml?? and then do all ur
changes to it.. and push it back when you want.. The catch is to write your
own update statement back.., but if you maintain the XML is same format as
the Database table.. the update is pretty easy.. I also belivie you can use
DataAdapaters in this case.. i have not done... maybe someone else can
help.. I have written simple updates back.. as our tables and data are not
very complicated..

I have seen some samples in CodeProject and C#corner on these... I will try
to see if I can post back a link

HTH
Vijay

 

[David P. Donahue]

At a very high level.. that dataset you are receiving from the webservice,

you have to keep it locally in a file.. say maybe xml?? and then do all ur
changes to it

I've dabbled in XML serielization before. I'm sure I can dump the
DataSet to an XML file that way after the updates have been made to it
in memory, right?

.. and push it back when you want.. The catch is to write your
own update statement back.., but if you maintain the XML is same format as
the Database table.. the update is pretty easy..

I invision this requiring a lot of trial and error testing. Maybe .NET
has some classes to help with this of which I'm unaware, but I'm
picturing this to be an ugly task. But, if I have to do it...

I also belivie you can use
DataAdapaters in this case.. i have not done... maybe someone else can
help..

I was just playing around with the code for the web service in question.
I was wondering if, perhaps, the DataSet object had enough internal
data to know what to do when passed to a DataAdapter's update function.
But doesn't creating the DataAdapter object require the original
SELECT statement that created the DataSet?

I have written simple updates back.. as our tables and data are not
very complicated..

That's pretty much been the case here, too. But times change...


Regards,
David P. Donahue
(e-mail address removed)
http://www.cyber0ne.com

 

[Vijay]

At a very high level.. that dataset you are receiving from the

I've dabbled in XML serielization before. I'm sure I can dump the DataSet
to an XML file that way after the updates have been made to it in memory,
right? - Yes you can....


I invision this requiring a lot of trial and error testing. Maybe .NET
has some classes to help with this of which I'm unaware, but I'm picturing
this to be an ugly task. But, if I have to do it...

- No actually very easy... , I am not sure why u need Trial and Error here??

 

[David P. Donahue]

- No actually very easy... , I am not sure why u need Trial and Error here??

I guess I'm just not seeing an easy way to turn an XML file from a
serialized DataSet into a set of executable SQL statements...

Or am I not understanding something?


Regards,
David P. Donahue
(e-mail address removed)
http://www.cyber0ne.com

 

[Guest]

A simplified pattern with a webservice would be as follows:

1) You have a webmethod to return a DataSet of results based on a query.
2) You bind a table of this DataSet to your DataGrid.
3) You can make changes to the cells in the windows forms DataGrid and the
changes will modify the row data and row states of the rows in the underlying
DataTable.
4) you can have an UPDATE button that calls GetChanges on the DataTable (or
parent DataSet) and sends this to another web method that

puts this "getChanges" dataset into DataAdapter with the proper update,
insert and delete commands and calls it's Update method.

Hope that helps.
Peter

 

[David P. Donahue]

puts this "getChanges" dataset into DataAdapter with the proper update,

insert and delete commands and calls it's Update method.

Unfortunately, I'm not in a position where I can test this at the
moment, so I have a couple questions regarding the DataAdapter...

As far as I know, creating the DataAdapter object requires a SELECT
statement. For the purpose of using this DataAdapter to hold INSERT,
UPDATE, and DELETE statements, does that SELECT statement have to
exactly match the SELECT statement that produced the DataSet being
passed to this function? Or is it really arbitrary at that point in the
process?

For the INSERT, UPDATE, and DELETE statements, am I right in
understanding that I just write one generic one for a row in that table
with parameters and the object will know to iterate through the changed
records and plug them in as necessary? For example, suppose the
following table:

Columns for table Users:
UID (integer, PK, auto-increment)
Name (varchar(30), nullable)

Records:
1 Robert
2 Micheal
3 Jeff

I put the whole table into a DataSet and display it on a DataGrid. In
the grid, I correct the spelling of the second record. Then I raise
some event (button click, for example) that saves the .GetChanges of the
DataGrid's DataSource to a new DataSet and sends that DataSet to a web
service function.

That function creates a DataAdapter with an arbitrary SELECT statement,
but includes an UpdateCommand object as such:

OleDbCommand uCmd = new OleDbCommand("UPDATE Users SET Name=? WHERE
UID=?", dbConnection);
[define parameter types here, blah blah blah, set the DataAdapter's
UpdateCommand to this command]

Then I just call Update on that DataAdapter and pass it the DataSet and
it will update any changes records automatically?

What if the PK is changed? How will it then know what to update?



Regards,
David P. Donahue
(e-mail address removed)
http://www.cyber0ne.com

 

[Vijay]

Ok David.. i am giving a outline of this code... The two methods, the most
generic form to generate Update or Insert statements

private void SQLForInsertCols(ref string qry, DataRow dtDataRow)
{

foreach ( DataColumn col in dtDataRow.Table.Columns)
{
switch (col.ColumnName)
{
case "ID" :
break;
default :
qry = qry + "[" + col.ColumnName + "], ";
break;
}
}

//qry = qry.Substring(1,(qry.Length -1));
qry = qry.Remove(qry.LastIndexOf(','),1);

}



private void SQLForInsertColVals(ref string qry, DataRow drDataRow)
{

foreach ( DataColumn col in drDataRow.Table.Columns)
{
switch (col.ColumnName)
{
case "ID" :
break;
default:
if ( dtDataRow.IsNull(col.ColumnName))
{
qry = qry + " NULL,";
}
else if (dtDataRow[col.ColumnName].ToString().Trim() == "")
{
qry = qry + " NULL,";
}
else
{
if ( col.DataType == System.Type.GetType("System.String") ||
col.DataType == System.Type.GetType("System.DateTime") )
{
qry = qry + " '" +
drDataRow[col.ColumnName].ToString().Replace("'","''") + "', ";
}
else
{
qry = qry + " " +
drDataRow[col.ColumnName].ToString().Replace("'","''") + ", ";
}
}
break;
}

}

//qry = qry.Substring(1,(qry.Length -1));
qry = qry.Remove(qry.LastIndexOf(','),1);

}

The method suggested by peter works.. like he said.. if u can achivie that..

Vijay