你的位置:首页 > ASP.net教程

[ASP.net教程]参数化查询模糊查询


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 "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">详情</a34            <%-- <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();      }    }      }}

egDataAp.aspx.cs