public void Prepare(ref Log returnLog) { try { TraceFile source = new TraceFile(); source.InitializeAsReader(TraceSource); while (source.Read()) { foreach (SQLEvent sqlEvent in EventList) { if (source.GetString(source.GetOrdinal("EventClass")) == sqlEvent.EventName) { SQLStatement newStatement = new SQLStatement(); newStatement.Statement = source.GetString(source.GetOrdinal("TextData")); newStatement.ConnectionString = ConnectionString == "{PARENT}" ? _config.SourceConnectionString : ConnectionString; QueryList.Add(newStatement); } } } returnLog.EndTime = DateTime.Now; returnLog.Message = "Prepare successful."; } catch (Exception ex) { returnLog.EndTime = DateTime.Now; returnLog.Successful = false; returnLog.Message = "Prepare failed - " + ex.Message; } }
public void defaultAll() { SQLStatement con = new SQLStatement(SLS.Static.Server, SLS.Static.Database); String sql = "SELECT FORMAT(MEMBER.MemberID,'00000000'), CONCAT(MEMBER.fName,' ',MEMBER.mName,' ',MEMBER.lName), MEMBERTYPE.MemberTypeName, SAVINGSTYPE.savingsTypeName, SAVINGSTYPE.interestRate, SAVINGSTYPE.initialDeposit, SAVINGSTYPE.maintainingBalance, SAVINGSTYPE.balanceToEarn, case SAVINGSTYPE.maxWithdrawAmount when 0 then 'Not Available' else CONCAT( (CONVERT(nvarchar, SAVINGSTYPE.maxWithdrawAmount)), case SAVINGSTYPE.maxWithdrawMode when 0 then ' / Day' when 1 then ' / Week' when 2 then ' / Month' else ' / Year' end) end, CONCAT(DORMANCY.inactivityValue, ' ',(case DORMANCY.inactivityTime when 0 then 'Day/s' when 1 then 'Week/s' when 2 then 'Month/s' else 'Year/s' end)), CONCAT(DORMANCY.deductionAmount, (case DORMANCY.isPercentage when 0 then ' Pesos ' else ' % ' end), (case DORMANCY.deductionMode when 0 then ' / Day' when 1 then ' / Week' when 2 then ' / Month' else ' / Year' end)), DORMANCY.activationFee, FORMAT(SAVINGSACCOUNT.SavingsAccountID,'00000000'), SAVINGSACCOUNT.dateOpened, SAVINGSACCOUNT.currentBalance FROM MEMBER, MEMBERTYPE, APPLICABLESAVINGS, SAVINGSTYPE, DORMANCY, SAVINGSACCOUNT WHERE MEMBER.MemberTypeID = MEMBERTYPE.MemberTypeID and MEMBERTYPE.MemberTypeID = APPLICABLESAVINGS.MemberTypeID and APPLICABLESAVINGS.SavingsTypeID = SAVINGSTYPE.SavingsTypeID and SAVINGSTYPE.SavingsTypeID = DORMANCY.SavingsTypeID and SAVINGSTYPE.SavingsTypeID = SAVINGSACCOUNT.SavingsTypeID and SAVINGSACCOUNT.MemberID = MEMBER.MemberID and SAVINGSACCOUNT.SavingsAccountID = " + SLS.Static.ID + ""; SqlDataReader reader = con.executeReader(sql); if (reader.HasRows) { reader.Read(); txtMemberID.Text = "MEM - " + reader.GetString(0); txtMemberName.Text = reader.GetString(1); txtMemberType.Text = reader.GetString(2); txtSavName.Text = reader.GetString(3); txtInterest.Text = reader.GetDecimal(4).ToString(); txtInitial.Text = reader.GetDecimal(5).ToString(); txtMainBal.Text = reader.GetDecimal(6).ToString(); txtBalToEarn.Text = reader.GetDecimal(7).ToString(); txtMaxWithdraw.Text = reader.GetString(8); txtDormancy.Text = reader.GetString(9); txtDeductDetails.Text = reader.GetString(10); txtActivationFee.Text = reader.GetDecimal(11).ToString(); txtSavAccount.Text = "SAV - " + reader.GetString(12); txtDateStarted.Text = reader.GetDateTime(13).ToString(); txtCurrentBalance.Text = reader.GetDecimal(14).ToString(); con = new SQLStatement(SLS.Static.Server, SLS.Static.Database); sql = "SELECT CONCAT('SAV - ', FORMAT(SAVINGSACCOUNT.SavingsAccountID,'00000000')) as [Savings Account ID], CASE SAVINGSTRANSACTION.transactionType when 1 then 'Deposit' when 2 then 'Withdraw' when 3 then 'Annual Increase' else 'Dormancy Deduction' end as [Transaction Type], SAVINGSTRANSACTION.transactionAmount as [Amount], SAVINGSTRANSACTION.transactionDate as [Date], SAVINGSTRANSACTION.transactionRepresentative as [Representative], SAVINGSTRANSACTION.currentBalance as [Balance] FROM SAVINGSACCOUNT, SAVINGSTRANSACTION WHERE SAVINGSACCOUNT.SavingsAccountID = SAVINGSTRANSACTION.SavingsAccountID and SAVINGSACCOUNT.SavingsAccountID = " + SLS.Static.ID + ""; DataSet ds = con.executeDataSet(sql, "Account"); dataGridView1.DataSource = ds; dataGridView1.DataMember = "Account"; dataGridView1.ColumnHeadersDefaultCellStyle.Alignment = DataGridViewContentAlignment.MiddleCenter; } }
protected override void Execute(CodeActivityContext context) { using (SqlConnection connection = new SqlConnection(connectionString.Get <string>(context))) { SqlCommand command = new SqlCommand(SQLStatement.Get <string>(context), connection); SqlDataAdapter da = new SqlDataAdapter(command); try { DataSet ds = new DataSet(); da.Fill(ds); DataRow[] linhas = new DataRow[ds.Tables[0].Rows.Count]; ds.Tables[0].Rows.CopyTo(linhas, 0); data.Set(context, linhas); } catch (Exception ex) { Console.WriteLine(ex.Message); } finally { connection.Close(); } } }
private void btnSave_Click(object sender, EventArgs e) { SQLStatement con = new SQLStatement(SLS.Static.Server, SLS.Static.Database); String sql = "INSERT INTO SAVINGSTRANSACTION(transactionDate, transactionType, transactionAmount, transactionRepresentative, currentBalance, SavingsAccountID) VALUES (@transactionDate, @transactionType, @transactionAmount, @transactionRepresentative, @currentBalance, @SavingsAccountID)"; Dictionary<String, Object> parameters = new Dictionary<string, object>(); parameters.Add("@transactionDate", Convert.ToDateTime(DateTime.Now.ToLongDateString())); parameters.Add("@transactionType", '2'); parameters.Add("@transactionAmount", Convert.ToDecimal(txtCurrentBalance.Text)); parameters.Add("@transactionRepresentative", txtMemberName.Text); parameters.Add("@currentBalance", '0'); parameters.Add("@SavingsAccountID", SLS.Static.ID); int result = Convert.ToInt32(con.executeNonQuery(sql, parameters)); if (result == 1) { con = new SQLStatement(SLS.Static.Server, SLS.Static.Database); sql = "UPDATE SAVINGSACCOUNT SET dateClosed = @dateClosed, currentBalance = '0' WHERE SavingsAccountID = @SavingsAccountID"; parameters = new Dictionary<string, object>(); parameters.Add("@dateClosed", Convert.ToDateTime(DateTime.Now.ToLongDateString())); parameters.Add("@SavingsAccountID", SLS.Static.ID); result = Convert.ToInt32(con.executeNonQuery(sql, parameters)); if (result == 1) { MessageBox.Show("Closing a savings account is successful.", "Success", MessageBoxButtons.OK, MessageBoxIcon.Information); defaultAll(); btnSave.Enabled = false; } } else { MessageBox.Show("Closing a savings account is not successful.", "Error", MessageBoxButtons.OK, MessageBoxIcon.Error); } }
public void addChildrenRelationship(int ind) { string relship; bool childgend; if (tableChild.Rows[ind][3].ToString() == "1") { relship = "Son"; childgend = true; } else { relship = "Daughter"; childgend = false; } SQLStatement con = new SQLStatement(SLS.Static.Server, SLS.Static.Database); String sql = "INSERT INTO FAMILYBACKGROUND (fName,mName, lName, birthDate, gender, relationship, MemberID) VALUES (@fName, @mName, @lName, @birthDate, @gender, @relationship, @MemberID)"; Dictionary<String, Object> parameters = new Dictionary<string, object>(); parameters.Add("@fName", tableChild.Rows[ind][1].ToString()); parameters.Add("@mName", tableChild.Rows[ind][2].ToString()); parameters.Add("@lName", tableChild.Rows[ind][0].ToString()); parameters.Add("@birthDate", tableChild.Rows[ind][4].ToString()); parameters.Add("@gender", childgend); parameters.Add("@relationship", relship.ToString()); parameters.Add("@MemberID", MemberID); int result = Convert.ToInt32(con.executeNonQuery(sql, parameters)); }
public void defaultAll() { txtFN.Clear(); txtMN.Clear(); txtLN.Clear(); txtPosition.Clear(); er1.Visible = false; er2.Visible = false; er3.Visible = false; er4.Visible = false; if (SLS.Static.ID != 0) { SQLStatement con = new SQLStatement(SLS.Static.Server, SLS.Static.Database); String sql = "SELECT fName, mName, lName, position FROM LOANGRANTOR where lgID = " + SLS.Static.ID + " "; SqlDataReader reader = con.executeReader(sql); if (reader.HasRows) { reader.Read(); txtFN.Text = Convert.ToString(reader[0]); txtMN.Text = Convert.ToString(reader[1]); txtLN.Text = Convert.ToString(reader[2]); txtPosition.Text = Convert.ToString(reader[3]); } } }
public override void Run(Context ctx, Schedule schedule) { SQLStatement = base.GetStatement(PayableSQLObject.Payable); opearteList = GetOpearteList(PayableSQLObject.Payable); if (connectionToRemoteDatabase().Sucessd) { ExcuteDataBaseResult excuteDataBaseResult = base.remoteExcuteDataBase.excuteStatement(SQLStatement, connectionToRemoteDatabase().Sqlconn); payableSaveInfoObjList = handleData <PayableInfoSaveObject>(excuteDataBaseResult.Ds); } else { //打印 } if (LoginAPI(model)) { Dictionary <string, Result> statusMap = new Dictionary <string, Result>(); foreach (PayableInfoSaveObject payableSaveInfoObject in payableSaveInfoObjList) { InvokeReturnHandle <InvokeResult> callResult = InvokeAPI(opearteList, payableSaveInfoObject, InvokeCloudAPI.Login(model), ctx, formID, model); statusMap.Add(payableSaveInfoObject.Model.FBillNo, callResult.CustomOpearteObject.Result); } List <string> getUpdateSQLStatements = new SQLStatement(model).getUpdateSQLStatement(model, statusMap); SqlConnection Sqlconn = connectionToRemoteDatabase().Sqlconn; foreach (string UpdateSQLStatement in getUpdateSQLStatements) { updateMiddleDataBase(UpdateSQLStatement, model, "t_kf_payable", Sqlconn); } } }
static void TestTpcds_LoadData() { var files = Directory.GetFiles(@"../../../tpcds", "*.sql"); string[] norun = { "q1", "q10" }; Tpcds.CreateTables(); Tpcds.LoadTables("tiny"); Tpcds.AnalyzeTables(); // make sure all queries can generate phase one opt plan QueryOption option = new QueryOption(); option.optimize_.enable_subquery_unnest_ = true; option.optimize_.remove_from_ = false; option.optimize_.use_memo_ = false; foreach (var v in files) { char[] splits = { '.', '/', '\\' }; var tokens = v.Split(splits, StringSplitOptions.RemoveEmptyEntries); if (norun.Contains(tokens[1])) { continue; } var sql = File.ReadAllText(v); var result = SQLStatement.ExecSQL(sql, out string phyplan, out _, option); } }
public void ProcessorComplete(TraceData data) { _data.ExecutionPath.AddRange(ctx.executionCalls); _data.AllExecutionCalls.AddRange(ctx.allCalls); foreach (ExecutionCall call in ctx.allCalls.Where(c => c.Type.HasFlag(ExecutionCallType.SQL))) { var lineNumber = call.StartLine; try { var sqlStatement = data.SQLStatements.Where(s => s.LineNumber == lineNumber + 1).First(); call.SQLStatement = sqlStatement; sqlStatement.ParentCall = call.Parent; call.Duration = sqlStatement.Duration; }catch (Exception ex) { /* this happens durring loops where there was no SQL statement. We need to find a SQL statement prior and clone it and update binds when we capture those */ var dummyStatement = new SQLStatement("SELECT 'X' FROM DUAL"); call.SQLStatement = dummyStatement; dummyStatement.ParentCall = call.Parent; call.Duration = dummyStatement.Duration; } } }
static void RunSQLFromFile(string filename) { // Not working as expected, need to investigate. var option = new QueryOption(); option.explain_.mode_ = ExplainMode.full; option.optimize_.use_memo_ = true; option.explain_.show_estCost_ = false; string allquery = File.ReadAllText(filename); string[] listquery = allquery.Split(';'); List <string> listoutput = new List <string>(); int linenum = 0; for (int i = 0; i < listquery.Length; ++i) { linenum = i + 1; string sql = listquery[i].Trim(); if (sql.Length <= 0) { continue; } else if (sql.StartsWith("--")) { continue; } try { Console.WriteLine(sql); string outline = linenum.ToString(); outline += ": " + sql + "\n"; listoutput.Add(outline); var result = SQLStatement.ExecSQL(sql, out string physicplan, out string error_, option); if (physicplan != null) { Console.WriteLine(physicplan); listoutput.Add(physicplan); } if (result != null) { Console.WriteLine(result); listoutput.Add(result.ToString()); } } catch (Exception e) { Console.WriteLine("SQL: " + sql + "\nEXCEPTION: " + e + "\n"); continue; } } string alloutput = string.Join('\n', listoutput); string outfile = filename + ".out"; File.WriteAllText(outfile, alloutput); }
public void defaultAll() { txtMemName.Text = ""; txtMinAge.Text = ""; txtMaxAge.Text = ""; txtFee.Text = ""; ckbSavings.Checked = false; ckbTime.Checked = false; ckbLoan.Checked = false; txtShare.Text = ""; er1.Visible = true; er2.Visible = true; er3.Visible = true; er4.Visible = true; er5.Visible = false; if (SLS.Static.ID != 0) { SQLStatement con = new SQLStatement(SLS.Static.Server, SLS.Static.Database); String sql = "SELECT MemberTypeID, MemberTypeName, MinAgeRequired, MaxAgeRequired, Fee, SavingsApplicable, TimeApplicable, LoanApplicable, ShareRequired FROM MEMBERTYPE where MemberTypeID = " + SLS.Static.ID + " "; SqlDataReader reader = con.executeReader(sql); if (reader.HasRows) { reader.Read(); MemberTypeID = Convert.ToInt32(reader[0]); txtMemName.Text = Convert.ToString(reader[1]); txtMinAge.Text = Convert.ToString(reader[2]); txtMaxAge.Text = Convert.ToString(reader[3]); txtFee.Text = Convert.ToString(reader[4]); if (Convert.ToString(reader[5]) == "True") { ckbSavings.Checked = true; } else { ckbSavings.Checked = false; } if (Convert.ToString(reader[6]) == "True") { ckbTime.Checked = true; } else { ckbTime.Checked = false; } if (Convert.ToString(reader[7]) == "True") { ckbLoan.Checked = true; txtShare.Enabled = true; txtShare.Text = Convert.ToString(reader[8]); } else { ckbLoan.Checked = false; txtShare.Enabled = false; txtShare.Clear(); } } } }
public SQLBookingInterface() { // // TODO: Add constructor logic here // date = new GetMeetingDate(); sql = new SQLStatement(); }
static public void AnalyzeTables() { foreach (var v in tabnames_) { var sql = $"analyze {v};"; SQLStatement.ExecSQL(sql, out _, out _); } }
static void createBuildInTestTables() { // create tables string[] createtables = { @"create table test (t1 int, t2 int, t3 int, t4 int);" , @"create table a (a1 int, a2 int, a3 int, a4 int);", @"create table b (b1 int, b2 int, b3 int, b4 int);", @"create table c (c1 int, c2 int, c3 int, c4 int);", @"create table d (d1 int, d2 int, d3 int, d4 int);", // nullable tables @"create table r (r1 int, r2 int, r3 int, r4 int);", // distributed tables @"create table ad (a1 int, a2 int, a3 int, a4 int) distributed by a1;", @"create table bd (b1 int, b2 int, b3 int, b4 int) distributed by b1;", @"create table cd (c1 int, c2 int, c3 int, c4 int) distributed by c1;", @"create table dd (d1 int, d2 int, d3 int, d4 int) distributed by d1;", @"create table ar (a1 int, a2 int, a3 int, a4 int) replicated;", @"create table br (b1 int, b2 int, b3 int, b4 int) replicated;", @"create table arb (a1 int, a2 int, a3 int, a4 int) roundrobin;", @"create table brb (b1 int, b2 int, b3 int, b4 int) roundrobin;", // steaming tables @"create table ast (a0 datetime, a1 int, a2 int, a3 int, a4 int);", // bounded table with ts @"create stream ainf (a0 datetime, a1 int, a2 int, a3 int, a4 int);", // unbounded table }; SQLStatement.ExecSQLList(string.Join("", createtables)); // load tables var appbin_dir = AppContext.BaseDirectory.Substring(0, AppContext.BaseDirectory.LastIndexOf("bin")); var folder = $@"{appbin_dir}/../data"; var tables = new List <string>() { "test", "a", "b", "c", "d", "r", "ad", "bd", "cd", "dd", "ar", "br", "arb", "brb", "ast" }; foreach (var v in tables) { string filename = $@"'{folder}/{v}.tbl'"; var sql = $"copy {v} from {filename};"; var result = SQLStatement.ExecSQL(sql, out _, out _); } // create index string[] createindexes = { @"create unique index dd1 on d(d1);", @"create index dd2 on d(d2);", }; SQLStatement.ExecSQLList(string.Join("", createindexes)); // analyze tables foreach (var v in tables) { var sql = $"analyze {v};"; var result = SQLStatement.ExecSQL(sql, out _, out _); } }
public void loadDatabase() { SQLStatement con = new SQLStatement(SLS.Static.Server, SLS.Static.Database); String sql = "SELECT MEMBER.MemberID as [ID], CONCAT(MEMBER.fName,' ',MEMBER.mName,' ',MEMBER.lName) as [Name], MEMBERTYPE.MemberTypeName as [Member Type Name], TIMEDEPOSITRATES.TimeDepositRatesID as [ID], CONCAT(TIMEDEPOSITRATES.noOfDays, ' Days') as [No. Of Days], CONCAT(TIMEDEPOSITRATES.minAmount,' - ', TIMEDEPOSITRATES.maxAmount) as [Amount Range], CONCAT(TIMEDEPOSITRATES.interest, ' %') as [Interest], TIMEDEPOSITACCOUNT.TimeDepositAccountID as [Time Deposit Account ID], TIMEDEPOSITACCOUNT.currentBalance as [Amount] FROM TIMEDEPOSITRATES, TIMEDEPOSITACCOUNT, MEMBER, MEMBERTYPE WHERE TIMEDEPOSITRATES.TimeDepositRatesID = TIMEDEPOSITACCOUNT.TimeDepositRatesID and TIMEDEPOSITACCOUNT.MemberID = MEMBER.MemberID and MEMBER.MemberTypeID = MEMBERTYPE.MemberTypeID and MEMBER.MemberID = " + SLS.Static.ID + ""; DataSet ds = con.executeDataSet(sql, "Account"); dataGridView1.DataSource = ds; dataGridView1.DataMember = "Account"; dataGridView1.ColumnHeadersDefaultCellStyle.Alignment = DataGridViewContentAlignment.MiddleCenter; }
static public void CreateIndexes() { string curdir = Directory.GetCurrentDirectory(); string folder = $@"{curdir}/../../../../tpch/sql_scripts"; string filename = $@"{folder}/TableIndexes.sql"; var sql = File.ReadAllText(filename); SQLStatement.ExecSQLList(sql); }
internal static void Run(Assembly assembly, SQLStatement stmt, ExecContext context) { // now we can execute it var type = assembly.GetType("QueryCode"); var instance = assembly.CreateInstance("QueryCode"); var meth = type.GetMember("Run").First() as MethodInfo; meth.Invoke(instance, new object[] { stmt, context }); }
public BindContext(SQLStatement current, BindContext parent) { stmt_ = current; parent_ = parent; if (parent is null) { globalSubqCounter_ = 0; } }
static public void CreateTables() { string curdir = Directory.GetCurrentDirectory(); string folder = $@"{curdir}\..\..\..\jobench\create"; string filename = $@"{folder}\schema.sql"; var sql = File.ReadAllText(filename); SQLStatement.ExecSQLList(sql); }
public override void Run(Context ctx, Schedule schedule) { //1从中间表中获取当前需要调用的模块的SQL // 1.2.连接中间数据库 // 1.3.获取数据 //2.调用Kingdee登录API //3.调用客户API //4.反写中间数据库 //5.关闭数据库连接 SQLStatement = GetStatement(CustomerSQLObject.Customer); opearteList = GetOpearteList(CustomerSQLObject.Customer); //修改的单独处理 BussnessModify customerModify = new BussnessModify(); customerModify.excuteSQL(model, connectionToRemoteDatabase(), ctx); if (connectionToRemoteDatabase().Sucessd) { ExcuteDataBaseResult excuteDataBaseResult = base.remoteExcuteDataBase.excuteStatement(SQLStatement, connectionToRemoteDatabase().Sqlconn); customSaveInfoObjList = handleData <CustomInfoSaveObject>(excuteDataBaseResult.Ds); if (!closeConnetction(connectionToRemoteDatabase().Sqlconn)) { //打印关闭连接 BussnessLog.WriteBussnessLog("", "数据库操作错误", model + "中间表取数后,关闭数据库连接失败! 请联系系统管理员!"); return; } } else { if (!closeConnetction(connectionToRemoteDatabase().Sqlconn)) { BussnessLog.WriteBussnessLog("", "数据库操作错误", model + "中间表取数前打开数据库连接失败! 请联系系统管理员!"); return; } } Dictionary <string, Result> statusMap = new Dictionary <string, Result>(); if (LoginAPI(model)) { foreach (CustomInfoSaveObject customSaveInfoObj in customSaveInfoObjList) { InvokeReturnHandle <InvokeResult> callResult = InvokeAPI(opearteList, customSaveInfoObj, InvokeCloudAPI.Login(model), ctx, formID, model); statusMap.Add(customSaveInfoObj.Model.Fnumber, callResult.CustomOpearteObject.Result); } List <string> getUpdateSQLStatements = new SQLStatement(model).getUpdateSQLStatement(model, statusMap); SqlConnection Sqlconn = connectionToRemoteDatabase().Sqlconn; foreach (string UpdateSQLStatement in getUpdateSQLStatements) { updateMiddleDataBase(UpdateSQLStatement, model, "t_kf_customer", Sqlconn); } if (!closeConnetction(Sqlconn)) { BussnessLog.WriteBussnessLog("", "数据库操作错误", model + "中间表数据更新后,关闭数据库连接失败! 请联系系统管理员!"); return; } } }
public void loadDatabase() { SQLStatement con = new SQLStatement(SLS.Static.Server, SLS.Static.Database); String sql = SLS.Static.sqlParams; DataSet ds = con.executeDataSet(sql,SLS.Static.parameters ,"Member"); dataGridView1.DataSource = ds; dataGridView1.DataMember = "Member"; dataGridView1.Columns[0].Visible = false; dataGridView1.ColumnHeadersDefaultCellStyle.Alignment = DataGridViewContentAlignment.MiddleCenter; }
static public void DropTables() { string curdir = Directory.GetCurrentDirectory(); string folder = $@"{curdir}/../../../../tpcds/sql_scripts"; string filename = $@"{folder}/DropTables.sql"; var sql = File.ReadAllText(filename); SQLStatement.ExecSQLList(sql); System.GC.Collect(); }
public override void Run(Context ctx, Schedule schedule) { //VoucherNumbers.Add("记", "PRE001"); //VoucherNumbers.Add("现收", "PRE002"); //VoucherNumbers.Add("现付", "PRE003"); //VoucherNumbers.Add("转", "PRE004"); //VoucherNumbers.Add("银收", "PZZ1"); //VoucherNumbers.Add("银付", "PZZ2"); SQLStatement = base.GetStatement(VouchSQLObject.Voucher); opearteList = GetOpearteList(VouchSQLObject.Voucher); if (connectionToRemoteDatabase().Sucessd) { ExcuteDataBaseResult excuteDataBaseResult = base.remoteExcuteDataBase.excuteStatement(SQLStatement, connectionToRemoteDatabase().Sqlconn); voucherSaveInfoObjList = handleData <VoucherInfoSaveObject>(excuteDataBaseResult.Ds); if (!closeConnetction(connectionToRemoteDatabase().Sqlconn)) { //打印关闭连接 BussnessLog.WriteBussnessLog("", "数据库操作错误", model + "中间表取数后,关闭数据库连接失败! 请联系系统管理员!"); return; } } else { if (!closeConnetction(connectionToRemoteDatabase().Sqlconn)) { //打印关闭连接 BussnessLog.WriteBussnessLog("", "数据库操作错误", model + "中间表取数前打开数据库连接失败! 请联系系统管理员!"); return; } } if (LoginAPI(model)) { Dictionary <string, Result> statusMap = new Dictionary <string, Result>(); foreach (VoucherInfoSaveObject voucherSaveInfoObj in voucherSaveInfoObjList) { InvokeReturnHandle <InvokeResult> callResult = InvokeAPI(opearteList, voucherSaveInfoObj, InvokeCloudAPI.Login(model), ctx, formID, model); statusMap.Add(voucherSaveInfoObj.Model.FVOUCHERGROUPNO, callResult.CustomOpearteObject.Result); } List <string> getUpdateSQLStatements = new SQLStatement(model).getUpdateSQLStatement(model, statusMap); SqlConnection Sqlconn = connectionToRemoteDatabase().Sqlconn; foreach (string UpdateSQLStatement in getUpdateSQLStatements) { updateMiddleDataBase(UpdateSQLStatement, model, "t_kf_voucher", Sqlconn); } if (!closeConnetction(Sqlconn)) { BussnessLog.WriteBussnessLog("", "数据库操作错误", model + "中间表数据更新后,关闭数据库连接失败! 请联系系统管理员!"); return; } } }
internal static void Run(CompilerResults cr, SQLStatement stmt, ExecContext context) { // now we can execute it var assembly = cr.CompiledAssembly; var queryCode = assembly.GetType("QueryCode"); var runmethod = queryCode.GetMethod("Run"); runmethod.Invoke(null, new object[2] { stmt, context }); }
static public void LoadTables(string subfolder) { string save_curdir = Directory.GetCurrentDirectory(); string folder = $@"{save_curdir}/../../../../tpch/sql_scripts"; Directory.SetCurrentDirectory(folder); var sql = File.ReadAllText($@"LoadTables-{subfolder}.sql"); SQLStatement.ExecSQLList(sql); Directory.SetCurrentDirectory(save_curdir); }
public CteExpr(string cteName, List <string> colNames, SQLStatement query) : base() { Utils.Assumes(query is SelectStmt); query_ = query as SelectStmt; Debug.Assert(!query_.isCteDefinition_); query_.isCteDefinition_ = true; cteName_ = cteName; colNames_ = colNames; refcnt_ = 0; }
protected override void Execute(CodeActivityContext context) { using (SqlConnection connection = new SqlConnection(ConnectionString.Get(context))) { using (SqlCommand command = new SqlCommand(SQLStatement.Get(context), connection)) { connection.Open(); command.ExecuteNonQuery(); } } }
static public void CreateTables(bool isdistr = false) { DropTables(); string curdir = Directory.GetCurrentDirectory(); string folder = $@"{curdir}/../../../../tpch/sql_scripts"; string postfix = isdistr ? "_d" : ""; string filename = $@"{folder}/tpch{postfix}.sql"; var sql = File.ReadAllText(filename); SQLStatement.ExecSQLList(sql); }
static public void LoadTables(string subfolder) { string curdir = Directory.GetCurrentDirectory(); string folder = $@"{curdir}\..\..\..\tpch\data\{subfolder}"; foreach (var v in tabnames_) { string filename = $@"'{folder}\{v}.tbl'"; var sql = $"copy {v} from {filename};"; SQLStatement.ExecSQL(sql, out _, out _); } }
public void loadDatabase() { SQLStatement con = new SQLStatement(SLS.Static.Server, SLS.Static.Database); String sql = "SELECT SAVINGSACCOUNT.SavingsAccountID, CONCAT('SAV - ', FORMAT(SAVINGSACCOUNT.SavingsAccountID,'00000000')) as [Savings Account ID], SAVINGSACCOUNT.MemberID as [Member ID], CONCAT(MEMBER.fName,' ' , MEMBER.mName, ' ', MEMBER.lName) as [Member Name], SAVINGSACCOUNT.SavingsTypeID as [Savings Type ID], SAVINGSTYPE.savingsTypeName as [Savings Type Name], SAVINGSTYPE.interestRate as [Interest Rate], SAVINGSTYPE.initialDeposit as [Initial Deposit], SAVINGSTYPE.maintainingBalance as [Maintaining Balance], SAVINGSTYPE.balanceToEarn as [Balance To Earn], case SAVINGSTYPE.maxWithdrawAmount when 0 then 'Not Available' else CONCAT( (CONVERT(nvarchar, SAVINGSTYPE.maxWithdrawAmount)), (case SAVINGSTYPE.maxWithdrawMode when 0 then ' / Day' when 1 then ' / Week' when 2 then ' / Month' else ' / Year' end)) end as [Maximum Withdrawal], CONCAT(DORMANCY.inactivityValue, ' ',(case DORMANCY.inactivityTime when 0 then 'Day/s' when 1 then 'Week/s' when 2 then 'Month/s' else 'Year/s' end)) as [Inactivity Period], CONCAT(DORMANCY.deductionAmount, (case DORMANCY.isPercentage when 0 then ' Pesos ' else ' % ' end), (case DORMANCY.deductionMode when 0 then ' / Day' when 1 then ' / Week' when 2 then ' / Month' else ' / Year' end)) as [Deduction], DORMANCY.activationFee[Activation Fee], SAVINGSACCOUNT.dateOpened as [Date Started], SAVINGSACCOUNT.currentBalance as [Current Balance] FROM SAVINGSACCOUNT, MEMBER, SAVINGSTYPE, DORMANCY WHERE SAVINGSACCOUNT.MemberID = MEMBER.MemberID and SAVINGSACCOUNT.SavingsTypeID = SAVINGSTYPE.SavingsTypeID and DORMANCY.SavingsTypeID = SAVINGSTYPE.SavingsTypeID and MEMBER.MemberID = " + SLS.Static.ID + " and SAVINGSACCOUNT.dateClosed IS NULL"; DataSet ds = con.executeDataSet(sql, "Account"); dataGridView1.DataSource = ds; dataGridView1.DataMember = "Account"; dataGridView1.Columns[0].Visible = false; dataGridView1.Columns[2].Visible = false; dataGridView1.Columns[3].Visible = false; dataGridView1.ColumnHeadersDefaultCellStyle.Alignment = DataGridViewContentAlignment.MiddleCenter; }
static public void CreateTables() { // hack: drop tpch table customer SQLStatement.ExecSQL("drop table customer;", out _, out _); string curdir = Directory.GetCurrentDirectory(); string folder = $@"{curdir}\..\..\..\tpch\create"; string filename = $@"{folder}\tpch.sql"; var sql = File.ReadAllText(filename); SQLStatement.ExecSQLList(sql); }
public void loadDatabase() { SQLStatement con = new SQLStatement(SLS.Static.Server, SLS.Static.Database); String sql = "SELECT MEMBER.MemberID as [ID], CONCAT(MEMBER.fName,' ',MEMBER.mName,' ',MEMBER.lName) as [Name], DATEDIFF(YEAR, MEMBER.birthDate, @DateNow ) as [Age], MEMBERTYPE.MemberTypeName as [Member Type] FROM MEMBER, MEMBERTYPE WHERE MEMBER.MemberTypeID = MEMBERTYPE.MemberTypeID"; Dictionary<String, Object> parameters = new Dictionary<string, object>(); parameters.Add("@DateNow", Convert.ToDateTime(DateTime.Now.ToString("yyyy-MM-dd"))); DataSet ds = con.executeDataSet(sql, parameters, "Member"); dataGridView1.DataSource = ds; dataGridView1.DataMember = "Member"; dataGridView1.Columns[0].Visible = false; dataGridView1.ColumnHeadersDefaultCellStyle.Alignment = DataGridViewContentAlignment.MiddleCenter; }
static void createBuildInTestTables() { // create tables string[] createtables = { @"create table test (a1 int, a2 int, a3 int, a4 int);" , @"create table a (a1 int, a2 int, a3 int, a4 int);", @"create table b (b1 int, b2 int, b3 int, b4 int);", @"create table c (c1 int, c2 int, c3 int, c4 int);", @"create table d (d1 int, d2 int, d3 int, d4 int);", // nullable tables @"create table r (r1 int, r2 int, r3 int, r4 int);", // distributed tables @"create table ad (a1 int, a2 int, a3 int, a4 int) distributed by a1;", @"create table bd (b1 int, b2 int, b3 int, b4 int) distributed by b1;", @"create table cd (c1 int, c2 int, c3 int, c4 int) distributed by c1;", @"create table dd (d1 int, d2 int, d3 int, d4 int) distributed by d1;", // steaming tables @"create table ast (a0 datetime, a1 int, a2 int, a3 int, a4 int);", }; SQLStatement.ExecSQLList(string.Join("", createtables)); // load tables var curdir = Directory.GetCurrentDirectory(); var folder = $@"{curdir}\..\..\..\data"; var tables = new List <string>() { "a", "b", "c", "d", "r", "ad", "bd", "cd", "dd", "ast" }; foreach (var v in tables) { string filename = $@"'{folder}\{v}.tbl'"; var sql = $"copy {v} from {filename};"; var result = SQLStatement.ExecSQL(sql, out _, out _); } // create index string[] createindexes = { @"create unique index dd1 on d(d1);", @"create index dd2 on d(d2);", }; SQLStatement.ExecSQLList(string.Join("", createindexes)); // analyze tables foreach (var v in tables) { var sql = $"analyze {v};"; var result = SQLStatement.ExecSQL(sql, out _, out _); } }
public void defaultAll() { SQLStatement con = new SQLStatement(SLS.Static.Server, SLS.Static.Database); String sql = "SELECT MEMBER.MemberID as [ID], CONCAT(MEMBER.fName,' ',MEMBER.mName,' ',MEMBER.lName) as [Name], MEMBERTYPE.MemberTypeName as [Member Type Name], TIMEDEPOSITRATES.TimeDepositRatesID as [ID], CONCAT(TIMEDEPOSITRATES.noOfDays, ' Days') as [No. Of Days], CONCAT(TIMEDEPOSITRATES.minAmount,' - ', TIMEDEPOSITRATES.maxAmount) as [Amount Range], CONCAT(TIMEDEPOSITRATES.interest, ' %') as [Interest], TIMEDEPOSITACCOUNT.TimeDepositAccountID as [Time Deposit Account ID], TIMEDEPOSITACCOUNT.currentBalance as [Amount] FROM TIMEDEPOSITRATES, TIMEDEPOSITACCOUNT, MEMBER, MEMBERTYPE WHERE TIMEDEPOSITRATES.TimeDepositRatesID = TIMEDEPOSITACCOUNT.TimeDepositRatesID and TIMEDEPOSITACCOUNT.MemberID = MEMBER.MemberID and MEMBER.MemberTypeID = MEMBERTYPE.MemberTypeID and MEMBER.MemberID = " + SLS.Static.ID + ""; SqlDataReader reader = con.executeReader(sql); if (reader.HasRows) { reader.Read(); txtMemberID.Text = "MEM - " + Convert.ToString(reader.GetInt32(0)); txtMemberName.Text = reader.GetString(1); txtMemberType.Text = reader.GetString(2); } }
public void addFatherRelationship() { SQLStatement con = new SQLStatement(SLS.Static.Server, SLS.Static.Database); String sql = "INSERT INTO FAMILYBACKGROUND (fName,mName, lName, birthDate, gender, relationship, MemberID) VALUES (@fName, @mName, @lName, @birthDate, @gender, @relationship, @MemberID)"; Dictionary<String, Object> parameters = new Dictionary<string, object>(); parameters.Add("@fName", txtFNFN.Text); parameters.Add("@mName", txtFNMN.Text); parameters.Add("@lName", txtFNLN.Text); parameters.Add("@birthDate", Convert.ToDateTime(dtFN.Value)); parameters.Add("@gender", "1"); parameters.Add("@relationship", "Father"); parameters.Add("@MemberID", MemberID); int result = Convert.ToInt32(con.executeNonQuery(sql, parameters)); }
public void loadDatabase() { if(SLS.Static.hasSearch == 0) { if(cobFilter.SelectedIndex == 0) { SQLStatement con = new SQLStatement(SLS.Static.Server, SLS.Static.Database); String sql = "SELECT LoanTypeID as [ID], loanTypeName as [Loan Type], minAmount as [Minimum Amount], maxAmount as [Maximum Amount], CONCAT(noOfComaker, ' Members') as [No. of Co-maker], CONCAT(entitlement, ' %') as [Entitlement], CONCAT(eligibility, ' %') as [Eligibility], [status] as [Status] FROM LOANTYPE"; DataSet ds = con.executeDataSet(sql, "Savings Type"); dataGridView1.DataSource = ds; dataGridView1.DataMember = "Savings Type"; dataGridView1.Columns[0].Visible = false; dataGridView1.ColumnHeadersDefaultCellStyle.Alignment = DataGridViewContentAlignment.MiddleCenter; } else if (cobFilter.SelectedIndex == 1) { SQLStatement con = new SQLStatement(SLS.Static.Server, SLS.Static.Database); String sql = "SELECT LoanTypeID as [ID], loanTypeName as [Loan Type], minAmount as [Minimum Amount], maxAmount as [Maximum Amount], CONCAT(noOfComaker, ' Members') as [No. of Co-maker], CONCAT(entitlement, ' %') as [Entitlement], CONCAT(eligibility, ' %') as [Eligibility], [status] as [Status] FROM LOANTYPE WHERE [status] = 1"; DataSet ds = con.executeDataSet(sql, "Savings Type"); dataGridView1.DataSource = ds; dataGridView1.DataMember = "Savings Type"; dataGridView1.Columns[0].Visible = false; dataGridView1.ColumnHeadersDefaultCellStyle.Alignment = DataGridViewContentAlignment.MiddleCenter; } else if (cobFilter.SelectedIndex == 2) { SQLStatement con = new SQLStatement(SLS.Static.Server, SLS.Static.Database); String sql = "SELECT LoanTypeID as [ID], loanTypeName as [Loan Type], minAmount as [Minimum Amount], maxAmount as [Maximum Amount], CONCAT(noOfComaker, ' Members') as [No. of Co-maker], CONCAT(entitlement, ' %') as [Entitlement], CONCAT(eligibility, ' %') as [Eligibility], [status] as [Status] FROM LOANTYPE WHERE [status] = 0"; DataSet ds = con.executeDataSet(sql, "Savings Type"); dataGridView1.DataSource = ds; dataGridView1.DataMember = "Savings Type"; dataGridView1.Columns[0].Visible = false; dataGridView1.ColumnHeadersDefaultCellStyle.Alignment = DataGridViewContentAlignment.MiddleCenter; } else { SQLStatement con = new SQLStatement(SLS.Static.Server, SLS.Static.Database); String sql = "SELECT LoanTypeID as [ID], loanTypeName as [Loan Type], minAmount as [Minimum Amount], maxAmount as [Maximum Amount], CONCAT(noOfComaker, ' Members') as [No. of Co-maker], CONCAT(entitlement, ' %') as [Entitlement], CONCAT(eligibility, ' %') as [Eligibility], [status] as [Status] FROM LOANTYPE"; DataSet ds = con.executeDataSet(sql, "Savings Type"); dataGridView1.DataSource = ds; dataGridView1.DataMember = "Savings Type"; dataGridView1.Columns[0].Visible = false; dataGridView1.ColumnHeadersDefaultCellStyle.Alignment = DataGridViewContentAlignment.MiddleCenter; } } else { } }
public override void Run(Context ctx, Schedule schedule) { SQLStatement = base.GetStatement(SupplierSQLObject.Supplier); opearteList = GetOpearteList(SupplierSQLObject.Supplier); //修改的单独处理 BussnessModify customerModify = new BussnessModify(); customerModify.excuteSQL(model, connectionToRemoteDatabase(), ctx); if (connectionToRemoteDatabase().Sucessd) { ExcuteDataBaseResult excuteDataBaseResult = base.remoteExcuteDataBase.excuteStatement(SQLStatement, connectionToRemoteDatabase().Sqlconn); supplierSaveInfoObjList = handleData <SupplierInfoSaveObject>(excuteDataBaseResult.Ds); if (!closeConnetction(connectionToRemoteDatabase().Sqlconn)) { //打印关闭连接 BussnessLog.WriteBussnessLog("", "数据库操作错误", model + "中间表取数后,关闭数据库连接失败! 请联系系统管理员!"); return; } } else { BussnessLog.WriteBussnessLog("", "数据库操作错误", model + "中间表取数前打开数据库连接失败! 请联系系统管理员!"); return; } if (LoginAPI(model)) { Dictionary <string, Result> statusMap = new Dictionary <string, Result>(); foreach (SupplierInfoSaveObject supplierSaveInfoObj in supplierSaveInfoObjList) { InvokeReturnHandle <InvokeResult> callResult = (InvokeReturnHandle <InvokeResult>)(object) InvokeAPI(opearteList, supplierSaveInfoObj, InvokeCloudAPI.Login(model), ctx, formID, model); statusMap.Add(supplierSaveInfoObj.Model.Fnumber, callResult.CustomOpearteObject.Result); } List <string> getUpdateSQLStatements = new SQLStatement(model).getUpdateSQLStatement(model, statusMap); // ExcuteDataBaseResult excuteDataBaseResult = remoteExcuteDataBase.excuteStatement(updateSQLStatement, connectionToRemoteDatabase().Sqlconn); SqlConnection Sqlconn = connectionToRemoteDatabase().Sqlconn; foreach (string UpdateSQLStatement in getUpdateSQLStatements) { updateMiddleDataBase(UpdateSQLStatement, model, "t_kf_supplier", Sqlconn); } if (!closeConnetction(Sqlconn)) { //打印关闭连接信息 BussnessLog.WriteBussnessLog("", "数据库操作错误", model + "中间表数据更新后,关闭数据库连接失败! 请联系系统管理员!"); return; } } }
private void button2_Click(object sender, EventArgs e) { var date = DateTime.Now; SQLStatement con = new SQLStatement(SLS.Static.Server, SLS.Static.Database); String sql = "INSERT INTO LOAN (MemberID, LoanTypeID, applyAmount, TermID, ModeID, dateApplied) VALUES (@MemID, @LTypeID, @ApplyAmt, @TermID, @ModeID, @dateApplied)"; Dictionary < String, Object > parameters = new Dictionary<string, object>(); parameters.Add("@MemId", Convert.ToInt32(textBox1.Text)); parameters.Add("@LTypeID", Convert.ToInt32(textBox6.Text)); parameters.Add("@ApplyAmt", textBox4.Text); parameters.Add("@TermID", Convert.ToInt32(textBox7.Text)); parameters.Add("@ModeID", Convert.ToInt32(textBox8.Text)); parameters.Add("@dateApplied", date); SqlDataReader reader = con.executeReader(sql, parameters); MessageBox.Show("Loan Application Saved", "Saved", MessageBoxButtons.OK, MessageBoxIcon.Information); }
private void btnStatus_Click(object sender, EventArgs e) { if (SLS.Static.ID != 0) { if (btnStatus.Text == "DELETE") { SQLStatement con = new SQLStatement(SLS.Static.Server, SLS.Static.Database); String sql = "UPDATE SAVINGSTYPE SET [status] = @status WHERE SavingsTypeID = @SavingsTypeID"; Dictionary<String, Object> parameters = new Dictionary<string, object>(); parameters.Add("@status", false); parameters.Add("@SavingsTypeID", SLS.Static.ID); int result = Convert.ToInt32(con.executeNonQuery(sql, parameters)); if (result == 1) { MessageBox.Show("A Savings Type is Updated.", "Saved", MessageBoxButtons.OK, MessageBoxIcon.Information); loadDatabase(); btnStatus.Text = "DELETE"; } else { MessageBox.Show("A Savings Type is Not Updated.", "Error", MessageBoxButtons.OK, MessageBoxIcon.Error); } } else { SQLStatement con = new SQLStatement(SLS.Static.Server, SLS.Static.Database); String sql = "UPDATE SAVINGSTYPE SET [status] = @status WHERE SavingsTypeID = @SavingsTypeID"; Dictionary<String, Object> parameters = new Dictionary<string, object>(); parameters.Add("@status", true); parameters.Add("@SavingsTypeID", SLS.Static.ID); int result = Convert.ToInt32(con.executeNonQuery(sql, parameters)); if (result == 1) { MessageBox.Show("A Savings Type is Updated.", "Saved", MessageBoxButtons.OK, MessageBoxIcon.Information); loadDatabase(); btnStatus.Text = "DELETE"; } else { MessageBox.Show("A Savings Type is Not Updated.", "Error", MessageBoxButtons.OK, MessageBoxIcon.Error); } } } else { MessageBox.Show("Please choose a record to be updated.", "Error", MessageBoxButtons.OK, MessageBoxIcon.Error); } }
public override void Run(Context ctx, Schedule schedule) { SQLStatement = base.GetStatement(ReceivableSQLObject.Receivable); opearteList = GetOpearteList(ReceivableSQLObject.Receivable); if (connectionToRemoteDatabase().Sucessd) { ExcuteDataBaseResult excuteDataBaseResult = base.remoteExcuteDataBase.excuteStatement(SQLStatement, connectionToRemoteDatabase().Sqlconn); receivableSaveInfoObjList = handleData <ReceivableInfoSaveObject>(excuteDataBaseResult.Ds); if (!closeConnetction(connectionToRemoteDatabase().Sqlconn)) { //打印关闭连接 BussnessLog.WriteBussnessLog("", "数据库操作错误", model + "中间表取数后,关闭数据库连接失败! 请联系系统管理员!"); return; } } else { if (!closeConnetction(connectionToRemoteDatabase().Sqlconn)) { //打印关闭连接 BussnessLog.WriteBussnessLog("", "数据库操作错误", model + "中间表取数前打开数据库连接失败! 请联系系统管理员!"); return; } } if (LoginAPI(model)) { Dictionary <string, Result> statusMap = new Dictionary <string, Result>(); foreach (ReceivableInfoSaveObject receivableInfoSaveObject in receivableSaveInfoObjList) { InvokeReturnHandle <InvokeResult> callResult = InvokeAPI(opearteList, receivableInfoSaveObject, InvokeCloudAPI.Login(model), ctx, formID, model); statusMap.Add(receivableInfoSaveObject.Model.FBillNo, callResult.CustomOpearteObject.Result); } List <string> getUpdateSQLStatements = new SQLStatement(model).getUpdateSQLStatement(model, statusMap); SqlConnection Sqlconn = connectionToRemoteDatabase().Sqlconn; foreach (string UpdateSQLStatement in getUpdateSQLStatements) { updateMiddleDataBase(UpdateSQLStatement, model, "t_kf_receivable", Sqlconn); } if (!closeConnetction(Sqlconn)) { BussnessLog.WriteBussnessLog("", "数据库操作错误", model + "中间表数据更新后,关闭数据库连接失败! 请联系系统管理员!"); return; } } }
public Int32 checkValues() { //Required Fields Validation Int32 isValid = 0; SLS.Validate.Alpha ctrlString = new SLS.Validate.Alpha(); if (ctrlString.checkString(txtModeName.Text) == 1) { isValid = 1; er1.Visible = true; } try { Convert.ToInt32(txtDaysInterval.Text); SQLStatement con = new SQLStatement(SLS.Static.Server, SLS.Static.Database); String sql = "SELECT daysInterval FROM MODE"; SqlDataReader reader = con.executeReader(sql); while(reader.Read()) { if (SLS.Static.ID == 0) { if (Convert.ToInt32(txtDaysInterval.Text) == Convert.ToInt32(reader.GetInt32(0))) { er2.Visible = true; isValid = 1; } } else { if(daysInterval != Convert.ToInt32(txtDaysInterval.Text)) { if (Convert.ToInt32(txtDaysInterval.Text) == Convert.ToInt32(reader[0])) { er2.Visible = true; isValid = 1; } } } } } catch (Exception) { isValid = 1; er2.Visible = true; } return isValid; }
public void ProcessorComplete(TraceData data) { //throw new System.NotImplementedException(); if (statements.Count == 0) { return; } /* Group them all by Where */ var sqlByWhereList = data.SQLByWhere; var byWheres = statements.Where(p => p.Type != SQLType.INSERT).GroupBy(p => p.WhereClause).Select(g => new SQLByWhere { NumberOfCalls = g.Count(), TotalTime = g.Sum(i => i.Duration), WhereClause = g.Key, HasError = g.Count(p => p.IsError) > 0 ? true : false }); foreach (var byW in byWheres) { sqlByWhereList.Add(byW); } var sqlByFromList = data.SQLByFrom; var byFroms = statements.Where(p => p.Type == SQLType.SELECT || p.Type == SQLType.DELETE).GroupBy(p => p.FromClause).Select(g => new SQLByFrom { NumberOfCalls = g.Count(), TotalTime = g.Sum(i => i.Duration), FromClause = g.Key, HasError = g.Count(p => p.IsError) > 0 ? true : false }); foreach (var byF in byFroms) { sqlByFromList.Add(byF); } data.Statistics.Add(new StatisticItem() { Category = "SQL Statements", Label = "Total Count", Value = statements.Count.ToString() }); SQLStatement longest = statements.OrderBy(s => s.Duration).Reverse().First(); data.Statistics.Add(new StatisticItem() { Category = "SQL Statements", Label = "Longest Execution", Value = longest.Duration.ToString(), Tag = longest }); SQLStatement mostFetches = statements.OrderBy(s => s.FetchCount).Reverse().First(); data.Statistics.Add(new StatisticItem() { Category = "SQL Statements", Label = "Most Fetches", Value = mostFetches.FetchCount.ToString(), Tag = mostFetches }); }
public void defaultAll() { SQLStatement con = new SQLStatement(SLS.Static.Server, SLS.Static.Database); String sql = "SELECT FORMAT(MEMBER.MemberID,'00000000') as [ID], CONCAT(MEMBER.fName,' ',MEMBER.mName,' ',MEMBER.lName) as [Name], DATEDIFF(YEAR, MEMBER.birthDate, '" + (DateTime.Now).ToString("yyyy-MM-dd") + "' ) as [Age] , MEMBERTYPE.MemberTypeID, MEMBERTYPE.MemberTypeName FROM MEMBER, MEMBERTYPE WHERE MEMBER.MemberTypeID = MEMBERTYPE.MemberTypeID AND MEMBER.MemberID = " + SLS.Static.ID + " "; SqlDataReader reader = con.executeReader(sql); if (reader.HasRows) { reader.Read(); MemberID = Convert.ToInt32(reader[0]); txtMemberID.Text = "MEM - " + Convert.ToString(reader[0]); txtMemberName.Text = Convert.ToString(reader[1]); txtMemberType.Text = Convert.ToString(reader[4]); con = new SQLStatement(SLS.Static.Server, SLS.Static.Database); sql = "SELECT SAVINGSTYPE.SavingsTypeID, SAVINGSTYPE.savingsTypeName FROM SAVINGSTYPE, APPLICABLESAVINGS WHERE SAVINGSTYPE.SavingsTypeID = APPLICABLESAVINGS.SavingsTypeID AND APPLICABLESAVINGS.MemberTypeID = " + Convert.ToString(reader[3]) + " and SAVINGSTYPE.hasDormancy = 'true' and SAVINGSTYPE.[status] = 1"; reader = con.executeReader(sql); cobSavingsType.Items.Clear(); int i = 0; while (reader.Read()) { string str = reader[1].ToString(); cobSavingsType.Items.Insert(i, "" + str); i += 1; } cobSavingsType.Text = ""; cobSavingsType.Enabled = true; er2.Visible = true; SavingsID = 0; } con = new SQLStatement(SLS.Static.Server, SLS.Static.Database); sql = "SELECT MAX(SavingsAccountID) FROM SAVINGSACCOUNT"; reader = con.executeReader(sql); try { if (reader.HasRows) { reader.Read(); txtSavAccount.Text = "SAV - " + (reader.GetInt32(0) + 1).ToString("00000000"); } } catch(Exception) { txtSavAccount.Text = "SAV - 00000001"; } txtDate.Text = DateTime.Now.ToLongDateString(); }
public void defaultAll() { txtNoDays.Clear(); er1.Visible = false; if (SLS.Static.ID != 0) { SQLStatement con = new SQLStatement(SLS.Static.Server, SLS.Static.Database); String sql = "SELECT noOfDays FROM TERM where TermID = " + SLS.Static.ID + " "; SqlDataReader reader = con.executeReader(sql); if (reader.HasRows) { reader.Read(); txtNoDays.Text = Convert.ToString(reader[0]); NoDays = Convert.ToInt32(reader[0]); } } }
public string SQLQueryVerify(string sql_dir_fn, string write_dir_fn, string expect_dir_fn, string[] badQueries) { QueryOption option = new QueryOption(); option.optimize_.TurnOnAllOptimizations(); option.optimize_.remove_from_ = false; option.explain_.show_output_ = true; option.explain_.show_cost_ = option.optimize_.use_memo_; // get a list of sql query fine names from the sql directory string[] sqlFiles = Directory.GetFiles(sql_dir_fn); // execute the query in each file and and verify the result foreach (string sqlFn in sqlFiles) { string dbg_name = Path.GetFileNameWithoutExtension(sqlFn); if (badQueries.Contains(dbg_name) == true) { continue; } // execute query var sql = File.ReadAllText(sqlFn); var test_result = SQLStatement.ExecSQLList(sql, option); // construct file name for result file and write result string f_name = Path.GetFileNameWithoutExtension(sqlFn); string write_fn = $@"{write_dir_fn}\{f_name}.txt"; File.WriteAllText(write_fn, test_result); // construct file name of expected result string expect_fn = $@"{expect_dir_fn}\{f_name}.txt"; // verify query result against the expected result if (!resultVerify(write_fn, expect_fn)) { return(write_fn); } } return(null); }
static void TestDataSet2() { Random rand = new Random(); int inside(int d) { var x = rand.NextDouble(); var y = rand.NextDouble(); var ret = x * x + y * y <= 1 ? 1 : 0; return(ret); } SQLContext sqlContext = new SQLContext(); SQLContext.Register <int, int>("inside", inside); var sql = "SELECT 4.0*sum(inside(a1.a1))/count(*) from a a1, a a2, a a3, a a4, a a5, a a6, a a7, a a8, a a9, a a10"; var rows = SQLStatement.ExecSQL(sql, out string plan, out _); }
static void TestDataSet() { // register c#'s sqrt as an external function string sqroot(double d) => Math.Sqrt(d).ToString("#.###"); SQLContext sqlContext = new SQLContext(); SQLContext.Register <double, string>("sqroot", sqroot); var a = sqlContext.Read("a"); var b = sqlContext.Read("b"); a.filter("a1>1").join(b, "b2=a2").select("a1", "sqroot(b1*a1+2)").show(); string s = a.physicPlan_.Explain(); Console.WriteLine(s); var sql = "SELECT a1, sqroot(b1*a1+2) from a join b on b2=a2 where a1>1"; var rows = SQLStatement.ExecSQL(sql, out string plan, out _); }
public void defaultAll() { SQLStatement con = new SQLStatement(SLS.Static.Server, SLS.Static.Database); String sql = "SELECT FORMAT(MEMBER.MemberID,'00000000') as [ID], CONCAT(MEMBER.fName,' ',MEMBER.mName,' ',MEMBER.lName) as [Name], DATEDIFF(YEAR, MEMBER.birthDate, '" + (DateTime.Now).ToString("yyyy-MM-dd") + "' ) as [Age], MEMBERTYPE.MemberTypeName as [Member Type] FROM MEMBER, MEMBERTYPE WHERE MEMBER.MemberTypeID = MEMBERTYPE.MemberTypeID AND MEMBER.MemberID = " + SLS.Static.ID + " "; SqlDataReader reader = con.executeReader(sql); if (reader.HasRows) { reader.Read(); MemberID = Convert.ToInt32(reader[0]); txtMemberID.Text = "MEM - " + Convert.ToString(reader[0]); txtMemberName.Text = Convert.ToString(reader[1]); txtMemberType.Text = Convert.ToString(reader[3]); } con = new SQLStatement(SLS.Static.Server, SLS.Static.Database); sql = "SELECT DISTINCT(TIMEDEPOSITRATES.noOfDays) FROM MEMBERTYPE, TIMEDEPOSITRATES WHERE MEMBERTYPE.TimeApplicable = 1 AND TIMEDEPOSITRATES.[status] = 1"; reader = con.executeReader(sql); cobNoOfDays.Items.Clear(); int i = 0; while(reader.Read()) { string str = reader[0].ToString(); cobNoOfDays.Items.Insert(i, "" + str + " Days"); i += 1; } cobNoOfDays.Text = ""; cobNoOfDays.Enabled = true; er1.Visible = true; try { if (reader.HasRows) { reader.Read(); txtTimeAccount.Text = "TIME - " + (reader.GetInt32(0) + 1).ToString("00000000"); } } catch (Exception) { txtTimeAccount.Text = "TIME - 00000001"; } txtNow.Text = Convert.ToString(DateTime.Now.ToLongDateString()); }
private void btnStatus_Click(object sender, EventArgs e) { if (btnStatus.Text == "DELETE") { SQLStatement con = new SQLStatement(SLS.Static.Server, SLS.Static.Database); String sql = "UPDATE TIMEDEPOSITRATES SET [status] = @status WHERE TimeDepositRatesID = @TimeDepositRatesID"; Dictionary<String, Object> parameters = new Dictionary<string, object>(); parameters.Add("@status", false); parameters.Add("@TimeDepositRatesID", SLS.Static.ID); int result = Convert.ToInt32(con.executeNonQuery(sql, parameters)); if (result == 1) { MessageBox.Show("Updating a time deposit rate is successful.", "Saved", MessageBoxButtons.OK, MessageBoxIcon.Information); SLS.Static.ID = 0; loadDatabase(); } else { MessageBox.Show("Updating a time deposit rate is not successful.", "Error", MessageBoxButtons.OK, MessageBoxIcon.Error); } } else { SQLStatement con = new SQLStatement(SLS.Static.Server, SLS.Static.Database); String sql = "UPDATE TIMEDEPOSITRATES SET [status] = @status WHERE TimeDepositRatesID = @TimeDepositRatesID"; Dictionary<String, Object> parameters = new Dictionary<string, object>(); parameters.Add("@status", true); parameters.Add("@TimeDepositRatesID", SLS.Static.ID); int result = Convert.ToInt32(con.executeNonQuery(sql, parameters)); if (result == 1) { MessageBox.Show("Updating a time deposit rate is successful.", "Saved", MessageBoxButtons.OK, MessageBoxIcon.Information); SLS.Static.ID = 0; loadDatabase(); } else { MessageBox.Show("Updating a time deposit rate is not successful.", "Error", MessageBoxButtons.OK, MessageBoxIcon.Error); } } }
static void TestJobench() { var files = Directory.GetFiles(@"../../../jobench"); JOBench.CreateTables(); // make sure all queries can generate phase one opt plan QueryOption option = new QueryOption(); option.optimize_.TurnOnAllOptimizations(); option.optimize_.memo_use_joinorder_solver_ = true; foreach (var v in files) { var sql = File.ReadAllText(v); var result = SQLStatement.ExecSQL(sql, out string phyplan, out _, option); Debug.Assert(result != null); Debug.Assert(phyplan != null); Console.WriteLine(v); Console.WriteLine(phyplan); } }
private void btnSave_Click(object sender, EventArgs e) { if (SLS.Static.ID == 0) { if (checkValues() == 0) { SQLStatement con = new SQLStatement(SLS.Static.Server, SLS.Static.Database); String sql = "INSERT INTO CREDITINVESTIGATOR (fName, mName, lName, [status]) VALUES (@fName, @mName, @lName, @status)"; Dictionary<String, Object> parameters = new Dictionary<string, object>(); parameters.Add("@fName", txtFN.Text); parameters.Add("@mName", txtMN.Text); parameters.Add("@lName", txtLN.Text); parameters.Add("@status", true); int result = Convert.ToInt32(con.executeNonQuery(sql, parameters)); if (result == 1) { MessageBox.Show("Adding a credit investigator is successful.", "Saved", MessageBoxButtons.OK, MessageBoxIcon.Information); this.Close(); } else { MessageBox.Show("Adding a credit investigator is not successful.", "Error", MessageBoxButtons.OK); } } else { MessageBox.Show("Some required fields are missing/invalid!", "Error"); } } else { if (checkValues() == 0) { SQLStatement con = new SQLStatement(SLS.Static.Server, SLS.Static.Database); String sql = "UPDATE CREDITINVESTIGATOR SET fName = @fName, mName = @mName, lName = @lName WHERE ciID = " + SLS.Static.ID + " "; Dictionary<String, Object> parameters = new Dictionary<string, object>(); parameters.Add("@fName", txtFN.Text); parameters.Add("@mName", txtMN.Text); parameters.Add("@lName", txtLN.Text); int result = Convert.ToInt32(con.executeNonQuery(sql, parameters)); if (result == 1) { MessageBox.Show("Updating a credit investigator is successful.", "Saved", MessageBoxButtons.OK, MessageBoxIcon.Information); defaultAll(); this.Close(); } else { MessageBox.Show("Updating a credit investigator is not successful.", "Error", MessageBoxButtons.OK, MessageBoxIcon.Error); } } else { MessageBox.Show("Some required fields are missing/invalid!", "Error"); } } }
public void fillTable() { if (SLS.Static.ID == 0) { try { table.Columns.Add("ChargeID"); table.Columns.Add("Charges"); foreach (object modeChecked in cListMode.CheckedItems) { foreach (object termChecked in cListTerm.CheckedItems) { table.Columns.Add(new DataColumn("" + modeChecked.ToString() + " - " + termChecked.ToString(), typeof(Decimal))); } } } catch { } try { DataRow Row = table.NewRow(); Row["ChargeID"] = 1; Row["Charges"] = "Interest On Loan"; table.Rows.Add(Row); foreach (object chargeChecked in cListCharges.CheckedItems) { SQLStatement con = new SQLStatement(SLS.Static.Server, SLS.Static.Database); String sql = "SELECT ChargeID, chargeName FROM CHARGES WHERE chargeName = @chargeName"; Dictionary<String, Object> parameters = new Dictionary<string, object>(); parameters.Add("@chargeName", chargeChecked.ToString()); SqlDataReader reader = con.executeReader(sql, parameters); if (reader.HasRows) { reader.Read(); Row = table.NewRow(); Row["ChargeID"] = reader[0].ToString(); Row["Charges"] = reader[1].ToString(); table.Rows.Add(Row); } } dataGridView1.DataSource = table; dataGridView1.Columns[0].Visible = false; } catch { } } else { } }
public void defaultAll() { txtLoanType.Clear(); ckbMemType.Checked = false; cListMemType.Items.Clear(); SQLStatement con = new SQLStatement(SLS.Static.Server, SLS.Static.Database); String sql = "SELECT memberTypeName FROM MEMBERTYPE where LoanApplicable = 1 and [status] = 1 ORDER BY membertypename"; SqlDataReader reader = con.executeReader(sql); int i = 0; while(reader.Read()) { string str = reader[0].ToString(); cListMemType.Items.Insert(i, "" + str); i += 1; } txtMinAmount.Clear(); txtMaxAmount.Clear(); txtNoOfComaker.Clear(); txtEntitlement.Clear(); txtEligibility.Clear(); ckbMode.Checked = false; cListMode.Items.Clear(); con = new SQLStatement(SLS.Static.Server, SLS.Static.Database); sql = "SELECT modeName FROM MODE where [status] = 1 ORDER BY daysInterval"; reader = con.executeReader(sql); i = 0; while (reader.Read()) { cListMode.Items.Insert(i, "" + Convert.ToString(reader.GetString(0))); i += 1; } ckbTerm.Checked = false; cListTerm.Items.Clear(); con = new SQLStatement(SLS.Static.Server, SLS.Static.Database); sql = "SELECT noOfDays FROM TERM where [status] = 1 ORDER BY noOfDays"; reader = con.executeReader(sql); i = 0; while (reader.Read()) { cListTerm.Items.Insert(i, "" + Convert.ToString(reader.GetInt32(0))); i += 1; } ckbCharges.Checked = false; cListCharges.Items.Clear(); con = new SQLStatement(SLS.Static.Server, SLS.Static.Database); sql = "SELECT chargeName FROM CHARGES where [status] = 1 ORDER BY chargeName"; reader = con.executeReader(sql); i = 0; while (reader.Read()) { if (reader.GetString(0) != "Interest On Loan") { cListCharges.Items.Insert(i, "" + Convert.ToString(reader.GetString(0))); i += 1; } } if(SLS.Static.ID != 0) { con = new SQLStatement(SLS.Static.Server, SLS.Static.Database); sql = "SELECT LoanTypeID as [ID], loanTypeName as [Loan Type], minAmount as [Minimum Amount], maxAmount as [Maximum Amount], noOfComaker as [No. of Co-maker], entitlement as [Entitlement], eligibility as [Eligibility], [status] as [Status] FROM LOANTYPE WHERE LoanTypeID = " + SLS.Static.ID + " "; reader = con.executeReader(sql); if (reader.HasRows) { reader.Read(); txtLoanType.Text = Convert.ToString(reader[1]); txtMinAmount.Text = Convert.ToString(reader[2]); txtMaxAmount.Text = Convert.ToString(reader[3]); txtNoOfComaker.Text = Convert.ToString(reader[4]); txtEntitlement.Text = Convert.ToString(reader[5]); txtEligibility.Text = Convert.ToString(reader[6]); } con = new SQLStatement(SLS.Static.Server, SLS.Static.Database); sql = "SELECT MEMBERTYPE.memberTypeName FROM MEMBERTYPE, APPLICABLELOAN WHERE MEMBERTYPE.MemberTypeID = APPLICABLELOAN.MemberTypeID and APPLICABLELOAN.LoanTypeID = @LoanTypeID"; Dictionary<String, Object> parameters = new Dictionary<string, object>(); parameters.Add("@LoanTypeID", SLS.Static.ID); reader = con.executeReader(sql, parameters); for (i = 0; i < cListMemType.Items.Count; i++) { cListMemType.SetItemChecked(i, false); } while (reader.Read()) { int index = cListMemType.Items.IndexOf(reader.GetString(0)); cListMemType.SetItemChecked(index, true); } con = new SQLStatement(SLS.Static.Server, SLS.Static.Database); sql = "SELECT DISTINCT(MODE.modeName) FROM MODE, LOANRATE, LOANTYPE WHERE MODE.ModeID = LOANRATE.ModeID and LOANRATE.LoanTypeID = LOANTYPE.LoanTypeID and LOANTYPE.LoanTypeID = @LoanTypeID"; parameters = new Dictionary<string, object>(); parameters.Add("@LoanTypeID", SLS.Static.ID); reader = con.executeReader(sql, parameters); for (i = 0; i < cListMode.Items.Count; i++) { cListMode.SetItemChecked(i, false); } while (reader.Read()) { int index = cListMode.Items.IndexOf(reader.GetString(0)); cListMode.SetItemChecked(index, true); } con = new SQLStatement(SLS.Static.Server, SLS.Static.Database); sql = "SELECT DISTINCT(TERM.noOfDays) FROM TERM, LOANRATE, LOANTYPE WHERE TERM.TermID = LOANRATE.TermID and LOANRATE.LoanTypeID = LOANTYPE.LoanTypeID and LOANTYPE.LoanTypeID = @LoanTypeID"; parameters = new Dictionary<string, object>(); parameters.Add("@LoanTypeID", SLS.Static.ID); reader = con.executeReader(sql, parameters); for (i = 0; i < cListTerm.Items.Count; i++) { cListTerm.SetItemChecked(i, false); } while (reader.Read()) { int index = cListTerm.Items.IndexOf(Convert.ToString(reader.GetInt32(0))); cListTerm.SetItemChecked(index, true); } con = new SQLStatement(SLS.Static.Server, SLS.Static.Database); sql = "SELECT DISTINCT(CHARGES.chargeName) FROM CHARGES, LOANRATE, LOANTYPE WHERE CHARGES.ChargeID = LOANRATE.ChargeID and LOANRATE.LoanTypeID = LOANTYPE.LoanTypeID and LOANTYPE.LoanTypeID = @LoanTypeID"; parameters = new Dictionary<string, object>(); parameters.Add("@LoanTypeID", SLS.Static.ID); reader = con.executeReader(sql, parameters); for (i = 0; i < cListCharges.Items.Count; i++) { cListCharges.SetItemChecked(i, false); } while (reader.Read()) { if (reader.GetString(0) != "Interest On Loan") { int index = cListCharges.Items.IndexOf(reader.GetString(0)); cListCharges.SetItemChecked(index, true); } } } }
//By Lhy 20110505 ITEM 6 ADD END //By ryzhang 20110516 ITEM 6 ADD Start /// <summary> /// set value in dropdownlist /// </summary> /// <param name="falg">1、region,2、cluster,3、Country</param> private void SetParent(int falg) { String sql = null; //BY RYZHANG ITEM6 20110525 MODIFY START switch (falg) { //case 1: // sql = "SELECT distinct ID, Name FROM [Region] WHERE Deleted = 0"; // break; //case 2: // sql = "SELECT distinct ID, Name FROM [Cluster] WHERE Deleted = 0"; // break; //case 3: // sql = "SELECT distinct ID, Name FROM [Country] WHERE Deleted = 0"; // break; //default: // return; case 1: sql = "SELECT distinct ID, Name FROM [Region] WHERE Deleted = 0 ORDER BY NAME"; break; case 2: sql = "SELECT distinct ID, Name FROM [Cluster] WHERE Deleted = 0 ORDER BY NAME"; break; case 3: sql = "SELECT distinct ID, Name FROM [Country] WHERE Deleted = 0 ORDER BY NAME"; break; default: return; } //BY RYZHANG ITEM6 20110525 MODIFY END DataSet ds = helper.GetDataSet(sql); ddlist_parent.Items.Clear(); if (ds.Tables.Count!=0) { foreach (DataRow row in ds.Tables[0].Rows) { ddlist_parent.Items.Add(new ListItem(row[1].ToString(), row[0].ToString())); } } }
private void btnSave_Click(object sender, EventArgs e) { if(SLS.Static.ID == 0) { if(checkValues() == 1) { MessageBox.Show("Some required fields are missing or invalid.", "Error"); } else { SQLStatement con = new SQLStatement(SLS.Static.Server, SLS.Static.Database); String sql = "INSERT INTO LOANTYPE (loanTypeName, minAmount, maxAmount, noOfComaker, entitlement, eligibility, [status]) VALUES (@loanTypeName, @minAmount, @maxAmount, @noOfComaker, @entitlement, @eligibility, @status); SELECT CAST(scope_identity() AS int)"; Dictionary<String, Object> parameters = new Dictionary<string, object>(); parameters.Add("@loanTypeName", txtLoanType.Text); parameters.Add("@minAmount", txtMinAmount.Text); parameters.Add("@maxAmount", txtMaxAmount.Text); parameters.Add("@noOfComaker", txtNoOfComaker.Text); parameters.Add("@entitlement", txtEntitlement.Text); parameters.Add("@eligibility", txtEligibility.Text); parameters.Add("@status", true); SqlDataReader reader = con.executeReader(sql, parameters); if (reader.HasRows) { reader.Read(); MessageBox.Show("Adding a loan type is successful.", "Saved", MessageBoxButtons.OK, MessageBoxIcon.Information); foreach (object itemChecked in cListMemType.CheckedItems) { con = new SQLStatement(SLS.Static.Server, SLS.Static.Database); sql = "INSERT INTO APPLICABLELOAN (LoanTypeID, MemberTypeID) VALUES (@LoanTypeID,(SELECT MemberTypeID FROM MEMBERTYPE WHERE memberTypeName = @memberTypeName))"; parameters = new Dictionary<string, object>(); parameters.Add("@LoanTypeID", reader[0]); parameters.Add("@memberTypeName", itemChecked.ToString()); int result = Convert.ToInt32(con.executeNonQuery(sql, parameters)); } modeArr = new String[cListMode.Items.Count]; int sInd = 0; for (int i = 0; i < cListMode.Items.Count; i++) { if (cListMode.GetItemCheckState(i) == CheckState.Checked) { modeArr[sInd] = Convert.ToString(cListMode.Items[i]); sInd++; } } termArr = new Int32[cListTerm.Items.Count]; sInd = 0; for (int i = 0; i < cListTerm.Items.Count; i++) { if (cListTerm.GetItemCheckState(i) == CheckState.Checked) { termArr[sInd] = Convert.ToInt32(cListTerm.Items[i].ToString()); sInd++; } } for (int j = 0; j <= dataGridView1.Rows.Count - 1; j++) { for (int k = 2; k <= dataGridView1.Columns.Count - 1; k++) { con = new SQLStatement(SLS.Static.Server, SLS.Static.Database); sql = "INSERT INTO LOANRATE (ModeID, ChargeID, TermID, LoanTypeID, rate) VALUES ((SELECT ModeID FROM MODE WHERE modeName = @modeName AND [status] = 1), @ChargeID, (SELECT TermID FROM TERM WHERE noOfDays = @noOfDays AND [status] = 1), @LoanTypeID, @rate)"; parameters = new Dictionary<string, object>(); parameters.Add("@modeName", modeArr[(k - 2) / cListTerm.CheckedItems.Count]); parameters.Add("@ChargeID", Convert.ToInt32(dataGridView1.Rows[j].Cells[0].Value.ToString())); parameters.Add("@noOfDays", termArr[(k - 2) / cListMode.CheckedItems.Count]); parameters.Add("@LoanTypeID", reader[0]); parameters.Add("@rate", Convert.ToDecimal(dataGridView1.Rows[j].Cells[k].Value.ToString())); int result = Convert.ToInt32(con.executeNonQuery(sql, parameters)); } } this.Close(); } else { MessageBox.Show("Adding a loan type is not successful.", "Error", MessageBoxButtons.OK); } } } }
private void delSubRegion(string id, string name) { //By Lhy 20110513 ITEM 6 ADD Start null_input(); //By Lhy 20110513 ITEM 6 ADD Start string sql = "UPDATE [SubRegion] SET Deleted = 1 WHERE ID = " + id; int count = helper.ExecuteNonQuery(CommandType.Text, sql, null); if (count == 1) { //By ryzhang 20110516 ITEM 6 ADD Start sql = "UPDATE [Country_SubRegion] SET Deleted = 1 WHERE SubRegionID = " + id; helper.ExecuteNonQuery(CommandType.Text, sql, null); //By ryzhang 20110516 ITEM 6 ADD End lbl_del.ForeColor = System.Drawing.Color.Green; lbl_del.Text = info.delLabelInfo(name, true); } else { lbl_del.ForeColor = System.Drawing.Color.Red; lbl_del.Text = info.delLabelInfo(name, false); } }
private void addSubRegion(string name) { //By Lhy 20110513 ITEM 6 ADD Start null_input(); //By Lhy 20110513 ITEM 6 ADD Start lbl_add.ForeColor = System.Drawing.Color.Red; if (name != "") { if (!existSubRegion(name)) { string insert_subregion = "INSERT INTO [SubRegion](Name,Deleted) VALUES('" + name + "',0)"; int count = helper.ExecuteNonQuery(CommandType.Text, insert_subregion, null); if (count == 1) { //By ryzhang 20110516 ITEM 6 ADD Start string sql = String.Format(@"SELECT ID FROM [SubRegion] WHERE Name = '{0}' AND Deleted = 0", name); DataSet ds = helper.GetDataSet(sql); if (ds.Tables.Count == 0 || ds.Tables[0].Rows.Count == 0) { return; } sql = String.Format(@"INSERT INTO [Country_SubRegion] (CountryID,SubRegionID,Deleted) VALUES ({0},{1},0)", ddlist_parent.SelectedItem.Value, ds.Tables[0].Rows[0][0].ToString()); helper.ExecuteNonQuery(CommandType.Text, sql, null); //By ryzhang 20110516 ITEM 6 ADD End lbl_add.ForeColor = System.Drawing.Color.Green; lbl_add.Text = info.addLabelInfo(name, true); } else lbl_add.Text = info.addLabelInfo(name, false); } else lbl_add.Text = info.addExist(name); } else lbl_add.Text = info.addillegal(); }
//By Lhy 20110505 ITEM 6 DEL Start /*protected DataSet getOptionInfo(int sel) { string sql = ""; DataSet ds = null; if (sel == 0) { sql = "SELECT ID, Name FROM [Region] WHERE Deleted = 0 GROUP BY Name, ID ORDER BY Name, ID ASC"; ds = helper.GetDataSet(sql); } else if (sel == 1) { sql = " SELECT ID, Name FROM [Cluster] WHERE Deleted = 0 GROUP BY Name, ID ORDER BY Name, ID ASC"; ds = helper.GetDataSet(sql); } else if (sel == 2) { sql = " SELECT ID, Name, ISO_Code FROM [Country] WHERE Deleted = 0 GROUP BY Name, ISO_Code, ID ORDER BY Name, ISO_Code, ID ASC"; ds = helper.GetDataSet(sql); } else if (sel == 3) { sql = " SELECT ID, Name FROM [SubRegion] WHERE Deleted = 0 GROUP BY Name, ID ORDER BY Name, ID ASC"; ds = helper.GetDataSet(sql); } return ds; } */ //By Lhy 20110505 ITEM 6 DEL Start //By Lhy 20110505 ITEM 6 ADD Start protected DataSet getOptionInfo(int sel, string text) { string sql = ""; DataSet ds = null; if (sel == 0) { sql = "SELECT ID, Name FROM [Region] WHERE Deleted = 0 AND Name like '%" + text + "%' GROUP BY Name, ID ORDER BY Name, ID ASC "; ds = helper.GetDataSet(sql); } else if (sel == 1) { sql = " SELECT ID, Name FROM [Cluster] WHERE Deleted = 0 AND Name like '%" + text + "%'GROUP BY Name, ID ORDER BY Name, ID ASC"; ds = helper.GetDataSet(sql); } else if (sel == 2) { sql = " SELECT ID, Name, ISO_Code FROM [Country] WHERE Deleted = 0 AND Name like '%" + text + "%'GROUP BY Name, ISO_Code, ID ORDER BY Name, ISO_Code, ID ASC"; ds = helper.GetDataSet(sql); } else if (sel == 3) { sql = " SELECT ID, Name FROM [SubRegion] WHERE Deleted = 0 AND Name like '%" + text + "%'GROUP BY Name, ID ORDER BY Name, ID ASC"; ds = helper.GetDataSet(sql); } return ds; }
protected void btn_modify_parent_Click(object sender, EventArgs e) { this.lbl_del.Text = ""; lbl_modify_parent_error.Text = ""; if (ddlist_modify_parent.SelectedItem.Value=="") { lbl_modify_parent_error.ForeColor = System.Drawing.Color.Red; switch (this.optionName.Value) { case "Cluster": lbl_modify_parent_error.Text = "Please select one Region!"; break; case "Country": lbl_modify_parent_error.Text = "Please select one Cluster!"; break; case "SubRegion": lbl_modify_parent_error.Text = "Please select one Country!"; break; default: break; } } else { String sql = null; switch (this.optionName.Value) { case "Cluster": sql = String.Format(@"UPDATE [Region_Cluster] SET RegionID = {0},Deleted=0 WHERE ClusterID={1}", ddlist_modify_parent.SelectedItem.Value, this.modify_option_id.Value); break; case "Country": sql = String.Format(@"UPDATE [Cluster_Country] SET ClusterID = {0},Deleted=0 WHERE CountryID={1}", ddlist_modify_parent.SelectedItem.Value, this.modify_option_id.Value); break; case "SubRegion": sql = String.Format(@"UPDATE [Country_SubRegion] SET CountryID = {0},Deleted=0 WHERE SubRegionID={1}", ddlist_modify_parent.SelectedItem.Value, this.modify_option_id.Value); break; default: break; } helper.ExecuteNonQuery(CommandType.Text, sql, null); lbl_modify_parent_error.ForeColor = System.Drawing.Color.Green; lbl_modify_parent_error.Text = @"Successfully modified!"; } }
protected DataSet getBookingDataByCountry(string str_ClusterID, string str_segmentID) { /////////////MONTH 10月份吗 string sql = ""; if (str_segmentID != "-1") { sql = "SELECT [Country].Name, " + " SUM(CASE WHEN BookingY = '" + yearBeforePre.Substring(2, 2) + "' and YEAR(TimeFlag) = '" + yearBeforePre + "' and MONTH(TimeFlag) = '10' THEN Amount ELSE 0 END) AS 'FY " + yearBeforePre + "'," + " SUM(CASE WHEN BookingY = '" + preyear.Substring(2, 2) + "' and YEAR(TimeFlag) = '" + preyear + "' and MONTH(TimeFlag) = '10' THEN Amount ELSE 0 END) AS 'FY " + preyear + "'," + " SUM(CASE WHEN BookingY = '" + year.Substring(2, 2) + "' and YEAR(TimeFlag) = '" + year + "' and MONTH(TimeFlag) = '10' THEN Amount ELSE 0 END) AS 'FY " + year + "'," + " SUM(CASE WHEN BookingY = '" + nextyear.Substring(2, 2) + "' and YEAR(TimeFlag) = '" + year + "' and MONTH(TimeFlag) = '10' THEN Amount ELSE 0 END) AS 'FY " + nextyear + "' " + " FROM [Bookings]" + " INNER JOIN [Country] ON [Country].ID = [Bookings].CountryID" + " WHERE CountryID IN (SELECT CountryID FROM [Cluster_Country] WHERE ClusterID = " + str_ClusterID + " AND Deleted=0)" + " AND SegmentID = " + str_segmentID + " AND [Country].Deleted=0 " + " GROUP BY [Country].Name" + " ORDER BY [Country].Name"; } else { sql = "SELECT [Country].Name, " + " SUM(CASE WHEN BookingY = '" + yearBeforePre.Substring(2, 2) + "' and YEAR(TimeFlag) = '" + yearBeforePre + "' and MONTH(TimeFlag) = '10' THEN Amount ELSE 0 END) AS 'FY " + yearBeforePre + "'," + " SUM(CASE WHEN BookingY = '" + preyear.Substring(2, 2) + "' and YEAR(TimeFlag) = '" + preyear + "' and MONTH(TimeFlag) = '10' THEN Amount ELSE 0 END) AS 'FY " + preyear + "'," + " SUM(CASE WHEN BookingY = '" + year.Substring(2, 2) + "' and YEAR(TimeFlag) = '" + year + "' and MONTH(TimeFlag) = '10' THEN Amount ELSE 0 END) AS 'FY " + year + "'," + " SUM(CASE WHEN BookingY = '" + nextyear.Substring(2, 2) + "' and YEAR(TimeFlag) = '" + year + "' and MONTH(TimeFlag) = '10' THEN Amount ELSE 0 END) AS 'FY " + nextyear + "' " + " FROM [Bookings]" + " INNER JOIN [Country] ON [Country].ID = [Bookings].CountryID" + " WHERE CountryID IN (SELECT CountryID FROM [Cluster_Country] WHERE ClusterID = " + str_ClusterID + " AND Deleted=0)" + " AND [Country].Deleted=0 " + " GROUP BY [Country].Name" + " ORDER BY [Country].Name"; } DataSet ds = helper.GetDataSet(sql); if (ds.Tables[0].Rows.Count > 0) return ds; else return null; }