.NET2.0下用ADOX动态创建ACCESS数据库(C#)
1.ADOX概述:
Microsoft® ActiveX® Data Objects Extensions for Data Definition Language and Security (ADOX) 是对 ADO 对象和编程模型的扩展。ADOX 包括用于模式创建和修改的对象,以及安全性。由于它是基于对象实现模式操作,所以用户可以编写对各种数据源都能有效运行的代码,而与它们原始语法中的差异无关。
ADOX 是核心 ADO 对象的扩展库。它显露的其他对象可用于创建、修改和删除模式对象,如表格和过程。它还包括安全对象,可用于维护用户和组,以及授予和撤消对象的权限。
要通过VS使用 ADOX,需要建立对 ADOX 类型库的引用。在“Add reference”对话框里切换到Com页面,选择“Microsoft ADO Ext. 2.8 for DDL and Security”,然后点击OK。在文件的开头using ADOX名字空间。
2.ADOX的对象模型:
Catalog:
使用如下语句可以创建数据库:
// 创建数据库字符串
string dbName = "D:\DataBase\FirstTable.mdb";
ADOX.CatalogClass catlog = new ADOX.CatalogClass();
//或者 ADOX.Catalog catlog = new ADOX.Catalog(); CatalogClass 是类 ,Catalog是接口 。
catlog .Create("Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" + dbName + ";"+"Jet OLEDB:Engine Type=5");
string dbName = "D:\DataBase\FirstTable.mdb";
ADOX.CatalogClass catlog = new ADOX.CatalogClass();
//或者 ADOX.Catalog catlog = new ADOX.Catalog(); CatalogClass 是类 ,Catalog是接口 。
catlog .Create("Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" + dbName + ";"+"Jet OLEDB:Engine Type=5");
Table对象包括列、索引和关键字的数据库表:
我们可以如下创建表:
ADOX.TableClass tbl = new ADOX.TableClass(); // 或者 ADOX.Table tbl = new ADOX.Table();
tbl.ParentCatalog = catlog; //数据库名
tbl.Name = "MyTable";
tbl.ParentCatalog = catlog; //数据库名
tbl.Name = "MyTable";
Table的属性:
Columns 对象 :
我们可以用以下语句创建列:
ADOX.ColumnClass FirstCol = new ADOX.ColumnClass();
FirstCol.ParentCatalog = catlog;
FirstCol.Type = ADOX.DataTypeEnum.adInteger;
FirstCol.Name = "StuID";
FirstCol.Properties["Jet OLEDB:Allow Zero Length"].Value = false;
FirstCol.Properties["AutoIncrement"].Value = true;
tbl.Columns.Append(FirstCol, ADOX.DataTypeEnum.adInteger, 0);
Columns 属性:
3.完整的示例:
using System;
using System.Collections.Generic;
using System.Text;
using ADOX;
namespace ADOXCreateTable
{
class Program
{
static void Main(string[] args)
{
string dbName = "D:\DataBase\FirstCatalog.mdb";
ADOX.CatalogClass catlog = new ADOX.CatalogClass();
catlog.Create("Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" + dbName + ";"+"Jet OLEDB:Engine Type=5");
ADOX.TableClass table = new ADOX.TableClass();
table.ParentCatalog = catlog;
table.Name = "FirstTable";
//StuId Column(AutoIncrement )
ADOX.ColumnClass col1 = new ADOX.ColumnClass();
col1.ParentCatalog = catlog;
col1.Type = ADOX.DataTypeEnum.adInteger;
col1.Name = "StuId";
col1.Properties["Jet OLEDB:Allow Zero Length"].Value = false;
col1.Properties["AutoIncrement"].Value = true;
//Name Column
ADOX.ColumnClass col2 = new ADOX.ColumnClass();
col2.ParentCatalog = catlog;
col2.Name = "StuName";
col2.Properties["Jet OLEDB:Allow Zero Length"].Value = false;
//Age Column
ADOX.ColumnClass col3 = new ADOX.ColumnClass();
col3.ParentCatalog = catlog;
col3.Name = "Stuage";
col3.Type = DataTypeEnum.adDouble;
col3.Properties["Jet OLEDB:Allow Zero Length"].Value = false;
// Primary
table.Keys.Append("PrimaryKey", ADOX.KeyTypeEnum.adKeyPrimary, "StuId", "", "");
table.Columns.Append(col1, ADOX.DataTypeEnum.adInteger, 0);
table.Columns.Append(col3, ADOX.DataTypeEnum.adDouble, 666);
table.Columns.Append(col2, ADOX.DataTypeEnum.adVarChar, 25);
catlog.Tables.Append(table);
System.Runtime.InteropServices.Marshal.ReleaseComObject(table);
System.Runtime.InteropServices.Marshal.ReleaseComObject(catlog);
table = null;
catlog = null;
GC.WaitForPendingFinalizers();
GC.Collect();
}
}
}
using System.Collections.Generic;
using System.Text;
using ADOX;
namespace ADOXCreateTable
{
class Program
{
static void Main(string[] args)
{
string dbName = "D:\DataBase\FirstCatalog.mdb";
ADOX.CatalogClass catlog = new ADOX.CatalogClass();
catlog.Create("Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" + dbName + ";"+"Jet OLEDB:Engine Type=5");
ADOX.TableClass table = new ADOX.TableClass();
table.ParentCatalog = catlog;
table.Name = "FirstTable";
//StuId Column(AutoIncrement )
ADOX.ColumnClass col1 = new ADOX.ColumnClass();
col1.ParentCatalog = catlog;
col1.Type = ADOX.DataTypeEnum.adInteger;
col1.Name = "StuId";
col1.Properties["Jet OLEDB:Allow Zero Length"].Value = false;
col1.Properties["AutoIncrement"].Value = true;
//Name Column
ADOX.ColumnClass col2 = new ADOX.ColumnClass();
col2.ParentCatalog = catlog;
col2.Name = "StuName";
col2.Properties["Jet OLEDB:Allow Zero Length"].Value = false;
//Age Column
ADOX.ColumnClass col3 = new ADOX.ColumnClass();
col3.ParentCatalog = catlog;
col3.Name = "Stuage";
col3.Type = DataTypeEnum.adDouble;
col3.Properties["Jet OLEDB:Allow Zero Length"].Value = false;
// Primary
table.Keys.Append("PrimaryKey", ADOX.KeyTypeEnum.adKeyPrimary, "StuId", "", "");
table.Columns.Append(col1, ADOX.DataTypeEnum.adInteger, 0);
table.Columns.Append(col3, ADOX.DataTypeEnum.adDouble, 666);
table.Columns.Append(col2, ADOX.DataTypeEnum.adVarChar, 25);
catlog.Tables.Append(table);
System.Runtime.InteropServices.Marshal.ReleaseComObject(table);
System.Runtime.InteropServices.Marshal.ReleaseComObject(catlog);
table = null;
catlog = null;
GC.WaitForPendingFinalizers();
GC.Collect();
}
}
}
4. 结束语
到次一个完整的Access 数据库创建完成了,当然你还可以用ADOX创建SQL数据库 ,创建存储过程。原代码:Source