What is the quickest way to get an xml based dataset to add entries to SQL Server? - XML SOAP

"MurdockSE" <murdock@nc.rr.com> wrote in message
news:1153922058.675709.165630@h48g2000cwc.googlegroups.com...
> Because there is no 'datareader' involved, what is the most efficient
> way to get an XML based dataset to be added to an SQL server dataset? I
> would hate to have to create a new SQL connection and iterate through
> making "executenonquery" for each row in the dataset. That seems
> horribly in-efficient. I know of no other way to get an xml based
> dataset to push data to the database though. Any pointers?

Use a DataAdapter and do an Update. Just make sure all the rows youi want
added are marked as being in Add state.

John

Excuse my ADO.NET naivite (newbie alert*) but how do I iterate the
existing ds and set all of the rows to ADD?

For example:

DataSet ds = new DataSet("MyRecords");
ds.ReadXml(xmlfilename);

And the seperate SQL creations would be similar to this:

using System.Data.SqlClient;
using System.Data;

string strDBConnection = "server=(local);database=DatabaseName;user
id=UserName;password=Pwd;connection reset=false;connection
lifetime=5;Trusted_Connection=Yes;"
SqlConnection dbConnection;
dbConnection = new SqlConnection(strDBConnection);

string strSelectSql = "Select * from
[DatabaseName].[OwnerName].[TableName] order by FieldName";

//Open the connection
dbConnection.Open();

//Create a command
SqlCommand selectSqlCommand = new
SqlCommand(strSelectSql,dbConnection);
SqlDataAdapter sqlData = new SqlDataAdapter(selectSqlCommand);
DataSet dsSelectData = new DataSet();
sqlData.Fill(dsSelectData);

How do I get the ds into the sqlData adapter set to ADD each row?


Peter Robbins

I got the answer/s I was looking for here:

http://groups.google.com/group/micro...9226fef?hl=en&

Thanks again. You basically stated the same thing. My naive self just
needed a little extra push on the particulars. Thanks.

Peter Robbins