Calling SQL Functions directly from C#

Question

I have a requirement to run a query against a database that will return either a zero or one (Checking for existance of specific criteria). The Tech specs I've been given for review state that I should be creating a stored procedure, that will return a single row, with a single column called "result" that will contain a bit value of 0 or 1. However, I'm not sure that a stored procedure would be the best approach, but am a little unsure so thought I'd ask for you opinions. The two options I can think of are:

1: Create a SQL scalar-valued function that performs the query and returns a bit. This could then be called directly from within the .Net client application using a "TEXT" SqlCommand object, and it would return a bool from the "ExecuteScalar()" method.

2: Create a stored procedure as described in the tech specs. This would then be called from the .Net Client app in the normal manner, and would return a DataTable with a single row and single column, that contains the bit value.

To me, option one seems the best. However, something in the back of my head is saying this isn't such a good idea.

Please could you give your opinions and help relieve my concerns? :)

Cheers, Ian

Answer 1

The calling scalar-valued function is absolutely correct solution.

Answer 2

Execute the Stored Procedure using the ExecuteScalar() method. You can then cast the result of this to a boolean.

e.g

   SqlConnection con = new SqlConnection(connectionString);
    SqlCommand com = new SqlCommand("Execute dbo.usp_MyStoredProc", con);
    return (Boolean)com.ExecuteScalar();

Answer 3

solving it with a stored procedure is better in the long run because it´s more flexible and adaptable

Answer 4

I suppose it depends on the logic the corresponding db function (sp/udf) has to execute.

If for e.g. we are interested in the number of times the particular db function has executed we'd definitely need to do some data manipulation and updates on various tables. Hence we'd have to go for stored procs here. If its a simple retrieval a udf will do.