Call Sql Function Using Ado .net

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

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 asObject = sqlcmd.ExecuteScalar() 
' obj hold now the value from the stored procedure.

Your stored procedure should look like this now,

CREATEPROCEDUREPROCEDURE_NAME
    @param1VARCHAR(15),
    @param2VARCHAR(15)
ASBEGINSELECTfunction_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

CREATEFUNCTIONTest
(
  @p1 varchar(10),
  @p2 varchar(10)
)
RETURNSvarchar(20)
ASBEGINRETURN @p1 + @p2END

vb.net code

Using cnn AsNew SqlClient.SqlConnection("Your Connection String")
  Using cmd AsNew 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 12Catch ex AsException
      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

Usingconnection= New SqlConnection(connectionString))
    SqlCommandcommand= 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.