C#取存儲(chǔ)過(guò)程的數(shù)據(jù)

2019-11-17 04:05:41

字體：大中小

供稿：網(wǎng)友

今天自己寫(xiě)了一個(gè)分頁(yè)的存儲(chǔ)過(guò)程來(lái)練習(xí)一下，該存儲(chǔ)過(guò)程一共有7個(gè)參數(shù)，其中最后一個(gè)參數(shù)的OUTPUT，用于返回一共得頁(yè)數(shù)

set ANSI_NULLS ON
set QUOTED_IDENTIFIER ON
go

ALTER PROCEDURE [dbo].[PagingProc]
    @PageIndex INT,                --當(dāng)前頁(yè)碼從0開(kāi)始
    @PageSize INT,                --每頁(yè)的大小
    @TableName NVARCHAR(100),    --表名稱(chēng)
    @Orders NVARCHAR(100),        --排序
    @Columns NVARCHAR(100),        --需要檢索的列集合，中間用英文逗號(hào)隔開(kāi)e.g.:ID,NAME
    @Filters NVARCHAR(100),        --過(guò)濾條件語(yǔ)句
    @TotalPages INT OUTPUT
AS
BEGIN
    DECLARE @SQL NVARCHAR(200)        --查詢(xún)當(dāng)前頁(yè)所有記錄的sql語(yǔ)句
    DECLARE @PAGESSQL NVARCHAR(200)    --查詢(xún)行數(shù)的sql語(yǔ)句
    DECLARE @TOTALCOUNT INT            --一共得行數(shù)，用于計(jì)算所總頁(yè)數(shù)
    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))--計(jì)算頁(yè)數(shù)
    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();
        }
    }
}

之前弄錯(cuò)了，執(zhí)行一次是可以獲得結(jié)果集，同時(shí)也能取出OUTPUT參數(shù)的值的。

上一篇：大家?guī)蛶兔珻#圖形設(shè)計(jì)的

下一篇：E8.net工作流-調(diào)查問(wèn)卷