private void Button_Click(object sender, RoutedEventArgs e)
{
//创建数据库文件
ADOX.Catalog catalog = new Catalog();
catalog.Create("Provider=Microsoft.Jet.OLEDB.4.0;Data Source=d:\\test.mdb;Jet OLEDB:Engine Type=5");
Debug.WriteLine("DataBase created");
//建表
ADODB.Connection cn = new ADODB.Connection();
cn.Open("Provider=Microsoft.Jet.OLEDB.4.0;Data Source=d:\\test.mdb", null, null, -1);
catalog.ActiveConnection = cn;
ADOX.Table table = new ADOX.Table();
table.Name = "FirstTable";
ADOX.Column column = new ADOX.Column();
column.ParentCatalog = catalog;
column.Name = "RecordId";
column.Type = DataTypeEnum.adInteger;
column.DefinedSize = 9;
column.Properties["AutoIncrement"].Value = true;
table.Columns.Append(column, DataTypeEnum.adInteger, 9);
table.Keys.Append("FirstTablePrimaryKey", KeyTypeEnum.adKeyPrimary, column, null, null);
table.Columns.Append("CustomerName", DataTypeEnum.adVarWChar, 50);
table.Columns.Append("Age", DataTypeEnum.adInteger, 9);
table.Columns.Append("Birthday", DataTypeEnum.adDate, 0);
catalog.Tables.Append(table);
cn.Close();
}
/// <summary>
/// Création de la base de données Acces
/// </summary>
/// <param name="fileName"></param>
/// <returns></returns>
public bool CreateAccessDatabase()
{
bool result = false;
ADOX.Catalog cat = new ADOX.Catalog();
ADOX.Table table = new ADOX.Table();
//Create the table and it's fields.
table.Name = "Table1";
table.Columns.Append("Field1");
table.Columns.Append("Field2");
try
{
cat.Create("Provider=Microsoft.Jet.OLEDB.4.0;" + "Data Source=" + this.dataBaseName + ".accdb; Jet OLEDB:Engine Type=5");
cat.Tables.Append(table);
//Now Close the database
ADODB.Connection con = cat.ActiveConnection as ADODB.Connection;
if (con != null)
{
con.Close();
}
result = true;
}
catch (Exception ex)
{
result = false;
}
cat = null;
return(result);
}
/// <summary>
/// 创建表方法
/// </summary>
/// <param name="_TableName">表明</param>
/// <param name="_Hashtable">列名和数据类型</param>
/// <param name="ADOX.Catalog">主键位置</param>
/// <returns>False：创建失败；True创建失败</returns>
private bool CreateTable(string _TableName, List <ColumnStruct> _ListColumnStruct, ADOX.Catalog _Catalog)
{
ADOX.Table _Table = null;
try
{
_Table = new ADOX.Table();
_Table.Name = _TableName;
for (int i = 0; i < _ListColumnStruct.Count; i++)
{
_Table.Columns.Append(_ListColumnStruct[i]._ColumnName, _ListColumnStruct[i]._DataTypeEnum, _ListColumnStruct[i]._ColumnLong);
if (_ListColumnStruct[i]._IsKeyPrimary)
{
_Table.Keys.Append("PrimaryKey", KeyTypeEnum.adKeyPrimary, _ListColumnStruct[i]._ColumnName, "", "");
}
}
_Catalog.Tables.Append(_Table);
return(true);
}
catch (Exception ex)
{
logger.Error(ex.ToString());
}
finally
{
_Table = null;
}
return(false);
}
public static ADOX.Table GetNewAdoxTable(System.Data.DataTable dt, string name)
{
var newTable = new ADOX.Table();
newTable.Name = name;
foreach (DataColumn col in dt.Columns)
{
ADOX.Column dbField = new Column();
dbField.Name = FunRepository.GetCleanAccessObjectName(col.ColumnName);
dbField.Attributes = ColumnAttributesEnum.adColNullable;
switch (col.DataType.ToString())
{
case "System.String":
case "System.Char":
case "System.Guid": dbField.Type = ADOX.DataTypeEnum.adVarWChar; break; //newTable.Columns.Append(cleanedColumnName, ADOX.DataTypeEnum.adVarWChar);
case "System.DateTime":
case "System.TimeSpan": dbField.Type = ADOX.DataTypeEnum.adDate; break;
case "System.Boolean": dbField.Type = ADOX.DataTypeEnum.adBoolean; break;
default: dbField.Type = ADOX.DataTypeEnum.adDouble; break; /*"System.Double", "System.Decimal","System.Byte","System.Int16","System.Int32","System.Int64","System.SByte","System.Single","System.UInt16","System.UInt32","System.UInt64" */
}
newTable.Columns.Append(dbField);
}
return(newTable);
}
//创建数据表
public bool CreateAccessTable(string FilePath, string tableName, params ADOX.Column[] colums)
{
bool bolReturn = false;
ADOX.Catalog clg = new Catalog();
//数据库文件存在
try {
if (CreateAccess(FilePath) == true)
{
ADODB.Connection cn = new ADODB.Connection(); //连接已创建的数据库文件
cn.Open("Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" + FilePath, null, null, -1);
clg.ActiveConnection = cn; //打开已创建的数据库文件
ADOX.Table table1 = new ADOX.Table();
table1.Name = tableName;
foreach (var column in colums)
{
if (column.Name != null)
{
table1.Columns.Append(column);
}
}
clg.Tables.Append(table1);
cn.Close();
bolReturn = true;
}
}catch (Exception ex) {
MessageBox.Show("创建失败\r\n" + ex.ToString(), "提示");
}
return(bolReturn);
}
/// <summary>
/// 在access数据库中创建表
/// </summary>
/// <param name="filePath">数据库表文件全路径如D:\\NewDb.mdb 没有则创建 </param>
/// <param name="tableName">表名</param>
/// <param name="colums">ADOX.Column对象数组</param>
public static bool CreateAccessTable(string filePath, string tableName, params ADOX.Column[] colums)
{
ADOX.Catalog catalog = new Catalog();
//数据库文件不存在则创建
if (!File.Exists(filePath))
{
try
{
catalog.Create("Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" + filePath + ";Jet OLEDB:Engine Type=5");
}
catch (System.Exception ex)
{
return(false);
}
}
ADODB.Connection cn = new ADODB.Connection();
cn.Open("Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" + filePath, null, null, -1);
catalog.ActiveConnection = cn;
ADOX.Table table = new ADOX.Table();
table.Name = tableName;
foreach (var column in colums)
{
table.Columns.Append(column);
}
colums[0].ParentCatalog = catalog;
colums[0].Properties["AutoIncrement"].Value = true; //设置自动增长
table.Keys.Append("FirstTablePrimaryKey", KeyTypeEnum.adKeyPrimary, colums[0], null, null); //定义主键
catalog.Tables.Append(table);
cn.Close();
return(true);
}
private void button3_Click(object sender, EventArgs e)
{
string dbn = System.AppDomain.CurrentDomain.BaseDirectory + "Access_Data\\" + "SpiderResult.mdb";//数据库文件名称
//// 创建数据库文件
File.Delete(dbn);
ADOX.Catalog catalog = new Catalog();
catalog.Create("Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" + dbn + ";Jet OLEDB:Engine Type=5");
ADOX.Table table = new ADOX.Table();
table.Name = "Content";
ADOX.Column column = new ADOX.Column();
column.ParentCatalog = catalog;
column.Name = "ID";
column.Type = DataTypeEnum.adInteger;
column.DefinedSize = 9;
column.Properties["AutoIncrement"].Value = true;
table.Columns.Append(column, DataTypeEnum.adInteger, 9);
table.Keys.Append("PrimaryKey", ADOX.KeyTypeEnum.adKeyPrimary, "ID", "", "");
table.Columns.Append("已采", DataTypeEnum.adBoolean, 0);
table.Columns.Append("已发", DataTypeEnum.adBoolean, 0);
table.Columns.Append("标题", DataTypeEnum.adVarWChar, 0);
table.Columns.Append("内容", DataTypeEnum.adVarWChar, 0);
table.Columns.Append("PageUrl", DataTypeEnum.adVarWChar, 0);
catalog.Tables.Append(table);
MessageBox.Show(string.Format("创建成功"));
}
public void DtaDbCre()
{
if (!File.Exists(@"Data\Data.accdb"))
{
ADOX.Catalog cat = new ADOX.Catalog();
ADOX.Table table = new ADOX.Table();
try
{
cat.Create("Provider=Microsoft.Jet.OLEDB.4.0;" + "Data Source=Data\\Data.accdb; Jet OLEDB:Engine Type=5");
//Now Close the database
ADODB.Connection con = cat.ActiveConnection as ADODB.Connection;
if (con != null)
{
con.Close();
}
//result = true;
}
catch //(Exception ex)
{
}
cat = null;
TblCre();
} //End if
} //End dbCre
/// <summary>
/// Create access database table; True = Success, False = Fail
/// </summary>
/// <typeparam name="T"></typeparam>
/// <param name="table_name"></param>
/// <returns></returns>
public bool CreateTable <T>(string table_name)
{
bool r = true;
CatalogClass cat = openDatabase();
//Get properties of T
Type itemType = typeof(T);
var properties = itemType.GetProperties(BindingFlags.Public | BindingFlags.Instance);
try {
//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);
}
static void Main(string[] args)
{
string username = Environment.GetFolderPath(Environment.SpecialFolder.UserProfile);
if (File.Exists(username + @"\Documents\H.T I.R Aide\Notes\NewMDB.accdb"))
{
File.Delete(username + @"\Documents\H.T I.R Aide\Notes\NewMDB.accdb");
}
ADOX.Catalog cat = new ADOX.Catalog();
ADOX.Table table = new ADOX.Table();
table.Name = "Overview";
table.Columns.Append("Offense Type");
table.Columns.Append("Total Notes");
table.Columns.Append("Percent of Notes");
table.Columns.Append("Number tagged as Closed");
DataTable test = new DataTable();
test.NewRow();
DataColumn column;
DataRow row;
column = new DataColumn();
column.ColumnName = "id";
test.Columns.Add(column);
column = new DataColumn();
column.ColumnName = "hi";
test.Columns.Add(column);
ArrayList hi = new ArrayList();
for (int i = 0; i < 10; i++)
{
row = test.NewRow();
row["id"] = i;
row["hi"] = i + 1;
test.Rows.Add(row);
hi.Add(row["id"] + " " + row["hi"]);
WriteLine(row["id"] + " " + row["hi"]);
}
string[] nope = new string [hi.Count];
hi.CopyTo(nope);
File.WriteAllLines(username + @"\Documents\H.T I.R Aide\Notes\testNewMDB.accdb", nope);
cat.Create("Provider =Microsoft.Jet.OLEDB.4.0;" +
"Data Source=" + username + @"\Documents\H.T I.R Aide\Notes\NewMDB.accdb; " +
"Jet OLEDB:Engine Type=5");
cat.Tables.Append(table);
WriteLine("Database Created Successfully");
ReadKey();
cat = null;
}
//创建数据库
public void createDb(String versionNum)
{
ADOX.Catalog catalog = new ADOX.Catalog();
//String versionNum = extension.Text;
try
{
catalog.Create(databaseCon + otherDbs + versionNum + ".mdb" + ";Jet OLEDB:Engine Type=5");
}
catch { }
ADODB.Connection cn = new ADODB.Connection();
cn.Open(databaseCon + otherDbs + versionNum + ".mdb", null, null, -1);
catalog.ActiveConnection = cn;
//创建表
ADOX.Table table = new ADOX.Table();
table.Name = "config1";
//创建列
ADOX.Column column = new ADOX.Column();
column.ParentCatalog = catalog;
column.Name = "ID";
column.Type = DataTypeEnum.adInteger;
column.DefinedSize = 9;
column.Properties["AutoIncrement"].Value = true;
table.Columns.Append(column, DataTypeEnum.adInteger, 9);
// 设置为主键
table.Keys.Append("FirstTablePrimaryKey", KeyTypeEnum.adKeyPrimary, column, null, null);
table.Columns.Append("fileName", DataTypeEnum.adVarWChar, 0);
table.Columns.Append("fileSize", DataTypeEnum.adInteger, 0);
table.Columns.Append("createTime", DataTypeEnum.adDate, 0);
table.Columns.Append("modifiedTime", DataTypeEnum.adDate, 0);
table.Columns.Append("path", DataTypeEnum.adLongVarWChar, 0);
table.Columns.Append("versionNum", DataTypeEnum.adInteger, 0);
table.Columns.Append("updateMethod", DataTypeEnum.adVarWChar, 0);
try
{
// 添加表
catalog.Tables.Append(table);
}
catch (Exception ex)
{
MessageBox.Show(ex.Message);
}
//此处一定要关闭连接，否则添加数据时候会出错
table = null;
catalog = null;
//Application.DoEvents();
cn.Close();
}
/// <summary>
/// 创建数据库表
/// </summary>
/// <param name="strConnection"></param>
/// <param name="strTableName"></param>
/// <param name="listColumns"></param>
public static void CreatDBTable(string strConnection, string strTableName, List <string> listColumns)
{
ADOX.Catalog catalog = new Catalog();
ADODB.Connection cn = new ADODB.Connection();
strConnection = "Provider=Microsoft.Jet.OLEDB.4.0; Data Source=" + strConnection;
OleDbConnection conn = new OleDbConnection(strConnection);
try
{
conn.Open();
cn.Open(strConnection, null, null, -1);
catalog.ActiveConnection = cn;
if (string.IsNullOrEmpty(strTableName))
{
loghelp.log.Error("表名不能为空!");
return;
}
bool flag = db.dbutils.GetTables(conn, strTableName);
if (!flag)//判断表名是否存在
{
ADOX.Table table = new ADOX.Table();
table.Name = strTableName;
ADOX.Column column = new ADOX.Column();
column.ParentCatalog = catalog;
column.Name = "id";
column.Type = ADOX.DataTypeEnum.adInteger;
column.DefinedSize = 50;
column.Properties["AutoIncrement"].Value = true;
table.Columns.Append(column, DataTypeEnum.adInteger, 50);
table.Keys.Append("FirstTablePrimaryKey", KeyTypeEnum.adKeyPrimary, column, null, null);
foreach (string strColumns in listColumns)
{
table.Columns.Append(strColumns, DataTypeEnum.adVarWChar, 50);
table.Columns[strColumns].Attributes = ColumnAttributesEnum.adColNullable;
}
catalog.Tables.Append(table);
//此处一定要关闭连接，否则添加数据时候会出错
table = null;
catalog = null;
}
}
catch (Exception ex)
{
loghelp.log.Fatal(ex.Message, ex);
}
finally
{
conn.Close();
cn.Close();
}
}
public static void NewDatabase()
{
//CComLibrary.GlobeVal.filesave.SampleDefaultName
if (System.IO.Directory.Exists(Application.StartupPath + "\\mdb") == true)
{
System.IO.Directory.CreateDirectory(Application.StartupPath + "\\mdb");
}
if (File.Exists(Application.StartupPath + "\\mdb\\" + CComLibrary.GlobeVal.filesave.methodname + ".mdb") == true)
{
File.Delete(Application.StartupPath + "\\mdb\\" + CComLibrary.GlobeVal.filesave.methodname + ".mdb");
}
ADOX.Catalog catalog = new Catalog();
catalog.Create("Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" + Application.StartupPath + "\\mdb\\" + CComLibrary.GlobeVal.filesave.methodname + ".mdb;" + "Jet OLEDB:Engine Type=5");
ADODB.Connection cn = new ADODB.Connection();
cn.Open("Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" + Application.StartupPath + "\\mdb\\" + CComLibrary.GlobeVal.filesave.methodname + ".mdb", null, null, -1);
catalog.ActiveConnection = cn;
ADOX.Table table = new ADOX.Table();
table.Name = "FirstTable";
ADOX.Column column = new ADOX.Column();
column.ParentCatalog = catalog;
column.Name = "RecordId";
column.Type = DataTypeEnum.adInteger;
column.DefinedSize = 9;
column.Properties["AutoIncrement"].Value = true;
table.Columns.Append(column, DataTypeEnum.adInteger, 9);
table.Keys.Append("FirstTablePrimaryKey", KeyTypeEnum.adKeyPrimary, column, null, null);
for (int i = 0; i < CComLibrary.GlobeVal.filesave.mdatabaseitemselect.Count; i++)
{
column = new ADOX.Column();
column.Name = CComLibrary.GlobeVal.filesave.mdatabaseitemselect[i].Name;
column.Attributes = ColumnAttributesEnum.adColNullable;
table.Columns.Append(column, DataTypeEnum.adVarWChar, 80);
}
// table.Columns.Append("CustomerName", DataTypeEnum.adVarWChar, 50);
// table.Columns.Append("Age", DataTypeEnum.adInteger, 9);
// table.Columns.Append("生日", DataTypeEnum.adVarWChar, 80);
catalog.Tables.Append(table);
cn.Close();
}
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);
}
public override ADOX.Table GetTable()
{
var table = new ADOX.Table();
table.Name = TableName;
table.Columns.Append("RemoteHost");
table.Columns.Append("RemoteIdentity");
table.Columns.Append("RemoteUser");
table.Columns.Append("Time", DataTypeEnum.adDate);
table.Columns.Append("Method");
table.Columns.Append("URL", DataTypeEnum.adLongVarWChar);
table.Columns.Append("Protocol");
table.Columns.Append("Status", DataTypeEnum.adSmallInt);
table.Columns.Append("BytesSent", DataTypeEnum.adInteger);
table.Columns.Append("Referer", DataTypeEnum.adLongVarWChar);
table.Columns.Append("UserAgent", DataTypeEnum.adLongVarWChar);
// allow all columns to be empty
foreach (ADOX.Column column in table.Columns)
{
column.Attributes = ColumnAttributesEnum.adColNullable;
}
var rhindex = new ADOX.Index();
rhindex.Name = "RemoteHost index";
rhindex.IndexNulls = AllowNullsEnum.adIndexNullsIgnore;
rhindex.Columns.Append("RemoteHost");
table.Indexes.Append(rhindex);
var mindex = new ADOX.Index();
mindex.Name = "Method index";
mindex.IndexNulls = AllowNullsEnum.adIndexNullsIgnore;
mindex.Columns.Append("Method");
table.Indexes.Append(mindex);
var tindex = new ADOX.Index();
tindex.Name = "Time index";
tindex.IndexNulls = AllowNullsEnum.adIndexNullsIgnore;
tindex.Columns.Append("Time", DataTypeEnum.adDate);
table.Indexes.Append(tindex);
var sindex = new ADOX.Index();
sindex.Name = "Status index";
sindex.IndexNulls = AllowNullsEnum.adIndexNullsIgnore;
sindex.Columns.Append("Status", DataTypeEnum.adSmallInt);
table.Indexes.Append(sindex);
return(table);
}
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();
}
static void SetTable(IRow header, IRow dataType, IRow desc, string tableName, ADODB.Connection cn,DataTable dt)
{
Catalog catalog = new Catalog();
catalog.ActiveConnection = cn;
ADOX.Table table = new ADOX.Table();
table.Name = tableName;
UpdateFieldsData(header, dataType, desc, table,dt);
ADOX.Column column = table.Columns[header.Cells[0].StringCellValue];
column.ParentCatalog = catalog;
table.Keys.Append("FirstTablePrimaryKey", KeyTypeEnum.adKeyPrimary, column, null, null);
catalog.Tables.Append(table);
}
//tests
public static void RunAdoxAdorTest()
{
try
{
ADOX.Catalog cat = new ADOX.Catalog();
ADOX.Table tab = new ADOX.Table();
_msg.Length = 0;
_msg.Append("RunAdoxAdorTest started ...");
Program._messageLog.WriteLine(_msg.ToString());
tab.Name = "TestTab1";
tab.Columns.Append("PK1", ADOX.DataTypeEnum.adInteger);
tab.Columns.Append("F1", ADOX.DataTypeEnum.adVarWChar, 30);
tab.Columns.Append("F2", ADOX.DataTypeEnum.adDouble);
tab.Columns.Append("F3", ADOX.DataTypeEnum.adVarBinary);
tab.Columns.Append("F4", ADOX.DataTypeEnum.adBoolean);
tab.Columns.Append("F5", ADOX.DataTypeEnum.adCurrency);
tab.Columns.Append("F6", ADOX.DataTypeEnum.adWChar);
tab.Columns.Append("F7", ADOX.DataTypeEnum.adSmallInt);
tab.Columns.Append("F8", ADOX.DataTypeEnum.adSingle);
//tab.Columns.Append("F9", ADOX.DataTypeEnum.adDecimal,18); //invalid, use double instead
tab.Columns.Append("F9", ADOX.DataTypeEnum.adLongVarBinary);
tab.Columns.Append("F10", ADOX.DataTypeEnum.adLongVarWChar);
tab.Columns.Append("F11", ADOX.DataTypeEnum.adBoolean);
tab.Columns.Append("F12", ADOX.DataTypeEnum.adVarWChar, 1);
tab.Columns.Append("F13", ADOX.DataTypeEnum.adUnsignedTinyInt);
tab.Columns.Append("F14", ADOX.DataTypeEnum.adDate);
//if (File.Exists(@"C:\Testfiles\Access\NewMDB.mdb"))
// File.Delete(@"C:\Testfiles\Access\NewMDB.mdb");
//cat.Create(@"Provider=Microsoft.Jet.OLEDB.4.0;Data Source=C:\Testfiles\Access\NewMDB.mdb;User Id=admin;Password=;Jet OLEDB:Engine Type=5");
if (File.Exists(@"C:\Testfiles\Access\NewMDB.accdb"))
{
File.Delete(@"C:\Testfiles\Access\NewMDB.accdb");
}
cat.Create(@"Provider=Microsoft.ACE.OLEDB.12.0;Data Source=C:\Testfiles\Access\NewMDB.accdb;User Id=admin;Password=;Jet OLEDB:Engine Type=6");
cat.Tables.Append(tab);
}
catch (System.Exception ex)
{
_msg.Length = 0;
_msg.Append(AppGlobals.AppMessages.FormatErrorMessage(ex));
Program._messageLog.WriteLine(_msg.ToString());
AppMessages.DisplayErrorMessage(_msg.ToString(), _saveErrorMessagesToAppLog);
}
finally
{
_msg.Length = 0;
_msg.Append("... RunAdoxAdorTest finished.");
Program._messageLog.WriteLine(_msg.ToString());
}
}
public void UpgradeDatabase()
{
var cat = new Catalog();
try
{
_myAccessConn.Open();
cat.ActiveConnection = _myAccessConn;
var mytable = new ADOX.Table {Name = "tblCustomerSettings"};
var myColumn = new ADOX.Column
{
Name = "CustomerSettingID",
Type = DataTypeEnum.adInteger,
ParentCatalog = cat
};
myColumn.Properties["Autoincrement"].Value = true;
mytable.Columns.Append(myColumn);
mytable.Columns.Append("CustomerID", DataTypeEnum.adInteger);
mytable.Columns.Append("BillingMethodID", DataTypeEnum.adInteger);
mytable.Columns.Append("MobileCarrierID",DataTypeEnum.adInteger);
cat.Tables.Append(mytable);
var mykey = new ADOX.Key
{
Name = "PrimaryKey",
Type = KeyTypeEnum.adKeyPrimary,
RelatedTable = "tblCustomerSettings"
};
mykey.Columns.Append("CustomerSettingID");
cat.Tables["tblCustomerSettings"].Keys.Append(mykey);
//Add data for new table
InitializeCustomerSettings(_myAccessConn);
//Upddate the Database Version
UpdateDBVersion(_myAccessConn);
foreach (Table tbl in cat.Tables)
{
var myname = tbl.Name;
}
}
catch (Exception ex)
{
Console.WriteLine("Error: Failed to retrieve the required data from the DataBase.\n{0}", ex.Message);
}
finally
{
_myAccessConn.Close();
}
}
public override ADOX.Table GetTable()
{
var table = new ADOX.Table();
table.Name = TableName;
table.Columns.Append("RemoteHost");
table.Columns.Append("RemoteIdentity");
table.Columns.Append("RemoteUser");
table.Columns.Append("Time", DataTypeEnum.adDate);
table.Columns.Append("Method");
table.Columns.Append("URL", DataTypeEnum.adLongVarWChar);
table.Columns.Append("Protocol");
table.Columns.Append("Status", DataTypeEnum.adSmallInt);
table.Columns.Append("BytesSent", DataTypeEnum.adInteger);
table.Columns.Append("Referer", DataTypeEnum.adLongVarWChar);
table.Columns.Append("UserAgent", DataTypeEnum.adLongVarWChar);
// allow all columns to be empty
foreach (ADOX.Column column in table.Columns)
{
column.Attributes = ColumnAttributesEnum.adColNullable;
}
var rhindex = new ADOX.Index();
rhindex.Name = "RemoteHost index";
rhindex.IndexNulls = AllowNullsEnum.adIndexNullsIgnore;
rhindex.Columns.Append("RemoteHost");
table.Indexes.Append(rhindex);
var mindex = new ADOX.Index();
mindex.Name = "Method index";
mindex.IndexNulls = AllowNullsEnum.adIndexNullsIgnore;
mindex.Columns.Append("Method");
table.Indexes.Append(mindex);
var tindex = new ADOX.Index();
tindex.Name = "Time index";
tindex.IndexNulls = AllowNullsEnum.adIndexNullsIgnore;
tindex.Columns.Append("Time", DataTypeEnum.adDate);
table.Indexes.Append(tindex);
var sindex = new ADOX.Index();
sindex.Name = "Status index";
sindex.IndexNulls = AllowNullsEnum.adIndexNullsIgnore;
sindex.Columns.Append("Status", DataTypeEnum.adSmallInt);
table.Indexes.Append(sindex);
return table;
}
/// <summary>
/// 在access数据库中创建表
/// </summary>
/// <param name="filePath">数据库表文件全路径</param>
/// <param name="tableName">表名</param>
/// <param name="colums">ADOX.Column对象数组</param>
public void CreateAccessTable(string filePath, string tableName, params ADOX.Column[] colums)
{
ADOX.Catalog catalog = new Catalog();
//数据库文件不存在则创建
if (!File.Exists(filePath))
{
//创建数据库
//catalog.Create("Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" + filePath + ";Jet OLEDB:Engine Type=5");
catalog.Create("Provider=Microsoft.ACE.OLEDB.12.0;Data Source=" + filePath + ";Jet OLEDB:Engine Type=5");
}
//创建连接
ADODB.Connection cn = new ADODB.Connection();
//打开连接
//cn.Open("Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" + filePath, null, null, -1);
cn.Open("Provider=Microsoft.ACE.OLEDB.12.0;Data Source=" + filePath, null, null, -1);
catalog.ActiveConnection = cn;
//创建一个表格
ADOX.Table table = new ADOX.Table
{
//获取表名
Name = tableName
};
//遍历一个字段的集合，从而添加字段
foreach (var column in colums)
{
//如果不是bool类型的，可以为空
if (column.Type != DataTypeEnum.adBoolean)
{
//允许空值
column.Attributes = ColumnAttributesEnum.adColNullable;
}
//保存字段
table.Columns.Append(column);
}
//定义主键
//主要解释一下第三个参数：你设置为主键的名称
//这里默认为：id
table.Keys.Append("FirstTablePrimaryKey", KeyTypeEnum.adKeyPrimary, colums[0], tableName, null);
//向数据中添加表
catalog.Tables.Append(table);
//关闭连接
cn.Close();
catalog = null; GC.Collect();
//设置自动增长
//column.Properties["AutoIncrement"].Value = true;
}
public MainWindow()
{
InitializeComponent();
//创建mdb文件
ADOX.Catalog catalog = new Catalog();
string filePath = "D:\\test.mdb";
if (!File.Exists(filePath))
{
try
{
catalog.Create("Provider=Microsoft.ACE.OLEDB.12.0;Data Source=" + filePath);
//catalog.Create("Provider=Microsoft.Jet.OLEDB.4.0;Data Source="+filePath);
//创建表
ADODB.Connection cn = new ADODB.Connection();
//cn.Open("Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" + filePath, null, null, -1);
cn.Open("Provider=Microsoft.ACE.OLEDB.12.0;Data Source=" + filePath, null, null, -1);
catalog.ActiveConnection = cn;
ADOX.Table table = new ADOX.Table();
//创建表
table.Name = "result";
//创建列
ADOX.Column column = new ADOX.Column();
//ParentCatalog,指定表、用户或列对象的父目录，以提供对特定于访问接口的属性的访问。
column.ParentCatalog = catalog;
column.Name = "id";
column.Type = DataTypeEnum.adInteger;
column.DefinedSize = 9;
//属性为自增，每次追加自动增加
column.Properties["AutoIncrement"].Value = true;
table.Columns.Append(column, DataTypeEnum.adInteger, 9);
table.Keys.Append("FirstTablePrimaryKey", KeyTypeEnum.adKeyPrimary, column, null, null);
table.Columns.Append("input", DataTypeEnum.adBigInt, 50);
table.Columns.Append("sum", DataTypeEnum.adBigInt, 50);
catalog.Tables.Append(table);
}
catch (System.Exception e)
{
MessageBox.Show(e.ToString());
}
}
}
private Boolean CreateJZDB(String conn)
{
ADOX.Catalog catalog = new Catalog();
ADOX.Table table = null;
ADODB.Connection cn = new ADODB.Connection();
Boolean flag = false;
try
{
catalog.Create(conn);
cn.Open(conn, null, null, -1);
catalog.ActiveConnection = cn;
//新建表
table = new ADOX.Table();
table.Name = "sys_update";
table.ParentCatalog = catalog;
ADOX.Column column = new ADOX.Column();
column.ParentCatalog = catalog;
column.Type = DataTypeEnum.adVarWChar;
column.Name = "ID";
table.Columns.Append(column, DataTypeEnum.adVarWChar, 36);
//设置主键
table.Keys.Append("PrimaryKey", KeyTypeEnum.adKeyPrimary, "ID", "", "");
table.Columns.Append("FileName", DataTypeEnum.adVarWChar, 50);
table.Columns.Append("FileType", DataTypeEnum.adSmallInt, 9);
table.Columns.Append("CreatedServerTime", DataTypeEnum.adVarWChar, 50);
table.Columns.Append("FileState", DataTypeEnum.adSmallInt, 9);
catalog.Tables.Append(table);
flag = true;
}
catch (Exception ex)
{
logger.Error(ex.Message);
}
finally
{
table = null;
catalog = null;
cn.Close();
}
return(flag);
}
/// <summary>
/// 建立 优惠劵 数据库
/// </summary>
public static void Coupons_InitDataBase()
{
// string sqlstr = "INSERT INTO WX_Coupons(CUserName, CUserPhone, CUserqq, WXOpenID) VALUES (N'" + CUserName + "',N'" + CUserPhone + "',N'" + CUserqq + "',N'" + Session["WXOpenID"] + "')";
string PathDataBase = System.IO.Path.Combine(SYSTEMDIR, "USER_DIR\\SYSUSER\\Coupons\\db.dll");//"USER_DIR\\SYSUSER\\SYSSET\\" +
if (System.IO.File.Exists(PathDataBase) == true )
{
return;
}
try
{
ADOX.Catalog catalog = new Catalog();
catalog.Create("Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" + PathDataBase + ";Jet OLEDB:Engine Type=5");
ADODB.Connection cn = new ADODB.Connection();
cn.Open("Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" + PathDataBase, null, null, -1);
catalog.ActiveConnection = cn;
if (true)
{
ADOX.Table table = new ADOX.Table();
table.Name = "WX_Coupons";
ADOX.Column column = new ADOX.Column();
column.ParentCatalog = catalog;
column.Name = "WXOpenID";
column.Type = DataTypeEnum.adInteger;
column.DefinedSize = 7;
column.Properties["AutoIncrement"].Value = true;
table.Columns.Append(column, DataTypeEnum.adInteger, 7);
table.Keys.Append("FirstTablePrimaryKey", KeyTypeEnum.adKeyPrimary, column, null, null);
// string sqlstr = "INSERT INTO WX_Coupons(CUserName, CUserPhone, CUserqq, WXOpenID) VALUES (N'" + CUserName + "',N'" + CUserPhone + "',N'" + CUserqq + "',N'" + Session["WXOpenID"] + "')";
table.Columns.Append("CUserName", DataTypeEnum.adVarWChar, 200);
table.Columns.Append("CUserPhone", DataTypeEnum.adVarWChar, 200);
table.Columns.Append("CUserqq", DataTypeEnum.adVarWChar, 200);
catalog.Tables.Append(table);
}
cn.Close();
}
catch
{
}
}
public Boolean CreateAccessDatabase()
{
bool result = false;
ADOX.Catalog cat = new ADOX.Catalog();
ADOX.Table table = new ADOX.Table();
//Create the table and it's fields.
table.Name = "UserInfo";
table.Columns.Append("username", ADOX.DataTypeEnum.adVarWChar, 40);
table.Columns.Append("password", ADOX.DataTypeEnum.adVarWChar, 40);
table.Columns.Append("age", ADOX.DataTypeEnum.adInteger, 2);
table.Columns.Append("gender", ADOX.DataTypeEnum.adVarWChar, 6);
table.Columns.Append("occupation", ADOX.DataTypeEnum.adVarWChar, 40);
table.Columns.Append("income", ADOX.DataTypeEnum.adDouble, 10);
table.Keys.Append("Primary Key", ADOX.KeyTypeEnum.adKeyPrimary, "password", "", "");
try
{
cat.Create("Provider=Microsoft.Jet.OLEDB.4.0;" + @"Data Source=C:\Users\Umer\Documents\Visual Studio 2015\Projects\masterpage\" + "UserInfo.mdb" + "; Jet OLEDB:Engine Type=5");
//cat.Columns.Append("col1", DataTypeEnum.adInteger, 4);
cat.Tables.Append(table);
//Now Close the database
ADODB.Connection con = cat.ActiveConnection as ADODB.Connection;
if (con != null)
{
con.Close();
}
result = true;
}
catch (Exception ex)
{
Console.WriteLine(ex);
// Namebox.Text = ex.ToString();
result = false;
}
cat = null;
return(result);
}
/// <summary>arma la tabla excel</summary>
/// <param name="strFileName">strfileName</param>
/// <param name="extencion">extension</param>
/// <returns>strTables</returns>
/// <example>
/// <code>public static string[] GetTableExcel(string strFileName, string extencion)
/// {
/// string[] strTables = new string[100];
/// Catalog oCatlog = new Catalog();
/// ADOX.Table oTable = new ADOX.Table();
/// ADODB.Connection oConn = new ADODB.Connection();
/// if (extencion == ".xls")
/// oConn.Open("Provider=Microsoft.Jet.OleDb.4.0; Data Source = " + strFileName + "; Extended Properties = \"Excel 8.0;HDR=Yes;IMEX=1\";", "", "", 0);
/// if (extencion == ".xlsx")
/// {
/// try
/// {
/// oConn.Open("Provider=Microsoft.ACE.OLEDB.12.0;Data Source=" + strFileName + "; Jet OLEDB:Engine Type=5;Extended Properties='Excel 12.0;HDR=YES;IMEX=1';", "", "", 0);
/// }
/// catch (Exception e)
/// {
/// Global.setError(e.Message);
/// log.Error(e.Message);
/// throw;
/// }
///
/// }
///
///
/// oCatlog.ActiveConnection = oConn;
/// if (oCatlog.Tables.Count > 0)
/// {
/// int item = 0;
/// foreach (ADOX.Table tab in oCatlog.Tables)
/// {
/// if (tab.Type == "TABLE")
/// {
/// strTables[item] = tab.Name;
/// item++;
/// }
/// }
/// }
/// oConn.Close();
/// return strTables;
/// }</code>
/// </example>
public static string[] GetTableExcel(string strFileName, string extencion)
{
string[] strTables = new string[100];
Catalog oCatlog = new Catalog();
ADOX.Table oTable = new ADOX.Table();
ADODB.Connection oConn = new ADODB.Connection();
if (extencion == ".xls")
{
oConn.Open("Provider=Microsoft.Jet.OleDb.4.0; Data Source = " + strFileName + "; Extended Properties = \"Excel 8.0;HDR=Yes;IMEX=1\";", "", "", 0);
}
if (extencion == ".xlsx")
{
try
{
oConn.Open("Provider=Microsoft.ACE.OLEDB.12.0;Data Source=" + strFileName + "; Jet OLEDB:Engine Type=5;Extended Properties='Excel 12.0;HDR=YES;IMEX=1';", "", "", 0);
}
catch (Exception e)
{
Global.setError(e.Message);
log.Error(e.Message);
throw;
}
}
oCatlog.ActiveConnection = oConn;
if (oCatlog.Tables.Count > 0)
{
int item = 0;
foreach (ADOX.Table tab in oCatlog.Tables)
{
if (tab.Type == "TABLE")
{
strTables[item] = tab.Name;
item++;
}
}
}
oConn.Close();
return(strTables);
}
private ADOX.Table CreateFoldersTable(Catalog cat)
{
ADOX.Table table = new ADOX.Table();
//Create the table and it's fields.
table.Name = DTables.Folders;
table.Columns.Append(DFolders.Id, ADOX.DataTypeEnum.adInteger);
table.Columns.Append(DFolders.ProjectId, ADOX.DataTypeEnum.adInteger);
table.Columns.Append(DFolders.FolderPath);
table.Columns.Append(DFolders.UseSubfolders, ADOX.DataTypeEnum.adBoolean);
// Add primary and auto-increment
table.Keys.Append("PrimaryKey", KeyTypeEnum.adKeyPrimary, DFolders.Id);
table.Columns[DFolders.Id].ParentCatalog = cat;
table.Columns[DFolders.Id].Properties["AutoIncrement"].Value = true;
table.Keys.Append("ForeignKeyFolders", ADOX.KeyTypeEnum.adKeyForeign, DFolders.ProjectId, DTables.Assignments, DAssignment.ProjectId);
//table.Keys["ForeignKeyFolders"].UpdateRule = RuleEnum.adRICascade;
return(table);
}
private void Init()
{
ADOX.Catalog cat = new ADOX.Catalog();
ADOX.Table customersTable = new ADOX.Table();
customersTable.Name = "customers";
customersTable.Columns.Append("customerId", ADOX.DataTypeEnum.adVarWChar, 255);
customersTable.Columns.Append("customerName", ADOX.DataTypeEnum.adVarWChar, 255);
customersTable.Columns.Append("flightId", ADOX.DataTypeEnum.adVarWChar, 255);
ADOX.Table flightsTable = new ADOX.Table();
flightsTable.Name = "flights";
flightsTable.Columns.Append("flightId", ADOX.DataTypeEnum.adVarWChar, 255);
flightsTable.Columns.Append("airlineCompany", ADOX.DataTypeEnum.adVarWChar, 255);
flightsTable.Columns.Append("origin", ADOX.DataTypeEnum.adVarWChar, 255);
flightsTable.Columns.Append("destination", ADOX.DataTypeEnum.adVarWChar, 255);
flightsTable.Columns.Append("flightDate", ADOX.DataTypeEnum.adVarWChar, 255);
cat.Create(@"Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" + dataSource);
cat.Tables.Append(customersTable);
cat.Tables.Append(flightsTable);
}
public static string[] GetTableExcel(string strFileName)
{
string[] strTables = new string[100];
Catalog oCatlog = new Catalog();
ADOX.Table oTable = new ADOX.Table();
ADODB.Connection oConn = new ADODB.Connection();
oConn.Open("Provider=Microsoft.Jet.OleDb.4.0; Data Source = " + strFileName + "; Extended Properties = \"Excel 8.0;HDR=Yes;IMEX=1\";", "", "", 0);
oCatlog.ActiveConnection = oConn;
if (oCatlog.Tables.Count > 0)
{
int item = 0;
foreach (ADOX.Table tab in oCatlog.Tables)
{
if (tab.Type == "TABLE")
{
strTables[item] = tab.Name;
item++;
}
}
}
return strTables;
}
private ADOX.Table CreateImageTable(Catalog cat)
{
ADOX.Table table = new ADOX.Table();
//Create the table and it's fields.
table.Name = DTables.Images;
table.Columns.Append(DImageAtLocation.Id, ADOX.DataTypeEnum.adInteger);
table.Columns.Append(DImageAtLocation.ProjectId, ADOX.DataTypeEnum.adInteger);
table.Columns.Append(DImageAtLocation.ImagePath);
table.Columns.Append(DImageAtLocation.Thumbnail); //ADOX.DataTypeEnum.adLongVarWChar
table.Columns.Append(DImageAtLocation.Latitude, ADOX.DataTypeEnum.adDouble);
table.Columns.Append(DImageAtLocation.Longitude, ADOX.DataTypeEnum.adDouble);
table.Columns.Append(DImageAtLocation.Altitude, ADOX.DataTypeEnum.adDouble);
table.Columns.Append(DImageAtLocation.Heading, ADOX.DataTypeEnum.adInteger);
table.Columns.Append(DImageAtLocation.Rotation, ADOX.DataTypeEnum.adInteger);
table.Columns.Append(DImageAtLocation.LocationSource, ADOX.DataTypeEnum.adInteger);
table.Columns.Append(DImageAtLocation.Creator);
table.Columns.Append(DImageAtLocation.TimeImageTaken);
table.Columns.Append(DImageAtLocation.TimeIndexed);
// Add primary and auto-increment
table.Keys.Append("PrimaryKey", KeyTypeEnum.adKeyPrimary, DImageAtLocation.Id);
table.Columns[DImageAtLocation.Id].ParentCatalog = cat;
table.Columns[DImageAtLocation.Id].Properties["AutoIncrement"].Value = true;
table.Keys.Append("ForeignKeyImages", ADOX.KeyTypeEnum.adKeyForeign, DImageAtLocation.ProjectId, DTables.Assignments, DAssignment.ProjectId);
//table.Keys["ForeignKeyImages"].UpdateRule = RuleEnum.;
// Allow null
table.Columns[DImageAtLocation.Thumbnail].Attributes = ColumnAttributesEnum.adColNullable;
table.Columns[DImageAtLocation.Latitude].Attributes = ColumnAttributesEnum.adColNullable;
table.Columns[DImageAtLocation.Longitude].Attributes = ColumnAttributesEnum.adColNullable;
table.Columns[DImageAtLocation.Altitude].Attributes = ColumnAttributesEnum.adColNullable;
table.Columns[DImageAtLocation.Heading].Attributes = ColumnAttributesEnum.adColNullable;
table.Columns[DImageAtLocation.LocationSource].Attributes = ColumnAttributesEnum.adColNullable;
return(table);
}
public static bool ExportAccessDb(String fileName, DataSet ds) {
bool result = false;
ADOX.Catalog cat = new ADOX.Catalog();
ADOX.Table table = new ADOX.Table();
try {
cat.Create("Provider=Microsoft.Jet.OLEDB.4.0;" + "Data Source=" + fileName + "; Jet OLEDB:Engine Type=5");
//create tables
for (int i = 0; i < ds.Tables.Count; i++) {
var ctTable = ds.Tables[i];
//Create the table and it's fields.
table.Name = ctTable.TableName;
CreateAccessCols(table, ctTable);
cat.Tables.Append(table);
}//end method
//now close the database
ADODB.Connection conn = cat.ActiveConnection as ADODB.Connection;
if (conn != null)
conn.Close();
result = true;
}
catch (Exception ex) {
result = false;
}
cat = null;
FillAccessDb(fileName, ds);
return result;
}//end method
public static List <String> GetSheetNames(string strFileName)
{
List <String> sheets = new List <string>();
Catalog oCatlog = new Catalog();
ADOX.Table oTable = new ADOX.Table();
ADODB.Connection oConn = new ADODB.Connection();
oConn.Open("Provider=Microsoft.ACE.OLEDB.12.0; Data Source = " + strFileName + "; Extended Properties = \"Excel 8.0;HDR=Yes;IMEX=1\";", "", "", 0);
oCatlog.ActiveConnection = oConn;
if (oCatlog.Tables.Count > 0)
{
int item = 0;
foreach (ADOX.Table tab in oCatlog.Tables)
{
if (tab.Type == "TABLE")
{
sheets.Add(tab.Name.Trim().Substring(1, tab.Name.Length - 3));
item++;
}
}
}
return(sheets);
}
public static string[] GetTableExcel(string strFileName)
{
string[] strTables = new string[100];
Catalog oCatlog = new Catalog();
ADOX.Table oTable = new ADOX.Table();
ADODB.Connection oConn = new ADODB.Connection();
oConn.Open("Provider=Microsoft.Jet.OleDb.4.0; Data Source = " + strFileName + "; Extended Properties = \"Excel 8.0;HDR=Yes;IMEX=1\";", "", "", 0);
oCatlog.ActiveConnection = oConn;
if (oCatlog.Tables.Count > 0)
{
int item = 0;
foreach (ADOX.Table tab in oCatlog.Tables)
{
if (tab.Type == "TABLE")
{
strTables[item] = tab.Name;
item++;
}
}
}
return(strTables);
}
private ADOX.Table CreateAssignmentTable(Catalog cat)
{
ADOX.Table table = new ADOX.Table();
//Create the table and it's fields.
table.Name = DTables.Assignments;
table.Columns.Append(DAssignment.ProjectId, ADOX.DataTypeEnum.adInteger);
table.Columns.Append(DAssignment.ProjectName);
table.Columns.Append(DAssignment.Latitude, ADOX.DataTypeEnum.adDouble);
table.Columns.Append(DAssignment.Longitude, ADOX.DataTypeEnum.adDouble);
table.Columns.Append(DAssignment.Thumbnail, ADOX.DataTypeEnum.adLongVarWChar);
table.Columns.Append(DAssignment.TimeCreated);
table.Columns.Append(DAssignment.TimeLastIndexed);
table.Keys.Append("PrimaryKey", KeyTypeEnum.adKeyPrimary, DAssignment.ProjectId);
//table.Columns[DAssignment.ProjectId].ParentCatalog = cat;
//table.Columns[DAssignment.ProjectId].Properties["AutoIncrement"].Value = true;
// Allow null
table.Columns[DAssignment.Thumbnail].Attributes = ColumnAttributesEnum.adColNullable;
table.Columns[DAssignment.TimeLastIndexed].Attributes = ColumnAttributesEnum.adColNullable;
return(table);
}
/// <summary>
/// 建立 大转盘 数据库
/// </summary>
public static void Turntable_InitDataBase()
{
string PathDataBase = System.IO.Path.Combine(SYSTEMDIR, "USER_DIR\\SYSUSER\\Turntable\\db.dll");//"USER_DIR\\SYSUSER\\SYSSET\\" +
if (System.IO.File.Exists(PathDataBase) == true)
{
return;
}
try
{
ADOX.Catalog catalog = new Catalog();
catalog.Create("Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" + PathDataBase + ";Jet OLEDB:Engine Type=5");
ADODB.Connection cn = new ADODB.Connection();
cn.Open("Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" + PathDataBase, null, null, -1);
catalog.ActiveConnection = cn;
// string Data1 = "CREATE TABLE WX_GGK( \r\n";
// Data1 += "ID int IDENTITY(1,1) NOT NULL,\r\n";
// Data1 += "Name nvarchar(200) NULL,\r\n";
// Data1 += "Cs int NULL\r\n";
// Data1 += ") ON PRIMARY\r\n";
// Data1 += "CREATE TABLE WX_GGK_JP(\r\n";
//Data1 += "ID int IDENTITY(1,1) NOT NULL,\r\n";
//Data1 += "Name nvarchar(200) NULL,\r\n";
//Data1 += "Jp nvarchar(50) NULL,\r\n";
//Data1 += "SJH nvarchar(50) NULL\r\n";
//Data1 += ") ON PRIMARY\r\n";
// object dummy = Type.Missing;
// cn.Execute(Data1, out dummy, 0);
if (true)
{
ADOX.Table table = new ADOX.Table();
table.Name = "wx_dzp";
ADOX.Column column = new ADOX.Column();
column.ParentCatalog = catalog;
column.Name = "ID";
column.Type = DataTypeEnum.adInteger;
column.DefinedSize = 7;
column.Properties["AutoIncrement"].Value = true;
table.Columns.Append(column, DataTypeEnum.adInteger, 7);
table.Keys.Append("FirstTablePrimaryKey", KeyTypeEnum.adKeyPrimary, column, null, null);
table.Columns.Append("Name", DataTypeEnum.adVarWChar, 200);
table.Columns.Append("Cs", DataTypeEnum.adInteger, 7);
catalog.Tables.Append(table);
}
if (true)
{
ADOX.Table table = new ADOX.Table();
table.Name = "wx_dzp_jp";
ADOX.Column column = new ADOX.Column();
column.ParentCatalog = catalog;
column.Name = "ID";
column.Type = DataTypeEnum.adInteger;
column.DefinedSize = 7;
column.Properties["AutoIncrement"].Value = true;
table.Columns.Append(column, DataTypeEnum.adInteger, 7);
table.Keys.Append("FirstTablePrimaryKey", KeyTypeEnum.adKeyPrimary, column, null, null);
table.Columns.Append("Name", DataTypeEnum.adVarWChar, 200);
table.Columns.Append("Jp", DataTypeEnum.adVarWChar, 200);
table.Columns.Append("SJH", DataTypeEnum.adVarWChar, 200);
catalog.Tables.Append(table);
}
cn.Close();
}
catch
{
}
}
//保存数据
private void CreateDataTable()
{
startTime = DateTime.Now.Year.ToString() + "-" + DateTime.Now.Month.ToString() + "-" + DateTime.Now.Day.ToString() + "-" + DateTime.Now.Hour.ToString() + "-" + DateTime.Now.Minute.ToString() + "-" + DateTime.Now.Second.ToString();
ADOX.Catalog catalog = new Catalog();
string tableName = startTime;
string myDataPath = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" + "D:\\Data\\" + tableName + ".mdb";
myConnectionString = myDataPath;
catalog.Create(myDataPath + ";Jet OLEDB:Engine Type=5");
ADODB.Connection connection = new ADODB.Connection();
connection.Open(myDataPath, null, null, -1);
catalog.ActiveConnection = connection;
ADOX.Table table = new ADOX.Table();
table.Name = "Data";
ADOX.Column column = new ADOX.Column();
column.ParentCatalog = catalog;
column.Name = "Num_My";
column.Type = DataTypeEnum.adInteger;
column.DefinedSize = 9;
column.Properties["AutoIncrement"].Value = true;
table.Columns.Append(column, DataTypeEnum.adInteger, 12);
table.Keys.Append("FirstTablePrimaryKey", KeyTypeEnum.adKeyPrimary, column, null, null);
table.Columns.Append("Date", DataTypeEnum.adDate, 20);
table.Columns.Append("Position", DataTypeEnum.adSingle, 9);
catalog.Tables.Append(table);
}
private bool CreateTableXmlTable()
{
try
{
ADOX.Table objTable = new ADOX.Table();
//Create the table
objTable.Name = "XML_TABLE";
//Create and Append a new field to the "Test_Table" Columns Collection
objTable.Columns.Append("XML_ID", DataTypeEnum.adInteger);
objTable.Columns.Append("FILE_NAME", DataTypeEnum.adWChar);
objTable.Columns.Append("XML_FILE", DataTypeEnum.adLongVarWChar);
objTable.Columns.Append("TIME_STAMP", DataTypeEnum.adDate);
//Create and Append a new key. Note that we are merely passing
//the "PimaryKey_Field" column as the source of the primary key. This
//new Key will be Appended to the Keys Collection of "Test_Table"
objTable.Keys.Append("PrimaryKey", KeyTypeEnum.adKeyPrimary, "XML_ID");
//Append the newly created table to the Tables Collection
_cat.Tables.Append(objTable);
return true;
}
catch (Exception)
{
return false;
}
}
private bool createSchema()
{
bool result = false;
string strMsg = "";
OleDbConnection conn;
ADODB.Connection adodbCon = new ADODB.Connection();
adodbCon.ConnectionString = connString;
ADOX.Catalog cat = new ADOX.Catalog();
ADOX.Table tab;
try
{
DirectoryInfo dir = new DirectoryInfo($@"{Path.GetDirectoryName(txtPathToArc.Text)}\unarc");
FileInfo[] filesH = dir.GetFiles("H*.xml");
if (filesH.Length == 0)
{
strMsg = "Файлы для обработки отсутствуют";
MessageBox.Show(strMsg);
txtLog.AppendLine(strMsg);
return(result);
}
foreach (FileInfo fi in filesH)
{
DataSet ds = new DataSet();
ds.ReadXmlSchema(fi.FullName);
foreach (string tableName in tableListH)
{
if (ds.Tables.Contains(tableName))
{
DataTable dt = ds.Tables[tableName];
foreach (DataColumn dc in dt.Columns)
{
if (!addFieldsH[tableName].Contains(dc.ColumnName))
{
addFieldsH[tableName].Add(dc.ColumnName);
}
}
}
}
}
adodbCon.Open();
cat.ActiveConnection = adodbCon;
conn = new OleDbConnection(connString);
if (conn.State == ConnectionState.Closed)
{
conn.Open();
}
txtLog.AppendLine("Создание структуры БД");
Application.DoEvents();
string postFix = "";
if (rbTypeTo.Checked)
{
postFix = "rokb";
}
foreach (string tabName in tableListH)
{
tab = new ADOX.Table();
tab.Name = $"{tabName}{postFix}";
//id
ADOX.Column column = new ADOX.Column();
column.Name = "id";
column.Type = ADOX.DataTypeEnum.adInteger;
column.ParentCatalog = cat;
column.Properties["AutoIncrement"].Value = true;
tab.Columns.Append(column);
foreach (string str in addFieldsH[tabName])
{
tab.Columns.Append(defCol(str));
}
cat.Tables.Append(tab);
}
//L
FileInfo[] filesL = dir.GetFiles("L*.xml");
if (filesL.Length == 0)
{
strMsg = "Файлы для обработки отсутствуют";
MessageBox.Show(strMsg);
txtLog.AppendLine(strMsg);
return(result);
}
foreach (FileInfo fi in filesL)
{
DataSet ds = new DataSet();
ds.ReadXmlSchema(fi.FullName);
foreach (string tableName in tableListL)
{
if (ds.Tables.Contains(tableName))
{
DataTable dt = ds.Tables[tableName];
foreach (DataColumn dc in dt.Columns)
{
if (!addFieldsL[tableName].Contains(dc.ColumnName))
{
addFieldsL[tableName].Add(dc.ColumnName);
}
}
}
}
}
foreach (string tabName in tableListL)
{
tab = new ADOX.Table();
tab.Name = $"{tabName}{postFix}";
//id
ADOX.Column column = new ADOX.Column();
column.Name = "id";
column.Type = ADOX.DataTypeEnum.adInteger;
column.ParentCatalog = cat;
column.Properties["AutoIncrement"].Value = true;
tab.Columns.Append(column);
foreach (string str in addFieldsL[tabName])
{
tab.Columns.Append(defCol(str));
}
cat.Tables.Append(tab);
}
if (conn.State == ConnectionState.Open)
{
conn.Close();
}
strMsg = "Структура БД создана";
txtLog.AppendLine(strMsg);
result = true;
}
catch (Exception ex)
{
MessageBox.Show(ex.Message);
}
return(result);
}
private bool CreateTableXmlTagsRelation()
{
try
{
ADOX.Table objTable = new ADOX.Table();
//Create the table
objTable.Name = "XML_TAG_RELATION";
//Create and Append a new field to the "Test_Table" Columns Collection
objTable.Columns.Append("TAG_ID", DataTypeEnum.adInteger);
objTable.Columns.Append("XML_ID", DataTypeEnum.adInteger);
//Create and Append a new key. Note that we are merely passing
//the "PimaryKey_Field" column as the source of the primary key. This
//new Key will be Appended to the Keys Collection of "Test_Table"
objTable.Keys.Append("ForeignKey1", KeyTypeEnum.adKeyForeign, "XML_ID", "XML_TABLE", "XML_ID");
objTable.Keys.Append("ForeignKey2", KeyTypeEnum.adKeyForeign, "TAG_ID", "TAGS", "TAG_ID");
//Append the newly created table to the Tables Collection
_cat.Tables.Append(objTable);
return true;
}
catch (Exception)
{
return false;
}
}
