Copying a Record from one DataTable to Another.

[Wayne Gibson]

Hi,
Firstly thanks for taking a look this thread.

I'm looking to update/insert records from one database into another. The
databases will look something like this:

Database1.MDB
Table1
fields (Won't know what these are at runtime)

Database2.MDB
Table1
fields (Won't know what these are at runtime)
ExtraField (Will know what this is at runtime)

I'm using a datareader to read an Access Table, using a "Select * from
TableName". I have no idea of what the structure of the table is, so this
is why it is being read into a datatable.
The destination table is exactly the same, except it would have an extra
field. Which I can update separately.
I could clone the datarow, which isn't a problem. The problem is
updating/inserting the row into the destination table. Am I right in
thinking that I would need to create the SQL Query to do this, by reading
the schema for the table to get the field names and types. Then execute it.
Or is there a simpler way of doing this..

Many thanks

Gibbo

 

[Guest]

Here is a code sample that shows you how to use DataTable.Merge:

using System;
using System.Collections.Generic;
using System.Text;
using System.Data;

namespace ConsoleApplication4
{
class Program
{
static void Main(string[] args)
{
DataTable dataTable1 = GetTable1();
DataTable dataTable2 = new DataTable();
dataTable2.Merge(dataTable1, true, MissingSchemaAction.Add);
AddExtraColumn(ref dataTable2);

foreach(DataColumn DC in dataTable1.Columns)
{
Console.Write(DC.ColumnName + "\t");
}
Console.Write("\n");
foreach(DataRow DR in dataTable1.Rows)
{
foreach(object obj in DR.ItemArray)
{
Console.Write(obj.ToString() + "\t");
}
Console.Write("\n");
}

//Data Added after table1 was merged with table 2
(dataTable2.Rows[0])["ExtraCol0"] = "DataAdded";

foreach(DataColumn DC in dataTable2.Columns)
{
Console.Write(DC.ColumnName + "\t");
}
Console.Write("\n");
foreach(DataRow DR in dataTable2.Rows)
{
foreach(object obj in DR.ItemArray)
{
Console.Write(obj.ToString() + "\t");
}
Console.Write("\n");
}

}
static DataTable GetTable1()
{
DataTable DT = CreateTable();
for (int x = 0; x < 4; x++)
{
DataRow R = DT.NewRow();
for (int y = 0; y < 4; y++)
{
string ColumnName = string.Format("Col{0}", y.ToString());
R[ColumnName] = string.Format("Data For: {0}",
ColumnName);
}
DT.Rows.Add(R);
}
return DT;

}
static DataTable CreateTable()
{
DataTable DT = new DataTable();
for (int x = 0; x < 4; x++)
{
string ColumnName = string.Format("Col{0}", x.ToString());
DataColumn DC =
new DataColumn(ColumnName, Type.GetType("System.String"));
DT.Columns.Add(DC);
}
return DT;

}
static void AddExtraColumn(ref DataTable dataTable)
{
string ColumnName = "ExtraCol0";
DataColumn DC =
new DataColumn(ColumnName, Type.GetType("System.String"));
dataTable.Columns.Add(DC);
}
}
}

 

[Cor Ligthert [MVP]]

Wayne,

I don't know if you are talking about an MS Office Access System or about an
Jet Engine with one table.

In the later case is extern renaming of the file the most propieriate.

Cor

 

[Wayne Gibson]

Hi Josh,
Create bit of code

Just one question, how do I then write the rows stored in dataTable2 to
Table in an Access database?

Thanks

Gibbo

 

[Guest]

You would use a dataadapter which has command objects for Insert and update
implemented. I recommend picking up a book on ADO.NET. You can also pass
command objects back independent of a dataset. This would just require you
foreach through each row and serialize based on the RowState. Then
AcceptChanges after you done writting the MDB.
+
DataAdapter Documentation
http://msdn2.microsoft.com/en-us/library/system.data.common.dataadapter_members.aspx

RowState:
http://msdn2.microsoft.com/en-us/library/system.data.datarow.rowstate.aspx

Information on connecting to an MDB
http://searchsqlserver.techtarget.com/generic/0,295582,sid87_gci1188407,00.html

Reason I am throwing documentation at you. Is its a multipart question that
infers ignorance on major concepts.
1)How do I connect to Access MDB?
Answer:
OLE-DB provider using a System.Data.Connection object
2) How do I create a table with the same base columns and one additional
column?
Answer:
If you are keeping both tables around. You are better making a table with an
ID and extra columns. Then either making a junction table between them (ID of
table A and ID of table B) or adding a foreign key to the base table pointing
back to the extra columns value table. Otherwise Select Into than add columns
you want if you still want to keep a copy of of the originial around. Then
Add columns.
3)How do I connect a Disconnected ADO.NET object (DataTable) to provide data
back to my database?
Answer:
Data Adapter.

I can highley recommend the Microsoft press book ADO.NET 2.0 Advance Topics
it is invaluable to learning the lay of the land of ADO.NET classes. Starts
from disconnected classes DataTable and a collection of related DataTables (a
DataSet) to connected classes (like the DataReader and DataAdapter) to
Transactions on up.