WEB项目中家常便饭的增-删-改-查操作总结

using System;
using System.Collections.Generic;
using System.Linq;
using System.Web;
using System.Web.UI;
using System.Web.UI.WebControls;
using System.Data.SqlClient;
using System.Configuration;
using System.Data;

namespace WebApplication9_29
{
    public partial class WebForm1 : System.Web.UI.Page
    {
        string ConnStr = ConfigurationManager.ConnectionStrings["ConnStr"].ConnectionString;
        protected void Page_Load(object sender, EventArgs e)
        {
            selectALL();
        }
        //查询所有的信息
        public void selectALL()
        {
            SqlConnection conn = new SqlConnection(ConnStr);
            SqlCommand cmd = new SqlCommand();
            cmd.Connection = conn;
            cmd.CommandText = "select * from userInfo";
            try
            {
                conn.Open();
                SqlDataReader dr = cmd.ExecuteReader();
                this.GridView1.Caption = "";
                this.GridView1.DataSource = dr;
                this.GridView1.DataBind();
            }
            catch (SqlException sex)
            {
                throw sex;
            }
            finally
            {
                conn.Close();
            }
        }
        //采用保持连接的方式，当单击删除按钮时，调用cmdDelete()方法执行删除操作
        private void cmdDelete(GridView g1, GridViewDeleteEventArgs e)
        {
            string id = g1.DataKeys[e.RowIndex].Value.ToString();
            string SqlStr = "delete from userInfo where id=" + id;
            using (SqlConnection conn = new SqlConnection(ConnStr))
            {
                SqlCommand cmd = new SqlCommand(SqlStr, conn);
                try
                {
                    conn.Open();
                    int iValue = cmd.ExecuteNonQuery();
                    if (iValue > 0)
                    {
                        cmd.CommandText = "Select * from userInfo";
                        SqlDataReader dr = cmd.ExecuteReader();
                        this.GridView1.Caption = "";
                        this.GridView1.DataSource = dr;
                        this.GridView1.DataBind();
                    }
                }
                catch (Exception ex)
                {
                    Response.Write("数据库错误，错误原因：" + ex.Message);
                    Response.End();
                }
            }
        }
        //采用无连接的方式，当单击删除按钮时，调用dsDelete()方法执行删除操作
        private void dsDelete(GridView g1, GridViewDeleteEventArgs e)
        {
            using (SqlConnection conn = new SqlConnection(ConnStr))
            {
                SqlDataAdapter ad = new SqlDataAdapter
                ("Select * from userInfo", conn);
                SqlCommandBuilder builder = new SqlCommandBuilder(ad);
                ad.DeleteCommand = builder.GetDeleteCommand();
                DataTable dt = new DataTable();
                ad.Fill(dt);
                dt.Rows[e.RowIndex].Delete();
                ad.Update(dt);
                this.GridView1.DataSource = dt;
                this.DataBind();
            }
        }
        //采用保持连接的方式，当单击添加按钮时，调用cmdAdd()方法执行添加操作
        private void cmdAdd()
        {
            using (SqlConnection conn = new SqlConnection(ConnStr))
            {
                string SqlStr = "insert into userInfo(name,pwd,sex) values('" + txtname.Text.Trim() + "','" + txtpwd.Text.Trim() + "','" + txtsex.Text.Trim() + "')";
                SqlCommand cmd = new SqlCommand(SqlStr, conn);
                try
                {
                    conn.Open();
                    int iValue = cmd.ExecuteNonQuery();
                    if (iValue > 0)
                    {
                        cmd.CommandText = "Select * from userInfo";
                        SqlDataReader dr = cmd.ExecuteReader();
                        this.GridView1.Caption = "人员信息表";
                        this.GridView1.DataSource = dr;
                        this.GridView1.DataBind();
                    }
                }
                catch (Exception ex)
                {
                    Response.Write("数据库错误，错误原因：" + ex.Message);
                    Response.End();
                }
            }
        }
        //采用无连接的方式，当单击添加按钮时，调用dsAdd()方法执行添加操作
        private void dsAdd()
        {
            using (SqlConnection conn = new SqlConnection(ConnStr))
            {
                SqlDataAdapter ad = new SqlDataAdapter
                ("Select * from userInfo", conn);
                SqlCommandBuilder builder = new SqlCommandBuilder(ad);
                ad.InsertCommand = builder.GetInsertCommand();
                DataTable dt = new DataTable();
                ad.Fill(dt);
                this.txtname.Focus();
                DataRow row = dt.NewRow();
                row[0] = 1;
                row[1] = this.txtname.Text.Trim();
                row[2] = this.txtsex.Text.Trim();
                row[3] = this.txtpwd.Text.Trim(); 
                dt.Rows.Add(row);
                ad.Update(dt);
                this.GridView1.DataSource = dt;
                this.DataBind();
            }
        }
        //采用保持连接的方式，当单击更新按钮时，调用cmdUpdate ()方法执行更新操作
        private void cmdUpdate()
        {
            int id = (int)this.GridView1.SelectedDataKey.Value;
            using (SqlConnection conn = new SqlConnection(ConnStr))
            {
                string SqlStr = "update userInfo set name='" + txtname.Text.Trim() + "',pwd='" + txtpwd.Text.Trim() + "',sex='" + txtsex.Text.Trim() + "' where id=" + id;
                SqlCommand cmd = new SqlCommand(SqlStr, conn);
                try
                {
                    conn.Open();
                    int iValue = cmd.ExecuteNonQuery();
                    if (iValue > 0)
                    {
                        cmd.CommandText = "Select * from userInfo";
                        SqlDataReader dr = cmd.ExecuteReader();
                        this.GridView1.Caption = "人员信息表";
                        this.GridView1.DataSource = dr;
                        this.GridView1.DataBind();
                    }
                }
                catch (Exception ex)
                {
                    Response.Write("数据库错误，错误原因：" + ex.Message);
                    Response.End();
                }
            }
        }

        protected void addrow_Click(object sender, EventArgs e)
        {
          

           // cmdAdd();
           // dsAdd();
          
        }

        protected void updaterow_Click(object sender, EventArgs e)
        {
          
            cmdUpdate();
        }
        //采用无连接的方式，当单击更新按钮时，调用dsUpdate()方法执行更新操作
        private void dsUpdate()
                       {
                           using (SqlConnection conn = new SqlConnection(ConnStr))
                            {
                            SqlDataAdapter ad = new SqlDataAdapter
                            ("Select * from PersonInfo", conn);
                            SqlCommandBuilder builder = new SqlCommandBuilder(ad);
                            ad.UpdateCommand = builder.GetUpdateCommand();
                            DataTable dt = new DataTable();
                            ad.Fill(dt);
                           dt.Rows[rownum][0] = id;
                           dt.Rows[rownum][1] = this.txtName.Text.Trim();
                           dt.Rows[rownum][2] = this.txtSex.Text.Trim();
                           dt.Rows[rownum][3] = this.txtBirthday.Text.Trim();
                           dt.Rows[rownum][4] = this.txtPhone.Text.Trim();
                            dt.Rows[rownum][5] = this.txtAddress.Text.Trim();
                           dt.Rows[rownum][6] = this.txtZIP.Text.Trim();
                           ad.Update(dt);
                           this.GridView1.DataSource = dt;
                            this.DataBind();
                          }
                       }
    }
}

转载于:https://www.cnblogs.com/zhangweiguo/archive/2012/10/07/qq774147247.html