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