C# - Copying Rows between DataTables

[EFileTahi-A]

Whats the best way of copying rows from a DataTable to another Datable?

 

Thanks.

[Nerseus]

All rows? Try this:

// Copy Table 0 to a new table named "MyNewName"
DataTable newTable = ds.Tables[0].Copy();
newTable.TableName = "MyNewName";
ds.Tables.Add(newTable);

 

If you only want certain rows, use ImportRow. Below I copy the first 3 rows of table 0 into a new table. Assumes 3 rows exist :)

DataTable newTable = ds.Tables[0].Clone();
newTable.TableName = "MyNewName";
for(int i=0; i<3; i++)
{
newTable.ImportRow(ds.Tables[0].Rows[i]);
}
ds.Tables.Add(newTable);

 

-ner

[EFileTahi-A]

Hi nerseus! Thanks for the reply, still, how about declaring 2 independent static datatables each one in one form and then performing the rows copy between them... :D Is this possible?

[Nerseus]

Yes - you can use either code snippet depending on what you want. I'm not sure if your DataTables start at null or if they're already defined (and possibly populated). If you need help, let me know more about what you've got and what you're trying to get to.

 

-ner

[EFileTahi-A]

I see that you used a DataSet to perforn the copy, but I don't want to use DataSet... Well, is it really necessary to use Datasets to copy rows between tables?

[Nerseus]

The Copy method is actually at the DataTable level. If you look at the code, I go through the DataSet to get to Table sub 0. It's the same as:

DataTable newTable = ds.Tables[0].Copy();

// Same as 
DataTable myTable = ds.Tables[0];
DataTable newTable = myTable.Copy();

 

So in the above, pretend that all you have is myTable. Regardless of where it came from, if it's an active object (not a null reference) you should be able to Copy it, Clone it and then loop through its rows.

 

-ner

[EFileTahi-A]

Well, notice this:

 

I have declared a Static DataTable on DB_Engine class, which have to following code:

 

//Query to execute in order to load the DataTable
sSQL = "SELECT * FROM InDocLin";

//Here I create a temporarely DataTable to store all Data from InDocLin table
OdbcDataAdapter daTemp = new OdbcDataAdapter(sSQL, MyConnData);

//Now I Fill the dtTemp with all the InDocLin data
System.Data.DataTable dtTemp = new System.Data.DataTable(); 
daTemp.Fill(dtTemp);

//I clone all the info from dtTemp to the Static DataTable (dtDocLinTemp)
dtDocLinTemp = dtTemp.Clone();

//Clearing any existing rows
dtDocLinTemp.Rows.Clear(); 

 

I think I have now my static DataTable ready to recieve new rows with the InDocLin table shema right?

 

Ok, now during run time, this static var dtDocLinTemp will suffer changes, that is, rows will be added. So, when it reachs the copy part, I will need to retrieve all the added rows from dtDocLinTemp to the DocLinTemp mysql table.

 

I did this code to acomplish this:

 

// here is dtInDocLin Table that will recieve the new rows from the static
Datatable DocLinTemp

//I did this to get the rows from the static table
DataRow[] rows = DB_Engine.dtDocLinTemp.Select();

//So now I supposely just need to put them back on dtInDocLin
dtInDocLin.Rows.Add(rows);

 

Unfortunatelly it give me this error when executinr the dtInDocLin.Rows.Add(rows); line:

 

Couldn't store <System.data.DataRow> in "numero" column expecting type is int32

 

 

I have no ideia how to see the values within rows, I mean, in a datatable I do datatable.rows[rownumber][colname] but with rows I haven't figure out yet... I can't tell what am I seding in number

column back to dtInDocLin...

 

Am not experience with C# database related objects. Hell! Am not experience in Oriented Objects languages all at! So, please, be patience with me...

Edited February 16, 2005 by EFileTahi-A

[Nerseus]

First, you don't need to Clear the rows in the code below, Clone only copies the Schema, not the data. The Copy method copies both the schema and the data:

dtDocLinTemp = dtTemp.Clone();
// Don't need to do the following
// dtDocLinTemp.Rows.Clear(); 

 

Second, I mentioned (twice) that you have to use ImportRow. Add only works for DataRows not associated to a DataTable, such as when you use NewRow to create a row. Here's probably what you want:

Datatable DocLinTemp = DB_Engine.dtDocLinTemp.Clone();

//So now I supposely just need to put them back on dtInDocLin
foreach(DataRow row in DB_Engine.dtDocLinTemp.Rows)
{
   DocLinTemp.Rows.ImportRow(row);
}

 

In the foreach above, I just coded it to loop through every row in the DataTable dtDocLinTemp. If you truly wanted the Select() method to do filtering or sorting, you could. Since you were just using it to get a DataRow array, I removed that extra step.

 

NOTE: In your last post, the second code snippet has THREE DataTables:

Datatable DocLinTemp // This is #1

DB_Engine.dtDocLinTemp // This is #2, used in the Select

dtInDocLin.Rows.Add // This is #3

 

I have no idea what dtInDocLin is, if it has the same schema as DB_Engine.dtDocLinTemp.

 

I'm guessing that your code was just a typo and that the last line should have been DocLinTemp. But you'd have to make sure the schema came from the table you're importing rows from.

 

-ner

[EFileTahi-A]

dtInDocLin is the table that will recieve the new rows from dtInDocTemp, that will also send the changes to the mySQL Data Base. Sorry I was not explicit about this... And yes, this was a typo :)

 

Anyway, by the time you made post I actualy saw the "import" thing you mentioned (twice) and I managed to do it... unfortunately the dtInDocLin which got the new rows from dtDocLinTemp for some good reason are not recorded into the database after doing dtInDocLin.Update()

 

Am Trying to fix this up, If I get trouble about it I will post...

 

Thank you for you help Nerseus! Truly apreciated!

[EFileTahi-A]

Am begining to desperate...

 

I just can't make this code save the records on mySQL database...

I wonder what am I doing wrong...

 

DataTable dtInDocLin = new DataTable();
DataRow newRow; 

string sRecBefore = "";
string sRecNow = "";

sSQL = "SELECT * FROM InDocLin";

OdbcDataAdapter daInDocLin = new OdbcDataAdapter(sSQL, MyConnection);
daInDocLin.Fill(dtInDocLin);
OdbcCommandBuilder cbInDocLin = new OdbcCommandBuilder(daInDocLin);

//dtDocLinTemp is a declared as static datatable in the declaration form's section
dtDocLinTemp = dtInDocLin.Clone();

//adding a new row into the static datatable
newRow = dtDocLinTemp.NewRow();
newRow["numlin"] = 30;
newRow["numdoc"] = 10; 
dtDocLinTemp.Rows.Add(newRow);

//I don't know if acceptchanges is really necessary
dtDocLinTemp.AcceptChanges();

//this actualy counts the correct rows inside dtDocLinTemp which is "1"
sRecBefore = dtDocLinTemp.Rows.Count.ToString();

foreach(DataRow row in dtDocLinTemp.Rows)
{
	dtInDocLin.ImportRow(row);
}

daInDocLin.Update(dtInDocLin);
dtInDocLin.AcceptChanges();

//This also returns the correct number of rows which is "1"
sRecNow =  dtInDocLin.Rows.Count.ToString();

 

This code works with no prob, only, dtInDocLin does not saves the new row into the mysql database...

Edited February 17, 2005 by EFileTahi-A

[penfold69]

Don't use AcceptChanges at all. It marks the records as 'Accepted and unchanged' so the DataAdapter believes that they don't need to be updated into the database.

 

B.

[EFileTahi-A]

Ahhhh, yes, you're right, it works fine without the AcceptChanges :)

 

Thanks to all who post here...

 

- THREAD CLOSED -