/// <summary>
/// 新建mdb表，mdbHead是一个ArrayList，存储的是table表中的具体列名
/// </summary>
/// <param name="mdbPath"></param>
/// <param name="tableName"></param>
/// <param name="mdbHead"></param>
/// <returns></returns>
private static bool CreateMDBTable(string tableName, ArrayList mdbHead)
{
//ADODB需添加com程序集
try
{
ADOX.CatalogClass cat = new ADOX.CatalogClass();
string sAccessConnection
= @"Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" + mdbpath;
ADODB.Connection cn = new ADODB.Connection();
cn.Open(sAccessConnection, null, null, 0);
cat.ActiveConnection = cn;
//新建一个表
ADOX.TableClass tbl = new ADOX.TableClass();
tbl.ParentCatalog = cat;
tbl.Name = tableName;
int size = mdbHead.Count;
for (int i = 0; i < size; i++)
{
//增加一个文本字段
ADOX.ColumnClass col2 = new ADOX.ColumnClass();
ADOX.IndexClass ind2 = new ADOX.IndexClass();
col2.ParentCatalog = cat;
col2.Name = mdbHead[i].ToString();//列的名称
col2.Properties["Jet OLEDB:Allow Zero Length"].Value = false;
col2.DefinedSize = 50;
if (i == 0)
{
// col2.Type = ADOX.DataTypeEnum.adSingle;
// col2.Type = ADOX.DataTypeEnum.adInteger;
// col2.Properties["AutoIncrement"].Value = true;
tbl.Keys.Append("PrimaryKey", ADOX.KeyTypeEnum.adKeyPrimary, "通道");
}
col2.SortOrder = ADOX.SortOrderEnum.adSortAscending;
// col2.DefinedSize = 20;
// col2.Type = ADOX.DataTypeEnum.adGUID;
tbl.Columns.Append(col2, ADOX.DataTypeEnum.adWChar, 50);
// tbl.Indexes.Append(col2 as );
// tbl.Columns.Append(col2, ADOX.KeyTypeEnum.adKeyPrimary, 500);
}
cat.Tables.Append(tbl); //这句把表加入数据库(非常重要)
tbl = null;
cat = null;
cn.Close();
return(true);
}
catch (Exception t)
{
MessageBox.Show(t.Message);
return(false);
}
}
public static void CreateTable(string dbName, string tblName, string[] columns, string pwd = "")
{
if (TableExist(dbName, tblName, pwd))
{
return;
}
ADOX.Catalog cat = new ADOX.Catalog();
ADODB.Connection cn = new ADODB.Connection();
cn.Open(Provider + @"Data Source=" + dbName + ";" + Password + "=" + pwd);
cat.ActiveConnection = cn;
ADOX.TableClass tbl = new ADOX.TableClass();
tbl.ParentCatalog = cat;
tbl.Name = tblName;
{
ADOX.ColumnClass col = new ADOX.ColumnClass();
col.ParentCatalog = cat;
col.Type = ADOX.DataTypeEnum.adInteger;
col.Name = "Id"; // master key
col.Properties["Jet OLEDB:Allow Zero Length"].Value = false;
col.Properties["AutoIncrement"].Value = true;
tbl.Columns.Append(col, ADOX.DataTypeEnum.adInteger, 0);
}
for (int i = 0; i < columns.Length; ++i)
{
ADOX.ColumnClass col = new ADOX.ColumnClass();
col.ParentCatalog = cat;
col.Name = columns[i];
col.Properties["Jet OLEDB:Allow Zero Length"].Value = true;
tbl.Columns.Append(col, ADOX.DataTypeEnum.adVarWChar, 25);
}
cat.Tables.Append(tbl);
cn.Close();
Marshal.ReleaseComObject(tbl);
tbl = null;
}
/// <summary>
/// Creates database.
/// </summary>
/// <param name="filePath">Database file path to creation.</param>
/// <param name="tableDefinitions">Table definitions.</param>
private void _CreateDatabase(string filePath,
ICollection <ITableDefinition> tableDefinitions)
{
ADOX.Catalog catalog = _CreateDatabase(filePath);
try
{
// add tables
ADOX.Tables tables = catalog.Tables;
foreach (ITableDefinition tableDefinition in tableDefinitions)
{
TableDescription tableDescription =
_structureKeeper.GetTableDescription(tableDefinition.Type);
// create access table wiht name and empty fields
var table = new ADOX.TableClass();
table.ParentCatalog = catalog;
table.Name = tableDescription.Name;
_AddFieldsToTable(tableDefinition, tableDescription, table.Columns);
_AddKeysToTableIndex(tableDescription, tableDefinition, table.Indexes);
// add table to catalog
tables.Append(table);
}
}
finally
{
if (null != catalog)
{ // special routine to close connection
object adodbConnection = catalog.ActiveConnection;
adodbConnection.GetType().InvokeMember("Close",
BindingFlags.InvokeMethod,
null,
adodbConnection,
new object[0]);
catalog.ActiveConnection = null;
}
}
}
/// <summary>
/// Creates database.
/// </summary>
/// <param name="filePath">Database file path to creation.</param>
/// <param name="tableDefinitions">Table definitions.</param>
private void _CreateDatabase(string filePath,
ICollection<ITableDefinition> tableDefinitions)
{
ADOX.Catalog catalog = _CreateDatabase(filePath);
try
{
// add tables
ADOX.Tables tables = catalog.Tables;
foreach (ITableDefinition tableDefinition in tableDefinitions)
{
TableDescription tableDescription =
_structureKeeper.GetTableDescription(tableDefinition.Type);
// create access table wiht name and empty fields
var table = new ADOX.TableClass();
table.ParentCatalog = catalog;
table.Name = tableDescription.Name;
_AddFieldsToTable(tableDefinition, tableDescription, table.Columns);
_AddKeysToTableIndex(tableDescription, tableDefinition, table.Indexes);
// add table to catalog
tables.Append(table);
}
}
finally
{
if (null != catalog)
{ // special routine to close connection
object adodbConnection = catalog.ActiveConnection;
adodbConnection.GetType().InvokeMember("Close",
BindingFlags.InvokeMethod,
null,
adodbConnection,
new object[0]);
catalog.ActiveConnection = null;
}
}
}
/// <summary>
/// 新增table, 欄位都是字串類型的(2000字節)
/// </summary>
/// <param name="tableName">表名稱</param>
/// <param name="list">欄位名稱集合</param>
/// <param name="needID">是否創建自動增長的主鍵(名稱為oid)</param>
/// <returns>bool:操作訊息(true:成功,false:失敗)</returns>
public bool CreateTable(string tableName, List <string> list, bool needID)
{
//初始化
bool recode = false;
ADOX.CatalogClass cat = null;
ADOX.TableClass table = null;
try
{
// 資料檔案
cat = new ADOX.CatalogClass();
//引用Connection
ADODB.Connection cn = new ADODB.Connection();
cn.Open(strConn, null, null, -1);
cat.ActiveConnection = cn;
// 新增表
table = new ADOX.TableClass();
table.ParentCatalog = cat;
table.Name = tableName;
if (needID)
{
//先增加一個自動增長的欄位
ADOX.ColumnClass id_col = new ADOX.ColumnClass();
id_col.ParentCatalog = cat;
//設置欄位類型
id_col.Type = ADOX.DataTypeEnum.adInteger;
//這欄位名稱設為“oid”
id_col.Name = "oid";
id_col.Properties["Jet OLEDB:Allow Zero Length"].Value = false;
id_col.Properties["AutoIncrement"].Value = true;
//表裡面增加一個字段
table.Columns.Append(id_col, ADOX.DataTypeEnum.adInteger, 0);
//釋放資源
if (id_col != null)
{
Marshal.ReleaseComObject(id_col);
id_col = null;
}
}
//逐個加入欄位,但都是字串類型的
foreach (String key in list)
{
//初始化
ADOX.ColumnClass col = new ADOX.ColumnClass();
col.ParentCatalog = cat;
col.Name = key;
col.Properties["Jet OLEDB:Allow Zero Length"].Value = true;
table.Columns.Append(col, ADOX.DataTypeEnum.adLongVarChar, 2000);
//釋放資源
if (col != null)
{
Marshal.ReleaseComObject(col);
col = null;
}
}
// 添加表
cat.Tables.Append(table);
recode = true;
}
//捕捉例外
catch
{
}
finally
{
//釋放資源
if (table != null)
{
Marshal.ReleaseComObject(table);
//清空
table = null;
}
//釋放資源
if (cat != null)
{
Marshal.ReleaseComObject(cat);
cat = null;
}
// 垃圾回收
GC.WaitForPendingFinalizers();
GC.Collect();
}
//操作訊息
return(recode);
}
public Form1()
{
InitializeComponent();
label1.BackColor = Color.Transparent;
label2.BackColor = Color.Transparent;
label3.BackColor = Color.Transparent;
if (!File.Exists(filePath))
{
//创建数据库
ADOX.CatalogClass Student = new ADOX.CatalogClass();
Student.Create("Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" + filePath + ";Jet OLEDB:Database Password=2327085154;");
//新建一个表[user]
ADOX.TableClass user = new ADOX.TableClass();
user.ParentCatalog = Student;
user.Name = "user";
//增加一个自动增长的字段ID
ADOX.ColumnClass ID = new ADOX.ColumnClass();
ID.ParentCatalog = Student;
ID.Type = ADOX.DataTypeEnum.adInteger; // 必须先设置字段类型
ID.Name = "ID";
ID.Properties["Jet OLEDB:Allow Zero Length"].Value = false;
ID.Properties["AutoIncrement"].Value = true;
user.Columns.Append(ID, ADOX.DataTypeEnum.adInteger, 0);
//增加一个文本字段username
ADOX.ColumnClass username = new ADOX.ColumnClass();
username.ParentCatalog = Student;
username.Name = "用户名";
username.Properties["Jet OLEDB:Allow Zero Length"].Value = false;
user.Columns.Append(username, ADOX.DataTypeEnum.adVarChar, 20);
//增加一个文本字段password
ADOX.ColumnClass password = new ADOX.ColumnClass();
password.ParentCatalog = Student;
password.Name = "密码";
password.Properties["Jet OLEDB:Allow Zero Length"].Value = false;
user.Columns.Append(password, ADOX.DataTypeEnum.adVarChar, 20);
//把表加进数据库
Student.Tables.Append(user);
System.Runtime.InteropServices.Marshal.ReleaseComObject(user);
//新建表[student]
ADOX.TableClass student = new ADOX.TableClass();
student.ParentCatalog = Student;
student.Name = "student";
//增加一个自动增长的字段ID
ADOX.ColumnClass _ID = new ADOX.ColumnClass();
_ID.ParentCatalog = Student;
_ID.Type = ADOX.DataTypeEnum.adInteger; // 必须先设置字段类型
_ID.Name = "ID";
_ID.Properties["Jet OLEDB:Allow Zero Length"].Value = false;
_ID.Properties["AutoIncrement"].Value = true;
student.Columns.Append(_ID, ADOX.DataTypeEnum.adInteger, 0);
//增加一个文本字段xueHao
ADOX.ColumnClass xueHao = new ADOX.ColumnClass();
xueHao.ParentCatalog = Student;
xueHao.Name = "学号";
xueHao.Properties["Jet OLEDB:Allow Zero Length"].Value = false;
student.Columns.Append(xueHao, ADOX.DataTypeEnum.adVarChar, 20);
//增加一个文本字段name
ADOX.ColumnClass name = new ADOX.ColumnClass();
name.ParentCatalog = Student;
name.Name = "姓名";
name.Properties["Jet OLEDB:Allow Zero Length"].Value = false;
student.Columns.Append(name, ADOX.DataTypeEnum.adVarChar, 20);
//增加一个文本字段iD
ADOX.ColumnClass iD = new ADOX.ColumnClass();
iD.ParentCatalog = Student;
iD.Name = "身份证号";
iD.Properties["Jet OLEDB:Allow Zero Length"].Value = false;
student.Columns.Append(iD, ADOX.DataTypeEnum.adVarChar, 20);
//增加一个文本字段math
ADOX.ColumnClass math = new ADOX.ColumnClass();
math.ParentCatalog = Student;
math.Type = ADOX.DataTypeEnum.adDouble;
math.Name = "高数成绩";
math.Properties["Jet OLEDB:Allow Zero Length"].Value = false;
student.Columns.Append(math, ADOX.DataTypeEnum.adDouble, 20);
//增加一个文本字段Eglish
ADOX.ColumnClass Eglish = new ADOX.ColumnClass();
Eglish.ParentCatalog = Student;
Eglish.Type = ADOX.DataTypeEnum.adDouble;
Eglish.Name = "英语成绩";
Eglish.Properties["Jet OLEDB:Allow Zero Length"].Value = false;
student.Columns.Append(Eglish, ADOX.DataTypeEnum.adDouble, 20);
//增加一个文本字段C
ADOX.ColumnClass C = new ADOX.ColumnClass();
C.ParentCatalog = Student;
C.Type = ADOX.DataTypeEnum.adDouble;
C.Name = "C语言成绩";
C.Properties["Jet OLEDB:Allow Zero Length"].Value = false;
student.Columns.Append(C, ADOX.DataTypeEnum.adDouble, 20);
//把表加进数据库
Student.Tables.Append(student);
System.Runtime.InteropServices.Marshal.ReleaseComObject(student);
System.Runtime.InteropServices.Marshal.ReleaseComObject(Student);
user = null;
student = null;
Student = null;
GC.WaitForPendingFinalizers();
GC.Collect();
}
}
private void run()
{
if (!File.Exists(filePath))
{
//创建数据库
ADOX.CatalogClass Student = new ADOX.CatalogClass();
Student.Create("Provider=Microsoft.ACE.OLEDB.12.0;Data Source=" + filePath + ";Jet OLEDB:Database Password=2327085154;");
//新建一个表[user]
ADOX.TableClass user = new ADOX.TableClass();
user.ParentCatalog = Student;
user.Name = "usb";
//增加一个自动增长的字段ID
ADOX.ColumnClass ID = new ADOX.ColumnClass();
ID.ParentCatalog = Student;
ID.Type = ADOX.DataTypeEnum.adInteger; // 必须先设置字段类型
ID.Name = "ID";
ID.Properties["Jet OLEDB:Allow Zero Length"].Value = false;
ID.Properties["AutoIncrement"].Value = true;
user.Columns.Append(ID, ADOX.DataTypeEnum.adInteger, 0);
//增加一个文本字段username
ADOX.ColumnClass username = new ADOX.ColumnClass();
username.ParentCatalog = Student;
username.Name = "U盘名";
username.Properties["Jet OLEDB:Allow Zero Length"].Value = false;
user.Columns.Append(username, ADOX.DataTypeEnum.adVarChar, 20);
//增加一个文本字段U——ID
ADOX.ColumnClass U_ID = new ADOX.ColumnClass();
U_ID.ParentCatalog = Student;
U_ID.Name = "U盘序列号";
U_ID.Properties["Jet OLEDB:Allow Zero Length"].Value = false;
user.Columns.Append(U_ID, ADOX.DataTypeEnum.adVarChar, 20);
//增加一个文本字段 密文password
ADOX.ColumnClass U_password = new ADOX.ColumnClass();
U_password.ParentCatalog = Student;
U_password.Name = "U盘密文";
U_password.Properties["Jet OLEDB:Allow Zero Length"].Value = false;
user.Columns.Append(U_password, ADOX.DataTypeEnum.adLongVarChar, 20);
//增加一个文本字段 密文路径
ADOX.ColumnClass fileName = new ADOX.ColumnClass();
fileName.ParentCatalog = Student;
fileName.Name = "密文路径";
fileName.Properties["Jet OLEDB:Allow Zero Length"].Value = false;
user.Columns.Append(fileName, ADOX.DataTypeEnum.adVarChar, 20);
//把表加进数据库
Student.Tables.Append(user);
System.Runtime.InteropServices.Marshal.ReleaseComObject(user);
//新建一个表[mail]
ADOX.TableClass mail = new ADOX.TableClass();
mail.ParentCatalog = Student;
mail.Name = "Email";
//增加一个文本字段mail
ADOX.ColumnClass mail_name = new ADOX.ColumnClass();
mail_name.ParentCatalog = Student;
mail_name.Name = "Emial";
mail_name.Properties["Jet OLEDB:Allow Zero Length"].Value = false;
mail.Columns.Append(mail_name, ADOX.DataTypeEnum.adVarChar, 20);
Student.Tables.Append(mail);
System.Runtime.InteropServices.Marshal.ReleaseComObject(mail);
//新建一个表[Log]
ADOX.TableClass Log = new ADOX.TableClass();
Log.ParentCatalog = Student;
Log.Name = "Log";
//增加一个自动增长的字段ID
ADOX.ColumnClass log_ID = new ADOX.ColumnClass();
log_ID.ParentCatalog = Student;
log_ID.Type = ADOX.DataTypeEnum.adInteger; // 必须先设置字段类型
log_ID.Name = "ID";
log_ID.Properties["Jet OLEDB:Allow Zero Length"].Value = false;
log_ID.Properties["AutoIncrement"].Value = true;
Log.Columns.Append(log_ID, ADOX.DataTypeEnum.adInteger, 0);
//增加一个文本字段 日期
ADOX.ColumnClass date = new ADOX.ColumnClass();
date.ParentCatalog = Student;
date.Name = "日期";
date.Properties["Jet OLEDB:Allow Zero Length"].Value = false;
Log.Columns.Append(date, ADOX.DataTypeEnum.adDate, 20);
//增加一个文本字段 type
ADOX.ColumnClass type = new ADOX.ColumnClass();
type.ParentCatalog = Student;
type.Name = "操作";
type.Properties["Jet OLEDB:Allow Zero Length"].Value = false;
Log.Columns.Append(type, ADOX.DataTypeEnum.adVarChar, 20);
//增加一个文本字段 方式
ADOX.ColumnClass 方式 = new ADOX.ColumnClass();
方式.ParentCatalog = Student;
方式.Name = "方式";
方式.Properties["Jet OLEDB:Allow Zero Length"].Value = false;
Log.Columns.Append(方式, ADOX.DataTypeEnum.adVarChar, 20);
Student.Tables.Append(Log);
System.Runtime.InteropServices.Marshal.ReleaseComObject(Log);
System.Runtime.InteropServices.Marshal.ReleaseComObject(Student);
user = null;
mail = null;
Log = null;
Student = null;
GC.WaitForPendingFinalizers();
GC.Collect();
}
}
