Call SQL Function using ADO .NET

Question

I want call function created in SQL Server, which receives two parameters and returns an integer. When I call stored procedure, I use the following code:

    sqlcmd.CommandType = CommandType.StoredProcedure
    sqlcmd.CommandText = "PROCEDURE_NAME"

    sqlcmd.Parameters.Add(New SqlClient.SqlParameter("@param1", Utilities.NothingToDBNull(user)))
    sqlcmd.Parameters.Add(New SqlClient.SqlParameter("@param2", Utilities.NothingToDBNull(password)))
    da = New SqlClient.SqlDataAdapter()
    da.SelectCommand = sqlcmd
    table = New DataTable()
    da.Fill(table)

In this case I have a table returned by the stored procedure. What changes if I want use a function that returns a scalar value instead of stored procedure?

Solution 1

You can't call that function directly, only StoredProcedure, Text (query), and TableDirect are allowed. Since you are already exposed with stored procedure, why not create a procedure that has the function on it?

In your C# code, you can use the ExecuteScalar of your command object

sqlcmd.CommandType = CommandType.StoredProcedure
sqlcmd.CommandText = "PROCEDURE_NAME"
sqlcmd.Parameters.Add(New SqlClient.SqlParameter("@param1", Utilities.NothingToDBNull(user)))
sqlcmd.Parameters.Add(New SqlClient.SqlParameter("@param2", Utilities.NothingToDBNull(password)))

Dim obj as Object = sqlcmd.ExecuteScalar() 
' obj hold now the value from the stored procedure.

Your stored procedure should look like this now,

CREATE PROCEDURE PROCEDURE_NAME
    @param1 VARCHAR(15),
    @param2 VARCHAR(15)
AS
BEGIN
    SELECT function_name(@param1, @param2)
    FROM...
    WHERE....
END

Solution 2

If you want to return a single value, you could call the function using a SELECT query

sql server code

CREATE FUNCTION Test
(
  @p1 varchar(10),
  @p2 varchar(10)
)
RETURNS varchar(20)
AS
BEGIN
  RETURN @p1 + @p2
END

vb.net code

Using cnn As New SqlClient.SqlConnection("Your Connection String")
  Using cmd As New SqlClient.SqlCommand("SELECT dbo.Test(@p1,@p2)", cnn)
    cmd.Parameters.AddWithValue("@p1", "1")
    cmd.Parameters.AddWithValue("@p2", "2")

    Try
      cnn.Open()
      Console.WriteLine(cmd.ExecuteScalar.ToString)  //returns 12
    Catch ex As Exception
      Console.WriteLine(ex.Message)
    End Try
  End Using
End Using

Solution 3

If you want to return the value from the stored procedure as a single row, single column result set use the SqlCommand.ExecuteScalar method.

My preferred method is to actually use the return value of the stored procedure, which has been written accordingly with the TSQL RETURN statement, and call SqlCommand.ExecuteNonQuery.

Examples are provided for both on MSDN but for your specific situation,

Dim returnValue As SomeValidType

Using connection = New SqlConnection(connectionString))
    SqlCommand command = New SqlCommand() With _
        {
            CommandType = CommandType.StoredProcedure, _
            CommandText = "PROCEDURE_NAME" _
        }

    command.Parameters.Add(New SqlParameter() With _
        {
            Name = "@RC", _
            DBType = SomeSQLType, _
            Direction = ParameterDirection.ReturnValue  _ // The important bit
        }
    command.AddWithValue("@param1", Utilities.NothingToDBNull(user))
    command.AddWithValue("@param2", Utilities.NothingToDBNull(password))

    command.Connection.Open()
    command.ExecuteNonQuery()

    returnValue = CType(command.Parameters["@RC"].Value, SomeValidType)
End Using

---

As an aside, you'll note that in .Net 4.5 there are handy asynchronous versions of these functions but I fear that is beyond the scope of the question.

Comments

[GVillani82]

SqlCommand command = New SqlCommand(queryString, connection). In this row, what is queryString?

[Jodrell]

@Joseph82, I edited the answer to more closely match your OP.