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

[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

 

[Miha Markic [MVP C#]]

Hi Robert,

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

 

[Robert Wang]

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.

-- 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