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 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 }
本文来自博客园,作者:wangwangwangMax,转载请注明原文链接:https://www.cnblogs.com/wangwangwangMax/p/5797778.html