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);
}
/// <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);
}
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);
}
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, "异常提示");
}
}
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();
}
}
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 MainForm_Load(object sender, EventArgs e)
{
dataGridView1.RowsDefaultCellStyle.Alignment = DataGridViewContentAlignment.MiddleCenter;
dataGridView3.RowsDefaultCellStyle.Alignment = DataGridViewContentAlignment.MiddleCenter;
//ReportParameterCostume ReportParameterPrintDate ReportParameterID ReportParameterRed ReportParameterCountAll
//ReportParameterCountAllCN ReportParameterDrawer ReportParameterPhone ReportParameterRecAddr ReportParameterWhite ReportParameterBlue
//ReportParameterSalesman ReportParameterVerification ReportParameterDelivery ReportParameterSaver ReportParameterPrintTime
PrintParams.Add("ReportParameterCostume", "");
PrintParams.Add("ReportParameterPrintDate", "");
PrintParams.Add("ReportParameterID", "");
PrintParams.Add("ReportParameterRed", "");
PrintParams.Add("ReportParameterCountAll", "");
PrintParams.Add("ReportParameterCountAllCN", "");
PrintParams.Add("ReportParameterDrawer", "");
PrintParams.Add("ReportParameterPhone", "");
PrintParams.Add("ReportParameterRecAddr", "");
PrintParams.Add("ReportParameterWhite", "");
PrintParams.Add("ReportParameterBlue", "");
PrintParams.Add("ReportParameterSalesman", "");
PrintParams.Add("ReportParameterVerification", "");
PrintParams.Add("ReportParameterDelivery", "");
PrintParams.Add("ReportParameterSaver", "");
PrintParams.Add("ReportParameterPrintTime", "");
PrintParams.Add("ReportParameterYellow", "");
#region 初始化数据库
string filePath = Environment.CurrentDirectory + @"\DrugsDatabase.mdb";
//初始化数据库，若数据库不存在则创建
if (!File.Exists(filePath))
{
if (AccessDbHelper.CreateAccessDb(filePath))
{
#region 初始化表字段
//药品名称 DrugsName
ADOX.ColumnClass DrugsName = new ADOX.ColumnClass
{
//ParentCatalog = catalog,
Name = "DrugsName",
Attributes = ColumnAttributesEnum.adColNullable, //允许空值
Type = DataTypeEnum.adVarWChar,
DefinedSize = 255,
};
//规格 Specification
ADOX.ColumnClass Specification = new ADOX.ColumnClass
{
//ParentCatalog = catalog,
Name = "Specification",
Attributes = ColumnAttributesEnum.adColNullable, //允许空值
Type = DataTypeEnum.adVarWChar,
DefinedSize = 255,
};
//型号 Model
ADOX.ColumnClass Model = new ADOX.ColumnClass
{
//ParentCatalog = catalog,
Name = "Model",
Attributes = ColumnAttributesEnum.adColNullable, //允许空值
Type = DataTypeEnum.adVarWChar,
DefinedSize = 50,
};
//单位 Unti
ADOX.ColumnClass Unti = new ADOX.ColumnClass
{
//ParentCatalog = catalog,
Name = "Unti",
Attributes = ColumnAttributesEnum.adColNullable, //允许空值
Type = DataTypeEnum.adVarWChar,
DefinedSize = 50,
};
//单价 Price
ADOX.ColumnClass Price = new ADOX.ColumnClass
{
//ParentCatalog = catalog,
Name = "Price",
Attributes = ColumnAttributesEnum.adColNullable, //允许空值
Type = DataTypeEnum.adDouble,
DefinedSize = 50,
};
//产地 Origin
ADOX.ColumnClass Origin = new ADOX.ColumnClass
{
//ParentCatalog = catalog,
Name = "Origin",
Attributes = ColumnAttributesEnum.adColNullable, //允许空值
Type = DataTypeEnum.adVarWChar,
DefinedSize = 255,
};
//批准批号 Batch
ADOX.ColumnClass Batch = new ADOX.ColumnClass
{
//ParentCatalog = catalog,
Name = "Batch",
Attributes = ColumnAttributesEnum.adColNullable, //允许空值
Type = DataTypeEnum.adVarWChar,
DefinedSize = 50,
};
//生产日期 ProductionDate
ADOX.ColumnClass ProductionDate = new ADOX.ColumnClass
{
//ParentCatalog = catalog,
Name = "ProductionDate",
Attributes = ColumnAttributesEnum.adColNullable, //允许空值
Type = ADOX.DataTypeEnum.adDate,
};
//有效日期 Validity
ADOX.ColumnClass Validity = new ADOX.ColumnClass
{
//ParentCatalog = catalog,
Name = "Validity",
Attributes = ColumnAttributesEnum.adColNullable, //允许空值
Type = ADOX.DataTypeEnum.adDate,
};
//库存 Stock
ADOX.ColumnClass Stock = new ADOX.ColumnClass
{
//ParentCatalog = catalog,
Name = "Stock",
Attributes = ColumnAttributesEnum.adColNullable, //允许空值
Type = ADOX.DataTypeEnum.adInteger,
DefinedSize = 100
};
//质量状况 Quality
ADOX.ColumnClass Quality = new ADOX.ColumnClass
{
//ParentCatalog = catalog,
Name = "Quality",
Attributes = ColumnAttributesEnum.adColNullable, //允许空值
Type = ADOX.DataTypeEnum.adVarWChar,
DefinedSize = 255
};
#endregion
List <ADOX.ColumnClass> columns = new List <ADOX.ColumnClass>();
columns.Add(DrugsName);
columns.Add(Specification);
columns.Add(Model);
columns.Add(Unti);
columns.Add(Price);
columns.Add(Origin);
columns.Add(Batch);
columns.Add(ProductionDate);
columns.Add(Validity);
columns.Add(Stock);
columns.Add(Quality);
AccessDbHelper.CreateAccessTable(filePath, "DrugsTable", columns);
}
else
{
MessageBox.Show("数据库创建失败，以管理员身份运行！或添加数据库[DrugsDatabase.mdb]到程序运行目录下。", "警告");
}
}
else
{
ReflashDataGridView();
}
#endregion
#region DataGridView 按钮渲染
DataGridViewButtonColumn btnDel = new DataGridViewButtonColumn();
btnDel.Name = "colbtnDel";
btnDel.HeaderText = "操作";
btnDel.DefaultCellStyle.NullValue = "删除";
DataGridViewButtonColumn btnEdit = new DataGridViewButtonColumn();
btnEdit.Name = "colbtnEdit";
btnEdit.HeaderText = "操作";
btnEdit.DefaultCellStyle.NullValue = "编辑";
DataGridViewButtonColumn btnPrint = new DataGridViewButtonColumn();
btnPrint.Name = "colbtnPrint";
btnPrint.HeaderText = "操作";
btnPrint.DefaultCellStyle.NullValue = "打印";
dataGridView1.Columns.Add(btnDel);
dataGridView1.Columns.Add(btnEdit);
dataGridView1.Columns.Add(btnPrint);
DataGridViewButtonColumn btnPrintDel = new DataGridViewButtonColumn();
btnPrintDel.Name = "btnPrintDel";
btnPrintDel.HeaderText = "操作";
btnPrintDel.DefaultCellStyle.NullValue = "删除";
dataGridView3.Columns.Add(btnPrintDel);
#endregion
}
//在指定的Access数据库中穿点指定的表格
public static bool CreateAccessTable(string filePath, string tbName, List <ADOX.ColumnClass> columns)
{
ADOX.Catalog catalog = new Catalog();
//数据库文件不存在则创建
try
{
ADODB.Connection cn = new ADODB.Connection();
try
{
cn.Open(connStr, null, null, -1);
}
catch (System.Exception ex)
{
Trace.TraceWarning("Access连接打开失败", ex);
return(false);
}
catalog.ActiveConnection = cn;
ADOX.Table table = new ADOX.Table();
table.ParentCatalog = catalog;
table.Name = tbName;
//主键
ADOX.ColumnClass DrugsID = new ADOX.ColumnClass();
DrugsID.ParentCatalog = catalog;
DrugsID.Type = ADOX.DataTypeEnum.adInteger; // 必须先设置字段类型
DrugsID.Name = "DrugsID";
DrugsID.Properties["Jet OLEDB:Allow Zero Length"].Value = false;
DrugsID.Properties["AutoIncrement"].Value = true;
DrugsID.Type = ADOX.DataTypeEnum.adInteger;
table.Columns.Append(DrugsID, ADOX.DataTypeEnum.adInteger, 0);
foreach (var column in columns)
{
table.Columns.Append(column); //默认数据类型和字段大小
}
//主键
table.Keys.Append("PrimaryKey", ADOX.KeyTypeEnum.adKeyPrimary, "DrugsID", "", "");
catalog.Tables.Append(table);
//accessConnection = new OleDbConnection(cn.ConnectionString);
//tableName = tbName;
//try
//{
// accessConnection.Open();
//}
//catch (System.Exception ex)
//{
// Trace.TraceWarning("Access连接打开失败", ex);
// return false;
//}
cn.Close();
}
catch (System.Exception e)
{
Trace.TraceWarning("创建Access表出错", e);
return(false);
}
return(true);
}
public static bool CreateAccessTable(string FilePath, string tbName)
{
ADOX.Catalog catalog = new Catalog();
List <string> colums = new List <string>();
//数据库文件不存在则创建
if (!File.Exists(FilePath))
{
try
{
catalog.Create("Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" + FilePath + ";Jet OLEDB:Engine Type=5");
}
catch (System.Exception e)
{
Trace.TraceWarning("创建Access表出错", e);
return(false);
}
}
ADODB.Connection cn = new ADODB.Connection();
try
{
cn.Open("Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" + FilePath, null, null, -1);
if (accessConnection == null)
{
accessConnection = new OleDbConnection(cn.ConnectionString);
}
}
catch (System.Exception ex)
{
Trace.TraceWarning("Access连接打开失败", ex);
return(false);
}
try
{
catalog.ActiveConnection = cn;
ADOX.Table table = new ADOX.Table();
table.ParentCatalog = catalog;
table.Name = tbName;
if (tbName == "ProjectConfig")
//公共字段
{
colums.Insert(0, "ProjectName");
colums.Insert(1, "GUIDSTR");
colums.Insert(2, "ProductName");
colums.Insert(3, "ProductGN");
colums.Insert(4, "ProductUse");
colums.Insert(5, "ExperTime");
colums.Insert(6, "ExperAddress");
colums.Insert(7, "ProjectPath");
}
if (tbName == "ExpermentParam")
{
colums.Insert(0, "GUIDSTR"); // 1加速 0 寿命
colums.Insert(1, "ExpermentType"); // 1加速 0 寿命
colums.Insert(2, "YLType");
colums.Insert(3, "JSMode");
colums.Insert(4, "YLSetType");
colums.Insert(5, "CGTemperature");
colums.Insert(6, "CGXDSD");
colums.Insert(7, "CPJXTemperature");
colums.Insert(8, "CPJXXDSD");
colums.Insert(9, "YLNumber");
colums.Insert(10, "YBNumber");
colums.Insert(11, "JWType");
colums.Insert(12, "CGZD");
colums.Insert(13, "CGDYL");
colums.Insert(14, "JXZD");
colums.Insert(15, "JXDYL");
}
if (tbName == "ExpermentDesign")
{
colums.Insert(0, "GUIDSTR"); // 1加速 0 寿命
colums.Insert(1, "Temperature"); // 1加速 0 寿命
colums.Insert(2, "SD");
colums.Insert(3, "YBNumber");
colums.Insert(4, "ExpermentTime");
}
if (tbName == "ChannelInfo")
{
colums.Insert(0, "GUIDSTR"); //
colums.Insert(1, "IndexStr"); //
colums.Insert(2, "FucntionName");
colums.Insert(3, "FunctionType");
colums.Insert(4, "XianHao");
colums.Insert(5, "Duanzi");//
colums.Insert(6, "Hz");
colums.Insert(7, "PerCount");
colums.Insert(8, "IsFunctionSelect");
}
if (tbName == "YBChannelInfo") //样本通道分配
{
colums.Insert(0, "GUIDSTR"); //
colums.Insert(1, "IndexStr"); //
colums.Insert(2, "FunctionName");
colums.Insert(3, "ChannelNumber");
colums.Insert(4, "YBNumber");
colums.Insert(5, "InfoType");
}
if (tbName == "YBSetSignle") //样本通道分配
{
colums.Insert(0, "GUIDSTR"); //
colums.Insert(1, "GNFunction"); //
colums.Insert(2, "TimeList");
colums.Insert(3, "ValueList");
colums.Insert(4, "YBList");
colums.Insert(5, "SetType");
}
if (tbName == "YBSetOutSignle") //样本通道分配
{
colums.Insert(0, "GUIDSTR"); //
colums.Insert(1, "GNFunction"); //
colums.Insert(2, "TimeList");
colums.Insert(3, "ValueList");
colums.Insert(4, "YBList");
colums.Insert(5, "SetType");
}
if (tbName == "YBSetAlarm") //样本通道分配
{
colums.Insert(0, "GUIDSTR"); //
colums.Insert(1, "GNFunction"); //
colums.Insert(2, "AlarmH");
colums.Insert(3, "AlarmL");
colums.Insert(4, "AlarmData");
colums.Insert(5, "AlarmAbs");
colums.Insert(6, "AlarmTimeAbs");
}
foreach (var column in colums)
{
ADOX.ColumnClass col = new ADOX.ColumnClass
{
ParentCatalog = catalog,
Name = column,
Attributes = ColumnAttributesEnum.adColNullable //允许空值
};
table.Columns.Append(col, DataTypeEnum.adVarWChar, 50); //默认数据类型和字段大小
}
catalog.Tables.Append(table);
accessConnection = new OleDbConnection(cn.ConnectionString);
TableName = tbName;
try
{
accessConnection.Open();
}
catch (System.Exception ex)
{
Trace.TraceWarning("Access连接打开失败", ex);
return(false);
}
cn.Close();
}
catch {
}
return(true);
}
