C#调用存储过程

1、

 SqlConnection conn = new SqlConnection("Data Source=.;Initial Catalog=ZKWebForm121920170831(删减版);User ID=sa;Password=123");
            conn.Open();
            SqlCommand command = conn.CreateCommand();
            //设置执行语句类型为存储过程  
            command.CommandType = CommandType.StoredProcedure;
            //指定存储过程名字           
            command.CommandText = "[dbo].[USP_Product_GetPaged2]";
            command.Parameters.Add("@WhereClause", SqlDbType.VarChar, 2000).Value = DBNull.Value;
            command.Parameters.Add("@OrderBy", SqlDbType.VarChar, 2000).Value = "ID DESC";
            command.Parameters.Add("@PageIndex", SqlDbType.Int).Value = 0;
            command.Parameters.Add("@PageSize", SqlDbType.Int).Value = 10;

            SqlDataReader sdr = command.ExecuteReader();

            DataTable dt = new DataTable();
            dt.Columns.Add(new DataColumn("ID"));
            dt.Columns.Add(new DataColumn("ProjectName"));
            dt.Columns.Add(new DataColumn("ContractNumber"));

            DataRow dr = dt.NewRow();
            int i = 0;
            while (sdr.Read())
            {
                //添加行列
                dr = dt.NewRow();
                dr["ID"] = sdr[0];
                dr["ProjectName"] = sdr[1];
                dr["ContractNumber"] = sdr[2];
                dt.Rows.Add(dr);

                //读取行列
                //dr["ID"] = dt.Rows[i]["ID"] = sdr[1];
                //dt.Rows.Add(dt.Rows[i]["ID"]);
                //dt.Rows.Add(dt.Rows[i]["ProjectName"]);              
            }
            GridView1.DataSource = dt;
            GridView1.DataBind();

存储过程：

USE [ZKWebForm121920170831(删减版)]
GO
/****** Object:  StoredProcedure [dbo].[USP_Product_GetPaged2]    Script Date: 09/28/2017 15:24:47 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
-- Batch submitted through debugger: SQLQuery2.sql|7|0|C:\Users\JM\AppData\Local\Temp\~vs60C9.sql
ALTER PROCEDURE [dbo].[USP_Product_GetPaged2]
    @WhereClause VARCHAR (2000),
    @OrderBy VARCHAR (2000),
    @PageIndex INT,
    @PageSize INT
AS
    BEGIN
        DECLARE @PageLowerBound INT, @PageUpperBound INT

        SET @PageLowerBound = @PageSize * @PageIndex
        SET @PageUpperBound = @PageLowerBound + @PageSize

        CREATE TABLE #PageIndex
        (
            [IndexID] INT IDENTITY (1, 1) NOT NULL,
            [ID] INT 
        )

        DECLARE @SQL AS NVARCHAR(4000)

        SET @SQL = 'INSERT INTO #PageIndex ([ID])'
        SET @SQL = @SQL + ' SELECT'
        IF @PageSize > 0
            SET @SQL = @SQL + ' TOP ' + CONVERT(NVARCHAR, @PageUpperBound)
        SET @SQL = @SQL + ' [ID]'
        SET @SQL = @SQL + ' FROM [dbo].[LbtProjectInfo]'
        IF LEN(@WhereClause) > 0
            SET @SQL = @SQL + ' WHERE ' + @WhereClause
        IF LEN(@OrderBy) > 0
            SET @SQL = @SQL + ' ORDER BY ' + @OrderBy
print @sql
        EXEC (@SQL)

        SELECT
            TempTable.[ID], 
            TempTable.[ProjectName], 
            TempTable.[ContractNumber]
        FROM
            [dbo].[LbtProjectInfo] TempTable
        INNER JOIN
            #PageIndex PageIndex
        ON
            TempTable.[ID] = PageIndex.[ID]
        WHERE
            PageIndex.IndexID > @PageLowerBound
        AND
            PageIndex.IndexID <= @PageUpperBound
        ORDER BY
            PageIndex.IndexID

---       查询项目信息的总条数
--        SET @SQL = 'SELECT COUNT(*) AS TotalRowCount'
--        SET @SQL = @SQL + ' FROM [dbo].[LbtProjectInfo]'
--        IF LEN(@WhereClause) > 0
--        SET @SQL = @SQL + ' WHERE ' + @WhereClause
---        打印sql语句
--         print @sql
--         EXEC (@SQL)
   
    END