public void buildAccessFile()
{
if (File.Exists(accessFilePath))
File.Delete(accessFilePath);
/* NOTE */
//COM MS ADO EXT and disable embeded type
/* NOTE */
CatalogClass catalog = new CatalogClass();
catalog.Create("Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" + accessFilePath);
//Build tables
library.oledbConn accessFile = new library.oledbConn(this.accessFilePath, "mdb");
accessFile.execute("create table Sites "
+ " ( "
+ " [siteId] integer,"
+ " [siteName] TEXT"
+ " ) "
);
accessFile.execute("create table Data"
+ " ( "
+ " [dataId] AUTOINCREMENT(1, 1) PRIMARY KEY,"
+ " [siteId] integer,"
+ " [_weekNum] integer,"
+ " [_year] integer,"
+ " [_calValue] TEXT"
+ " ) "
);
accessFile.terminate();
}
public void CreateDatabase([JetBrains.Annotations.NotNull] string databaseName, bool deleteIfExists = false)
{
if (databaseName == null) throw new ArgumentNullException("databaseName");
databaseName = databaseName.Trim();
if (!databaseName.ToLower().EndsWith(".mdb"))
databaseName += ".mdb";
if (File.Exists(databaseName))
{
if (!deleteIfExists)
return;
File.Delete(databaseName);
}
var connectionString = string.Format(
@"Provider=Microsoft.Jet.OLEDB.4.0;Data Source={0};Locale Identifier=1033;Jet OLEDB:Engine Type=5",
databaseName);
CreateFileDatabase(
databaseName, deleteIfExists, ".mdb",
dbName =>
{
dynamic catalog = new CatalogClass();
var conn = catalog.Create(connectionString);
if (conn != null)
conn.Close();
});
}
/// <summary>
/// 创建Access库
/// </summary>
/// <param name="connectionString"></param>
/// <param name="accessFileFullpath"></param>
/// <param name="dts"></param>
public static void CreateDataBase(string connectionString, string accessFileFullpath, DataFieldTypeCollection dts)
{
CatalogClass cat = null;
try
{
cat = new CatalogClass();
cat.Create(connectionString);
}
catch (Exception ex)
{
throw new Exception($"创建Access数据库失败,路径[{accessFileFullpath}],异常信息:[{ex.Message}]");
}
#region 新建表
try
{
TableClass tbl = new TableClass();
tbl.ParentCatalog = cat;
tbl.Name = "mytable";
foreach (DataFieldType dt in dts)
{
//增加一个文本字段
ColumnClass col2 = new ColumnClass();
col2.ParentCatalog = cat;
col2.Name = dt.FiledName;
col2.Properties["Jet OLEDB:Allow Zero Length"].Value = true;
switch (dt.Type.ToLower())
{
case "string":
col2.Type = ADOX.DataTypeEnum.adLongVarWChar;
tbl.Columns.Append(col2, ADOX.DataTypeEnum.adLongVarWChar, 16);
break;
case "datetime":
tbl.Columns.Append(col2, ADOX.DataTypeEnum.adDate, dt.Length);
break;
case "int":
tbl.Columns.Append(col2, ADOX.DataTypeEnum.adInteger, dt.Length);
break;
}
}
//把表加入数据库(非常重要)
cat.Tables.Append(tbl);
//转换为ADO连接,并关闭
(cat.ActiveConnection as ADODB.Connection).Close();
cat.ActiveConnection = null;
cat = null;
}
catch (Exception ex)
{
throw new Exception($"创建Access数据库表失败,路径[{accessFileFullpath}],异常信息:[{ex.Message}]");
}
#endregion
}
/// <summary>
///
/// </summary>
/// <returns></returns>
CatalogClass openDatabase()
{
CatalogClass catalog = new CatalogClass();
con = new Connection();
try {
con.Open(this.conString);
catalog.ActiveConnection = con;
} catch { catalog.Create(this.conString); }
return(catalog);
}
/// <summary>
///
/// </summary>
/// <param name="name"></param>
protected override void CreateCatalog(string name)
{
// Force the run-time to let go of the file! Otherwise,
// cleanup and other operations might fail because the file
// will still be in use.
Catalog catalog = new CatalogClass();
catalog.Create(GetConnectionString(name));
catalog.ActiveConnection = null;
catalog = null;
GC.Collect();
}
public static bool CreateMDBDataBase(string mdbPath)
{
bool result;
try
{
CatalogClass catalogClass = new CatalogClass();
catalogClass.Create("Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" + mdbPath + ";");
result = true;
}
catch
{
result = false;
}
return(result);
}
/// <summary>
/// Girilen dosyayoluna mdb uzantılı access doyası oluşturan metot.
/// </summary>
/// <param name="MdbFilePath">Mdb dosyasının tamyolu ve dosyanın uzantılı ismi</param>
/// <returns>
/// 1: Dosya oluşturuldu.
/// 0: Dosya zaten mevcut olduğundan oluşturulmadı.
/// </returns>
public static Int32 createMDBFile(string MdbFilePath)
{
try
{
Int32 retInt = 0;
CatalogClass Cat = new CatalogClass();
Cat.Create(String.Format(@"Provider=Microsoft.Jet.OLEDB.4.0;
Data Source={0};
Jet OLEDB:Engine Type=5", MdbFilePath));
retInt = 1;
Cat = null;
return(retInt);
}
catch (Exception exc)
{
throw exc;
}
}
public void CreateDatabase([JetBrains.Annotations.NotNull] string databaseName, bool deleteIfExists = false)
{
if (databaseName == null)
{
throw new ArgumentNullException(nameof(databaseName));
}
databaseName = databaseName.Trim();
if (!databaseName.ToLower().EndsWith(".mdb"))
{
databaseName += ".mdb";
}
if (File.Exists(databaseName))
{
if (!deleteIfExists)
{
return;
}
File.Delete(databaseName);
}
var connectionString = string.Format(
@"Provider=Microsoft.Jet.OLEDB.4.0;Data Source={0};Locale Identifier=1033;Jet OLEDB:Engine Type=5",
databaseName);
CreateFileDatabase(
databaseName, deleteIfExists, ".mdb",
dbName =>
{
dynamic catalog = new CatalogClass();
var conn = catalog.Create(connectionString);
if (conn != null)
{
conn.Close();
}
});
}
public void buildAccessFile()
{
if (File.Exists(accessFilePath))
{
File.Delete(accessFilePath);
}
/* NOTE */
//COM MS ADO EXT and disable embeded type
/* NOTE */
CatalogClass catalog = new CatalogClass();
catalog.Create("Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" + accessFilePath);
//Build tables
library.oledbConn accessFile = new library.oledbConn(this.accessFilePath, "mdb");
accessFile.execute("create table Sites "
+ " ( "
+ " [siteId] integer,"
+ " [siteName] TEXT"
+ " ) "
);
accessFile.execute("create table Data"
+ " ( "
+ " [dataId] AUTOINCREMENT(1, 1) PRIMARY KEY,"
+ " [siteId] integer,"
+ " [_weekNum] integer,"
+ " [_year] integer,"
+ " [_calValue] TEXT"
+ " ) "
);
accessFile.terminate();
}
public static void WriteDB(List <xl2cad_cad.midTable> tables)
{
string filePath = @"D:\xl2cad.mdb";
string mdbCommand = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" + filePath + ";Jet OLEDB:Engine Type=5";
ADODB.Connection cn = null;
ADOX.CatalogClass cat = null;
try
{
//创建数据库，有就删除重建，没有则新建
cat = new CatalogClass();
if (File.Exists(filePath))
{
File.Delete(filePath);
}
cat.Create(mdbCommand);
//连接数据库
cn = new ADODB.Connection();
cat = null;
cat = new CatalogClass();
//创建数据表
foreach (xl2cad_cad.midTable midtable in tables)
{
cn.Open("Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" + filePath, null, null, -1);
cat.ActiveConnection = cn;
ADOX.TableClass table = new TableClass();
string tablename = midtable.Name;
table.ParentCatalog = cat;
table.Name = tablename;
cat.Tables.Append(table);
//将数据写入数据表
//Excel.WorkSheet.Range导出的object[obj1,obj2]中,obj1为行，obj2为列
//因此先按obj2的个数建立字段，在按obj1的个数一行行填入数据
object[,] datas = midtable.data;
int rowCount = 0;
int columnCount = 0;
rowCount = datas.GetUpperBound(0); //第一维obj1的最大值，行数
columnCount = datas.GetUpperBound(1); //第二维obj2的最大值，列数
//建立字段
for (int i = 0; i <= columnCount; i++)
{
ADOX.ColumnClass col = null;
col = new ADOX.ColumnClass();
col.ParentCatalog = cat;
col.Properties["Jet OLEDB:Allow Zero Length"].Value = true;
col.Name = "Value" + i;
table.Columns.Append(col, ADOX.DataTypeEnum.adVarChar, 25);
}
//按行填入数据
object ra = null;
ADODB.Recordset rs = new ADODB.Recordset();
for (int i = 0; i <= rowCount; i++)
{
//构造按行写入的sql语句
string sql1 = String.Format("INSERT INTO {0} (", tablename);
string sql2 = String.Format(") VALUES (");
string strValue = null;
for (int j = 0; j <= columnCount; j++)
{
if (datas[i, j] == null)
{
strValue = datas[i, j] as string;
}
else
{
strValue = datas[i, j].ToString();
}
sql1 += String.Format("Value{0},", j);
sql2 += String.Format("'{0}',", strValue);
}
string sql = sql1 + sql2 + ")";
//将 ,) 替换为 ）
sql = sql.Replace(",)", ")");
rs = cn.Execute(sql, out ra, -1);
}
}
}
catch (System.Exception ex)
{
MessageBox.Show(ex.ToString());
}
finally
{
cn.Close();
System.Runtime.InteropServices.Marshal.FinalReleaseComObject(cn);
System.Runtime.InteropServices.Marshal.FinalReleaseComObject(cat);
System.GC.Collect();
}
}
//Создаем новую пустую БД из схемы
public void CreateDB(string pathDB)
{
if (!m_isSchema)
{
return;
}
if (File.Exists(pathDB))
{
File.Copy(pathDB, pathDB + ".old", true);
File.Delete(pathDB);
}
string connStr = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" + pathDB;
try
{
m_catalogADOX.Create(connStr);
for (int i = 0; i < m_schema.tables.Length; i++)
{
TableClass tableADOX = new TableClass();
tableADOX.Name = m_schema.tables[i].name;
tableADOX.ParentCatalog = m_catalogADOX;
jcolumns[] cols = m_schema.tables[i].columns;
for (int j = 0; j < cols.Length; j++)
{
ColumnClass columnADOX = new ColumnClass();
columnADOX.ParentCatalog = m_catalogADOX;
columnADOX.Name = cols[j].name;
columnADOX.Type = cols[j].type;
columnADOX.DefinedSize = cols[j].definedSize;
columnADOX.Precision = cols[j].precision;
columnADOX.Properties["Autoincrement"].Value = (object)cols[j].autoincrement;
columnADOX.Properties["Nullable"].Value = (object)cols[j].nullable;
columnADOX.Properties["Fixed Length"].Value = (object)cols[j].fixedLength;
tableADOX.Columns.Append(columnADOX, cols[j].type, cols[j].definedSize);
}
m_catalogADOX.Tables.Append(tableADOX);
//index
jindexs[] ind = m_schema.tables[i].indexs;
if (ind != null)
{
for (int j = 0; j < ind.Length; j++)
{
IndexClass indexADOX = new IndexClass();
indexADOX.Name = ind[j].name;
indexADOX.Clustered = ind[j].clustered;
indexADOX.IndexNulls = ind[j].indexNulls;
indexADOX.PrimaryKey = ind[j].primaryKey;
indexADOX.Unique = ind[j].unique;
m_catalogADOX.Tables[m_schema.tables[i].name].Indexes.Append(ind[j].name, ind[j].name);
}
}
//key
jkeys[] key = m_schema.tables[i].keys;
if (key != null)
{
for (int j = 0; j < key.Length; j++)
{
KeyClass keyADOX = new KeyClass();
keyADOX.Name = key[j].name;
keyADOX.Type = key[j].type;
//keyADOX.Columns = key[j].column;
//keyADOX.Columns.Append(key[j].column, ADOX.DataTypeEnum.adInteger, 0);
//ColumnClass columnADOX = new ColumnClass();
//columnADOX.Name = key[j].column;
m_catalogADOX.Tables[m_schema.tables[i].name].Keys.Append(
key[j].name, key[j].type, m_catalogADOX.Tables[m_schema.tables[i].name].Columns[key[j].column], "", "");
//Без этой записи на win2000 выдавала глюк!!! Первая строка в vars отказывалась запичыватся
m_catalogADOX.Tables[m_schema.tables[i].name].Keys.Refresh();
}
}
}
}
finally
{
//if (conn != null)
// conn.Close();
//m_catalogADOX.ActiveConnection = null;
}
//Data и
ConnectionClass conn = null;
try
{
conn = new ConnectionClass();
conn.Open(connStr, "", "", 0);
for (int i = 0; i < m_schema.tables.Length; i++)
{
jrows[] rows = m_schema.tables[i].rows;
if (rows != null && m_schema.tables[i].name == "vars")
{
FillDataRestruct(conn, m_schema.tables[i].name, rows);
}
}
}
finally
{
if (conn != null)
{
conn.Close();
}
}
}
private void BT_LoginLogin_Click(object sender, EventArgs e)
{
var a = 0;
#region "Timer arguments"
timer1.Start();
// Start timer!
toolStripStatusLabel1.Visible = true;
toolStripStatusLabel1.Text = "Loading";
// UI stuff
// Shows in the bottom of the form that you are loading, etc.
#endregion
try
{
a += 1;
// Increment a + 1
usernameFromLogin = TB_LoginUsername.Text;
// Declare that current username is now cloned into usernameFromLogin
const string constring =
@"Provider=Microsoft.ACE.OLEDB.12.0;Data Source=A:\Users\Atul Anand Sinha\Documents\Visual Studio 2013\Projects\Database\Database.accdb;Persist Security Info=False";
// Create connection string
var connection = new OleDbConnection(constring);
// OleDbConnection is now connected using the connection string/address
var command =
new OleDbCommand(
"SELECT * FROM TPersons WHERE UserName='******' AND PassWord='******';", connection);
// OleDbCommand assigns command the new instance and the ADO.NET query
// Select everything from the TPersons table where the username and password are equal to the textbox values
OleDbDataReader reader;
// Create a OleDbDataReader instance to read data
{
connection.Open();
// Open this node
reader = command.ExecuteReader();
// Execute and run the reader
var count = 0;
// Error counter
while (reader != null && reader.Read())
{
count = count + 1;
}
if (count == 1)
// If count is exactly equal to 1 then:
{
if (
MessageBox.Show("Login successful!", "Success!", MessageBoxButtons.OK,
MessageBoxIcon.Information) == DialogResult.OK)
// Show success message!
// If OK is pressed in success message then
{
using (var clientForm = new ClientMain())
// Create an instance of ClientMain form
{
clientForm.ShowDialog();
// Show it
}
connection.Dispose();
// Dispose the connection to prevent future mishaps
#region "Create log database args"
// If the user is new, they might now have a training record database.
// This creates it for them
Catalog cat = new CatalogClass();
// Create a new catalog using the CatalogClass instance
var cntPath = Directory.GetCurrentDirectory();
// Declare local directory
var createStr = "Provider=Microsoft.ACE.OLEDB.12.0;Data Source=" + cntPath + "\\" +
TB_LoginUsername.Text.ToLower() + "_LOG.accdb;";
// Connection string with local directory and the lowercase username_LOG.accdb
if (!File.Exists(cntPath + "\\" + TB_LoginUsername.Text.ToLower() + "_LOG.accdb"))
// If the file doesn't exist (if the user is new or file got deleted) then
{
cat.Create(createStr);
// Create the connection string
var tbl = new Table();
// Create a new table
tbl.Name = TB_LoginUsername.Text + "_SESSIONS";
// Table shall be called the username_SESSIONS
tbl.Columns.Append("ID", DataTypeEnum.adInteger);
// Add an ID column only for integers
tbl.Columns.Append("UserName", DataTypeEnum.adVarWChar, 100);
// Create a username column only for shorttext. Max limit is 100.
// ClientMain.Load() will update UserName to TB_LoginUsername.Text or usernamestringo if you will
tbl.Columns.Append("Cycling", DataTypeEnum.adVarWChar, 100);
// Create a cycling column only for shorttext. Max limit is 100.
tbl.Columns.Append("Running", DataTypeEnum.adVarWChar, 100);
// Create a running column only for shorttext. Max limit is 100.
tbl.Columns.Append("Swimming", DataTypeEnum.adVarWChar, 100);
// Create a swimming column only for shorttext. Max limit is 100.
cat.Tables.Append(tbl);
// Append changes to the table
Marshal.FinalReleaseComObject(tbl);
Marshal.FinalReleaseComObject(cat.Tables);
Marshal.FinalReleaseComObject(cat.ActiveConnection);
Marshal.FinalReleaseComObject(cat);
// Use System.InteropServices and Marshal to create the table
/*
* Inserting UserName into Row
*/
try
{
const string empty = "";
// Empty string to use this as a placeholder for cells we are not filling right now
////int emptyInt = int.Parse(empty);
//int? emptyInt = null; // using nullable int
//int empInt = 0;
var tableau = TB_LoginUsername.Text + "_SESSIONS";
// username_SESSIONS will be the table name
var usernametostring = TB_LoginUsername.Text;
// Already explained :)
var startPath = Directory.GetCurrentDirectory();
// Declare local directory path
var myCon =
new OleDbConnection(@"Provider=Microsoft.ACE.OLEDB.12.0;Data Source=" +
startPath + "\\" +
TB_LoginUsername.Text.ToLower() + "_LOG.accdb;");
// Declare connection strings
var cmd = new OleDbCommand();
// Create an OleDbCommand instance
cmd.CommandType = CommandType.Text;
var query = "INSERT INTO " + tableau +
" ([UserName], [ID], [Cycling], [Running], [Swimming]) VALUES(@username, @id, @cycling, @running, @swimming)";
// Declare ADO.NET query into a string
cmd.CommandText = query;
// The query is the command
cmd.Parameters.AddWithValue("@username", usernametostring);
cmd.Parameters.AddWithValue("@id", a);
// Has to be parsed otherwise @id returns a graphical ID value (eg. {00000000-CF60-05B3-B1D6-020F00000000})
// Nope, I accidentally used DataTypeEnum.adGUID thinking that it would be the most suitable for an ID variable
// adInteger is way more better!
cmd.Parameters.AddWithValue("@cycling", empty);
cmd.Parameters.AddWithValue("@running", empty);
cmd.Parameters.AddWithValue("@swimming", empty);
// We don't want anything in the sports columns yet as that will be updated in ClientMain.Log()
// We just want the username so ClientMain.BT_Log() can find is using WHERE
cmd.Connection = myCon;
// Declaring myCon as the connection
myCon.Open();
// Open the connection
cmd.ExecuteNonQuery();
// Execute the above command
MessageBox.Show("Username has been entered in your session database!");
// Show message!
cmd.Parameters.Clear();
// Clear verbatim values
//myCon.Dispose();
}
catch (OleDbException ex)
{
MessageBox.Show(ex.ToString());
}
}
}
}
else if (count > 1)
{
// If not then either username or password is duplicate
toolStripStatusLabel1.Visible = true;
toolStripStatusLabel1.Text = "Try again!";
MessageBox.Show("Duplicate username or password");
}
else
{
// Or it doesn't exist!
toolStripStatusLabel1.Visible = true;
toolStripStatusLabel1.Text = "Try again!";
MessageBox.Show("Username or password do not match.");
//ProgBarColor.SetState(toolStripProgressBar1, 2);
}
}
}
catch (OleDbException exception)
{
var errorMessages = "";
for (var i = 0; i < exception.Errors.Count; i++)
{
var oleDbError = exception.Errors[i];
if (oleDbError != null)
{
errorMessages += "Index #" + i + "\n" +
"Message: " + oleDbError.Message + "\n" +
"NativeError: " + oleDbError.NativeError + "\n" +
"Source: " + oleDbError.Source + "\n" +
"SQLState: " + oleDbError.SQLState + "\n";
}
// Generate OleDbException message
}
MessageBox.Show(errorMessages);
// Print it and throw.
throw;
}
#endregion
}
private void CreateCatalogue()
{
CatalogClass catalogClass = new CatalogClass();
catalogClass.Create(CONNECTION_STRING);
}
