Creating SQL Statement from Dataadapter Insertcommand

[Kamal Vaghjiani]

Using oleDBDataAdapter I want to loop through a recordset and generate
automatically a list of SQL insert/update statements.

i.e.
Update table set field1="test1" where id=1;
Update table set field1="test2" where id=2;
etc.....

How can this be done using the adapter, instead of manually generating each
statement via code.

KV

 

[Jon Skeet [C# MVP]]

Using oleDBDataAdapter I want to loop through a recordset and generate
automatically a list of SQL insert/update statements.

i.e.
Update table set field1="test1" where id=1;
Update table set field1="test2" where id=2;
etc.....

How can this be done using the adapter, instead of manually generating each
statement via code.

Create an adapter with an appropriate select command and insert command
(I believe you need the select in order to get the schema, but I could
be wrong). Create an empty datatable with the appropriate columns, and
add entries to it. Then call the data adapter's Update method, passing
in the DataTable.

 

[Kamal Vaghjiani]

How will this expose a list of statements ?

I need to get a list of SQL statements to pass to a webservice.

These statements will be used to in synchronising data if record on server
is out of date

KV

 

[Cor]

Hi Kamal,

I know how to read from a recordset in a dataset using the dataadapter.
However how to update a recordset than I do not know.

Assuming like I think Jon did too that you want to use a dataset. Than I
assume also that you are looking for the command parameters.

http://msdn.microsoft.com/library/d...mDataOleDbOleDbDataAdapterClassctorTopic2.asp

I hope this helps?

Cor

 

[Jon Skeet [C# MVP]]

How will this expose a list of statements ?

I need to get a list of SQL statements to pass to a webservice.

These statements will be used to in synchronising data if record on server
is out of date

Ah, right. Sorry, I thought you wanted to *execute* the statements.

In that case, I don't know. Frankly, I think it would be better to pass
the SQL including the parameter names in, and then the parameter
separately - that should end up being much more portable, and won't
require any clever quoting etc.

 

[Kamal Vaghjiani]

Thank you for your replies

Sorry I may have confused the issue here

Basically I am trying to create a solution which will synchronize data
stored locally in Access DB, and
when the application has an active internet connection will synchronize data
to SQL server using a web service.

This process will be a two way process.

At the moment I am testing different methods of doing this.

Method proposed

[Web Method]
public string SyncRecord(string RecID,datetime ModifiedDate, string
insertQry, string updateQry)
{
if record (RecID) exists
{
if ModifiedDate > Server.ModifiedDate
execute updateQry
else
return update statement for server record
}
else
{
execute insertQry
}

}

The above method needs to be passed a valid update string and insert string.
I can either manually create these or try to figure out how I can get the
DataAdapter to feed these strings to me with all the parameters filled in.

I hope this is sufficient info

Regards

Kamal

 

[Jon Skeet [C# MVP]]

The above method needs to be passed a valid update string and insert string.
I can either manually create these or try to figure out how I can get the
DataAdapter to feed these strings to me with all the parameters filled in.

As I said before, I don't believe filling the parameters in is the best
way of doing things. If you could send the command and parameter
definitions once, and then a list of rows with just the parameter
values (in some appropriate format) then you'll probably find it's more
efficient *and* it'll probably be more robust than sending direct SQL.

Don't forget that the database provider might not even end up creating
SQL itself - depending on the protocol, it may be sending the
parameters down the wire in some native format, and letting the
database itself work out what to do.

 

[Brad Williams]

Does OleDbCommandBuilder help you?

Brad Williams