C#取存储过程的数据

2019-11-17 04:05:41

字体：大中小

来源：转载

供稿：网友

今天自己写了一个分页的存储过程来练习一下，该存储过程一共有7个参数，其中最后一个参数的OUTPUT，用于返回一共得页数

set ANSI_NULLS ON
set QUOTED_IDENTIFIER ON
go

ALTER PROCEDURE [dbo].[PagingProc]
    @PageIndex INT,                --当前页码从0开始
    @PageSize INT,                --每页的大小
    @TableName NVARCHAR(100),    --表名称
    @Orders NVARCHAR(100),        --排序
    @Columns NVARCHAR(100),        --需要检索的列集合，中间用英文逗号隔开e.g.:ID,NAME
    @Filters NVARCHAR(100),        --过滤条件语句
    @TotalPages INT OUTPUT
AS
BEGIN
    DECLARE @SQL NVARCHAR(200)        --查询当前页所有记录的sql语句
    DECLARE @PAGESSQL NVARCHAR(200)    --查询行数的sql语句
    DECLARE @TOTALCOUNT INT            --一共得行数，用于计算所总页数
    SET NOCOUNT ON
    IF @Filters <> ''
        SET @PAGESSQL = 'SELECT @TOTALCOUNT = COUNT(*) FROM ' + @TableName + ' WHERE ' + @Filters
    ELSE
        SET @PAGESSQL = 'SELECT @TOTALCOUNT = COUNT(*) FROM ' + @TableName
    EXEC SP_EXECUTESQL @PAGESSQL, N'@TOTALCOUNT  INT OUT',@TOTALCOUNT OUT
    SET @TotalPages = Ceiling(CONVERT(REAL,@TOTALCOUNT) / CONVERT(REAL,@PageSize))--计算页数
    SET @SQL='SELECT TOP ' + CAST(@PageSize as varchar(10)) + ' ' + @Columns + ' FROM ' + @TableName + '
    WHERE ID NOT IN
    (
        SELECT TOP ' + CAST(@PageIndex * @PageSize as varchar(10)) + ' ID
        FROM ' + @TableName
    IF @Filters <> ''
        SET @SQL = @SQL + ' WHERE ' + @Filters
    IF @Orders <> ''
        SET @SQL = @SQL + ' ORDER BY ' + @ORDERS
    SET @SQL = @SQL + ')'
    IF @Filters <> ''
        SET @SQL = @SQL + ' AND ' + @Filters
    IF @Orders <> ''
    SET @SQL = @SQL + ' ORDER BY ' + @ORDERS
    EXEC(@SQL)
END
下面是C#代码

using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;

namespace PagingProcedure
{
    class Program
    {
        static void Main(string[] args)
        {
            System.Data.SqlClient.SqlConnection conn = new System.Data.SqlClient.SqlConnection();
            conn.ConnectionString = @"Data Source=./SQLEXPRESS;Initial Catalog=Rap_Kevin;Integrated Security=True;";
            System.Data.SqlClient.SqlCommand cmd = conn.CreateCommand();
            cmd.CommandText = "pagingproc";
            cmd.CommandType = System.Data.CommandType.StoredProcedure;

            cmd.Parameters.Add(new System.Data.SqlClient.SqlParameter("@PageIndex", System.Data.SqlDbType.Int));
            cmd.Parameters["@PageIndex"].Value = 0;
            cmd.Parameters.Add(new System.Data.SqlClient.SqlParameter("@PageSize", System.Data.SqlDbType.Int));
            cmd.Parameters["@PageSize"].Value = 5;
            cmd.Parameters.Add(new System.Data.SqlClient.SqlParameter("@TableName", System.Data.SqlDbType.VarChar, 100));
            cmd.Parameters["@TableName"].Value = "Sells";
            cmd.Parameters.Add(new System.Data.SqlClient.SqlParameter("@Orders", System.Data.SqlDbType.VarChar, 100));
            cmd.Parameters["@Orders"].Value = "LastModifyTime DESC";
            cmd.Parameters.Add(new System.Data.SqlClient.SqlParameter("@Columns", System.Data.SqlDbType.VarChar, 100));
            cmd.Parameters["@Columns"].Value = "*";
            cmd.Parameters.Add(new System.Data.SqlClient.SqlParameter("@Filters", System.Data.SqlDbType.VarChar, 100));
            cmd.Parameters["@Filters"].Value = "";
            cmd.Parameters.Add(new System.Data.SqlClient.SqlParameter("@TotalPages", System.Data.SqlDbType.Int));
            cmd.Parameters["@TotalPages"].Value = 0;
            cmd.Parameters["@TotalPages"].Direction = System.Data.ParameterDirection.InputOutput;

            conn.Open();
            System.Data.SqlClient.SqlDataReader reader = cmd.ExecuteReader();
            while (reader.Read())
            {
                System.Console.WriteLine((Guid)reader[0]);
            }
            reader.Close();
            System.Console.WriteLine(cmd.Parameters["@TotalPages"].Value);

            conn.Close();
        }
    }
}

之前弄错了，执行一次是可以获得结果集，同时也能取出OUTPUT参数的值的。

上一篇：大家帮帮忙哈，C#图形设计的

下一篇：E8.net工作流-调查问卷