How to execute SQL Server user-defined function from C# application

Frontend
1

I created a custom function in my SQL Server database and now I need to use it in my C# frontend application. I’m not sure if this is something I can do directly or if there’s a specific way to handle it.

Here’s what I’m working with:

using (SqlConnection conn = new SqlConnection(connectionString))
{
    SqlCommand cmd = new SqlCommand("SELECT dbo.CalculateDiscount(@price, @category)", conn);
    cmd.Parameters.AddWithValue("@price", productPrice);
    cmd.Parameters.AddWithValue("@category", itemCategory);
    conn.Open();
    var result = cmd.ExecuteScalar();
}

Is this the right approach to call my database function from the frontend code? Are there any better methods or things I should be careful about when doing this?

2

looks good, but wrap that connection in a try-catch block. sql functions can throw exceptions and you don’t want your app crashing. also consider using async methods like ExecuteScalarAsync() if this is part of a web app - keeps the ui responsive.

3

Nice setup! What happens if your function returns null though? executeScalar gets weird with nulls. Also, you planning to use this function call anywhere else? I’d probably create a dedicated method so you’re not copying that SQL string all over the place.

4

Your approach works and will call user-defined functions from C# just fine. But I’d switch from AddWithValue to explicitly typed parameters - you’ll get better performance and type safety. Try cmd.Parameters.Add("@price", SqlDbType.Decimal).Value = productPrice instead. Don’t forget error handling, and make sure you’re using the right return type. ExecuteScalar() works great for scalar functions that return single values, but if you’re dealing with table-valued functions, you’ll need ExecuteReader() or fill a DataTable. Also worth validating your parameters first - saves you from pointless database calls when the input’s already bad.