参数化查询模糊查询

2018-06-18 04:34:28来源：未知阅读 ()

1.参数化查询模糊查询

sql语句：

create proc procegDataAp
(
@UserName nvarchar(50)
)
as
select * from users where userName=@UserName

给参数赋值

new SqlParameter("@UserName","%"+TxtsUserName.Text.Trim()+"%")

 1 <%@ Page Language="C#" AutoEventWireup="true" CodeBehind="egDataAp.aspx.cs" Inherits="_20160520.egDataAp" %>
 2 
 3 <!DOCTYPE html>
 4 
 5 <html xmlns="http://www.w3.org/1999/xhtml">
 6 <head runat="server">
 7 <meta http-equiv="Content-Type" content="text/html; charset=utf-8"/>
 8     <title></title>
 9 </head>
10 <body>
11     <form id="form1" runat="server">
12     <div>
13         <table>
14             <tr>
15                 <td>用户名：</td>
16                 <td>
17                     <asp:TextBox ID="TxtsUserName" runat="server"></asp:TextBox>
18                     <asp:Button ID="BtnsSel" runat="server" Text="查询" />
19                 </td>
20             </tr>
21         </table>
22           <asp:GridView ID="IdGridView" runat="server" AutoGenerateColumns="False">
23             <Columns>
24                 <asp:BoundField DataField="id" HeaderText="ID" />
25                 <asp:BoundField DataField="username" HeaderText="用户名" />
26                 <asp:BoundField DataField="PWD" HeaderText="密码" />
27                 <asp:BoundField DataField="loginname" HeaderText="姓名" />
28                 <asp:BoundField DataField="qq" HeaderText="QQ" />
29                 <asp:BoundField DataField="classname" HeaderText="班级" />
30                 <asp:TemplateField HeaderText="详情">
31                     <ItemTemplate>
32                         <a href="UserInfo.aspx?id=<%#Eval("ID") %>" target="_blank">详情</a>
33                         <%--<a href='UserInfo.aspx?userid=<%#Eval("UserId") %>' target="_blank">详情</a
34                        <%-- <a href="one.aspx?">详情</a>--%>
35                     </ItemTemplate>
36                 </asp:TemplateField>
37             </Columns>
38         </asp:GridView>
39     </div>
40     </form>
41 </body>
42 </html>

egDataAp.aspx

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.Data;

namespace _20160520
{
    public partial class egDataAp : System.Web.UI.Page
    {
        private string conStr = ConfigurationManager.ConnectionStrings["mySchool"].ToString();
        SqlConnection con = null;//相当于是电话
        SqlCommand cmd = null;//执行sql语句
        SqlDataReader dr = null;//用于储存查询结果
        //首先创建一个DataSet
        DataSet ds = new DataSet();
        protected void Page_Load(object sender, EventArgs e)
        {
            //用数据适配器的方式做一个查询
            con = new SqlConnection(conStr);
            //string ssql = "select ID,username,PWD,loginname,qq,classname from Users";
            //以上是之前的写法，这里我们直接传一个存储过程名
            using (cmd = new SqlCommand("procegDataAp", con))
            {
                //指定一个sqlcommand的CommandType(默认情况下等于CommandType.text)为CommandType的存储过程名
                cmd.CommandType = CommandType.StoredProcedure;
                List<SqlParameter> para = new List<SqlParameter>() 
                {
                    //通过sqlParameter数组把它加到cmd里面去，需指定名称，类型，值
                    //模糊查询
                    new SqlParameter("@UserName","%"+TxtsUserName.Text.Trim()+"%")
                };
                foreach(var a in para)
                {
                    cmd.Parameters.Add(a);
                }
                //创建一个DataAdapter,传一个cmd
                SqlDataAdapter da = new SqlDataAdapter(cmd);
                //应用数据适配器进行填充,填充到ds里
                da.Fill(ds);
                //指定一下数据源,.Tables[0]添加第一个table表
                //IdGridView.DataSource = ds;
                IdGridView.DataSource = ds.Tables[0];
                IdGridView.DataBind();
            }
        }
        
    }
}