首页 > > 网络编程 > 其它 >

Ado.net[登录，增删改查，Get传值,全选，不选，…

2018-06-17 22:21:47来源：未知阅读 ()

[虽然说，开发的时候，我们可以使用各种框架，ado.net作为底层的东西，作为一个合格的程序员，在出问题的时候我们还是要知道如何调试]

一、增删改查

cmd.ExecuteReader();执行查询,所有sql语句的查询都用这个方法;

cmd.ExecuteNonQuery();执行所有sql语句的增删改都用这个方法;

  1       <div>
  2           <table>
  3               <tr>
  4                   <td> 用户名:</td>
  5                   <td>
  6                       <asp:TextBox ID="txtSUserName" runat="server"></asp:TextBox>
  7                   </td>
  8                   <td>班级：</td>
  9                   <td>
 10                       <asp:DropDownList ID="ddlselPhase" runat="server">
 11                           <asp:ListItem>---请选择---</asp:ListItem>
 12                           <asp:ListItem>.NET高级班01期</asp:ListItem>
 13                           <asp:ListItem>.NET高级班02期</asp:ListItem>
 14                           <asp:ListItem>.NET讲师</asp:ListItem>
 15                           <asp:ListItem>.NET网站开发01期</asp:ListItem>
 16                           <asp:ListItem>.NET网站开发02期</asp:ListItem>
 17                           <asp:ListItem>.NET网站开发03期</asp:ListItem>
 18                           <asp:ListItem>.NET网站开发04期</asp:ListItem>
 19                           <asp:ListItem>.NET网站开发05期</asp:ListItem>
 20                           <asp:ListItem>.NET网站开发06期</asp:ListItem>
 21                           <asp:ListItem>.NET网站开发07期</asp:ListItem>
 22                           <asp:ListItem>.NET网站开发08期</asp:ListItem>
 23                           <asp:ListItem>.NET网站开发09期</asp:ListItem>
 24                           <asp:ListItem>.NET网站开发10期</asp:ListItem>
 25                           <asp:ListItem>.NET网站开发11期</asp:ListItem>
 26                           <asp:ListItem>.NET网站开发12期</asp:ListItem>
 27                           <asp:ListItem>.NET网站开发13期</asp:ListItem>
 28                           <asp:ListItem>.NET网站开发14期</asp:ListItem>
 29                           <asp:ListItem>.NET网站开发15期</asp:ListItem>
 30                           <asp:ListItem>.NET网站开发16期</asp:ListItem>
 31                           <asp:ListItem>java第一期</asp:ListItem>
 32                           <asp:ListItem>JAVA讲师</asp:ListItem>
 33                           <asp:ListItem>ps设计01期</asp:ListItem>
 34                           <asp:ListItem>ps设计02期</asp:ListItem>
 35                           <asp:ListItem>ps设计03期</asp:ListItem>
 36                           <asp:ListItem>网页前端01期</asp:ListItem>
 37                       </asp:DropDownList>
 38                   </td>
 39                   <td>
 40                       <asp:Button ID="btnSel" runat="server" Text="查询" OnClick="btnSel_Click" />
 41                   </td>
 42               </tr>
 43           </table>
 44        </div>
 45        <div>
 46             <table>
 47               <tr>
 48                   <td> 用户名:</td>
 49                   <td>
 50                       <asp:TextBox ID="txtAddUserName" runat="server"></asp:TextBox>
 51                   </td>
 52                   <td> 密码:</td>
 53                   <td>
 54                       <asp:TextBox ID="txtAddPwd" runat="server" TextMode="Password"></asp:TextBox>
 55                   </td>
 56                   <td> QQ:</td>
 57                   <td>
 58                       <asp:TextBox ID="txtAddQq" runat="server"></asp:TextBox>
 59                   </td>
 60                   <td>班级：</td>
 61                   <td>
 62                       <asp:DropDownList ID="ddlAddPhase" runat="server">
 63                           <asp:ListItem>---请选择---</asp:ListItem>
 64                           <asp:ListItem>.NET高级班01期</asp:ListItem>
 65                           <asp:ListItem>.NET高级班02期</asp:ListItem>
 66                           <asp:ListItem>.NET讲师</asp:ListItem>
 67                           <asp:ListItem>.NET网站开发01期</asp:ListItem>
 68                           <asp:ListItem>.NET网站开发02期</asp:ListItem>
 69                           <asp:ListItem>.NET网站开发03期</asp:ListItem>
 70                           <asp:ListItem>.NET网站开发04期</asp:ListItem>
 71                           <asp:ListItem>.NET网站开发05期</asp:ListItem>
 72                           <asp:ListItem>.NET网站开发06期</asp:ListItem>
 73                           <asp:ListItem>.NET网站开发07期</asp:ListItem>
 74                           <asp:ListItem>.NET网站开发08期</asp:ListItem>
 75                           <asp:ListItem>.NET网站开发09期</asp:ListItem>
 76                           <asp:ListItem>.NET网站开发10期</asp:ListItem>
 77                           <asp:ListItem>.NET网站开发11期</asp:ListItem>
 78                           <asp:ListItem>.NET网站开发12期</asp:ListItem>
 79                           <asp:ListItem>.NET网站开发13期</asp:ListItem>
 80                           <asp:ListItem>.NET网站开发14期</asp:ListItem>
 81                           <asp:ListItem>.NET网站开发15期</asp:ListItem>
 82                           <asp:ListItem>.NET网站开发16期</asp:ListItem>
 83                           <asp:ListItem>java第一期</asp:ListItem>
 84                           <asp:ListItem>JAVA讲师</asp:ListItem>
 85                           <asp:ListItem>ps设计01期</asp:ListItem>
 86                           <asp:ListItem>ps设计02期</asp:ListItem>
 87                           <asp:ListItem>ps设计03期</asp:ListItem>
 88                           <asp:ListItem>网页前端01期</asp:ListItem>
 89                       </asp:DropDownList>
 90                   </td>
 91                   <td>
 92                       <asp:Button ID="btnAdd" runat="server" Text="添加" OnClick="btnAdd_Click" style="height: 21px" />
 93                   </td>
 94               </tr>
 95           </table>
 96         </div>
 97        <div>
 98             <table>
 99                 <tr>
100                     <td>用户ID:</td>
101                     <td>
102                         <asp:TextBox ID="txtDUId" runat="server"></asp:TextBox>
103                     </td>
104                     <td>
105                         <asp:Button ID="btnD" runat="server" Text="删除" OnClick="btnD_Click" />
106                     </td>
107                 </tr>
108             </table>
109         </div>

110        <div>
111            <table>
112                <tr>
113                    <td>ID:</td>
114                    <td>
115                        <asp:TextBox ID="txtUId" runat="server"></asp:TextBox>
116                    </td>
117                    <td>
118                        <asp:TextBox ID="txtUUserName" runat="server"></asp:TextBox>
119                    </td>
120                    <td>
121                        <asp:Button ID="btnU" runat="server" Text="更新" OnClick="btnU_Click" />
122                    </td>
123                </tr>
124            </table>
125        </div>
126        <div>
127         <asp:GridView ID="GriVShow" runat="server" AutoGenerateColumns="False">
128             <Columns>
129                 <asp:BoundField DataField="Userid" HeaderText="用户ID" />
130                 <asp:BoundField DataField="UserName" HeaderText="用户名：" />
131                 <asp:BoundField DataField="phonenum" HeaderText="电话号码" />
132                 <asp:BoundField DataField="sex" HeaderText="性别" />
133                 <asp:BoundField DataField="phase" HeaderText="班级" />
134                 <asp:BoundField DataField="qq" HeaderText="QQ" />
135                 <asp:BoundField DataField="Message" HeaderText="信息" />
136                 <asp:BoundField DataField="HeadPic" HeaderText="头像" />
137                 <asp:BoundField DataField="CreatedTime" HeaderText="创建时间" />
138                 <asp:TemplateField HeaderText="详情">
139                     <ItemTemplate>
140                         <a href="UserInforManagerContext.aspx?Id=<%#Eval("UserId")%>">详情</a>
141                     </ItemTemplate>
142                 </asp:TemplateField>
143             </Columns>
144         </asp:GridView>
145     </div>

  1   public partial class UserInforManager : System.Web.UI.Page
  2     {
  3         string constr = ConfigurationManager.ConnectionStrings["sq_r"].ToString();
  4         SqlConnection con = null;
  5         SqlCommand cmd = null;
  6         SqlDataReader read = null;
  7         protected void Page_Load(object sender, EventArgs e)
  8         {
  9             if (!(IsPostBack))
 10             {
 11                 BindUserInfor();
 12             }
 13         } 
 14         /// <summary>
 15         /// 数据绑定
 16         /// </summary>
 17         public void BindUserInfor()
 18         {
 19             try
 20             {
 21                 //string strstring = "select Userid,UserName,phonenum,sex,phase,qq,Message,HeadPic,CreatedTime from UserInfor where 1=1";
 22                 using (con = new SqlConnection(constr))
 23                 {
 24                     con.Open();
 25                     cmd = new SqlCommand(GetSql(), con);
 26                     using (read = cmd.ExecuteReader())
 27                     {
 28                         //开始读以上sql数据,这句话一定要有，如果把它取出来的话是需要读的，但是这地方我只想它把内容赋值给gridview
 29                         //if (read.Read())
 30                         //{
 31                         //}
 32                         //但是这地方我只想它把内容赋值给gridview
 33                         //内容取出来之后，我希望有东西可以接收它的内容
 34                         GriVShow.DataSource = read;
 35                         GriVShow.DataBind();
 36                     }
 37                 }
 38             }
 39             catch (Exception ex)
 40             {
 41                 Response.Write("<script>alret('系统正在维护，请联系管理员!');</script>");
 42             }
 43         }
 44 
 45         protected void btnSel_Click(object sender, EventArgs e)
 46         {
 47             BindUserInfor();
 48         }
 49         /// <summary>
 50         /// 获取SQL
 51         /// </summary>
 52         /// <returns></returns>
 53         public string GetSql()
 54         {
 55             //string username = txtSUserName.Text.Trim();
 56             //string phase = ddlselPhase.SelectedValue;
 57             StringBuilder sb = new StringBuilder();
 58             sb.Append("select Userid,UserName,phonenum,sex,phase,qq,Message,HeadPic,CreatedTime from UserInfor where 1=1");
 59             if (!string.IsNullOrEmpty(txtSUserName.Text.Trim()))
 60             {
 61                 sb.Append(string.Format("and UserName='{0}'", txtSUserName.Text.Trim()));
 62             }
 63             if (ddlselPhase.SelectedIndex > 0)
 64             {
 65                 sb.Append(string.Format("and phase='{0}'", ddlselPhase.SelectedValue));
 66             }
 67             return sb.ToString();
 68         }
 69 
 70         protected void btnAdd_Click(object sender, EventArgs e)
 71         {
 72             try
 73             { 
 74                 string addUserName = txtAddUserName.Text.Trim();
 75                 string addPwd = txtAddPwd.Text.Trim();
 76                 string addqq = txtAddQq.Text.Trim();
 77                 string addPhase = ddlAddPhase.SelectedIndex > 0 ? ddlAddPhase.SelectedValue : "";
 78                 if (!string.IsNullOrEmpty(addUserName))
 79                 {
 80                     using (con = new SqlConnection(constr))
 81                     {
 82                         con.Open();
 83                         string sstring1 = string.Format("insert into UserInfor(UserName,Pwd,QQ,Phase)values('{0}','{1}','{2}','{3}')", addUserName, addPwd, addqq, addPhase);
 84                         cmd = new SqlCommand(sstring1, con);
 85                         if (cmd.ExecuteNonQuery() > 0)
 86                         {
 87                             Response.Write("<script>alert('插入成功！');</script>");
 88                         }
 89                         BindUserInfor();
 90                     }
 91                 }
 92                 else
 93                 {
 94                     Response.Write("<script>alert('请输入内容');</script>");
 95                 }
 96             }
 97             catch (Exception)
 98             {
 99                 Response.Write("网页正在维护！");
100             }
101         }
102 
103         protected void btnD_Click(object sender, EventArgs e)
104         {
105             int UserId = txtDUId.Text.Trim() == "" ? 0 : Convert.ToInt32(txtDUId.Text.Trim());
106             try
107             {
108                 using (con = new SqlConnection(constr))
109                 {
110                     con.Open();
111                     string ssql = string.Format("select UserId from UserInfor where UserId='{0}'", UserId);
112                     cmd = new SqlCommand(ssql, con);
113                     read = cmd.ExecuteReader();
114                     if (read.HasRows)
115                     {
116                         read.Dispose();
117                         read.Close();
118                         string sstring1 = string.Format("delete UserInfor where UserId='{0}'", UserId);
119                         cmd = new SqlCommand(sstring1, con);
120                         if (cmd.ExecuteNonQuery() > 0)
121                         {
122                             Response.Write("<script>alert('删除成功！');</script>");
123                         }
124                         BindUserInfor();
125                     }
126                     else
127                     {
128                         Response.Write("该用户不存在！");
129                     }
130                 }
131             }
132             catch (Exception ex)
133             {
134                 Response.Write("<script>alret('系统正在维护，请联系管理员!');</script>");
135             }
136         }
137 
138         protected void btnU_Click(object sender, EventArgs e)
139         {
140             int uId = txtUId.Text.Trim()==""?0:Convert.ToInt32(txtUId.Text.Trim());
141             string uUserName = txtUUserName.Text.Trim();
142             try
143             {
144                 using (con = new SqlConnection(constr))
145                 {
146                     con.Open();
147                     string ssql = string.Format("select UserId from UserInfor where UserId='{0}'", uId);
148                     cmd = new SqlCommand(ssql, con);
149                     read = cmd.ExecuteReader();
150                     if (read.HasRows)
151                     {
152                         read.Dispose();
153                         read.Close();
154                         string ssql1 = string.Format("update UserInfor set UserName='{0}' where UserId='{1}'", uUserName, uId);
155                         cmd = new SqlCommand(ssql1, con);
156                         if (cmd.ExecuteNonQuery() > 0)
157                         {
158                             Response.Write("更新成功！");
159                         }
160                         BindUserInfor();
161                     }
162                     else
163                     {
164                         Response.Write("该用户不存在！");
165                     }
166                 }
167             }
168             catch (Exception)
169             {
170                 Response.Write("<script>alret('系统正在维护，请联系管理员!');</script>");
171             }
172         }
173     }

另一种更新的方法，调用cmd的ExecuteScalar()执行查询，记录数，返回0或1，返回是object类型

 1       <div>
 2            <table>
 3                <tr>
 4                    <td>ID:</td>
 5                    <td>
 6                        <asp:TextBox ID="txtUuid" runat="server"></asp:TextBox>
 7                    </td>
 8                    <td>
 9                        <asp:TextBox ID="txtUuserName2" runat="server"></asp:TextBox>
10                    </td>
11                    <td>
12                        <asp:Button ID="btnU2" runat="server" Text="更新" OnClick="btnU2_Click"/>
13                    </td>
14                </tr>
15            </table>
16        </div>

 1      protected void btnU2_Click(object sender, EventArgs e)
 2         {
 3             int Uid2 = txtUuid.Text.Trim() == "" ? 0 : Convert.ToInt32(txtUuid.Text.Trim());
 4             string uusername2 = txtUuserName2.Text.Trim();
 5             try
 6             {
 7                 using (con = new SqlConnection(constr))
 8                 {
 9                     con.Open();
10                     string ssql = string.Format("select count(*) from UserInfor where userid='{0}'", Uid2);
11                     cmd = new SqlCommand(ssql, con);
12                     int icount = Convert.ToInt32(cmd.ExecuteScalar().ToString());
13                     if (icount> 0)
14                     {
15                         string ssql1 = string.Format("update UserInfor set UserName='{0}' where UserId='{1}'", uusername2, Uid2);
16                         cmd = new SqlCommand(ssql1,con);
17                         if (cmd.ExecuteNonQuery() > 0)
18                         {
19                             Response.Write("<script>alert('数据更新成功！');</script>");
20                         }
21                         BindUserInfor();
22                     }
23                     else
24                     {
25                         Response.Write("该用户不存在！");
26                     }
27                 }
28             }
29             catch (Exception)
30             {
31                 Response.Write("<script>alret('系统正在维护，请联系管理员!');</script>");
32             }
33         }

二、Get传值：传一些安全系数低，Id,传类型，比较小的数据

1     <div>
2             <%=GetUserInfor()%>
3     </div>

 1 public partial class UserInforManagerContext : System.Web.UI.Page
 2     {
 3         /// <summary>
 4         ///通过这种方式把ID传了过去 ?<%#Eval("UserId") %>,接收get传值后的id
 5         /// </summary>
 6         /// <param name="sender"></param>
 7         /// <param name="e"></param>
 8 
 9         private int _userId;//定义一个字段,只可以访问
10 
11         public int UserId
12         {
13             //get,set是属性，既可以访问，又可以写
14             get
15             {
16                 try
17                 {
18                     _userId = Request.QueryString["Id"] == "" ? 0 : Convert.ToInt32(Request.QueryString["Id"].ToString());
19                 }
20                 catch (Exception)
21                 {
22                     _userId = 0;
23                 }
24                 return _userId;
25             }
26             set { _userId = value; }
27         }
28         protected void Page_Load(object sender, EventArgs e)
29         {
30 
31         }
32 
33         public string GetUserInfor()
34         {
35             StringBuilder sb = new StringBuilder();
36             sb.Append("<table>");
37             try
38             {
39                 if (UserId > 0)
40                 {
41                     string constr = ConfigurationManager.ConnectionStrings["sq_r"].ToString();
42                     using (SqlConnection con = new SqlConnection(constr))
43                     {
44                         con.Open();
45                         string sString = string.Format("select * from UserInfor where UserId='{0}'", UserId);
46                         SqlCommand cmd = new SqlCommand(sString, con);
47                         using (SqlDataReader read = cmd.ExecuteReader())
48                         {
49                             if (read.HasRows)
50                             {
51                                 if (read.Read())
52                                 {
53                                     sb.Append(string.Format("<tr><td>ID:</td><td>{0}</td></tr>", UserId));
54                                     sb.Append(string.Format("<tr><td>用户名:</td><td>{0}</td></tr>", read["userName"].ToString()));
55                                     sb.Append(string.Format("<tr><td>电话号码:</td><td>{0}</td></tr>", read["PhoneNum"].ToString()));
56                                     sb.Append(string.Format("<tr><td>QQ:</td><td>{0}</td></tr>", read["QQ"].ToString()));
57                                     sb.Append(string.Format("<tr><td>信息:</td><td>{0}</td></tr>", read["Message"].ToString()));
58                                 }
59                             }
60                         }
61                     }
62                 }
63                 else
64                 {
65                     sb.Append("<tr><td>未找到相关数据!</td></tr>");
66                 }
67             }
68             catch (Exception)
69             {
70                 Response.Write("网站正在维护，请联系管理员!");
71             }
72             sb.Append("</table>");
73             return sb.ToString();
74         }
75     }

三、批量删除:

3.1全选：

 1 <div>
 2             <table>
 3                 <tr>
 4                     <td>用户ID:</td>
 5                     <td>
 6                         <asp:TextBox ID="txtDUId" runat="server"></asp:TextBox>
 7                     </td>
 8                     <td>
 9                         <asp:Button ID="btnD" runat="server" Text="删除" OnClick="btnD_Click" />
10                     </td>
11                 </tr>
12             </table>
13         </div>
14        <div class="dItem">
15             <asp:Button ID="btnDelAll" runat="server" Text="批量删除" OnClick="btnDelAll_Click" OnClientClick="return confirm('你确定删除吗？');"/>
16         </div>
17        <div>
18         <asp:GridView ID="GriVShow" runat="server" AutoGenerateColumns="False">
19             <Columns>
20                 <asp:TemplateField>
21                     <HeaderTemplate>
22                         <asp:CheckBox ID="chkAll" runat="server" AutoPostBack="True" OnCheckedChanged="chkAll_CheckedChanged" />
23                     </HeaderTemplate>
24                     <ItemTemplate>
25                         <asp:CheckBox ID="chkItem" runat="server" />
26                     </ItemTemplate>
27                 </asp:TemplateField>
28                 <asp:BoundField DataField="Userid" HeaderText="用户ID" />
29                 <asp:BoundField DataField="UserName" HeaderText="用户名：" />
30                 <asp:BoundField DataField="phonenum" HeaderText="电话号码" />
31                 <asp:BoundField DataField="sex" HeaderText="性别" />
32                 <asp:BoundField DataField="phase" HeaderText="班级" />
33                 <asp:BoundField DataField="qq" HeaderText="QQ" />
34                 <asp:BoundField DataField="Message" HeaderText="信息" />
35                 <asp:BoundField DataField="HeadPic" HeaderText="头像" />
36                 <asp:BoundField DataField="CreatedTime" HeaderText="创建时间" />
37                 <asp:TemplateField HeaderText="详情">
38                     <ItemTemplate>
39                         <a href="UserInforManagerContext.aspx?Id=<%#Eval("UserId")%>">详情</a>
40                     </ItemTemplate>
41                 </asp:TemplateField>
42             </Columns>
43         </asp:GridView>
44     </div>

  1     public partial class UserInforManager : System.Web.UI.Page
  2     {
  3         string constr = ConfigurationManager.ConnectionStrings["sq_r"].ToString();
  4         SqlConnection con = null;
  5         SqlCommand cmd = null;
  6         SqlDataReader read = null;
  7         protected void Page_Load(object sender, EventArgs e)
  8         {
  9             if (!(IsPostBack))
 10             {
 11                 BindUserInfor();
 12             }
 13         } 
 14         /// <summary>
 15         /// 数据绑定
 16         /// </summary>
 17         public void BindUserInfor()
 18         {
 19             try
 20             {
 21                 //string strstring = "select Userid,UserName,phonenum,sex,phase,qq,Message,HeadPic,CreatedTime from UserInfor where 1=1";
 22                 using (con = new SqlConnection(constr))
 23                 {
 24                     con.Open();
 25                     cmd = new SqlCommand(GetSql(), con);
 26                     using (read = cmd.ExecuteReader())
 27                     {
 28                         //开始读以上sql数据,这句话一定要有，如果把它取出来的话是需要读的，但是这地方我只想它把内容赋值给gridview
 29                         //if (read.Read())
 30                         //{
 31                         //}
 32                         //但是这地方我只想它把内容赋值给gridview
 33                         //内容取出来之后，我希望有东西可以接收它的内容
 34                         GriVShow.DataSource = read;
 35                         GriVShow.DataBind();
 36                     }
 37                 }
 38             }
 39             catch (Exception ex)
 40             {
 41                 Response.Write("<script>alret('系统正在维护，请联系管理员!');</script>");
 42             }
 43         }
 44 
 45         protected void btnSel_Click(object sender, EventArgs e)
 46         {
 47             BindUserInfor();
 48         }
 49         /// <summary>
 50         /// 获取SQL
 51         /// </summary>
 52         /// <returns></returns>
 53         public string GetSql()
 54         {
 55             //string username = txtSUserName.Text.Trim();
 56             //string phase = ddlselPhase.SelectedValue;
 57             StringBuilder sb = new StringBuilder();
 58             sb.Append("select Userid,UserName,phonenum,sex,phase,qq,Message,HeadPic,CreatedTime from UserInfor where 1=1");
 59             if (!string.IsNullOrEmpty(txtSUserName.Text.Trim()))
 60             {
 61                 sb.Append(string.Format("and UserName='{0}'", txtSUserName.Text.Trim()));
 62             }
 63             if (ddlselPhase.SelectedIndex > 0)
 64             {
 65                 sb.Append(string.Format("and phase='{0}'", ddlselPhase.SelectedValue));
 66             }
 67             return sb.ToString();
 68         }
 69 
 70         protected void btnAdd_Click(object sender, EventArgs e)
 71         {
 72             try
 73             { 
 74                 string addUserName = txtAddUserName.Text.Trim();
 75                 string addPwd = txtAddPwd.Text.Trim();
 76                 string addqq = txtAddQq.Text.Trim();
 77                 string addPhase = ddlAddPhase.SelectedIndex > 0 ? ddlAddPhase.SelectedValue : "";
 78                 if (!string.IsNullOrEmpty(addUserName))
 79                 {
 80                     using (con = new SqlConnection(constr))
 81                     {
 82                         con.Open();
 83                         string sstring1 = string.Format("insert into UserInfor(UserName,Pwd,QQ,Phase)values('{0}','{1}','{2}','{3}')", addUserName, addPwd, addqq, addPhase);
 84                         cmd = new SqlCommand(sstring1, con);
 85                         if (cmd.ExecuteNonQuery() > 0)
 86                         {
 87                             Response.Write("<script>alert('插入成功！');</script>");
 88                         }
 89                         BindUserInfor();
 90                     }
 91                 }
 92                 else
 93                 {
 94                     Response.Write("<script>alert('请输入内容');</script>");
 95                 }
 96             }
 97             catch (Exception)
 98             {
 99                 Response.Write("网页正在维护！");
100             }
101         }
102 
103         public void Del(int UserId)
104         {
105             try
106             {
107                 using (con = new SqlConnection(constr))
108                 {
109                     con.Open();
110                     string ssql = string.Format("select UserId from UserInfor where UserId='{0}'", UserId);
111                     cmd = new SqlCommand(ssql, con);
112                     read = cmd.ExecuteReader();
113                     if (read.HasRows)
114                     {
115                         read.Dispose();
116                         read.Close();
117                         string sstring1 = string.Format("delete UserInfor where UserId='{0}'", UserId);
118                         cmd = new SqlCommand(sstring1, con);
119                         if (cmd.ExecuteNonQuery() > 0)
120                         {
121                             Response.Write("<script>alert('删除成功！');</script>");
122                         }
123                        
124                     }
125                     else
126                     {
127                         Response.Write("该用户不存在！");
128                     }
129                 }
130             }
131             catch (Exception ex)
132             {
133                 Response.Write("<script>alret('系统正在维护，请联系管理员!');</script>");
134             }
135         }
136         protected void btnD_Click(object sender, EventArgs e)
137         {
138             int UserId = txtDUId.Text.Trim() == "" ? 0 : Convert.ToInt32(txtDUId.Text.Trim());
139             Del(UserId);
140             BindUserInfor();
141         }
142 
143         /// <summary>
144         /// 更新的第一种方法
145         /// </summary>
146         /// <param name="sender"></param>
147         /// <param name="e"></param>
148         protected void btnU_Click(object sender, EventArgs e)
149         {
150             int uId = txtUId.Text.Trim()==""?0:Convert.ToInt32(txtUId.Text.Trim());
151             string uUserName = txtUUserName.Text.Trim();
152             try
153             {
154                 using (con = new SqlConnection(constr))
155                 {
156                     con.Open();
157                     string ssql = string.Format("select UserId from UserInfor where UserId='{0}'", uId);
158                     cmd = new SqlCommand(ssql, con);
159                     read = cmd.ExecuteReader();
160                     if (read.HasRows)
161                     {
162                         read.Dispose();
163                         read.Close();
164                         string ssql1 = string.Format("update UserInfor set UserName='{0}' where UserId='{1}'", uUserName, uId);
165                         cmd = new SqlCommand(ssql1, con);
166                         if (cmd.ExecuteNonQuery() > 0)
167                         {
168                             Response.Write("更新成功！");
169                         }
170                         BindUserInfor();
171                     }
172                     else
173                     {
174                         Response.Write("该用户不存在！");
175                     }
176                 }
177             }
178             catch (Exception)
179             {
180                 Response.Write("<script>alret('系统正在维护，请联系管理员!');</script>");
181             }
182         }
183 
184         /// <summary>
185         /// 更新的第二种方法
186         /// </summary>
187         /// <param name="sender"></param>
188         /// <param name="e"></param>
189         protected void btnU2_Click(object sender, EventArgs e)
190         {
191             int Uid2 = txtUuid.Text.Trim() == "" ? 0 : Convert.ToInt32(txtUuid.Text.Trim());
192             string uusername2 = txtUuserName2.Text.Trim();
193             try
194             {
195                 using (con = new SqlConnection(constr))
196                 {
197                     con.Open();
198                     string ssql = string.Format("select count(*) from UserInfor where userid='{0}'", Uid2);
199                     cmd = new SqlCommand(ssql, con);
200                     int icount = Convert.ToInt32(cmd.ExecuteScalar().ToString());
201                     if (icount > 0)
202                     {
203                         string ssql1 = string.Format("update UserInfor set UserName='{0}' where UserId='{1}'", uusername2, Uid2);
204                         cmd = new SqlCommand(ssql1, con);
205                         if (cmd.ExecuteNonQuery() > 0)
206                         {
207                             Response.Write("<script>alert('数据更新成功！');</script>");
208                         }
209                         BindUserInfor();
210                     }
211                     else
212                     {
213                         Response.Write("该用户不存在！");
214                     }
215                 }
216             }
217             catch (Exception)
218             {
219                 Response.Write("<script>alret('系统正在维护，请联系管理员!');</script>");
220             }   
221         }
222 
223         protected void chkAll_CheckedChanged(object sender, EventArgs e)
224         {
225             //1.当我们点击按钮时，去找chkAll它的事件源的对象把它变成checkBox
226             CheckBox chkAll = sender as CheckBox;
227             //2.对它的每一行进行遍历循环
228             foreach (GridViewRow gvr in GriVShow.Rows)
229             {
230                 //2.1获得到第一行的第一列，找到每一列id等于chkitem的对象把它变成checkbox
231                 CheckBox chkItem = gvr.Cells[0].FindControl("chkItem") as CheckBox;
232                 //2.2让它当前全选的checked属性等于下面每一行的属性，选中就为true，没选就为Fulse
233                 chkItem.Checked = chkAll.Checked;
234             }
235         }
236 
237         protected void btnDelAll_Click(object sender, EventArgs e)
238         {
239             //1.对Gridview进行遍历循环
240             foreach (GridViewRow gvr in GriVShow.Rows)
241             {
242                 //2.1获取到每行第二列的值
243                 int UserId = Convert.ToInt32(gvr.Cells[1].Text);
244                 //2.2获取到每一行的第一列就是去找ID等于chkItem的对象把它变成checkbox
245                 CheckBox chkItem=gvr.Cells[0].FindControl("chkItem") as CheckBox;
246                 if(chkItem.Checked)
247                 {
248                     Del(UserId);
249                 }
250             }
251             BindUserInfor();
252         }
253     }

4.GridView-模板绑定下拉列表数据

[首先这地方一定要与数据库要绑定的字段对应]，不然像楼主一样走了一个大坑

 1 <asp:TemplateField HeaderText="班级">
 2                     <ItemTemplate>
 3                         <asp:DropDownList ID="ddlgvPhase" runat="server" ToolTip='<%#Eval("phase")%>'>
 4                             <asp:ListItem>---请选择---</asp:ListItem>
 5                             <asp:ListItem>.NET高级班01期</asp:ListItem>
 6                             <asp:ListItem>.NET高级班02期</asp:ListItem>
 7                             <asp:ListItem>.NET讲师</asp:ListItem>
 8                             <asp:ListItem>.NET网站开发01期</asp:ListItem>
 9                             <asp:ListItem>.NET网站开发02期</asp:ListItem>
10                             <asp:ListItem>.NET网站开发03期</asp:ListItem>
11                             <asp:ListItem>.NET网站开发04期</asp:ListItem>
12                             <asp:ListItem>.NET网站开发05期</asp:ListItem>
13                             <asp:ListItem>.NET网站开发06期</asp:ListItem>
14                             <asp:ListItem>.NET网站开发07期</asp:ListItem>
15                             <asp:ListItem>.NET网站开发08期</asp:ListItem>
16                             <asp:ListItem>.NET网站开发09期</asp:ListItem>
17                             <asp:ListItem>.NET网站开发10期</asp:ListItem>
18                             <asp:ListItem>.NET网站开发11期</asp:ListItem>
19                             <asp:ListItem>.NET网站开发12期</asp:ListItem>
20                             <asp:ListItem>.NET网站开发13期</asp:ListItem>
21                             <asp:ListItem>.NET网站开发14期</asp:ListItem>
22                             <asp:ListItem>ps设计01期</asp:ListItem>
23                             <asp:ListItem>ps设计03期</asp:ListItem>
24                             <asp:ListItem>网页前端01期</asp:ListItem>
25                         </asp:DropDownList>
26                     </ItemTemplate>
27                 </asp:TemplateField>

这时候，我们需要调用gridView的RowDataBound事件，当我们打开浏览器，gridView加载从第一行到第二行的逐行加载，加载的时候就执行这个方法，为什么要调用这个方法呢？表示对gridView进行一个遍历

如何找到这个事件呢？GridView的属性-事件里就有，找到双击就好

 1  protected void GriVShow_RowDataBound(object sender, GridViewRowEventArgs e)
 2         {
 3             //判断当前行是不是数据行
 4             //获取到某行输入的数据把它变成UserInfor类型,对象才能调用它的属性方法
 5             //e.Row.DataItem当前行的数据集
 6             //找打当前行的班级
 7             if (e.Row.RowType == DataControlRowType.DataRow)
 8             {
 9                 DropDownList ddlPhase = e.Row.FindControl("ddlgvPhase") as DropDownList;
10                 string phase = ddlPhase.ToolTip;
11                 if (!string.IsNullOrEmpty(phase))
12                 {
13                     //清空列表里所有的项
14                     ddlPhase.ClearSelection();
15                     ddlPhase.Items.FindByValue(phase).Selected = true;
16                 }
17             }
18         }

5.批量更新:

userinfor这个常用的东西，我们直接封装成一个方法，直接调用该方法就好

 1 SqlConnection con = null;
 2         SqlCommand cmd = null;
 3         SqlDataReader read = null;
 4         string constr = ConfigurationManager.ConnectionStrings["sq_r"].ToString();
 5         protected void Page_Load(object sender, EventArgs e)
 6         {
 7             if (!(IsPostBack))//页面第一次加载
 8             {
 9                 BindUserInfor();
10             }
11         }
12         
13         public void BindUserInfor()
14         {
15             try
16             {
17                 using (con = new SqlConnection(constr))
18                 {
19                     // where 1=1 order by UserId desc    
20                     con.Open();
21                     string sSql = "select  top 100 Userid,UserName,Pwd,phonenum,phase,qq,CreatedTime from UserInfor where 1=1 order by UserId desc";
22                     cmd = new SqlCommand(sSql, con);
23                     using (read = cmd.ExecuteReader())
24                     {
25                         GriVShow.DataSource = read;
26                         GriVShow.DataBind();
27                     }
28                 }
29             }
30             catch (Exception)
31             {
32                 Response.Write("网页正在维护!");
33             }
34         }

前台实例的代码都在这：

 1 <form id="form1" runat="server">
 2        <div class="dItem">
 3            <table>
 4                <tr>
 5                    <td>
 6                        <asp:Button ID="btnDelAll" runat="server" Text="批量删除" OnClick="btnDelAll_Click" OnClientClick="return confirm('你确定删除吗？');"/>
 7                    </td>
 8                    <td>
 9                        <asp:Button ID="btnUpAll" runat="server" Text="批量修改" OnClick="btnUpAll_Click" />
10                    </td>
11                    <td>
12                        <asp:Button ID="btnAddBottom" runat="server" Text="添加" OnClick="btnAddBottom_Click" />
13                    </td>
14                </tr>
15            </table>
16         </div>
17        <div>
18         <asp:GridView ID="GriVShow" runat="server" AutoGenerateColumns="False" OnRowDataBound="GriVShow_RowDataBound">
19             <Columns>
20                 <asp:TemplateField>
21                     <HeaderTemplate>
22                         <asp:CheckBox ID="chkAll" runat="server" AutoPostBack="True" OnCheckedChanged="chkAll_CheckedChanged" />
23                     </HeaderTemplate>
24                     <ItemTemplate>
25                         <asp:CheckBox ID="chkItem" runat="server" />
26                     </ItemTemplate>
27                 </asp:TemplateField>
28                 <asp:BoundField DataField="Userid" HeaderText="ID" />
29                 <asp:BoundField DataField="UserName" HeaderText="用户名：" />
30                 <asp:BoundField DataField="phonenum" HeaderText="电话号码" />
31                 <asp:BoundField DataField="qq" HeaderText="QQ" />
32                 <asp:BoundField DataField="phase" HeaderText="班级" />
33                 <asp:BoundField DataField="CreatedTime" HeaderText="创建时间" />
34                 <asp:TemplateField HeaderText="用户名">
35                     <ItemTemplate>
36                         <asp:TextBox ID="txtgvUserName" runat="server" Text='<%#Eval("UserName")%>'></asp:TextBox>
37                     </ItemTemplate>
38                 </asp:TemplateField>
39                 <asp:TemplateField HeaderText="密码">
40                     <ItemTemplate>
41                         <asp:TextBox ID="txtgvPwd" runat="server" Text='<%#Eval("Pwd")%>'></asp:TextBox>
42                     </ItemTemplate>
43                 </asp:TemplateField>
44                 <asp:TemplateField HeaderText="QQ">
45                     <ItemTemplate>
46                         <asp:TextBox ID="txtgvQq" runat="server" Text='<%#Eval("QQ")%>'></asp:TextBox>
47                     </ItemTemplate>
48                 </asp:TemplateField>
49                 <asp:TemplateField HeaderText="班级">
50                     <ItemTemplate>
51                         <asp:DropDownList ID="ddlgvPhase" runat="server" ToolTip='<%#Eval("phase")%>'>
52                             <asp:ListItem>---请选择---</asp:ListItem>
53                             <asp:ListItem>.NET高级班01期</asp:ListItem>
54                             <asp:ListItem>.NET高级班02期</asp:ListItem>
55                             <asp:ListItem>.NET讲师</asp:ListItem>
56                             <asp:ListItem>.NET网站开发01期</asp:ListItem>
57                             <asp:ListItem>.NET网站开发02期</asp:ListItem>
58                             <asp:ListItem>.NET网站开发03期</asp:ListItem>
59                             <asp:ListItem>.NET网站开发04期</asp:ListItem>
60                             <asp:ListItem>.NET网站开发05期</asp:ListItem>
61                             <asp:ListItem>.NET网站开发06期</asp:ListItem>
62                             <asp:ListItem>.NET网站开发07期</asp:ListItem>
63                             <asp:ListItem>.NET网站开发08期</asp:ListItem>
64                             <asp:ListItem>.NET网站开发09期</asp:ListItem>
65                             <asp:ListItem>.NET网站开发10期</asp:ListItem>
66                             <asp:ListItem>.NET网站开发11期</asp:ListItem>
67                             <asp:ListItem>.NET网站开发12期</asp:ListItem>
68                             <asp:ListItem>.NET网站开发13期</asp:ListItem>
69                             <asp:ListItem>.NET网站开发14期</asp:ListItem>
70                             <asp:ListItem>ps设计01期</asp:ListItem>
71                             <asp:ListItem>ps设计03期</asp:ListItem>
72                             <asp:ListItem>网页前端01期</asp:ListItem>
73                         </asp:DropDownList>
74                     </ItemTemplate>
75                 </asp:TemplateField>
76                 <asp:TemplateField HeaderText="详情">
77                     <ItemTemplate>
78                         <a href="UserInforManagerContext.aspx?Id=<%#Eval("UserId")%>">详情</a>
79                     </ItemTemplate>
80                 </asp:TemplateField>
81             </Columns>
82         </asp:GridView>
83     </div>
84     </form>

封装了一个更新的方法，直接调用该方法即可

 1 public void UpUser(int userId,string username,string pwd,string qq,string phase)
 2         {
 3             try
 4             {
 5                 using (con = new SqlConnection(constr))
 6                 {
 7                     con.Open();
 8                     string ssql= string.Format("update UserInfor set UserName='{0}',Pwd='{1}',QQ='{2}',Phase='{3}' where UserId='{4}'", username, pwd, qq, phase, userId);
 9                     cmd = new SqlCommand(ssql, con);
10                         if (cmd.ExecuteNonQuery() > 0)
11                         {
12                             Response.Write("<script>alert('数据更新成功！');</script>");
13                         }
14                 }
15             }
16             catch (Exception)
17             {
18                 Response.Write("网页正在维护!");
19             }
20            
21         }
22         /// <summary>
23         /// 批量更新
24         /// </summary>
25         /// <param name="sender"></param>
26         /// <param name="e"></param>
27         protected void btnUpAll_Click(object sender, EventArgs e)
28         {
29             //遍历gridView
30             foreach (GridViewRow gvr in GriVShow.Rows)
31             {
32                 int UserId = Convert.ToInt32(gvr.Cells[1].Text);
33                 CheckBox chkItem = gvr.Cells[0].FindControl("chkItem") as CheckBox;
34                 if (chkItem.Checked)
35                 {
36                     TextBox txtusername = gvr.Cells[7].FindControl("txtgvUserName") as TextBox;
37                     TextBox txtpwd = gvr.Cells[8].FindControl("txtgvPwd") as TextBox;
38                     TextBox txtqq = gvr.Cells[9].FindControl("txtgvQq") as TextBox;
39                     DropDownList ddlphase = gvr.Cells[10].FindControl("ddlgvPhase") as DropDownList;
40                     UpUser(UserId,txtusername.Text,txtpwd.Text,txtqq.Text,ddlphase.SelectedValue);
41                 }
42             }
43             BindUserInfor();
44         }

5.添加的后台代码：双击添加按钮进入后台事件

 1 protected void btnAdd_Click(object sender, EventArgs e)
 2         {
 3             try
 4             {
 5                 using (con = new SqlConnection(constr))
 6                 {
 7                     con.Open();
 8                     string ssql = string.Format("insert into UserInfor (Phase,CreatedTime) values('{0}','{1}')", "网页前端01期", DateTime.Now.ToString());
 9                     cmd = new SqlCommand(ssql, con);
10                     cmd.ExecuteNonQuery();
11                 }
12                 BindUserInfor(); 
13             }
14             catch (Exception)
15             {
16                 Response.Write("网页正在维护!");
17             }   
18         }