Saving data in DataSet from one source in another

TheMSsForum.com: The Microsoft Software Forum

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

Re: Saving data in DataSet from one source in another by Mikael

Mikael
Sun Jul 27 17:22:08 CDT 2003

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" <p.dutkiewicz@deltatrans.pl> wrote in message
news:013201c35472$c0882a60$a101280a@phx.gbl...
> 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.
>


Top

Re: Saving data in DataSet from one source in another by Joe

Joe
Sun Jul 27 18:12:16 CDT 2003

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" <muffiz[at]hotmail.nospam.com> wrote in message
news:ecGdB2IVDHA.3220@tk2msftngp13.phx.gbl...
> 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" <p.dutkiewicz@deltatrans.pl> wrote in message
> news:013201c35472$c0882a60$a101280a@phx.gbl...
> > 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.
> >
>
>


Top