Stored procedure with output parameter

[ashtek]

Hi,
I have a generic function that executes a stored procedure & returns a
data table.
Code:
===
public static DataTable ExecuteStoredProcedure(string
strProc,SqlParameter[] paramArray)
{
SqlCommand command = new SqlCommand(strProc, connection);
command.CommandType = CommandType.StoredProcedure;
for(int i=0;i<paramArray.Length;i++)
command.Parameters.Add(paramArray);
SqlDataAdapter adapter = new SqlDataAdapter(command);
DataTable table = new DataTable();
adapter.Fill(table);
connection.Close();
return(table);
}

The stored proc is used for paging, it takes page number & page size as
input parameters & returns the records for a particular page.

This code works fine and I use the data table returned by this function
to bind a datagrid and also to do some other stuff during the
itemdatabound of the grid.

Now I want the stored proc to return the total number of records (it
returns only n records of a particular page) in the database. I can not
use data table's rowcount coz it will always give the page size 10),
not the total number of records in the database.

So I added an OUTPUT parameter to the stored proc which returns the
total number of records as well.

Now my problem is how do I change this function to take care of this
output parameter.
I can add the output parameter to the parameter array and execute the
stored proc but the data table will not contain the output paramerter's
value. I can get it using command.parameter("@out") but then I can not
return both the data table & this value from the function.

Is there any way I can tweak this function to return both the values? I
want the calling function to receive both the output paramer's value
and also the records returned by the stored proc.

Thanks,
Ashish.

 

[Marc Gravell]

You need to change the parameter's direction to InputOutput; the default is
Input. Then it *will* be updated as long as the SP also declares it as OUT.
And for ref, I'm 99% that you can get both table data a result value - you
just need to add a result parameter - I believe that means a parameter with
direction ReturnValue.

Marc

 

[DeveloperX]

Couple of options, either add an out parameter to the function

private void footest {
int i;
foo(out i);
Console.WriteLine(i.ToString());
}
private bool foo(out int pFoo)
{
pFoo = 1;
return true;
}

Alternatively create an object or structure to hold both the DataTable
and the count and return that.
public class Holder
{
private DataTable _table;
private int _count;
....
}

 

[Marc Gravell]

Sorry - I misread the question; in my example, the caller *will* see the
update, but in their array of the parameters. You can also use separate
parameters, but then you need to watch for conflicts - i.e. what happens if
the caller has already added a ReturnValue parameter - then you can't add
your own, etc.

Marc

 

[ashtek]

Thank you for your reply Marc.

I am calling this function like this:
========================================

SqlParameter[] paramArray=new SqlParameter[3];

SqlParameter param1=new SqlParameter();
param1.Direction=ParameterDirection.Input;
param1.ParameterName="@CurrentPage";
param1.Value=iCurPageNumber;
paramArray[0]=(param1);

SqlParameter param2=new SqlParameter();
param2.Direction=ParameterDirection.Input;
param2.ParameterName="@PageSize";
param2.Value=iPageSize;
paramArray[1]=(param2);

SqlParameter param3=new SqlParameter();
param3.Direction=ParameterDirection.Output;
param3.ParameterName="@RowCount";
param3.Size=10;
paramArray[2]=(param3);

dataTable=DAC.ExecuteStoredProcedure1("pr_GetRecords",paramArray);

========================================

Are you saying that I have to use
param3.Direction=ParameterDirection.ReturnValue; ?

If I do this, I get an error saying that the proc expects a parameter
"@out" which was not supplied.

-Ashish.

 

[ashtek]

My stored procedure looks like this:

CREATE PROCEDURE [dbo].[pr_ProcName]
@CurrentPage INT=NULL,
@PageSize INT=NULL,
@RowCount INT OUTPUT
AS
------------------
some code...
......
.......
.......
-----------------

SELECT * FROM #tmp_Tbl

SELECT @RowCount=COUNT(PKTMP) FROM #tmp_Tbl

Drop table #tmp_Tbl

-Ashish.

 

[ashtek]

It is working fine with the existing code.
Yor are right Marc, the parameter (param3) is updated with the output
parameter's value from the stored proc.
iTotRecords=Convert.ToInt32(param3.Value.ToString());
I was thinking that this value will not be available in the calling
function as I am passing this parameter to another function.
Thank you again,
Ashish

Thank you for your reply Marc.

I am calling this function like this:
========================================

SqlParameter[] paramArray=new SqlParameter[3];

SqlParameter param1=new SqlParameter();
param1.Direction=ParameterDirection.Input;
param1.ParameterName="@CurrentPage";
param1.Value=iCurPageNumber;
paramArray[0]=(param1);

SqlParameter param2=new SqlParameter();
param2.Direction=ParameterDirection.Input;
param2.ParameterName="@PageSize";
param2.Value=iPageSize;
paramArray[1]=(param2);

SqlParameter param3=new SqlParameter();
param3.Direction=ParameterDirection.Output;
param3.ParameterName="@RowCount";
param3.Size=10;
paramArray[2]=(param3);

dataTable=DAC.ExecuteStoredProcedure1("pr_GetRecords",paramArray);

========================================

Are you saying that I have to use
param3.Direction=ParameterDirection.ReturnValue; ?

If I do this, I get an error saying that the proc expects a parameter
"@out" which was not supplied.

-Ashish.

You need to change the parameter's direction to InputOutput; the default is
Input. Then it *will* be updated as long as the SP also declares it as OUT.
And for ref, I'm 99% that you can get both table data a result value - you
just need to add a result parameter - I believe that means a parameter with
direction ReturnValue.

Marc

 

[ashtek]

Thank you for your suggestion. I was not aware of the out parameter to
a function.
My code is working as it is, I was just confused!
Thanks,
Ashish

Couple of options, either add an out parameter to the function

private void footest {
int i;
foo(out i);
Console.WriteLine(i.ToString());
}
private bool foo(out int pFoo)
{
pFoo = 1;
return true;
}

Alternatively create an object or structure to hold both the DataTable
and the count and return that.
public class Holder
{
private DataTable _table;
private int _count;
...
}

Hi,
I have a generic function that executes a stored procedure & returns a
data table.
Code:
===
public static DataTable ExecuteStoredProcedure(string
strProc,SqlParameter[] paramArray)
{
SqlCommand command = new SqlCommand(strProc, connection);
command.CommandType = CommandType.StoredProcedure;
for(int i=0;i<paramArray.Length;i++)
command.Parameters.Add(paramArray);
SqlDataAdapter adapter = new SqlDataAdapter(command);
DataTable table = new DataTable();
adapter.Fill(table);
connection.Close();
return(table);
}

The stored proc is used for paging, it takes page number & page size as
input parameters & returns the records for a particular page.

This code works fine and I use the data table returned by this function
to bind a datagrid and also to do some other stuff during the
itemdatabound of the grid.

Now I want the stored proc to return the total number of records (it
returns only n records of a particular page) in the database. I can not
use data table's rowcount coz it will always give the page size 10),
not the total number of records in the database.

So I added an OUTPUT parameter to the stored proc which returns the
total number of records as well.

Now my problem is how do I change this function to take care of this
output parameter.
I can add the output parameter to the parameter array and execute the
stored proc but the data table will not contain the output paramerter's
value. I can get it using command.parameter("@out") but then I can not
return both the data table & this value from the function.

Is there any way I can tweak this function to return both the values? I
want the calling function to receive both the output paramer's value
and also the records returned by the stored proc.

Thanks,
Ashish.