Example #1
1
    protected void Page_Load(object sender, EventArgs e)
    {
        if (Session["userID"] == null)
        {
            Response.Redirect("ADLogin.aspx", true);
        }
        if (!IsPostBack)
        {
            if (PreviousPage != null && !IsCrossPagePostBack)//如果是Server.Transfer()轉來的網頁IsCrossPagePostBack就會是false
                ViewState["ShopID"] = PreviousPage.ShopID;
            else
            {
                Response.Redirect("~/Index.aspx");
                Response.End();
            }

            Remark.Attributes["placeholder"] = "在這裡輸入你要對訂購人說的話";

            for (int i = 0 ; i < 13 ; i++)
            {
                Hour.Items.Add(new ListItem(i.ToString().PadLeft(2, '0')));
                Minute.Items.Add(new ListItem(i.ToString().PadLeft(2, '0')));
            }
            for (int i = 13 ; i < 24 ; i++)
            {
                Hour.Items.Add(new ListItem(i.ToString()));
                Minute.Items.Add(new ListItem(i.ToString()));
            }
            for (int i = 25 ; i < 60 ; i++)
            {
                Minute.Items.Add(new ListItem(i.ToString()));
            }

            Hour.SelectedValue = DateTime.Now.Hour.ToString().PadLeft(2, '0');
            Minute.SelectedValue = DateTime.Now.Minute.ToString().PadLeft(2, '0');

            //查店名
            using (SqlConnection conn = new SqlConnection(DBTools.ConnectionString))
            {
                conn.Open();
                using (SqlCommand cmd = new SqlCommand(@"SELECT ShopName FROM ShopHead where [email protected]", conn))
                {
                    cmd.Parameters.Add(new SqlParameter("@ShopID", SqlDbType.UniqueIdentifier));
                    cmd.Parameters[0].Value = PreviousPage.ShopID;

                    using (SqlDataReader dr = cmd.ExecuteReader())
                    {
                        dr.Read();
                        ShopName.Text = HttpUtility.HtmlEncode(dr.GetString(0));
                    }
                    cmd.Cancel();
                }
            }
        }
    }
 protected void Button1_Click1(object sender, EventArgs e)
 {
     SqlConnection conn_graph = new SqlConnection(WebConfigurationManager.ConnectionStrings["ConnectionString"].ConnectionString);
     //SqlCommand cmd_graph = new SqlCommand("SELECT Account_name, COUNT(Account_name) AS count FROM [Account_Order_M_View] WHERE (Order_date like '%" + DropDownList1.Text + "%') GROUP BY Account_name", conn_graph);
     SqlCommand cmd_graph = new SqlCommand("SELECT Account_name, COUNT(Account_name) AS count FROM [Account_Order_M_View] WHERE (Order_date like @Order_date) GROUP BY Account_name", conn_graph);
     cmd_graph.Parameters.AddWithValue("@order_date", DropDownList1.Text.Trim() + "%");
     SqlDataReader dr_graph = null;
     try //==== 以下程式,只放「執行期間」的指令!=====================
     {
         conn_graph.Open();//---- 這時候才連結DB
         dr_graph = cmd_graph.ExecuteReader();//---- 這時候執行SQL指令,取出資料
         Chart1.DataSource = dr_graph;
         Chart1.DataBind();
     }
     catch (Exception ex_graph) //---- 如果程式有錯誤或是例外狀況,將執行這一段
     {
         Response.Write("error" + ex_graph.ToString());
     }
     finally //---- 關掉資料連結
     {
         if (dr_graph == null)
         {
             cmd_graph.Cancel();
         }
         if (conn_graph.State == ConnectionState.Open)
         {
             conn_graph.Close();
             conn_graph.Dispose();
         }
     }
 }
Example #3
0
        protected void Button1_Click(object sender, EventArgs e)
        {
            if (TextBox1.Text.Trim() != "")
            {
                SqlConnection cnn = new SqlConnection();
                cnn.ConnectionString = "Data Source=IDEA-PC\\sqlexpress;Initial Catalog=AspNet_LK;Integrated Security=True";
                try
                {
                    cnn.Open();
                }
                catch (Exception ex)
                {
                    Label1.Text = ex.Message;
                }
                if (cnn.State.ToString() == "Open") Label1.Text = "Соединение открыто";

                SqlCommand comm = new SqlCommand();
                comm.CommandType = System.Data.CommandType.Text;
                comm.CommandText = "INSERT INTO Discussion ([Message],[n_ls]) VALUES ('"+  TextBox1.Text.Trim() +"'," + n_ls.ToString() +  ")";
                comm.Connection = cnn;
                comm.ExecuteNonQuery();
                comm.Cancel();
                cnn.Close();

                GridView1.DataBind();
            }
            TextBox1.Text = "";
        }
Example #4
0
        public ShowOutput(int id, SqlConnection sql)
        {
            InitializeComponent();

              Title = "Job " + id;

              SqlCommand cmd = new SqlCommand("SELECT SharedDir+'\\'+Strings.s as Filename,stdout,stderr " +
                                      "FROM Data,Strings,Experiments " +
                                      "WHERE FilenameP=Strings.ID AND Data.ExperimentID=Experiments.ID " +
                                      "AND Data.ID=" + id, sql);
              cmd.CommandTimeout = 0;
              SqlDataReader r = cmd.ExecuteReader();

              if (!r.Read())
            throw new Exception("Could not read from SQL server.");

              Object sOut = r["stdout"];
              Object sErr = r["stderr"];

              if (sOut == DBNull.Value)
            textBoxOut.Text = "*** NO OUTPUT SAVED ***";
              else
            textBoxOut.Text = (string)sOut;

              if (sErr == DBNull.Value)
            textBoxErr.Text = "*** NO OUTPUT SAVED ***";
              else
            textBoxErr.Text = (string)sErr;

              textBoxFn.Text = (string) r["Filename"];

              cmd.Cancel();
              r.Close();
        }
Example #5
0
 public static void ejecutarNonQuery(SqlCommand comandoo, String unaQuery)
 {
     comandoo.Cancel();
     comandoo.CommandText = unaQuery;
     comandoo.CommandTimeout = 21600;
     comandoo.ExecuteNonQuery();
     return;
 }
Example #6
0
        private void PopulateOccupation()
        {
            SqlConnection sqlConnectionX;
            SqlCommand sqlCommandX;
            SqlParameter sqlParam;
            SqlDataReader sqlDR;

            try
            {
                sqlConnectionX = new SqlConnection(ConfigurationManager.AppSettings["SQLConnection"]);
                sqlConnectionX.Open();

                sqlCommandX = new SqlCommand();
                sqlCommandX.Connection = sqlConnectionX;
                sqlCommandX.CommandType = CommandType.StoredProcedure;
                sqlCommandX.CommandText = "spx_SELECT_Occupation";

                sqlDR = sqlCommandX.ExecuteReader();
                DataTable dtResult = new DataTable("Result");
                dtResult.Load(sqlDR);
                                
                sqlDR.Close();
                sqlCommandX.Cancel();
                sqlCommandX.Dispose();

                RadComboBoxOccupation.DataTextField = "Occupation";
                RadComboBoxOccupation.DataValueField = "OccupationID";

                RadComboBoxItem cbDefaultItem = new RadComboBoxItem();
                cbDefaultItem.Value = "0";
                cbDefaultItem.Text = "- Please select an occupation -";
                RadComboBoxOccupation.Items.Add(cbDefaultItem);

                foreach (DataRow dataRow in dtResult.Rows)
                {                    
                    RadComboBoxItem cbItem = new RadComboBoxItem();
                    cbItem.Value = dataRow[0].ToString().Trim();
                    cbItem.Text = dataRow[1].ToString().Trim();
                    cbItem.Attributes.Add("Life", dataRow[2].ToString());
                    cbItem.Attributes.Add("ADW", dataRow[3].ToString());
                    cbItem.Attributes.Add("OCC", dataRow[4].ToString());
                    RadComboBoxOccupation.Items.Add(cbItem);
                }

                //RadComboBoxOccupation.DataSource = dtResult;
                //RadComboBoxOccupation.DataBind();

            }
            catch (Exception ex)
            {
                lblInfo.Text = ex.Message;
                lblInfo2.Text = ex.Message;   
            }
        }
Example #7
0
    protected void Page_Load(object sender, EventArgs e)
    {
        if (Session["USER_TYPE"] == null)
        {
            Response.Redirect("Logout.aspx");
        }
        if (Page.IsPostBack == false)
        {
            if (Session["USER_TYPE"].ToString() == "A")
            {
                Panel2.Visible = false;
            }
            else
            {
                Panel3.Visible = false;
            }
            try
            {

                string connStr = ConfigurationManager.ConnectionStrings["LibraryConn"].ToString();
                SqlConnection conn = new SqlConnection(connStr);

                /*string sql = "SELECT DISTINCT BT.BOOK_ID, BM.BOOK_TITLE, A.AUTHOR_NAME, P.PUBLISHER_NAME, P.PUBLISHING_YEAR, P.PUBLISHING_HOUSE, P.PUBLISHER_EDITION, C.CATEGORY_NAME, ";
                sql = sql + "S.SUPPLIER_NAME, BT.ISSUE_DATE, BT.RETURN_DATE, BT.ACTUAL_RETURN_DATE, BT.LATE_FEE FROM BOOK_TRANSACTION BT INNER JOIN BOOK_MASTER BM ON BT.BOOK_ID = BM.BOOK_ID ";
                sql = sql + "INNER JOIN AUTHOR A ON BM.AUTHOR_ID = A.AUTHOR_ID INNER JOIN PUBLISHER P ON BM.PUBLISHER_ID = P.PUBLISHER_ID INNER JOIN CATEGORY C ON BM.CATEGORY_ID = C.CATEGORY_ID ";
                sql = sql + "INNER JOIN SUPPLIER S ON BM.SUPPLIER_ID = S.SUPPLIER_ID WHERE BT.MEMBER_ID = 'M0002' ORDER BY BT.BOOK_ID";*/
                string sql = "SELECT DISTINCT BT.BOOK_ID AS 'Book ID', BM.BOOK_TITLE AS 'Book Tite' , A.AUTHOR_NAME AS 'Author Name', ";
                sql = sql + "P.PUBLISHER_NAME AS 'Publisher Name', BT.ISSUE_DATE AS 'Issue Date', BT.RETURN_DATE AS 'Return Date', ";
                sql = sql + "BT.ACTUAL_RETURN_DATE AS 'Actual Return Date', BT.LATE_FEE AS 'Late Fee' FROM BOOK_TRANSACTION BT INNER JOIN ";
                sql = sql + "BOOK_MASTER BM ON BT.BOOK_ID = BM.BOOK_ID INNER JOIN AUTHOR A ON BM.AUTHOR_ID = A.AUTHOR_ID ";
                sql = sql + "INNER JOIN PUBLISHER P ON BM.PUBLISHER_ID = P.PUBLISHER_ID WHERE BT.MEMBER_ID = '" + Session["MEMBER_ID"].ToString() + "' ORDER BY BT.BOOK_ID";
                SqlCommand cmd = new SqlCommand(sql, conn);
                SqlDataAdapter adp = new SqlDataAdapter(cmd);

                //Fill Dataet
                DataSet ds = new DataSet();
                adp.Fill(ds, "Book_Transaction");

                gvBook.DataSource = ds;
                gvBook.DataBind();

                adp.Dispose();
                cmd.Cancel();
                conn.Close();

            }
            catch (Exception ex)
            {
                throw ex;
            }
        }
    }
 public void executeSql(string sql)
 {
     try
     {
         SqlCommand sqlCommand = new SqlCommand(sql, this.GetSqlConnection());
         sqlCommand.ExecuteNonQuery();
         sqlCommand.Cancel();                
     }
     catch (Exception ex)
     {
         MessageBox.Show(ex.Message);
     }
 }
    protected void Button1_Click(object sender, EventArgs e)
    {
        // Show資料用的
        SqlConnection Conn = new SqlConnection();
        Conn.ConnectionString = WebConfigurationManager.ConnectionStrings["ConnectionString"].ConnectionString;
        //SqlConnection Conn = new SqlConnection(WebConfigurationManager.ConnectionStrings("testConnectionString").ConnectionString.ToString());

        SqlDataReader dr = null;

        SqlCommand cmd = new SqlCommand("SELECT id, Order_date, Assign_numbers, Account_name, Income, Spend FROM [Account_Order_M_View] where Account_name like '%" + TextBox1.Text + "%'", Conn);

        try     //==== 以下程式,只放「執行期間」的指令!=====================
        {
            Conn.Open();   //---- 這時候才連結DB

            dr = cmd.ExecuteReader();   //---- 這時候執行SQL指令,取出資料

            //string myTitle, mySummary;
            myTable.Rows.Add(BuildNewRow("Id", "Order_date", "Assign_numbers", "Account_name", "Income", "Spend"));
            while (dr.Read())
            {
                //myTitle = "<Strong><B><A href=Disp.aspx?id=" + dr["id"] + ">" + dr["title"] + "</A></B></Strong>";
                //mySummary = "<small><font color=#969696>" + dr["summary"] + "</font></small>......<A href=Disp.aspx?id=" + dr["id"] + ">詳見內文</A>";

                myTable.Rows.Add(BuildNewRow(dr["Id"].ToString(), dr["Order_date"].ToString(), dr["Assign_numbers"].ToString(), dr["Account_name"].ToString(), dr["Income"].ToString(), dr["Spend"].ToString()));
                //Table1.Rows.Add(BuildNewRow("", mySummary));
            }

        }
        catch (Exception ex)   //---- 如果程式有錯誤或是例外狀況,將執行這一段
        {
            Response.Write("<b>Error Message----  </b>" + ex.ToString());
        }
        finally
        {
            //---- Always call Close when done reading.
            if (dr != null)
            {
                cmd.Cancel();
                dr.Close();
            }

            //---- Close the connection when done with it.
            if (Conn.State == ConnectionState.Open)
            {
                Conn.Close();
                Conn.Dispose();  //---- 一開始宣告有用到 New的,最後必須以 .Dispose()結束
            }
        }
    }
Example #10
0
    public static List<Account> GetAccount()
    {
        List<Account> AccountList = new List<Account>();

        SqlDataReader dr = null;
        const string storedProcedureName = "GetAccount";

        using (SqlConnection connection = new SqlConnection(Reuasbles.GetConnectionString()))
        {
            SqlCommand command = new SqlCommand(storedProcedureName, connection);
            command.CommandType = CommandType.StoredProcedure;

            try
            {
                connection.Open();
                dr = command.ExecuteReader();

                Account acct;

                while (dr.Read())
                {
                    acct = new Account();

                    //acct.id = (int)dr["id"];
                    //acct.balance = (int)dr["balance"];
                    //acct.trn = (int)dr["trn"];
                    //acct.acc_type = dr["acc_type"].ToString();
                    ////acct.date_created = dr["mname"].ToString();
                  

                    AccountList.Add(acct);
                }
                dr.Close();


                command.Cancel();
            }
            catch (Exception err)
            {
                err.Data["Procedure"] = "Getting list of Clients";
                err.Data["sp"] = storedProcedureName;
            }
            finally
            {
                connection.Close();
            }
        }

        return AccountList;
    }
Example #11
0
    public static List<Car> GetCar()
    {
        List<Car> CarList = new List<Car>();

        SqlDataReader dr = null;
        const string storedProcedureName = "sp_GetCars";

        using (SqlConnection connection = new SqlConnection(Reuasbles.GetConnectionString()))
        {
            SqlCommand command = new SqlCommand(storedProcedureName, connection);
            command.CommandType = CommandType.StoredProcedure;

            try
            {
                connection.Open();
                dr = command.ExecuteReader();

                Car car;

                while (dr.Read())
                {
                    car = new Car();

                    car.chas = dr["chassi_no"].ToString();
                    car.colour = dr["colour"].ToString();
                    car.carmake = dr["carmake"].ToString();
                  


                    CarList.Add(car);
                }
                dr.Close();


                command.Cancel();
            }
            catch (Exception err)
            {
                err.Data["Procedure"] = "Getting list of Cars";
                err.Data["sp"] = storedProcedureName;
            }
            finally
            {
                connection.Close();
            }
        }

        return CarList;
    }
 public SqlDataReader executeRetornaSql(string sql)
 {
     try
     {
         SqlCommand sqlCommand = new SqlCommand(sql, this.GetSqlConnection());
         SqlDataReader reader = sqlCommand.ExecuteReader();
         sqlCommand.Cancel();                
         return reader;
     }
     catch (Exception ex)
     {
         MessageBox.Show(ex.Message);
         return null;
     }
 }
Example #13
0
        public static DataTable GetDataTable(string strSQL, List<Parameter> Pars)
        {
            DataTable dt = new DataTable();
            try
            {

                Open();
                SqlCommand sqlCand = new SqlCommand(strSQL, SqlConn);

                if ((Pars != null) && (Pars.Count > 0))
                {
                    foreach (Parameter par in Pars)
                    {
                        SqlParameter spar = new SqlParameter();
                        spar.Value = par.pvalues;
                        spar.ParameterName = par.pname;                        
                        sqlCand.Parameters.Add(spar);
                    }

                }

                SqlDataAdapter sqlda = new SqlDataAdapter(sqlCand);
                sqlCand.ExecuteNonQuery();
                sqlda.Fill(dt);
                
                sqlCand.Cancel();
            }
            catch (Exception ex)
            {
                //  flog_Class.WriteFlog(strSQL + "\r\n" + ex.Message);
            }
            //   Close();
            try
            {
                for (int i = 0; i < dt.Columns.Count; i++)
                {
                    dt.Columns[i].ColumnName = dt.Columns[i].ColumnName.ToLower().Trim();
                }
            }
            catch (Exception ex)
            {
                //string str4 = strSQL + "\r\n" + ex.Message;
                //flog_Class.WriteFlog(str4);
            }
            return dt;
        }
Example #14
0
        public int GetSangamDashboardData(string strConnectionString, string strSangamID,
             ref Core.Sangam.SangamDashboardEntity objSangamDashboardEntity)
        {
            try
            {
                using (SqlConnection objSqlConnection = new SqlConnection(strConnectionString))
                {
                    objSqlConnection.Open();
                    // 1.  create a command object identifying the stored procedure
                    SqlCommand objSqlCommand = new SqlCommand("UspGetSangamProfilesInfo", objSqlConnection);

                    // 2. set the command object so it knows to execute a stored procedure
                    objSqlCommand.CommandType = CommandType.StoredProcedure;

                    // 3. add parameter to command, which will be passed to the stored procedure
                    objSqlCommand.Parameters.Add(new SqlParameter("@SangamID", strSangamID));
                    // execute the command
                    using (SqlDataReader objSqlDataReader = objSqlCommand.ExecuteReader())
                    {
                        while (objSqlDataReader.Read())
                        {
                            if (!string.IsNullOrEmpty(objSqlDataReader["LoggedInCount"].ToString()))
                                objSangamDashboardEntity.TotalLogin = Convert.ToInt32(objSqlDataReader["LoggedInCount"].ToString());
                            if (!string.IsNullOrEmpty(objSqlDataReader["ViewedProfile"].ToString()))
                                objSangamDashboardEntity.ProfilesViewed = Convert.ToInt32(objSqlDataReader["ViewedProfile"].ToString());
                            if (!string.IsNullOrEmpty(objSqlDataReader["ActiveProfiles"].ToString()))
                                objSangamDashboardEntity.ActiveProfiles = Convert.ToInt32(objSqlDataReader["ActiveProfiles"].ToString());
                            if (!string.IsNullOrEmpty(objSqlDataReader["TotalProfiles"].ToString()))
                                objSangamDashboardEntity.TotalProfiles = Convert.ToInt32(objSqlDataReader["TotalProfiles"].ToString());
                        }
                        objSqlDataReader.Close();
                    }
                    objSqlCommand.Cancel();
                    objSqlCommand.Dispose();
                    objSqlConnection.Close();
                    objSqlConnection.Dispose();
                }
            }
            catch (Exception objEx)
            {
                Helpers.LogExceptionInFlatFile(objEx);
            }
            return 0;
        }
    protected void Button1_Click(object sender, EventArgs e)
    {
        // Show資料用的
        SqlConnection Conn = new SqlConnection();
        Conn.ConnectionString = WebConfigurationManager.ConnectionStrings["ConnectionString"].ConnectionString;
        SqlDataReader dr = null;
        SqlCommand cmd = new SqlCommand("select ID, Order_date, Assign_numbers, Account_numbers, Account_name, Account_abstract from [Account_Order_M_View] where Assign_numbers like @Assign_numbers order by Order_date,ID desc", Conn);
        cmd.Parameters.AddWithValue("@Assign_numbers", TextBox1.Text.Trim() + "%");
        try
        {
            Conn.Open();

            dr = cmd.ExecuteReader();

            myTable.Rows.Add(BuildNewRow("Order_date", "Assign_numbers", "Account_name", "Account_abstract",""));
            string Lotto;
            while (dr.Read())
            {
                Lotto = "<b><a href=LottoReceiptUpdate.aspx?id="+ dr["Id"]+">選擇</a></b>";
                myTable.Rows.Add(BuildNewRow(Convert.ToDateTime(dr["Order_date"]).ToShortDateString(), dr["Assign_numbers"].ToString(), dr["Account_name"].ToString(), dr["Account_abstract"].ToString(),Lotto));
            }

        }
        catch (Exception ex)
        {
            Response.Write("<b>Error Message----  </b>" + ex.ToString());
        }
        finally
        {
            if (dr != null)
            {
                cmd.Cancel();
                dr.Close();
            }

            if (Conn.State == ConnectionState.Open)
            {
                Conn.Close();
                Conn.Dispose();
            }
        }
    }
Example #16
0
        public VO.usuario validar(String usuario, String password)
        {
            VO.usuario Obusuario = null;

            using (SqlConnection con = conexion.obtenerConexion())
            {
                SqlCommand comando = new SqlCommand(string.Format("select * from usuario where usuario='{0}' and clave= '{1}' ", usuario, password), con);

                SqlDataReader reader = comando.ExecuteReader();
                if (reader.Read()) {

                     Obusuario = new VO.usuario(reader.GetString(0),reader.GetString(1),reader.GetString(2),reader.GetString(3));

                }
                comando.Cancel();
                con.Close();
            }

                return Obusuario;
        }
    protected void Page_Load(object sender, EventArgs e)
    {
        if (!Page.IsPostBack)
        {
            SqlConnection conn = new SqlConnection(WebConfigurationManager.ConnectionStrings["ConnectionString"].ConnectionString.ToString());
            SqlDataReader dr = null;
            SqlCommand cmd = new SqlCommand("select * from [Account_Order_M_View] where id=" + Request["Id"], conn);

            try
            {
                conn.Open();
                dr = cmd.ExecuteReader();
                dr.Read();
                Label1.Text = Convert.ToDateTime(dr["Order_date"]).ToShortDateString();
                Label2.Text = dr["Assign_numbers"].ToString();
                Label3.Text = dr["Account_numbers"].ToString();
                Label4.Text = dr["Account_name"].ToString();
                Label5.Text = dr["Account_abstract"].ToString();
                Label6.Text = dr["Income"].ToString();
                Label7.Text = dr["Spend"].ToString();
            }
            catch (Exception ex)
            {
                Response.Write("error" + ex.ToString());
            }
            finally
            {
                if (dr != null)
                {
                    cmd.Cancel();
                    dr.Close();
                }
                if (conn.State == ConnectionState.Open)
                {
                    conn.Close();
                    conn.Dispose();
                }
            }

        }
    }
    protected void Page_Load(object sender, EventArgs e)
    {
        if (!Page.IsPostBack)
        {

            //月份用
            SqlConnection Conn_Month = new SqlConnection();
            Conn_Month.ConnectionString = WebConfigurationManager.ConnectionStrings["ConnectionString"].ConnectionString;
            SqlDataReader dr_Month = null;
            SqlCommand cmd_Month = new SqlCommand("SELECT * FROM [Account_Numbers_D]", Conn_Month);
            try
            {
                Conn_Month.Open();
                dr_Month = cmd_Month.ExecuteReader();
                DropDownList1.DataValueField = "Account_numbers";
                DropDownList1.DataTextField = "Account_name";
                DropDownList1.DataSource = dr_Month;
                DropDownList1.DataBind();
            }
            catch (Exception ex_Month)
            {
                Response.Write("<b>Error Message----  </b>" + ex_Month.ToString());
            }
            finally
            {
                if (dr_Month != null)
                {
                    cmd_Month.Cancel();
                    dr_Month.Close();
                }
                if (Conn_Month.State == ConnectionState.Open)
                {
                    Conn_Month.Close();
                    Conn_Month.Dispose();
                }
            }
            //月份用
        }
    }
 protected void Page_Load(object sender, EventArgs e)
 {
     if (!Page.IsPostBack)
     {
         SqlConnection Conn_Month = new SqlConnection();
         Conn_Month.ConnectionString = WebConfigurationManager.ConnectionStrings["ConnectionString"].ConnectionString;
         SqlDataReader dr_Month = null;
         SqlCommand cmd_Month = new SqlCommand("SELECT DISTINCT LEFT (Order_date, 7) AS Expr1 FROM Account_Order_M_View", Conn_Month);
         try
         {
             Conn_Month.Open();
             dr_Month = cmd_Month.ExecuteReader();
             DropDownList1.DataValueField = "Expr1"; //沒有指定名稱直接沿用expr1
             DropDownList1.DataTextField = "Expr1"; //沒有指定名稱直接沿用expr1
             DropDownList1.DataSource = dr_Month;
             DropDownList1.DataBind();
             DropDownList1.Items.Insert(0, "-- Please select One --");
         }
         catch (Exception ex_Month)
         {
             Response.Write("<b>Error Message----  </b>" + ex_Month.ToString());
         }
         finally
         {
             if (dr_Month != null)
             {
                 cmd_Month.Cancel();
                 dr_Month.Close();
             }
             if (Conn_Month.State == ConnectionState.Open)
             {
                 Conn_Month.Close();
                 Conn_Month.Dispose();
             }
         }
     }
 }
Example #20
0
        public static DataSet GetDataSet(string strSQL, string d_strSQL)
        {
            DataSet dt = new DataSet();
            try
            {

                // flog_Class.WriteFlog(strSQL);
                Open();
                SqlCommand sqlCand = new SqlCommand(strSQL, SqlConn);

                SqlDataAdapter sqlda = new SqlDataAdapter(sqlCand);
                sqlCand.ExecuteNonQuery();
                sqlda.Fill(dt);
                try
                {
                    for (int i = 0; i < dt.Tables[0].Columns.Count; i++)
                    {
                        dt.Tables[0].Columns[i].Caption = dt.Tables[0].Columns[i].Caption.ToLower().Trim();
                    }
                }
                catch (Exception ex)
                {
                    //string str4 = strSQL + "\r\n" + ex.Message;
                    //flog_Class.WriteFlog(str4);
                }
                sqlCand.Cancel();
            }
            catch (Exception ex)
            {
                //  flog_Class.WriteFlog(strSQL + "\r\n" + ex.Message);
            }
            Close();
            return dt;
        }
Example #21
0
        public static bool ExecuteSQL(string strSQL, string d_strSQL)
        {
            int count = -1;
            try
            {
                Open();

                SqlCommand sqlCand = new SqlCommand(strSQL, SqlConn);
                // flog_Class.WriteFlog(strSQL + "\r\n");
                count = sqlCand.ExecuteNonQuery();
                sqlCand.Cancel();
                return true;
            }
            catch (Exception ex)
            {
                //string str4 = strSQL + "\r\n" + ex.Message;
                //flog_Class.WriteFlog(str4);
            }
            Close();
            return false;
        }
Example #22
0
        public static DataSet GetDataSet(string strSQL, List<SqlParameter> Pars)
        {
            DataSet dt = new DataSet();
            try
            {

                Open();
                SqlCommand sqlCand = new SqlCommand(strSQL, SqlConn);

                if ((Pars != null) && (Pars.Count > 0))
                {
                    foreach (SqlParameter par in Pars)
                    {
                        sqlCand.Parameters.Add(par);
                    }

                }

                SqlDataAdapter sqlda = new SqlDataAdapter(sqlCand);
                sqlCand.ExecuteNonQuery();
                sqlda.Fill(dt);
                try
                {
                    for (int i = 0; i < dt.Tables[0].Columns.Count; i++)
                    {
                        dt.Tables[0].Columns[i].Caption = dt.Tables[0].Columns[i].Caption.ToLower().Trim();
                    }
                }
                catch (Exception ex)
                {
                    //string str4 = strSQL + "\r\n" + ex.Message;
                    //flog_Class.WriteFlog(str4);
                }
                sqlCand.Cancel();
            }
            catch (Exception ex)
            {
                // flog_Class.WriteFlog(strSQL + "\r\n" + ex.Message);
            }
            Close();
            return dt;
        }
Example #23
0
    protected void AddUser_Click(object sender, EventArgs e)
    {
        Register.Visible = true;
        Department.Visible = true;
        login.Visible = false;
        Nickname.Visible = true;
        AddUser.Visible = false;
        Label1.Visible = true;

        Department.Items.Add(new ListItem("請選擇您的部門"));

        using (SqlConnection conn = new SqlConnection(DBTools.ConnectionString))
        {
            conn.Open();
            using (SqlCommand cmd = new SqlCommand("SELECT DepartmentName,DepartmentID from Department", conn))
            {
                using (SqlDataReader dr = cmd.ExecuteReader())
                {
                    while (dr.Read())
                        Department.Items.Add(new ListItem(dr.GetString(0), dr.GetByte(1).ToString()));
                }
                cmd.Cancel();
            }
        }
    }
        /// <summary>
        /// Executes an SQL query and populates a given <see cref="QueryResults" /> instance with the results.
        /// </summary>
        /// <param name="results"><see cref="QueryResults" /> instance to populate with results.</param>
        /// <param name="command">SQL command to execute.</param>
        /// <param name="result"><see cref="AsyncResult"/> instance to use to mark state changes.</param>
        /// <param name="messages"><see cref="StringBuilder" /> instance to which to append messages.</param>
        /// <param name="IncludeExecutionPlan">If true indciates that the query execution plans are expected to be contained
        /// in the results sets; otherwise, false.</param>
        private static void PopulateResults(QueryResults results, SqlCommand command, AsyncQueryRunner.AsyncResult result, StringBuilder messages, bool IncludeExecutionPlan)
        {
            QueryPlan plan = new QueryPlan();
            using (SqlDataReader reader = command.ExecuteReader())
            {
                if (result != null && reader.HasRows)
                {
                    result.HasOutput = true;
                }

                do
                {
                    // Check to see if the resultset is an execution plan
                    if (IncludeExecutionPlan && reader.FieldCount == 1 && reader.GetName(0) == "Microsoft SQL Server 2005 XML Showplan")
                    {
                        if (reader.Read())
                        {
                            plan.AppendStatementPlan(reader[0].ToString());
                        }
                    }
                    else
                    {
                        if (reader.FieldCount == 0)
                        {
                            if (reader.RecordsAffected >= 0)
                            {
                                messages.AppendFormat("({0} row(s) affected)\n\n", reader.RecordsAffected);
                            }
                            continue;
                        }

                        var resultSet = new ResultSet();
                        resultSet.MessagePosition = messages.Length;
                        results.ResultSets.Add(resultSet);

                        for (int i = 0; i < reader.FieldCount; i++)
                        {
                            var columnInfo = new ResultColumnInfo();
                            columnInfo.Name = reader.GetName(i);
                            ResultColumnType colType;
                            if (ResultColumnInfo.ColumnTypeMap.TryGetValue(reader.GetFieldType(i), out colType))
                            {
                                columnInfo.Type = colType;
                            }

                            resultSet.Columns.Add(columnInfo);
                        }

                        int currentRow = 0;
                        while (reader.Read())
                        {
                            if (currentRow++ >= MAX_RESULTS)
                            {
                                results.Truncated = true;
                                results.MaxResults = MAX_RESULTS;
                                break;
                            }
                            var row = new List<object>();
                            resultSet.Rows.Add(row);

                            for (int i = 0; i < reader.FieldCount; i++)
                            {
                                object col = reader.GetValue(i);
                                if (col is DateTime)
                                {
                                    var date = (DateTime)col;
                                    col = date.ToJavascriptTime();
                                }
                                row.Add(col);
                            }
                        }
                        if (results.Truncated)
                        {
                            // next result would force ado.net to fast forward
                            //  through the result set, which is way too slow
                            break;
                        }

                        if (reader.RecordsAffected >= 0)
                        {
                            messages.AppendFormat("({0} row(s) affected)\n\n", reader.RecordsAffected);
                        }

                        messages.AppendFormat("({0} row(s) affected)\n\n", resultSet.Rows.Count);
                    }
                } while (reader.NextResult());
                command.Cancel();
            }
            results.ExecutionPlan = plan.PlanXml;
        }
Example #25
0
        private static string[][] RedJobsSub(DataDBAsynchService ddbs)
        {
            //Console.WriteLine(DateTime.Now.ToLongTimeString() + " Начали получать расшифровку заданий");
            List<string[]> jobs = null;
            string SQLstr = "";
            try
            {
                SQLstr =
                    //"begin tran tran1; "+
                    //"SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED; "+
                    "select pj.ParserName, pj.id parser_id, am.[ID], am.[Address],am.[Sticker],[Encoding],[IpProxy],[PortProxy],[WebPreAuthentificationPage] "
                + ",[WebAuthentificationPage],[WebConnectionString],[Site],[Source],[PathForPhoto], DelayFrom, DelayTo, ap.id "
                + "from AddressesMask am with(xlock) "
                + "inner join PjobStat pj "
                + "on pj.ID=am.JobID "
                    //+ "inner join AddressesParsing ap with(xlock) "
                + "inner join AddressesParsingStart ap "
                + "with(xlock) on ap.Address_ref=am.id "
                + " where ap.id not in(select AddressStart_Ref from AddressesParsingFinish) and ap.Host_Ref= " + GetHostRef(ddbs);
                //+"; commit tran tran1;";
                //+ " order by  pj.ParserName";
                lock(DataDBAsynchService.db_lock)
                using(SqlConnection sc =ddbs.GetDBCon(ddbs.ConnectionString))
                try
                {
                using(SqlCommand SQLCmd = new SqlCommand(SQLstr, sc))
                    try
                    {
                        SQLCmd.CommandTimeout = 29;
                        using (SqlDataReader MydataReader2 = SQLCmd.ExecuteReader())
                            try
                            {
                                if (MydataReader2 != null)
                                    if (MydataReader2.HasRows)
                                        try
                                        {
                                            jobs = new List<string[]>();
                                            object o0 = "";
                                            //if (MydataReader2.HasRows)
                                            while (MydataReader2.Read())
                                            {
                                                string[] job = new string[17];
                                                //Console.WriteLine(DateTime.Now.ToLongTimeString() + " Считали строку расшифровки");
                                                try
                                                {
                                                    if (MydataReader2.FieldCount == 17)
                                                    {

                                                        if (MydataReader2.IsDBNull(0))
                                                            o0 = "";
                                                        else
                                                            o0 = MydataReader2.GetSqlString(0).ToString();
                                                        /*Array.Resize(ref job, 1);*/
                                                        job[0] = (string)o0; //[ID]
                                                        ///*Array.Resize(ref job, 1);*/ job[0] = "70";

                                                        if (MydataReader2.IsDBNull(1))
                                                            o0 = "0";
                                                        else
                                                            o0 = MydataReader2.GetInt32(1).ToString();
                                                        /*Array.Resize(ref job, 2);*/
                                                        job[1] = (string)o0;//[ParserJobID]
                                                        ///*Array.Resize(ref job, 2);*/ job[1] = "GilcomGotovoeDataCollector";

                                                        if (MydataReader2.IsDBNull(2))
                                                            o0 = "0";
                                                        else
                                                            o0 = MydataReader2.GetInt32(2).ToString();
                                                        /*Array.Resize(ref job, 3);*/
                                                        job[2] = (string)o0;//[AddressMaskID]
                                                        ///*Array.Resize(ref job, 2);*/ job[1] = "GilcomGotovoeDataCollector";

                                                        if (MydataReader2.IsDBNull(3))
                                                            o0 = "";
                                                        else
                                                            o0 = MydataReader2.GetSqlString(3).ToString();
                                                        /*Array.Resize(ref job, 4);*/
                                                        job[3] = (string)o0;//[Encoding]
                                                        ///*Array.Resize(ref job, 3);*/ job[2] = "UTF8";

                                                        if (MydataReader2.IsDBNull(4))
                                                            o0 = "";
                                                        else
                                                            o0 = MydataReader2.GetSqlString(4).ToString();
                                                        /*Array.Resize(ref job, 5);*/
                                                        job[4] = (string)o0;


                                                        if (MydataReader2.IsDBNull(5))
                                                            o0 = "";
                                                        else
                                                            o0 = MydataReader2.GetSqlString(5).ToString();
                                                        /*Array.Resize(ref job, 6);*/
                                                        job[5] = (string)o0;

                                                        if (MydataReader2.IsDBNull(6))
                                                            o0 = "";
                                                        else
                                                            o0 = MydataReader2.GetSqlString(6).ToString();
                                                        /*Array.Resize(ref job, 7);*/
                                                        job[6] = (string)o0;

                                                        if (MydataReader2.IsDBNull(7))
                                                            o0 = "0";
                                                        else
                                                            o0 = MydataReader2.GetInt32(7).ToString();
                                                        /*Array.Resize(ref job, 8);*/
                                                        job[7] = (string)o0;

                                                        if (MydataReader2.IsDBNull(8))
                                                            o0 = "";
                                                        else
                                                            o0 = MydataReader2.GetSqlString(8).ToString();
                                                        /*Array.Resize(ref job, 9);*/
                                                        job[8] = (string)o0;

                                                        if (MydataReader2.IsDBNull(9))
                                                            o0 = "";
                                                        else
                                                            o0 = MydataReader2.GetSqlString(9).ToString();
                                                        /*Array.Resize(ref job, 10);*/
                                                        job[9] = (string)o0;

                                                        if (MydataReader2.IsDBNull(10))
                                                            o0 = "";
                                                        else
                                                            o0 = MydataReader2.GetSqlString(10).ToString();
                                                        /*Array.Resize(ref job, 11);*/
                                                        job[10] = (string)o0;

                                                        if (MydataReader2.IsDBNull(11))
                                                            o0 = "";
                                                        else
                                                            o0 = MydataReader2.GetSqlString(11).ToString();
                                                        /*Array.Resize(ref job, 12);*/
                                                        job[11] = (string)o0;

                                                        if (MydataReader2.IsDBNull(12))
                                                            o0 = "";
                                                        else
                                                            o0 = MydataReader2.GetSqlString(12).ToString();
                                                        /*Array.Resize(ref job, 13);*/
                                                        job[12] = (string)o0;

                                                        if (MydataReader2.IsDBNull(13))
                                                            o0 = "";
                                                        else
                                                            o0 = MydataReader2.GetSqlString(13).ToString();
                                                        /*Array.Resize(ref job, 14);*/
                                                        job[13] = (string)o0;

                                                        if (MydataReader2.IsDBNull(14))
                                                            o0 = "0";
                                                        else
                                                            o0 = MydataReader2.GetInt32(14).ToString();
                                                        /*Array.Resize(ref job, 15);*/
                                                        job[14] = (string)o0;

                                                        if (MydataReader2.IsDBNull(15))
                                                            o0 = "0";
                                                        else
                                                            o0 = MydataReader2.GetInt32(15).ToString();
                                                        /*Array.Resize(ref job, 16);*/
                                                        job[15] = (string)o0;


                                                        if (MydataReader2.IsDBNull(16))
                                                            o0 = "0";
                                                        else
                                                            o0 = MydataReader2.GetInt32(16).ToString();
                                                        /*Array.Resize(ref job, 17);*/
                                                        job[16] = (string)o0;
                                                        /*if (MydataReader2.IsDBNull(14))
                                                            o0 = "0";
                                                        else
                                                            o0 = MydataReader2.GetSqlString(14).ToString();
                                                        /*Array.Resize(ref job, 15);*/
                                                        //job[14] = (string)o0;

                                                        //Array.Resize(ref jobs, jobs.Count() + 1);
                                                        jobs.Add(job);
                                                    }
                                                }
                                                catch (SystemException exx)
                                                {
                                                    SQLCmd.Cancel();
                                                    if (o0 != null)
                                                    {
                                                        Console.WriteLine("Ошибка четния задания2. " + exx.Message + " получено " + o0.ToString());
                                                        ddbs.WriteErrorMessage(ddbs.ConnectionString, 0, null, "Ошибка четния задания2 " + SQLstr + ". " + exx.Message + " получено " + o0.ToString());
                                                    }
                                                    else
                                                    {
                                                        Console.WriteLine("Ошибка четния задания2 " + exx.Message + " получено null");
                                                        ddbs.WriteErrorMessage(ddbs.ConnectionString, 0, null, "Ошибка четния задания2 " + exx.Message + " получено null");
                                                    }
                                                }
                                                finally
                                                { 
                                                        GC.SuppressFinalize(job);                                                
                                                }
                                            }
                                            /*else
                                            {
                                                Console.WriteLine("Запрос вернул 0 строк. Вроде. Или HasRows косячит");
                                            }*/
                                        }
                                        catch (Exception ex)
                                        {
                                            SQLCmd.Cancel();
                                            Console.WriteLine("Ошибка четния задания1 " + ex.Message);
                                            ddbs.WriteErrorMessage(ddbs.ConnectionString, 0, null, "Ошибка четния задания1 " + ex.Message);
                                        }
                            }
                            catch { }
                            finally
                            {
                                if (MydataReader2 != null)
                                {
                                    if (!MydataReader2.IsClosed)
                                        MydataReader2.Close();
                                    MydataReader2.Dispose();
                                    GC.SuppressFinalize(MydataReader2);
                                }
                            }
                    }
                    catch (Exception ex)
                    {
                        SQLCmd.Cancel();
                        Console.WriteLine("Ошибка четния задания3 " + ex.Message);
                        //ddbs.WriteErrorMessage(ddbs.ConnectionString, 0, null, "Ошибка четния задания3 " + ex.Message);
                        //return null;
                    }
                    finally
                    {
                        SQLCmd.Dispose();
                        GC.SuppressFinalize(SQLCmd);
                    }
                }
                catch (Exception ex)
                {
                    Console.WriteLine("Ошибка четния задания4 " + ex.Message);
                    ddbs.WriteErrorMessage(ddbs.ConnectionString, 0, null, "Ошибка четния задания4 " + ex.Message);
                    //return null;
                }
                finally
                {
                    if (sc != null)
                    {
                        if(sc.State!= System.Data.ConnectionState.Closed)
                            sc.Close();
                        sc.Dispose();
                        GC.SuppressFinalize(sc);
                    }
                }
            }
            catch (SystemException ex)
            {
                ddbs.WriteErrorMessage(ddbs.ConnectionString, 0, null, "Ошибка четния задания "  + ". " + ex.Message);
                //return null;
            }
            finally
            {
                //GC.Collect();
                if (jobs!=null)
                    Console.WriteLine(DateTime.Now.ToLongTimeString() + " Закончили получать задания. Получили: " + jobs.Count());
                else
                    Console.WriteLine(DateTime.Now.ToLongTimeString() + " Закончили получать задания. Но не получили(");
            }
            /*List<string[]> jobs_ret = new List<string[]>(jobs.Count());
            for (int i = 0; i < 10; i++)
                for (int j = 0; j <= jobs.Count() / 10;j++ )
                    if (10 * j + i < jobs.Count())
                        jobs_ret.Add(jobs[10 * j + i]);*/
            string[][] jobs_ = null;
            if (jobs != null)
            {
                if (jobs.Any())
                {
                    try
                    {
                        jobs_ = new string[jobs.Count][];
                        for (int i = 0; i < jobs.Count; i++)
                        {
                            jobs_[i] = jobs[i];
                            GC.SuppressFinalize(jobs[i]);
                        }
                        jobs.Clear();
                    }
                    catch { Console.WriteLine("Произошла ошибка в финальной обработке заданий"); }
                    finally
                    {
                        GC.SuppressFinalize(jobs);
                    }
                }
            }
            return jobs_;
        }
Example #26
0
    protected void Repeater1_ItemDataBound(object sender, RepeaterItemEventArgs e)
    {
        if (e.Item.ItemType == ListItemType.Item || e.Item.ItemType == ListItemType.AlternatingItem)
        {
            HiddenField HiddenShopID = e.Item.FindControl("ShopID") as HiddenField;

            using (SqlConnection conn = new SqlConnection(DBTools.ConnectionString))
            {
                conn.Open();

                HtmlTableCell td = e.Item.FindControl("ImgLink") as HtmlTableCell;
                using (SqlCommand cmd = new SqlCommand("select ShopID,FileName from MenuImg where [email protected]", conn))
                {
                    #region 設定菜單連結
                    cmd.Parameters.Add(new SqlParameter("@ShopID", SqlDbType.UniqueIdentifier));
                    cmd.Parameters[0].Value = Guid.Parse(HiddenShopID.Value);
                    using (SqlDataReader dr = cmd.ExecuteReader())
                    {
                        LiteralControl startCenter = new LiteralControl();
                        startCenter.Text = "<center>";
                        td.Controls.Add(startCenter);
                        while (dr.Read())
                        {
                            LiteralControl lit = new LiteralControl();
                            //上線要改
                            lit.Text = @"<a href=""http://" + Request.Url.Authority + "/Menu/" + dr[1].ToString() + @""" target=""_blank"">" + dr[1].ToString() + "</a>";
                            //lit.Text = @"<a href=""" + Request.Url.Scheme + "://" + Request.Url.Authority + "/" + Request.Url.Segments[1] + "Menu/" + HttpUtility.UrlEncode(dr[1].ToString()) + @""" target=""_blank"">" + dr[1].ToString() + "</a>";
                            td.Controls.Add(lit);
                            LiteralControl br = new LiteralControl();
                            br.Text = "</br>";
                            td.Controls.Add(br);
                        }
                        LiteralControl endCenter = new LiteralControl();
                        endCenter.Text = "</center>";
                        td.Controls.Add(endCenter);
                        cmd.Cancel();
                    }
                    #endregion
                    /*#region 設定訂單管理按鈕開單者才看得到
                    HiddenField hidOrderID = e.Item.FindControl("OrderID") as HiddenField;
                    cmd.Parameters.Clear();
                    cmd.CommandText = "SELECT a.Creator FROM OrderHead a WHERE [email protected] AND [email protected]";
                    cmd.Parameters.Add("@OrderID", SqlDbType.UniqueIdentifier);
                    cmd.Parameters[0].Value = Guid.Parse(hidOrderID.Value);
                    cmd.Parameters.Add("@Creator", SqlDbType.VarChar, 10);
                    cmd.Parameters[1].Value = Session["userID"];

                    if (cmd.ExecuteScalar() == null)
                    {
                        Button btnOrderManagement = e.Item.FindControl("OrderManagement") as Button;
                        btnOrderManagement.Visible = false;
                    }
                    #endregion*/
                }
            }
        }
    }
Example #27
0
    protected void Page_Load(object sender, EventArgs e)
    {
        if (!IsPostBack)
        {
            //WindowsPrincipal wp = new WindowsPrincipal(WindowsIdentity.GetCurrent());
            //string[] cADName = wp.Identity.Name.Split(new Char[] { '\\' });
            string cADName = User.Identity.Name.Split(new Char[] { '\\' })[1].Trim().ToUpper();

            //如果帳號不是這三個開頭的就轉去註冊頁面
            if (!cADName.StartsWith("TA") && !cADName.StartsWith("itap") && !cADName.StartsWith("TOO"))
            {
                Response.Redirect("Login.aspx", true);
            }

            
            string userID = null;
            /*#region 檢查使用者是否在白名單之中
             * using (SqlConnection conn = new SqlConnection(DBTools.ConnectionString))
            {
                conn.Open();
                using (SqlCommand cmd = new SqlCommand("SELECT userID FROM WhiteList Where [email protected]", conn))
                {
                    cmd.Parameters.Add("@userID", SqlDbType.VarChar, 10);
                    cmd.Parameters[0].Value = cADName;
                    userID = (string)cmd.ExecuteScalar();
                }
            }
            if (userID == null)
            {
                Response.End();
            }
            #endregion*/

            #region 檢查該使用者存不存在,存在就進入首頁,不存在就註冊
            using (SqlConnection conn = new SqlConnection(DBTools.ConnectionString))
            {
                conn.Open();
                using (SqlCommand cmd = new SqlCommand("SELECT UserID FROM UserData WHERE [email protected]", conn))
                {
                    cmd.Parameters.Add("@UserID", SqlDbType.VarChar, 10);
                    cmd.Parameters[0].Value = cADName;
                    userID = cmd.ExecuteScalar() as string;
                    cmd.Cancel();
                }
            }
            if (userID != null)
            {
                Session.Add("userID", cADName);
                Response.Redirect("Index.aspx", true);
            }
            else
            {
                ViewState.Add("userID", cADName);
                NickName.Attributes["placeholder"] = "可以輸入任何名稱,中英文都可以,只要別人認得就行";
                NickName.Attributes["required"] = "";
                NickName.Attributes["autofocus"] = "";

                Department.Items.Add(new ListItem("請選擇您的部門"));

                using (SqlConnection conn = new SqlConnection(DBTools.ConnectionString))
                {
                    conn.Open();
                    using (SqlCommand cmd = new SqlCommand("SELECT DepartmentName,DepartmentID FROM Department", conn))
                    {
                        using (SqlDataReader dr = cmd.ExecuteReader())
                        {
                            while (dr.Read())
                                Department.Items.Add(new ListItem(dr.GetString(0), dr.GetByte(1).ToString()));
                        }
                        cmd.Cancel();
                    }
                }
            }
            #endregion
        }
    }
Example #28
0
        public static int ExecuteSQLCount(string strSQL, List<Parameter> Pars)
        {
            int count = -1;
            try
            {
                Open();

                SqlCommand sqlCand = new SqlCommand(strSQL, SqlConn);
                if ((Pars != null) && (Pars.Count > 0))
                {
                    foreach (Parameter par in Pars)
                    {
                        SqlParameter spar = new SqlParameter();
                        spar.Value = par.pvalues;
                        spar.ParameterName = par.pname;
                        sqlCand.Parameters.Add(spar);
                    }
                }
                count = sqlCand.ExecuteNonQuery();
                sqlCand.Cancel();
            }
            catch (Exception ex)
            {
                //string str4 = strSQL + "\r\n" + ex.Message;
                //flog_Class.WriteFlog(str4);
            }
            Close();
            return count;
        }
Example #29
0
    protected void Page_Load(object sender, EventArgs e)
    {
        if (Session["userID"] == null)
        {
            Response.Redirect("ADLogin.aspx", true);
        }
        if (!IsPostBack)
        {
            #region 設定餐點類型
            Category.Items.Add(new ListItem("不拘", "不拘"));
            Category.Items.Add(new ListItem("中餐", "0"));
            Category.Items.Add(new ListItem("點心", "1"));
            Category.Items.Add(new ListItem("飲料", "2"));
            #endregion

            #region 設定素食選項
            IsVegetarianism.Items.Add(new ListItem("不拘", "不拘"));
            IsVegetarianism.Items.Add(new ListItem("否", "false"));
            IsVegetarianism.Items.Add(new ListItem("是", "true"));
            #endregion

            #region 設定部門別
            DepartMent.Items.Add(new ListItem("不拘", "不拘"));
            using (SqlConnection conn = new SqlConnection(DBTools.ConnectionString))
            {
                conn.Open();
                using (SqlCommand cmd = new SqlCommand("SELECT * FROM Department", conn))
                {
                    using (SqlDataReader dr = cmd.ExecuteReader())
                    {
                        //while (dr.Read())
                        //    DepartMent.Items.Add(new ListItem(dr.GetString(1), dr.GetString(1)));
                        DepartMent.DataTextField = "DepartmentName";
                        DepartMent.DataValueField = "DepartmentName";
                        DepartMent.DataSource = dr;
                        DepartMent.DataBind();
                    }
                    cmd.Cancel();
                }
            }
            #endregion

            #region 查詢菜單內容
            if (!string.IsNullOrWhiteSpace(Request["ShopName"]))
            {
                QueryString.Add("ShopName", Request["ShopName"]);
                ShopName.Text = Request["ShopName"];
            }
            if (!string.IsNullOrWhiteSpace(Request["Category"]))
            {
                QueryString.Add("Category", Request["Category"]);
                Category.Items.FindByValue(Request["Category"]).Selected = true;
            }

            if (!string.IsNullOrWhiteSpace(Request["IsVegetarianism"]) && (Request["IsVegetarianism"].Equals("是") || Request["IsVegetarianism"].Equals("否")))
            {
                QueryString.Add("IsVegetarianism", Request["IsVegetarianism"]);
                IsVegetarianism.Items.FindByText(Request["IsVegetarianism"]).Selected = true;
            }

            if (!string.IsNullOrWhiteSpace(Request["Department"]))
            {
                QueryString.Add("Department", Request["Department"]);
                DepartMent.Items.FindByText(Request["Department"]).Selected = true;
            }

            if (!string.IsNullOrWhiteSpace(Request["Creator"]))
            {
                QueryString.Add("Creator", Request["Creator"]);
                Creator.Text = Request["Creator"];
            }

            int PageNumber;
            if (string.IsNullOrWhiteSpace(Request["Page"]) || !int.TryParse(Request["Page"], out PageNumber))
                PageNumber = 1;
            ucPagination.CPage = PageNumber;
            Query(PageNumber);
            #endregion
        }
    }
Example #30
0
        public string ExecuteSQLCommand(string queryString, string connectionString, int TimeOut=29)
        {
            string s = "";
            int l = 32;
            if (l > queryString.Length)
                l = queryString.Length;
            try
            {
                lock (db_lock)
                using (SqlConnection connection =GetDBCon(connectionString))
                {
                    try
                    {
                        //connection.Open();
                        if(connection.State != ConnectionState.Closed)
                        using (SqlCommand command = new SqlCommand(queryString, connection))
                        {
                            try
                            {
                                if (command != null)
                                {
                                    command.CommandTimeout = TimeOut;
                                    command.ExecuteNonQuery();
                                }
                            }
                            catch (Exception exx)
                            {
                                s = exx.Message;
                                try
                                {
                                    if (command != null)
                                        command.Cancel();
                                }
                                catch (Exception exxx) { Console.WriteLine("Ошибка отмены запроса " + queryString.Substring(0, l) + " т.к. " + exxx.Message); }
                                finally
                                {
                                    Console.WriteLine(DateTime.Now.ToLongTimeString() + " Не могу выполнить команду2 " + queryString.Substring(0, l) + " т.к. " + exx.Message);
                                }
                            }
                            /*finally
                            {
                                if (command != null)
                                { command.Dispose(); GC.SuppressFinalize(command); }
                            }*/
                        }
                    }
                    catch (Exception ex)
                    {
                        s = ex.Message;
                        Console.WriteLine("Не могу выполнить команду1 " + queryString.Substring(0, l) + " т.к. " + ex.Message);
                    }
                    finally
                    {
                        if (connection != null)
                        {
                            if(connection.State!= ConnectionState.Closed)
                                connection.Close();
                            connection.Dispose();
                            GC.SuppressFinalize(connection); 
                        }
                    }
                }
            }
            catch (Exception ex)
            {
                s = ex.Message;
                Console.WriteLine("Не могу выполнить команду " + queryString.Substring(0, l) + " т.к. " + ex.Message);
            }

            return s; //lineCount;
        }