存储过程代码
CREATE PROC pro_NewsCount
@newskey varchar(20),
@count int out
as
select t1.NewsTitle,SUBSTRING(t1.NewsContent,1,20)+'......' as NewsContent,t1.CreateTime
from T_News t1 where t1.NewsTitle like @newskey or t1.NewsContent like @newskey
select @count=COUNT(*) from T_News where T_News.NewsTitle like @newskey or T_News.NewsContent like @newskey
go
前台代码
<html xmlns="http://www.w3.org/1999/xhtml">
<head runat="server">
<title></title>
</head>
<body>
<form id="form1" runat="server">
<asp:TextBox ID="TextBox1" runat="server"></asp:TextBox>
<asp:Button ID="Button1" runat="server" Text="Button" οnclick="Button1_Click" />
<asp:Label ID="Label1" runat="server" Text="Label"></asp:Label>
<div id="divResult" runat="server">
</div>
</form>
</body>
</html>
后台代码
using System;
using System.Collections.Generic;
using System.Linq;
using System.Web;
using System.Web.UI;
using System.Web.UI.WebControls;
using System.Configuration;
using System.Data.SqlClient;
using System.Data;
using System.Text;
namespace 新闻系统3
{
public partial class zhuanhuan : System.Web.UI.Page
{
string constr = ConfigurationManager.ConnectionStrings["constr"].ConnectionString;
protected void Page_Load(object sender, EventArgs e)
{
if (!IsPostBack)
{
DataLoad();
}
}
private void DataLoad()
{
using (SqlConnection conn = new SqlConnection(constr))
{
using (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";
//转换函数第一种方式
//SqlParameter param = new SqlParameter("@newskey", "%"+TextBox1.Text+"%");
//cmd.Parameters.Add(param);
cmd.CommandText = "pro_NewsCount";
//转换函数第二种方式
SqlParameter param = new SqlParameter("@newskey", SqlDbType.VarChar);
param.Value = "%" + TextBox1.Text + "%";
cmd.Parameters.Add(param);
SqlParameter paramcount = new SqlParameter("@count", SqlDbType.Int);
paramcount.Direction = ParameterDirection.Output;
cmd.Parameters.Add(paramcount);
cmd.CommandType = CommandType.StoredProcedure;
using (SqlDataAdapter adapter = new SqlDataAdapter(cmd))
{
DataTable dt = new DataTable();
//adapter.Fill(dt);
int count = adapter.Fill(dt);
Label1.Text = "为您找到" + paramcount.Value.ToString() + "条符合条件的记录";
//Response.Write(count);
StringBuilder sb1 = new StringBuilder();
sb1.Append("<table>");
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"] + "</td>");
sb1.Append("<td>" + dt.Rows[i]["NewsContent"] + "</td>");
sb1.Append("<td>" + dt.Rows[i]["CreateTime"] + "</td>");
sb1.Append("</tr>");
}
sb1.Append("</table>");
divResult.InnerHtml = sb1.ToString();
}
}
}
}
protected void Button1_Click(object sender, EventArgs e)
{
DataLoad();
}
}
}