黑马程序员_对数据库进行增删改查操作

 ------------------ Windows Phones 7手机开发、.Net培训、期待与您交流！ ----------------------

对数据库进行增删改查操作
前台：
<div>
        学生ID:<asp:TextBox ID="txtStudentID" runat="server"></asp:TextBox><br />
        姓名:<asp:TextBox ID="txtName" runat="server"></asp:TextBox><br />
        性别:<asp:TextBox ID="txtSex" runat="server"></asp:TextBox><br />
        年龄:<asp:TextBox ID="txtAge" runat="server"></asp:TextBox><br />
        毕业学校:<asp:TextBox ID="txtSchool" runat="server"></asp:TextBox><br />
        毕业时间:<asp:TextBox ID="txtByTime" runat="server"></asp:TextBox><br />
        <asp:Button ID="btnAdd" runat="server" Text="添加" οnclick="btnAdd_Click1" />
        <asp:Button ID="btnUpdate" runat="server" Text="更新" οnclick="btnUpdate_Click" /><br />
        学生ID:<asp:TextBox ID="TextBox1" runat="server"></asp:TextBox>
        <asp:Button ID="btnQuery" runat="server" Text="查询" οnclick="btnQuery_Click" />

        <asp:GridView ID="GridView1" runat="server" AutoGenerateColumns="False"
            onrowcommand="GridView1_RowCommand" onrowediting="GridView1_RowEditing">
            <Columns>
                <asp:TemplateField HeaderText="学生ID">
                    <ItemTemplate>
                        <%#Eval("StudentID") %>
                    </ItemTemplate>
                </asp:TemplateField>
                <asp:TemplateField HeaderText="姓名">
                    <ItemTemplate>
                        <%#Eval("Name") %>
                    </ItemTemplate>
                </asp:TemplateField>
                <asp:TemplateField HeaderText="性别">
                    <ItemTemplate>
                        <%#Eval("Sex") %>
                    </ItemTemplate>
                </asp:TemplateField>
                <asp:TemplateField HeaderText="年龄">
                    <ItemTemplate>
                        <%#Eval("Age") %>
                    </ItemTemplate>
                </asp:TemplateField>
                <asp:TemplateField HeaderText="毕业学校">
                    <ItemTemplate>
                        <%#Eval("School") %>
                    </ItemTemplate>
                </asp:TemplateField>
                <asp:TemplateField HeaderText="毕业时间">
                    <ItemTemplate>
                        <%#Eval("ByTime") %>
                    </ItemTemplate>
                </asp:TemplateField>
                <asp:TemplateField HeaderText="操作">
                    <ItemTemplate>
                        <asp:Button ID="btnDel" CommandArgument='<%#Eval("StudentID") %>' CommandName="Del" runat="server" Text="删除" />
                        <asp:Button ID="btnEdit" CommandArgument='<%#Eval("StudentID") %>' CommandName="Edit" runat="server" Text="编辑" />
                    </ItemTemplate>
                </asp:TemplateField>
            </Columns>
        </asp:GridView>
    </div>

后台：
    public partial class IndexZSGC : System.Web.UI.Page
    {
        /// <summary>
        /// 执行页面加载
        /// </summary>
        /// <param name="sender"></param>
        /// <param name="e"></param>
        protected void Page_Load(object sender, EventArgs e)
        {
            if (!IsPostBack)
            {
                databing();
            }
        }
        /// <summary>
        /// 查询数据绑定
        /// </summary>
        public void databing()
        {
            this.GridView1.DataSource = GetQuery();
            this.GridView1.DataBind();
        }
        /// <summary>
        /// 获取数据
        /// </summary>
        public DataSet GetQuery()
        {
            string SQL = "";
            if (this.TextBox1.Text.Trim().Equals(""))
            {
                SQL = "select * from Student";
            }
            else
            {
                SQL = "select * from Student where StudentID='" + this.TextBox1.Text + "'";
            }
            SqlConnection conn = new SqlConnection("Data Source=WANG-PC;Initial Catalog=WANG;User ID=sa;Password=123");//连接数据库
            conn.Open();//打开数据库
            SqlDataAdapter da = new SqlDataAdapter();//创建适配器用于填充数据
            da.SelectCommand = new SqlCommand();//创建SQL命令
            da.SelectCommand.Connection = conn;//使用SQL命令连接数据库
            da.SelectCommand.CommandText = SQL;//执行SQL
            da.SelectCommand.CommandType = CommandType.Text;//声明类型
            DataSet ds = new DataSet();//填充数据集
            da.Fill(ds);//得到结果集合
            conn.Close();//关闭数据库
            conn.Dispose();
            return ds;
        }

        /// <summary>
        /// 添加方法
        /// </summary>
        public void AddInfo()
        {
            string SQL = "insert into Student(StudentID,Name,Sex,Age,School,ByTime) values(@StudentID,@Name,@Sex,@Age,@School,@ByTime)";
            SqlConnection conn = new SqlConnection("Data Source=WANG-PC;Initial Catalog=WANG;User ID=sa;Password=123");//连接数据库
            conn.Open();//打开数据库
            SqlCommand smd = new SqlCommand();//创建SQL命令
            smd.Connection = conn;//使用SQL命令连接数据库
            smd.CommandText = SQL;//执行SQL
            smd.CommandType = CommandType.Text;//声明类型
            smd.Parameters.Add(new SqlParameter("@StudentID", this.txtStudentID.Text.Trim()));//给smd添加新的参数
            smd.Parameters.Add(new SqlParameter("@Name", this.txtName.Text.Trim()));
            smd.Parameters.Add(new SqlParameter("@Sex", this.txtSex.Text.Trim()));
            smd.Parameters.Add(new SqlParameter("@Age", this.txtAge.Text.Trim()));
            smd.Parameters.Add(new SqlParameter("@School", this.txtSchool.Text.Trim()));
            smd.Parameters.Add(new SqlParameter("@ByTime", this.txtByTime.Text.Trim()));
            smd.ExecuteNonQuery();//调用smd的ExecuteNonQuery方法完成添加操作
            conn.Close();//关闭数据库
        }

        /// <summary>
        /// 删除方法
        /// </summary>
        /// <param name="ID"></param>
        public void Delete(string ID)
        {
            string SQL = "delete from Student where StudentID='" + ID + "'";
            SqlConnection conn = new SqlConnection("Data Source=WANG-PC;Initial Catalog=WANG;User ID=sa;Password=123");//连接数据库
            conn.Open();//打开数据库
            SqlCommand smd = new SqlCommand();//创建SQL命令
            smd.Connection = conn;//使用SQL命令连接数据库
            smd.CommandText = SQL;//执行SQL
            smd.CommandType = CommandType.Text;//声明类型
            smd.ExecuteNonQuery();//调用smd的ExecuteNonQuery方法完成删除操作
            conn.Close();//关闭数据库
            conn.Dispose();
        }

        /// <summary>
        /// 编辑方法
        /// </summary>
        /// <param name="ID"></param>
        public void EditInfo(string ID)
        {
            string SQL = "Update Student set StudentID=@StudentID,Name=@Name,Sex=@Sex,Age=@Age,School=@School,ByTime=@ByTime where StudentID='" + ID + "'";
            SqlConnection conn = new SqlConnection("Data Source=WANG-PC;Initial Catalog=WANG;User ID=sa;Password=123");//连接数据库
            conn.Open();//打开数据库
            SqlCommand smd = new SqlCommand();//创建SQL命令
            smd.Connection = conn;//使用SQL命令连接数据库
            smd.CommandText = SQL;//执行SQL
            smd.CommandType = CommandType.Text;//声明类型
            smd.Parameters.Add(new SqlParameter("@StudentID", this.txtStudentID.Text));//给smd添加新的参数
            smd.Parameters.Add(new SqlParameter("@Name", this.txtName.Text));
            smd.Parameters.Add(new SqlParameter("@Sex", this.txtSex.Text));
            smd.Parameters.Add(new SqlParameter("@Age", this.txtAge.Text));
            smd.Parameters.Add(new SqlParameter("@School", this.txtSchool.Text));
            smd.Parameters.Add(new SqlParameter("@ByTime", this.txtByTime.Text));
            smd.ExecuteNonQuery();//调用smd的ExecuteNonQuery方法完成编辑操作
            conn.Close();
            conn.Dispose();
        }
       
        protected void GridView1_RowCommand(object sender, GridViewCommandEventArgs e)
        {
            if (e.CommandName == "Del")
            {
                string ID = e.CommandArgument.ToString();//获取需要删除的ID
                Delete(ID);//执行删除方法删除数据
                databing();
                Response.Write("<script type=\"text/javascript\">alert('删除成功')</script>");
            }
            else if (e.CommandName == "Edit")
            {
                string ID = e.CommandArgument.ToString();//获取需要编辑的ID
                SqlConnection conn = new SqlConnection("Data Source=WANG-PC;Initial Catalog=WANG;User ID=sa;Password=123");//连接数据库
                conn.Open();//打开数据库
                SqlCommand smd = new SqlCommand();//创建SQL命令
                smd.Connection = conn;//使用SQL命令连接数据库
                smd.CommandText = "select * from Student where StudentID='" + ID + "'";//执行SQL命令
                smd.CommandType = CommandType.Text;//声明类型
                SqlDataReader dr = smd.ExecuteReader();//创建阅读器阅读数据
                if (dr.Read())//循环阅读数据
                {
                    this.txtStudentID.Text = Convert.ToString(dr["StudentID"]);
                    this.txtName.Text = Convert.ToString(dr["Name"]);
                    this.txtSex.Text = Convert.ToString(dr["Sex"]);
                    this.txtAge.Text = Convert.ToString(dr["Age"]);
                    this.txtSchool.Text = Convert.ToString(dr["School"]);
                    //this.txtByTime.Text = Convert.ToString(dr["ByTime"]);
                    this.txtByTime.Text = dr["ByTime"].ToString();
                }
                dr.Close();
                conn.Close();
                conn.Dispose();
                databing();
            }
        }

        /// <summary>
        /// 添加事件
        /// </summary>
        /// <param name="sender"></param>
        /// <param name="e"></param>
        protected void btnAdd_Click1(object sender, EventArgs e)
        {
            AddInfo();//执行添加方法添加数据
            databing();
            Response.Write("<script type=\"text/javascript\">alert('添加成功')</script>");
        }

        /// <summary>
        /// 更新事件
        /// </summary>
        /// <param name="sender"></param>
        /// <param name="e"></param>
        protected void btnUpdate_Click(object sender, EventArgs e)
        {
            EditInfo(this.txtStudentID.Text);//执行编辑方法编辑数据
            databing();
            Response.Write("<script type=\"text/javascript\">alert('编辑成功')</script>");
        }

        /// <summary>
        /// 查询事件
        /// </summary>
        /// <param name="sender"></param>
        /// <param name="e"></param>
        protected void btnQuery_Click(object sender, EventArgs e)
        {
            databing();
        }

        /// <summary>
        /// 触发事件
        /// </summary>
        /// <param name="sender"></param>
        /// <param name="e"></param>
        protected void GridView1_RowEditing(object sender, GridViewEditEventArgs e)
        {

        }
    }

 

 

转载于:https://www.cnblogs.com/wang346351/archive/2013/04/16/3024486.html