首页 > 代码库 > Ado.net[登录，增删改查，Get传值,全选，不选，批量删除,批量更新，添加]
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=http://www.mamicode.com/"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=http://www.mamicode.com/"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=http://www.mamicode.com/"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 }
Ado.net[登录，增删改查，Get传值,全选，不选，批量删除,批量更新，添加]
声明：以上内容来自用户投稿及互联网公开渠道收集整理发布，本网站不拥有所有权，未作人工编辑处理，也不承担相关法律责任，若内容有误或涉及侵权可进行投诉： 投诉/举报 工作人员会在5个工作日内联系你，一经查实，本站将立刻删除涉嫌侵权内容。