Will Ado.net pass to a stored procedure just the value or also the type?

Question

hello,

<asp:SqlDataSource ID="sourceEmployees" runat="server"
     ProviderName="System.Data.SqlClient"
     ConnectionString="<%$ ConnectionStrings:Northwind %>"
     SelectCommand="SELECT EmployeeID, FirstName,LastName,
     Title, City FROM Employees WHERE City=@City">
         <SelectParameters>
             <asp:Parameter Name="City"/>
         </SelectParameters>
</asp:SqlDataSource>

A) I assume that when you don’t specify of which type Parameter instance “City” is, it is automatically of type Object, which means it can later be assigned value of any type. Thus if “City” is later ( say inside SqlDataSource2_Selecting() event handler ) assigned a value of a wrong type, this wrong assignment will only be detected on Sql server, and not before ( of course Sql server will report that error back to web server )?

B) If we create a SqlParameter instance of type NVarChar(20) and want to pass this parameter to a stored procedure, will Ado.net pass to a stored procedure just the value of this parameter, or will it also somehow inform the procedure the exact type of this parameter ( which is NVarChar(20))?

thanx

Answer 1

The data type where the parameter value is stored is always object. If you don't specify the type, the driver will guess the database data type based on the data type of the value assigned to it, so it's safer to specify the type that you want.

The data type (NVarChar) may be passed in to the database, of that I am not really sure, but the length is not. The length specified does only limit the data that is sent to the database, it doesn't have to match the length specified in the stored procedure (but it normally should). If you use a length that is longer than that specified in the stored procedure, the driver may allow to send a value that is too long for the stored procedure to accept.

Regardless if the data type is sent to the database or not, it's used to send the value in the correct way.

Answer 2

The parameters are set by SQL Server and I'm fairly certain the type specified by the Parameter is not passed along with the data when ADO actually communicates with the server. So yes, it's entirely possible to specify the wrong datatype and you won't know until SQL Server returns an error.

Likewise for B... only the data is sent.

Answer 3

No, sql server will not be told the type of the parameter in a stored procedure call. If you pass a parameter in, it will try and convert it to the correct type and complain if it cannot do the conversion.