Exemplo n.º 1
0
 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();
    }
Exemplo n.º 3
0
 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);
     }
 }
Exemplo n.º 4
0
    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;
        }
    }
Exemplo n.º 6
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"]);
            }
        }
    }
Exemplo n.º 7
0
    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;
    }
Exemplo n.º 8
0
    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();
        }
    }
Exemplo n.º 10
0
    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();
    }
Exemplo n.º 11
0
    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);
        }
    }
Exemplo n.º 12
0
        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);
            }
        }
Exemplo n.º 13
0
 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()));
 }
Exemplo n.º 14
0
 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());
 }