public int Executar(String sql, Object parametros) { using (DbAccessHelper conexao = ObterConexao()) { return(conexao.Execute(Formatar(sql, parametros), parametros)); } }
protected void btnSearch_Click(object sender, ImageClickEventArgs e) { DbAccessHelper database = new DbAccessHelper("InnoluxDB"); this.lsbAvaliableDefCode.DataSource = database.ExecuteDataSet(string.Format("SELECT DEFECT_CODE, DEFECT_CAT || ':' || DEFECT_CODE AS DEF_GROUP FROM {0} WHERE DEFECT_CODE LIKE '{1}%'", this.DBTableName, this.txtSearch.Text)); this.lsbAvaliableDefCode.DataTextField = "DEFECT_CODE"; this.lsbAvaliableDefCode.DataValueField = "DEF_GROUP"; this.lsbAvaliableDefCode.DataBind(); }
protected void Page_Load(object sender, EventArgs e) { if (!this.IsPostBack) { DbAccessHelper database = new DbAccessHelper("InnoluxDB"); string shiftDate = database.ExecuteScalar("SELECT T.SHIFT_DATE FROM SHIFT_DATE T WHERE SYSDATE BETWEEN T.CALSTARTDATE AND T.CALENDDATE").ToString(); string year = shiftDate.Substring(0, 4); string month = shiftDate.Substring(4, 2); string day = shiftDate.Substring(6, 2); this.EndDatePicker.SelectedDate = Convert.ToDateTime(year + "-" + month + "-" + day); this.StartDatePicker.SelectedDate = Convert.ToDateTime(year + "-" + month + "-1").AddDays(-3); } }
private void DataBindWeekDdl(DropDownList ddl, string year, bool startOrEnd) { DbAccessHelper database = new DbAccessHelper("InnoluxDB"); string fmSql = @"SELECT TO_CHAR(T.TD_WEEK, '00') as TD_WEEK, MIN(T.SHIFT_DATE) START_DATE, MAX(T.SHIFT_DATE) END_DATE FROM SHIFT_DATE T WHERE T.YEAR = {0} AND T.TD_WEEK IS NOT NULL GROUP BY T.TD_WEEK ORDER BY TO_CHAR(T.TD_WEEK, '00')" ; ddl.DataTextField = "TD_WEEK"; ddl.DataValueField = startOrEnd ? "START_DATE" : "END_DATE"; ddl.DataSource = database.ExecuteDataSet(string.Format(fmSql, year)).Tables[0]; ddl.DataBind(); }
protected void Page_Load(object sender, EventArgs e) { if (!this.IsPostBack) { DbAccessHelper database = new DbAccessHelper("InnoluxDB"); this.lsbDefGroup.DataSource = database.ExecuteDataSet(string.Format("SELECT DISTINCT DEFECT_CAT FROM {0} WHERE DEFECT_CAT IS NOT NULL", this.DBTableName)); this.lsbDefGroup.DataTextField = "DEFECT_CAT"; this.lsbDefGroup.DataValueField = "DEFECT_CAT"; this.lsbDefGroup.DataBind(); this.RequiredFieldValidator1.ValidationGroup = this.ValidationGroup; this.btnFilter.Enabled = this.lsbDefGroup.Items.Count > 0; } }
private void DatabindDefaultValue() { #region SQL string sql = @" SELECT MIN(X.SHIFT_DATE) AS START_DT, MAX(Y.MIN_YR) AS START_YR, MAX(Y.MIN_WK) AS START_WK, MAX(X.SHIFT_DATE) AS END_DT, MAX(Y.MAX_YR) AS END_YR, MAX(Y.MAX_WK) AS END_WK, MIN(Z.MIN_YR) AS MIN_YR, MAX(Z.MAX_YR) AS MAX_YR FROM SHIFT_DATE X, (SELECT MIN(YEAR) AS MIN_YR, MAX(TD_WEEK) - 2 AS MIN_WK, MAX(YEAR) AS MAX_YR, SUBSTR(MAX(YEAR || TD_WEEK)-1, 5, 2) AS MAX_WK FROM SHIFT_DATE T WHERE SYSDATE BETWEEN T.CALSTARTDATE AND ADD_MONTHS(T.CALENDDATE, 12) ORDER BY SHIFT_DATE) Y, (SELECT MAX(YEAR) AS MAX_YR, MIN(YEAR) AS MIN_YR FROM SHIFT_DATE) Z WHERE X.YEAR || X.TD_WEEK = Y.MIN_YR || Y.MIN_WK OR X.YEAR || X.TD_WEEK = Y.MAX_YR || Y.MAX_WK" ; #endregion DbAccessHelper database = new DbAccessHelper("InnoluxDB"); using (IDataReader reader = database.ExecuteReader(sql)) { if (reader.Read()) { int minYR = Utils.ConvertObject <int>(reader["MIN_YR"]); int maxYR = Utils.ConvertObject <int>(reader["MAX_YR"]); this.DataBindDdlYear(minYR, maxYR); this.ddlStartYear.Text = Utils.ConvertObject <string>(reader["START_YR"]); this.DataBindWeekDdl(this.ddlStartWeek, this.ddlStartYear.Text, true); this.ddlEndYear.Text = Utils.ConvertObject <string>(reader["END_YR"]); this.DataBindWeekDdl(this.ddlEndWeek, this.ddlEndYear.Text, false); this.ddlStartWeek.Text = Utils.ConvertObject <string>(reader["START_DT"]); this.ddlEndWeek.Text = Utils.ConvertObject <string>(reader["END_DT"]); } } }
protected void RadTreeView1_NodeExpand(object sender, RadTreeNodeEventArgs e) { string sql = ""; switch (e.Node.Level) { case 0: sql = "SELECT distinct PROD_MODEL as text FROM " + this.ProductTable + " WHERE PROD_MODEL IS NOT NULL AND PROD_SIZE =" + e.Node.Value + " and SHOP='" + this.ShopName + "'"; break; case 1: sql = "SELECT PROD_NAME as text FROM " + this.ProductTable + " WHERE ACTIVE_FLAG='1' AND PROD_SIZE=" + e.Node.ParentNode.Value + " AND PROD_MODEL=" + e.Node.Value + " and SHOP='" + this.ShopName + "'"; break; case 2: sql = "SELECT prod_name as text FROM " + this.ProductTable + " WHERE prod_name = '{0}' UNION SELECT SUBPROD_NAME AS prod_name FROM " + this.SubProductTable + " T WHERE T.PROD_NAME = " + e.Node.Value; break; default: return; } DbAccessHelper dbHelper = new DbAccessHelper(this.DBConnectionName); foreach (DataRow row in dbHelper.ExecuteDataSet(sql).Tables[0].Rows) { RadTreeNode node = new RadTreeNode(); node.Text = row["text"].ToString(); node.Value = "'" + row["text"].ToString() + "'"; if (e.Node.Level < 2) { node.ExpandMode = TreeNodeExpandMode.ServerSideCallBack; } e.Node.Nodes.Add(node); } e.Node.Expanded = true; }
private void SetDefaultValue() { string sql = @" with X as (select max(to_number(year)) as max_yr, min(to_number(year)) as min_yr from shift_date ), Y as ( select max(to_number(td_week)) - 2 as start_wk, year as start_yr, max(to_number(td_week)) as start_max_wk from shift_date where (year + 1) = to_char(sysdate, 'yyyy') group by year ), Z as ( select year as end_yr, td_week as end_wk, shift_date as end_dt, m.end_max_wk as end_max_wk from shift_date, ( select max(to_number(td_week)) as end_max_wk from shift_date where year = to_char(sysdate, 'yyyy') ) m where sysdate between calstartdate and calenddate ) select Y.start_yr, Y.start_wk, Y.start_max_wk, Z.end_dt, Z.end_yr, Z.end_max_wk, Z.end_wk, X.max_yr, X.min_yr from X, Y, Z "; DbAccessHelper database = new DbAccessHelper("InnoluxDB"); DataTable dt = database.ExecuteDataSet(sql).Tables[0]; if (dt == null || dt.Rows.Count < 1) { return; } int minYR = Utils.ConvertObject <int>(dt.Rows[0]["MIN_YR"]); int maxYR = Utils.ConvertObject <int>(dt.Rows[0]["MAX_YR"]); int startMaxWeek = Utils.ConvertObject <int>(dt.Rows[0]["START_MAX_WK"]); int endMaxWeek = Utils.ConvertObject <int>(dt.Rows[0]["END_MAX_WK"]); int endDefaultWeek = Utils.ConvertObject <int>(dt.Rows[0]["END_WK"]); this.BindToComobox(this.ddlStartYear, minYR, maxYR, DateTime.Now.Year - 1); this.BindToComobox(this.ddlEndYear, minYR, maxYR, DateTime.Now.Year); this.BindToComobox(this.ddlStartWeek, 1, startMaxWeek, startMaxWeek - 2); this.BindToComobox(this.ddlEndWeek, 1, endMaxWeek, endDefaultWeek); }
protected void btnFilter_Click(object sender, EventArgs e) { string temp = ""; foreach (ListItem item in this.lsbDefGroup.Items) { if (item.Selected == true) { temp += ("'" + item.Value + "',"); } } if (!string.IsNullOrEmpty(temp)) { DbAccessHelper database = new DbAccessHelper("InnoluxDB"); temp = temp.Substring(0, temp.Length - 1); this.lsbAvaliableDefCode.DataSource = database.ExecuteDataSet(string.Format("SELECT DEFECT_CODE, DEFECT_CAT || ':' || DEFECT_CODE AS DEF_GROUP FROM {0} WHERE DEFECT_CAT IN ({1})", this.DBTableName, temp)); this.lsbAvaliableDefCode.DataTextField = "DEFECT_CODE"; this.lsbAvaliableDefCode.DataValueField = "DEF_GROUP"; this.lsbAvaliableDefCode.DataBind(); } }
private void DataBindAndSetDefault() { // Bind Year dropdownlist DbAccessHelper database = new DbAccessHelper("InnoluxDB"); DataTable source = database.ExecuteDataSet("SELECT DISTINCT YEAR FROM SHIFT_DATE ORDER BY YEAR").Tables[0]; this.ddlStartYear.DataTextField = "YEAR"; this.ddlStartYear.DataValueField = "YEAR"; this.ddlEndYear.DataTextField = "YEAR"; this.ddlEndYear.DataValueField = "YEAR"; this.ddlEndYear.DataSource = this.ddlStartYear.DataSource = source; this.ddlStartYear.DataBind(); this.ddlEndYear.DataBind(); // Set default value DateTime current = DateTime.Now; this.ddlStartYear.Text = current.AddYears(-1).Year.ToString(); this.ddlStartMonth.Text = "10"; this.ddlEndYear.Text = current.Year.ToString(); this.ddlEndMonth.Text = current.AddMonths(-1).Month.ToString(); }
private DataTable GetDataSource() { DbAccessHelper dbHelper = new DbAccessHelper(this.DBConnectionName); string sql = @"select prod_size, prod_model, t.prod_name, s.subprod_name from " + this.ProductTable + @" t, " + this.SubProductTable + @" s where t.fab_id ='Fab2' and t.prod_size is not null and t.shop='" + this.ShopName + @"' and t.prod_name = s.prod_name and T.active_flag = 1" + @"union select prod_size, prod_model, t.prod_name, t.prod_name as subprod_name from " + this.ProductTable + @" t where t.fab_id ='Fab2' and t.prod_size is not null and t.shop='" + this.ShopName + @"' and T.active_flag = 1 " + @"ORDER BY prod_size, prod_model, prod_name"; try { return(dbHelper.ExecuteDataSet(sql).Tables[0]); } catch (Exception ex) { throw new Exception(ex.Message); } }
private static void PrintRows(DataTableReader reader) { try { string connStr = "Data Source=(local);Initial Catalog=mydb;integrated security=sspi;Min Pool Size=10"; DbAccessHelper db = new DbAccessHelper(connStr); EventWaitHandle clearCount = new EventWaitHandle(false, EventResetMode.ManualReset); int count = 0; int maxCount = 2; for (int i = 0; i < maxCount; i++) { string sql = string.Format("insert into [AOH_SeckillingWinner] (userid,Category) " + " select ROW_NUMBER() over (order by newid()) ,'{0}' from (select 1 as r from sys.objects as a " + " cross join sys.objects as b) as c", i); Thread t = new Thread((p) => { // SqlConnection conn = db.GetOpenConnection(); Interlocked.Increment(ref count); clearCount.WaitOne(); //db.ExcuteNoQuery(conn, p.ToString()); Console.WriteLine("finish."); //conn.Close(); Interlocked. Decrement(ref count); }); t.Start(sql); } while (count < maxCount) { Thread.Sleep(500); } clearCount.Set(); //Console.WriteLine(); } catch (Exception ex) { Console.WriteLine(ex.Message); } }
public override object ExecuteScalar(IDbConnection connection, string cmdText, IList <IDbDataParameter> parameter) { return(DbAccessHelper.ExecuteScalar((OleDbConnection)connection, CommandType.Text, cmdText, parameter == null ? null : parameter.Cast <OleDbParameter>().ToArray())); }
public override int ExecuteNonQuery(IDbTransaction trans, string cmdText, IList <IDbDataParameter> parameter) { return(DbAccessHelper.ExecuteNonQuery(trans as OleDbTransaction, CommandType.Text, cmdText, parameter == null ? null : parameter.Cast <OleDbParameter>().ToArray()).ToInt()); }