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;
}
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 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 button2_Click(object sender, EventArgs e)
{
//创建access数据库
#region
if (textBox_Excel.Text.Length == 0)
{
MessageBox.Show("请选择目标Excel文件");
}
SaveFileDialog AccessSaveFileDialog = new SaveFileDialog();
AccessSaveFileDialog.Title = "创建Access文件";
AccessSaveFileDialog.Filter = "Access文件（*.accdb）|*.accdb";
#endregion
//获取access数据库路径
#region
if (File.Exists(textBox_Excel.Text))
{
AccessSaveFileDialog.FileName = System.IO.Path.GetFileNameWithoutExtension(textBox_Excel.Text);
}
if (AccessSaveFileDialog.ShowDialog() == DialogResult.OK)
{
textBox_Access.Text = AccessSaveFileDialog.FileName;
form2.database_path = textBox_Access.Text;
form3.database_path = textBox_Access.Text;
}
#endregion
//Access创建
//Access表构造
#region
//不存在则创建
//获取excel表名作为access表中字段名
//excel_sheetname数组存放表名
OleDbConnection conn_excel = new OleDbConnection
("Provider=Microsoft.ACE.OLEDB.12.0;" + "Data Source="
+ textBox_Excel.Text + ";" + "Extended Properties=Excel 8.0;");
conn_excel.Open();
DataTable dt_sheetname = conn_excel.GetOleDbSchemaTable(OleDbSchemaGuid.Tables, null);
string [] excel_sheetname = new string[dt_sheetname.Rows.Count];
int sheets_number = 0;
foreach (DataRow row in dt_sheetname.Rows)
{
excel_sheetname[sheets_number] =
row["TABLE_NAME"].ToString().Replace("'", "").Replace("$", "");
form2.comboBox1.Items.Add(excel_sheetname[sheets_number]);
sheets_number++;
}//获取excel表名完毕
form3.sheet_name = excel_sheetname;
//开始创建access
ADOX.Catalog catalog = new Catalog();
catalog.Create("Provider=Microsoft.Jet.OLEDB.4.0;Data Source="
+ AccessSaveFileDialog.FileName + ";" + "Jet OLEDB:Engine Type=5");
ADODB.Connection ado_conn = new ADODB.Connection();
ado_conn.Open("Provider=Microsoft.Jet.OLEDB.4.0;Data Source="
+ AccessSaveFileDialog.FileName, null, null, -1);
catalog.ActiveConnection = ado_conn;
ADOX.TableClass tb = new ADOX.TableClass();
tb.Name = "监测点坐标";
tb.Columns.Append("监测点编号");
tb.Columns.Append("X坐标");
tb.Columns.Append("Y坐标");
catalog.Tables.Append(tb);
for (int i = 0; i < excel_sheetname.Length - 1; i++)
{
ADOX.TableClass TB = new ADOX.TableClass();
TB.Name = excel_sheetname[i];
TB.Columns.Append("监测点编号");
TB.Columns.Append("X坐标");
TB.Columns.Append("Y坐标");
TB.Columns.Append("高程值");
catalog.Tables.Append(TB);
}
//access数据库构造完毕
#endregion
//向数据库中录入数据
#region
//打开“坐标”表，先输入所有点号和对应坐标
string sql_coordinate_sheet_open = "select*from[坐标$]";
OleDbDataAdapter ada_coordinate_sheet_open = new OleDbDataAdapter
(sql_coordinate_sheet_open, conn_excel);
DataTable dt_coordinate_sheet_open = new DataTable();
ada_coordinate_sheet_open.Fill(dt_coordinate_sheet_open);
form3.point_number = dt_coordinate_sheet_open.Rows.Count;
string sql_conn_access = "Provider=Microsoft.ACE.OLEDB.12.0;" + "Data Source="
+ textBox_Access.Text + ";" + "Persist Security Info=False;";
OleDbConnection conn_access = new OleDbConnection(sql_conn_access);
conn_access.Open();
//设置进度条
progressBar1.Value = 0;
progressBar1.Maximum = dt_coordinate_sheet_open.Rows.Count;
label4.Text = "正在导入监测点坐标...";
try
{
for (int i = 0; i < dt_coordinate_sheet_open.Rows.Count; i++)
{
string sql_add_into_access = "insert into 监测点坐标(监测点编号,X坐标,Y坐标) values ('"
+ Convert.ToDouble(dt_coordinate_sheet_open.Rows[i][0])
+ "','" + Convert.ToDouble(dt_coordinate_sheet_open.Rows[i][2])
+ "','" + Convert.ToDouble(dt_coordinate_sheet_open.Rows[i][3]) + "') ";
OleDbCommand com = new OleDbCommand(sql_add_into_access, conn_access);
com.ExecuteNonQuery();
progressBar1.Value++;
}
}
catch
{
MessageBox.Show("点坐标导入失败!");
}
//导入高程值
label4.Text = "正在导入各期高程测值...";
progressBar1.Value = 0;
progressBar1.Maximum = excel_sheetname.Length - 1;
for (int i = 0; i < excel_sheetname.Length - 1; i++)
{
string sql_height_from_excel = "select 监测点编号,横坐标,纵坐标,高程值 from[" + excel_sheetname[i] + "$]";
OleDbDataAdapter Ada = new OleDbDataAdapter(sql_height_from_excel, conn_excel);
DataTable DT = new DataTable();
Ada.Fill(DT);
//导入access数据库
for (int j = 0; j < DT.Rows.Count; j++)
{
string sql_insert_height_into_access = "insert into " + excel_sheetname[i] + "(监测点编号,X坐标,Y坐标,高程值) values ('" + Convert.ToDouble(DT.Rows[j][0]) + "','" + Convert.ToDouble(DT.Rows[j][1]) + "','" + Convert.ToDouble(DT.Rows[j][2]) + "','" + Convert.ToDouble(DT.Rows[j][3]) + "') ";
OleDbCommand COM = new OleDbCommand(sql_insert_height_into_access, conn_access);
COM.ExecuteNonQuery();
//progressBar1.Value++;
}
progressBar1.Value++;
//MessageBox.Show("导入完毕！");
}
label4.Text = "数据导入完毕！";
ado_conn.Close();
conn_excel.Close();
conn_access.Close();
#endregion
}
