asp.net中SqlCommand對象使用

 

 

一、SqlCommand常見的屬性：SqlCommand屬性為執行命令作準備。

1、 CommandText屬性：執行的SQL語句；2、 Connection屬性：串連資料庫SqlConnection對象；3、 CommandType屬性：解析CommandText的值；            SqlCommand cmd = new SqlCommand("login", conn);     cmd.CommandType = CommandType.StoredProcedure; //這裡採用預存程序4、 CommandTimeout屬性：設定需要執行多久停止；5、 Parameters屬性：設定參數；二、SqlCommand類建構函式             SqlCommand myCommand = new SqlCommand(sqlupdate, conn);三、SqlCommand常見的方法： SqlCommand方法主要執行SQL語句。1、 ExecuteReader()方法：主要執行select語句。將結果返回到 SqlDataReader對象。例：        SqlCommand myconn = new SqlCommand("select * from v_economy2_comidd where eid=" + Request.QueryString["eid"] + "", conn);        conn.Open();        SqlDataReader rd = myconn.ExecuteReader();        rd.Read();        Lbyear1.Text = rd["year1"].ToString();        Lbmonth1.Text = rd["month1"].ToString();        Lbcom_name.Text = rd["com_name"].ToString();        rd.Close();        conn.Close();2、 ExecuteNonQuery()方法：主要執行Insert、Update、Delete語句。傳回值為該命令所影響的行數。例：    protected void Button1_Click(object sender, EventArgs e)    {        string class_name = TextBox1.Text;        string pwd = PwdMd5.md5l("111");        SqlCommand myconn = new SqlCommand("insert into UserAdmin(UserName,UserPwd,UserLevel,tim,num)values('" + class_name + "','" + pwd + "','U',@tim,1)", conn);        myconn.Parameters.Add(new SqlParameter("@tim", SqlDbType.DateTime, 8));        myconn.Parameters["@tim"].Value = DateTime.Now.ToString();   //顯示詳細的日期和時間        conn.Open();        myconn.ExecuteNonQuery();        conn.Close();        Response.Write("<script   language='javascript'>alert('添加管理員成功!初始密碼為123456');location='AdUserMag.aspx'</script>");}     protected void GridView1_RowUpdating(object sender, GridViewUpdateEventArgs e)    {        string sqlUpdate = "update UserAdmin set UserPwd=@UserPwd Where UserId='" + int.Parse(GridView1.DataKeys[e.RowIndex].Value.ToString().Trim()) + "'";        SqlCommand MyConn = new SqlCommand(sqlUpdate, conn);        MyConn.Parameters.Add(new SqlParameter("@UserPwd", SqlDbType.VarChar, 500));        MyConn.Parameters["@UserPwd"].Value = PwdMd5.md5l("11111eaderonQuery       ");        conn.Open();        MyConn.ExecuteNonQuery();        conn.Close();        Response.Write("<script   language='javascript'>alert('還原密碼成功!!還原密碼為111');location='AdUserMag.aspx'</script>");     }     protected void GridView1_RowDeleting(object sender, GridViewDeleteEventArgs e)    {        string sqldel = "delete from UserAdmin where UserId=" + int.Parse(GridView1.DataKeys[e.RowIndex].Value.ToString().Trim());         SqlCommand myconn = new SqlCommand(sqldel, conn);        conn.Open();        myconn.ExecuteNonQuery();        //lbsql.Text = "<b>已刪除記錄</b><br>" + sqldel;        conn.Close();        BindGrid();    } 3、 ExecuteScalar()方法：返回獲得的彙總值（共有多少行資料）。4、 ExecuteXmlReader()方法。