<$BlogRSDURL$>

Sunday, March 28, 2004

ADO.Net: Passing parameters to Stored Procedures 

Microsoft can become real irritating sometime. Quite often you come across issues for which there are not solutions but only workarounds. Today I came across something which I have been able to "solve", but I have no idea what was wrong or what is the real issue. Possibely it is something I do not know and my n00b-iness.

I am using largely MS's SqlHelper class from its Data Access Application Block in my project as a layer to access database(If you do not know what it is, Google for Application Blocks. This along with quite a few others, are quite good code samples to do lots of common things in .Net). Apart from that I have written another wrapper function on top of it to handle various calls. Things seem to going pretty much OK.

Wrapper Function
Public Function ExecuteStoredProcedure(ByVal sStoredProcedure As String, ByVal oarParams As SqlParameter(), ByVal type As QUERYTYPE) As Object
Dim oConnection As SqlConnection = New SqlConnection(sConnString)

If type = QUERYTYPE.READER Then
Dim _oReturnVal As SqlDataReader = SqlHelper.ExecuteReader(oConnection, sStoredProcedure, oarParams)
Return _oReturnVal
ElseIf type = QUERYTYPE.SCALAR Then
Dim _oReturnVal As Integer = SqlHelper.ExecuteScalar(oConnection, sStoredProcedure, oarParams)
Return _oReturnVal
ElseIf type = QUERYTYPE.NONQUERY Then
Dim _oReturnVal As Integer = SqlHelper.ExecuteNonQuery(oConnection, sStoredProcedure, oarParams)
Return _oReturnVal
End If
End Function


And all SqlParameter creation is like this.
SQL Parameters
Dim oarParam As SqlParameter() = {New SqlParameter("ParamName",ParamValue)}

But at couple of places I need to call these functions without using the wrapper like this
Direct Calls
Dim oarParam As SqlParameter() = {New SqlParameter("ParamName",ParamValue)}
Dm oConnection As SqlConnection = New SqlConnection(sConnString)
Dim oRoleTable As DataTable = SqlHelper.GetTable(SqlHelper.ExecuteReader(oConnection, CommandType.StoredProcedure, "spGetRolesByFunctionPage", oarParam))


And this fails becuase there is no "@" in front of "ParamName". I have freaking no idea why I need to put "@" here and not anywhere else. It took me pretty long time figuring this out. With this code the statement kept giving me error ParamName is not an argument of Stored Procedure name. Anybody got any freaking idea what is the actual issue?

0 Comments:

Post a Comment

<< Home

This page is powered by Blogger. Isn't yours?