public 存数据库()
{
InitializeComponent();
DatebaseFinished = func_finished;
Conn = new OleDbConnection(connstr);
Conn.Open();
string strSql = "DELETE 时间 from Alarm where 时间<#" + DateTime.Now.AddDays(-7) + "#";
OleDbCommand Cmd = new OleDbCommand(strSql, Conn);
Cmd.ExecuteNonQuery();
Conn.Close();
DBEngineClass dbengine = new DBEngineClass();
dbengine.CompactDatabase("F:\\操作日志\\logAccess.mdb", "F:\\操作日志\\myaccess2.mdb");
File.Delete("F:\\操作日志\\logAccess.mdb");
File.Move("F:\\操作日志\\myaccess2.mdb", "F:\\操作日志\\logAccess.mdb");
File.Delete("F:\\操作日志\\myaccess2.mdb");
Conn.Open();
}
/*
* public void WriteSqlData<T>( string tableName, IList<T> entitys, bool objectid = true)
* {
* Dictionary<string, Clazz> dictionary = ReflectUtils.MethodToFunction<T>();
* string str = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" + MDBPath;
* OleDbConnection oleDbConnection = new OleDbConnection(str + ";OLE DB Services=-4");
* oleDbConnection.Open();
* OleDbTransaction oleDbTransaction = oleDbConnection.BeginTransaction();
* OleDbCommand oleDbCommand = oleDbConnection.CreateCommand();
* oleDbCommand.Transaction = oleDbTransaction;
* IList<string> titleField = GetTitleField(oleDbCommand, tableName, objectid);
* oleDbCommand.CommandText = "";
* StringBuilder stringBuilder = new StringBuilder("INSERT INTO " + tableName + "(");
* StringBuilder stringBuilder2 = new StringBuilder();
* foreach (string current in titleField)
* {
* stringBuilder.Append(current + ",");
* }
* stringBuilder.Remove(stringBuilder.Length - 1, 1);
* stringBuilder.Append(") VALUES(");
* int length = stringBuilder.Length;
* foreach (T current2 in entitys)
* {
* foreach (string current3 in titleField)
* {
* Clazz clazz;
* bool flag = dictionary.TryGetValue(current3, out clazz);
* if (flag)
* {
* object obj = clazz.GetMethodInfo.Invoke(current2, null);
* bool flag2 = obj != null && obj.ToString().Contains("'");
* if (flag2)
* {
* stringBuilder.Append("'" + obj.ToString().Replace("'", "") + "',");
* }
* else
* {
* bool flag3 = obj is DateTime;
* if (flag3)
* {
* bool flag4 = ((DateTime)obj).Year == 1;
* if (flag4)
* {
* stringBuilder.Append("'',");
* }
* else
* {
* stringBuilder.Append("'" + obj + "',");
* }
* }
* else
* {
* stringBuilder.Append("'" + obj + "',");
* }
* }
* }
* else
* {
* stringBuilder.Append("'',");
* }
* }
* stringBuilder.Remove(stringBuilder.Length - 1, 1);
* stringBuilder.Append(");");
* OleDbCommand expr_251 = oleDbCommand;
* expr_251.CommandText += stringBuilder.ToString();
* stringBuilder.Remove(length, stringBuilder.Length - length);
* }
* string text = oleDbCommand.CommandText;
* text = text.Remove(text.Length - 1, 1);
* Console.WriteLine(text);
* oleDbCommand.CommandText = text;
* int num = oleDbCommand.ExecuteNonQuery();
* oleDbTransaction.Commit();
* oleDbConnection.Close();
* }*/
public static void WriteData <T>(string MDBPath, string tableName, IList <T> entitys, bool objectid = true)
{
DBEngine dBEngine = new DBEngineClass();
Database database = dBEngine.OpenDatabase(MDBPath, Type.Missing, Type.Missing, Type.Missing);
database.BeginTrans();
Recordset recordset = database.OpenRecordset(tableName, Type.Missing, Type.Missing, Type.Missing);
Dictionary <Field, Clazz> filedClazz = GetFiledClazz <T>(recordset, objectid);
foreach (T current in entitys)
{
recordset.AddNew();
foreach (Field current2 in filedClazz.Keys)
{
object obj = filedClazz[current2].GetMethodInfo.Invoke(current, null);
bool flag = obj is DateTime;
if (flag)
{
bool flag2 = ((DateTime)obj).Year == 1;
if (flag2)
{
continue;
}
}
bool flag3 = obj != null;
if (flag3)
{
bool flag4 = obj is int && (int)obj == 0;
if (!flag4)
{
current2.Value = obj;
}
}
}
recordset.Update(1, false);
}
database.CommitTrans(0);
recordset.Close();
database.Close();
}
void IProvider.CreateDatabase()
{
if (File.Exists(dbName))
{
throw SqlClient.Error.CreateDatabaseFailedBecauseSqlCEDatabaseAlreadyExists(dbName);
}
//创建数据库文件
var dbEngineClass = new DBEngineClass();
var dataBase = dbEngineClass.CreateDatabase(dbName, LanguageConstants.dbLangGeneral, DatabaseTypeEnum.dbEncrypt);
dataBase.Close();
var connection = new OleDbConnection(GetConnectionString(dbName));
connection.Open();
var transaction = connection.BeginTransaction();
var AccessDbSqlBuilder = new AccessDbSqlBuilder(this);
try
{
if (services.Model.GetTables().FirstOrDefault() == null)
{
throw SqlClient.Error.CreateDatabaseFailedBecauseOfContextWithNoTables(services.Model.DatabaseName);
}
var model = services.Model;
foreach (var table in model.GetTables())
{
//string createTableCommand = AccessDbSqlBuilder.GetCreateTableCommand(table);
//if (!string.IsNullOrEmpty(createTableCommand))
// Execute(connection, transaction, createTableCommand);
var commands = AccessDbSqlBuilder.GetCreateTableCommands(table);
foreach (var command in commands)
{
Debug.Assert(command != null);
Execute(connection, transaction, command);
}
}
foreach (MetaTable table in model.GetTables())
{
foreach (string commandText in AccessDbSqlBuilder.GetCreateForeignKeyCommands(table))
{
if (!string.IsNullOrEmpty(commandText))
{
Execute(connection, transaction, commandText);
}
}
}
transaction.Commit();
}
catch
{
transaction.Rollback();
throw;
}
finally
{
connection.Close();
}
}
private void CreateDatabase()
{
DBEngineClass dbe = new DBEngineClass();
Database db = dbe.CreateDatabase(name, LanguageConstants.dbLangGeneral, DatabaseTypeEnum.dbVersion40);
//table Clients. Used to store the name of the clients computers.
TableDef tblNew = db.CreateTableDef("Clients", this.oMissing, this.oMissing, this.oMissing);
this.CreateField(tblNew, "ID", false, DataTypeEnum.dbLong, (int)FieldAttributeEnum.dbAutoIncrField, 0, null);
this.CreateField(tblNew, "Computer", false, DataTypeEnum.dbText, 0, 255, null);
db.TableDefs.Append(tblNew);
//table Sections. Contains the sections within your session.
tblNew = db.CreateTableDef("Section", this.oMissing, this.oMissing, this.oMissing);
this.CreateField(tblNew, "ID", false, DataTypeEnum.dbInteger, 0, 0, null);
this.CreateField(tblNew, "Letter", false, DataTypeEnum.dbText, 0, 2, null);
this.CreateField(tblNew, "Tables", false, DataTypeEnum.dbInteger, 0, 0, null);
this.CreateField(tblNew, "MissingPair", false, DataTypeEnum.dbInteger, 0, 0, 0);
db.TableDefs.Append(tblNew);
//table Tables. Contains all the tables for all sections.
tblNew = db.CreateTableDef("Tables", this.oMissing, this.oMissing, this.oMissing);
this.CreateField(tblNew, "Section", false, DataTypeEnum.dbInteger, 0, 0, null);
this.CreateField(tblNew, "Table", false, DataTypeEnum.dbInteger, 0, 0, null);
this.CreateField(tblNew, "ComputerID", false, DataTypeEnum.dbInteger, 0, 0, 0);
this.CreateField(tblNew, "Status", false, DataTypeEnum.dbInteger, 0, 0, 0);
this.CreateField(tblNew, "LogOnOff", false, DataTypeEnum.dbInteger, 0, 0, 2);
this.CreateField(tblNew, "CurrentRound", false, DataTypeEnum.dbInteger, 0, 0, 0);
this.CreateField(tblNew, "CurrentBoard", false, DataTypeEnum.dbInteger, 0, 0, 0);
this.CreateField(tblNew, "UpdateFromRound", false, DataTypeEnum.dbInteger, 0, 0, 0);
db.TableDefs.Append(tblNew);
//table RoundData. Contains the movement data for all tables.
tblNew = db.CreateTableDef("RoundData", this.oMissing, this.oMissing, this.oMissing);
this.CreateField(tblNew, "Section", false, DataTypeEnum.dbInteger, 0, 0, null);
this.CreateField(tblNew, "Table", false, DataTypeEnum.dbInteger, 0, 0, null);
this.CreateField(tblNew, "Round", false, DataTypeEnum.dbInteger, 0, 0, null);
this.CreateField(tblNew, "NSPair", false, DataTypeEnum.dbInteger, 0, 0, null);
this.CreateField(tblNew, "EWPair", false, DataTypeEnum.dbInteger, 0, 0, null);
this.CreateField(tblNew, "LowBoard", false, DataTypeEnum.dbInteger, 0, 0, null);
this.CreateField(tblNew, "HighBoard", false, DataTypeEnum.dbInteger, 0, 0, null);
this.CreateField(tblNew, "KoPSTour", false, DataTypeEnum.dbInteger, 0, 0, null);
db.TableDefs.Append(tblNew);
//table ReceivedData. You need to create it here, but it will be filled by
//BPC when results are coming in.
for (int i = 0; i < 2; i++)
{
tblNew = db.CreateTableDef(i == 0 ? "ReceivedData" : "IntermediateData", this.oMissing, this.oMissing, this.oMissing);
this.CreateField(tblNew, "ID", false, DataTypeEnum.dbLong, (int)FieldAttributeEnum.dbAutoIncrField, 0, 1);
this.CreateField(tblNew, "Section", false, DataTypeEnum.dbInteger, 0, 0, null);
this.CreateField(tblNew, "Table", false, DataTypeEnum.dbInteger, 0, 0, null);
this.CreateField(tblNew, "Round", false, DataTypeEnum.dbInteger, 0, 0, null);
this.CreateField(tblNew, "Board", false, DataTypeEnum.dbInteger, 0, 0, null);
this.CreateField(tblNew, "PairNS", false, DataTypeEnum.dbInteger, 0, 0, null);
this.CreateField(tblNew, "PairEW", false, DataTypeEnum.dbInteger, 0, 0, null);
this.CreateField(tblNew, "Declarer", false, DataTypeEnum.dbInteger, 0, 0, null);
this.CreateField(tblNew, "NS/EW", true, DataTypeEnum.dbText, 0, 2, null);
this.CreateField(tblNew, "Contract", true, DataTypeEnum.dbText, 0, 10, null);
this.CreateField(tblNew, "Result", true, DataTypeEnum.dbText, 0, 10, null);
this.CreateField(tblNew, "LeadCard", true, DataTypeEnum.dbText, 0, 10, null);
this.CreateField(tblNew, "Remarks", true, DataTypeEnum.dbText, 0, 255, null);
this.CreateField(tblNew, "DateLog", false, DataTypeEnum.dbDate, 0, 0, null);
this.CreateField(tblNew, "TimeLog", false, DataTypeEnum.dbDate, 0, 0, null);
this.CreateField(tblNew, "Processed", false, DataTypeEnum.dbBoolean, 0, 0, false);
for (int j = 1; j < 5; j++)
{
this.CreateField(tblNew, "Processed" + j.ToString(), false, DataTypeEnum.dbBoolean, 0, 0, false);
}
this.CreateField(tblNew, "Erased", false, DataTypeEnum.dbBoolean, 0, 0, false);
db.TableDefs.Append(tblNew);
}
//table PlayerNumbers. Only required if you want your scoring program to have
//the possibility of registering the player numbers from Bridgemate input.
tblNew = db.CreateTableDef("PlayerNumbers", this.oMissing, this.oMissing, this.oMissing);
this.CreateField(tblNew, "Section", false, DataTypeEnum.dbInteger, 0, 0, null);
this.CreateField(tblNew, "Table", false, DataTypeEnum.dbInteger, 0, 0, null);
this.CreateField(tblNew, "Direction", false, DataTypeEnum.dbText, 0, 2, "");
this.CreateField(tblNew, "Number", true, DataTypeEnum.dbText, 0, 16, "");
db.TableDefs.Append(tblNew);
//table Settings. Only required when you want to control Bridgemate settings
//from the scoring program. Include only those fields you want to control the values.
tblNew = db.CreateTableDef("Settings", this.oMissing, this.oMissing, this.oMissing);
this.CreateField(tblNew, "Section", false, DataTypeEnum.dbInteger, 0, 0, 0);
this.CreateField(tblNew, "ShowResults", false, DataTypeEnum.dbBoolean, 0, 0, true);
this.CreateField(tblNew, "ShowOwnResult", false, DataTypeEnum.dbBoolean, 0, 0, true);
this.CreateField(tblNew, "RepeatResults", false, DataTypeEnum.dbBoolean, 0, 0, false);
this.CreateField(tblNew, "MaximumResults", false, DataTypeEnum.dbInteger, 0, 0, 0);
this.CreateField(tblNew, "ShowPercentage", false, DataTypeEnum.dbBoolean, 0, 0, false);
this.CreateField(tblNew, "GroupSections", false, DataTypeEnum.dbBoolean, 0, 0, false);
this.CreateField(tblNew, "ScorePoints", false, DataTypeEnum.dbInteger, 0, 0, 0);
this.CreateField(tblNew, "EnterResultsMethod", false, DataTypeEnum.dbInteger, 0, 0, 0);
this.CreateField(tblNew, "ShowPairNumbers", false, DataTypeEnum.dbBoolean, 0, 0, true);
this.CreateField(tblNew, "IntermediateResults", false, DataTypeEnum.dbBoolean, 0, 0, false);
this.CreateField(tblNew, "AutopoweroffTime", false, DataTypeEnum.dbInteger, 0, 0, 10);
this.CreateField(tblNew, "VerificationTime", false, DataTypeEnum.dbInteger, 0, 0, 2);
this.CreateField(tblNew, "ShowContract", false, DataTypeEnum.dbInteger, 0, 0, 0);
this.CreateField(tblNew, "LeadCard", false, DataTypeEnum.dbBoolean, 0, 0, true);
this.CreateField(tblNew, "MemberNumbers", false, DataTypeEnum.dbBoolean, 0, 0, false);
this.CreateField(tblNew, "MemberNumbersNoBlankEntry", false, DataTypeEnum.dbBoolean, 0, 0, true);
this.CreateField(tblNew, "BoardOrderVerification", false, DataTypeEnum.dbBoolean, 0, 0, true);
this.CreateField(tblNew, "HandRecordValidation", false, DataTypeEnum.dbBoolean, 0, 0, false);
this.CreateField(tblNew, "AutoShutDownBPC", false, DataTypeEnum.dbBoolean, 0, 0, false);
db.TableDefs.Append(tblNew);
db.Close();
dbe.FreeLocks();
}
private void CreateDatabase()
{
DBEngineClass dbe = new DBEngineClass();
Database db = dbe.CreateDatabase(name, LanguageConstants.dbLangGeneral, DatabaseTypeEnum.dbVersion40);
//table Clients. Used to store the name of the clients computers.
TableDef tblNew = db.CreateTableDef("Clients", this.oMissing, this.oMissing, this.oMissing);
this.CreateField(tblNew, "ID", false, DataTypeEnum.dbLong, (int) FieldAttributeEnum.dbAutoIncrField, 0, null);
this.CreateField(tblNew, "Computer", false, DataTypeEnum.dbText, 0, 255, null);
db.TableDefs.Append(tblNew);
//table Sections. Contains the sections within your session.
tblNew = db.CreateTableDef("Section", this.oMissing, this.oMissing, this.oMissing);
this.CreateField(tblNew, "ID", false, DataTypeEnum.dbInteger, 0, 0, null);
this.CreateField(tblNew, "Letter", false, DataTypeEnum.dbText, 0, 2, null);
this.CreateField(tblNew, "Tables", false, DataTypeEnum.dbInteger, 0, 0, null);
this.CreateField(tblNew, "MissingPair", false, DataTypeEnum.dbInteger, 0, 0, 0);
db.TableDefs.Append(tblNew);
//table Tables. Contains all the tables for all sections.
tblNew = db.CreateTableDef("Tables", this.oMissing, this.oMissing, this.oMissing);
this.CreateField(tblNew, "Section", false, DataTypeEnum.dbInteger, 0, 0, null);
this.CreateField(tblNew, "Table", false, DataTypeEnum.dbInteger, 0, 0, null);
this.CreateField(tblNew, "ComputerID", false, DataTypeEnum.dbInteger, 0, 0, 0);
this.CreateField(tblNew, "Status", false, DataTypeEnum.dbInteger, 0, 0, 0);
this.CreateField(tblNew, "LogOnOff", false, DataTypeEnum.dbInteger, 0, 0, 2);
this.CreateField(tblNew, "CurrentRound", false, DataTypeEnum.dbInteger, 0, 0, 0);
this.CreateField(tblNew, "CurrentBoard", false, DataTypeEnum.dbInteger, 0, 0, 0);
this.CreateField(tblNew, "UpdateFromRound", false, DataTypeEnum.dbInteger, 0, 0, 0);
db.TableDefs.Append(tblNew);
//table RoundData. Contains the movement data for all tables.
tblNew = db.CreateTableDef("RoundData", this.oMissing, this.oMissing, this.oMissing);
this.CreateField(tblNew, "Section", false, DataTypeEnum.dbInteger, 0, 0, null);
this.CreateField(tblNew, "Table", false, DataTypeEnum.dbInteger, 0, 0, null);
this.CreateField(tblNew, "Round", false, DataTypeEnum.dbInteger, 0, 0, null);
this.CreateField(tblNew, "NSPair", false, DataTypeEnum.dbInteger, 0, 0, null);
this.CreateField(tblNew, "EWPair", false, DataTypeEnum.dbInteger, 0, 0, null);
this.CreateField(tblNew, "LowBoard", false, DataTypeEnum.dbInteger, 0, 0, null);
this.CreateField(tblNew, "HighBoard", false, DataTypeEnum.dbInteger, 0, 0, null);
this.CreateField(tblNew, "KoPSTour", false, DataTypeEnum.dbInteger, 0, 0, null);
db.TableDefs.Append(tblNew);
//table ReceivedData. You need to create it here, but it will be filled by
//BPC when results are coming in.
for (int i = 0; i < 2; i++) {
tblNew = db.CreateTableDef(i == 0 ? "ReceivedData" : "IntermediateData", this.oMissing, this.oMissing, this.oMissing);
this.CreateField(tblNew, "ID", false, DataTypeEnum.dbLong, (int) FieldAttributeEnum.dbAutoIncrField, 0, 1);
this.CreateField(tblNew, "Section", false, DataTypeEnum.dbInteger, 0, 0, null);
this.CreateField(tblNew, "Table", false, DataTypeEnum.dbInteger, 0, 0, null);
this.CreateField(tblNew, "Round", false, DataTypeEnum.dbInteger, 0, 0, null);
this.CreateField(tblNew, "Board", false, DataTypeEnum.dbInteger, 0, 0, null);
this.CreateField(tblNew, "PairNS", false, DataTypeEnum.dbInteger, 0, 0, null);
this.CreateField(tblNew, "PairEW", false, DataTypeEnum.dbInteger, 0, 0, null);
this.CreateField(tblNew, "Declarer", false, DataTypeEnum.dbInteger, 0, 0, null);
this.CreateField(tblNew, "NS/EW", true, DataTypeEnum.dbText, 0, 2, null);
this.CreateField(tblNew, "Contract", true, DataTypeEnum.dbText, 0, 10, null);
this.CreateField(tblNew, "Result", true, DataTypeEnum.dbText, 0, 10, null);
this.CreateField(tblNew, "LeadCard", true, DataTypeEnum.dbText, 0, 10, null);
this.CreateField(tblNew, "Remarks", true, DataTypeEnum.dbText, 0, 255, null);
this.CreateField(tblNew, "DateLog", false, DataTypeEnum.dbDate, 0, 0, null);
this.CreateField(tblNew, "TimeLog", false, DataTypeEnum.dbDate, 0, 0, null);
this.CreateField(tblNew, "Processed", false, DataTypeEnum.dbBoolean, 0, 0, false);
for (int j = 1; j < 5; j++) {
this.CreateField(tblNew, "Processed" + j.ToString(), false, DataTypeEnum.dbBoolean, 0, 0, false);
}
this.CreateField(tblNew, "Erased", false, DataTypeEnum.dbBoolean, 0, 0, false);
db.TableDefs.Append(tblNew);
}
//table PlayerNumbers. Only required if you want your scoring program to have
//the possibility of registering the player numbers from Bridgemate input.
tblNew = db.CreateTableDef("PlayerNumbers", this.oMissing, this.oMissing, this.oMissing);
this.CreateField(tblNew, "Section", false, DataTypeEnum.dbInteger, 0, 0, null);
this.CreateField(tblNew, "Table", false, DataTypeEnum.dbInteger, 0, 0, null);
this.CreateField(tblNew, "Direction", false, DataTypeEnum.dbText, 0, 2, "");
this.CreateField(tblNew, "Number", true, DataTypeEnum.dbText, 0, 16, "");
db.TableDefs.Append(tblNew);
//table Settings. Only required when you want to control Bridgemate settings
//from the scoring program. Include only those fields you want to control the values.
tblNew = db.CreateTableDef("Settings", this.oMissing, this.oMissing, this.oMissing);
this.CreateField(tblNew, "Section", false, DataTypeEnum.dbInteger, 0, 0, 0);
this.CreateField(tblNew, "ShowResults", false, DataTypeEnum.dbBoolean, 0, 0, true);
this.CreateField(tblNew, "ShowOwnResult", false, DataTypeEnum.dbBoolean, 0, 0, true);
this.CreateField(tblNew, "RepeatResults", false, DataTypeEnum.dbBoolean, 0, 0, false);
this.CreateField(tblNew, "MaximumResults", false, DataTypeEnum.dbInteger, 0, 0, 0);
this.CreateField(tblNew, "ShowPercentage", false, DataTypeEnum.dbBoolean, 0, 0, false);
this.CreateField(tblNew, "GroupSections", false, DataTypeEnum.dbBoolean, 0, 0, false);
this.CreateField(tblNew, "ScorePoints", false, DataTypeEnum.dbInteger, 0, 0, 0);
this.CreateField(tblNew, "EnterResultsMethod", false, DataTypeEnum.dbInteger, 0, 0, 0);
this.CreateField(tblNew, "ShowPairNumbers", false, DataTypeEnum.dbBoolean, 0, 0, true);
this.CreateField(tblNew, "IntermediateResults", false, DataTypeEnum.dbBoolean, 0, 0, false);
this.CreateField(tblNew, "AutopoweroffTime", false, DataTypeEnum.dbInteger, 0, 0, 10);
this.CreateField(tblNew, "VerificationTime", false, DataTypeEnum.dbInteger, 0, 0, 2);
this.CreateField(tblNew, "ShowContract", false, DataTypeEnum.dbInteger, 0, 0, 0);
this.CreateField(tblNew, "LeadCard", false, DataTypeEnum.dbBoolean, 0, 0, true);
this.CreateField(tblNew, "MemberNumbers", false, DataTypeEnum.dbBoolean, 0, 0, false);
this.CreateField(tblNew, "MemberNumbersNoBlankEntry", false, DataTypeEnum.dbBoolean, 0, 0, true);
this.CreateField(tblNew, "BoardOrderVerification", false, DataTypeEnum.dbBoolean, 0, 0, true);
this.CreateField(tblNew, "HandRecordValidation", false, DataTypeEnum.dbBoolean, 0, 0, false);
this.CreateField(tblNew, "AutoShutDownBPC", false, DataTypeEnum.dbBoolean, 0, 0, false);
db.TableDefs.Append(tblNew);
db.Close();
dbe.FreeLocks();
}
