Okay, so this function is supposed to update a table with the values from a dataset, right?
Does anyone have some good sample code for using one dataAdapter to pull data from one data source, throw that data into a dataset and then use that dataset as the parameter in a dataAdapter.update call to a different data source.
Basically, I have a table in DB2 and I have a matching table on a MS SQL Server box and I want to copy the data from DB2 to the MS box with as few lines as possible.
Thanks for any help/advice.Not sure this is possible as the key information is gathered or detailed in the code is specific to the connection.
But the dataAdapter for SQL is
DataSet ds = new DataSet();
cmd.CommandType = CommandType.StoredProcedure;
SqlDataAdapter da = new SqlDataAdapter(cmd);
da.Fill(ds,"TableName");
da.Dispose(); //ADO.net is not managed requires explicit release.
that should fill your data adpater.Well, here's what I have at the moment...
OdbcConnection myConn = new OdbcConnection(strDb2Conx);
OdbcDataAdapter myDataAdapter = new OdbcDataAdapter();
string mySelectQuery = "SELECT * FROM BATCH_TAB WHERE BT_CREATE_TS >= '" +
string.Format("{0:yyyy-MM-dd}",startDate) + " 00:00:00.00000'";
myDataAdapter.SelectCommand = new OdbcCommand(mySelectQuery, myConn);
OdbcCommandBuilder custCB = new OdbcCommandBuilder(myDataAdapter);
myConn.Open();
DataSet custDS = new DataSet();
myDataAdapter.Fill(custDS);
myConn.Close();
SqlDataAdapter da = new SqlDataAdapter("SELECT * FROM IREP_BATCH_TAB",strSqlConx);
da.Update(custDS,"IREP_BATCH_TAB");
I wasn't getting any errors with it (that I can remember - can't test run it at the moment to see if I made any "breaking" changes) but the data wouldn't transfer.You do realize that you can use the OLE DB command object like that of the SQL command object right?
select * from tbl where field = @Value
cmd.Parameters.Add(new Parameter("@Name",DbType.Int,4));
cmd.Parameters["@Name"].Value = 1;
it will actually replace the fields defined in teh query correctly for you type safe and correctly padded for strings/dates.
You most likely are going to have to create a Data from an empty selected table... IE
select * from table where 1 = 0
so that the dataSet gets the key information and fields.
Then you can use a dataReader on the first on to insert into the actual SQL table appending the correct fileds into the table, then calling the dataSet's update method to update the table.while(dr.Read())
{
ds.Tables[1].columns[0].Value = ds.Tables[2].Columns[0].Value;
}
ds.Tables
or in the MSDN version..
DataSet custDS = new DataSet();
custDA.FillSchema(custDS, SchemaType.Source, "Customers");
custDA.Fill(custDS, "Customers");
DataSet orderDS = new DataSet();
orderDS.ReadXml("Orders.xml", XmlReadMode.ReadSchema);
orderDS.AcceptChanges();
nwindConn.Close();
custDS.Merge(orderDS, true, MissingSchemaAction.AddWithKey);
....
Does anyone have some good sample code for using one dataAdapter to pull data from one data source, throw that data into a dataset and then use that dataset as the parameter in a dataAdapter.update call to a different data source.
Basically, I have a table in DB2 and I have a matching table on a MS SQL Server box and I want to copy the data from DB2 to the MS box with as few lines as possible.
Thanks for any help/advice.Not sure this is possible as the key information is gathered or detailed in the code is specific to the connection.
But the dataAdapter for SQL is
DataSet ds = new DataSet();
cmd.CommandType = CommandType.StoredProcedure;
SqlDataAdapter da = new SqlDataAdapter(cmd);
da.Fill(ds,"TableName");
da.Dispose(); //ADO.net is not managed requires explicit release.
that should fill your data adpater.Well, here's what I have at the moment...
OdbcConnection myConn = new OdbcConnection(strDb2Conx);
OdbcDataAdapter myDataAdapter = new OdbcDataAdapter();
string mySelectQuery = "SELECT * FROM BATCH_TAB WHERE BT_CREATE_TS >= '" +
string.Format("{0:yyyy-MM-dd}",startDate) + " 00:00:00.00000'";
myDataAdapter.SelectCommand = new OdbcCommand(mySelectQuery, myConn);
OdbcCommandBuilder custCB = new OdbcCommandBuilder(myDataAdapter);
myConn.Open();
DataSet custDS = new DataSet();
myDataAdapter.Fill(custDS);
myConn.Close();
SqlDataAdapter da = new SqlDataAdapter("SELECT * FROM IREP_BATCH_TAB",strSqlConx);
da.Update(custDS,"IREP_BATCH_TAB");
I wasn't getting any errors with it (that I can remember - can't test run it at the moment to see if I made any "breaking" changes) but the data wouldn't transfer.You do realize that you can use the OLE DB command object like that of the SQL command object right?
select * from tbl where field = @Value
cmd.Parameters.Add(new Parameter("@Name",DbType.Int,4));
cmd.Parameters["@Name"].Value = 1;
it will actually replace the fields defined in teh query correctly for you type safe and correctly padded for strings/dates.
You most likely are going to have to create a Data from an empty selected table... IE
select * from table where 1 = 0
so that the dataSet gets the key information and fields.
Then you can use a dataReader on the first on to insert into the actual SQL table appending the correct fileds into the table, then calling the dataSet's update method to update the table.while(dr.Read())
{
ds.Tables[1].columns[0].Value = ds.Tables[2].Columns[0].Value;
}
ds.Tables
or in the MSDN version..
DataSet custDS = new DataSet();
custDA.FillSchema(custDS, SchemaType.Source, "Customers");
custDA.Fill(custDS, "Customers");
DataSet orderDS = new DataSet();
orderDS.ReadXml("Orders.xml", XmlReadMode.ReadSchema);
orderDS.AcceptChanges();
nwindConn.Close();
custDS.Merge(orderDS, true, MissingSchemaAction.AddWithKey);
....