----------------------------------------------前台部分---------------------------------------------------------------
<div> <asp:TextBox ID="txtkey" runat="server"></asp:TextBox> <asp:Button ID="Button1" runat="server" Text="查询" οnclick="Button1_Click" /><asp:Label ID="Label1" runat="server" Text=""></asp:Label> <div id="divResult" runat="server"></div> </div>
运行后效果如图所示：
----------------------------------------后台部分-----------------------------------
namespace _2013_3_4sql复习 { public partial class 参数替换 : System.Web.UI.Page { protected void Page_Load(object sender, EventArgs e) { if (!IsPostBack) { LoadData(); } } private void LoadData() { string constr=ConfigurationManager.ConnectionStrings["ConnStr"].ConnectionString; SqlConnection conn = new SqlConnection(constr); conn.Open(); SqlCommand cmd = new SqlCommand(); cmd.Connection = conn; cmd.CommandText = "SELECt NewsTitle,SUBSTRINg(NewsContent,1,20)+'....' AS NewsContent,CreateTime From T_News WHERe NewsTitle LIKE @newskey OR NewsContent LIKE @newskey"; #region 参数替换的第一种方式 //cmd.Parameters.AddWithValue("@newskey","%"+txtkey.Text+"%");//如果不加%号，什么东西也不会显示 #endregion #region 参数替换的的第二种方式 //SqlParameter SP = new SqlParameter("@newskey","%"+txtkey.Text+"%"); //cmd.Parameters.Add(SP); #endregion #region 参数替换的第三种方式 SqlParameter sp = new SqlParameter("@newskey",SqlDbType.VarChar); sp.Value = "%"+txtkey.Text+"%"; cmd.Parameters.Add(sp); #endregion DataTable dt = new DataTable(); SqlDataAdapter adapter = new SqlDataAdapter(cmd); adapter.Fill(dt); Label1.Text = "共为你匹配到了"+dt.Rows.Count.ToString()+"条数据"; cmd.Dispose(); conn.Dispose(); StringBuilder sb1 = new StringBuilder(); sb1.Append("<table border=2>"); sb1.Append("<tr><td>新闻标题</td><td>新闻内容</td><td>创建时间</td></tr>"); for (int i = 0; i < dt.Rows.Count; i++) { sb1.Append("<tr>"); sb1.Append("<td>"+dt.Rows[i]["NewsTitle"].ToString()+"</td>"); sb1.Append("<td>"+dt.Rows[i]["NewsContent"].ToString()+"</td>"); sb1.Append("<td>"+Convert.ToDateTime(dt.Rows[i]["CreateTime"]).ToString("yyyy-MM-dd hh:mm:ss")+"</td>"); sb1.Append("</tr>"); } sb1.Append("</table>"); divResult.InnerHtml = sb1.ToString(); }
protected void Button1_Click(object sender, EventArgs e) { LoadData(); } } }
------------------------------使用存储过程完成上面的问题----------------------------------------------------
-----------------存储过程如下---------------------
--参数名称前面一定要加@前缀 CREATE PROC Proc_GetNewsByTitle @newskey varchar(64), @maxcount INT OUT AS SELECt NewsTitle,SUBSTRINg(NewsContent,1,20)+'......' AS NewsContent,CreateTime FROM T_News WHERe NewsTitle LIKE @newskey OR NewsContent LIKE @newskey SET @maxcount=@@ROWCOUNT GO
---------------------------------下面是实现功能的一部分代码，其余的都一样-----------------------------------------
cmd.CommandText = "Proc_GetNewsByTitle"; cmd.CommandType = CommandType.StoredProcedure;//CommandType是枚举 cmd.Parameters.AddWithValue("@newskey", "%" + txttitle.Text + "%"); cmd.Parameters.Add("@maxcount",SqlDbType.Int); cmd.Parameters["@maxcount"].Direction = ParameterDirection.Output; cmd.ExecuteNonQuery(); Label1.Text="共为你匹配到了"+cmd.Parameters["@maxcount"].Value.ToString()+"条数据"; DataTable dt = new DataTable(); SqlDataAdapter adapter = new SqlDataAdapter(cmd); adapter.Fill(dt); cmd.Dispose(); conn.Dispose();