c#调用存储过程,并返回数据集

阿狸Ahri 2013-12-29 06:50:07



 public void ProcessRequest (HttpContext context) {

        context.Response.ContentType = "JSON";

        int index =Convert.ToInt32( context.Request["pageIndex"]);

        SqlConnection con = new SqlConnection("Data Source=.;Initial Catalog=NetShop;Integrated Security=True");

        con.Open();

        string sql = string.Format("select * from NS_Shop_Discuss");

        string namepro = "xp_GetPage ";

        SqlParameter apa1 = new SqlParameter("@sql",SqlDbType.VarChar,255);

        apa1.Value = sql;

        SqlParameter apa2 = new SqlParameter("@page",SqlDbType.Int,64);

        apa2.Value = index;

        SqlParameter apa3 = new SqlParameter("@pageSize", SqlDbType.Int, 64);

        apa3.Value = 5;

        SqlParameter apa4= new SqlParameter("@totalcount", SqlDbType.Int, 64);

        apa4.Direction = ParameterDirection.Output;

        SqlCommand com = new SqlCommand(namepro, con);

        com.CommandType = CommandType.StoredProcedure;

        com.Parameters.Add(apa1);

        com.Parameters.Add(apa2);

        com.Parameters.Add(apa3);

        com.Parameters.Add(apa4);

        SqlDataAdapter ad=new SqlDataAdapter();

        ad.SelectCommand = com;

        DataSet da = new DataSet();

        ad.Fill(da);

        DataTable table = new DataTable();

        table = da.Tables[0];

        string json = CreateJsonParameters(table);

      

        context.Response.Write(json);

    }



CREATE PROCEDURE xp_GetPage  

(   

    @sql varchar(1000),   

    @page int = 1,   

    @pageSize int = 20  

    @totalcount int

)   

AS   

    SET NOCOUNT ON   

    DECLARE @P1 int    --P1是游标的id   

   

    SET @page = (@page-1) * @pagesize + 1   

    EXEC sp_cursoropen @P1 output,@sql   

    EXEC sp_cursorfetch @P1, 16, @page, @pagesize   

    EXEC sp_cursorclose @P1   

   

    --上面的就够了，下面的代码是为了统计总记录条数   

     select @totalCount=@@RowCount   

   

   

GO