void Application_BeginRequest(object sender, EventArgs e)
{
DBScript dBScript = new DBScript();
string sql = "SELECT SUM(emp_status_login) AS userOnilne FROM tbl_emp_user";
MySqlDataReader rs = dBScript.selectSQL(sql);
if (rs.Read())
{
if (!rs.IsDBNull(0))
{
Application.Lock();
Application["TotalOnlineUsers"] = int.Parse(rs.GetString("userOnilne"));
Application.UnLock();
}
}
rs.Close();
dBScript.userLogOutTimeUpdate();
//อัพเดทการลาออก
dBScript.UpdateEmpEx();
//อัพเดทการเปลี่ยนตำแหน่ง
dBScript.UpdateEmpPos();
//อัพเดทการย้ายด่านฯ
dBScript.UpdateEmpCpoint();
//อัพเดทพนักงานที่ไม่มีประวัติการทำงานภายในฝ่าย
dBScript.CreateMotowayWorking();
//อัพเดทพนักงานที่ไม่มีประวัติการทำงานที่ด่านฯ
dBScript.CreateCpointWorking();
dBScript.CloseConnection();
}
void initCheck(int sel_Index)
{
#region 当前选中则勾选，其他取消勾选
if (ckl_DBTypes.Items.Count > 0)
{
ckl_DBTypes.SetItemCheckState(sel_Index, CheckState.Checked);
ckl_DBTypes.SetItemChecked(sel_Index, true);
for (int i = 0; i < ckl_DBTypes.Items.Count; i++)
{
if (i != sel_Index)
{
ckl_DBTypes.SetItemCheckState(i, CheckState.Unchecked);
ckl_DBTypes.SetItemChecked(i, false);
}
}
}
#endregion
TableInfo tabInfo = new TableInfo();
tabInfo.LstColInfo = lstCol;
tabInfo.TableName = TableName;
tabInfo.TabComment = table_Comment;
DataBaseType tgtDBType = ckl_DBTypes.SelectedItem.ToString().ConvertTo <DataBaseType>();
string sql_script = DBScript.GetTableScript(ConnectionModel.DbType, tabInfo, tgtDBType);
txtSqlContent.Text = sql_script;
}
public void _09_CreateTableWithIndex()
{
DBDatabase db = ConnectDb();
try
{
DBQuery tbl = DBQuery.Create.Table("CustomTable")
.Add("col1", DbType.Int32, DBColumnFlags.PrimaryKey | DBColumnFlags.AutoAssign)
.Add("col2", DbType.String, 50)
.Add("col3", DbType.String, 255);
//create 3 indexes on this table for the other columns
DBQuery idx1 = DBQuery.Create.Index("CT_ColBoth").Unique().On("CustomTable")
.Columns("col1", Order.Ascending, "col2", Order.Ascending);
DBQuery idx2 = DBQuery.Create.Index(true, "CT_Col2").On("CustomTable")
.Add("col2");
DBQuery idx3 = DBQuery.Create.Index("CT_Col1").On("CustomTable")
.Add("col1", Order.Ascending);
if (db.GetProperties().CheckSupports(DBSchemaTypes.CommandScripts))
{
DBScript create = DBQuery.Script(tbl, idx1, idx2, idx3);
TestContext.WriteLine(create.ToSQLString(db));
db.ExecuteNonQuery(create);
}
else //scripts are not supported so we are going to execute individually
{
db.ExecuteNonQuery(tbl);
db.ExecuteNonQuery(idx1);
db.ExecuteNonQuery(idx2);
db.ExecuteNonQuery(idx3);
}
}
finally
{
DBQuery droptbl = DBQuery.Drop.Table("CustomTable");
DBQuery dropidx1 = DBQuery.Drop.Index("CT_ColBoth").On("CustomTable");
DBQuery dropidx2 = DBQuery.Drop.Index("CT_Col2").On("CustomTable");
DBQuery dropidx3 = DBQuery.Drop.Index("CT_Col1").On("CustomTable");
if (db.GetProperties().CheckSupports(DBSchemaTypes.CommandScripts))
{
DBScript dropall = DBScript.Script(dropidx3, dropidx2, dropidx1, droptbl);
TestContext.WriteLine("\r\n" + dropall.ToSQLString(db));
db.ExecuteNonQuery(dropall);
}
else
{
db.ExecuteNonQuery(dropidx1);
db.ExecuteNonQuery(dropidx2);
db.ExecuteNonQuery(dropidx3);
db.ExecuteNonQuery(droptbl);
}
}
}
private object ExecuteScalarScript(DBDatabase db, DBScript script, bool supportsMultipleStatements)
{
if (supportsMultipleStatements)
{
return(db.ExecuteScalar(script));
}
else
{
object value = null;
using (System.Data.Common.DbConnection con = db.CreateConnection())
{
con.Open();
foreach (DBQuery q in script)
{
value = db.ExecuteScalar(con, q);
}
}
return(value);
}
}
public void Test_04_InsertScript()
{
byte[] imgdata = this.GetLocalImage("bomb.gif");
DBParam cName = DBParam.ParamWithValue("name", DbType.String, (object)"newType_1");
DBParam cDesc = DBParam.ParamWithValue("desc", DbType.String, (object)"newDescrption_1");
DBParam cPic = DBParam.ParamWithValue("pic", DbType.Binary, (object)imgdata);
DBInsertQuery ins = DBQuery.InsertInto("Categories")
.Fields("CategoryName", "Description", "Picture")
.Values(cName, cDesc, cPic);
DBSelectQuery sel = DBQuery.Select(DBFunction.LastID());
DBScript script = DBQuery.Begin(ins)
.Then(sel)
.End();
this.OutputSql(script, "Insert and Return last ID script");
this.OutputXML(script, "Insert and Return last ID script");
}
public void _10_CreateTableWithForeignKeys()
{
DBDatabase db = ConnectDb();
//Create the persons table
DBQuery createPersons = DBQuery.Create.Table("DSQL_Persons")
.Add("Person_ID", DbType.Int32, DBColumnFlags.AutoAssign | DBColumnFlags.PrimaryKey)
.Add("Person_Name", DbType.String, 50);
TestContext.WriteLine(createPersons.ToSQLString(db));
db.ExecuteNonQuery(createPersons);
//Create the orders table
DBQuery createOrders = DBQuery.Create.Table("DSQL_Orders")
.Add("Order_ID", DbType.Int32, DBColumnFlags.AutoAssign | DBColumnFlags.PrimaryKey)
.Add("Ordered_By", DbType.Int32)
.Add("Ordered_Date", DbType.DateTime)
.Add("Signed_By", DbType.Int32, DBColumnFlags.Nullable)
.Constraints(
DBConstraint.ForeignKey().Column("Ordered_By") //unnamed foreign key first
.References("DSQL_Persons").Column("Person_ID"),
DBConstraint.ForeignKey("Orders_Signed_By_2_Persons_PersonID").Column("Signed_By")
.References("DSQL_Persons").Column("Person_ID")
.OnDelete(DBFKAction.Cascade)
.OnUpdate(DBFKAction.Cascade)
);
//Execute the Create Table statements
TestContext.WriteLine(createOrders.ToSQLString(db));
db.ExecuteNonQuery(createOrders);
try
{
bool scripts = db.GetProperties().CheckSupports(DBSchemaTypes.CommandScripts);
DBParam pname = DBParam.Param("name", DbType.String);
DBScript insertperson = DBQuery.Script(
DBQuery.InsertInto("DSQL_Persons").Field("Person_Name").Value(pname),
DBQuery.Select(DBFunction.LastID())
);
//Insert one row into the persons table
pname.Value = "First Person";
TestContext.WriteLine(insertperson.ToSQLString(db));
int firstpid = Convert.ToInt32(this.ExecuteScalarScript(db, insertperson, scripts));
//And another row
pname.Value = "Second Person";
int secondpid = Convert.ToInt32(this.ExecuteScalarScript(db, insertperson, scripts));
//Create an order with orderedby = firstpid and signedby = secondpid
DBParam orderedby = DBParam.ParamWithValue(DbType.Int32, firstpid);
DBParam signedby = DBParam.ParamWithValue(DbType.Int32, secondpid);
DBScript insertorder = DBQuery.Script(
DBQuery.InsertInto("DSQL_Orders")
.Field("Ordered_By").Value(orderedby)
.Field("Ordered_Date").Value(DBFunction.GetDate())
.Field("Signed_By").Value(signedby),
DBQuery.Select(DBFunction.LastID())
);
TestContext.WriteLine(insertorder.ToSQLString(db));
int orderid = Convert.ToInt32(this.ExecuteScalarScript(db, insertorder, scripts));
//Now try to create an order that breaks referential integrity
orderedby.Value = -100;
try
{
orderid = Convert.ToInt32(db.ExecuteScalar(insertorder));
throw new InvalidOperationException("We should not be able to insert these rows. FAILED test");
}
catch (InvalidOperationException)
{
throw;
}
catch (Exception ex)
{
TestContext.WriteLine("Sucessfully caught an exception that breaks referential integrity");
}
//Finally check the cascading deletes
//check the Orders table count
//delete a person row
//ensure that the corresponding row for Signed By FK was deleted
DBQuery getcount = DBQuery.SelectCount().From("DSQL_ORDERS");
int ordercount = Convert.ToInt32(
db.ExecuteScalar(getcount));
DBQuery del = DBQuery.DeleteFrom("DSQL_Persons")
.WhereField("Person_ID", Compare.Equals, DBParam.ParamWithValue(secondpid));
int removed = db.ExecuteNonQuery(del);
Assert.AreEqual(1, removed);
TestContext.WriteLine("Removed a single row from the persons table");
int newordercount = Convert.ToInt32(db.ExecuteScalar(getcount));
//Make sure the orders row has been deleted
Assert.AreEqual(newordercount, ordercount - 1);
TestContext.WriteLine("Validated that the corresponding row in the orders table has been removed too");
}
finally
{
//Clean up tables in order
db.ExecuteNonQuery(DBQuery.Drop.Table("DSQL_Orders"));
db.ExecuteNonQuery(DBQuery.Drop.Table("DSQL_Persons"));
TestContext.WriteLine("Database has been cleaned up");
}
}
public override void GenerateCreateProcedureScript(DBSchemaSproc schemaSproc, DBScript script)
{
if (null == schemaSproc)
{
throw new ArgumentNullException("schemaSproc");
}
if (string.IsNullOrEmpty(schemaSproc.Name))
{
throw new ArgumentNullException("schemaSproc.Name");
}
if (schemaSproc.Type != DBSchemaTypes.StoredProcedure)
{
throw new ArgumentOutOfRangeException("schemaSproc.Type");
}
if (null == script)
{
throw new ArgumentNullException("script");
}
this._buildingsproc = true;
this.BeginCreate(DBSchemaTypes.StoredProcedure, null);
this.WriteSource(schemaSproc.Catalog, schemaSproc.Schema, schemaSproc.Name);
try
{
GenerateRoutineParameters(schemaSproc, true);
//now just output the script as SQL
script.BuildStatement(this);
}
finally
{
this._buildingsproc = false;
}
}
public override void GenerateCreateFunctionScript(DBSchemaFunction schemaFunc, DBScript script)
{
if (null == schemaFunc)
{
throw new ArgumentNullException("schemaFunc");
}
if (string.IsNullOrEmpty(schemaFunc.Name))
{
throw new ArgumentNullException("schemaFunc.Name");
}
if (schemaFunc.Type != DBSchemaTypes.Function)
{
throw new ArgumentOutOfRangeException("schemaFunc.Type");
}
if (null == script)
{
throw new ArgumentNullException("script");
}
this._buildingsproc = true;
this.BeginCreate(DBSchemaTypes.Function, null);
this.WriteSource(schemaFunc.Catalog, schemaFunc.Schema, schemaFunc.Name);
try
{
this.GenerateRoutineParameters(schemaFunc, false);
WriteFunctionReturns(schemaFunc.ReturnParameter.DbType, schemaFunc.ReturnParameter.ParameterSize);
this.BeginNewLine();
script.BuildStatement(this);
}
finally
{
this._buildingsproc = false;
}
}
