private void CreateTestDB_Click(object sender, EventArgs e)
{
string dir = AppDomain.CurrentDomain.BaseDirectory;
string dbName = dir + "test.mdb";
ADOX.CatalogClass catlog = new ADOX.CatalogClass();
catlog.Create("Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" + dbName + ";");
ADOX.TableClass tableClass = new ADOX.TableClass();
tableClass.ParentCatalog = catlog;
tableClass.Name = "TestTable";
ADOX.ColumnClass columnID = new ADOX.ColumnClass();
columnID.ParentCatalog = catlog;
columnID.Type = ADOX.DataTypeEnum.adInteger;
columnID.Name = "Id";
columnID.Properties["Jet OLEDB:Allow Zero Length"].Value = false;
columnID.Properties["AutoIncrement"].Value = true;
tableClass.Columns.Append(columnID, ADOX.DataTypeEnum.adInteger, 0);
ADOX.ColumnClass columnName = addCol(catlog, "Name");
tableClass.Columns.Append(columnName, ADOX.DataTypeEnum.adVarChar, 30);
catlog.Tables.Append(tableClass);
MessageBox.Show("Successful");
tableClass = null;
catlog = null;
}
/// <summary>
///
/// </summary>
/// <param name="colName"></param>
/// <param name="catalog"></param>
/// <param name="dataType"></param>
/// <returns></returns>
ADOX.ColumnClass tableField(string colName, ADOX.CatalogClass catalog, ADOX.DataTypeEnum dataType)
{
ADOX.ColumnClass column = new ADOX.ColumnClass();
column.Name = colName; // The name of the column
column.ParentCatalog = catalog;
column.Type = dataType;
return(column);
}
private ADOX.ColumnClass addCol(ADOX.CatalogClass cat, string colName)
{
ADOX.ColumnClass col = new ADOX.ColumnClass();
col.ParentCatalog = cat;
// col.Type = ADOX.DataTypeEnum.adInteger; // 必须先设置字段类型
col.Name = colName;
col.Properties["Jet OLEDB:Allow Zero Length"].Value = false;
return(col);
}
/// <summary>
///
/// </summary>
/// <param name="colName"></param>
/// <param name="catalog"></param>
/// <returns></returns>
ADOX.ColumnClass tableIDField(string colName, ADOX.CatalogClass catalog)
{
ADOX.ColumnClass column = new ADOX.ColumnClass();
column.Name = colName; // The name of the column
column.ParentCatalog = catalog;
column.Type = ADOX.DataTypeEnum.adInteger; //Indicates a four byte signed integer.
column.Properties["AutoIncrement"].Value = true; //Enable the auto increment property for this column.
return(column);
}
static void Main(string[] args)
{
ADOX.CatalogClass cat = new ADOX.CatalogClass();
cat.Create("Provider=Microsoft.ACE.OLEDB.12.0;Data Source=C:\myFolder\myAccess2007file.accdb;Persist
Security Info=False;");
Console.WriteLine("Database Created Successfully");
cat = null;
}
public static void Main(string [] args)
{
ADOX.CatalogClass cat = new ADOX.CatalogClass();
string create =
@"Provider=Microsoft.Jet.OLEDB.4.0;Data
Source=C:\BlankAccessDB\MyAccessDBCreatedFromCsharp.mdb;" +
"Jet OLEDB:Engine Type=5";
cat.Create(create);
cat = null;
}
private ADOX.Table CreateGroupTable(ADOX.CatalogClass cat, ADOX.Table parentTable, MappingGroup group, bool createIndex)
{
var newTable = CreateTable(cat, parentTable, group.TableName, group.Column, createIndex);
foreach (var childGroup in group.Group)
{
int index = group.Group.IndexOf(childGroup); // MDB has maximum of 32 indexes per table
CreateGroupTable(cat, newTable, childGroup, index < 31);
}
return(newTable);
}
private void Form1_Load(object sender, EventArgs e)
{
string str = System.Environment.CurrentDirectory;
string dbName1 = str + "\\StudentManager.mdb";//注意扩展名必须为mdb,否则不能插入表
string dbName = @dbName1;
ADOX.CatalogClass cat = new ADOX.CatalogClass();
try {
cat.Create("Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" + dbName);
}
catch (System.Runtime.InteropServices.COMException) {
}
ADODB.Connection cn = new ADODB.Connection();
cn.Open("Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" + dbName, null, null, -1);
cat.ActiveConnection = cn;
//新建表
ADOX.Table table = new ADOX.Table();
table.Name = "students";
ADOX.Column column = new ADOX.Column();
column.ParentCatalog = cat;
column.Type = ADOX.DataTypeEnum.adVarWChar; // 必须先设置字段类型
column.Name = "ID";
column.DefinedSize = 50;
column.Properties["AutoIncrement"].Value = true;
table.Columns.Append(column, DataTypeEnum.adVarWChar, 50);
//设置主键
table.Keys.Append("PrimaryKey", ADOX.KeyTypeEnum.adKeyPrimary, "ID", "", "");
table.Columns.Append("Name", DataTypeEnum.adVarWChar, 50);
table.Columns.Append("Sex", DataTypeEnum.adVarWChar, 50);
table.Columns.Append("Class", DataTypeEnum.adVarWChar, 50);
table.Columns.Append("Email", DataTypeEnum.adVarWChar, 50);
table.Columns.Append("Chinese", DataTypeEnum.adVarWChar, 50);
table.Columns.Append("Math", DataTypeEnum.adVarWChar, 50);
table.Columns.Append("English", DataTypeEnum.adVarWChar, 50);
try
{
cat.Tables.Append(table);
}
catch (Exception ex)
{
Console.WriteLine("111");
}
//此处一定要关闭连接，否则添加数据时候会出错
table = null;
cat = null;
Application.DoEvents();
cn.Close();
}
private static bool CreateFile()
{
ADOX.CatalogClass cat = new ADOX.CatalogClass();
try {
cat.Create(string.Format(CreateStringFormat, FileName));
cat = null;
}
catch (Exception) {
return(false);
}
return(true);
}
static void Main(string[] args)
{
ADOX.CatalogClass cat = new ADOX.CatalogClass();
cat.Create("Provider=Microsoft.Jet.OLEDB.4.0;" +
"Data Source=D:\\AccessDB\\NewMDB.mdb;" +
"Jet OLEDB:Engine Type=5");
Console.WriteLine("Database Created Successfully");
cat = null;
}
/// <summary>
/// 根据指定的文件名称创建数据
/// </summary>
/// <param name="DBPath">绝对路径+文件名称</param>
public static void CreateAccess(string DBPath)
{
if (File.Exists(DBPath))//检查数据库是否已存在
{
throw new Exception("目标数据库已存在,无法创建");
}
DBPath = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" + DBPath;
//创建一个CatalogClass对象实例
ADOX.CatalogClass cat = new ADOX.CatalogClass();
//使用CatalogClass对象的Create方法创建ACCESS数据库
cat.Create(DBPath);
}
/// <summary>
/// Create access database; True = Success, False = Fail
/// </summary>
/// <returns></returns>
public bool CreateDB()
{
ADOX.CatalogClass cat = new ADOX.CatalogClass();
bool r = true;
try {
cat.Create(this.dbString);
Thread.Sleep(100);
r = File.Exists(this.msDatabasePath);
} catch { r = false; }
cat = null;
return(r);
}
/// <summary>
/// Girilen dosyayoluna mdb uzantılı access doyası oluşturan metot.
/// </summary>
/// <param name="mdbfilePath">Mdb dosyasının tamyolu ve dosyanın uzantılı ismi</param>
/// <param name="password">dosyanın şifresi.</param>
/// <returns>
/// 1: Dosya oluşturuldu.
/// 0: Dosya zaten mevcut olduğundan oluşturulmadı.
/// -1: Yakalanamayan istisna oluştuğunda gelir.
/// -2: System.FormatException istisnası.
/// -3: System.ArgumentException istisnası.
/// -4: System.Security.SecurityException istisnası.
/// -5: System.ArgumentNullException istisnası.
/// -6: System.UnauthorizedAccessException istisnası.
/// -7: System.IO.PathTooLongException istisnası.
/// -8: System.NotSupportedException istisnası.
/// </returns>
public static Int32 createMDBFile(string mdbfilePath, string password)
{
Int32 retInt = 0;
try
{
ADOX.CatalogClass cat = new ADOX.CatalogClass();
System.IO.FileInfo fInfo = new System.IO.FileInfo(mdbfilePath);
if (!fInfo.Exists)
{
if (fInfo.Extension != "mdb")
{
fInfo = new System.IO.FileInfo(String.Concat(fInfo.FullName, ".mdb"));
}
cat.Create(String.Format(@"Provider=Microsoft.Jet.OLEDB.4.0;
Data Source={0};
Jet OLEDB:Database Password={1};
Jet OLEDB:Engine Type=5", mdbfilePath, password));
retInt = 1;
cat = null;
}
else
{
retInt = 0;
}
System.GC.Collect();
}
catch (Exception exc)
{
throw exc;
}
/*
* catch (System.NotSupportedException) { retInt = -8; }
* catch (System.IO.PathTooLongException) { retInt = -7; }
* catch (System.UnauthorizedAccessException) { retInt = -6; }
* catch (System.ArgumentNullException) { retInt = -5; }
* catch (System.Security.SecurityException) { retInt = -4; }
* catch (System.ArgumentException) { retInt = -3; }
* catch (System.FormatException) { retInt = -2; }
* catch (System.Exception) { retInt = -1; }
*/
return(retInt);
}
private void funcCreate()
{
try {
// MDB파일을 동적으로 생성
ADOX.CatalogClass adoxCC = new ADOX.CatalogClass();
adoxCC.Create(strDBConnection);
// 연결을 끊기위해 null을 할당.
adoxCC.ActiveConnection = null;
adoxCC = null;
// 연결을 끊었다고 adoxCC = null을 해줘도 가비지컬렉션이 작동안하면,
// 프로그램 실행 중에는 언제될지 모른다. 수동으로 가비지컬렉션 작동
GC.Collect();
}
catch (Exception ex) {
throw new Exception("데이터베이스를 생성 중 에러 발생.", ex);
}
}
private void CreateDatabase()
{
ADOX.CatalogClass cat = new ADOX.CatalogClass();
cat.Create(GetConnectionString(true));
var recordTable = CreateTable(cat, null, config.TableName, config.Column, false);
foreach (var groupConfig in config.Group)
{
int index = config.Group.IndexOf(groupConfig); // MDB has maximum of 32 indexes per table
try
{
CreateGroupTable(cat, recordTable, groupConfig, index < 31);
}
catch (Exception ex)
{
Log(ex.Message + "\r\n");
}
}
}
/// <summary>
/// Create access data to path; True = Success, False = Fail
/// </summary>
/// <param name="filepath"></param>
/// <returns></returns>
public bool CreateDBAndTable <T>(string table_name)
{
ADOX.CatalogClass cat = new ADOX.CatalogClass();
bool r = true;
//Get properties of T
Type itemType = typeof(T);
var properties = itemType.GetProperties(BindingFlags.Public | BindingFlags.Instance);
try {
//Create microsoft database
cat.Create(this.dbString);
//Create the table and it's fields.
ADOX.Table table = new ADOX.Table();
table.Name = table_name;
//Add column to the table.
foreach (var p in properties)
{
table.Columns.Append(tableField(p.Name, cat, myConverter.FromVSTypeToTableAccessDataType(p.PropertyType.Name.ToString())));
}
//Add the table to our database
cat.Tables.Append(table);
// Close the connection to the database after we are done creating it and adding the table to it.
con = (ADODB.Connection)cat.ActiveConnection;
if (con != null && con.State != 0)
{
con.Close();
}
} catch { r = false; }
cat = null;
return(r);
}
public void creat_DB()
{
try
{
string dbName = Application.StartupPath + @"\source\StuContact.mdb";//注意扩展名必须为mdb,否则不能插入表
ADOX.CatalogClass cat = new ADOX.CatalogClass();
cat.Create(string.Format("Provider=Microsoft.Jet.OLEDB.4.0;Data Source='{0}';Jet OLEDB:Database Password=jiemi;Jet OLEDB:Engine Type=5", dbName));
//新建表
ADOX.TableClass tbl = new ADOX.TableClass();
tbl.ParentCatalog = cat;
tbl.Name = "Contact_Users";
ADOX.TableClass tb2 = new ADOX.TableClass();
tb2.ParentCatalog = cat;
tb2.Name = "UserGroup";
ADOX.TableClass tb3 = new ADOX.TableClass();
tb3.ParentCatalog = cat;
tb3.Name = "UserInfo";
#region 表一：
//给各个表增加自动增长的字段
ADOX.ColumnClass tb_one_col1 = new ADOX.ColumnClass();
tb_one_col1.ParentCatalog = cat;
tb_one_col1.Type = ADOX.DataTypeEnum.adInteger; //必须先设置字段类型
tb_one_col1.Name = "ID";
tb_one_col1.Properties["Jet OLEDB:Allow Zero Length"].Value = false; //是否允许为空
tb_one_col1.Properties["AutoIncrement"].Value = true; //自增长
tbl.Columns.Append(tb_one_col1, ADOX.DataTypeEnum.adInteger, 0); //添加字段
//增加文本字段
ADOX.ColumnClass col2 = new ADOX.ColumnClass();//注意col序号
col2.ParentCatalog = cat;
col2.Name = "User_Name";
col2.Properties["Jet OLEDB:Allow Zero Length"].Value = false;
tbl.Columns.Append(col2, ADOX.DataTypeEnum.adVarChar, 50);
ADOX.ColumnClass col3 = new ADOX.ColumnClass();
col3.ParentCatalog = cat;
col3.Name = "User_Img";
col3.Type = ADOX.DataTypeEnum.adLongVarBinary;////////////OLE类型设置,用来存储图片
col3.Properties["Jet OLEDB:Allow Zero Length"].Value = true;
tbl.Columns.Append(col3, ADOX.DataTypeEnum.adLongVarBinary, 0);
ADOX.ColumnClass col4 = new ADOX.ColumnClass();
col4.ParentCatalog = cat;
col4.Name = "User_Sex";
col4.Properties["Jet OLEDB:Allow Zero Length"].Value = true;
tbl.Columns.Append(col4, ADOX.DataTypeEnum.adVarChar, 4);
ADOX.ColumnClass col5 = new ADOX.ColumnClass();
col5.ParentCatalog = cat;
col5.Name = "User_Age";
col5.Properties["Jet OLEDB:Allow Zero Length"].Value = true;
tbl.Columns.Append(col5, ADOX.DataTypeEnum.adInteger, 200);
ADOX.ColumnClass col6 = new ADOX.ColumnClass();
col6.ParentCatalog = cat;
col6.Name = "User_Birth";
col6.Properties["Jet OLEDB:Allow Zero Length"].Value = true;
tbl.Columns.Append(col6, ADOX.DataTypeEnum.adVarChar, 50);
ADOX.ColumnClass col7 = new ADOX.ColumnClass();
col7.ParentCatalog = cat;
col7.Name = "User_Phone";
col7.Properties["Jet OLEDB:Allow Zero Length"].Value = false;
tbl.Columns.Append(col7, ADOX.DataTypeEnum.adVarChar, 15);
ADOX.ColumnClass col8 = new ADOX.ColumnClass();
col8.ParentCatalog = cat;
col8.Name = "User_Qq";
col8.Properties["Jet OLEDB:Allow Zero Length"].Value = true;
tbl.Columns.Append(col8, ADOX.DataTypeEnum.adVarChar, 25);
ADOX.ColumnClass col9 = new ADOX.ColumnClass();
col9.ParentCatalog = cat;
col9.Name = "User_Company";
col9.Properties["Jet OLEDB:Allow Zero Length"].Value = true;
tbl.Columns.Append(col9, ADOX.DataTypeEnum.adVarChar, 50);
ADOX.ColumnClass col10 = new ADOX.ColumnClass();
col10.ParentCatalog = cat;
col10.Name = "User_Address";
//col10.Type
col10.Properties["Jet OLEDB:Allow Zero Length"].Value = true;
tbl.Columns.Append(col10, ADOX.DataTypeEnum.adVarChar, 100);
ADOX.ColumnClass col11 = new ADOX.ColumnClass();
col11.Type = ADOX.DataTypeEnum.adLongVarWChar;//长文本
col11.ParentCatalog = cat;
col11.Name = "User_Remark";
col11.Properties["Jet OLEDB:Allow Zero Length"].Value = true;
tbl.Columns.Append(col11, ADOX.DataTypeEnum.adLongVarChar, 16);
ADOX.ColumnClass col12 = new ADOX.ColumnClass();
col12.ParentCatalog = cat;
col12.Name = "User_BelongGroup";
col12.Properties["Jet OLEDB:Allow Zero Length"].Value = false;
tbl.Columns.Append(col12, ADOX.DataTypeEnum.adInteger, 200);
cat.Tables.Append(tbl); //把表加入数据库(非常重要)
#endregion
#region 表二：
//给各增加自动增长的字段
ADOX.ColumnClass tb_two_col1 = new ADOX.ColumnClass(); //代表表的第一列
tb_two_col1.ParentCatalog = cat;
tb_two_col1.Type = ADOX.DataTypeEnum.adInteger; //必须先设置字段类型
tb_two_col1.Name = "ID";
tb_two_col1.Properties["Jet OLEDB:Allow Zero Length"].Value = false;
tb_two_col1.Properties["AutoIncrement"].Value = true;
tb2.Columns.Append(tb_two_col1, ADOX.DataTypeEnum.adInteger, 0);
//增加文本字段
ADOX.ColumnClass tb_two_col2 = new ADOX.ColumnClass();//注意col序号
tb_two_col2.ParentCatalog = cat;
tb_two_col2.Name = "Group_Id";
tb_two_col2.Properties["Jet OLEDB:Allow Zero Length"].Value = false;
tb2.Columns.Append(tb_two_col2, ADOX.DataTypeEnum.adVarChar, 50);
ADOX.ColumnClass tb_two_col3 = new ADOX.ColumnClass();
tb_two_col3.ParentCatalog = cat;
tb_two_col3.Name = "Group_Name";
tb_two_col3.Properties["Jet OLEDB:Allow Zero Length"].Value = false;
tb2.Columns.Append(tb_two_col3, ADOX.DataTypeEnum.adVarChar, 50);
cat.Tables.Append(tb2);
#endregion
#region 表三：
ADOX.ColumnClass tb_three_col1 = new ADOX.ColumnClass(); //代表表的第一列
tb_three_col1.ParentCatalog = cat;
tb_three_col1.Type = ADOX.DataTypeEnum.adInteger; //必须先设置字段类型
tb_three_col1.Name = "ID";
tb_three_col1.Properties["Jet OLEDB:Allow Zero Length"].Value = false;
tb_three_col1.Properties["AutoIncrement"].Value = true;
tb3.Columns.Append(tb_three_col1, ADOX.DataTypeEnum.adInteger, 0);
//增加文本字段
ADOX.ColumnClass tb_three_col2 = new ADOX.ColumnClass();
tb_three_col2.ParentCatalog = cat;
tb_three_col2.Name = "User_Name";
tb_three_col2.Properties["Jet OLEDB:Allow Zero Length"].Value = false;
tb3.Columns.Append(tb_three_col2, ADOX.DataTypeEnum.adVarChar, 50);
ADOX.ColumnClass tb_three_col3 = new ADOX.ColumnClass();
tb_three_col3.ParentCatalog = cat;
tb_three_col3.Name = "User_Pwd";
tb_three_col3.Properties["Jet OLEDB:Allow Zero Length"].Value = false;
tb3.Columns.Append(tb_three_col3, ADOX.DataTypeEnum.adVarChar, 50);
ADOX.ColumnClass tb_three_col4 = new ADOX.ColumnClass();
tb_three_col4.ParentCatalog = cat;
tb_three_col4.Name = "User_Img";
tb_three_col4.Type = ADOX.DataTypeEnum.adLongVarBinary;
tb_three_col4.Properties["Jet OLEDB:Allow Zero Length"].Value = true;
tb3.Columns.Append(tb_three_col4, ADOX.DataTypeEnum.adLongVarBinary, 0);
ADOX.ColumnClass tb_three_col5 = new ADOX.ColumnClass();
tb_three_col5.ParentCatalog = cat;
tb_three_col5.Name = "User_Group";
tb_three_col5.Properties["Jet OLEDB:Allow Zero Length"].Value = false;
tb3.Columns.Append(tb_three_col5, ADOX.DataTypeEnum.adInteger, 200);
ADOX.ColumnClass tb_three_col6 = new ADOX.ColumnClass();
tb_three_col6.ParentCatalog = cat;
tb_three_col6.Name = "User_IsImg";
tb_three_col6.Properties["Jet OLEDB:Allow Zero Length"].Value = false;
tb3.Columns.Append(tb_three_col6, ADOX.DataTypeEnum.adInteger, 2);
cat.Tables.Append(tb3);
#endregion
//转换为ADO连接,并关闭
(cat.ActiveConnection as ADODB.Connection).Close();
cat.ActiveConnection = null;
cat = null;
//创建完数据库之后自动初始化必要数据
using (OleDbConnection olconn = new OleDbConnection(connStr))
{
olconn.Open();
OleDbCommand olcmd = new OleDbCommand();
olcmd.Connection = olconn;
olcmd.CommandText = "insert into UserInfo(User_Name,User_Pwd,User_Group,User_IsImg) values('admin','admin','1','0')";
olcmd.ExecuteNonQuery();
olcmd.CommandText = "insert into UserGroup(Group_Id,Group_Name) values('0','家人')";
olcmd.ExecuteNonQuery();
olcmd.CommandText = "insert into UserGroup(Group_Id,Group_Name) values('1','朋友')";
olcmd.ExecuteNonQuery();
olcmd.CommandText = "insert into UserGroup(Group_Id,Group_Name) values('2','同学')";
olcmd.ExecuteNonQuery();
olcmd.CommandText = "insert into UserGroup(Group_Id,Group_Name) values('3','同事')";
olcmd.ExecuteNonQuery();
olcmd.CommandText = "insert into UserGroup(Group_Id,Group_Name) values('4','老师')";
olcmd.ExecuteNonQuery();
}
//开始将数据库的密码加密后写入配置文件
config.writeConfig_IsEditDBandAddPwd(true, "jiemi");
}
catch (Exception ex)
{
MessageBox.Show("出现了如下问题：" + ex.Message, "异常提示");
}
}
//Create Access Database
public static void CreatAccessDatabase(string Password)
{
ADOX.CatalogClass catalog = new ADOX.CatalogClass();
try
{
#region Create Access Database
//Creat Access
catalog.Create(S_AccessConnection_1 + S_AccessConnection_2);
#region Create Stock Table for Access Database
TableClass Stock_Table = new TableClass();
Stock_Table.ParentCatalog = catalog;
Stock_Table.Name = "Script_Data";
#region Create & Add Member in this Table
Column st_column = new ADOX.Column();
st_column.ParentCatalog = catalog;
st_column.Name = "Record_ID";
st_column.Type = DataTypeEnum.adInteger;
st_column.DefinedSize = 9;
st_column.Properties["AutoIncrement"].Value = true;
Stock_Table.Columns.Append(st_column, DataTypeEnum.adInteger, 9);
Stock_Table.Keys.Append("PrimaryKey", KeyTypeEnum.adKeyPrimary, st_column, null, null);
Column st_column1 = new Column();
st_column1.ParentCatalog = catalog;
st_column1.Name = "Drive_Front";
st_column1.Type = DataTypeEnum.adVarWChar;
st_column1.DefinedSize = 255;
st_column1.Properties["Jet OLEDB:Allow Zero Length"].Value = true;
Stock_Table.Columns.Append(st_column1, DataTypeEnum.adVarWChar, 255);
Column st_column2 = new Column();
st_column2.ParentCatalog = catalog;
st_column2.Name = "Passenger_Front";
st_column2.Type = DataTypeEnum.adVarWChar;
st_column2.DefinedSize = 255;
st_column2.Properties["Jet OLEDB:Allow Zero Length"].Value = true;
Stock_Table.Columns.Append(st_column2, DataTypeEnum.adVarWChar, 255);
Column st_column3 = new Column();
st_column3.ParentCatalog = catalog;
st_column3.Name = "Left_Rear";
st_column3.Type = DataTypeEnum.adVarWChar;
st_column3.DefinedSize = 255;
st_column3.Properties["Jet OLEDB:Allow Zero Length"].Value = true;
Stock_Table.Columns.Append(st_column3, DataTypeEnum.adVarWChar, 255);
Column st_column4 = new Column();
st_column4.ParentCatalog = catalog;
st_column4.Name = "Right_Rear";
st_column4.Type = DataTypeEnum.adVarWChar;
st_column4.DefinedSize = 255;
st_column4.Properties["Jet OLEDB:Allow Zero Length"].Value = true;
Stock_Table.Columns.Append(st_column4, DataTypeEnum.adVarWChar, 255);
Column st_column5 = new Column();
st_column5.ParentCatalog = catalog;
st_column5.Name = "Sound_Priority";
st_column5.Type = DataTypeEnum.adInteger;
st_column5.DefinedSize = 9;
Stock_Table.Columns.Append(st_column5, DataTypeEnum.adInteger, 9);
Column st_column6 = new Column();
st_column6.ParentCatalog = catalog;
st_column6.Name = "Sound_Duty_Cycle";
st_column6.Type = DataTypeEnum.adVarWChar;
st_column6.DefinedSize = 255;
st_column6.Properties["Jet OLEDB:Allow Zero Length"].Value = true;
Stock_Table.Columns.Append(st_column6, DataTypeEnum.adVarWChar, 255);
Column st_column7 = new Column();
st_column7.ParentCatalog = catalog;
st_column7.Name = "Sound_Cadence_Period";
st_column7.Type = DataTypeEnum.adDouble;
st_column7.DefinedSize = 9;
Stock_Table.Columns.Append(st_column7, DataTypeEnum.adDouble, 9);
Column st_column8 = new Column();
st_column8.ParentCatalog = catalog;
st_column8.Name = "Number_of_Repetitions";
st_column8.Type = DataTypeEnum.adDouble;
st_column8.DefinedSize = 9;
Stock_Table.Columns.Append(st_column8, DataTypeEnum.adDouble, 9);
Column st_column9 = new Column();
st_column9.ParentCatalog = catalog;
st_column9.Name = "Sound_Tone";
st_column9.Type = DataTypeEnum.adDouble;
st_column9.DefinedSize = 9;
Stock_Table.Columns.Append(st_column9, DataTypeEnum.adDouble, 9);
Column st_cloumn10 = new Column();
st_cloumn10.ParentCatalog = catalog;
st_cloumn10.Name = "Sleep";
st_cloumn10.Type = DataTypeEnum.adVarWChar;
st_cloumn10.DefinedSize = 255;
st_cloumn10.Properties["Jet OLEDB:Allow Zero Length"].Value = true;
Stock_Table.Columns.Append(st_cloumn10, DataTypeEnum.adVarWChar, 255);
#endregion
catalog.Tables.Append(Stock_Table);
#endregion
//Release
System.Runtime.InteropServices.Marshal.ReleaseComObject(Stock_Table);
Stock_Table = null;
#endregion
}
catch (Exception ex)
{
//MainForm.Access_Result = ex.Message.ToString();
}
finally
{
System.Runtime.InteropServices.Marshal.FinalReleaseComObject(catalog.ActiveConnection);
System.Runtime.InteropServices.Marshal.FinalReleaseComObject(catalog);
catalog = null;
System.GC.WaitForPendingFinalizers();
System.GC.Collect();
}
}
public static void WriteDB(List <xl2cad_cad.midTable> tables)
{
string filePath = @"D:\xl2cad.mdb";
string mdbCommand = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" + filePath + ";Jet OLEDB:Engine Type=5";
ADODB.Connection cn = null;
ADOX.CatalogClass cat = null;
try
{
//创建数据库，有就删除重建，没有则新建
cat = new CatalogClass();
if (File.Exists(filePath))
{
File.Delete(filePath);
}
cat.Create(mdbCommand);
//连接数据库
cn = new ADODB.Connection();
cat = null;
cat = new CatalogClass();
//创建数据表
foreach (xl2cad_cad.midTable midtable in tables)
{
cn.Open("Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" + filePath, null, null, -1);
cat.ActiveConnection = cn;
ADOX.TableClass table = new TableClass();
string tablename = midtable.Name;
table.ParentCatalog = cat;
table.Name = tablename;
cat.Tables.Append(table);
//将数据写入数据表
//Excel.WorkSheet.Range导出的object[obj1,obj2]中,obj1为行，obj2为列
//因此先按obj2的个数建立字段，在按obj1的个数一行行填入数据
object[,] datas = midtable.data;
int rowCount = 0;
int columnCount = 0;
rowCount = datas.GetUpperBound(0); //第一维obj1的最大值，行数
columnCount = datas.GetUpperBound(1); //第二维obj2的最大值，列数
//建立字段
for (int i = 0; i <= columnCount; i++)
{
ADOX.ColumnClass col = null;
col = new ADOX.ColumnClass();
col.ParentCatalog = cat;
col.Properties["Jet OLEDB:Allow Zero Length"].Value = true;
col.Name = "Value" + i;
table.Columns.Append(col, ADOX.DataTypeEnum.adVarChar, 25);
}
//按行填入数据
object ra = null;
ADODB.Recordset rs = new ADODB.Recordset();
for (int i = 0; i <= rowCount; i++)
{
//构造按行写入的sql语句
string sql1 = String.Format("INSERT INTO {0} (", tablename);
string sql2 = String.Format(") VALUES (");
string strValue = null;
for (int j = 0; j <= columnCount; j++)
{
if (datas[i, j] == null)
{
strValue = datas[i, j] as string;
}
else
{
strValue = datas[i, j].ToString();
}
sql1 += String.Format("Value{0},", j);
sql2 += String.Format("'{0}',", strValue);
}
string sql = sql1 + sql2 + ")";
//将 ,) 替换为 ）
sql = sql.Replace(",)", ")");
rs = cn.Execute(sql, out ra, -1);
}
}
}
catch (System.Exception ex)
{
MessageBox.Show(ex.ToString());
}
finally
{
cn.Close();
System.Runtime.InteropServices.Marshal.FinalReleaseComObject(cn);
System.Runtime.InteropServices.Marshal.FinalReleaseComObject(cat);
System.GC.Collect();
}
}
private ADOX.Table CreateTable(ADOX.CatalogClass cat, ADOX.Table parentTable, string tableName, List <MappingColumn> columns, bool createIndex)
{
var newTable = new ADOX.Table();
newTable.Name = tableName;
var idCol = new ADOX.Column();
idCol.Name = "id";
idCol.Type = DataTypeEnum.adInteger;
idCol.ParentCatalog = cat;
idCol.Properties["AutoIncrement"].Value = true;
newTable.Columns.Append(idCol);
newTable.Keys.Append(tableName + "PK", KeyTypeEnum.adKeyPrimary, "id");
if (tableName == config.TableName)
{
var fileNameCol = new ADOX.Column();
fileNameCol.Name = "fileName";
fileNameCol.Type = DataTypeEnum.adVarWChar;
fileNameCol.ParentCatalog = cat;
newTable.Columns.Append(fileNameCol);
}
if (parentTable != null)
{
newTable.Columns.Append(parentTable.Name + "Id", DataTypeEnum.adInteger);
if (createIndex)
{
newTable.Keys.Append(tableName + "FKey", KeyTypeEnum.adKeyForeign, parentTable.Name + "Id", parentTable.Name, "id");
}
}
List <string> columnNames = new List <string>();
foreach (var groupColumnConfig in columns)
{
if (columnNames.Contains(groupColumnConfig.Name))
{
Log(string.Format("Column {0} is a duplicated (occurs more than once)\r\n", groupColumnConfig.Name));
}
else
{
columnNames.Add(groupColumnConfig.Name);
}
if (groupColumnConfig.Name.ToLower() == "id")
{
Log("Column name \"id\" in table " + tableName + " is reserved for used. Please rename the column in the config.\r\n");
}
if (parentTable != null && groupColumnConfig.Name.ToLower() == parentTable.Name.ToLower() + "id")
{
Log("Column name \"" + parentTable.Name + "Id\" is reserved for use. Please rename the column in the config.\r\n");
}
var newCol = new ADOX.Column();
newCol.Name = groupColumnConfig.Name;
newCol.Type = DataTypeEnum.adVarWChar;
if (groupColumnConfig.IsNarrative)
{
newCol.Type = DataTypeEnum.adLongVarWChar;
}
newCol.ParentCatalog = cat;
newCol.Attributes = ColumnAttributesEnum.adColNullable;
newTable.Columns.Append(newCol);
}
cat.Tables.Append(newTable);
return(newTable);
}
/// <summary>
/// 根据指定的文件名称创建数据
/// </summary>
/// <param name="DBPath">绝对路径+文件名称</param>
public static void CreateAccess(string DBPath)
{
if (File.Exists(DBPath))//检查数据库是否已存在
{
throw new Exception("目标数据库已存在,无法创建");
}
DBPath = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" + DBPath;
//创建一个CatalogClass对象实例
ADOX.CatalogClass cat = new ADOX.CatalogClass();
//使用CatalogClass对象的Create方法创建ACCESS数据库
cat.Create(DBPath);
}
public static void CreatAccessDatabase(string Address)
{
root = S_AccessConnection_1 + Address + S_AccessConnection_2;
ADOX.CatalogClass catalog = new ADOX.CatalogClass();
try
{
#region Create Access Database
//Creat Access
if (System.IO.File.Exists(@Address))
{
System.IO.File.Delete(Address);
}
catalog.Create(S_AccessConnection_1 + Address + S_AccessConnection_2);
#region Create Stock Table for Access Database
TableClass Test_Script = new TableClass();
Test_Script.ParentCatalog = catalog;
Test_Script.Name = "Auto_Test_Script";
#region Create & Add Member in this Table
Column st_column = new ADOX.Column();
st_column.ParentCatalog = catalog;
st_column.Name = "Record_ID";
st_column.Type = DataTypeEnum.adInteger;
st_column.DefinedSize = 9;
st_column.Properties["AutoIncrement"].Value = true;
Test_Script.Columns.Append(st_column, DataTypeEnum.adInteger, 9);
Test_Script.Keys.Append("PrimaryKey", KeyTypeEnum.adKeyPrimary, st_column, null, null);
Column st_column1 = new Column();
st_column1.ParentCatalog = catalog;
st_column1.Name = "Drive_Front";
st_column1.Type = DataTypeEnum.adInteger;
st_column1.DefinedSize = 9;
Test_Script.Columns.Append(st_column1, DataTypeEnum.adInteger, 9);
Column st_column2 = new Column();
st_column2.ParentCatalog = catalog;
st_column2.Name = "Passenger_Front";
st_column2.Type = DataTypeEnum.adInteger;
st_column2.DefinedSize = 9;
Test_Script.Columns.Append(st_column2, DataTypeEnum.adInteger, 9);
Column st_column3 = new Column();
st_column3.ParentCatalog = catalog;
st_column3.Name = "Left_Rear";
st_column3.Type = DataTypeEnum.adInteger;
st_column3.DefinedSize = 9;
Test_Script.Columns.Append(st_column3, DataTypeEnum.adInteger, 9);
Column st_column4 = new Column();
st_column4.ParentCatalog = catalog;
st_column4.Name = "Right_Rear";
st_column4.Type = DataTypeEnum.adInteger;
st_column4.DefinedSize = 9;
Test_Script.Columns.Append(st_column4, DataTypeEnum.adInteger, 9);
Column st_column5 = new Column();
st_column5.ParentCatalog = catalog;
st_column5.Name = "Sound_Priority";
st_column5.Type = DataTypeEnum.adInteger;
st_column5.DefinedSize = 9;
Test_Script.Columns.Append(st_column5, DataTypeEnum.adInteger, 9);
Column st_column6 = new Column();
st_column6.ParentCatalog = catalog;
st_column6.Name = "Sound_Duty_Cycle";
st_column6.Type = DataTypeEnum.adInteger;
st_column6.DefinedSize = 9;
Test_Script.Columns.Append(st_column6, DataTypeEnum.adInteger, 9);
Column st_column7 = new Column();
st_column7.ParentCatalog = catalog;
st_column7.Name = "Sound_Cadence_Period";
st_column7.Type = DataTypeEnum.adInteger;
st_column7.DefinedSize = 9;
Test_Script.Columns.Append(st_column7, DataTypeEnum.adInteger, 9);
Column st_column8 = new Column();
st_column8.ParentCatalog = catalog;
st_column8.Name = "Number_of_Repetitions";
st_column8.Type = DataTypeEnum.adInteger;
st_column8.DefinedSize = 9;
Test_Script.Columns.Append(st_column8, DataTypeEnum.adInteger, 9);
Column st_column9 = new Column();
st_column9.ParentCatalog = catalog;
st_column9.Name = "Sound_Tone";
st_column9.Type = DataTypeEnum.adInteger;
st_column9.DefinedSize = 9;
Test_Script.Columns.Append(st_column9, DataTypeEnum.adInteger, 9);
Column st_column10 = new Column();
st_column10.ParentCatalog = catalog;
st_column10.Name = "Sleep";
st_column10.Type = DataTypeEnum.adInteger;
st_column10.DefinedSize = 9;
Test_Script.Columns.Append(st_column10, DataTypeEnum.adInteger, 9);
#endregion
catalog.Tables.Append(Test_Script);
#endregion
//Release
System.Runtime.InteropServices.Marshal.ReleaseComObject(Test_Script);
Test_Script = null;
#endregion
}
finally
{
System.Runtime.InteropServices.Marshal.FinalReleaseComObject(catalog.ActiveConnection);
System.Runtime.InteropServices.Marshal.FinalReleaseComObject(catalog);
catalog = null;
System.GC.WaitForPendingFinalizers();
System.GC.Collect();
}
}
public Form1()
{
InitializeComponent();
label1.BackColor = Color.Transparent;
label2.BackColor = Color.Transparent;
label3.BackColor = Color.Transparent;
button1.ButtonStyle();
button2.ButtonStyle();
button3.ButtonStyle();
//创建数据库
if (!File.Exists(filePath))
{
//创建数据库
ADOX.CatalogClass Bike = new ADOX.CatalogClass();
Bike.Create("Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" + filePath + ";Jet OLEDB:Database Password=2327085154;");
//新建一个表[user]
ADOX.TableClass user = new ADOX.TableClass();
user.ParentCatalog = Bike;
user.Name = "user";
//增加一个自动增长的字段ID
ADOX.ColumnClass ID = new ADOX.ColumnClass();
ID.ParentCatalog = Bike;
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 = Bike;
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 = Bike;
password.Name = "密码";
password.Properties["Jet OLEDB:Allow Zero Length"].Value = false;
user.Columns.Append(password, ADOX.DataTypeEnum.adVarChar, 20);
//把表加进数据库
Bike.Tables.Append(user);
System.Runtime.InteropServices.Marshal.ReleaseComObject(user);
//新建表[bike]
ADOX.TableClass bike = new ADOX.TableClass();
bike.ParentCatalog = Bike;
bike.Name = "bike";
//增加一个自动增长的字段 序号
ADOX.ColumnClass _ID = new ADOX.ColumnClass();
_ID.ParentCatalog = Bike;
_ID.Type = ADOX.DataTypeEnum.adInteger; // 必须先设置字段类型
_ID.Name = "序号";
_ID.Properties["Jet OLEDB:Allow Zero Length"].Value = false;
_ID.Properties["AutoIncrement"].Value = true;
bike.Columns.Append(_ID, ADOX.DataTypeEnum.adInteger, 0);
//增加一个文本字段name
ADOX.ColumnClass name = new ADOX.ColumnClass();
name.ParentCatalog = Bike;
name.Name = "姓名";
name.Properties["Jet OLEDB:Allow Zero Length"].Value = false;
bike.Columns.Append(name, ADOX.DataTypeEnum.adVarChar, 40);
//增加一个文本字段Class
ADOX.ColumnClass Class = new ADOX.ColumnClass();
Class.ParentCatalog = Bike;
Class.Name = "班级";
Class.Properties["Jet OLEDB:Allow Zero Length"].Value = false;
bike.Columns.Append(Class, ADOX.DataTypeEnum.adVarChar, 100);
//增加一个文本字段pNumber
ADOX.ColumnClass pNumber = new ADOX.ColumnClass();
pNumber.ParentCatalog = Bike;
pNumber.Name = "联系电话";
pNumber.Properties["Jet OLEDB:Allow Zero Length"].Value = false;
bike.Columns.Append(pNumber, ADOX.DataTypeEnum.adVarChar, 20);
//增加一个文本字段room
ADOX.ColumnClass room = new ADOX.ColumnClass();
room.ParentCatalog = Bike;
room.Name = "寝室号";
room.Properties["Jet OLEDB:Allow Zero Length"].Value = false;
bike.Columns.Append(room, ADOX.DataTypeEnum.adVarChar, 20);
//增加一个文本字段bikeID
ADOX.ColumnClass bikeID = new ADOX.ColumnClass();
bikeID.ParentCatalog = Bike;
bikeID.Name = "自行车牌号";
bikeID.Properties["Jet OLEDB:Allow Zero Length"].Value = false;
bike.Columns.Append(bikeID, ADOX.DataTypeEnum.adVarChar, 20);
//增加一个文本字段image
ADOX.ColumnClass image = new ADOX.ColumnClass();
image.ParentCatalog = Bike;
image.Name = "自行车照片";
image.Properties["Jet OLEDB:Allow Zero Length"].Value = false;
bike.Columns.Append(image, ADOX.DataTypeEnum.adLongVarChar, 1000);
//把表加进数据库
Bike.Tables.Append(bike);
System.Runtime.InteropServices.Marshal.ReleaseComObject(bike);
System.Runtime.InteropServices.Marshal.ReleaseComObject(bike);
user = null;
bike = null;
Bike = null;
GC.WaitForPendingFinalizers();
GC.Collect();
}
}
private void button9_Click(object sender, EventArgs e)
{
if (INFOMODE) return;
try
{
ADOX.CatalogClass cat = new ADOX.CatalogClass();
cat.Create(MyConString);
// The junk below closes the connection. You only need this if you rly want to delete the file after I guess...
// Microsoft ActiveX Data Objects 2.8 Library
ADODB.Connection connection = (ADODB.Connection)cat.ActiveConnection;
connection.Close();
// //Marshal.ReleaseComObject(cat); // we don't need this to close the connection anymore... just interesting
// cat = null;
// GC.Collect(); // This is the last resort - don't use if ReleaseComObject works.
}
catch (Exception exp)
{
MessageBox.Show("Already exists (probably) \n\r" + exp);
}
SetDbsExistance();
}
