Creating SQL Statement from Dataadapter Insertcommand

  • Thread starter Thread starter Kamal Vaghjiani
  • Start date Start date
K

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
 
Kamal Vaghjiani said:
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.
Click to expand...

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.
 
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
 
Kamal Vaghjiani said:
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
Click to expand...

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.
 
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
 
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.
Click to expand...

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