ExecuteReader and output params

  • Thread starter Thread starter shimonsim
  • Start date Start date
S

shimonsim

Hi
I am trying to run SP using ExecuteReader for SqlCommand object. SP has
Output parameters that I add to Parameters collections but it seems that
output parameter is always null. Debugging SP return not null values so it
is not a problem with SP.
Does ExecuteReader skips output parameters?
Thanks,
Shimon.
 
As William says, you have to define the parameters on the client and mark
them as output or input-output (ParameterDirection)

Also, keep in mind that output parameter values are populated only after you
close the datareader.

--
Pablo Castro
Program Manager - ADO.NET Team
Microsoft Corp.

This posting is provided "AS IS" with no warranties, and confers no rights.
 
Thanks
I did the way you offered and it worked. The only issue is that my project
is using MS Data Access Block. It seems to me that I can't use this block
for this kind of tasks.
Thanks,
Shimon.
 
You can add a method and expand upon it so that you could. The Data Access
Block isn't the end all of DataAccess, it's a framework.
 
I understand that it is just a framework. But as I understand in order to
accomplish my task I need to create instance of the object with Command as
object private variable create property for Parameters Collection... but in
the framework all the methods are static. All that I would be doing looks
totally contradicting basic design. For other programmers will be not
obvious at all (in the beginning.
But of cause it cold be done. I just wanted to know that I am not missing
any point. And it actually can't be done with existing methods.
Thanks,
Shimon.
 
Shimon:

No, I think you are missing the point. All you need to do is create and add
Paramters to add to the parameters collection. You can pass them in from
Anywhere, or you could add them in your calling code, and pass in the
command. I'm missing you about the All methods are static in the Framework.
The Paramters collection is not a static property, it's an instance one.
Show us your existing stuff and where you have the problem, there's
definitely a way to get there from here.

I'll be up for a while...so let me know.

Cheers,

Bill
 
I am not sure if we are talking about the same thing. I will try to break my
points and see where you agree and where not.
1. MS Data Access Block class SqlHelper has only static methods.
2. In this block Command object is always local variable so it is not
accessible for client of SqlHelper class
3. Because of 2. client of SqlHelper can't get Parameters collection after
DataReader is closed.
The problem is that I can't get output parameters from SqlHelper
So as the result I can't use this Block to access parameters after
DataReader is closed.

To create my owns solution I need to do something like this
(C#)
public class SqlHelper
{
SqlCommand cmd = new SqlCommand;
public SqlParameterCollection Parameters
{
get{return cmd.Parameters};
}
public SqlDataReader ExecuteReader (string conString, CommandType type,
string commandString, SqlParameter[] sqlParams)
{
SqlConnection con= new SqlConnection(conString);
cmd.CommandText= commandString;
cmd.CommandType=type;
cmd.Connection=con;
//fill the parameters
foreach(SqlParameter p in sqlParams)
{
//some checking probably should be done...
cmd.Parameters.Add(p);
}
con.Open();
SqlDataReader rd=cmd.ExecuteReader();
return rd;
}
}
Client uses this method to get SqlDataReader does with it whatever he wants,
closes it and retrieves parameters from Parameters property (or sqlParams)
 
If you want to use the MS Data Access Application Block you could change it's code so it doesn't detach the parameters from the command right after executing. I didn't look at the code to
see the implications of that, but in the worse case it may need some refactorization. (actually, that's the nice thing about the application blocks, you get the code, so you can change it :)

--
Pablo Castro
Program Manager - ADO.NET Team
Microsoft Corp.

This posting is provided "AS IS" with no warranties, and confers no rights.


--------------------
From: "(e-mail address removed)" <[email protected]>
References: <#Rgdnp#[email protected]> <#98w68#[email protected]> <[email protected]> <ejHP41$eDHA.1748
Click to expand...
@TK2MSFTNGP10.phx.gbl> said:
Subject: Re: ExecuteReader and output params
Date: Tue, 16 Sep 2003 08:16:51 -0400
Lines: 153
X-Priority: 3
X-MSMail-Priority: Normal
X-Newsreader: Microsoft Outlook Express 6.00.2800.1158
X-MimeOLE: Produced By Microsoft MimeOLE V6.00.2800.1165
Message-ID: <[email protected]>
Newsgroups: microsoft.public.dotnet.framework.adonet
NNTP-Posting-Host: 108.newark-08rh15rt.nj.dial-access.att.net 12.89.174.108
Path: cpmsftngxa07.phx.gbl!cpmsftngxa10.phx.gbl!TK2MSFTNGXA06.phx.gbl!TK2MSFTNGXA05.phx.gbl!TK2MSFTNGP08.phx.gbl!tk2msftngp13.phx.gbl
Xref: cpmsftngxa07.phx.gbl microsoft.public.dotnet.framework.adonet:61076
X-Tomcat-NG: microsoft.public.dotnet.framework.adonet

I am not sure if we are talking about the same thing. I will try to break my
points and see where you agree and where not.
1. MS Data Access Block class SqlHelper has only static methods.
2. In this block Command object is always local variable so it is not
accessible for client of SqlHelper class
3. Because of 2. client of SqlHelper can't get Parameters collection after
DataReader is closed.
The problem is that I can't get output parameters from SqlHelper
So as the result I can't use this Block to access parameters after
DataReader is closed.

To create my owns solution I need to do something like this
(C#)
public class SqlHelper
{
SqlCommand cmd = new SqlCommand;
public SqlParameterCollection Parameters
{
get{return cmd.Parameters};
}
public SqlDataReader ExecuteReader (string conString, CommandType type,
string commandString, SqlParameter[] sqlParams)
{
SqlConnection con= new SqlConnection(conString);
cmd.CommandText= commandString;
cmd.CommandType=type;
cmd.Connection=con;
//fill the parameters
foreach(SqlParameter p in sqlParams)
{
//some checking probably should be done...
cmd.Parameters.Add(p);
}
con.Open();
SqlDataReader rd=cmd.ExecuteReader();
return rd;
}
}
Client uses this method to get SqlDataReader does with it whatever he wants,
closes it and retrieves parameters from Parameters property (or sqlParams)

William Ryan said:
Shimon:

No, I think you are missing the point. All you need to do is create and add
Paramters to add to the parameters collection. You can pass them in from
Anywhere, or you could add them in your calling code, and pass in the
command. I'm missing you about the All methods are static in the Framework.
The Paramters collection is not a static property, it's an instance one.
Show us your existing stuff and where you have the problem, there's
definitely a way to get there from here.

I'll be up for a while...so let me know.

Cheers,

Bill
Click to expand...
Click to expand...
 
Back
Top