21.2015.08.13第二十三课ado.net3(增删改查、get传值、post传值、SQL防注入、调存储过程、SQLHELPER)
//登陆前端部分demo.aspx <%@ Page Language="C#" AutoEventWireup="true" CodeBehind="demo.aspx.cs" Inherits="web20150811.demo" %> <!DOCTYPE html> <html xmlns="http://www.w3.org/1999/xhtml"> <head runat="server"> <meta http-equiv="Content-Type" content="text/html; charset=utf-8"/> <title></title> </head> <body> <form id="form1" runat="server"> <table> <tr><td>用户名：</td><td> <asp:TextBox ID="txtUserName" runat="server"></asp:TextBox></td></tr> <tr><td>密码：</td><td> <asp:TextBox ID="txtPwd" runat="server" TextMode="Password"></asp:TextBox></td></tr> <tr><td> <asp:Button ID="btnLogin" runat="server" Text="登录" OnClick="btnLogin_Click" /></td></tr> </table> </form> </body> </html> // 登陆后台部分demo.aspx.cs using System; using System.Collections.Generic; using System.Configuration; using System.Linq; using System.Web; using System.Web.UI; using System.Web.UI.WebControls; using System.Data.SqlClient; using System.Data; namespace web20150811 { public partial class demo : System.Web.UI.Page { protected void Page_Load(object sender, EventArgs e) { } protected void btnLogin_Click(object sender, EventArgs e) { string username = txtUserName.Text.Trim();//获取到用户输入的用户名 string pwd = txtPwd.Text.Trim();//获取到用户输入的密码 if (string.IsNullOrEmpty(username) || string.IsNullOrEmpty(pwd)) { Response.Write("<script>alert('用户名或密码不为空');</script>"); } else { //string connStr = "Database=TestDB;Server=.;Integrated Security=false;Uid=sa;Password=123;";//数据库的而连接信息，相当于电话号码 string connStr = ConfigurationManager.ConnectionStrings["sq_ruanmou"].ToString(); SqlConnection con = new SqlConnection(connStr);//相当于是电话 con.Open();//和数据库建立起连接了 // string strSql = string.Format("select * from UserInfor where UserName='{0}' and Pwd='{1}'", username, pwd);//得当sql语句 //防止sql注入的方法 // string strSql = "select * from UserInfor where UserName=@UserName and Pwd=@Pwd"; SqlParameter[] para = new SqlParameter[] { new SqlParameter("@UserName", SqlDbType.NVarChar, 50), new SqlParameter("@Pwd", SqlDbType.NVarChar, 50) }; para[0].Value = username; para[1].Value = pwd; using (SqlCommand cmd = new SqlCommand("procSelUserInfor", con))//cmd去执行sql语句 { cmd.CommandType = CommandType.StoredProcedure; foreach (var p in para) { cmd.Parameters.Add(p); } using (SqlDataReader read = cmd.ExecuteReader())//执行sql语句，并将执行之后的结果给read { if (read.Read()) { //Response.Write("<script>alert('登录成功');</script>"); Response.Redirect("StuAskM.aspx"); } else { Response.Write("<script>alert('用户名或密码错误');</script>"); } //read.Dispose(); //read.Close(); //con.Dispose(); //con.Close(); } } } } } }
//StuAskM.aspx增删改查前端部分 <%@ Page Language="C#" AutoEventWireup="true" CodeBehind="StuAskM.aspx.cs" Inherits="web20150811.StuAskM" %> <!DOCTYPE html> <html xmlns="http://www.w3.org/1999/xhtml"> <head runat="server"> <meta http-equiv="Content-Type" content="text/html; charset=utf-8"/> <title></title> <link href="css/StuAskM.css" rel="stylesheet" /> </head> <body> <form id="form1" runat="server"> <div> <table><tr><td>帖子ID：</td><td> <asp:TextBox ID="txtSAskId" runat="server" Width="50px"></asp:TextBox></td> <td>标题：</td><td> <asp:TextBox ID="txtSTitle" runat="server"></asp:TextBox></td> <td>分类：</td><td> <asp:DropDownList ID="ddlSCate" runat="server"> <asp:ListItem>---请选择---</asp:ListItem> <asp:ListItem Value="1">网页前端</asp:ListItem> <asp:ListItem Value="2">数据库</asp:ListItem> <asp:ListItem Value="3">.NET</asp:ListItem> <asp:ListItem Value="4">PS</asp:ListItem> </asp:DropDownList></td> <td> <asp:Button ID="btnSel" runat="server" Text="查询" OnClick="btnSel_Click" /></td> </tr></table> </div> <div> <table> <tr><td>标题：</td><td> <asp:TextBox ID="txtATitle" runat="server"></asp:TextBox></td></tr> <tr><td>内容：</td><td> <asp:TextBox ID="txtAText" runat="server" TextMode="MultiLine" Width="200px" Height="100px"></asp:TextBox></td></tr> <tr><td>分类：</td><td> <asp:DropDownList ID="ddlACate" runat="server"> <asp:ListItem>---请选择---</asp:ListItem> <asp:ListItem Value="1">网页前端</asp:ListItem> <asp:ListItem Value="2">数据库</asp:ListItem> <asp:ListItem Value="3">.NET</asp:ListItem> <asp:ListItem Value="4">PS</asp:ListItem> </asp:DropDownList></td></tr> <tr><td>用户ID：</td><td> <asp:TextBox ID="txtAUserIds" runat="server" Width="50px"></asp:TextBox></td></tr> <tr><td colspan="2"> <asp:Button ID="btnAdd" runat="server" Text="添加" OnClick="btnAdd_Click" /></td></tr> </table> </div> <div><table> <tr><td>帖子ID：</td><td> <asp:TextBox ID="txtDAskId" runat="server" style="width: 148px"></asp:TextBox></td><td> <asp:Button ID="btnDel" runat="server" Text="删除" OnClick="btnDel_Click" /></td></tr> </table></div> <div> <table> <tr><td>帖子ID：</td><td> <asp:TextBox ID="txtUAskId" runat="server" style="width: 148px"></asp:TextBox></td></tr> <tr><td>标题：</td><td> <asp:TextBox ID="txtUTitle" runat="server"></asp:TextBox></td></tr> <tr><td> <asp:Button ID="btnUpdate" runat="server" Text="更新" OnClick="btnUpdate_Click" /></td></tr> </table> </div> <div> <asp:GridView ID="GridView1" runat="server" AutoGenerateColumns="False" OnRowDataBound="GridView1_RowDataBound"> <Columns> <asp:BoundField DataField="AskId" HeaderText="ID" /> <asp:BoundField DataField="Title" HeaderText="标题" /> <asp:BoundField DataField="AskCategory" HeaderText="分类" /> <asp:BoundField DataField="CreateTime" HeaderText="创建时间" /> <asp:BoundField DataField="UserId" HeaderText="用户id" /> <asp:TemplateField HeaderText="详情"> <ItemTemplate><a href='AskInfor.aspx?AskId=<%#Eval("AskId") %>'>详情</a></ItemTemplate> </asp:TemplateField> </Columns> </asp:GridView> </div> </form> </body> </html> //StuAskM.aspx.cs 增删改查代码部分 using System; using System.Collections.Generic; using System.Configuration; using System.Data.SqlClient; using System.Linq; using System.Web; using System.Web.UI; using System.Web.UI.WebControls; using System.Text; namespace web20150811 { public partial class StuAskM : System.Web.UI.Page { string connStr = ConfigurationManager.ConnectionStrings["sq_ruanmou"].ToString(); SqlConnection con; SqlCommand cmd; SqlDataReader read; /// <summary> /// 连接数据库 /// </summary> private void OpenDB() { con = new SqlConnection(connStr);//相当于是电话 con.Open(); } /// <summary> /// 得到sql语句 /// </summary> /// <returns></returns> private string GetSql() { StringBuilder sb = new StringBuilder(); sb.Append("select AskId,Title,AskCategory,CreateTime,UserId from StuAsk where 1=1 "); if (!string.IsNullOrEmpty(txtSAskId.Text.Trim())) { sb.Append(string.Format(" and AskId={0}", Convert.ToInt32(txtSAskId.Text.Trim()))); } if (!string.IsNullOrEmpty(txtSTitle.Text.Trim())) { sb.Append(string.Format(" and Title like '%{0}%'", txtSTitle.Text.Trim())); } if (ddlSCate.SelectedIndex > 0) { sb.Append(string.Format(" and AskCategory={0}", Convert.ToInt32(ddlSCate.SelectedValue))); } return sb.ToString(); } /// <summary> /// 数据绑定 /// </summary> private void BindAsk() { OpenDB(); string strSql = GetSql(); using (cmd = new SqlCommand(strSql, con)) { using (read = cmd.ExecuteReader())//对数据库做查询ExecuteReader() { GridView1.DataSource = read; GridView1.DataBind(); } } } protected void Page_Load(object sender, EventArgs e) { BindAsk(); } /// <summary> /// 绑定每一行数据 /// </summary> /// <param name="sender"></param> /// <param name="e"></param> protected void GridView1_RowDataBound(object sender, GridViewRowEventArgs e) { if (e.Row.RowType == DataControlRowType.DataRow)//判断是数据行 { string cate = e.Row.Cells[2].Text; switch (cate) { case "1": e.Row.Cells[2].Text = "网页前端"; break; case "2": e.Row.Cells[2].Text = "数据库"; break; case "3": e.Row.Cells[2].Text = ".NET"; break; case "4": e.Row.Cells[2].Text = "PS"; break; default: break; } DateTime cdate = Convert.ToDateTime(e.Row.Cells[3].Text); e.Row.Cells[3].Text = cdate.ToLongDateString().ToString(); } } /// <summary> /// 查询 /// </summary> /// <param name="sender"></param> /// <param name="e"></param> protected void btnSel_Click(object sender, EventArgs e) { BindAsk(); } /// <summary> /// 添加 /// </summary> /// <param name="sender"></param> /// <param name="e"></param> protected void btnAdd_Click(object sender, EventArgs e) { try { string title = txtATitle.Text.Trim(); string text = txtAText.Text.Trim(); int cate = ddlACate.SelectedIndex > 0 ? Convert.ToInt32(ddlACate.SelectedValue) : 1; int userid = string.IsNullOrEmpty(txtAUserIds.Text.Trim()) ? 0 : Convert.ToInt32(txtAUserIds.Text.Trim()); OpenDB(); string strSql = string.Format("insert into StuAsk(Title,Text,AskCategory,CreateTime,UserId) values('{0}','{1}',{2},'{3}',{4})", title, text, cate, DateTime.Now.ToString(), userid); using (cmd = new SqlCommand(strSql, con)) { cmd.ExecuteNonQuery();//对数据库进行增加、删除、修改ExecuteNonQuery } BindAsk(); Response.Write("<script>alert('添加成功');</script>"); } catch (Exception ex) { Response.Write("网站正在维修中..."); } } /// <summary> /// 删除 /// </summary> /// <param name="sender"></param> /// <param name="e"></param> protected void btnDel_Click(object sender, EventArgs e) { int askid = Convert.ToInt32(txtDAskId.Text.Trim()); //string strSql = string.Format("select AskId from StuAsk where AskId={0} ", askid); //using (cmd = new SqlCommand(strSql, con)) //{ // using (read = cmd.ExecuteReader()) // { // if (!read.Read()) // { // Response.Write("<script>alert('这个帖子不存在');</script>"); // } // else { // } // } //} string strSql = string.Format("select COUNT(*) from StuAsk where AskId={0}", askid); using (cmd = new SqlCommand(strSql, con)) { if (Convert.ToInt32(cmd.ExecuteScalar().ToString()) == 0) { Response.Write("<script>alert('这个帖子不存在');</script>"); } else { string s = string.Format("delete StuAsk where AskId={0}", askid); using (cmd = new SqlCommand(s, con)) { cmd.ExecuteNonQuery(); } } } BindAsk(); Response.Write("<script>alert('删除成功');</script>"); } /// <summary> /// 更新 /// </summary> /// <param name="sender"></param> /// <param name="e"></param> protected void btnUpdate_Click(object sender, EventArgs e) { int askid = Convert.ToInt32(txtUAskId.Text.Trim()); string title = txtUTitle.Text.Trim(); string strSql = string.Format("update StuAsk set Title='{0}' where AskId={1}", title, askid); using (cmd = new SqlCommand(strSql, con)) { cmd.ExecuteNonQuery(); } BindAsk(); Response.Write("<script>alert('更新成功');</script>"); } } }
//前端部分 <%@ Page Language="C#" AutoEventWireup="true" CodeBehind="AskInfor.aspx.cs" Inherits="web20150811.AskInfor" %> <!DOCTYPE html> <html xmlns="http://www.w3.org/1999/xhtml"> <head runat="server"> <meta http-equiv="Content-Type" content="text/html; charset=utf-8"/> <title></title> <link href="css/StuAskM.css" rel="stylesheet" /> </head> <body> <form id="form1" runat="server"> <div> <%=GetAskInfor() %> </div> </form> </body> </html> //代码部分 using System; using System.Collections.Generic; using System.Linq; using System.Web; using System.Web.UI; using System.Web.UI.WebControls; using System.Text; using System.Configuration; using System.Data.SqlClient; using System.Data; namespace web20150811 { public partial class AskInfor : System.Web.UI.Page { string connStr = ConfigurationManager.ConnectionStrings["sq_ruanmou"].ToString(); SqlConnection con; SqlCommand cmd; SqlDataReader read; /// <summary> /// 连接数据库 /// </summary> private void OpenDB() { con = new SqlConnection(connStr);//相当于是电话 //con.Open(); } /// <summary> ///获取传过来的AskId /// </summary> private int _askId; public int AskId { get { try { _askId = Request.QueryString["AskId"] == null ? 0 : Convert.ToInt32(Request.QueryString["AskId"].ToString()); } catch { _askId = 0; } return _askId; } set { _askId = value; } } public string GetAskInfor() { OpenDB(); StringBuilder sb = new StringBuilder(); if (AskId > 0) { string strSql = string.Format(@"select UI.RealName,UI.UserName,UI.Phase,SA.AskId,SA.Title,SA.Text from UserInfor UI inner join StuAsk SA on UI.UserId=SA.UserId where AskId={0}", AskId); using (cmd = new SqlCommand(strSql, con)) { SqlDataAdapter ada = new SqlDataAdapter(cmd); //DataSet ds = new DataSet(); //ada.Fill(ds); //DataTable dt = ds.Tables[0]; DataTable dt=new DataTable(); ada.Fill(dt); if (dt.Rows.Count > 0) { sb.Append("<table>"); sb.Append(string.Format("<tr><td>用户姓名：</td><td>{0}</td></tr>", dt.Rows[0][0].ToString())); sb.Append(string.Format("<tr><td>用户名：</td><td>{0}</td></tr>", dt.Rows[0][1].ToString())); sb.Append(string.Format("<tr><td>用户班级：</td><td>{0}</td></tr>", dt.Rows[0][2].ToString().ToString())); sb.Append(string.Format("<tr><td>帖子内容：</td><td>{0}</td></tr>", dt.Rows[0][5].ToString().ToString())); sb.Append(string.Format("<tr><td>帖子ID：</td><td>{0}</td></tr>", AskId)); sb.Append(string.Format("<tr><td>帖子标题：</td><td>{0}</td></tr>", dt.Rows[0][4].ToString().ToString())); sb.Append("</table>"); } else { sb.Append("暂无数据"); } //using (read = cmd.ExecuteReader()) //{ // if (read.Read()) // { // sb.Append("<table>"); // sb.Append(string.Format("<tr><td>用户姓名：</td><td>{0}</td></tr>", read["RealName"].ToString())); // sb.Append(string.Format("<tr><td>用户名：</td><td>{0}</td></tr>", read["UserName"].ToString())); // sb.Append(string.Format("<tr><td>用户班级：</td><td>{0}</td></tr>", read["Phase"].ToString())); // sb.Append(string.Format("<tr><td>帖子内容：</td><td>{0}</td></tr>", read["Text"].ToString())); // sb.Append(string.Format("<tr><td>帖子ID：</td><td>{0}</td></tr>", AskId)); // sb.Append(string.Format("<tr><td>帖子标题：</td><td>{0}</td></tr>", read["Title"].ToString())); // sb.Append("</table>"); // } // else // { // sb.Append("暂无数据"); // } //} } } else { sb.Append("暂无数据"); } return sb.ToString(); } protected void Page_Load(object sender, EventArgs e) { } } }
POST传值方式
<?xml version="1.0" encoding="utf-8"?> <!-- 有关如何配置 ASP.NET 应用程序的详细信息，请访问 http://go.microsoft.com/fwlink/?LinkId=169433 --> <configuration> <connectionStrings> <add name="sq_ruanmou" connectionString="Database=sq_ruanmou;Server=.;Integrated Security=false;Uid=sa;Password=123;" providerName="System.Data.SqlClient"/> </connectionStrings> <system.web> <compilation debug="true" targetFramework="4.5" /> <httpRuntime targetFramework="4.5" /> </system.web> </configuration>
body { font-size: 14px; font-family: "微软雅黑"; } div { margin-bottom:10px; } table { border-collapse:collapse; } td { text-align: center; border:solid 1px #cccccc; }
//前端部分,先要将SqlHelper类导入工程内 <%@ Page Language="C#" AutoEventWireup="true" CodeBehind="SqlHelperDemo.aspx.cs" Inherits="web20150811.SqlHelperDemo" %> <!DOCTYPE html> <html xmlns="http://www.w3.org/1999/xhtml"> <head runat="server"> <meta http-equiv="Content-Type" content="text/html; charset=utf-8"/> <title></title> </head> <body> <form id="form1" runat="server"> <div> <table> <tr><td>用户名：</td><td> <asp:TextBox ID="txtUserName" runat="server"></asp:TextBox></td></tr> <tr><td>密码：</td><td><asp:TextBox ID="txtPwd" runat="server" TextMode="Password"></asp:TextBox></td></tr> <tr><td> <asp:Button ID="btnLogin" runat="server" Text="登录" OnClick="btnLogin_Click" /></td></tr> </table> </div> </form> </body> </html> //代码部分 using System; using System.Collections.Generic; using System.Data; using System.Data.SqlClient; using System.Linq; using System.Web; using System.Web.UI; using System.Web.UI.WebControls; namespace web20150811 { public partial class SqlHelperDemo : System.Web.UI.Page { protected void Page_Load(object sender, EventArgs e) { } protected void btnLogin_Click(object sender, EventArgs e) { string username = txtUserName.Text.Trim(); string pwd = txtPwd.Text.Trim(); //string strSql = string.Format("select * from UserInfor where UserName='{0}' and pwd='{1}'", username, pwd); string strSql = "select * from UserInfor where UserName=@UserName and Pwd=@Pwd"; SqlParameter[] para = new SqlParameter[] { new SqlParameter("@UserName", SqlDbType.NVarChar, 50), new SqlParameter("@Pwd", SqlDbType.NVarChar, 50) }; para[0].Value = username; para[1].Value = pwd; if (SqlHelper.Exists(strSql,para)) { Response.Write("<script>alert('登陆成功');</script>"); } else{ Response.Write("<script>alert('用户名或密码错误');</script>"); } } } }
