ADO.Net or DB Engine?

[Robert Wang]

Hi all,

this post also appears in ado.net group, anyway I appreciate any help
here too.

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

 

[Guest]

try to change this
SqlCommandBuilder.DeriveParameters(cmd);

and insert the parameters with code, like cmd.parameters.add(new
sqlclient.sqlparameter()) i dont remember if its just so, but something like
that...