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 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 Wang" <(E-Mail Removed)> wrote in message
news:(E-Mail Removed)...
>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,

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

Miha Markic [MVP C#] wrote:
> 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
Wang" <(E-Mail Removed)> wrote in message
news:(E-Mail Removed)...

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