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>
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(); } } } }