I have an application that uses an access database to store data. In this database I have "template" tables that I need to be able to copy the template and all its structures (such as autoincrement columns, allow zero value, properties etc.), rename it and write back to the access database. The templates just hold the correct table structures necessary for the application.
Previously I had done this using ADO and ADOX where it was relatively easy. Now I am in the process of updating the code & doing some bug-fixes I thought I would also update it to ADO.NET but I'm stuck on this one function. I hope someone out there has had to do this before and can help?
Before anyone says, I know this is easy to do if using SQL Server but for commercial licensing reasons I have to use Access (JET Engine) as the RDBMS and the JET engine has some limited functionality.
First I tried using the SQL Query "SELECT * INTO [destTable] FROM [sorceTable] but this only copies the table and values but not the properties (i.e. if their is a primary key in the source table it is not copied to the destination table).
So my idea was to get the table in a DataSet, clone it, rename it and using a DataAdapter write it back to the database.
Below is my test program...
The problem I have now is the SQL command built by the CommandBuilder refers to the table retrieved using the Fill method, and not the newly cloned DataTable (which makes sense since the cloned table wasn't retrieved from the access database using the Fill method).
So the solution is to manually create the correct SQL command but I don't know how to determine the correct SQL command when you don't know the structure of the table?
I even tried copying the SelectCommand.CommandText produced by the CommandBuilder and manipulating it to change the name of the table in the command to that of the destination table but that didn't work (I tried with both UPDATE & INSERT INTO commands), so now I'm stuck.
Maybe my concept of doing this using the DataSet and DataAdapter is wrong and someone can suggest an easier option?
Many Thanks
Axel
Previously I had done this using ADO and ADOX where it was relatively easy. Now I am in the process of updating the code & doing some bug-fixes I thought I would also update it to ADO.NET but I'm stuck on this one function. I hope someone out there has had to do this before and can help?
Before anyone says, I know this is easy to do if using SQL Server but for commercial licensing reasons I have to use Access (JET Engine) as the RDBMS and the JET engine has some limited functionality.
First I tried using the SQL Query "SELECT * INTO [destTable] FROM [sorceTable] but this only copies the table and values but not the properties (i.e. if their is a primary key in the source table it is not copied to the destination table).
So my idea was to get the table in a DataSet, clone it, rename it and using a DataAdapter write it back to the database.
Below is my test program...
Code:
/*
* function: CopyTableLayout
* Date-Author: 27-Jan-2012 PCAB
* ------------------------------------------------------------------
* This function attempts to retrieve a table from an access database
* and load it into a DataSet. Clone the resulting DataTable in
* the DataSet (with all its structure, constraints etc.) and then
* write the newley created DataTable back to the underlying
* access database.
*/
public static bool CopyTableLayout(string tableName)
{
string strConn = @"Provider=Microsoft.Jet.OLEDB.4.0;" +
@"Data source= C:\exDatabase\MikeVideo2004.mdb";
string strQuery = "SELECT * FROM " + tableName;
System.Data.DataSet ds = new System.Data.DataSet();
OleDbConnection conn = new OleDbConnection(strConn);
//-- DataAdapter used for retrieving source access table & CommandBuilder
//-- for generating Update, Insert & Delete queries.
OleDbDataAdapter adapter = new OleDbDataAdapter();
adapter.SelectCommand = new OleDbCommand(strQuery, conn);
OleDbCommandBuilder cmdBlder = new OleDbCommandBuilder(adapter);
try
{
conn.Open();
//-- add source DataTable to DataSet.
adapter.Fill(ds, tableName);
////-- clone DataTable & rename it.
System.Data.DataTable dtClone = ds.Tables[tableName].Clone();
string cloneTableName = tableName + "_new"; // <-- added for clarity!
dtClone.TableName = cloneTableName;
//-- add the clone of the DataTable to the DataSet.
ds.Tables.Add(dtClone);
cmdBlder.GetUpdateCommand(); // <-- THIS CAUSES THE PROBLEM.
adapter.Update(ds, tableName + "_new");
Console.WriteLine(string.Format("DataTable {0} copied successfully", tableName));
return true;
}
catch (SystemException ex)
{
Console.WriteLine("Error while attempting to copy {0}...\n{1}", tableName, ex.ToString());
return false;
}
finally
{
conn.Close();
}
}
The problem I have now is the SQL command built by the CommandBuilder refers to the table retrieved using the Fill method, and not the newly cloned DataTable (which makes sense since the cloned table wasn't retrieved from the access database using the Fill method).
So the solution is to manually create the correct SQL command but I don't know how to determine the correct SQL command when you don't know the structure of the table?
I even tried copying the SelectCommand.CommandText produced by the CommandBuilder and manipulating it to change the name of the table in the command to that of the destination table but that didn't work (I tried with both UPDATE & INSERT INTO commands), so now I'm stuck.
Maybe my concept of doing this using the DataSet and DataAdapter is wrong and someone can suggest an easier option?
Many Thanks
Axel