T-SQL語句實作GRIDVIEW的增删改查的執行個體練習

using System;

using System.Data;

using System.Configuration;

using System.Collections;

using System.Web;

using System.Web.Security;

using System.Web.UI;

using System.Web.UI.WebControls;

using System.Web.UI.WebControls.WebParts;

using System.Web.UI.HtmlControls;

using System.Data.SqlClient;

namespace CommandExercise

{

    public partial class _Default : System.Web.UI.Page

    {

        protected void Page_Load(object sender, EventArgs e)

        {

            if (!IsPostBack)

            {

                bind();

            }

        }

        protected void btnAddStu_Click(object sender, EventArgs e)  //增

        {

            string stuId = txtStuId.Text;

            string stuname = txtStuName.Text;

            string stuclass = txtStuClass.Text;

            string strSql ="insert stuform values("+stuId+",'"+stuname+"','"+stuclass+"')";

            string connectString = ConfigurationManager.ConnectionStrings["資料庫連接配接"].ConnectionString;

            SqlConnection con = new SqlConnection();

            con.ConnectionString = connectString;

            con.Open();

            SqlCommand com = new SqlCommand(strSql, con);

            int sum = com.ExecuteNonQuery();

            if (sum > 0)

            {

                bind();

                txtStuClass.Text = txtStuId.Text = txtStuName.Text = "";

            }

            else

            {

                Page.ClientScript.RegisterClientScriptBlock(Page.GetType(), "", "<script>alert('添加失敗!')</script>");

            }

            con.Close();

        }

        protected void bind()

        {

            string connectString = @"Data Source=GANG;Initial Catalog=stu;Persist Security Info=True;User ID=aspuser;password=000000";   

            string strSql = "select * from stuform";

            SqlConnection con = new SqlConnection();

            con.ConnectionString = connectString;

            con.Open();

            SqlCommand com = new SqlCommand(strSql, con);

            SqlDataAdapter sda = new SqlDataAdapter();

            sda.SelectCommand = com;

            DataSet ds = new DataSet();

            sda.Fill(ds);

            con.Close();

            gvShowStu.DataSource = ds;

            gvShowStu.DataBind();

        }

        protected void btnDeleStu_Click(object sender, EventArgs e) //删

        {

            string stuid = txtDeleStuId.Text;

            string strSql = "delete from stuform where StuID=" + stuid;

            string connectString = ConfigurationManager.ConnectionStrings["資料庫連接配接"].ConnectionString;

            SqlConnection con = new SqlConnection();

            con.ConnectionString = connectString;

            con.Open();

            SqlCommand com = new SqlCommand(strSql, con);

            int sum = com.ExecuteNonQuery();

            if (sum > 0)

            {

                bind();

                txtDeleStuId.Text = "";

            }

            else

            {

                Page.ClientScript.RegisterClientScriptBlock(Page.GetType(), "", "<script>alert('删除失敗!')</script>");

            }

            con.Close();

        }

        protected void btnSearchStu_Click(object sender, EventArgs e) //查

        {

            string stuid = txtSearchStuID.Text;

            string strSql = "select * from stuform where StuID=" + stuid;

            string connectString = ConfigurationManager.ConnectionStrings["資料庫連接配接"].ConnectionString;

            SqlConnection con = new SqlConnection();

            con.ConnectionString = connectString;

            con.Open();

            SqlCommand com = new SqlCommand(strSql,con);

            SqlDataReader sdr = com.ExecuteReader();

            sdr.Read();

            lblName.Text = sdr["StuName"].ToString();

            lblClass.Text = sdr["StuClass"].ToString();

            con.Close(); 

            sdr.Close();    

        }

        protected void btnUpdateStu_Click(object sender, EventArgs e)//改

        {

            string strid = txtUpdateID.Text;

            string strName = txtUpdateName.Text;

            string strClass = txtUpdateClass.Text;

            string strSql="update stuform set StuName='"+strName+"',StuClass='"+strClass+"'where StuID="+strid;

            string connectString = ConfigurationManager.ConnectionStrings["資料庫連接配接"].ConnectionString;

            SqlConnection con = new SqlConnection();

            con.ConnectionString = connectString;

            con.Open();

            SqlCommand com = new SqlCommand(strSql,con);

            int sum = com.ExecuteNonQuery();

            if (sum > 0)

            {

                bind();

                txtUpdateClass.Text = txtUpdateID.Text = txtUpdateName.Text = "";

            }

            else

            {

                Page.ClientScript.RegisterClientScriptBlock(Page.GetType(), "", "<script>alert('修改失敗!')</script>");

            }

            con.Close();

        }     

    }

}