C#调用存储过程

来源：互联网发布：sql日期转换成字符格式编辑：程序博客网时间：2024/06/06 04:00

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 ONGOSET QUOTED_IDENTIFIER ONGO-- Batch submitted through debugger: SQLQuery2.sql|7|0|C:\Users\JM\AppData\Local\Temp\~vs60C9.sqlALTER PROCEDURE [dbo].[USP_Product_GetPaged2]    @WhereClause VARCHAR (2000),    @OrderBy VARCHAR (2000),    @PageIndex INT,    @PageSize INTAS    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 ' + @OrderByprint @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

阅读全文

0 0