/// <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> /// 新增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(); } }
/// <summary> /// 创建FID表填充信息 /// </summary> /// <param name="sender"></param> /// <param name="e"></param> private void buttonXGenerateFID_Click(object sender, EventArgs e) { this._DesDSName = comboBoxEx2.Text; //构建FID if (comboBoxEx1.SelectedIndex == 0) //本地辅助库 { if (File.Exists(textBoxXServer.Text)) { SysCommon.Error.ErrorHandle.ShowFrmErrorHandle("提示", "数据文件已存在!\n" + textBoxXServer.Text); return; } //创建mdb文件 ADOX.Catalog AdoxCatalog = new ADOX.Catalog(); AdoxCatalog.Create("Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" + textBoxXServer.Text + ";"); ADOX.TableClass tbl = new ADOX.TableClass(); tbl.ParentCatalog = AdoxCatalog; tbl.Name = "FID记录表"; ADOX.ColumnClass col = new ADOX.ColumnClass(); col.ParentCatalog = AdoxCatalog; col.Type = ADOX.DataTypeEnum.adInteger; // 必须先设置字段类型 col.Name = "GOFID"; col.Properties["Jet OLEDB:Allow Zero Length"].Value = false; col.Properties["AutoIncrement"].Value = true; tbl.Columns.Append(col, ADOX.DataTypeEnum.adInteger, 0); col = new ADOX.ColumnClass(); col.ParentCatalog = AdoxCatalog; col.Type = ADOX.DataTypeEnum.adLongVarWChar; col.Name = "FCNAME"; col.Properties["Jet OLEDB:Allow Zero Length"].Value = false; tbl.Columns.Append(col, ADOX.DataTypeEnum.adInteger, 0); col = new ADOX.ColumnClass();//增加一个本地OID字段 col.ParentCatalog = AdoxCatalog; col.Type = ADOX.DataTypeEnum.adInteger; col.Name = "OID"; col.Properties["Jet OLEDB:Allow Zero Length"].Value = false; tbl.Columns.Append(col, ADOX.DataTypeEnum.adInteger, 0); AdoxCatalog.Tables.Append(tbl); string pConn = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" + textBoxXServer.Text + ";Persist Security Info=False;"; GeoFIDCreator FIDCreator = new GeoFIDCreator(this._UpadateDesWorkspace, pConn, FIDDBType.access); FIDCreator.DatasetName = this._DesDSName; if (FIDCreator.CreateFID("FID记录表") == false) { SysCommon.Error.ErrorHandle.ShowInform("提示", "创建业务信息表失败!"); return; } } else if (comboBoxEx1.SelectedIndex == 1) //远程辅助库 { string Conn = "Data Source=" + textBoxXServer.Text + ";User Id=" + textBoxXUser.Text + ";Password="******";"; GeoFIDCreator FIDCreator = new GeoFIDCreator(this._UpadateDesWorkspace, Conn, FIDDBType.oracle); FIDCreator.DatasetName = this._DesDSName; if (FIDCreator.CreateFID("FID记录表") == false) { SysCommon.Error.ErrorHandle.ShowInform("提示", "创建业务信息表失败!"); return; } } ///将FID记录表的访问方式写入xml文档对象 /// DevComponents.AdvTree.Node pCurNode = m_Hook.ProjectTree.SelectedNode; ///获得树图上选择的工程节点 string pProjectname = pCurNode.Name; System.Xml.XmlNode Projectnode = m_Hook.DBXmlDocument.SelectSingleNode("工程管理/工程[@名称='" + pProjectname + "']"); System.Xml.XmlElement ProjectNodeElement = Projectnode as System.Xml.XmlElement; System.Xml.XmlElement ProjectConnEle = ProjectNodeElement.SelectSingleNode(".//FID记录表/连接信息") as System.Xml.XmlElement; System.Xml.XmlElement ProjectDBDSConnEle = ProjectNodeElement.SelectSingleNode(".//现势库/连接信息/库体") as System.Xml.XmlElement; ProjectDBDSConnEle.SetAttribute("名称", comboBoxEx2.Text); ///设置数据库连接类型 /// if (this.comboBoxEx1.SelectedIndex == 0) { ProjectConnEle.SetAttribute("类型", "Access"); } else if (this.comboBoxEx1.SelectedIndex == 1) { ProjectConnEle.SetAttribute("类型", "Oracle"); } ///设置具体连接方式 /// if (this.comboBoxEx1.SelectedIndex == 0) { string text = textBoxXServer.Text; ProjectConnEle.SetAttribute("数据库", text); } else if (this.comboBoxEx1.SelectedIndex == 1) { ProjectConnEle.SetAttribute("数据库", textBoxXServer.Text); ProjectConnEle.SetAttribute("用户", textBoxXUser.Text); ProjectConnEle.SetAttribute("密码", textBoxXPassword.Text); } m_Hook.DBXmlDocument.Save(ModData.v_projectXML); //释放类成员 if (_UpadateDesWorkspace != null) { System.Runtime.InteropServices.Marshal.ReleaseComObject(_UpadateDesWorkspace); } SysCommon.Error.ErrorHandle.ShowFrmErrorHandle("提示", "业务维护信息初始化完成!"); this.Close(); }
public static void CreateDatabase(string connectionString) { try { // Create DB in form of .mdb file ADOX.CatalogClass catalog = new ADOX.CatalogClass(); catalog.Create(connectionString); ADOX.Table table = new ADOX.Table(); table.Name = "Files"; // Table name // Id column ADOX.ColumnClass idCol = new ADOX.ColumnClass() { Name = "Id", ParentCatalog = catalog, Type = ADOX.DataTypeEnum.adInteger, }; idCol.Properties["AutoIncrement"].Value = true; // Name column ADOX.ColumnClass nameCol = new ADOX.ColumnClass() { Name = "Name", ParentCatalog = catalog, Type = ADOX.DataTypeEnum.adVarWChar, DefinedSize = 16, }; // FileData column (BLOBs) ADOX.ColumnClass fileCol = new ADOX.ColumnClass() { Name = "FileData", ParentCatalog = catalog, Type = ADOX.DataTypeEnum.adLongVarBinary }; // Add columns to Files table table.Columns.Append(idCol); table.Columns.Append(nameCol); table.Columns.Append(fileCol); // Add table to .mdb catalog catalog.Tables.Append(table); // Close the connection ADODB.Connection con = (ADODB.Connection)catalog.ActiveConnection; if (con != null && con.State != 0) con.Close(); } catch (Exception ex) { throw ex; } }