你的位置:首页 > ASP.net教程

[ASP.net教程]Ado.net[登录,增删改查,Get传值,全选,不选,批量删除,批量更新,添加]


[虽然说,开发的时候,我们可以使用各种框架,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       try107       {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           else127           {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       try143       {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           else163           {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           else24           {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 UserId12     {13       //get,set是属性,既可以访问,又可以写14       get15       {16         try17         {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       try38       {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         else64         {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       try106       {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           else126           {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       try153       {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           else173           {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       try194       {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           else212           {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它的事件源的对象把它变成checkBox226       CheckBox chkAll = sender as CheckBox;227       //2.对它的每一行进行遍历循环228       foreach (GridViewRow gvr in GriVShow.Rows)229       {230         //2.1获得到第一行的第一列,找到每一列id等于chkitem的对象把它变成checkbox231         CheckBox chkItem = gvr.Cells[0].FindControl("chkItem") as CheckBox;232         //2.2让它当前全选的checked属性等于下面每一行的属性,选中就为true,没选就为Fulse233         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的对象把它变成checkbox245         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       try16       {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       //遍历gridView30       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     }