/// <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; }
}
