Saving data in DataSet from one source in another

  • Thread starter Thread starter Przemo
  • Start date Start date
P

Przemo

Hi,

I have a dataset containing 2 tables from XML file. I
have used XmlTextReader and DataSet.ReadXml to do it.
These data are shown on user form using DataGrid. Now I
would like to inset these data (after approving by the user)
into 2 tables in SQL2000. Tables of course have required
columns but with different names.
Anybody knows how to do it? I thought about binding
existing DataSet with new DataAdapter or making a new
copy of DataSet. But with no result...

Thanks!

Przemo.
 
Hi!

Use parameters with your update sql statment.

Ill just do it with one example table, but the idea is the same with both.

DS_Table1 //The first table in the dataset has the following design
-----------
MyID int
MyData string


DB_Table1 //The fírst table in the database has the following design
------------
TableId int
TableData nvarchar(50)


Ok, now you need to insert the dataset table data into the database table so
write the sqlstatment like this.

string commandString = "UPDATE DB_Table1 SET (TableData = @MyData) WHERE
TableId = @MyId";

The thing is that if the parameters are named the same as the columns in the
dataset then you can
let a dataadapter do the work for you and do the updates needed.
I don't know if you use a OleDb client or SqlClient. I will show you with
the SqlClient but they are similar. I will not bother to open or close
connections and such.

SqlConnection cn = new SqlConnection("MyConnectionString");

SqlCommand cmd = new SqlCommand(commandString, cn);

//Here you add the diffrent parameters that your command needs, you give
them
//names, declare what type they are, their length and what the corresponding
column is named
cmd.Parameters.Add("@MyData", SqlDbType.NVarChar, 50, "MyData");
cmd.Parameters.Add("@MyId", SqlDbType.Int, 4, MyId");

SqlDataAdapter da = new SqlDataAdapter();
da.UpdateCommand = cmd;

//Here the dataadapter will use the update command you have supplied to
update your tables in the database
da.Update(ds.Tables["DS_Table1"] );

Well, I hope this helps you a bit on the way at least. Good luck!

//Mikael
 
Also,
you can use the TableMappings and ColumnMappings properties of the
dataadapter during the import process to dynamically change the table and
cloumn names to match the ones in the SQL database so you can take advantage
of the simpler upload method of the 2nd dataadapter.
--
Joe Fallon



Mikael Gustavsson said:
Hi!

Use parameters with your update sql statment.

Ill just do it with one example table, but the idea is the same with both.

DS_Table1 //The first table in the dataset has the following design
-----------
MyID int
MyData string


DB_Table1 //The fírst table in the database has the following design
------------
TableId int
TableData nvarchar(50)


Ok, now you need to insert the dataset table data into the database table so
write the sqlstatment like this.

string commandString = "UPDATE DB_Table1 SET (TableData = @MyData) WHERE
TableId = @MyId";

The thing is that if the parameters are named the same as the columns in the
dataset then you can
let a dataadapter do the work for you and do the updates needed.
I don't know if you use a OleDb client or SqlClient. I will show you with
the SqlClient but they are similar. I will not bother to open or close
connections and such.

SqlConnection cn = new SqlConnection("MyConnectionString");

SqlCommand cmd = new SqlCommand(commandString, cn);

//Here you add the diffrent parameters that your command needs, you give
them
//names, declare what type they are, their length and what the corresponding
column is named
cmd.Parameters.Add("@MyData", SqlDbType.NVarChar, 50, "MyData");
cmd.Parameters.Add("@MyId", SqlDbType.Int, 4, MyId");

SqlDataAdapter da = new SqlDataAdapter();
da.UpdateCommand = cmd;

//Here the dataadapter will use the update command you have supplied to
update your tables in the database
da.Update(ds.Tables["DS_Table1"] );

Well, I hope this helps you a bit on the way at least. Good luck!

//Mikael



Przemo said:
Hi,

I have a dataset containing 2 tables from XML file. I
have used XmlTextReader and DataSet.ReadXml to do it.
These data are shown on user form using DataGrid. Now I
would like to inset these data (after approving by the user)
into 2 tables in SQL2000. Tables of course have required
columns but with different names.
Anybody knows how to do it? I thought about binding
existing DataSet with new DataAdapter or making a new
copy of DataSet. But with no result...

Thanks!

Przemo.
Click to expand...
Click to expand...
 
Back
Top