Tuesday, April 14, 2009

How to: Create Database Using C#

In this article I will show you How to Create Database and Tables at run time using C#:

I’ll Use ADOX Component to Create Database and Tables.

First Create Windows Application Using C#.

In Solution Explorer > References > Right Click > Add Reference
In Com Section Select (Microsoft ADO Ext. 2.8 for DDL and Security) then press Ok.

using ADOX;

In Form Load Event Put the Following Code:

Catalog catlog=new Catalog();
string tempStr;
string fileName = @"C:\Sample.mdb";


//Create Connection String
tempStr = "Provider=Microsoft.Jet.OLEDB.4.0;";
tempStr += "Data Source=" + fileName + ";Jet OLEDB:Engine Type=5";

//Create Database
catlog.Create(tempStr);

//Create Defects Table
Table tblDefects=new Table();
tblDefects.Name = "Defects";

//Add Table Cloumns
tblDefects.Columns.Append("Date", DataTypeEnum.adVarWChar, 50);
tblDefects.Columns.Append("Time", DataTypeEnum.adVarWChar, 50);
tblDefects.Columns.Append("Defect-Code", DataTypeEnum.adVarWChar, 50);
tblDefects.Columns.Append("Start-Finish", DataTypeEnum.adVarWChar, 50);
tblDefects.Columns.Append("ClockStart", DataTypeEnum.adVarWChar, 50);
tblDefects.Columns.Append("ClockEnd", DataTypeEnum.adVarWChar, 50);
tblDefects.Columns.Append("Pilot", DataTypeEnum.adVarWChar, 50);
tblDefects.Columns.Append("Logger", DataTypeEnum.adVarWChar, 50);
tblDefects.Columns.Append("Payout", DataTypeEnum.adVarWChar, 50);
tblDefects.Columns.Append("Lat", DataTypeEnum.adVarWChar, 50);
tblDefects.Columns.Append("Long", DataTypeEnum.adVarWChar, 50);
tblDefects.Columns.Append("Depth", DataTypeEnum.adVarWChar, 50);
tblDefects.Columns.Append("Comment", DataTypeEnum.adVarWChar, 250);
tblDefects.Columns.Append("Pics", DataTypeEnum.adVarWChar, 50);

//Append Tables into Database
catlog.Tables.Append(tblDefects);

//Create Log Table
Table tblLog=new Table();
tblLog.Name = "Log";

//Add Table Cloumns
tblLog.Columns.Append("Date", DataTypeEnum.adVarWChar, 50);
tblLog.Columns.Append("Time", DataTypeEnum.adVarWChar, 50);
tblLog.Columns.Append("Pilot", DataTypeEnum.adVarWChar, 50);
tblLog.Columns.Append("Logger", DataTypeEnum.adVarWChar, 50);
tblLog.Columns.Append("Payout", DataTypeEnum.adVarWChar, 50);
tblLog.Columns.Append("Lat", DataTypeEnum.adVarWChar, 50);
tblLog.Columns.Append("Long", DataTypeEnum.adVarWChar, 50);
tblLog.Columns.Append("Depth", DataTypeEnum.adVarWChar, 50);

//Append Tables into Database
catlog.Tables.Append(tblLog);

//release the com objects properly and in the proper order
System.Runtime.InteropServices.Marshal.FinalReleaseComObject(tblDefects);
System.Runtime.InteropServices.Marshal.FinalReleaseComObject(tblLog);
System.Runtime.InteropServices.Marshal.FinalReleaseComObject(catlog.Tables);
System.Runtime.InteropServices.Marshal.FinalReleaseComObject(catlog.ActiveConnection);
System.Runtime.InteropServices.Marshal.FinalReleaseComObject(catlog);