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