C# SqlCommand.Cancel方法代码示例

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

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

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

 public static void ejecutarNonQuery(SqlCommand comandoo, String unaQuery)
 {
     comandoo.Cancel();
     comandoo.CommandText = unaQuery;
     comandoo.CommandTimeout = 21600;
     comandoo.ExecuteNonQuery();
     return;
 }

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

    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()結束
            }
        }
    }

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

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

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

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