private void MakeOwnedDBbgw_DoWork(object sender, DoWorkEventArgs e) { string DocLocation = System.Environment.GetFolderPath(Environment.SpecialFolder.UserProfile) + "\\Documents\\Magic Manager\\"; if (!System.IO.Directory.Exists(DocLocation)) System.IO.Directory.CreateDirectory(DocLocation); DocLocation += "MyCards.mmodb"; ADOX.Catalog CreateDB = new ADOX.Catalog(); CreateDB.Create("Provider=Microsoft.Jet.OLEDB.4.0; Data Source=" + DocLocation + "; Jet OLEDB:Engine Type=5"); ADOX.Table CardTable = new ADOX.Table(); CardTable.Name = "MyCards"; CardTable.Columns.Append("MultiverseID"); CardTable.Columns.Append("Name"); CardTable.Columns.Append("Expansion"); CardTable.Columns.Append("stdAmount"); CardTable.Columns.Append("foilAmount"); CreateDB.Tables.Append(CardTable); OleDbConnection DBcon = CreateDB.ActiveConnection as OleDbConnection; if (DBcon != null) DBcon.Close(); Marshal.ReleaseComObject(CreateDB.ActiveConnection); Marshal.ReleaseComObject(CreateDB); GC.Collect(); GC.WaitForPendingFinalizers(); Properties.Settings.Default.OwnedDatabase = DocLocation; Properties.Settings.Default.Save(); }
public bool CreateNewAccessDatabase(string fileName) { 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=" + fileName + "; 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; }
private bool buildPath1_Month(ref string lPath, ref string lServerPath) { bool functionReturnValue = false; ADOX.Catalog cat = new ADOX.Catalog(); ADOX.Table tbl = new ADOX.Table(); ADODB.Recordset rs = default(ADODB.Recordset); ADODB.Connection cn = default(ADODB.Connection); string lFile = null; string holdfile = null; short x = 0; Scripting.FileSystemObject fso = new Scripting.FileSystemObject(); string lDir = null; // ERROR: Not supported in C#: OnErrorStatement Cursor = System.Windows.Forms.Cursors.WaitCursor; //lPath = upgradePath System.Windows.Forms.Application.DoEvents(); lDir = Strings.LCase("\\\\" + lServerPath + "\\C\\4posServer\\"); cn = modRecordSet.openConnectionInstance(ref lPath); if (cn == null) { } else { cat.let_ActiveConnection(cn); foreach ( tbl in cat.Tables) { if (tbl.Type == "LINK") { System.Windows.Forms.Application.DoEvents(); //lFile = tbl.Name if (tbl.Properties("Jet OLEDB:Link Datasource").Value != lDir + "pricing.mdb") { tbl.Properties("Jet OLEDB:Link Datasource").Value = Strings.Replace(Strings.LCase(tbl.Properties("Jet OLEDB:Link Datasource").Value), Strings.LCase("C:\\4posServer\\"), lDir); } //DoEvents //If tbl.Properties("Jet OLEDB:Link Datasource") <> lDIR & "pricing.mdb" Then // tbl.Properties("Jet OLEDB:Link Datasource") = Replace(LCase(tbl.Properties("Jet OLEDB:Link Datasource")), LCase("C:\4posServer\"), lDIR) //End If } } //UPGRADE_NOTE: Object cat may not be destroyed until it is garbage collected. Click for more: 'ms-help://MS.VSCC.v90/dv_commoner/local/redirect.htm?keyword="6E35BFF6-CD74-4B09-9689-3E1A43DF8969"' cat = null; cn.Close(); //UPGRADE_NOTE: Object cn may not be destroyed until it is garbage collected. Click for more: 'ms-help://MS.VSCC.v90/dv_commoner/local/redirect.htm?keyword="6E35BFF6-CD74-4B09-9689-3E1A43DF8969"' cn = null; cat = new ADOX.Catalog(); } System.Windows.Forms.Application.DoEvents(); Cursor = System.Windows.Forms.Cursors.Default; functionReturnValue = true; return functionReturnValue; buildPath_Error: Cursor = System.Windows.Forms.Cursors.Default; Interaction.MsgBox(Err().Description); functionReturnValue = false; return functionReturnValue; }
public ColumnSchema[] GetTableColumns(string connectionString, TableSchema table) { ADOX.Catalog catalog = GetCatalog(connectionString); ADOX.Table adoxtable = catalog.Tables[table.Name]; ArrayList columns = new ArrayList(); if (adoxtable.Columns != null) { for (int i = 0; i < adoxtable.Columns.Count; i++) { var properties = new ExtendedPropertyCollection(ConvertToExtendedProperties(adoxtable.Columns[i].Properties)); if (adoxtable.Columns[i].Properties["Default"] != null) { properties.Add(new ExtendedProperty(ExtendedPropertyNames.DefaultValue, adoxtable.Columns[i].Properties["Default"].Value, DbType.String, PropertyStateEnum.ReadOnly)); } bool allowDBNull = adoxtable.Columns[i].Properties["Nullable"] != null && (bool)adoxtable.Columns[i].Properties["Nullable"].Value; columns.Add(new ColumnSchema( table, adoxtable.Columns[i].Name, GetDbType(adoxtable.Columns[i].Type), adoxtable.Columns[i].Type.ToString(), adoxtable.Columns[i].DefinedSize, Convert.ToByte(adoxtable.Columns[i].Precision), adoxtable.Columns[i].NumericScale, allowDBNull, properties.ToArray())); } } Cleanup(); return((ColumnSchema[])columns.ToArray(typeof(ColumnSchema))); }
public PrimaryKeySchema GetTablePrimaryKey(string connectionString, TableSchema table) { ADOX.Catalog catalog = GetCatalog(connectionString); ADOX.Table adoxtable = catalog.Tables[table.Name]; if (adoxtable.Keys != null) { for (int i = 0; i < adoxtable.Keys.Count; i++) { if (adoxtable.Keys[i].Type == ADOX.KeyTypeEnum.adKeyPrimary) { string[] memberColumns = new string[adoxtable.Keys[i].Columns.Count]; for (int x = 0; x < adoxtable.Keys[i].Columns.Count; x++) { memberColumns[x] = adoxtable.Keys[i].Columns[x].Name; } Cleanup(); var extendedProperties = new ExtendedPropertyCollection(); extendedProperties.Add(new ExtendedProperty("DeleteRule", adoxtable.Keys[i].DeleteRule.ToString(), DbType.String, PropertyStateEnum.ReadOnly)); extendedProperties.Add(new ExtendedProperty("UpdateRule", adoxtable.Keys[i].UpdateRule.ToString(), DbType.String, PropertyStateEnum.ReadOnly)); return(new PrimaryKeySchema(table, adoxtable.Keys[i].Name, memberColumns, extendedProperties.ToArray())); } } } Cleanup(); return(null); }
public IndexSchema[] GetTableIndexes(string connectionString, TableSchema table) { ADOX.Catalog catalog = GetCatalog(connectionString); ADOX.Table adoxtable = catalog.Tables[table.Name]; IndexSchema[] indexes = new IndexSchema[0]; if (adoxtable.Indexes != null) { indexes = new IndexSchema[adoxtable.Indexes.Count]; for (int i = 0; i < adoxtable.Indexes.Count; i++) { string[] memberColumns = new string[adoxtable.Indexes[i].Columns.Count]; for (int x = 0; x < adoxtable.Indexes[i].Columns.Count; x++) { memberColumns[x] = adoxtable.Indexes[i].Columns[x].Name; } indexes[i] = new IndexSchema(table, adoxtable.Indexes[i].Name, adoxtable.Indexes[i].PrimaryKey, adoxtable.Indexes[i].Unique, adoxtable.Indexes[i].Clustered, memberColumns, ConvertToExtendedProperties(adoxtable.Indexes[i].Properties)); } } Cleanup(); return(indexes); }
public static bool TableExist(string dbName, string tblName, string pwd = "") { try { ADOX.Catalog cat = new ADOX.Catalog(); ADODB.Connection cn = new ADODB.Connection(); cn.Open(Provider + @"Data Source=" + dbName + ";" + Password + "=" + pwd); cat.ActiveConnection = cn; for (int i = 0; i < cat.Tables.Count; ++i) { ADOX.Table t = cat.Tables[i]; if (t.Name == tblName) { return(true); } } } catch (Exception ex) { MessageBox.Show(ex.ToString()); return(false); } return(false); }
public object IndexDefinition(ADOX.Table tblDef, ADOX.Index idxDef) { int intLoop = 0; string strIndex = null; ADOX.Column colDef = null; if (idxDef.PrimaryKey == true) { strIndex = strIndex + "PRIMARY KEY "; } else if (idxDef.Unique) { strIndex = strIndex + "UNIQUE INDEX " + MySQLName(idxDef.Name); } else { strIndex = strIndex + "INDEX " + MySQLName(idxDef.Name); } strIndex = strIndex + "("; for (intLoop = 0; intLoop < idxDef.Columns.Count; intLoop++) { colDef = idxDef.Columns[intLoop]; strIndex = strIndex + MySQLName(colDef.Name); if (intLoop < idxDef.Columns.Count - 1) { strIndex = strIndex + ","; } } strIndex = strIndex + ")"; return(strIndex); }
private void Form1_Load(object sender, EventArgs e) { OleDbConnectionStringBuilder builder = new OleDbConnectionStringBuilder() { Provider = "Microsoft.Jet.OLEDB.4.0", DataSource = System.IO.Directory.GetCurrentDirectory() + "\\database.accdb", }; builder.Add("Jet OLEDB:Engine Type", 5); var catalog = new ADOX.Catalog(); var table = new ADOX.Table(); table.Name = "DataTableSample1"; table.Columns.Append("Column"); table.Columns.Append("Column1"); table.Columns.Append("Column2"); if (!System.IO.File.Exists(builder.DataSource)) { catalog.Create(builder.ConnectionString); } try { catalog.Tables.Append(table); } catch (Exception ex) { MessageBox.Show("Exists"); } var connection = catalog.ActiveConnection as ADODB.Connection; if (connection != null) { connection.Close(); } }
public override void SetFieldMark(string tableName, string tableMemo, TableField[] fields) { try { ADODB.Connection adodb_conn = new ADODB.Connection(); adodb_conn.Open(base.connector.ConnectionString, null, null, -1); ADOX.Catalog catalog = new ADOX.Catalog(); catalog.ActiveConnection = adodb_conn; ADOX.Table table = catalog.Tables[tableName]; foreach (TableField field in fields) { ADOX.Column col = table.Columns[field.Name]; } Marshal.FinalReleaseComObject(catalog.ActiveConnection); Marshal.FinalReleaseComObject(catalog); } catch (Exception e) { throw (e); } //--------------------- // 作者:重庆 - 传说 //来源:CSDN //原文:https://blog.csdn.net/zdb330906531/article/details/49420991 //版权声明:本文为博主原创文章,转载请附上博文链接! }
public override TableField[] GetFieldMark(string tableName) { List <TableField> fields = new List <TableField>(); try { ADODB.Connection adodb_conn = new ADODB.Connection(); adodb_conn.Open(base.connector.ConnectionString, null, null, -1); ADOX.Catalog catalog = new ADOX.Catalog(); catalog.ActiveConnection = adodb_conn; ADOX.Table table = catalog.Tables[tableName]; foreach (ADOX.Column col in table.Columns) { string type = col.Type.ToString(); if (type.Contains('(')) { type = type.Substring(type.IndexOf('(')); } TableField field = new TableField() { Name = col.Name, Type = type, NoNull = false }; fields.Add(field); } Marshal.FinalReleaseComObject(catalog.ActiveConnection); Marshal.FinalReleaseComObject(catalog); return(fields.ToArray()); } catch (Exception e) { throw (e); } }
private void linkFirstTable(ref string source) { ADOX.Catalog cat = default(ADOX.Catalog); ADOX.Table tbl = default(ADOX.Table); Scripting.FileSystemObject fso = new Scripting.FileSystemObject(); // ERROR: Not supported in C#: OnErrorStatement if (fso.FileExists(modRecordSet.serverPath + source + ".mdb")) { } else { return; } cat = new ADOX.Catalog(); short x = 0; // Open the catalog. cat.let_ActiveConnection(modReport.cnnDBreport); for (x = cat.Tables.Count - 1; x >= 0; x += -1) { switch (Strings.LCase(cat.Tables(x).name)) { case "adayendstockitemlnk": cat.Tables.delete(cat.Tables(x).name); break; } } tbl = new ADOX.Table(); tbl.name = "aDayEndStockItemLnk"; tbl.ParentCatalog = cat; tbl.Properties("Jet OLEDB:Link Datasource").Value = modRecordSet.serverPath + source + ".mdb"; tbl.Properties("Jet OLEDB:Remote Table Name").Value = "DayEndStockItemLnk"; tbl.Properties("Jet OLEDB:Create Link").Value = true; cat.Tables.Append(tbl); cat.Tables.Refresh(); cat = null; return; withPass: openConnection_linkFirstTable: //cat.ActiveConnection("Jet OLEDB:Database Password") = "lqd" //Resume Next //Exit Sub //If Err.Description = "[Microsoft][ODBC Microsoft Access Driver] Not a valid password." Then // GoTo withPass //ElseIf Err.Description = "Not a valid password." Then // GoTo withPass //Else Interaction.MsgBox(Err().Number + " - " + Err().Description); //End If }
private bool buildPath1(ref string lPath) { bool functionReturnValue = false; ADOX.Catalog cat = default(ADOX.Catalog); ADOX.Table tbl = default(ADOX.Table); ADODB.Recordset rs = default(ADODB.Recordset); ADODB.Connection cn = new ADODB.Connection(); string lFile = null; string holdfile = null; string[] lArray = null; short x = 0; Scripting.FileSystemObject fso = new Scripting.FileSystemObject(); string lDir = null; cat = new ADOX.Catalog(); tbl = new ADOX.Table(); // ERROR: Not supported in C#: OnErrorStatement Cursor = System.Windows.Forms.Cursors.WaitCursor; if (modReport.cnnDBreport == null) { } else { cat.let_ActiveConnection(modReport.cnnDBreport); foreach (tbl in cat.Tables) { if (tbl.Type == "LINK") { System.Windows.Forms.Application.DoEvents(); tbl.Properties("Jet OLEDB:Link Datasource").Value = modRecordSet.serverPath + "pricing.mdb"; //Replace(LCase(tbl.Properties("Jet OLEDB:Link Datasource")), LCase("C:\4posServer\"), serverPath) } } cat = null; cn.Close(); cn = null; cat = new ADOX.Catalog(); } System.Windows.Forms.Application.DoEvents(); Cursor = System.Windows.Forms.Cursors.Default; functionReturnValue = true; return(functionReturnValue); buildPath_Error: Cursor = System.Windows.Forms.Cursors.Default; Interaction.MsgBox(Err().Description); functionReturnValue = false; return(functionReturnValue); }
/// <summary> /// Creates a new access database and populates it with the templates table and columns. /// </summary> /// <returns>True if database created and selected successfully, false if otherwise.</returns> public bool CreateNewAccessDatabase() { bool result = false; if (currentFile.Equals("")) { return(result); } if (File.Exists(@currentFile)) { try { File.Delete(@currentFile); } catch (Exception) { MessageBox.Show("Unable to replace file \"" + @currentFile + "\"! It may already " + "be in use by another applicaton.", "Database Create Error", MessageBoxButtons.OK, MessageBoxIcon.Error); } } this.cat = new ADOX.Catalog(); this.table = new ADOX.Table(); // Create the table and it's fields. table.Name = TABLE_NAME; table.Columns.Append(STUDENT_NO_COL, ADOX.DataTypeEnum.adLongVarWChar); table.Columns.Append(SIGN_IN_COL, ADOX.DataTypeEnum.adDate); table.Columns.Append(SIGN_OUT_COL, ADOX.DataTypeEnum.adDate); table.Columns[SIGN_IN_COL].Attributes = ADOX.ColumnAttributesEnum.adColNullable; table.Columns[SIGN_OUT_COL].Attributes = ADOX.ColumnAttributesEnum.adColNullable; try { this.cat.Create(this.connString); this.cat.Tables.Append(table); this.conn = cat.ActiveConnection as ADODB.Connection; if (this.conn != null) { if (this.conn.State == 1) { this.conn.Close(); } } result = true; } catch (Exception ex) { Console.WriteLine("Error: " + ex); result = false; } return(result); }
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 = "AdminInfo"; 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 { ADODB.Connection con = cat.ActiveConnection as ADODB.Connection; ADODB.Connection Cn = new ADODB.Connection(); Cn.Open(@"Provider=Microsoft.Jet.OLEDB.4.0;" + @"Data Source=C:\Users\Umer\Documents\Visual Studio 2015\Projects\masterpage\UserInfo.mdb;Persist Security Info=False"); cat.ActiveConnection = Cn; cat.Tables.Append(table); if (con != null) { con.Close(); } result = true; } catch (Exception ex) { // Console.WriteLine(ex); // check.Text = ex.ToString(); Namebox.Text = ex.ToString(); result = false; return(result); } return(true); }
private bool RelinkTables() { string strConx; ADOX.Table tblLink; catJCMS.ActiveConnection = conJCMS; catLoop.ActiveConnection = conJCMS; catMySQL.ActiveConnection = conMySQL; //opened in ConnectToMySQL try { //drop existing table links foreach (ADOX.Table tblLoop in catLoop.Tables) { if (tblLoop.Type == "LINK") { catJCMS.Tables.Delete(tblLoop.Name); } } //create a new link for each table in the MySQL database foreach (ADOX.Table tblLoop in catMySQL.Tables) { tblLink = new ADOX.Table(); tblLink.Name = tblLoop.Name; tblLink.ParentCatalog = catJCMS; tblLink.Properties["Jet OLEDB:Link Datasource"].Value = strSourceDbFullPath; tblLink.Properties["Jet OLEDB:Link Provider String"].Value = "ODBC;DATABASE=" + strMySQLDBName + ";FILEDSN=myJCMS;OPTION=0;UID=" + strMySQLUser + ";PWD=" + strMySQLPassword + ";PORT=" + strMySQLPort + ";SERVER=" + strMySQLHost + ";"; tblLink.Properties["Jet OLEDB:Remote Table Name"].Value = tblLoop.Name; tblLink.Properties["Jet OLEDB:Create Link"].Value = true; tblLink.Properties["Jet OLEDB:Cache Link Name/Password"].Value = true; catJCMS.Tables.Append(tblLink); } } catch (Exception e) { WriteOutput("Could not re-link tables. Error: " + e.Message + ". This step must be performed manually. Please see help section 'Troubleshooting problems with database conversion' for details."); return(false); } return(true); }
private string GetTableDescription(ADOX.Table pTable, ADODB.Connection pcon) { string strDesc = null; try { ADODB.Recordset rsSchema = pcon.OpenSchema(ADODB.SchemaEnum.adSchemaTables, new object[] { null, null, pTable.Name, "TABLE" }, System.Reflection.Missing.Value); if (!rsSchema.EOF) { if (rsSchema.Fields["DESCRIPTION"].Value != System.DBNull.Value) { strDesc = (string)rsSchema.Fields["DESCRIPTION"].Value; } } } catch (Exception) { } if ("".Equals(strDesc)) { strDesc = null; } return(strDesc); }
public bool CreateNewAccessDatabase(string fileName) { if (File.Exists(fileName)) { return(true); } bool result = false; ADOX.Catalog cat = new ADOX.Catalog(); ADOX.Table table = new ADOX.Table(); table.Name = "ThreadData"; table.Columns.Append("ID", ADOX.DataTypeEnum.adInteger); table.Keys.Append("PrimaryKey", ADOX.KeyTypeEnum.adKeyPrimary, "ID", null, null); table.Columns.Append("ThreadID"); table.Columns.Append("Time"); table.Columns.Append("Data"); try { cat.Create("Provider=Microsoft.Jet.OLEDB.4.0;" + "Data Source=" + fileName + "; Jet OLEDB:Engine Type=5"); ADODB.Connection con = cat.ActiveConnection; table.Columns["ID"].ParentCatalog = cat; table.Columns["ID"].Properties["AutoIncrement"].Value = true; cat.Tables.Append(table); if (con != null) { con.Close(); } result = true; } catch (Exception ex) { result = false; } cat = null; return(result); }
public object TableDefinition(ADOX.Table tblDef) { int intLoop = 0; string strTable = ""; string strIndex = ""; ADODB.Recordset recSchema = new ADODB.Recordset(); Console.WriteLine("Table: " + tblDef.Name.ToString()); recSchema.Open("SELECT * FROM [" + tblDef.Name + "]", conJCMS_db, ADODB.CursorTypeEnum.adOpenStatic, ADODB.LockTypeEnum.adLockBatchOptimistic, 0); strTable = "CREATE TABLE " + strMySQLDBName + "." + MySQLName(tblDef.Name) + System.Environment.NewLine + "("; for (intLoop = 0; intLoop < recSchema.Fields.Count; intLoop++) { strTable = strTable + ColDefinition(tblDef.Columns[recSchema.Fields[intLoop].Name]); if (intLoop + 1 < tblDef.Columns.Count) { strTable = strTable + ", " + System.Environment.NewLine + " "; } } foreach (ADOX.Index idxLoop in tblDef.Indexes) { strIndex = IndexDefinition(tblDef, idxLoop).ToString(); if (strIndex != "") { strTable = strTable + ", " + System.Environment.NewLine + strIndex; } } strTable = strTable + ")"; strTable = strTable + " ENGINE =INNODB"; strTable = strTable + " CHARACTER SET utf8"; return(strTable); }
public static void CreateAccessDatabase(string file) { string connectionString = string.Format("Provider={0}; Data Source={1}; Jet OLEDB:Engine Type={2}", "Microsoft.Jet.OLEDB.4.0", file, 5); ADOX.Catalog catalog = new ADOX.Catalog(); catalog.Create(connectionString); ADOX.Table table = new ADOX.Table(); table.Name = "Manufacturers"; // Table name // Column 1 (BSSID) ADOX.Column BSSIDCol = new ADOX.Column(); BSSIDCol.Name = "BSSID"; BSSIDCol.ParentCatalog = catalog; BSSIDCol.Type = ADOX.DataTypeEnum.adVarWChar; BSSIDCol.DefinedSize = 6; // Column 2 (Manufacturer) ADOX.Column ManuCol = new ADOX.Column(); ManuCol.Name = "Manufacturer"; ManuCol.ParentCatalog = catalog; ManuCol.Type = ADOX.DataTypeEnum.adVarWChar; ManuCol.DefinedSize = 255; table.Columns.Append(BSSIDCol); table.Columns.Append(ManuCol); catalog.Tables.Append(table); // Close the connection to the database after we are done creating it and adding the table to it. ADODB.Connection con = (ADODB.Connection)catalog.ActiveConnection; if (con != null && con.State != 0) { con.Close(); } }
/// <summary> /// Mose Like Excel /// Just different in Connection String /// </summary> /// <param name="csvFile"></param> /// <param name="sdfFile"></param> /// <param name="p"></param> /// <param name="p_4"></param> /// <returns></returns> internal static bool ConvertCSVToSdf(string csvFile, string sdfFile, bool NeedCopyData, string targetDbPwd, bool isFirstRowIsColumnName) { bool result = false; if (!File.Exists(csvFile)) { "ImportData_FileNotFound".GetFromResourece().Notify(); return(false); } ICoreEAHander srcEngine = new CoreEA.CoreE(CoreE.UsedDatabaseType.CSV).X_Handler; srcEngine.Open(new LoginInfo_CSV() { Database = csvFile, IsFirstRowIsColumnName = isFirstRowIsColumnName }); if (!srcEngine.IsOpened) { "ImportData_ReadError".GetFromResourece().Notify(); return(false); } List <string> tableList = srcEngine.GetTableListInDatabase(); ICoreEAHander destEngine = new CoreEA.CoreE(CoreE.UsedDatabaseType.SqlCE35).X_Handler; //IF the ce database not existed ,then create it . if (!File.Exists(sdfFile)) { if (!destEngine.CreateDatabase(new LoginInfo_SSCE() { DbName = sdfFile })) { "ImportData_CreateSSCEFileFailure".GetFromResourece().Notify(); return(false); } } destEngine.Open(new LoginInfo_SSCE() { DbName = sdfFile, Pwd = "", IsEncrypted = false, CurOpenMode = OpenMode.ReadWrite }); List <string> targetDbList = destEngine.GetTableListInDatabase(); try { foreach (string tableName in tableList) { //Don't import table which name has existed. if (targetDbList.Contains(tableName)) { continue; } string sqlCeTableName = tableName; string strconnection = CoreEA.ConnSTR.DbConnectionString.TxtFile.OleDb_DelimitedColumns(csvFile, true); ADODB.Connection conn = new ADODB.ConnectionClass(); //conn.ConnectionString = strconnection; conn.Open(strconnection, "", "", 0); //Prepare to retrive schema info from access via COM ADOX.Catalog catelog = new ADOX.CatalogClass(); catelog.let_ActiveConnection(conn); ADOX.Table tempTable = catelog.Tables[tableName]; //Start Generate the Create Sdf table command string tempCreateTableCmd = string.Empty; tempCreateTableCmd = String.Format("CREATE TABLE [{0}] ", sqlCeTableName); string tempSechma = string.Empty; for (int i = 0; i < tempTable.Columns.Count; i++) { Debug.WriteLine("Source Field Name ------>" + tempTable.Columns[i].Name); tempSechma += String.Format("{0} {1},", tempTable.Columns[i].Name, CoreEA.Utility.TypeConvertor.ParseADODbTypeToSqlCeDbType(tempTable.Columns[i].Type.ToString(), tempTable.Columns[i].DefinedSize) ); } tempSechma = tempSechma.Substring(0, tempSechma.Length - 1); tempCreateTableCmd = String.Format("{0} ({1})", tempCreateTableCmd, tempSechma); if (destEngine.DoExecuteNonQuery(tempCreateTableCmd) != -1) { throw new Exception(string.Format("Create table {0} error", tableName)); } if (NeedCopyData) { CopyTable(srcEngine.GetConnection(), (SqlCeConnection)destEngine.GetConnection(), string.Format("Select * from [{0}]", tableName), sqlCeTableName); } } result = true; } catch (Exception ee) { ee.HandleMyException(); } return(result); }
public TableKeySchema[] GetTableKeys(string connectionString, TableSchema table) { ADOX.Catalog catalog = GetCatalog(connectionString); ADOX.Table adoxtable = catalog.Tables[table.Name]; List <TableKeySchema> keys = new List <TableKeySchema>(); if (adoxtable.Keys != null) { for (int i = 0; i < adoxtable.Keys.Count; i++) { if (adoxtable.Keys[i].Type == ADOX.KeyTypeEnum.adKeyForeign) { string[] primaryKeyMemberColumns = new string[adoxtable.Keys[i].Columns.Count]; string[] foreignKeyMemberColumns = new string[adoxtable.Keys[i].Columns.Count]; for (int x = 0; x < adoxtable.Keys[i].Columns.Count; x++) { primaryKeyMemberColumns[x] = adoxtable.Keys[i].Columns[x].RelatedColumn; foreignKeyMemberColumns[x] = adoxtable.Keys[i].Columns[x].Name; } var extendedProperties = new ExtendedPropertyCollection(); extendedProperties.Add(new ExtendedProperty("DeleteRule", adoxtable.Keys[i].DeleteRule.ToString(), DbType.String, PropertyStateEnum.ReadOnly)); extendedProperties.Add(new ExtendedProperty("UpdateRule", adoxtable.Keys[i].UpdateRule.ToString(), DbType.String, PropertyStateEnum.ReadOnly)); keys.Add(new TableKeySchema(table.Database, adoxtable.Keys[i].Name, foreignKeyMemberColumns, table.Name, primaryKeyMemberColumns, adoxtable.Keys[i].RelatedTable, extendedProperties.ToArray())); } } } for (int i = 0; i < catalog.Tables.Count; i++) { var catalogKeys = catalog.Tables[i].Keys; if (catalogKeys == null) { continue; } for (int x = 0; x < catalogKeys.Count; x++) { if (catalogKeys[x].RelatedTable == table.Name && catalogKeys[x].Type == ADOX.KeyTypeEnum.adKeyForeign) { string[] primaryKeyMemberColumns = new string[catalogKeys[x].Columns.Count]; string[] foreignKeyMemberColumns = new string[catalogKeys[x].Columns.Count]; for (int y = 0; y < catalogKeys[x].Columns.Count; y++) { primaryKeyMemberColumns[y] = catalogKeys[x].Columns[y].Name; foreignKeyMemberColumns[y] = catalogKeys[x].Columns[y].RelatedColumn; } var extendedProperties = new ExtendedPropertyCollection(); extendedProperties.Add(new ExtendedProperty("DeleteRule", catalogKeys[x].DeleteRule.ToString(), DbType.String, PropertyStateEnum.ReadOnly)); extendedProperties.Add(new ExtendedProperty("UpdateRule", catalogKeys[x].UpdateRule.ToString(), DbType.String, PropertyStateEnum.ReadOnly)); keys.Add(new TableKeySchema(table.Database, catalogKeys[x].Name, foreignKeyMemberColumns, catalog.Tables[i].Name, primaryKeyMemberColumns, table.Name, extendedProperties.ToArray())); } } } Cleanup(); return(keys.ToArray()); }
private bool buildPath1(ref string lPath) { bool functionReturnValue = false; ADOX.Catalog cat = default(ADOX.Catalog); ADOX.Table tbl = default(ADOX.Table); ADODB.Recordset rs = default(ADODB.Recordset); ADODB.Connection cn = new ADODB.Connection(); string lFile = null; string holdfile = null; string[] lArray = null; short x = 0; Scripting.FileSystemObject fso = new Scripting.FileSystemObject(); string lDir = null; cat = new ADOX.Catalog(); tbl = new ADOX.Table(); // ERROR: Not supported in C#: OnErrorStatement Cursor = System.Windows.Forms.Cursors.WaitCursor; if (modReport.cnnDBreport == null) { } else { cat.let_ActiveConnection(modReport.cnnDBreport); foreach ( tbl in cat.Tables) { if (tbl.Type == "LINK") { System.Windows.Forms.Application.DoEvents(); tbl.Properties("Jet OLEDB:Link Datasource").Value = modRecordSet.serverPath + "pricing.mdb"; //Replace(LCase(tbl.Properties("Jet OLEDB:Link Datasource")), LCase("C:\4posServer\"), serverPath) } } cat = null; cn.Close(); cn = null; cat = new ADOX.Catalog(); } System.Windows.Forms.Application.DoEvents(); Cursor = System.Windows.Forms.Cursors.Default; functionReturnValue = true; return functionReturnValue; buildPath_Error: Cursor = System.Windows.Forms.Cursors.Default; Interaction.MsgBox(Err().Description); functionReturnValue = false; return functionReturnValue; }
private bool RelinkTables() { string strConx; ADOX.Table tblLink; catJCMS.ActiveConnection = conJCMS; catLoop.ActiveConnection = conJCMS; catMySQL.ActiveConnection = conMySQL; //opened in ConnectToMySQL try { //drop existing table links foreach (ADOX.Table tblLoop in catLoop.Tables) { if (tblLoop.Type == "LINK") catJCMS.Tables.Delete(tblLoop.Name); } //create a new link for each table in the MySQL database foreach (ADOX.Table tblLoop in catMySQL.Tables) { tblLink = new ADOX.Table(); tblLink.Name = tblLoop.Name; tblLink.ParentCatalog = catJCMS; tblLink.Properties["Jet OLEDB:Link Datasource"].Value = strSourceDbFullPath; tblLink.Properties["Jet OLEDB:Link Provider String"].Value = "ODBC;DATABASE=" + strMySQLDBName + ";FILEDSN=myJCMS;OPTION=0;UID=" + strMySQLUser + ";PWD=" + strMySQLPassword + ";PORT=" + strMySQLPort + ";SERVER=" + strMySQLHost + ";"; tblLink.Properties["Jet OLEDB:Remote Table Name"].Value = tblLoop.Name; tblLink.Properties["Jet OLEDB:Create Link"].Value = true; tblLink.Properties["Jet OLEDB:Cache Link Name/Password"].Value = true; catJCMS.Tables.Append(tblLink); } } catch (Exception e) { WriteOutput("Could not re-link tables. Error: " + e.Message + ". This step must be performed manually. Please see help section 'Troubleshooting problems with database conversion' for details."); return false; } return true; }
public OleDbModel(String server, Int32 port, String username, String password, String database) { //connectionString = String.Format("server={0}; port={1}; user id={2}; password={3}; database={4}; Max Pool Size=50; Min Pool Size=5; Pooling=True; Reset Pooled Connections=False; Cache Server Configuration=True;", server, port, username, password, database); filename = Path.Combine(Directory.GetCurrentDirectory(), @"config\data\vsg.mdb"); connectionString = @"Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" + filename; this.database = database; OleDbConnection connection = new OleDbConnection(connectionString); try { connection.Open(); } catch (OleDbException ex) { // error codes from http://dev.mysql.com/doc/refman/5.0/en/error-messages-server.html switch (ex.ErrorCode) { case -2147467259: // unknown / bad DB, create the database for them { ADOX.CatalogClass mdb = new ADOX.CatalogClass(); try { mdb.Create("Provider=Microsoft.Jet.OLEDB.4.0;" + "Data Source=" + filename + ";" + "Jet OLEDB:Engine Type=5"); ADOX.Table componentTable = new ADOX.Table(); componentTable.Name = "ComponentTable"; mdb.Tables.Append(componentTable); ADOX.Table linkTable = new ADOX.Table(); linkTable.Name = "LinkTable"; mdb.Tables.Append(linkTable); ADOX.Table parameterTable = new ADOX.Table(); parameterTable.Name = "ParameterTable"; mdb.Tables.Append(parameterTable); } catch (Exception e) { // Let the user know what went wrong. MessageBox.Show(e.Message, "File error"); } finally { mdb = null; } break; } default: { connectionString = String.Empty; database = String.Empty; throw new Exception(ex.Message); } } } finally { connection.Close(); } }
protected Boolean CreateTable() { bool result = false; ADOX.Catalog cat = new ADOX.Catalog(); ADOX.Table table = new ADOX.Table(); //Create the table and it's fields. table.Name = "Survey"; table.Columns.Append("password", ADOX.DataTypeEnum.adVarWChar, 40); table.Columns.Append("drink", ADOX.DataTypeEnum.adVarWChar, 40); table.Columns.Append("fastfood", ADOX.DataTypeEnum.adVarWChar, 40); table.Columns.Append("icecream", ADOX.DataTypeEnum.adVarWChar, 40); table.Columns.Append("sports", ADOX.DataTypeEnum.adVarWChar, 40); table.Columns.Append("movie", ADOX.DataTypeEnum.adVarWChar, 40); table.Columns.Append("animated", ADOX.DataTypeEnum.adVarWChar, 40); table.Columns.Append("comics", ADOX.DataTypeEnum.adVarWChar, 40); table.Columns.Append("street", ADOX.DataTypeEnum.adVarWChar, 40); table.Columns.Append("sportsman", ADOX.DataTypeEnum.adVarWChar, 40); table.Columns.Append("tv", ADOX.DataTypeEnum.adVarWChar, 40); table.Columns.Append("mentor", ADOX.DataTypeEnum.adVarWChar, 40); table.Keys.Append("ForeignKey", ADOX.KeyTypeEnum.adKeyForeign, "password", "UserInfo", "password"); 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"); /* string connectionString = @"Provider=Microsoft.Jet.OLEDB.4.0;" + * @"Data Source=..\..\UserInfo.mdb;Persist Security Info=False"; * * OleDbConnection dbConn = new OleDbConnection(connectionString); * cat.let_ActiveConnection(dbConn); * * cat.Tables.Append(table); */ ADODB.Connection con = cat.ActiveConnection as ADODB.Connection; ADODB.Connection Cn = new ADODB.Connection(); Cn.Open(@"Provider=Microsoft.Jet.OLEDB.4.0;" + @"Data Source=C:\Users\Umer\Documents\Visual Studio 2015\Projects\masterpage\UserInfo.mdb;Persist Security Info=False"); cat.ActiveConnection = Cn; cat.Tables.Append(table); if (con != null) { con.Close(); } result = true; //} //catch (Exception ex) //{ // Console.WriteLine(ex); // check.Text = ex.ToString(); result = false; //} cat = null; //check.Text = result.ToString(); return(result); }
protected void CreateNewAccessDatabase(string connstr) { var cat = new ADOX.Catalog(); cat.Create(connstr); var laptimeTable = new ADOX.Table(); laptimeTable.Name = "DriverLaptime"; laptimeTable.Columns.Append("car"); laptimeTable.Columns.Append("driver"); laptimeTable.Columns.Append("laptime", ADOX.DataTypeEnum.adInteger); var pkIdx = new ADOX.Index(); pkIdx.PrimaryKey = true; pkIdx.Name = "PK_DriverLaptime"; pkIdx.Columns.Append("car"); pkIdx.Columns.Append("driver"); laptimeTable.Indexes.Append(pkIdx); var laptimeIdx = new ADOX.Index(); laptimeIdx.PrimaryKey = false; laptimeIdx.Name = "LaptimeIndex_DriverLaptime"; laptimeIdx.Columns.Append("car"); laptimeIdx.Columns.Append("laptime", ADOX.DataTypeEnum.adInteger); laptimeTable.Indexes.Append(laptimeIdx); cat.Tables.Append(laptimeTable); // track이 바뀌면 데이터를 리셋해야 하므로 track 이름을 저장할 곳이 필요하다. var trackTable = new ADOX.Table(); trackTable.Name = "Track"; trackTable.Columns.Append("name"); cat.Tables.Append(trackTable); var timestampTable = new ADOX.Table(); timestampTable.Name = "Timestam"; timestampTable.Columns.Append("v"); cat.Tables.Append(timestampTable); var conn = cat.ActiveConnection as ADODB.Connection; if (conn != null) { try { object recordsAffected; conn.Execute("insert into Track (name) values (' ')", out recordsAffected); conn.Execute($"insert into Timestam (v) values ('{DateTime.Now}')", out recordsAffected); } finally { conn.Close(); } } }
public void CopyTable(ADOX.Table tblAccess) { ADODB.Recordset recMaster = new ADODB.Recordset(); ADODB.Recordset recLoop = new ADODB.Recordset(); int intLoop = 0; string strInfile = ""; string strSQL = "SELECT "; string strRecord; string strLoadFilePath = strSourceDbPath.Replace("\\", "\\\\"); string strFileName = strTempPath + tblAccess.Name + ".txt"; StreamWriter sw = new StreamWriter(strFileName, false); //create the infile strInfile += "LOAD DATA LOCAL INFILE '" + strFileName + "' INTO TABLE " + strMySQLDBName + "." + tblAccess.Name + " "; strInfile += "FIELDS TERMINATED BY ',' "; strInfile += "ESCAPED BY '\\\\' "; strInfile += "LINES TERMINATED BY 0x0d0a "; strInfile += "("; //loop through fields to enumerate them for the infile and build a select statement for (intLoop = 0; intLoop < tblAccess.Columns.Count; intLoop++) { strInfile += MySQLName((tblAccess.Columns[intLoop].Name)); switch (tblAccess.Columns[intLoop].Type) { case ADOX.DataTypeEnum.adDate: //convert to MySQL datetime format strSQL += "FORMAT([" + tblAccess.Columns[intLoop].Name + "], 'YYYY-MM-DD HH:MM:SS') as " + tblAccess.Columns[intLoop].Name; break; default: strSQL += "[" + tblAccess.Columns[intLoop].Name + "]"; break; } if (intLoop < tblAccess.Columns.Count - 1) { strSQL += ","; strInfile += ", "; } } strInfile += ");"; strSQL += " FROM [" + tblAccess.Name + "]"; //open the "Master" recordset recMaster.CursorLocation = ADODB.CursorLocationEnum.adUseClient; recMaster.Open(strSQL, conJCMS_db, ADODB.CursorTypeEnum.adOpenStatic, ADODB.LockTypeEnum.adLockOptimistic, 0); //create the "Loop" recordset, this is a clone of the master, with the exception //that the definedsize for text fields is lengthened. This is because the added //escape characters could potentially exceed the field length in the master recordset recLoop.CursorLocation = ADODB.CursorLocationEnum.adUseClient; ADODB.Fields fdsLoop = recLoop.Fields; ADODB.Fields fdsMaster = recMaster.Fields; foreach (ADODB.Field fldIn in fdsMaster) { if (fldIn.Type.ToString().IndexOf("Char") > 0) { fdsLoop.Append(fldIn.Name, fldIn.Type, fldIn.DefinedSize + 30, ADODB.FieldAttributeEnum.adFldIsNullable, null); } else { fdsLoop.Append(fldIn.Name, fldIn.Type, fldIn.DefinedSize, ADODB.FieldAttributeEnum.adFldIsNullable, null); } } recLoop.Open(System.Reflection.Missing.Value, System.Reflection.Missing.Value, ADODB.CursorTypeEnum.adOpenStatic, ADODB.LockTypeEnum.adLockOptimistic, 0); recLoop.AddNew(System.Reflection.Missing.Value, System.Reflection.Missing.Value); while (!recMaster.EOF) { for (int columnIndex = 0; columnIndex < recMaster.Fields.Count; columnIndex++) { recLoop.Fields[columnIndex].Value = recMaster.Fields[columnIndex].Value; if (recLoop.Fields[columnIndex].Value.ToString().Length > 0) { if ((recLoop.Fields[columnIndex].Value.ToString().IndexOf("\\", 0) + 1) > 0) { recLoop.Fields[columnIndex].Value = recLoop.Fields[columnIndex].Value.ToString().Replace("\\", "\\\\"); } if ((recLoop.Fields[columnIndex].Value.ToString().IndexOf(",", 0) + 1) > 0) { recLoop.Fields[columnIndex].Value = recLoop.Fields[columnIndex].Value.ToString().Replace(",", "\\,"); } if ((recLoop.Fields[columnIndex].Value.ToString().IndexOf(System.Environment.NewLine, 0) + 1) > 0) { recLoop.Fields[columnIndex].Value = recLoop.Fields[columnIndex].Value.ToString().Replace(System.Environment.NewLine, " "); } } } strRecord = recLoop.GetString(ADODB.StringFormatEnum.adClipString, 1, ",", System.Environment.NewLine, "\\N"); recLoop.MovePrevious(); sw.Write(strRecord); recMaster.MoveNext(); } recMaster.Close(); recMaster.ActiveConnection = null; try { recLoop.Close(); } catch { } sw.Close(); ExecuteSQL(strInfile); File.Delete(strFileName); recLoop = null; }
private bool buildPath1_Month(ref string lPath, ref string lServerPath) { bool functionReturnValue = false; ADOX.Catalog cat = new ADOX.Catalog(); ADOX.Table tbl = new ADOX.Table(); ADODB.Recordset rs = default(ADODB.Recordset); ADODB.Connection cn = default(ADODB.Connection); string lFile = null; string holdfile = null; short x = 0; Scripting.FileSystemObject fso = new Scripting.FileSystemObject(); string lDir = null; // ERROR: Not supported in C#: OnErrorStatement Cursor = System.Windows.Forms.Cursors.WaitCursor; //lPath = upgradePath System.Windows.Forms.Application.DoEvents(); lDir = Strings.LCase("\\\\" + lServerPath + "\\C\\4posServer\\"); cn = modRecordSet.openConnectionInstance(ref lPath); if (cn == null) { } else { cat.let_ActiveConnection(cn); foreach (tbl in cat.Tables) { if (tbl.Type == "LINK") { System.Windows.Forms.Application.DoEvents(); //lFile = tbl.Name if (tbl.Properties("Jet OLEDB:Link Datasource").Value != lDir + "pricing.mdb") { tbl.Properties("Jet OLEDB:Link Datasource").Value = Strings.Replace(Strings.LCase(tbl.Properties("Jet OLEDB:Link Datasource").Value), Strings.LCase("C:\\4posServer\\"), lDir); } //DoEvents //If tbl.Properties("Jet OLEDB:Link Datasource") <> lDIR & "pricing.mdb" Then // tbl.Properties("Jet OLEDB:Link Datasource") = Replace(LCase(tbl.Properties("Jet OLEDB:Link Datasource")), LCase("C:\4posServer\"), lDIR) //End If } } //UPGRADE_NOTE: Object cat may not be destroyed until it is garbage collected. Click for more: 'ms-help://MS.VSCC.v90/dv_commoner/local/redirect.htm?keyword="6E35BFF6-CD74-4B09-9689-3E1A43DF8969"' cat = null; cn.Close(); //UPGRADE_NOTE: Object cn may not be destroyed until it is garbage collected. Click for more: 'ms-help://MS.VSCC.v90/dv_commoner/local/redirect.htm?keyword="6E35BFF6-CD74-4B09-9689-3E1A43DF8969"' cn = null; cat = new ADOX.Catalog(); } System.Windows.Forms.Application.DoEvents(); Cursor = System.Windows.Forms.Cursors.Default; functionReturnValue = true; return(functionReturnValue); buildPath_Error: Cursor = System.Windows.Forms.Cursors.Default; Interaction.MsgBox(Err().Description); functionReturnValue = false; return(functionReturnValue); }
private dbdefinitionEntity GenerateEntity(ADOX.Table ptbl, ADODB.Connection pcon) { dbdefinitionEntity entity = new dbdefinitionEntity(); entity.name = ptbl.Name; entity.description = GetTableDescription(ptbl, pcon); // Generate attributes ArrayList alAttributes = new ArrayList(); int intColIndex = 0; ADODB.Recordset rs = new ADODB.RecordsetClass(); rs.Open("SELECT * FROM [" + ptbl.Name + "] WHERE (3=4)", pcon, ADODB.CursorTypeEnum.adOpenStatic, ADODB.LockTypeEnum.adLockReadOnly, 0); foreach (ADODB.Field fld in rs.Fields) { alAttributes.Add(GenerateColumn(ptbl.Columns[fld.Name], intColIndex)); intColIndex++; } rs.Close(); if (alAttributes.Count != 0) { entity.attributes = (dbdefinitionEntityAttribute[])alAttributes.ToArray(typeof(dbdefinitionEntityAttribute)); } // Generate primary/unique keys/constraints entity.keys = new dbdefinitionEntityKeys(); ArrayList alUniqueKeys = new ArrayList(); ArrayList alForeignKeys = new ArrayList(); int intIndex = 0; foreach (ADOX.Key key in ptbl.Keys) { switch (key.Type) { case ADOX.KeyTypeEnum.adKeyPrimary: entity.keys.primarykey = new dbdefinitionEntityKeysPrimarykey(); entity.keys.primarykey.name = ptbl.Name + "_pk"; // get all primary key columns ArrayList aAttr = new ArrayList(); foreach (ADOX.Column col in key.Columns) { dbdefinitionEntityKeysPrimarykeyAttributeref attr = new dbdefinitionEntityKeysPrimarykeyAttributeref(); attr.attribute = col.Name; aAttr.Add(attr); } // create the primary key attribute array entity.keys.primarykey.attributeref = (dbdefinitionEntityKeysPrimarykeyAttributeref []) aAttr.ToArray(typeof(dbdefinitionEntityKeysPrimarykeyAttributeref)); break; case ADOX.KeyTypeEnum.adKeyUnique: dbdefinitionEntityKeysUniquekey uniquekey = new dbdefinitionEntityKeysUniquekey(); ArrayList alAttributeRefs = new ArrayList(); foreach (ADOX.Column col in key.Columns) { dbdefinitionEntityKeysUniquekeyAttributeref attributeref = new dbdefinitionEntityKeysUniquekeyAttributeref(); attributeref.attribute = col.Name; alAttributeRefs.Add(attributeref); } if (alAttributeRefs.Count != 0) { uniquekey.attributeref = (dbdefinitionEntityKeysUniquekeyAttributeref[])alAttributeRefs.ToArray(typeof(dbdefinitionEntityKeysUniquekeyAttributeref)); } // check for duplicate indexes - ignore duplicate index defintions bool isDuplicate = false; foreach (dbdefinitionEntityKeysUniquekey existingKey in alUniqueKeys) { if (uniquekey.attributeref.Length == existingKey.attributeref.Length) { isDuplicate = true; for (int i = 0; i < uniquekey.attributeref.Length; i++) { if (!uniquekey.attributeref[i].attribute.Equals(existingKey.attributeref[i].attribute)) { isDuplicate = false; break; } } if (isDuplicate) { break; } } } if (!isDuplicate) { uniquekey.name = ptbl.Name + "_uk" + intIndex; alUniqueKeys.Add(uniquekey); } break; case ADOX.KeyTypeEnum.adKeyForeign: dbdefinitionEntityKeysForeignkey foreignkey = new dbdefinitionEntityKeysForeignkey(); foreignkey.name = ptbl.Name + "_fk" + intIndex; foreignkey.foreignentity = key.RelatedTable; foreignkey.cascadingdelete = (key.DeleteRule & ADOX.RuleEnum.adRICascade) != 0; foreignkey.attributeref = new dbdefinitionEntityKeysForeignkeyAttributeref(); foreignkey.attributeref.attribute = key.Columns[0].Name; foreignkey.attributeref.foreignattribute = key.Columns[0].RelatedColumn; alForeignKeys.Add(foreignkey); break; } intIndex++; } if (alUniqueKeys.Count != 0) { entity.keys.uniquekey = (dbdefinitionEntityKeysUniquekey[])alUniqueKeys.ToArray(typeof(dbdefinitionEntityKeysUniquekey)); } if (alForeignKeys.Count != 0) { entity.keys.foreignkey = (dbdefinitionEntityKeysForeignkey[])alForeignKeys.ToArray(typeof(dbdefinitionEntityKeysForeignkey)); } // Generate indices ArrayList alIndexes = new ArrayList(); intIndex = 0; foreach (ADOX.Index idx in ptbl.Indexes) { if (!idx.PrimaryKey) { dbdefinitionEntityIndex index = new dbdefinitionEntityIndex(); index.unique = idx.Unique; index.ignorenulls = (idx.IndexNulls == ADOX.AllowNullsEnum.adIndexNullsIgnore); ArrayList alAttributeRefs = new ArrayList(); foreach (ADOX.Column col in idx.Columns) { dbdefinitionEntityIndexAttributeref attributeref = new dbdefinitionEntityIndexAttributeref(); attributeref.attribute = col.Name; alAttributeRefs.Add(attributeref); } index.attributeref = (dbdefinitionEntityIndexAttributeref[])alAttributeRefs.ToArray(typeof(dbdefinitionEntityIndexAttributeref)); // check for duplicate indexes - ignore duplicate index defintions bool isDuplicate = false; foreach (dbdefinitionEntityIndex existingIndex in alIndexes) { if (index.attributeref.Length == existingIndex.attributeref.Length) { isDuplicate = true; for (int i = 0; i < index.attributeref.Length; i++) { if (!index.attributeref[i].attribute.Equals(existingIndex.attributeref[i].attribute)) { isDuplicate = false; break; } } if (isDuplicate) { break; } } } if (!isDuplicate) { index.name = ptbl.Name + "_idx" + intIndex; alIndexes.Add(index); } intIndex++; } } if (alIndexes.Count != 0) { entity.indexes = (dbdefinitionEntityIndex[])alIndexes.ToArray(typeof(dbdefinitionEntityIndex)); } // mark 1:1 relationship foreign keys with "one-to-one" attribute if (entity.keys != null && entity.keys.foreignkey != null && entity.indexes != null) { foreach (dbdefinitionEntityKeysForeignkey foreignkey in entity.keys.foreignkey) { foreach (dbdefinitionEntityIndex index in entity.indexes) { if (index.attributeref.Length == 1 && index.attributeref[0].attribute.Equals(foreignkey.attributeref.foreignattribute)) { if (index.unique) { foreignkey.onetoone = true; } } } } } // Generate hashcode of entity definition XmlSerializer entityDefSerializer = new XmlSerializer(typeof(dbdefinitionEntity)); StringWriter stringWriter = new StringWriter(); entityDefSerializer.Serialize(stringWriter, entity); String entityDef = stringWriter.ToString(); entity.hashcode = entityDef.GetHashCode().ToString(); return(entity); }
private static void linkFirstTable(ref string Source) { ADOX.Catalog cat = default(ADOX.Catalog); ADOX.Table tbl = default(ADOX.Table); Scripting.FileSystemObject fso = new Scripting.FileSystemObject(); if (fso.FileExists(strLocation)) { } else { return; } cat = new ADOX.Catalog(); short x = 0; //Open the catalog. //MsgBox StrLocRep cat.let_ActiveConnection(modReport.cnnDBConsReport); for (x = cat.Tables.Count - 1; x >= 0; x += -1) { switch (Strings.LCase(cat.Tables(x).Name)) { case "adayendstockitemlnk": cat.Tables.delete(cat.Tables(x).Name); break; } } tbl = new ADOX.Table(); tbl.Name = "aDayEndStockItemLnk"; tbl.ParentCatalog = cat; tbl.Properties("Jet OLEDB:Link Datasource").Value = strLocation; tbl.Properties("Jet OLEDB:Remote Table Name").Value = "DayEndStockItemLnk"; tbl.Properties("Jet OLEDB:Create Link").Value = true; cat.Tables.Append(tbl); cat.Tables.Refresh(); cat = null; }
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; } }
/// <summary> /// Accesss to sqlce /// </summary> /// <param name="p"></param> /// <param name="sdfFile"></param> public static bool SyncMdbToSdf(string mdbFile, string sdfFile, bool NeedCopyData, string targetDbPwd) { bool result = false; if (!File.Exists(mdbFile)) { "ImportData_FileNotFound".GetFromResourece().Notify(); return(false); } ICoreEAHander srcEngine = new CoreEA.CoreE(CoreE.UsedDatabaseType.OleDb).X_Handler; srcEngine.Open(new LoginInfo_Oledb() { Database = mdbFile }); if (!srcEngine.IsOpened) { "ImportData_ReadError".GetFromResourece().Notify(); return(false); } //Filter system table List <string> tableList = new List <string>(); foreach (string item in srcEngine.GetTableListInDatabase()) { if (!item.StartsWith("MSys")) { tableList.Add(item); } } if (tableList == null) { "ImportData_NoTable".GetFromResourece().Notify(); return(false); } ICoreEAHander destEngine = new CoreEA.CoreE(CoreE.UsedDatabaseType.SqlCE35).X_Handler; if (!File.Exists(sdfFile)) { if (!destEngine.CreateDatabase(new LoginInfo_SSCE() { DbName = sdfFile, IsEncrypted = false, IsCaseSensitive = false })) { "ImportData_CreateSSCEFileFailure".GetFromResourece().Notify(); return(false); } } destEngine.Open(new LoginInfo_SSCE() { DbName = sdfFile, Pwd = targetDbPwd }); List <string> targetDBList = destEngine.GetTableListInDatabase(); try { foreach (string tableName in tableList) { //Don't import table which name has existed. if (targetDBList.Contains(tableName)) { continue; } string sqlCeTableName = tableName; //if (Properties.Settings.Default.IsAllowAutoParseInvalidCharsInTableName) //{ // sqlCeTableName= sqlCeTableName.Replace(" ", ""); //} string strconnection = string.Format("provider=microsoft.jet.oledb.4.0;data source={0}", mdbFile); ADODB.Connection conn = new ADODB.ConnectionClass(); //conn.ConnectionString = strconnection; conn.Open(strconnection, "Admin", "", 0); //Prepare to retrive schema info from access via COM ADOX.Catalog catelog = new ADOX.CatalogClass(); catelog.let_ActiveConnection(conn); ADOX.Table tempTable = catelog.Tables[tableName]; //Start Generate the Create Sdf table command string tempCreateTableCmd = string.Empty; tempCreateTableCmd = String.Format("CREATE TABLE [{0}] ", sqlCeTableName); string tempSechma = string.Empty; for (int i = 0; i < tempTable.Columns.Count; i++) { Debug.WriteLine("Source Field Name ------>" + tempTable.Columns[i].Name); tempSechma += String.Format("[{0}] {1},", tempTable.Columns[i].Name, CoreEA.Utility.TypeConvertor.ParseADODbTypeToSqlCeDbType(tempTable.Columns[i].Type.ToString(), tempTable.Columns[i].DefinedSize) ); } tempSechma = tempSechma.Substring(0, tempSechma.Length - 1); tempCreateTableCmd = String.Format("{0} ({1})", tempCreateTableCmd, tempSechma); if (destEngine.DoExecuteNonQuery(tempCreateTableCmd) != -1) { return(false); } if (NeedCopyData) { CopyTable(srcEngine.GetConnection(), (SqlCeConnection)destEngine.GetConnection(), string.Format("Select * from [{0}]", tableName), sqlCeTableName); } } result = true; } catch (Exception ee) { ee.HandleMyException(); //((SqlCeDatabase)destEngine.DbHandler).CloseSharedConnection(); } return(result); }
private void linkTables() { //catalogs contain all the tables that exist in the database (or are linked there). //need one for JCMS (access interface) for a destination and one for mysql (source) ADOX.Catalog catJCMS = new ADOX.Catalog(); ADOX.Catalog catMySQL = new ADOX.Catalog(); //connections allow you to read all tables into catalogs, need one for JCMS to say where tables are supposed to go //and one for MySQL to say where connections are coming from ADODB.Connection conJCMS = new ADODB.Connection(); ADODB.Connection conMySQL = new ADODB.Connection(); string userDSN = "JCMS" + host + databaseName; try { //STEP 1: connect to JCMS interface string strConx = "Provider=Microsoft.ACE.OLEDB.12.0;Persist Security Info=True;Data Source=" + interfacePath + ";"; conJCMS.Mode = ADODB.ConnectModeEnum.adModeReadWrite; conJCMS.Open(strConx, "", "", 0); //STEP 1.A.: Create catalog for JCMS catJCMS.ActiveConnection = conJCMS; //STEP 2: CREATE USER DSN String strAttributes = string.Format("DSN={0};", userDSN); strAttributes = strAttributes + "Database=" + databaseName + ";"; strAttributes = strAttributes + string.Format("Description=DSN for {0}:{1};", host, databaseName); strAttributes = strAttributes + "Server=" + host + ";"; strAttributes = strAttributes + "User="******";"; strAttributes = strAttributes + "Password="******";"; IntPtr please = new IntPtr(0); bool DSNSuccess = SQLConfigDataSource(please, 1, "MySQL ODBC 3.51 Driver", strAttributes); if (!DSNSuccess) { WriteOutput("DSN Could not be created, could be a permissions issue, or ODBC driver is not properly installed. Tables will have to be linked manually."); } else { WriteOutput("DSN created successfully"); //STEP 3: open mysql connection using user dsn created above //string strConxString = "Provider=MSDASQL;" + "Driver=" + "MySQL ODBC 3.51 Driver" + ";" + "Server=" + host + ";" + "UID=" + username + ";" + "PWD=" + password + ";" + "Port=" + port; string strConxString = string.Format("Driver={0};Server={1};Database={2};Uid={3};Pwd={4};", "MySQL ODBC 3.51 Driver", host, databaseName, username, password); conMySQL.Open(strConxString, username, password, 0); catMySQL.ActiveConnection = conMySQL; ADOX.Table tblLink; //create links from MySQL to JCMS. foreach (ADOX.Table tblLoop in catMySQL.Tables) { tblLink = new ADOX.Table(); tblLink.Name = tblLoop.Name; tblLink.ParentCatalog = catJCMS; tblLink.Properties["Jet OLEDB:Link Datasource"].Value = interfacePath; tblLink.Properties["Jet OLEDB:Link Provider String"].Value = "ODBC;DATABASE=" + databaseName + ";DSN=" + userDSN +";OPTION=0;UID=" + username + ";PWD=" + password + ";PORT=" + port + ";SERVER=" + host + ";"; tblLink.Properties["Jet OLEDB:Remote Table Name"].Value = tblLoop.Name; tblLink.Properties["Jet OLEDB:Create Link"].Value = true; tblLink.Properties["Jet OLEDB:Cache Link Name/Password"].Value = true; catJCMS.Tables.Append(tblLink); WriteOutput(string.Format("Table {0} successfully linked.", tblLoop.Name)); } if (programVariables.getInstall()) { WriteOutput("Install process complete, tables successfully linked."); } else { WriteOutput("Upgrade process complete, tables successfully linked."); } } } catch (Exception e) { WriteOutput("The following error occurred when trying to relink tables: " + e.Message + " This step will have to be completed manually. For help please visit our FAQs page at http://colonymanagement.jax.org/support-2/faqs/ " + "or our forums at http://community.jax.org/jcms_discussion_forum/default.aspx"); } }
public static bool CreateUserDatabase(ADOX.Catalog catalog) { ADOX.Table table = new ADOX.Table(); table.Name = "Message"; catalog.Tables.Append(table); ADOX.Column col = newColumn("sender", ADOX.DataTypeEnum.adWChar, catalog); col.DefinedSize = 50; col.Attributes = ADOX.ColumnAttributesEnum.adColNullable; table.Columns.Append(col); col = newColumn("receiver", ADOX.DataTypeEnum.adWChar, catalog); col.DefinedSize = 50; col.Attributes = ADOX.ColumnAttributesEnum.adColNullable; table.Columns.Append(col); col = newColumn("content", ADOX.DataTypeEnum.adLongVarWChar, catalog); col.DefinedSize = 5000; col.Attributes = ADOX.ColumnAttributesEnum.adColNullable; table.Columns.Append(col); col = newColumn("message_date", ADOX.DataTypeEnum.adDate, catalog); table.Columns.Append(col); col = newColumn("type", ADOX.DataTypeEnum.adWChar, catalog); col.DefinedSize = 50; col.Attributes = ADOX.ColumnAttributesEnum.adColNullable; table.Columns.Append(col); col = newColumn("group_message", ADOX.DataTypeEnum.adBoolean, catalog); table.Columns.Append(col); table = new ADOX.Table(); table.Name = "Friend"; catalog.Tables.Append(table); col = newColumn("friend_username", ADOX.DataTypeEnum.adWChar, catalog); col.DefinedSize = 50; col.Attributes = ADOX.ColumnAttributesEnum.adColNullable; table.Columns.Append(col); table = new ADOX.Table(); table.Name = "Group"; catalog.Tables.Append(table); col = newColumn("group_id", ADOX.DataTypeEnum.adWChar, catalog); col.DefinedSize = 50; col.Attributes = ADOX.ColumnAttributesEnum.adColNullable; table.Columns.Append(col); table = new ADOX.Table(); table.Name = "friend_request"; catalog.Tables.Append(table); col = newColumn("sender", ADOX.DataTypeEnum.adWChar, catalog); col.DefinedSize = 50; col.Attributes = ADOX.ColumnAttributesEnum.adColNullable; table.Columns.Append(col); col = newColumn("receiver", ADOX.DataTypeEnum.adWChar, catalog); col.DefinedSize = 50; col.Attributes = ADOX.ColumnAttributesEnum.adColNullable; table.Columns.Append(col); col = newColumn("request_date", ADOX.DataTypeEnum.adDate, catalog); table.Columns.Append(col); return(true); }
public override bool CreateColumns(ADOX.Catalog cat, string tableName) { try { ADOX.Table tbl = new ADOX.Table { ParentCatalog = cat, Name = tableName }; ADOX.Column col0 = new ADOX.Column { ParentCatalog = cat, Type = ADOX.DataTypeEnum.adInteger, Name = "索引" }; col0.Properties["Jet OLEDB:Allow Zero Length"].Value = false; tbl.Columns.Append(col0, ADOX.DataTypeEnum.adInteger, 1); ADOX.Column col1 = new ADOX.Column { ParentCatalog = cat, Type = ADOX.DataTypeEnum.adDate, Name = "日期" }; col1.Properties["Jet OLEDB:Allow Zero Length"].Value = false; tbl.Columns.Append(col1, ADOX.DataTypeEnum.adDate, 1); ADOX.Column col2 = new ADOX.Column { ParentCatalog = cat, Name = "产能总量", Type = ADOX.DataTypeEnum.adInteger }; col2.Properties["Jet OLEDB:Allow Zero Length"].Value = false; tbl.Columns.Append(col2, ADOX.DataTypeEnum.adInteger, 1); ADOX.Column col3 = new ADOX.Column { ParentCatalog = cat, Name = "贝壳", Type = ADOX.DataTypeEnum.adInteger }; col3.Properties["Jet OLEDB:Allow Zero Length"].Value = false; tbl.Columns.Append(col3, ADOX.DataTypeEnum.adInteger, 1); ADOX.Column col4 = new ADOX.Column { ParentCatalog = cat, Name = "破角", Type = ADOX.DataTypeEnum.adInteger }; col4.Properties["Jet OLEDB:Allow Zero Length"].Value = false; tbl.Columns.Append(col4, ADOX.DataTypeEnum.adInteger, 1); ADOX.Column col5 = new ADOX.Column { ParentCatalog = cat, Name = "凸边", Type = ADOX.DataTypeEnum.adInteger }; col5.Properties["Jet OLEDB:Allow Zero Length"].Value = false; tbl.Columns.Append(col5, ADOX.DataTypeEnum.adInteger, 1); tbl.Keys.Append("PrimaryKey", ADOX.KeyTypeEnum.adKeyPrimary, "索引", "", ""); cat.Tables.Append(tbl);; tbl = null; return(true); } catch (Exception ex) { return(false); } }
private void linkTables(ref string source) { ADOX.Catalog cat = default(ADOX.Catalog); ADOX.Table tbl = default(ADOX.Table); Scripting.FileSystemObject fso = new Scripting.FileSystemObject(); if (fso.FileExists(modRecordSet.serverPath + source + ".mdb")) { } else { return; } cat = new ADOX.Catalog(); short x = 0; // Open the catalog. cat.let_ActiveConnection(modReport.cnnDBreport); for (x = cat.Tables.Count - 1; x >= 0; x += -1) { switch (Strings.LCase(cat.Tables(x).name)) { case "acustomertransaction": case "adayendstockitemlnk": case "adeclaration": case "asale": case "asaleitem": case "asuppliertransaction": cat.Tables.delete(cat.Tables(x).name); break; } } tbl = new ADOX.Table(); tbl.name = "aCustomerTransaction"; tbl.ParentCatalog = cat; tbl.Properties("Jet OLEDB:Link Datasource").Value = modRecordSet.serverPath + source + ".mdb"; tbl.Properties("Jet OLEDB:Remote Table Name").Value = "CustomerTransaction"; tbl.Properties("Jet OLEDB:Create Link").Value = true; cat.Tables.Append(tbl); tbl = new ADOX.Table(); tbl.name = "aDayEndStockItemLnk"; tbl.ParentCatalog = cat; tbl.Properties("Jet OLEDB:Link Datasource").Value = modRecordSet.serverPath + source + ".mdb"; tbl.Properties("Jet OLEDB:Remote Table Name").Value = "DayEndStockItemLnk"; tbl.Properties("Jet OLEDB:Create Link").Value = true; cat.Tables.Append(tbl); tbl = new ADOX.Table(); tbl.name = "aDeclaration"; tbl.ParentCatalog = cat; tbl.Properties("Jet OLEDB:Link Datasource").Value = modRecordSet.serverPath + source + ".mdb"; tbl.Properties("Jet OLEDB:Remote Table Name").Value = "Declaration"; tbl.Properties("Jet OLEDB:Create Link").Value = true; cat.Tables.Append(tbl); tbl = new ADOX.Table(); tbl.name = "aSale"; tbl.ParentCatalog = cat; tbl.Properties("Jet OLEDB:Link Datasource").Value = modRecordSet.serverPath + source + ".mdb"; tbl.Properties("Jet OLEDB:Remote Table Name").Value = "Sale"; tbl.Properties("Jet OLEDB:Create Link").Value = true; cat.Tables.Append(tbl); tbl = new ADOX.Table(); tbl.name = "aSaleItem"; tbl.ParentCatalog = cat; tbl.Properties("Jet OLEDB:Link Datasource").Value = modRecordSet.serverPath + source + ".mdb"; tbl.Properties("Jet OLEDB:Remote Table Name").Value = "SaleItem"; tbl.Properties("Jet OLEDB:Create Link").Value = true; cat.Tables.Append(tbl); tbl = new ADOX.Table(); tbl.name = "aSupplierTransaction"; tbl.ParentCatalog = cat; tbl.Properties("Jet OLEDB:Link Datasource").Value = modRecordSet.serverPath + source + ".mdb"; tbl.Properties("Jet OLEDB:Remote Table Name").Value = "SupplierTransaction"; tbl.Properties("Jet OLEDB:Create Link").Value = true; cat.Tables.Append(tbl); cat.Tables.Refresh(); cat = null; }
/// <summary> /// Creates a new database and sets how many cards this is going to pull from Gatherer /// </summary> /// <returns></returns> static string mkDatabase() { try { Console.WriteLine("Enter the path/name for the new database. \nExample: C:\\Users\\w9jds\\Desktop\\GathererDB.mdb"); string input = Console.ReadLine(); do { try { Console.WriteLine("What multiverseid would you like to start with?"); multiverseidstart = Convert.ToInt32(Console.ReadLine()); Console.WriteLine("What multiverseid would you like to end with?"); multiverseidend = Convert.ToInt32(Console.ReadLine()); } catch (Exception) { multiverseidend = 0; multiverseidstart = 1; } } while (multiverseidstart > multiverseidend); ADOX.Catalog CreateDB = new ADOX.Catalog(); ADOX.Table CardTable = new ADOX.Table(); CardTable.Name = "Cards"; CardTable.Columns.Append("MultiverseID"); CardTable.Columns.Append("Name"); CardTable.Columns.Append("ConvManaCost"); CardTable.Columns.Append("Type"); CardTable.Columns.Append("CardText"); CardTable.Columns.Append("Power"); CardTable.Columns.Append("Toughness"); CardTable.Columns.Append("Expansion"); CardTable.Columns.Append("Rarity"); CardTable.Columns.Append("ImgURL"); CreateDB.Create("Provider=Microsoft.Jet.OLEDB.4.0; Data Source=" + input + "; Jet OLEDB:Engine Type=5"); CreateDB.Tables.Append(CardTable); //ask: Console.WriteLine("Would you like to add card legalities to the database? (Will add A LOT of time to runtime.) y/n"); // string leginput = Console.ReadLine(); // if (string.Equals(leginput, "y", StringComparison.OrdinalIgnoreCase) == true || string.Equals(leginput, "yes", StringComparison.OrdinalIgnoreCase)) // incLegality = true; // else if (string.Equals(leginput, "n", StringComparison.OrdinalIgnoreCase) == true || string.Equals(leginput, "no", StringComparison.OrdinalIgnoreCase)) // incLegality = false; // else // goto ask; //if (incLegality == true) //{ // ADOX.Table Legality = new ADOX.Table(); // Legality.Name = "CardsLegality"; // Legality.Columns.Append("MultiverseID"); // Legality.Columns.Append("Format"); // Legality.Columns.Append("Legality"); // CreateDB.Tables.Append(Legality); //} OleDbConnection DBcon = CreateDB.ActiveConnection as OleDbConnection; if (DBcon != null) { DBcon.Close(); } return(input); } catch (OleDbException) { Console.WriteLine("Entered Invalid Path"); return(null); } catch (Exception) { Console.WriteLine("\nAn error has occured while making the Database"); return(null); } }
/// <summary> /// 添加表. /// </summary> /// <returns></returns> public string CreateTable(string dbPath, string tableName, string[] fieldNames, string[] fieldTypes) { if (fieldNames.Length != fieldTypes.Length) { return("field name count discordance with type count -->CreateTable"); } string names = GetTableName(); if (names.Contains(tableName)) { return("this data base already exist the table -->CreateTable"); } ADOX.DataTypeEnum[] ADOXFieldTypes = new ADOX.DataTypeEnum[fieldTypes.Length]; for (int i = 0; i < fieldTypes.Length; i++) { switch (fieldTypes[i]) { case "int": ADOXFieldTypes[i] = ADOX.DataTypeEnum.adInteger; break; case "string": ADOXFieldTypes[i] = ADOX.DataTypeEnum.adVarWChar; break; case "double": ADOXFieldTypes[i] = ADOX.DataTypeEnum.adDouble; break; case "bool": ADOXFieldTypes[i] = ADOX.DataTypeEnum.adBoolean; break; default: return("nonsupport the data type -->CreateTable"); } } try { ADOX.Catalog cataLog = new ADOX.Catalog(); ADODB.Connection cn = new ADODB.Connection(); cn.Open("Provider=Microsoft.ACE.OLEDB.12.0;Data Source=" + dbPath + ";" + "Jet OLEDB:Engine Type=5"); cataLog.ActiveConnection = cn; ADOX.Table table = new ADOX.Table(); table.ParentCatalog = cataLog; table.Name = tableName; for (int i = 0; i < fieldNames.Length; i++) { ADOX.Column col = new ADOX.Column(); col.ParentCatalog = cataLog; col.Type = ADOXFieldTypes[i]; col.Name = fieldNames[i]; col.Properties["Jet OLEDB:Allow Zero Length"].Value = true; col.Properties["AutoIncrement"].Value = false; //自动编号,注意此处不允许自动编号 table.Columns.Append(col, ADOX.DataTypeEnum.adDouble, 50); } cataLog.Tables.Append(table); ShowTableNames(combox_table_names); return("OK"); } catch (Exception ex) { return(ex.Message + " -->CreateTable"); } }
/// <summary> /// Creates a new database and sets how many cards this is going to pull from Gatherer /// </summary> /// <returns></returns> static string mkDatabase() { try { Console.WriteLine("Enter the path/name for the new database. \nExample: C:\\Users\\w9jds\\Desktop\\GathererDB.mdb"); string input = Console.ReadLine(); do { try { Console.WriteLine("What multiverseid would you like to start with?"); multiverseidstart = Convert.ToInt32(Console.ReadLine()); Console.WriteLine("What multiverseid would you like to end with?"); multiverseidend = Convert.ToInt32(Console.ReadLine()); } catch (Exception) { multiverseidend = 0; multiverseidstart = 1; } } while (multiverseidstart > multiverseidend); ADOX.Catalog CreateDB = new ADOX.Catalog(); ADOX.Table CardTable = new ADOX.Table(); CardTable.Name = "Cards"; CardTable.Columns.Append("MultiverseID"); CardTable.Columns.Append("Name"); CardTable.Columns.Append("ConvManaCost"); CardTable.Columns.Append("Type"); CardTable.Columns.Append("CardText"); CardTable.Columns.Append("Power"); CardTable.Columns.Append("Toughness"); CardTable.Columns.Append("Expansion"); CardTable.Columns.Append("Rarity"); CardTable.Columns.Append("ImgURL"); CreateDB.Create("Provider=Microsoft.Jet.OLEDB.4.0; Data Source=" + input + "; Jet OLEDB:Engine Type=5"); CreateDB.Tables.Append(CardTable); //ask: Console.WriteLine("Would you like to add card legalities to the database? (Will add A LOT of time to runtime.) y/n"); // string leginput = Console.ReadLine(); // if (string.Equals(leginput, "y", StringComparison.OrdinalIgnoreCase) == true || string.Equals(leginput, "yes", StringComparison.OrdinalIgnoreCase)) // incLegality = true; // else if (string.Equals(leginput, "n", StringComparison.OrdinalIgnoreCase) == true || string.Equals(leginput, "no", StringComparison.OrdinalIgnoreCase)) // incLegality = false; // else // goto ask; //if (incLegality == true) //{ // ADOX.Table Legality = new ADOX.Table(); // Legality.Name = "CardsLegality"; // Legality.Columns.Append("MultiverseID"); // Legality.Columns.Append("Format"); // Legality.Columns.Append("Legality"); // CreateDB.Tables.Append(Legality); //} OleDbConnection DBcon = CreateDB.ActiveConnection as OleDbConnection; if (DBcon != null) DBcon.Close(); return input; } catch (OleDbException) { Console.WriteLine("Entered Invalid Path"); return null; } catch (Exception) { Console.WriteLine("\nAn error has occured while making the Database"); return null; } }