Tuesday, January 25, 2011

How to get SQL Server stored procedure output parameters in C#?

How to get SQL Server stored procedure output parameters in C#?
Two part of codes for this:
First: C#

    public static DataSet Get(out int RowCount)
    {
        SqlCommand selectCommand = new SqlCommand();
        selectCommand.Connection = connection;
        selectCommand.CommandType = CommandType.StoredProcedure;
        selectCommand.CommandText = "SP_OUPUT";
 
        RowCount = 0;
        var p = selectCommand.Parameters.AddWithValue("@RETURNCOUNT", RowCount);
        p.Direction = ParameterDirection.Output;
 
        SqlDataAdapter adapter = new SqlDataAdapter(selectCommand);
        DataSet dataSet = new DataSet("Results");
        adapter.Fill(dataSet);
        RowCount = Convert.ToInt32(selectCommand.Parameters["@TotalSize"].Value);
        return dataSet;
    }

Second part: Store Procedure
CREATE PROCEDURE [SP_OUPUT]
        @RETURNCOUNT        INT OUTPUT

AS
  BEGIN
SELECT * FROM TABLENAME
SET @RETURNCOUNT = @@ROWCOUNT
  END

No comments:

Post a Comment