1.參數化查詢模糊查詢 sql語句： create proc procegDataAp( @UserName nvarchar(50))asselect * from users where userName=@UserName 給參數賦值 1 <%@ Page Language="C#" Auto ...
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(); } } } }egDataAp.aspx.cs
