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
dd******@ccs.neu.edu
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" <dd******@ccs.neu.edu> wrote in message
news:%2****************@TK2MSFTNGP11.phx.gbl...

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
dd******@ccs.neu.edu
http://www.cyber0ne.com

[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
dd******@ccs.neu.edu
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
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....

.. 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...

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

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? - Yes you need to have the original
select... , that is why i mostly don't use DataAdapaters, as our
fetch/update vary.. and we can't maintain the select....

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... - Yes
they do... these really depend onn what is your case... if you can give me
a more close example what you are trying to do, then we can see what is
best for you
Regards,
David P. Donahue
dd******@ccs.neu.edu
http://www.cyber0ne.com

[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
dd******@ccs.neu.edu
http://www.cyber0ne.com

[Peter Bromberg [C# MVP]]

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

--
Co-founder, Eggheadcafe.com developer portal:
http://www.eggheadcafe.com
UnBlog:
http://petesbloggerama.blogspot.com


"David P. Donahue" wrote:

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
dd******@ccs.neu.edu
http://www.cyber0ne.com

[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
dd******@ccs.neu.edu
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

"David P. Donahue" <dd******@ccs.neu.edu> wrote in message
news:%2****************@TK2MSFTNGP11.phx.gbl...

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
dd******@ccs.neu.edu
http://www.cyber0ne.com