confused - exec sp with pure output parameter in ADO.Net

  • Thread starter Thread starter Robert Wang
  • Start date Start date
R

Robert Wang

I created an sp in northwind in SQL server 2000,

CREATE proc [dbo].[up_getCount]
@Count int output
as
select @Count=count(*)
from northwind.dbo.customers


And ran the following C# code:

using (SqlConnection conn = new
SqlConnection("Server=localhost;Integrated Security=true"))
{
conn.Open();
SqlCommand cmd = new SqlCommand("Northwind.dbo.up_getCount", conn);

cmd.CommandType = CommandType.StoredProcedure;

SqlCommandBuilder.DeriveParameters(cmd);
cmd.ExecuteNonQuery(); // exception got here
}

I got an exception in the line specified. Later I checked out that
cmd.Parameters[1].Direction=ParameterDirection.InputOutput after
SqlCommandBuilder.DeriveParameters was executed. After digging into the
problem, I found several solutions:
1. set cmd.Parameters[1].Direction = ParameterDirection.Output. this
will prevent the value of @Count being checked upon
2. set cmd.Parameters[1].Value = any number or System.DBNull.Value.
3. set the default value for the parameter @Count in the sp, namely,
@Count int = 1 output

these solutions just made me even more confused. I mean in my first
example, who is responsible for detecting the direction and value of the
SqlParameter, the ADO.Net or the database engine? I don't think it is
the ADO.Net because otherwise solution 3 will not work. if it is the db
engine, then what happened when ADO.Net send a System.DBNull to the
dbengine? is is just a "null"?

Can anyone help? Thanks very much.

Robert
MCDBA, MCSD for .Net
 
Hi Robert,

Why don't you set your parameter in code?
You'll gain speed and more readable code.
 
Miha,

Thanks for your suggestion. Anyway, I'm aware of the performance issue
here. I wrote the code just to discover how
SqlCommandBuilder.DeriveParameters works. ^_^

Robert
Hi Robert,

Why don't you set your parameter in code?
You'll gain speed and more readable code.
Click to expand...

-- Miha Markic [MVP C#] - RightHand .NET consulting & development
www.rthand.com Blog: http://cs.rthand.com/blogs/blog_with_righthand/
SLODUG - Slovene Developer Users Group www.codezone-si.info "Robert
I created an sp in northwind in SQL server 2000,

CREATE proc [dbo].[up_getCount]
@Count int output
as
select @Count=count(*)
from northwind.dbo.customers


And ran the following C# code:

using (SqlConnection conn = new SqlConnection("Server=localhost;Integrated
Security=true"))
{
conn.Open();
SqlCommand cmd = new SqlCommand("Northwind.dbo.up_getCount", conn);

cmd.CommandType = CommandType.StoredProcedure;

SqlCommandBuilder.DeriveParameters(cmd);
cmd.ExecuteNonQuery(); // exception got here
}

I got an exception in the line specified. Later I checked out that
cmd.Parameters[1].Direction=ParameterDirection.InputOutput after
SqlCommandBuilder.DeriveParameters was executed. After digging into the
problem, I found several solutions:
1. set cmd.Parameters[1].Direction = ParameterDirection.Output. this will
prevent the value of @Count being checked upon
2. set cmd.Parameters[1].Value = any number or System.DBNull.Value.
3. set the default value for the parameter @Count in the sp, namely,
@Count int = 1 output

these solutions just made me even more confused. I mean in my first
example, who is responsible for detecting the direction and value of the
SqlParameter, the ADO.Net or the database engine? I don't think it is the
ADO.Net because otherwise solution 3 will not work. if it is the db
engine, then what happened when ADO.Net send a System.DBNull to the
dbengine? is is just a "null"?

Can anyone help? Thanks very much.

Robert
MCDBA, MCSD for .Net
Click to expand...
Click to expand...
 
Back
Top