sqlHelper做增删改查,SQL注入处理，存储值，cookie，session

 1                if (SqlHelper.Exists(sSql, para))
 2                 {
 3                     //把session保存安全系数高的东西,保存于浏览器缓存里
 4                     Session["name"] = username;
 5                     Response.Redirect("HomeAdoSqlHelper.aspx");
 6                 }
 7                 else
 8                 {
 9                     Response.Write("<script>alert(‘密码错误！‘);</script>");
10                 }
11             }

 1        protected void Page_Load(object sender, EventArgs e)
 2         {
 3             //cookie获取状态
 4             if (Request.Cookies["name"] == null)
 5             {
 6                 Response.Redirect("loginSqlHelper.aspx");
 7             }
 8             if (!(IsPostBack))
 9             {
10                 BindUserInfor();
11             }
12         }

 1          protected void Page_Load(object sender, EventArgs e)
 2         {
 3             //session
 4             if (Session["name"] == null)
 5             {
 6                 Response.Redirect("loginSqlHelper.aspx");
 7             }
 8             if (!(IsPostBack))
 9             {
10                 BindUserInfor();
11             }
12         }

二、sqlHelper做增删改查,SQL注入处理[后台绑定下拉一定要对应]

  1     <form id="form1" runat="server">
  2        <div>
  3           <table>
  4               <tr>
  5                   <td> 用户名:</td>
  6                   <td>
  7                       <asp:TextBox ID="txtSUserName" runat="server"></asp:TextBox>
  8                   </td>
  9                   <td>班级：</td>
 10                   <td>
 11                       <asp:DropDownList ID="ddlselPhase" runat="server">
 12                           <asp:ListItem>---请选择---</asp:ListItem>
 13                           <asp:ListItem>.NET高级班01期</asp:ListItem>
 14                           <asp:ListItem>.NET高级班02期</asp:ListItem>
 15                           <asp:ListItem>.NET讲师</asp:ListItem>
 16                           <asp:ListItem>.NET网站开发01期</asp:ListItem>
 17                           <asp:ListItem>.NET网站开发02期</asp:ListItem>
 18                           <asp:ListItem>.NET网站开发03期</asp:ListItem>
 19                           <asp:ListItem>.NET网站开发04期</asp:ListItem>
 20                           <asp:ListItem>.NET网站开发05期</asp:ListItem>
 21                           <asp:ListItem>.NET网站开发06期</asp:ListItem>
 22                           <asp:ListItem>.NET网站开发07期</asp:ListItem>
 23                           <asp:ListItem>.NET网站开发08期</asp:ListItem>
 24                           <asp:ListItem>.NET网站开发09期</asp:ListItem>
 25                           <asp:ListItem>.NET网站开发10期</asp:ListItem>
 26                           <asp:ListItem>.NET网站开发11期</asp:ListItem>
 27                           <asp:ListItem>.NET网站开发12期</asp:ListItem>
 28                           <asp:ListItem>.NET网站开发13期</asp:ListItem>
 29                           <asp:ListItem>.NET网站开发14期</asp:ListItem>
 30                           <asp:ListItem>.NET网站开发15期</asp:ListItem>
 31                           <asp:ListItem>.NET网站开发16期</asp:ListItem>
 32                           <asp:ListItem>java第一期</asp:ListItem>
 33                           <asp:ListItem>JAVA讲师</asp:ListItem>
 34                           <asp:ListItem>ps设计01期</asp:ListItem>
 35                           <asp:ListItem>ps设计02期</asp:ListItem>
 36                           <asp:ListItem>ps设计03期</asp:ListItem>
 37                           <asp:ListItem>网页前端01期</asp:ListItem>
 38                       </asp:DropDownList>
 39                   </td>
 40                   <td>
 41                       <asp:Button ID="btnSel" runat="server" Text="查询" OnClick="btnSel_Click" style="height: 21px" />
 42                   </td>
 43               </tr>
 44           </table>
 45        </div>
 46        <div>
 47             <table>
 48               <tr>
 49                   <td> 用户名:</td>
 50                   <td>
 51                       <asp:TextBox ID="txtAddUserName" runat="server"></asp:TextBox>
 52                   </td>
 53                   <td> 密码:</td>
 54                   <td>
 55                       <asp:TextBox ID="txtAddPwd" runat="server" TextMode="Password"></asp:TextBox>
 56                   </td>
 57                   <td> QQ:</td>
 58                   <td>
 59                       <asp:TextBox ID="txtAddQq" runat="server"></asp:TextBox>
 60                   </td>
 61                   <td>班级：</td>
 62                   <td>
 63                       <asp:DropDownList ID="ddlAddPhase" runat="server">
 64                           <asp:ListItem>---请选择---</asp:ListItem>
 65                           <asp:ListItem>.NET高级班01期</asp:ListItem>
 66                           <asp:ListItem>.NET高级班02期</asp:ListItem>
 67                           <asp:ListItem>.NET讲师</asp:ListItem>
 68                           <asp:ListItem>.NET网站开发01期</asp:ListItem>
 69                           <asp:ListItem>.NET网站开发02期</asp:ListItem>
 70                           <asp:ListItem>.NET网站开发03期</asp:ListItem>
 71                           <asp:ListItem>.NET网站开发04期</asp:ListItem>
 72                           <asp:ListItem>.NET网站开发05期</asp:ListItem>
 73                           <asp:ListItem>.NET网站开发06期</asp:ListItem>
 74                           <asp:ListItem>.NET网站开发07期</asp:ListItem>
 75                           <asp:ListItem>.NET网站开发08期</asp:ListItem>
 76                           <asp:ListItem>.NET网站开发09期</asp:ListItem>
 77                           <asp:ListItem>.NET网站开发10期</asp:ListItem>
 78                           <asp:ListItem>.NET网站开发11期</asp:ListItem>
 79                           <asp:ListItem>.NET网站开发12期</asp:ListItem>
 80                           <asp:ListItem>.NET网站开发13期</asp:ListItem>
 81                           <asp:ListItem>.NET网站开发14期</asp:ListItem>
 82                           <asp:ListItem>ps设计01期</asp:ListItem>
 83                           <asp:ListItem>ps设计03期</asp:ListItem>
 84                           <asp:ListItem>网页前端01期</asp:ListItem>
 85                       </asp:DropDownList>
 86                   </td>
 87                   <td>
 88                       <asp:Button ID="btnAdd" runat="server" Text="添加" OnClick="btnAdd_Click" />
 89                   </td>
 90               </tr>
 91           </table>
 92         </div>
 93        <div>
 94             <table>
 95                 <tr>
 96                     <td>用户ID:</td>
 97                     <td>
 98                         <asp:TextBox ID="txtDUId" runat="server"></asp:TextBox>
 99                     </td>
100                     <td>
101                         <asp:Button ID="btnD" runat="server" Text="删除" OnClick="btnD_Click" />
102                     </td>
103                 </tr>
104             </table>
105         </div>
106        <%--<div>
107            <table>
108                <tr>
109                    <td>ID:</td>
110                    <td>
111                        <asp:TextBox ID="txtUId" runat="server"></asp:TextBox>
112                    </td>
113                    <td>
114                        <asp:TextBox ID="txtUUserName" runat="server"></asp:TextBox>
115                    </td>
116                    <td>
117                        <asp:Button ID="btnU" runat="server" Text="更新" OnClick="btnU_Click" />
118                    </td>
119                </tr>
120            </table>
121        </div>--%>
122        <div>
123            <table>
124                <tr>
125                    <td>ID:</td>
126                    <td>
127                        <asp:TextBox ID="txtUuid" runat="server"></asp:TextBox>
128                    </td>
129                    <td>
130                        <asp:TextBox ID="txtUuserName2" runat="server"></asp:TextBox>
131                    </td>
132                    <td>
133                        <asp:Button ID="btnU2" runat="server" Text="更新" OnClick="btnU2_Click"/>
134                    </td>
135                </tr>
136            </table>
137        </div>
138        <div class="dItem">
139            <table>
140                <tr>
141                    <td>
142                        <asp:Button ID="btnDelAll" runat="server" Text="批量删除" OnClick="btnDelAll_Click" OnClientClick="return confirm(‘你确定删除吗？‘);"/>
143                    </td>
144                    <td>
145                        <asp:Button ID="btnUpAll" runat="server" Text="批量修改" OnClick="btnUpAll_Click" />
146                    </td>
147                    <td>
148                        <asp:Button ID="btnAllAdd" runat="server" Text="添加" OnClick="btnAllAdd_Click"/>
149                    </td>
150                </tr>
151            </table>
152         </div>
153        <div>
154         <asp:GridView ID="GriVShow" runat="server" AutoGenerateColumns="False" OnRowDataBound="GriVShow_RowDataBound">
155             <Columns>
156                 <asp:TemplateField>
157                     <HeaderTemplate>
158                         <asp:CheckBox ID="chkAll" runat="server" AutoPostBack="True" OnCheckedChanged="chkAll_CheckedChanged" />
159                     </HeaderTemplate>
160                     <ItemTemplate>
161                         <asp:CheckBox ID="chkItem" runat="server" />
162                     </ItemTemplate>
163                 </asp:TemplateField>
164                 <asp:BoundField DataField="Userid" HeaderText="ID" />
165                 <asp:BoundField DataField="UserName" HeaderText="用户名：" />
166                 <asp:BoundField DataField="phonenum" HeaderText="电话号码" />
167                 <asp:BoundField DataField="qq" HeaderText="QQ" />
168                 <asp:BoundField DataField="phase" HeaderText="班级" />
169                 <asp:BoundField DataField="CreatedTime" HeaderText="创建时间" />
170                 <asp:TemplateField HeaderText="用户名">
171                     <ItemTemplate>
172                         <asp:TextBox ID="txtgvUserName" runat="server" Text=‘<%#Eval("UserName")%>‘></asp:TextBox>
173                     </ItemTemplate>
174                 </asp:TemplateField>
175                 <asp:TemplateField HeaderText="密码">
176                     <ItemTemplate>
177                         <asp:TextBox ID="txtgvPwd" runat="server" Text=‘<%#Eval("Pwd")%>‘></asp:TextBox>
178                     </ItemTemplate>
179                 </asp:TemplateField>
180                 <asp:TemplateField HeaderText="QQ">
181                     <ItemTemplate>
182                         <asp:TextBox ID="txtgvQq" runat="server" Text=‘<%#Eval("QQ")%>‘></asp:TextBox>
183                     </ItemTemplate>
184                 </asp:TemplateField>
185                 <asp:TemplateField HeaderText="班级">
186                     <ItemTemplate>
187                         <asp:DropDownList ID="ddlgvPhase" runat="server" ToolTip=‘<%#Eval("phase")%>‘>
188                             <asp:ListItem>---请选择---</asp:ListItem>
189                             <asp:ListItem>.NET高级班01期</asp:ListItem>
190                             <asp:ListItem>.NET高级班02期</asp:ListItem>
191                             <asp:ListItem>.NET讲师</asp:ListItem>
192                             <asp:ListItem>.NET网站开发01期</asp:ListItem>
193                             <asp:ListItem>.NET网站开发02期</asp:ListItem>
194                             <asp:ListItem>.NET网站开发03期</asp:ListItem>
195                             <asp:ListItem>.NET网站开发04期</asp:ListItem>
196                             <asp:ListItem>.NET网站开发05期</asp:ListItem>
197                             <asp:ListItem>.NET网站开发06期</asp:ListItem>
198                             <asp:ListItem>.NET网站开发07期</asp:ListItem>
199                             <asp:ListItem>.NET网站开发08期</asp:ListItem>
200                             <asp:ListItem>.NET网站开发09期</asp:ListItem>
201                             <asp:ListItem>.NET网站开发10期</asp:ListItem>
202                             <asp:ListItem>.NET网站开发11期</asp:ListItem>
203                             <asp:ListItem>.NET网站开发12期</asp:ListItem>
204                             <asp:ListItem>.NET网站开发13期</asp:ListItem>
205                             <asp:ListItem>.NET网站开发14期</asp:ListItem>
206                             <asp:ListItem>ps设计01期</asp:ListItem>
207                             <asp:ListItem>ps设计03期</asp:ListItem>
208                             <asp:ListItem>网页前端01期</asp:ListItem>
209                         </asp:DropDownList>
210                     </ItemTemplate>
211                 </asp:TemplateField>
212                 <asp:TemplateField HeaderText="详情">
213                     <ItemTemplate>
214                         <a href="UserInforManagerContext.aspx?Id=<%#Eval("UserId")%>">详情</a>
215                     </ItemTemplate>
216                 </asp:TemplateField>
217             </Columns>
218         </asp:GridView>
219     </div>
220     </form>

  1 namespace sqlHelperStudy2160907
  2 {
  3     public partial class HomeAdoSqlHelperFirst : System.Web.UI.Page
  4     {
  9         SqlParameter[] para=new SqlParameter[2];
 10         protected void Page_Load(object sender, EventArgs e)
 11         {
 12             if (!(IsPostBack))
 13             {
 14                 BindUserInfor();
 15             }
 16         }
 17         public void BindUserInfor()
 18         {
 19             try
 20             {
 21                 DataTable dt = SqlHelper.ExecuteDataSetText(GetSql(), para).Tables[0];
 22                 GriVShow.DataSource = dt;
 23                 GriVShow.DataBind();
 24             }
 25             catch (Exception ex)
 26             {
 27                 Response.Write("<script>alret(‘系统正在维护，请联系管理员!‘);</script>");
 28             }
 29         }
 30         public string GetSql()
 31         {
 32             StringBuilder sb = new StringBuilder();
 33             //sb.Append("select Userid,UserName,phonenum,sex,phase,qq,Message,HeadPic,CreatedTime from UserInfor where 1=1");
 34             sb.Append("select top 100 * from UserInfor where 1=1 ");
 35             if (!string.IsNullOrEmpty(txtSUserName.Text.Trim()))
 36             {
 37                 //sb.Append(string.Format("and UserName=‘{0}‘", txtSUserName.Text.Trim()));
 38                 sb.Append("and UserName=@UserName");
 39                 para[0] = new SqlParameter("@UserName",txtSUserName.Text.Trim());
 40             }
 41             if (ddlselPhase.SelectedIndex > 0)
 42             {
 43                 sb.Append("and phase=@phase");
 44                 para[1] = new SqlParameter("@phase",ddlselPhase.SelectedValue);
 45                 //sb.Append(string.Format("and phase=‘{0}‘", ddlselPhase.SelectedValue));
 46             }
 47             return sb.ToString();
 48         }
 49 
 50         protected void btnSel_Click(object sender, EventArgs e)
 51         {
 52             BindUserInfor();
 53         }
 54 
 55         protected void chkAll_CheckedChanged(object sender, EventArgs e)
 56         {
 57             //1.当我们点击按钮时，去找chkAll它的事件源的对象把它变成checkBox
 58             CheckBox chkAll = sender as CheckBox;
 59             //2.对它的每一行进行遍历循环
 60             foreach (GridViewRow gvr in GriVShow.Rows)
 61             {
 62                 //2.1获得到第一行的第一列，找到每一列id等于chkitem的对象把它变成checkbox
 63                 CheckBox chkItem = gvr.Cells[0].FindControl("chkItem") as CheckBox;
 64                 //2.2让它当前全选的checked属性等于下面每一行的属性，选中就为true，没选就为Fulse
 65                 chkItem.Checked = chkAll.Checked;
 66             }
 67         }
 68         protected void GriVShow_RowDataBound(object sender, GridViewRowEventArgs e)
 69         {
 70             if (e.Row.RowType == DataControlRowType.DataRow)
 71             {
 72                 DropDownList ddlphase = e.Row.FindControl("ddlgvPhase") as DropDownList;
 73                 string phase = ddlphase.ToolTip;
 74                 if (!string.IsNullOrEmpty(phase))
 75                 {
 76                     ddlphase.ClearSelection();
 77                     ddlphase.Items.FindByValue(phase).Selected = true;
 78                 }
 79             }
 80         }
 81 
 82         protected void btnAdd_Click(object sender, EventArgs e)
 83         {
 84             string username = txtAddUserName.Text.Trim();
 85             string pwd = txtAddPwd.Text.Trim();
 86             string qq = txtAddQq.Text.Trim();
 87             string phase = ddlAddPhase.SelectedIndex > 0 ? ddlAddPhase.SelectedValue : "";
 88             if (!string.IsNullOrEmpty(username))
 89             {
 90                 string sSql = string.Format("insert into UserInfor(UserName,Pwd,QQ,Phase)values(@UserName,@Pwd,@QQ,@Phase)");
 91                 SqlParameter[] paraA = new SqlParameter[] 
 92                 {
 93                       new SqlParameter("@UserName",username),
 94                       new SqlParameter("@Pwd",pwd),
 95                       new SqlParameter("@QQ",qq),
 96                       new SqlParameter("@Phase",phase)
 97                 };
 98                 SqlHelper.ExecteNonQueryText(sSql, paraA);
 99                 BindUserInfor();
100             } 
101         }
102         public void Del(int UserId)
103         {
104             try
105             {
106                     //string ssql = string.Format("select UserId from UserInfor where UserId=‘{0}‘", UserId);
107                     string ssql = "select UserId from UserInfor where UserId=@UserId";
108                     SqlParameter[] paraD = new SqlParameter[] { new SqlParameter("@UserId", UserId) };
109                     if(SqlHelper.Exists(ssql,paraD))
110                     {
111                         string sSql1 = string.Format("delete UserInfor where UserId=@UserId");
112                         if(SqlHelper.ExecteNonQueryText(sSql1,paraD)>0)
113                         {
114                            Response.Write("<script>alert(‘删除成功！‘);</script>");
115                         }
116                         BindUserInfor();
117                     }
118                     else
119                     {
120                         Response.Write("该用户不存在！");
121                     }
122             }
123             catch (Exception ex)
124             {
125                 Response.Write("<script>alret(‘系统正在维护，请联系管理员!‘);</script>");
126             }
127         }
128         protected void btnD_Click(object sender, EventArgs e)
129         {
130             int UserId = txtDUId.Text.Trim() == "" ? 0 : Convert.ToInt32(txtDUId.Text.Trim());
131             Del(UserId);
132             BindUserInfor();
133         }
134 
135         protected void btnU2_Click(object sender, EventArgs e)
136         {
137             int Uid2 = txtUuid.Text.Trim() == "" ? 0 : Convert.ToInt32(txtUuid.Text.Trim());
138             string uusername2 = txtUuserName2.Text.Trim();
139             try
140             {
141                     string sSql = string.Format("select count(*) from UserInfor where UserId=@UserId");
142                    SqlParameter[] paraU = new SqlParameter[] { new SqlParameter("@UserId", Uid2) };
143                     int icount = Convert.ToInt32(SqlHelper.ExecuteScalar(CommandType.Text,sSql,paraU));
144                     if (icount > 0)
145                     {
146                         string sSql1 = string.Format("update UserInfor set UserName=@UserName where UserId=@UserId");
147                         //string ssql1 = "update UserInfor set UserName=@UserName where UserId=@UserId";
148                         SqlParameter[] paraU1 = new SqlParameter[] 
149                         { 
150                             new SqlParameter("@UserName",uusername2),
151                             new SqlParameter("@UserId",Uid2)
152                         };
153                         if (SqlHelper.ExecteNonQuery