利用存储过程实现查询功能

最新推荐文章于 2024-04-21 19:34:31 发布

成长的小码农

最新推荐文章于 2024-04-21 19:34:31 发布

阅读量1k

点赞数

分类专栏： ado.net

本文链接：https://blog.csdn.net/zhs954838550/article/details/8664840

版权

ado.net 专栏收录该内容

15 篇文章 0 订阅

订阅专栏

存储过程代码

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

前台代码

后台代码

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

成长的小码农

关注

0
点赞
踩
0

收藏

觉得还不错? 一键收藏
0
评论
利用存储过程实现查询功能

存储过程代码CREATE PROC pro_NewsCount@newskey varchar(20),@count int outasselect t1.NewsTitle,SUBSTRING(t1.NewsContent,1,20)+'......' as NewsContent,t1.CreateTimefrom T_News t1 where t1.NewsTitl
复制链接

扫一扫

专栏目录