Export dataset to Ms Access .mdb file

[PROKA]

It's pretty urgent, I have to hand in a project in 1 hour.

 

I have this dataset with data in it, and I want to export it to an .mdf file

 

Please help ! :(( my neck is going to be cut off if you don't help me :(

[Joe Mamma]

do you have ms access available for interop or only the jet engine?

[Joe Mamma]

how much of the schema do you need? just the tabes and columns??? or relations, defaults and constraints, too????

[PROKA]

actually, there's only one table in the dataset. so pretty much, that one table and the columns

[Joe Mamma]

just hacked this together . . . no test -

but this copies the schema to a Jet Database (creating it if it doent exist)

once the schema is copied, moving the data is trivial, isnt it???

 

usage:

ADOXJetXML.DatasetToJet.CopyDatasetSchemaToJetDB(someDataset, someFilePath);

 

NOTE: requires interop of the ADO and ADO extensions Libraries!

using ADOX;
using ADODB;
using System;
using System.Data;
using System.Data.OleDb;
namespace ADOXJetXML
{
/// <summary>
/// Summary description for DatasetToJet.
/// </summary>
public class DatasetToJet
{
 static public void CopyDatasetSchemaToJetDB(DataSet ds, string jetFileName)
 {
  string connstr = string.Format(@"Provider=Microsoft.Jet.OLEDB.4.0;" +
   @"Data Source={0};Jet OLEDB:Engine Type=5;", jetFileName);
  Catalog cat = new CatalogClass();
  if (!System.IO.File.Exists(jetFileName))
   cat.Create(connstr);
  else
  {
   cat.ActiveConnection = new ADODB.ConnectionClass(); 
   (cat.ActiveConnection as ADODB.Connection).Open(connstr, "","", -1);
  }
  foreach(DataTable table in ds.Tables)
   cat.Tables.Append(CopyDataTable(table));
 }

 static private ADOX.Table CopyDataTable(DataTable table)
 {
  ADOX.Table adoxTable = new ADOX.TableClass();
  adoxTable.Name = table.TableName;
  foreach(System.Data.DataColumn col in table.Columns)
   adoxTable.Columns.Append(
    new ADOX.ColumnClass(), 
    TranslateDataTypeToADOXDataType(col.DataType),
    col.MaxLength);
  return adoxTable;
 }

 static private ADOX.DataTypeEnum TranslateDataTypeToADOXDataType(Type type)
 {
  string guid = type.GUID.ToString();
  return 
   guid == typeof(bool).GUID.ToString() ? ADOX.DataTypeEnum.adBoolean : 
   guid == typeof(byte).GUID.ToString() ? ADOX.DataTypeEnum.adUnsignedTinyInt: 
   guid == typeof(char).GUID.ToString() ? ADOX.DataTypeEnum.adChar : 
   guid == typeof(DateTime).GUID.ToString() ?  ADOX.DataTypeEnum.adDate :  
   guid == typeof(decimal).GUID.ToString() ?  ADOX.DataTypeEnum.adDecimal : 
   guid == typeof(double).GUID.ToString() ?  ADOX.DataTypeEnum.adDouble : 
   guid == typeof(Int16).GUID.ToString() ?  ADOX.DataTypeEnum.adSmallInt :  
   guid == typeof(Int32).GUID.ToString() ?  ADOX.DataTypeEnum.adInteger : 
   guid == typeof(Int64).GUID.ToString() ?  ADOX.DataTypeEnum.adBigInt : 
   guid == typeof(SByte).GUID.ToString() ?  ADOX.DataTypeEnum.adTinyInt :  
   guid == typeof(Single).GUID.ToString() ?  ADOX.DataTypeEnum.adSingle :
   guid == typeof(string).GUID.ToString() ?  ADOX.DataTypeEnum.adVarChar : 
   guid == typeof(TimeSpan).GUID.ToString() ?  ADOX.DataTypeEnum.adDouble : 
   guid == typeof(UInt16).GUID.ToString() ?  ADOX.DataTypeEnum.adUnsignedSmallInt : 
   guid == typeof(UInt32).GUID.ToString() ?  ADOX.DataTypeEnum.adUnsignedInt : 
   guid == typeof(UInt64).GUID.ToString() ?  ADOX.DataTypeEnum.adUnsignedBigInt :  
   ADOX.DataTypeEnum.adBinary;
  }
}
}

[PROKA]

I am using vb.net and I am having really big problems translating your code :(((((

[PROKA]

ok, It creates the table, but not it's structure though :( ( I mean those columns etc )

 

hope you don't get upset I am using VB.NET ( which 'sux' ) and not C#, and you will still help me :) I'm like a pathetic twin brother but still, I am ur brother

Edited August 11, 2005 by PROKA

[Joe Mamma]

you can compile a c# project, yes???

 

let me make a library project that you can reference. . . give me a second to debug it to see why it doesn't work as expected.

[PROKA]

OK ! Thank you very very very much. I have a little wound on my neck, but with your help I hope I will survive !

[Joe Mamma]

debugged. . . note the changes -

Strings will be defined as memos, hope thats ok?!?

attached cs lib project you can reference.

usage:

ADOXJetXML.DatasetToJet.CopyDatasetSchemaToJetDB(productDS1, "foobar.mdb");

 

 

NB - Deletes the table if it already exists!!!!

using System;
using ADOX;
using ADODB;
using System.Data;
using System.Data.OleDb;
namespace ADOXJetXML
{
/// <summary>
/// Summary description for DatasetToJet.
/// </summary>
public class DatasetToJet
{
 static public void CopyDatasetSchemaToJetDB(DataSet ds, string jetFileName)
 {
  string connstr = string.Format(@"Data Source=""{0}"";Jet OLEDB:Engine Type=5;"+
    @"Provider=""Microsoft.Jet.OLEDB.4.0"";User ID=Admin;",  jetFileName);
  Catalog cat = new CatalogClass();
  if (!System.IO.File.Exists(jetFileName))
   cat.Create(connstr);
  else
  {
   ADODB.Connection conn = new ConnectionClass();
   conn.Open(connstr, "Admin", "",-1);
   cat.ActiveConnection = conn; 
  }
  
  foreach(DataTable table in ds.Tables)
  {
   try
   {
    cat.Tables.Delete(table.TableName);
   }
   catch{}
   ADOX.Table adoxTab = CopyDataTable(table, cat);
   cat.Tables.Append(adoxTab);
  }
 }
 static private ADOX.Table CopyDataTable(DataTable table, Catalog cat)
 {
  ADOX.Table adoxTable = new ADOX.TableClass();
  adoxTable.Name = table.TableName;
  adoxTable.ParentCatalog = cat;
  foreach(System.Data.DataColumn col in table.Columns)
  {
   ADOX.Column adoxCol = new ADOX.ColumnClass();
   adoxCol.ParentCatalog = cat;
   adoxCol.Name = col.ColumnName;
   adoxCol.Type = TranslateDataTypeToADOXDataType(col.DataType);
   if (col.MaxLength >=0 )
    adoxCol.DefinedSize = col.MaxLength;
   adoxTable.Columns.Append(adoxCol, adoxCol.Type, adoxCol.DefinedSize);
  }
  return adoxTable;
 }

 static private ADOX.DataTypeEnum TranslateDataTypeToADOXDataType(Type type)
 {
  string guid = type.GUID.ToString();
  ADOX.DataTypeEnum adoxType = 
   guid == typeof(bool).GUID.ToString() ? ADOX.DataTypeEnum.adBoolean : 
   guid == typeof(byte).GUID.ToString() ? ADOX.DataTypeEnum.adUnsignedTinyInt: 
   guid == typeof(char).GUID.ToString() ? ADOX.DataTypeEnum.adChar : 
   guid == typeof(DateTime).GUID.ToString() ?  ADOX.DataTypeEnum.adDate :  
   guid == typeof(decimal).GUID.ToString() ?  ADOX.DataTypeEnum.adDouble : 
   guid == typeof(double).GUID.ToString() ?  ADOX.DataTypeEnum.adDouble : 
   guid == typeof(Int16).GUID.ToString() ?  ADOX.DataTypeEnum.adSmallInt :  
   guid == typeof(Int32).GUID.ToString() ?  ADOX.DataTypeEnum.adInteger : 
   guid == typeof(Int64).GUID.ToString() ?  ADOX.DataTypeEnum.adBigInt : 
   guid == typeof(SByte).GUID.ToString() ?  ADOX.DataTypeEnum.adTinyInt :  
   guid == typeof(Single).GUID.ToString() ?  ADOX.DataTypeEnum.adSingle :
   guid == typeof(string).GUID.ToString() ?  ADOX.DataTypeEnum.adLongVarWChar : 
   guid == typeof(TimeSpan).GUID.ToString() ?  ADOX.DataTypeEnum.adDouble : 
   guid == typeof(UInt16).GUID.ToString() ?  ADOX.DataTypeEnum.adUnsignedSmallInt : 
   guid == typeof(UInt32).GUID.ToString() ?  ADOX.DataTypeEnum.adUnsignedInt : 
   guid == typeof(UInt64).GUID.ToString() ?  ADOX.DataTypeEnum.adUnsignedBigInt :  
   ADOX.DataTypeEnum.adBinary;
  return adoxType;
  }
}
}

ADOXJetXML.zip

Edited August 11, 2005 by Joe Mamma

[Joe Mamma]

be sure to get the one I just edited as I removed console I/O I inadvertantly left in for debugging.

[PROKA]

Thanks a lot, it worked creating the mdb file correctly, Now I have some trouble transferring the data. I am using "Insert into ... " statements, but I don't know if the data type is text to use 'value' or integer to use the value without the '

enfin ...

thank u ! :)

[Joe Mamma]

God, I love this stuff!!!!

You may run into some problems with datatype conversions, let me know exactly the problem and it should be very easy to fix.

 

replace the contents of DataSetToJet.cs with this code.

Addied method MoveData whic is called in the loop through the datatables immediately after attaching the generated ADOX.Table to the catalog.

 

review the code in MoveData as it calls a method that factories an ADODB.Command

 

note: since the ADODB libraries were already referenced, I am using adodb.command object as the cmd.Execute allows for easy parameterization.

using System;
using ADOX;
using ADODB;
using System.Data;
using System.Data.OleDb;
namespace ADOXJetXML
{
public class DatasetToJet
{
 static public void CopyDatasetSchemaToJetDB(DataSet ds, string jetFileName)
 {
  string connstr = string.Format(@"Data Source=""{0}"";Jet OLEDB:Engine Type=5;"+
    @"Provider=""Microsoft.Jet.OLEDB.4.0"";User ID=Admin;",  jetFileName);
  Catalog cat = new CatalogClass();
  if (!System.IO.File.Exists(jetFileName))
   cat.Create(connstr);
  else
  {
   ADODB.Connection conn = new ConnectionClass();
   conn.Open(connstr, "Admin", "",-1);
   cat.ActiveConnection = conn; 
  }
  
  foreach(DataTable table in ds.Tables)
  {
   try
   {
    cat.Tables.Delete(table.TableName);
   }
   catch{}
   ADOX.Table adoxTab = CopyDataTable(table, cat);
   cat.Tables.Append(adoxTab);
// 
// NEW METHOD CALL
//
   MoveData(adoxTab, table);
  }
 }

 static private ADOX.Table CopyDataTable(DataTable table, Catalog cat)
 {
  ADOX.Table adoxTable = new ADOX.TableClass();
  adoxTable.Name = table.TableName;
  adoxTable.ParentCatalog = cat;
  foreach(System.Data.DataColumn col in table.Columns)
  {
   ADOX.Column adoxCol = new ADOX.ColumnClass();
   adoxCol.ParentCatalog = cat;
   adoxCol.Name = col.ColumnName;
   adoxCol.Type = TranslateDataTypeToADOXDataType(col.DataType);
   adoxCol.Attributes = ADOX.ColumnAttributesEnum.adColNullable;
   if (col.MaxLength >=0 )
    adoxCol.DefinedSize = col.MaxLength;
   adoxTable.Columns.Append(adoxCol, adoxCol.Type, adoxCol.DefinedSize);
  }
  return adoxTable;
 }

 static private ADOX.DataTypeEnum TranslateDataTypeToADOXDataType(Type type)
 {
  string guid = type.GUID.ToString();
  ADOX.DataTypeEnum adoxType = 
   guid == typeof(bool).GUID.ToString() ? ADOX.DataTypeEnum.adBoolean : 
   guid == typeof(byte).GUID.ToString() ? ADOX.DataTypeEnum.adUnsignedTinyInt: 
   guid == typeof(char).GUID.ToString() ? ADOX.DataTypeEnum.adChar : 
   guid == typeof(DateTime).GUID.ToString() ?  ADOX.DataTypeEnum.adDate :  
   guid == typeof(decimal).GUID.ToString() ?  ADOX.DataTypeEnum.adDouble : 
   guid == typeof(double).GUID.ToString() ?  ADOX.DataTypeEnum.adDouble : 
   guid == typeof(Int16).GUID.ToString() ?  ADOX.DataTypeEnum.adSmallInt :  
   guid == typeof(Int32).GUID.ToString() ?  ADOX.DataTypeEnum.adInteger : 
   guid == typeof(Int64).GUID.ToString() ?  ADOX.DataTypeEnum.adBigInt : 
   guid == typeof(SByte).GUID.ToString() ?  ADOX.DataTypeEnum.adTinyInt :  
   guid == typeof(Single).GUID.ToString() ?  ADOX.DataTypeEnum.adSingle :
   guid == typeof(string).GUID.ToString() ?  ADOX.DataTypeEnum.adLongVarWChar : 
   guid == typeof(TimeSpan).GUID.ToString() ?  ADOX.DataTypeEnum.adDouble : 
   guid == typeof(UInt16).GUID.ToString() ?  ADOX.DataTypeEnum.adUnsignedSmallInt : 
   guid == typeof(UInt32).GUID.ToString() ?  ADOX.DataTypeEnum.adUnsignedInt : 
   guid == typeof(UInt64).GUID.ToString() ?  ADOX.DataTypeEnum.adUnsignedBigInt :  
   ADOX.DataTypeEnum.adVarBinary;
  return adoxType;
 }

// 
// NEW METHOD
//
 private static ADODB.Command ADOXTableInsertCommand(ADOX.Table adoxTab)
 {
  ADODB.Command result  = new ADODB.CommandClass();
  object conn =  adoxTab.ParentCatalog.ActiveConnection;
  result.ActiveConnection = (ConnectionClass) conn; 
  result.CommandText = string.Format("INSERT INTO {0} ({1}) values({2}) " , adoxTab.Name, "{0}", "{1}");
  string colNames = string.Empty;
  string colVals = string.Empty;
  for (int i = 0; i < adoxTab.Columns.Count; i++)
  {
   ADOX.Column adoxCol = adoxTab.Columns[i];
   string name = adoxCol.Name;
   ADOX.DataTypeEnum type = adoxCol.Type;
   switch ( type )
   { 
    case ADOX.DataTypeEnum.adVarBinary: break;
    default:
     colNames += ( colNames != string.Empty ? "," : "") + name;
     colVals += (colVals != string.Empty ? "," : "") + "?" ;
     break;
   }
  }
  result.CommandText = string.Format(result.CommandText, colNames, colVals);
  return result;
 }

// 
// NEW METHOD
//
 private static void MoveData(ADOX.Table adoxTab, DataTable aTable)
 {
  object i;
  ADODB.Command cmd = ADOXTableInsertCommand(adoxTab);
  foreach(DataRow row in aTable.Rows)
  {
   object arry = row.ItemArray;
   cmd.Execute(out i, ref arry, 1);
  }
 }
}
}

ADOXJetXML.zip

[JAYUL]

I am getting follwing error

 

"System.Runtime.InteropServices.COMException (0x80040E14): Syntax error in INSERT INTO statement.

at ADODB.CommandClass.Execute(Object& RecordsAffected, Object& Parameters, Int32 Options)

at ADOXJetXML.DatasetToJet.MoveData(Table adoxTab, DataTable aTable)

at ADOXJetXML.DatasetToJet.CopyDatasetSchemaToJetDB(DataSet ds, String jetFileName)

at LMS.bookbuilty.DirectPrint_Ext(String prtName, Boolean DosPrint) in D:\LMS 22 Jan\LMS\bookbuilty.vb:line 3675"

[pmuruaga]

Hi there: I am doing this and I get the following message:

'System.Runtime.InteropServices.COMException -> DataTypes doesn't match in criteria expresion' or something like that, I have my visual studio in spanish.

What could be the problem. It is creating a file and if I open it it have the table and the structure but no records.

Thanks a lot.

[pmuruaga]

This is the stack trace:

at ADODB.CommandClass.Execute(Object& RecordsAffected, Object& Parameters, Int32 Options)

at PruebaDataSetToMDBAccess.DatasetToJet.MoveData(Table adoxTab, DataTable aTable) in C:\Documents and Settings\Pablo Muruaga\Mis documentos\Visual Studio 2008\Projects\PruebaDataSetToMDBAccess\PruebaDataSetToMDBAccess\Util.cs:line 122

at PruebaDataSetToMDBAccess.DatasetToJet.CopyDatasetSchemaToJetDB(DataSet ds, String jetFileName) in C:\Documents and Settings\Pablo Muruaga\Mis documentos\Visual Studio 2008\Projects\PruebaDataSetToMDBAccess\PruebaDataSetToMDBAccess\Util.cs:line 36

at PruebaDataSetToMDBAccess._Default.Button1_Click(Object sender, EventArgs e) in C:\Documents and Settings\Pablo Muruaga\Mis documentos\Visual Studio 2008\Projects\PruebaDataSetToMDBAccess\PruebaDataSetToMDBAccess\Default.aspx.cs:line 89

at System.Web.UI.WebControls.Button.OnClick(EventArgs e)

at System.Web.UI.WebControls.Button.RaisePostBackEvent(String eventArgument)

at System.Web.UI.WebControls.Button.System.Web.UI.IPostBackEventHandler.RaisePostBackEvent(String eventArgument)

at System.Web.UI.Page.RaisePostBackEvent(IPostBackEventHandler sourceControl, String eventArgument)

at System.Web.UI.Page.RaisePostBackEvent(NameValueCollection postData)

at System.Web.UI.Page.ProcessRequestMain(Boolean includeStagesBeforeAsyncPoint, Boolean includeStagesAfterAsyncPoint)

[xonimiro]

Hi there: I am doing this and I get the following message:

'System.Runtime.InteropServices.COMException -> DataTypes doesn't match in criteria expresion' or something like that, I have my visual studio in spanish.

What could be the problem. It is creating a file and if I open it it have the table and the structure but no records.

Thanks a lot.

 

I got the same problem. It was that the order of the columns in the command creation and moving the data was not the same so types mismatched. I changed:

 

private static void MoveData(ADOX.Table adoxTab, DataTable aTable)

{

object i;

ADODB.Command cmd = ADOXTableInsertCommand(adoxTab);

foreach (DataRow row in aTable.Rows)

{

object[] array = new object[aTable.Columns.Count];

for(int c = 0; c < aTable.Columns.Count; c++)

array[c] = row[(string)columnas[c]];

 

object arry = array;

cmd.Execute(out i, ref arry, 1);

}

}

 

columnas is an ArrayList in the same order as when creating the command.

 

Another problem I found is that the first time you create the MDB the move data function fails (something like cannot convert COM object to ADODB.Connection). It is because the connection opened is not 'ADODB.Connection' to solve that I changed:

 

if (!System.IO.File.Exists(jetFileName))

{

cat.Create(connstr);

}

else

{

ADODB.Connection conn = new ConnectionClass();

conn.Open(connstr, "Admin", "", -1);

cat.ActiveConnection = conn;

}

 

to

 

if (!System.IO.File.Exists(jetFileName))

cat.Create(connstr);

ADODB.Connection conn = new ConnectionClass();

conn.Open(connstr, "Admin", "", -1);

cat.ActiveConnection = conn;

 

Hope that helps.

 

Toni, Alicante (Spain)

[LeeDeb]

Hello, it is necessary to save all structure of DB in new (tables, keys, relations, defaults and constraints). The resulted code is excellent, but a variant is needed with relations. Help with my problem.

[penoo]

Hi, Toni and Joe Mamma,

 

Really appreciate your brain sharing. I got to solve the issue that I had to finish by this code provided and corrected by yours.

 

Toni, I have corrected as below in your code as it returned an error on columnas array. Actually these codes are quite difficult to understand from my brain. Just had the same idea but could solve the problem by your hint.

 

//array[c] = row[(string)columnas[c]];

array[c] = row[(string)adoxTab.Columns[c].Name];

 

Hope this also help others looking for the same fucntion.

woojin in shanghai.

[avdeshkataria]

Hello Sir,

I used your code for creating mdb database by using dataset. But Its create only Schema of the tables. But I want both table Schema & Data...

 

Example:

Dataset has 1 Datatable[Temp] with 4 coulmns[A,B,C,D].and Datatable[Temp] having two Rows [(1,2,3,4),(5,6,7,8)].

Now I want mdb database having 1 table with all Data[both Rows].

 

I'm waiting for your reply,

 

Thanks And Regards,

Avdesh

avdeshkataria@gmail.com

[Amyda]

I think that Spire.DataExport can satisfy with you for it is very professional on exporting data to Access. If you want to have a try, you can visit

http://www.e-iceblue.com/Download/download-dataexport-for-net-now.html

to download the free version which it released recently to check if it is really useful.

[sachinc]

Hello avdeshkataria,

 

have you found the solution??

i there is same workaround for me. i need to export all tables with schema and data to mdf.

 

Please let me know.

 

 

Thanks in advance.

 

 

 

Sachinc.

 

 

 

Hello Sir,

I used your code for creating mdb database by using dataset. But Its create only Schema of the tables. But I want both table Schema & Data...

 

Example:

Dataset has 1 Datatable[Temp] with 4 coulmns[A,B,C,D].and Datatable[Temp] having two Rows [(1,2,3,4),(5,6,7,8)].

Now I want mdb database having 1 table with all Data[both Rows].

 

I'm waiting for your reply,

 

Thanks And Regards,

Avdesh

avdeshkataria@gmail.com

[sachinc]

God, I love this stuff!!!!

You may run into some problems with datatype conversions, let me know exactly the problem and it should be very easy to fix.

 

replace the contents of DataSetToJet.cs with this code.

Addied method MoveData whic is called in the loop through the datatables immediately after attaching the generated ADOX.Table to the catalog.

 

review the code in MoveData as it calls a method that factories an ADODB.Command

 

note: since the ADODB libraries were already referenced, I am using adodb.command object as the cmd.Execute allows for easy parameterization.

using System;
using ADOX;
using ADODB;
using System.Data;
using System.Data.OleDb;
namespace ADOXJetXML
{
public class DatasetToJet
{
 static public void CopyDatasetSchemaToJetDB(DataSet ds, string jetFileName)
 {
  string connstr = string.Format(@"Data Source=""{0}"";Jet OLEDB:Engine Type=5;"+
    @"Provider=""Microsoft.Jet.OLEDB.4.0"";User ID=Admin;",  jetFileName);
  Catalog cat = new CatalogClass();
  if (!System.IO.File.Exists(jetFileName))
   cat.Create(connstr);
  else
  {
   ADODB.Connection conn = new ConnectionClass();
   conn.Open(connstr, "Admin", "",-1);
   cat.ActiveConnection = conn; 
  }
  
  foreach(DataTable table in ds.Tables)
  {
   try
   {
    cat.Tables.Delete(table.TableName);
   }
   catch{}
   ADOX.Table adoxTab = CopyDataTable(table, cat);
   cat.Tables.Append(adoxTab);
// 
// NEW METHOD CALL
//
   MoveData(adoxTab, table);
  }
 }

 static private ADOX.Table CopyDataTable(DataTable table, Catalog cat)
 {
  ADOX.Table adoxTable = new ADOX.TableClass();
  adoxTable.Name = table.TableName;
  adoxTable.ParentCatalog = cat;
  foreach(System.Data.DataColumn col in table.Columns)
  {
   ADOX.Column adoxCol = new ADOX.ColumnClass();
   adoxCol.ParentCatalog = cat;
   adoxCol.Name = col.ColumnName;
   adoxCol.Type = TranslateDataTypeToADOXDataType(col.DataType);
   adoxCol.Attributes = ADOX.ColumnAttributesEnum.adColNullable;
   if (col.MaxLength >=0 )
    adoxCol.DefinedSize = col.MaxLength;
   adoxTable.Columns.Append(adoxCol, adoxCol.Type, adoxCol.DefinedSize);
  }
  return adoxTable;
 }

 static private ADOX.DataTypeEnum TranslateDataTypeToADOXDataType(Type type)
 {
  string guid = type.GUID.ToString();
  ADOX.DataTypeEnum adoxType = 
   guid == typeof(bool).GUID.ToString() ? ADOX.DataTypeEnum.adBoolean : 
   guid == typeof(byte).GUID.ToString() ? ADOX.DataTypeEnum.adUnsignedTinyInt: 
   guid == typeof(char).GUID.ToString() ? ADOX.DataTypeEnum.adChar : 
   guid == typeof(DateTime).GUID.ToString() ?  ADOX.DataTypeEnum.adDate :  
   guid == typeof(decimal).GUID.ToString() ?  ADOX.DataTypeEnum.adDouble : 
   guid == typeof(double).GUID.ToString() ?  ADOX.DataTypeEnum.adDouble : 
   guid == typeof(Int16).GUID.ToString() ?  ADOX.DataTypeEnum.adSmallInt :  
   guid == typeof(Int32).GUID.ToString() ?  ADOX.DataTypeEnum.adInteger : 
   guid == typeof(Int64).GUID.ToString() ?  ADOX.DataTypeEnum.adBigInt : 
   guid == typeof(SByte).GUID.ToString() ?  ADOX.DataTypeEnum.adTinyInt :  
   guid == typeof(Single).GUID.ToString() ?  ADOX.DataTypeEnum.adSingle :
   guid == typeof(string).GUID.ToString() ?  ADOX.DataTypeEnum.adLongVarWChar : 
   guid == typeof(TimeSpan).GUID.ToString() ?  ADOX.DataTypeEnum.adDouble : 
   guid == typeof(UInt16).GUID.ToString() ?  ADOX.DataTypeEnum.adUnsignedSmallInt : 
   guid == typeof(UInt32).GUID.ToString() ?  ADOX.DataTypeEnum.adUnsignedInt : 
   guid == typeof(UInt64).GUID.ToString() ?  ADOX.DataTypeEnum.adUnsignedBigInt :  
   ADOX.DataTypeEnum.adVarBinary;
  return adoxType;
 }

// 
// NEW METHOD
//
 private static ADODB.Command ADOXTableInsertCommand(ADOX.Table adoxTab)
 {
  ADODB.Command result  = new ADODB.CommandClass();
  object conn =  adoxTab.ParentCatalog.ActiveConnection;
  result.ActiveConnection = (ConnectionClass) conn; 
  result.CommandText = string.Format("INSERT INTO {0} ({1}) values({2}) " , adoxTab.Name, "{0}", "{1}");
  string colNames = string.Empty;
  string colVals = string.Empty;
  for (int i = 0; i < adoxTab.Columns.Count; i++)
  {
   ADOX.Column adoxCol = adoxTab.Columns[i];
   string name = adoxCol.Name;
   ADOX.DataTypeEnum type = adoxCol.Type;
   switch ( type )
   { 
    case ADOX.DataTypeEnum.adVarBinary: break;
    default:
     colNames += ( colNames != string.Empty ? "," : "") + name;
     colVals += (colVals != string.Empty ? "," : "") + "?" ;
     break;
   }
  }
  result.CommandText = string.Format(result.CommandText, colNames, colVals);
  return result;
 }

// 
// NEW METHOD
//
 private static void MoveData(ADOX.Table adoxTab, DataTable aTable)
 {
  object i;
  ADODB.Command cmd = ADOXTableInsertCommand(adoxTab);
  foreach(DataRow row in aTable.Rows)
  {
   object arry = row.ItemArray;
   cmd.Execute(out i, ref arry, 1);
  }
 }
}
}

Hello Joe Mamma,

 

Greate coding!!

but while running the code i get error in MoveData() function at

 

cmd.Execute(out i, ref arry, 1);

 

error "Data type mismatch in criteria expression."

 

Please help!

[ihtfp69]

First of all, thanks for the code. This saved me loads of time today. I did make some corrections fix the error about the data type being incorrect. It was because the INSERT query was being built incorrectly. Here is my code in case anyone is curious.

 

using System;
using System.Data;
using ADODB;
using ADOX;

namespace ADOXJetXML
{
   /// <summary>
   /// Summary description for DatasetToJet.
   /// </summary>
   public class DatasetToJet
   {

       static public string DeleteDatabase(string jetFileName)
       {
           try
           {
              System.IO.File.Delete(jetFileName);
           }
           catch (Exception eX)
           {
               return eX.Message;
           }

           return string.Empty;
       }

       static public void CopyDatasetSchemaToJetDB(DataSet ds, string jetFileName)
       {
           string connstr = string.Format(@"Data Source=""{0}"";Jet OLEDB:Engine Type=5;" +
             @"Provider=""Microsoft.Jet.OLEDB.4.0"";User ID=Admin;", jetFileName);
           Catalog cat = new CatalogClass();

           if (!System.IO.File.Exists(jetFileName))
               cat.Create(connstr);

           ADODB.Connection conn = new ConnectionClass();
           conn.Open(connstr, "Admin", "", -1);
           cat.ActiveConnection = conn;

           foreach (DataTable table in ds.Tables)
           {
               try
               {
                   cat.Tables.Delete(table.TableName);
               }
               catch { }
               ADOX.Table adoxTab = CopyDataTable(table, cat);
               cat.Tables.Append(adoxTab);

               MoveData(adoxTab, table);
           }
       }

       static private ADOX.Table CopyDataTable(DataTable table, Catalog cat)
       {
           ADOX.Table adoxTable = new ADOX.TableClass();
           adoxTable.Name = table.TableName;
           adoxTable.ParentCatalog = cat;
           foreach (System.Data.DataColumn col in table.Columns)
           {
               ADOX.Column adoxCol = new ADOX.ColumnClass();
               adoxCol.ParentCatalog = cat;
               adoxCol.Name = col.ColumnName;
               adoxCol.Type = TranslateDataTypeToADOXDataType(col.DataType);
               if (col.MaxLength >= 0)
                   adoxCol.DefinedSize = col.MaxLength;
               adoxCol.Attributes = ColumnAttributesEnum.adColNullable;
               adoxTable.Columns.Append(adoxCol, adoxCol.Type, adoxCol.DefinedSize);
           }
           return adoxTable;
       }

       static private ADOX.DataTypeEnum TranslateDataTypeToADOXDataType(Type type)
       {
           string guid = type.GUID.ToString();
           ADOX.DataTypeEnum adoxType =
            guid == typeof(bool).GUID.ToString() ? ADOX.DataTypeEnum.adBoolean :
            guid == typeof(byte).GUID.ToString() ? ADOX.DataTypeEnum.adUnsignedTinyInt :
            guid == typeof(char).GUID.ToString() ? ADOX.DataTypeEnum.adChar :
            guid == typeof(DateTime).GUID.ToString() ? ADOX.DataTypeEnum.adDate :
            guid == typeof(decimal).GUID.ToString() ? ADOX.DataTypeEnum.adDouble :
            guid == typeof(double).GUID.ToString() ? ADOX.DataTypeEnum.adDouble :
            guid == typeof(Int16).GUID.ToString() ? ADOX.DataTypeEnum.adSmallInt :
            guid == typeof(Int32).GUID.ToString() ? ADOX.DataTypeEnum.adInteger :
            guid == typeof(Int64).GUID.ToString() ? ADOX.DataTypeEnum.adBigInt :
            guid == typeof(SByte).GUID.ToString() ? ADOX.DataTypeEnum.adTinyInt :
            guid == typeof(Single).GUID.ToString() ? ADOX.DataTypeEnum.adSingle :
            guid == typeof(string).GUID.ToString() ? ADOX.DataTypeEnum.adVarWChar :
            guid == typeof(TimeSpan).GUID.ToString() ? ADOX.DataTypeEnum.adDouble :
            guid == typeof(UInt16).GUID.ToString() ? ADOX.DataTypeEnum.adUnsignedSmallInt :
            guid == typeof(UInt32).GUID.ToString() ? ADOX.DataTypeEnum.adUnsignedInt :
            guid == typeof(UInt64).GUID.ToString() ? ADOX.DataTypeEnum.adUnsignedBigInt :
            ADOX.DataTypeEnum.adBinary;
           return adoxType;
       }

       private static ADODB.Command ADOXTableInsertCommand(DataTable aTable)
       {
           ADODB.Command result = new ADODB.CommandClass();
           result.CommandText = string.Format("INSERT INTO {0} ({1}) values({2}) ", aTable.TableName, "{0}", "{1}");

           string colNames = string.Empty;
           string colVals = string.Empty;
           for (int i = 0; i < aTable.Columns.Count; i++)
           {
               System.Data.DataColumn adoxCol = aTable.Columns[i];
               string name = adoxCol.ToString();
               ADOX.DataTypeEnum type = TranslateDataTypeToADOXDataType(adoxCol.DataType);
               switch (type)
               {
                   case ADOX.DataTypeEnum.adVarBinary: break;
                   default:
                       colNames += (colNames != string.Empty ? "," : "") + name;
                       colVals += (colVals != string.Empty ? "," : "") + "?";
                       break;
               }
           }
           result.CommandText = string.Format(result.CommandText, colNames, colVals);
           return result;
       }

       private static void MoveData(ADOX.Table adoxTab, DataTable aTable)
       {
           object i;
           ADODB.Command cmd = ADOXTableInsertCommand(aTable);
           cmd.ActiveConnection = (ConnectionClass)adoxTab.ParentCatalog.ActiveConnection;
           foreach (DataRow row in aTable.Rows)
           {
               object arry = row.ItemArray;
               cmd.Execute(out i, ref arry, 1);
           }
       }
   }
}

[ihtfp69]

Hello Joe Mamma,

 

Greate coding!!

but while running the code i get error in MoveData() function at

 

cmd.Execute(out i, ref arry, 1);

 

error "Data type mismatch in criteria expression."

 

Please help!

 

First of all, this saved me loads of time, so thank you very much for the code! :D

 

I found the reason for that error is because the fields in the insert command are being written to the string out of order. Thus the fields and the data do not jive. I have posted my corrected code below.

 

[CS]using System;

using System.Data;

using ADODB;

using ADOX;

 

namespace ADOXJetXML

{

/// <summary>

/// Class used to convert a Dataset to an MS Access database with tables

/// </summary>

public class ADOXmlToMDB

{

 

static public void CopyDatasetSchemaToJetDB(DataSet ds, string jetFileName)

{

string connstr = string.Format(@"Data Source=""{0}"";Jet OLEDB:Engine Type=5;" +

@"Provider=""Microsoft.Jet.OLEDB.4.0"";User ID=Admin;", jetFileName);

Catalog cat = new CatalogClass();

 

if (!System.IO.File.Exists(jetFileName))

cat.Create(connstr);

 

ADODB.Connection conn = new ConnectionClass();

conn.Open(connstr, "Admin", "", -1);

cat.ActiveConnection = conn;

 

foreach (DataTable table in ds.Tables)

{

try

{

cat.Tables.Delete(table.TableName);

}

catch { }

ADOX.Table adoxTab = CopyDataTable(table, cat);

cat.Tables.Append(adoxTab);

 

MoveData(adoxTab, table);

}

 

conn.Close();

}

 

static private ADOX.Table CopyDataTable(DataTable table, Catalog cat)

{

ADOX.Table adoxTable = new ADOX.TableClass();

adoxTable.Name = table.TableName;

adoxTable.ParentCatalog = cat;

foreach (System.Data.DataColumn col in table.Columns)

{

ADOX.Column adoxCol = new ADOX.ColumnClass();

adoxCol.ParentCatalog = cat;

adoxCol.Name = col.ColumnName;

adoxCol.Type = TranslateDataTypeToADOXDataType(col.DataType);

if (col.MaxLength >= 0)

adoxCol.DefinedSize = col.MaxLength;

adoxCol.Attributes = ColumnAttributesEnum.adColNullable;

adoxTable.Columns.Append(adoxCol, adoxCol.Type, adoxCol.DefinedSize);

}

return adoxTable;

}

 

static private ADOX.DataTypeEnum TranslateDataTypeToADOXDataType(Type type)

{

string guid = type.GUID.ToString();

ADOX.DataTypeEnum adoxType =

guid == typeof(bool).GUID.ToString() ? ADOX.DataTypeEnum.adBoolean :

guid == typeof(byte).GUID.ToString() ? ADOX.DataTypeEnum.adUnsignedTinyInt :

guid == typeof(char).GUID.ToString() ? ADOX.DataTypeEnum.adChar :

guid == typeof(DateTime).GUID.ToString() ? ADOX.DataTypeEnum.adDate :

guid == typeof(decimal).GUID.ToString() ? ADOX.DataTypeEnum.adDouble :

guid == typeof(double).GUID.ToString() ? ADOX.DataTypeEnum.adDouble :

guid == typeof(Int16).GUID.ToString() ? ADOX.DataTypeEnum.adSmallInt :

guid == typeof(Int32).GUID.ToString() ? ADOX.DataTypeEnum.adInteger :

guid == typeof(Int64).GUID.ToString() ? ADOX.DataTypeEnum.adBigInt :

guid == typeof(SByte).GUID.ToString() ? ADOX.DataTypeEnum.adTinyInt :

guid == typeof(Single).GUID.ToString() ? ADOX.DataTypeEnum.adSingle :

guid == typeof(string).GUID.ToString() ? ADOX.DataTypeEnum.adVarWChar :

guid == typeof(TimeSpan).GUID.ToString() ? ADOX.DataTypeEnum.adDouble :

guid == typeof(UInt16).GUID.ToString() ? ADOX.DataTypeEnum.adUnsignedSmallInt :

guid == typeof(UInt32).GUID.ToString() ? ADOX.DataTypeEnum.adUnsignedInt :

guid == typeof(UInt64).GUID.ToString() ? ADOX.DataTypeEnum.adUnsignedBigInt :

ADOX.DataTypeEnum.adBinary;

return adoxType;

}

 

private static ADODB.Command ADOXTableInsertCommand(DataTable aTable)

{

ADODB.Command result = new ADODB.CommandClass();

result.CommandText = string.Format("INSERT INTO {0} ({1}) values({2}) ", aTable.TableName, "{0}", "{1}");

 

string colNames = string.Empty;

string colVals = string.Empty;

for (int i = 0; i < aTable.Columns.Count; i++)

{

System.Data.DataColumn adoxCol = aTable.Columns;

string name = adoxCol.ToString();

ADOX.DataTypeEnum type = TranslateDataTypeToADOXDataType(adoxCol.DataType);

switch (type)

{

case ADOX.DataTypeEnum.adVarBinary: break;

default:

colNames += (colNames != string.Empty ? "," : "") + name;

colVals += (colVals != string.Empty ? "," : "") + "?";

break;

}

}

result.CommandText = string.Format(result.CommandText, colNames, colVals);

return result;

}

 

private static void MoveData(ADOX.Table adoxTab, DataTable aTable)

{

object i;

//send the DataTable instead of the ADOX.Table to get the correct field order

ADODB.Command cmd = ADOXTableInsertCommand(aTable);

cmd.ActiveConnection = (ConnectionClass)adoxTab.ParentCatalog.ActiveConnection;

foreach (DataRow row in aTable.Rows)

{

object arry = row.ItemArray;

 

cmd.Execute(out i, ref arry, 1);

}

}

}

}[/CS]

Edited May 17, 2011 by ihtfp69