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();
}
override internal void LoadAll()
{
ADODB.Connection cnn = new ADODB.Connection();
ADODB.Recordset rs = new ADODB.Recordset();
ADOX.Catalog cat = new ADOX.Catalog();
// Open the Connection
cnn.Open(dbRoot.ConnectionString, null, null, 0);
cat.ActiveConnection = cnn;
ADOX.Procedure proc = cat.Procedures[this.Procedure.Name];
// Retrieve Parameter information
rs.Source = proc.Command as ADODB.Command;
rs.Fields.Refresh();
Pervasive.PervasiveResultColumn resultColumn;
if(rs.Fields.Count > 0)
{
int ordinal = 0;
foreach(ADODB.Field field in rs.Fields)
{
resultColumn = this.dbRoot.ClassFactory.CreateResultColumn() as Pervasive.PervasiveResultColumn;
resultColumn.dbRoot = this.dbRoot;
resultColumn.ResultColumns = this;
resultColumn.name = field.Name;
resultColumn.ordinal = ordinal++;
resultColumn.typeName = field.Type.ToString();
this._array.Add(resultColumn);
}
}
cnn.Close();
}
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 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 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);
}
}
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 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 void CreateFile(string fileName, string pwd = "")
{
string dir = Path.GetDirectoryName(fileName);
if (!Directory.Exists(dir))
{
Directory.CreateDirectory(dir);
}
if (!File.Exists(fileName))
{
ADOX.Catalog cat = new ADOX.Catalog();
cat.Create(Provider + @"Data Source=" + fileName + ";" + Password + "=" + pwd);
ADODB.Connection con = cat.ActiveConnection as ADODB.Connection;
if (con != null)
{
con.Close();
}
cat.ActiveConnection = null;
Marshal.ReleaseComObject(cat);
cat = null;
GC.Collect();
}
}
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 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)));
}
private static void PopulateStoredProcedures(ADOX.Catalog catalog, ADODB.Connection connection, NodeViewModel storedProcedureContainer)
{
foreach (ADOX.Procedure procedure in catalog.Procedures)
{
storedProcedureContainer.Children.Add(new StoredProcedureNodeViewModel(procedure, connection, storedProcedureContainer));
}
}
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();
}
}
/// <summary>
/// This function will create the database that will be used for retreiving the PRPO data.
/// </summary>
/// <exception cref="DatabaseCreationFailureException"></exception>
/// <returns>
/// A boolean value indicating whether or not he database was created.
/// </returns>
public static bool CreateAccessDB()
{
bool result = false;
ADOX.Catalog cat = new ADOX.Catalog();
if (!File.Exists(AI.Path))
{
try
{
cat.Create(AI.connectionString());
ADODB.Connection con = cat.ActiveConnection as ADODB.Connection;
if (con != null)
{
con.Close();
}
result = true;
AccessDatabaseUtils.US_PRPO_TableExists = false;
AccessDatabaseUtils.MX_PRPO_TableExists = false;
}
catch (Exception)
{
throw new DatabaseCreationFailureException("There was an error while creating the MS Access Database.");
}
}
return(result);
}
public string CreateDataBase(string CID, string CType)
{
connect.ConnectionString = @"Provider=Microsoft.ACE.OLEDB.12.0;Data Source=BadgesTable.accdb;";
string IsDataBaseCreated = connect.ConnectionString;
if ((File.Exists("BadgesTable.accdb")))//update database
{
Console.WriteLine("Found");
connect.Open();
OleDbCommand command = new OleDbCommand();
command.Connection = connect;
command.CommandText = "INSERT INTO Table1 (Badge_Number, Door_Access) values('" + CID + "','" + CType + "')";
connect.Close();
}
else if (!(File.Exists("BadgesTable.accdb")))//Create the database.
{
var create = new ADOX.Catalog();
create.Create(connect.ConnectionString);
connect.Open();
OleDbCommand command = new OleDbCommand();
command.Connection = connect;
command.CommandText = "CREATE TABLE Table1 (" +
"[Badge_Number] VARCHAR( 50 ) ," +
"[Door_Access] VARCHAR( 50 ))";
command.ExecuteNonQuery();
command.CommandText = "INSERT INTO Table1 (Badge_Number, Door_Access) values('" + CID + "','" + CType + "')";
command.ExecuteNonQuery();
connect.Close();
}
return(IsDataBaseCreated);
}
/// <summary>
/// Створення файла БД
/// </summary>
/// <param name="name">назва/шлях для файлу БД</param>
/// <returns></returns>
public static string CreateFile(string name)
{
// створення каталогу
var catalog = new ADOX.Catalog();
// провайдер і шлях до файлу БД
string connect = "Provider=Microsoft.Jet." +
$@"OLEDB.4.0; Data Source = {name}.mdb";
try
{
// створення бази данних
catalog.Create(connect);
Console.WriteLine("File of data base was create.");
}
catch (COMException ex)
{
Console.WriteLine(ex.Message);
}
finally
{
catalog = null;
}
return(connect);
}
public string CreateDataBase(string CID, string CType, string CDescription, string CAmount, string CdateIncident)
{
connect.ConnectionString = @"Provider=Microsoft.ACE.OLEDB.12.0;Data Source=CafeTable.accdb;";
string IsDataBaseCreated = @"Provider=Microsoft.ACE.OLEDB.12.0;Data Source=CafeTable.accdb;";
if ((File.Exists("CafeTable.accdb")))//update database
{
connect.Open();
OleDbCommand command = new OleDbCommand();
command.Connection = connect;
command.CommandText = "INSERT INTO Table1 (Meal_Number, Meal_Name, Meal_Description, Meal_Ingredients, Meal_Price) values('" + CID + "','" + CType + "','" + CDescription + "','" + CAmount + "','" + CdateIncident + "')"; command.ExecuteNonQuery();
connect.Close();
}
else //Create the database.
{
var create = new ADOX.Catalog();
create.Create(connect.ConnectionString);
connect.Open();
OleDbCommand command = new OleDbCommand();
command.Connection = connect;
command.CommandText = "CREATE TABLE Table1 (" +
"[Meal_Number] VARCHAR( 50 ) ," +
"[Meal_Name] VARCHAR( 50 ) ," +
"[Meal_Description] VARCHAR( 50 )," +
"[Meal_Ingredients] VARCHAR( 50 )," +
"[Meal_Price] VARCHAR( 50 ))";
command.ExecuteNonQuery();
command.CommandText = "INSERT INTO Table1 (Meal_Number, Meal_Name, Meal_Description, Meal_Ingredients, Meal_Price) values('" + CID + "','" + CType + "','" + CDescription + "','" + CAmount + "','" + CdateIncident + "')";
command.ExecuteNonQuery();
connect.Close();
}
return(IsDataBaseCreated);
}
public string GetCommandText(string connectionString, CommandSchema command)
{
ADOX.Catalog catalog = GetCatalog(connectionString);
ADOX.Procedure procedure = catalog.Procedures[command.Name];
ADODB.Command cmd = procedure.Command as ADODB.Command;
return(cmd != null ? cmd.CommandText : String.Empty);
}
public string GetViewText(string connectionString, ViewSchema view)
{
ADOX.Catalog catalog = GetCatalog(connectionString);
ADOX.View v = catalog.Views[view.Name];
ADODB.Command cmd = v.Command as ADODB.Command;
return(cmd != null ? cmd.CommandText : String.Empty);
}
private static ADOX.Column newColumn(string name, ADOX.DataTypeEnum type, ADOX.Catalog cat)
{
ADOX.Column temp = new ADOX.Column();
temp.Name = name;
temp.ParentCatalog = cat;
temp.Type = type;
return(temp);
}
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 void GenerateXMLFromMDB(string pFilename)
{
mfSysTableExport = false;
mfSysTablePVEntityGenerator = false;
mDBDefinition = new dbdefinition();
mDBDefinition.generatedon = DateTime.Now;
// Generate entities
ArrayList alEntities = new ArrayList();
ADODB.Connection con = new ADODB.ConnectionClass();
try {
con.Open(DBServerHelper.getDatabaseOleDbConnectionString(pFilename), "", "", 0);
}
catch (Exception ex) {
throw new Exception("Unable to open database file:\n" + ex.Message);
}
ADOX.Catalog cat = new ADOX.Catalog();
cat.ActiveConnection = con;
mStatusHandler.InitStatus("Analyzing MDB table structure...", cat.Tables.Count);
int intTable = 0;
int intTableRealCount = 0;
foreach (ADOX.Table tbl in cat.Tables)
{
intTable++;
if (!IsSystemTableName(tbl.Name))
{
intTableRealCount++;
mStatusHandler.SetStatus("Analyzing table '" + tbl.Name + "'...", intTable);
alEntities.Add(GenerateEntity(tbl, con));
}
else
{
// Check if settings from older versions of SQLExporter or PVEntityGenerator exist
if (!mfSysTableExport)
{
mfSysTableExport = tbl.Name.ToLower().Equals("_export");
}
if (!mfSysTablePVEntityGenerator)
{
mfSysTablePVEntityGenerator = tbl.Name.ToLower().Equals("_pventitygenerator");
}
}
}
con.Close();
if (alEntities.Count != 0)
{
mDBDefinition.entities = (dbdefinitionEntity[])alEntities.ToArray(typeof(dbdefinitionEntity));
}
mStatusHandler.ClearStatus("Analyzed " + intTableRealCount + " table(s).");
}
/// <summary>
/// creates a new connection to an MS Access database and automatically
/// opens the connection.
/// </summary>
/// <param name="connectionString"></param>
public OleDBDataAccessLayer(string fileName)
: base(string.Format("Provider=Microsoft.Jet.OleDb.4.0;Data Source={0}", fileName))
{
if (!System.IO.File.Exists(fileName))
{
ADOX.Catalog cat = new ADOX.Catalog();
cat.Create(string.Format("Provider=Microsoft.Jet.OLEDB.4.0;Data Source={0}; Jet OLEDB:Engine Type=5", fileName));
}
}
private void Cleanup()
{
if (_connection != null && _connection.State == (int)ADODB.ObjectStateEnum.adStateOpen)
{
_connection.Close();
}
_connection = null;
_catalog = null;
}
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);
}
// 通过ADOX创建ACCESS数据库文件
public static void CreatDataBase(string filename)
{
if (System.IO.File.Exists(filename))
{
System.IO.File.Delete(filename);
}
ADOX.Catalog cat = new ADOX.Catalog();
cat.Create("Provider=Microsoft.ACE.OLEDB.12.0;Data Source=" + filename + ";");
System.Runtime.InteropServices.Marshal.FinalReleaseComObject(cat.ActiveConnection);
System.Runtime.InteropServices.Marshal.FinalReleaseComObject(cat);
}
private static void EnsureMdbExists(string dbFileName, string connectionString)
{
if (File.Exists(dbFileName))
{
return;
}
var catalog = new ADOX.Catalog();
catalog.Create(connectionString);
}
// 通过ADOX创建ACCESS数据库文件
public static void CreatDataBase(string filename)
{
if (System.IO.File.Exists(filename))
{
System.IO.File.Delete(filename);
}
ADOX.Catalog cat = new ADOX.Catalog();
cat.Create("Provider=Microsoft.ACE.OLEDB.12.0;Data Source=" + filename + ";");
System.Runtime.InteropServices.Marshal.FinalReleaseComObject(cat.ActiveConnection);
System.Runtime.InteropServices.Marshal.FinalReleaseComObject(cat);
}
/// <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);
}
static void Main(string[] args)
{
string myConnectionString = @"Provider=Microsoft.ACE.OLEDB.12.0;Data source=C:\__tmp\myDB.accdb;";
// the following code requires a COM reference to "Microsoft ADO Ext. 2.8 for DDL and Security"
var cat = new ADOX.Catalog();
cat.Create(myConnectionString); // create a new, empty .accdb file
Console.WriteLine("Done.");
}
public static OleDbConnection CreateOrOpenUserDataBase(string username)
{
ADOX.Catalog catalog = new ADOX.Catalog();
OleDbConnectionStringBuilder builder = new OleDbConnectionStringBuilder();
builder.DataSource = appDataPath + username + ".mdb";
builder.Provider = "Microsoft.Jet.OLEDB.4.0";
myCon = new OleDbConnection();
dbSet = new DataSet();
try
{
myCon.ConnectionString = builder.ConnectionString;
myCon.Open();
}
catch (Exception)
{
catalog.Create(builder.ConnectionString);
CreateUserDatabase(catalog);
myCon.ConnectionString = builder.ConnectionString;
myCon.Open();
}
adapterMessage = new OleDbDataAdapter("select * from Message", myCon);
adapterMessage.Fill(dbSet, "Message");
adapterMessage.InsertCommand = new OleDbCommand("insert into [Message]([sender], [receiver], [content], [group_message], [type], [message_date]) Values(@sender, @receiver, @content, @group_message, @type, @message_date);", myCon);
adapterMessage.InsertCommand.Parameters.Add(new OleDbParameter("@sender", OleDbType.WChar, 50, "sender"));
adapterMessage.InsertCommand.Parameters.Add(new OleDbParameter("@receiver", OleDbType.WChar, 50, "receiver"));
OleDbParameter tempPar = new OleDbParameter("@content", OleDbType.LongVarWChar);
tempPar.SourceColumn = "content";
adapterMessage.InsertCommand.Parameters.Add(tempPar);
tempPar = new OleDbParameter("@group_Message", OleDbType.Boolean);
tempPar.SourceColumn = "group_Message";
adapterMessage.InsertCommand.Parameters.Add(tempPar);
adapterMessage.InsertCommand.Parameters.Add(new OleDbParameter("@type", OleDbType.WChar, 50, "type"));
tempPar = new OleDbParameter("@message_date", OleDbType.Date);
tempPar.SourceColumn = "message_date";
adapterMessage.InsertCommand.Parameters.Add(tempPar);
adapterMessage.AcceptChangesDuringUpdate = true;
adapterFriendRequest = new OleDbDataAdapter("select * from Friend_Request", myCon);
adapterFriendRequest.Fill(dbSet, "Friend_Request");
adapterFriendRequest.InsertCommand = new OleDbCommand("insert into Friend_Request(sender, receiver, request_date) values(@sender, @receiver, @request_date); ", myCon);
adapterFriendRequest.InsertCommand.Parameters.Add(new OleDbParameter("@sender", OleDbType.WChar, 50, "sender"));
adapterFriendRequest.InsertCommand.Parameters.Add(new OleDbParameter("@receiver", OleDbType.WChar, 50, "receiver"));
tempPar = new OleDbParameter("@request_date", OleDbType.Date);
tempPar.SourceColumn = "request_date";
adapterFriendRequest.InsertCommand.Parameters.Add(tempPar);
adapterFriendRequest.AcceptChangesDuringUpdate = true;
return(myCon);
}
static void Main(string[] args)
{
if (!((Directory.Exists(Environment.GetFolderPath(Environment.SpecialFolder.ApplicationData) + "\\Utilities"))))
{
Directory.CreateDirectory(Environment.GetFolderPath(Environment.SpecialFolder.ApplicationData) + "\\Utilities");
}
if (!(File.Exists(Environment.GetFolderPath(Environment.SpecialFolder.ApplicationData) + "\\Utilities\\Veicoli.accdb")))
{
var cat = new ADOX.Catalog();
cat.Create(connStr);
}
char scelta;
do
{
menu();
Console.Write("Scegli un'operazione da eseguire: ");
scelta = Console.ReadKey().KeyChar;
switch (scelta)
{
case '1':
{
creaTabella();
break;
}
case '2':
{
visualizzaListaVeicoli();
break;
}
case '3':
{
Utils.esportaInWord();
break;
}
case '4':
{
cancellaRecords();
break;
}
case '5':
{
cancellaTabellaVeicoli();
break;
}
}
}while (scelta != 'X' && scelta != 'x');
}
private void ConvertSingleFile(string xlsFile, string mdbFile)
{
if (!File.Exists(xlsFile))
{
"Source file not existed".Notify();
return;
}
string connStr =
String.Format("Provider=Microsoft.Jet.OLEDB.4.0;Data Source={0};Extended Properties=Excel 8.0;", xlsFile);
if (!File.Exists(mdbFile))
{
string createMDBFileString = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" + mdbFile;
ADOX.Catalog category = new ADOX.Catalog();
category.Create(createMDBFileString);
/*here is the old way to create by offfice com */
//Microsoft.Office.Interop.Access.ApplicationClass a = new Microsoft.Office.Interop.Access.ApplicationClass();
//a.NewCurrentDatabase(mdbFile);
}
CoreEA.ICoreEAHander excelEg = new CoreEA.CoreE(CoreEA.CoreE.UsedDatabaseType.OleDb).X_Handler;
excelEg.Open(new CoreEA.LoginInfo.LoginInfo_Oledb()
{
Database = xlsFile,
});
if (!excelEg.IsOpened)
{
"Can't open excel file.the action will cancel".Notify();
return;
}
OleDbConnection conn = new OleDbConnection(connStr);
OleDbCommand cmd = new OleDbCommand();
cmd.Connection = conn;
conn.Open();
string cmdText = string.Empty;
foreach (string item in excelEg.GetTableListInDatabase())
{
cmdText =
String.Format("SELECT * INTO [MS Access;Database={0}].[{1}] FROM [{2}]", mdbFile, item, item);
cmd.CommandText = cmdText;
cmd.ExecuteNonQuery();
}
conn.Close();
}
private bool CreateNewFile(string connectionString)
{
if (File.Exists(this.FileName))
{
return(false);
}
// create access database file with the ADO-ActiveX
ADOX.Catalog catalog = new ADOX.Catalog();
catalog.Create(connectionString);
catalog.ActiveConnection.Close();
return(true);
}
private void button2_Click(object sender, EventArgs e)
{
if (!File.Exists("Config"))
{
MessageBox.Show("設定檔不存在!!請載入設定用EXCEL");
}
if (ChapterNunber == 0)
{
MessageBox.Show("未選擇章節");
}
else if (Catagory == 0)
{
MessageBox.Show("未選擇測試種類");
}
else if (ChapterNunber == 0 && Catagory == 0)
{
MessageBox.Show("兩者皆未選擇");
}
else if (Catagory == 1)
{
static_test(ChapterNunber);
}
else if (Catagory == 2)
{
dynamic_test(ChapterNunber);
}
else if (Catagory == 3)
{
if (!File.Exists("Config"))
{
MessageBox.Show("設定檔不存在!!");
return;
}
if (!File.Exists("board.accdb"))
{
ADOX.Catalog cat = new ADOX.Catalog();
cat.Create("Provider=Microsoft.ACE.OLEDB.12.0;Data Source=board.accdb;");
cat.ActiveConnection.Close();
}
else
{
File.Delete("board.accdb");
ADOX.Catalog cat = new ADOX.Catalog();
cat.Create("Provider=Microsoft.ACE.OLEDB.12.0;Data Source=board.accdb;");
cat.ActiveConnection.Close();
}
DataBaseProcess baseProcess = new DataBaseProcess(DeserializeBinary(), path);
MessageBox.Show("Done");
}
}
private static void CreateAccessDatabase(string connectionString)
{
var db = new ADOX.Catalog();
db.Create(connectionString);
db.Tables.Append(_logFileProcessor.GetTable());
// get active connection if any
var connection = db.ActiveConnection as ADODB.Connection;
// close connection to database if open
if (connection != null) connection.Close();
// release memory
db = null;
}
private static void AddTable(OleDbConnectionStringBuilder csb)
{
// check if table exists
var con = new ADODB.Connection();
con.Open(csb.ToString());
var db = new ADOX.Catalog();
db.ActiveConnection = con;
try
{
var table = db.Tables[_logFileProcessor.TableName];
}
catch (COMException)
{
db.Tables.Append(_logFileProcessor.GetTable());
}
finally
{
con.Close();
}
}
private void getLinkedDatabase()
{
try
{
//try 2
System.Data.OleDb.OleDbConnection conn = new OleDbConnection();
ADODB.Connection conJCMS = new ADODB.Connection();
string strConx = "Provider=Microsoft.Jet.OLEDB.4.0;Persist Security Info=True;Data Source=" + updateDir.Text + ";";
conJCMS.Mode = ADODB.ConnectModeEnum.adModeReadWrite;
conJCMS.Open(strConx, "", "", 0);
ADODB.Recordset tables = new ADODB.Recordset();
tables.Open("SELECT * FROM Mouse;", conJCMS, ADODB.CursorTypeEnum.adOpenStatic, ADODB.LockTypeEnum.adLockBatchOptimistic, 0);
ADOX.Catalog catalog = new ADOX.Catalog();
catalog.ActiveConnection = conJCMS;
foreach (ADOX.Table tbl in catalog.Tables)
{
if (tbl.Type == "LINK")
{
string strSourceDbFullPath = tbl.Properties["Jet OLEDB:Link DataSource"].Value.ToString();
string strSourceDbPath = strSourceDbFullPath.Substring(0, strSourceDbFullPath.LastIndexOf("\\") + 1);
string strSourceDbName = strSourceDbFullPath.Substring(strSourceDbFullPath.LastIndexOf("\\") + 1);
break;
}
}
//try one
if (Environment.Is64BitOperatingSystem)
{
conn.ConnectionString = @"Provider=Microsoft.ACE.OLEDB.12.0;" + @"Data Source=" + updateDir.Text +";";
}
else
{
conn.ConnectionString = @"Provider=Microsoft.Jet.OLEDB.4.0;" + @"Data Source=" + updateDir.Text + ";";
}
conn.Open();
ADOX.Catalog theCatalog = new ADOX.Catalog();
theCatalog.ActiveConnection = conn;
string databaseQuery = "SELECT * FROM Mouse;";
OleDbCommand theCommand = new OleDbCommand(databaseQuery);
theCommand.Connection = conn;
DataTable schemaTable = conn.GetOleDbSchemaTable(OleDbSchemaGuid.Tables, new object[] { null, null, null, "TABLE" });
foreach (DataRow row in schemaTable.Rows)
{
foreach(var item in row.ItemArray){
Console.WriteLine(item);
}
}
OleDbDataReader theReader = theCommand.ExecuteReader();
while (theReader.Read())
{
Console.WriteLine(theReader.GetString(1));
}
conn.Close();
}
catch (Exception e) { Console.WriteLine(e); }
}
override internal void LoadAll()
{
DataTable metaData = CreateDataTable();
ADODB.Connection cnn = new ADODB.Connection();
ADOX.Catalog cat = new ADOX.Catalog();
// Open the Connection
cnn.Open(dbRoot.ConnectionString, null, null, 0);
cat.ActiveConnection = cnn;
ADOX.Procedure proc = cat.Procedures[this.Procedure.Name];
ADODB.Command cmd = proc.Command as ADODB.Command;
// Retrieve Parameter information
cmd.Parameters.Refresh();
if(cmd.Parameters.Count > 0)
{
int ordinal = 0;
foreach(ADODB.Parameter param in cmd.Parameters)
{
DataRow row = metaData.NewRow();
string hyperlink = "False";
try
{
hyperlink = param.Properties["Jet OLEDB:Hyperlink"].Value.ToString();
}
catch {}
row["TYPE_NAME"] = hyperlink == "False" ? param.Type.ToString() : "Hyperlink";
row["PROCEDURE_CATALOG"] = this.Procedure.Database;
row["PROCEDURE_SCHEMA"] = null;
row["PROCEDURE_NAME"] = this.Procedure.Name;
row["PARAMETER_NAME"] = param.Name;
row["ORDINAL_POSITION"] = ordinal++;
row["PARAMETER_TYPE"] = param.Type;//.ToString();
row["PARAMETER_HASDEFAULT"] = false;
row["PARAMETER_DEFAULT"] = null;
row["IS_NULLABLE"] = false;
row["DATA_TYPE"] = param.Type;//.ToString();
row["CHARACTER_MAXIMUM_LENGTH"] = 0;
row["CHARACTER_OCTET_LENGTH"] = 0;
row["NUMERIC_PRECISION"] = param.Precision;
row["NUMERIC_SCALE"] = param.NumericScale;
row["DESCRIPTION"] = "";
// row["TYPE_NAME"] = "";
row["LOCAL_TYPE_NAME"] = "";
metaData.Rows.Add(row);
}
}
cnn.Close();
base.PopulateArray(metaData);
}
public void ExportQueryToDB(DataSet ds)
{
try
{
//The connection strings needed: One for SQL and one for Access
String accessConnectionString = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" + Application.StartupPath + "\\Report\\Report.mdb"; //"Provider=Microsoft.ACE.OLEDB.12.0;Data Source=C:\\...\\test.accdb;";
//String sqlConnectionString = "Data Source=LUKMAN\SQLEXPRESSR22008;Initial Catalog=Your_Catalog;Integrated Security=True";
//Make adapters for each table we want to export
//SqlDataAdapter adapter1 = new SqlDataAdapter("select * from Table1", sqlConnectionString);
//SqlDataAdapter adapter2 = new SqlDataAdapter("select * from Table2", sqlConnectionString);
////Fills the data set with data from the SQL database
//DataSet dataSet = new DataSet();
//adapter1.Fill(dataSet, "Table1");
//adapter2.Fill(dataSet, "Table2");
if (File.Exists(Application.StartupPath + "\\Report\\Report.mdb"))
{
//Create an empty Access file that we will fill with data from the data set
File.Delete(Application.StartupPath + "\\Report\\Report.mdb");
}
ADOX.Catalog catalog = new ADOX.Catalog();
catalog.Create(accessConnectionString);
//Create an Access connection and a command that we'll use
OleDbConnection accessConnection = new OleDbConnection(accessConnectionString);
OleDbCommand command = new OleDbCommand();
command.Connection = accessConnection;
command.CommandType = CommandType.Text;
accessConnection.Open();
//This loop creates the structure of the database
foreach (DataTable table in ds.Tables)
{
String columnsCommandText = "(";
foreach (DataColumn column in table.Columns)
{
String columnName = column.ColumnName;
String dataTypeName = column.DataType.Name == "Boolean" || column.DataType.Name == "Int32" || column.DataType.Name == "Int64" || column.DataType.Name == "Integer" ? "Number" : column.DataType.Name;
String sqlDataTypeName = dataTypeName;//getSqlDataTypeName(dataTypeName);
columnsCommandText += "[" + columnName + "] " + sqlDataTypeName + ",";
//columnsCommandText += "[" + columnName + "] " + ",";
}
columnsCommandText = columnsCommandText.Remove(columnsCommandText.Length - 1);
columnsCommandText += ")";
command.CommandText = "CREATE TABLE " + table.TableName + columnsCommandText;
command.ExecuteNonQuery();
}
//This loop fills the database with all information
foreach (DataTable table in ds.Tables)
{
foreach (DataRow row in table.Rows)
{
String commandText = "INSERT INTO " + table.TableName + " VALUES (";
foreach (var item in row.ItemArray)
{
commandText += "'" + item.ToString() + "',";
}
commandText = commandText.Remove(commandText.Length - 1);
commandText += ")";
command.CommandText = commandText;
command.ExecuteNonQuery();
}
}
accessConnection.Close();
}
catch (Exception ex)
{
throw new Exception(ex.Message.ToString());
}
}
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 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;
}
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");
}
}
/// <summary>
/// Opens or creates an MS Access database file for writing
/// </summary>
public bool SetWriteFile(string filename)
{
_filename = filename;
string connString = String.Format("Provider={0}; Data Source={1};", "Microsoft.Jet.OLEDB.4.0", _filename);
if (!System.IO.File.Exists(filename))
{
ADOX.Catalog arghCatalog = new ADOX.Catalog();
arghCatalog.Create(connString);
}
//if the file doesn't exist, do I need a different connection string?
_conn = new System.Data.OleDb.OleDbConnection(connString);
_conn.Open();
if ((_conn == null) || (_conn.State != System.Data.ConnectionState.Open))
return false;
return true;
}
//private void Delete()
//{
// Process proc = new Process();
// proc.StartInfo.CreateNoWindow = true;
// proc.StartInfo.FileName = "cmd.exe";
// proc.StartInfo.UseShellExecute = false;
// proc.StartInfo.RedirectStandardError = true;
// proc.StartInfo.RedirectStandardInput = true;
// proc.StartInfo.RedirectStandardOutput = true;
// proc.Start();
// proc.StandardInput.WriteLine("del " + m_strFilePathName.Substring(0, m_strFilePathName.Length - 3) + "ldb");
// proc.StandardInput.WriteLine("del " + m_strFilePathName);
// //proc.StandardInput.WriteLine("delete " + m_strPath + ".tmp.mdb");
// proc.Close();
//}
private bool Connect(string strFilePathName)
{
try
{
m_strFilePathName = strFilePathName;
if (File.Exists(strFilePathName))
{
try
{
File.Delete(strFilePathName);
}
catch (Exception ee)
{
//Delete();
strFilePathName += "_1.mdb";
}
}
try
{
ADOX.Catalog catalog = new ADOX.Catalog();
catalog.Create("Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" + strFilePathName + ";Jet OLEDB:Engine Type=5");
System.Runtime.InteropServices.Marshal.FinalReleaseComObject(catalog.ActiveConnection);
System.Runtime.InteropServices.Marshal.FinalReleaseComObject(catalog);
}
catch (Exception ee)
{
}
DbConnectionStringBuilder dcsBuilder = new DbConnectionStringBuilder();
dcsBuilder.Clear();
dcsBuilder.Add("Provider", "Microsoft.Jet.Oledb.4.0");
dcsBuilder.Add("User ID", "Admin");
dcsBuilder.Add("Password", "");
dcsBuilder.Add("Data Source", @strFilePathName);
m_pOleDbConnection = new OleDbConnection(dcsBuilder.ConnectionString);
m_pOleDbConnection.Open();
//m_pPolygonNodeTable = new PolygonNodeTable(m_pOleDbConnection, true);
//m_pLineNodeExTable = new LineNodeExTable(m_pOleDbConnection, true);
//m_pLineNodeTable = new LineNodeTable(m_pOleDbConnection, true);
return true;
}
catch (Exception ex)
{
Logger.WriteErrorLog(ex);
}
return false;
}
public static void AssemblyInit()
{
File.Delete(databaseFileName);
var connectionString = ConfigurationManager.ConnectionStrings["AccessTestDatabase"].ConnectionString;
var cat = new ADOX.Catalog();
cat.Create(connectionString);
var dbConnection = new OleDbConnection(connectionString);
using (dbConnection)
{
dbConnection.Open();
string sql = @"
CREATE TABLE Employee
(
EmployeeKey COUNTER PRIMARY KEY,
FirstName TEXT(30) NOT NULL,
MiddleName TEXT(30) NULL,
LastName TEXT(30) NOT NULL,
Title TEXT(100) null,
ManagerKey LONG NULL REFERENCES Employee(EmployeeKey),
CreatedDate DateTime NOT NULL DEFAULT NOW(),
UpdatedDate DateTime NULL
)";
string sql2 = @"CREATE TABLE Customer
(
CustomerKey COUNTER PRIMARY KEY,
FullName TEXT(100) NULL,
State TEXT(2) NOT NULL,
CreatedByKey INTEGER NULL,
UpdatedByKey INTEGER NULL,
CreatedDate DATETIME NULL DEFAULT NOW(),
UpdatedDate DATETIME NULL,
DeletedFlag BIT NOT NULL DEFAULT 0,
DeletedDate DateTime NULL,
DeletedByKey INTEGER NULL
)";
string sql3 = @"CREATE VIEW EmployeeLookup AS SELECT FirstName, LastName, EmployeeKey FROM Employee";
using (var command = new OleDbCommand(sql, dbConnection))
command.ExecuteNonQuery();
using (var command = new OleDbCommand(sql2, dbConnection))
command.ExecuteNonQuery();
using (var command = new OleDbCommand(sql3, dbConnection))
command.ExecuteNonQuery();
sql = @"INSERT INTO Employee ([FirstName], [MiddleName], [LastName], [Title], [ManagerKey]) VALUES (@FirstName, @MiddleName, @LastName, @Title, @ManagerKey)";
sql2 = @"INSERT INTO Employee ([FirstName], [MiddleName], [LastName], [Title], [ManagerKey], [CreatedDate]) VALUES (@FirstName, @MiddleName, @LastName, @Title, @ManagerKey, @CreatedDate)";
//Date/Time format - 4/30/2016 5:25:17 PM
const string DateTimeFormat = "M/d/yyyy h:mm:ss tt";
using (var command = new OleDbCommand(sql, dbConnection))
{
//command.Parameters.AddWithValue("@EmployeeKey", DBNull.Value);
command.Parameters.AddWithValue("@FirstName", "Tom");
command.Parameters.AddWithValue("@MiddleName", DBNull.Value);
command.Parameters.AddWithValue("@LastName", "Jones");
command.Parameters.AddWithValue("@Title", "CEO");
command.Parameters.AddWithValue("@ManagerKey", DBNull.Value);
command.ExecuteNonQuery();
}
using (var command = new OleDbCommand(sql2, dbConnection))
{
//command.Parameters.AddWithValue("@EmployeeKey", DBNull.Value);
command.Parameters.AddWithValue("@FirstName", "Tom");
command.Parameters.AddWithValue("@MiddleName", DBNull.Value);
command.Parameters.AddWithValue("@LastName", "Jones");
command.Parameters.AddWithValue("@Title", "CEO");
command.Parameters.AddWithValue("@ManagerKey", DBNull.Value);
command.Parameters.AddWithValue("@CreatedDate", DateTime.Now.ToString(DateTimeFormat));
command.ExecuteNonQuery();
}
using (var command = new OleDbCommand(sql2, dbConnection))
{
//command.Parameters.AddWithValue("@EmployeeKey", DBNull.Value);
command.Parameters.AddWithValue("@FirstName", "Tom");
command.Parameters.AddWithValue("@MiddleName", DBNull.Value);
command.Parameters.AddWithValue("@LastName", "Jones");
command.Parameters.AddWithValue("@Title", "CEO");
command.Parameters.AddWithValue("@ManagerKey", DBNull.Value);
var param = command.Parameters.AddWithValue("@CreatedDate", DateTime.Now);
param.OleDbType = OleDbType.Date;
command.ExecuteNonQuery();
}
using (var command = new OleDbCommand("SELECT @@IDENTITY", dbConnection))
{
var key = command.ExecuteScalar();
using (var command2 = new OleDbCommand("UPDATE Employee SET MiddleName = 'Frank' WHERE EmployeeKey = @EmployeeKey", dbConnection))
{
command2.Parameters.AddWithValue("@EmployeeKey", key);
var updateCount = command2.ExecuteNonQuery();
}
}
}
}
internal static Boolean CreateAccessDatabase(String DatabaseFullPath)
{
Boolean bAns = false;
ADOX.Catalog cat = new ADOX.Catalog();
try
{
string theConnection = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" + DatabaseFullPath;
cat.Create(theConnection);
bAns = true;
}
catch (System.Runtime.InteropServices.COMException ex)
{
MessageBox.Show(ex.Message);
bAns = false;
}
cat = null;
return bAns;
}
private void LoadExtraDataForView()
{
try
{
if(this._array.Count > 0)
{
ADODB.Connection cnn = new ADODB.Connection();
ADODB.Recordset rs = new ADODB.Recordset();
ADOX.Catalog cat = new ADOX.Catalog();
// Open the Connection
cnn.Open(dbRoot.ConnectionString, null, null, 0);
cat.ActiveConnection = cnn;
rs.Source = cat.Views[this.View.Name].Command;
rs.Fields.Refresh();
ADODB.Fields flds = rs.Fields;
Column col = this._array[0] as Column;
f_TypeName = new DataColumn("TYPE_NAME", typeof(string));
col._row.Table.Columns.Add(f_TypeName);
f_AutoKey = new DataColumn("AUTO_INCREMENT", typeof(Boolean));
col._row.Table.Columns.Add(f_AutoKey);
Column c = null;
ADODB.Field fld = null;
int count = this._array.Count;
for( int index = 0; index < count; index++)
{
fld = flds[index];
c = (Column)this[fld.Name];
c._row["TYPE_NAME"] = fld.Type.ToString();
c._row["AUTO_INCREMENT"] = false;
}
rs.Close();
cnn.Close();
}
}
catch {}
}
/// <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 LoadExtraDataForTable()
{
try
{
if(this._array.Count > 0)
{
ADODB.Connection cnn = new ADODB.Connection();
ADOX.Catalog cat = new ADOX.Catalog();
// Open the Connection
cnn.Open(dbRoot.ConnectionString, null, null, 0);
cat.ActiveConnection = cnn;
ADOX.Columns cols = null;
cols = cat.Tables[this.Table.Name].Columns;
Column col = this._array[0] as Column;
f_TypeName = new DataColumn("TYPE_NAME", typeof(string));
col._row.Table.Columns.Add(f_TypeName);
f_AutoKey = new DataColumn("AUTO_INCREMENT", typeof(Boolean));
col._row.Table.Columns.Add(f_AutoKey);
f_AutoKeySeed = new DataColumn("AUTO_KEY_SEED", typeof(System.Int32));
col._row.Table.Columns.Add(f_AutoKeySeed);
f_AutoKeyIncrement = new DataColumn("AUTO_KEY_INCREMENT", typeof(System.Int32));
col._row.Table.Columns.Add(f_AutoKeyIncrement);
int count = this._array.Count;
Column c = null;
ADOX.Column cx = null;
for( int index = 0; index < count; index++)
{
cx = cols[index];
c = (Column)this[cx.Name];
string hyperlink = "False";
try
{
hyperlink = cx.Properties["Jet OLEDB:Hyperlink"].Value.ToString();
}
catch {}
string name = cx.Name;
Console.WriteLine("-----------------------------------------");
foreach(ADOX.Property prop in cx.Properties)
{
Console.WriteLine(prop.Attributes.ToString());
Console.WriteLine(prop.Name);
if(null != prop.Value)
Console.WriteLine(prop.Value.ToString());
}
c._row["TYPE_NAME"] = hyperlink == "False" ? cx.Type.ToString() : "Hyperlink";
try
{
if(c.Default == "GenGUID()")
{
c._row["AUTO_INCREMENT"] = Convert.ToBoolean(cx.Properties["Jet OLEDB:AutoGenerate"].Value);
}
else
{
c._row["AUTO_INCREMENT"] = Convert.ToBoolean(cx.Properties["Autoincrement"].Value);
c._row["AUTO_KEY_SEED"] = Convert.ToInt32(cx.Properties["Seed"].Value);
c._row["AUTO_KEY_INCREMENT"] = Convert.ToInt32(cx.Properties["Increment"].Value);
}
}
catch {}
}
cnn.Close();
}
}
catch {}
}
private void ConvertDB()
{
//set up the thread for copying data so that it can run in the background
bwkr = new BackgroundWorker();
bwkr.WorkerReportsProgress = true;
bwkr.WorkerSupportsCancellation = true;
bwkr.DoWork += new DoWorkEventHandler(bwkr_DoWork);
bwkr.RunWorkerCompleted += new RunWorkerCompletedEventHandler(bwkr_RunWorkerCompleted);
bwkr.ProgressChanged += new ProgressChangedEventHandler(bwkr_ProgressChanged);
//connect to the databases
StepStarted("Connecting to Access source");
if (ConnectToMSACC())
StepComplete("Connected to Access source");
else
blnSuccess = false;
if (blnSuccess == true)
{
StepStarted("Connecting to MySQL");
if (ConnectToMySQL())
StepComplete("Connected to MySQL");
else
blnSuccess = false;
}
// copy database schema
if (blnSuccess == true)
{
StepStarted( "Creating database schema in MySQL");
if (CopySchema())
{
StepComplete ("MySQL schema created");
pgbConversion.PerformStep();
}
else
blnSuccess = false;
}
//copy the data: this is handled differently because it runs in the background
if (blnSuccess == true)
{
StepStarted("Copying data to MySQL");
bwkr.RunWorkerAsync();
//wait for the data copy to complete
while (bwkr.IsBusy)
Application.DoEvents();
//a cancel or error during the copy process will set success flag to false
if (blnSuccess == true)
StepComplete("Data copied to MySQL database.");
}
//Create foreign keys
if (blnSuccess == true)
{
StepStarted( "Creating table relationships");
if (CreateRelationships() == true)
StepComplete ("Relationships created");
else
blnSuccess = false;
}
//Re-link tables
if (blnSuccess == true)
{
StepStarted( "Re-linking tables");
if (RelinkTables() == true)
StepComplete("Tables re-linked");
}
//Close the database connections, a wait here for garbage collection to happen,
//otherwise the connections are held open and the db cannot be renamed
if (blnSuccess == true)
{
StepStarted("Closing connections");
try
{
conMySQL.Close();
conJCMS_db.Close();
conJCMS.Close();
conJCMS = null;
conJCMS_db = null;
conMySQL = null;
catAccess = null;
catJCMS = null;
System.GC.Collect();
System.GC.WaitForPendingFinalizers();
// Worker loop ensure connections are closed
for (int i = 0; i < 10000; i++)
{
Console.WriteLine("Disabling the source database");
}
}
catch
{
};
}
//Disable database
if ((blnSuccess == true) && (blnDisable == true))
{
StepStarted("Disabling database");
if (DisableDatabase() == true)
{
StepComplete ("Database disabled");
pgbConversion.PerformStep();
}
else
//still considered a successful conversion
WriteOutput("Could not disable database. This step must be performed manually by renaming the source database.");
}
//final steps
if (blnSuccess == true)
{
lblConversion.Text = "Database conversion successful";
WriteOutput("Database conversion successful.");
}
else
{
if (blnCancel == true)
{
lblConversion.Text = "Database conversion cancelled.";
WriteOutput("Database conversion cancelled.");
}
else
{
//drop the schema
string strSQL = null;
//drop the MySQL database
strSQL = "DROP SCHEMA " + strMySQLDBName;
blnCancel = true;
blnSuccess = false;
try
{
ExecuteSQL(strSQL);
}
catch
{ }
lblConversion.Text = "Database conversion failed.";
WriteOutput("Database conversion failed.");
}
}
try
{
swLog.Close();
pgbConversion.Value = 15;
btnCancel.Enabled = false;
btnExit.Enabled = true;
}
catch { }
}
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 button1_Click(object sender, EventArgs e)
{
if (comboBox1.Text.ToString() == "")
{
MessageBox.Show("Выбери год", "Внимание!", MessageBoxButtons.OK, MessageBoxIcon.Error);
}
else if (comboBox2.Text.ToString() == "")
{
MessageBox.Show("Выбери месяц", "Внимание!", MessageBoxButtons.OK, MessageBoxIcon.Error);
}
else if (textBox1.Text == "")
{
MessageBox.Show("Выбери файл", "Внимание!", MessageBoxButtons.OK, MessageBoxIcon.Error);
}
else
{
string strConn;
//Check for Excel version
if (textBox1.Text.Substring(textBox1.Text.LastIndexOf('.')).ToLower() == ".xlsx")
{
strConn = "Provider=Microsoft.ACE.OLEDB.12.0;Data Source=" + textBox1.Text + ";Extended Properties=\"Excel 12.0;HDR=YES; IMEX=0\"";
}
else
{
strConn = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" + textBox1.Text + ";Extended Properties=\"Excel 8.0;HDR=YES; IMEX=0\"";
}
System.Data.OleDb.OleDbConnection con = new System.Data.OleDb.OleDbConnection(strConn);
con.Open();
DataSet ds = new DataSet();
DataTable shemaTable = con.GetOleDbSchemaTable(System.Data.OleDb.OleDbSchemaGuid.Tables,
new object[] { null, null, null, "TABLE" });
string sheet1 = (string)shemaTable.Rows[0].ItemArray[2];
string select = String.Format("SELECT * FROM [{0}]", sheet1);
System.Data.OleDb.OleDbDataAdapter ad = new System.Data.OleDb.OleDbDataAdapter(select, con);
ad.Fill(ds);
if (System.IO.File.Exists("your_base.mdb"))
{
int year = Convert.ToInt32(comboBox1.Text.ToString());
int month = Convert.ToInt32(comboBox2.Text.ToString());
string conStr = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=your_base.mdb;Jet OLEDB:Engine Type=5";
System.Data.OleDb.OleDbConnection connectDb = new System.Data.OleDb.OleDbConnection(conStr);
connectDb.Open();
System.Data.OleDb.OleDbCommand myCMD = new System.Data.OleDb.OleDbCommand();
myCMD.Connection = connectDb;
for (int i = 0; i < ds.Tables[0].Rows.Count; i++)
{
myCMD.CommandText = "Insert into SURVEY (Anim, weight, height, s_year, s_month) VALUES (\"" +
ds.Tables[0].Rows[i][0] + "\", " + ds.Tables[0].Rows[i][1] + ", " + ds.Tables[0].Rows[i][2] + ", " + year + ", " + month + ")";
myCMD.ExecuteNonQuery();
}
MessageBox.Show("Данные загружены в БД", "", MessageBoxButtons.OK, MessageBoxIcon.Information);
}
else
{
int year = Convert.ToInt32(comboBox1.Text.ToString());
int month = Convert.ToInt32(comboBox2.Text.ToString());
ADOX.Catalog cat = new ADOX.Catalog();
string connstr = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source={0};Jet OLEDB:Engine Type=5";
cat.Create(String.Format(connstr, "your_base.mdb"));
cat = null;
string conStr = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=your_base.mdb;Jet OLEDB:Engine Type=5";
Querry("CREATE TABLE SURVEY(Anim varchar(255), weight int, height int, s_year int, s_month int);", "your_base.mdb");
System.Data.OleDb.OleDbConnection connectDb = new System.Data.OleDb.OleDbConnection(conStr);
connectDb.Open();
System.Data.OleDb.OleDbCommand myCMD = new System.Data.OleDb.OleDbCommand();
myCMD.Connection = connectDb;
for (int i = 0; i < ds.Tables[0].Rows.Count; i++)
{
myCMD.CommandText = "Insert into SURVEY (Anim, weight, height, s_year, s_month) VALUES (\"" +
ds.Tables[0].Rows[i][0] + "\", " + ds.Tables[0].Rows[i][1] + ", " + ds.Tables[0].Rows[i][2] + ", " + year + ", " + month + ")";
myCMD.ExecuteNonQuery();
}
//string comm = "Insert into SURVEY (Anim, weight, height) VALUES (hare, 10, 20)";
//System.Data.OleDb.OleDbDataAdapter dbAdp = new System.Data.OleDb.OleDbDataAdapter(comm, conStr);
//dbAdp.Update(ds.Tables[0]);
MessageBox.Show("Данные загружены в БД", "", MessageBoxButtons.OK, MessageBoxIcon.Information);
}
con.Close();
}
}
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;
}
