Learn more about SQL Server tools

mssqltips logo
 

Tutorials          DBA          Dev          BI          Career          Categories          Webcasts          Whitepapers          Today's Tip          Join

Tutorials      DBA      Dev      BI      Categories      Webcasts

DBA    Dev    BI    Categories

 

Working with SQL Server Functions and .NET


By:   |   Last Updated: 2018-12-13   |   Comments (4)   |   Related Tips: More > Application Development

Problem

In previous .NET Application Development tips, we've learned how to get started with .NET and SQL Server data access. To this end, we've learned how to connect to SQL Server from a C# program and run a simple query, as well as how to query SQL Server tables from .NET and process the results. Moreover, we've learned how to work with SQL Server stored procedures from within .NET applications. In this tip, we continue this journey and we will learn how to work with SQL Server functions from within a .NET Application.

Solution

There is more than one type of SQL Server function. Currently, in SQL Server 2017, the function types are:

  • Table-valued Functions
  • Scalar-valued Functions
  • Aggregate Functions
  • System Functions

In this tip, we are going to see 3 different examples of calling 3 of the above types of SQL Server functions from within our .NET application. Note that we won't examine Aggregate Functions, because we would need to create a CLR assembly for this, which is something that will be covered in a future tip.

Sample Database

Just like in my previous tips, all examples will be based on the database "SampleDB" which can be found on a test SQL Server 2017 named instance on my local machine, which is called "SQL2K17".

Here's a screenshot of the SQL Server instance, as it can be seen in SSMS:

Sample database used in this tip

The sample database has two tables named "employees" and "location", as well as the below two user-defined functions:

  • fnGetEmployeeInfo (table-valued function)
  • fnGetTotalEmployees (scalar-valued function)

Let's take a look at their DDL script:

Function "fnGetEmployeeInfo" DDL Script:

USE [SampleDB]
GO

SET ANSI_NULLS ON
GO

SET QUOTED_IDENTIFIER ON
GO


CREATE FUNCTION [dbo].[fnGetEmployeeInfo]
(
    @empID INT
)
RETURNS TABLE
AS
RETURN
(
    SELECT e.id,
           e.code,
           e.firstName,
           e.lastName,
           e.locationID,
           l.code AS locationCode,
           l.descr AS localDescr
    FROM dbo.employees e
        INNER JOIN dbo.location l
            ON l.id = e.locationID
         WHERE e.id=@empID
);
GO

Function "fnGetTotalEmployees" DDL Script:

USE [SampleDB]
GO

SET ANSI_NULLS ON
GO

SET QUOTED_IDENTIFIER ON
GO


CREATE FUNCTION [dbo].[fnGetTotalEmployees]
(
   @locationID INT
)
RETURNS INT
AS
BEGIN
   
   DECLARE @result INT
   SELECT @result=(SELECT COUNT(*) FROM dbo.employees WHERE locationID=@locationID);

   -- Return the result of the function
   RETURN @result

END
GO

The fnGetEmployeeInfo function, takes as an input parameter the employee ID and returns employee-related information.

The fnGetTotalEmployees function, takes an input parameter the location ID and returns the number of all employees for the specific location.

We will use the above two functions in our examples, as well as the system function "GETDATE()".

The system function "GETDATE()", as the name implies, returns the current date and time.

Sample Data

The sample database has two tables named "employees" and "location". In the below screenshot, you can take a look at the data currently stored in these two tables.

Sample data used for this tip

Calling the SQL Server Functions from a .NET Application

The next step in our example, is to write a .NET application, preferable in C#, that connects to the database server and properly calls the 3 functions.

This example, is based on the examples originally presented in my previous .NET Application Development tips.

One of the main points, again like in the case of stored procedures, is that I will make use of the .NET Class SqlParameter in order to write more secure code, thus minimizing the risk for SQL injections.

Connecting to the SQL Server Instance – Connection String

This is my connection string:

string connString = @"Server =.\SQL2K17; Database = SampleDB; Trusted_Connection = True;";

Using the above connection string, I will connect to the named instance "SQL2K17" on the local machine, using a trusted connection, that is, with my windows account.

Calling SQL Server Function "fnGetEmployeeInfo"

In the below code example, I'm presenting the full .NET code for a C# console application that calls the function "fnGetEmployeeInfo" and process the results.

Note: Prior to start writing the code, similarly to my previous tips on .NET Application Development, you will need to create a new Visual C# project in Visual Studio, and select the "Console App (.NET Framework)" template. In my example, I named the project " TestApp4" and saved it in the "C:\temp\demos" folder on my local machine (a new subfolder with the name of the project was created).

Here's the code:

using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Threading.Tasks;
using System.Data.SqlClient;
using System.Data;

namespace TestApp4
{
    class Program
    {

        static void Main(string[] args)
        {
            //set the connection string
            string connString = @"Server =.\SQL2K17; Database = SampleDB; Trusted_Connection = True;";

            //variables to store the query results
            int empID, locationID;
            string empCode, empFirstName, empLastName, locationCode, locationDescr;


            try
            {
                //sql connection object
                using (SqlConnection conn = new SqlConnection(connString))
                {

                    //define the query text
                    string query = @"SELECT * FROM [dbo].[fnGetEmployeeInfo](@empID);";

                    //define the SqlCommand object
                    SqlCommand cmd = new SqlCommand(query, conn);

                    //parameter value will be set from command line
                    SqlParameter param1 = new SqlParameter();
                    param1.ParameterName = "@empID";
                    param1.SqlDbType = SqlDbType.Int;
                    param1.Value = int.Parse(args[0].ToString());

                    //pass parameter to the SQL Command
                    cmd.Parameters.Add(param1);

                    //open connection
                    conn.Open();

                    //execute the SQLCommand
                    SqlDataReader dr = cmd.ExecuteReader();

                    Console.WriteLine(Environment.NewLine + "Retrieving data from database..." + Environment.NewLine);
                    Console.WriteLine("Retrieved records:");

                    //check if there are records
                    if (dr.HasRows)
                    {
                        while (dr.Read())
                        {
                            empID = dr.GetInt32(0);
                            empCode = dr.GetString(1);
                            empFirstName = dr.GetString(2);
                            empLastName = dr.GetString(3);
                            locationID = dr.GetInt32(4);
                            locationCode = dr.GetString(5);
                            locationDescr = dr.GetString(6);

                            //display retrieved record
                            Console.WriteLine("{0},{1},{2},{3},{4},{5},{6}", empID.ToString(), empCode, empFirstName, empLastName, locationID, locationCode, locationDescr);
                        }
                    }
                    else
                    {
                        Console.WriteLine("No data found.");
                    }

                    //close data reader
                    dr.Close();

                    //close connection
                    conn.Close();
                }
            }
            catch (Exception ex)
            {
                //display error message
                Console.WriteLine("Exception: " + ex.Message);
            }


        }
    }

}

Explanation

As you can see in the above code, the main query text is:

string query = @"SELECT * FROM [dbo].[fnGetEmployeeInfo](@empID);";

So, what I actually do in the above code, is that I'm treating the function "fnGetEmployeeInfo" like a table, because it is a table-valued function and returns a table. Moreover, again, for safer code, I'm using a SqlParameter object.

Right after you finish writing the code, you will need to compile and run the program. To do this, within our project in Visual Studio, by pressing the key F6 or by clicking on the "Build" menu and then click on "Build Solution", our program will be compiled and if everything is OK, that is if we get no errors and see the "Build succeeded" notification on the bottom left corner of the window, it means that the program is now ready for execution.

To execute the program, since this is a console application, we need to navigate to the project's directory and run the executable. In this demo, the project directory is "C:\temp\demos\TestApp4". Therefore, from the command prompt, we need to navigate to "C:\temp\demos\TestApp4\TestApp4\bin\Debug" and execute the "TestApp4.exe" program (or if you changed the assembly name, run the proper executable file that is generated upon compiling the program) along with passing the parameter value for the employee ID.

For example, I'm running the below command based on which I'm requesting information for employee with ID=2:

C:\temp\demos\TestApp4\TestApp4\bin\Debug>TestApp4.exe 2

Here's the output of the program's execution:

Program output for first example (table-valued function call).

Calling SQL Server Function "fnGetTotalEmployees"

Now let's modify our code in order to call the scalar function "fnGetTotalEmployees". This function takes location ID as an input parameter and returns all employees that have the same location ID.

using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Threading.Tasks;
using System.Data.SqlClient;
using System.Data;

namespace TestApp4
{
    class Program
    {

        static void Main(string[] args)
        {
            //set the connection string
            string connString = @"Server =.\SQL2K17; Database = SampleDB; Trusted_Connection = True;";


            try
            {
                //sql connection object
                using (SqlConnection conn = new SqlConnection(connString))
                {

                    //define the query text
                    string query = @"SELECT [dbo].[fnGetTotalEmployees](@empID) AS TotalEmployees;";

                    //define the SqlCommand object
                    SqlCommand cmd = new SqlCommand(query, conn);

                    //parameter value will be set from command line
                    SqlParameter param1 = new SqlParameter();
                    param1.ParameterName = "@empID";
                    param1.SqlDbType = SqlDbType.Int;
                    param1.Value = int.Parse(args[0].ToString());

                    //pass parameter to the SQL Command
                    cmd.Parameters.Add(param1);

                    //open connection
                    conn.Open();

                    //execute the SQLCommand
                    Int32 functionResult = (Int32)cmd.ExecuteScalar();

                    Console.WriteLine(Environment.NewLine + "Retrieving data from database..." + Environment.NewLine);
                    Console.WriteLine("Retrieved result:");


                    //display retrieved result
                    Console.WriteLine("Total employees for location id={0}: {1}", args[0].ToString(), functionResult.ToString());


                    //close connection
                    conn.Close();
                }
            }
            catch (Exception ex)
            {
                //display error message
                Console.WriteLine("Exception: " + ex.Message);
            }

        }
    }

}

Explanation

As you can see in the above code, the main query text is:

string query = @"SELECT [dbo].[fnGetTotalEmployees](@empID) AS TotalEmployees;";

This query is totally different than the first example, because now we are calling a scalar-valued SQL function, that is a function that just returns one result.

Again, I'm using a parameter, but as you can see in the code, there is no need to use a SqlDataReader object because scalar-valued functions return a single value. Therefore, I have only converted the value to an Int32 data type.

OK, let's compile and run the new code and request the total number of employees for location id 2.

C:\temp\demos\TestApp4\TestApp4\bin\Debug>TestApp4.exe 2

Here's the output of the program's execution:

Program output for second example (scalar-valued function call).

Let's run again for location 1:

C:\temp\demos\TestApp4\TestApp4\bin\Debug>TestApp4.exe 1
Program output for second example (scalar-valued function call).

Calling SQL Server System Function "GETDATE()"

Our last example, is to call one SQL Server system function. In this example, we are calling "GETDATE()" which returns the current date and time.

Here's how our code changes:

using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Threading.Tasks;
using System.Data.SqlClient;
using System.Data;

namespace TestApp4
{
    class Program
    {

        static void Main(string[] args)
        {
            //set the connection string
            string connString = @"Server =.\SQL2K17; Database = SampleDB; Trusted_Connection = True;";


            try
            {
                //sql connection object
                using (SqlConnection conn = new SqlConnection(connString))
                {

                    //define the query text
                    string query = @"SELECT GETDATE() AS CurrentDateTime;";

                    //define the SqlCommand object
                    SqlCommand cmd = new SqlCommand(query, conn);

                    //open connection
                    conn.Open();

                    //execute the SQLCommand
                    DateTime functionResult = (DateTime)cmd.ExecuteScalar();

                    Console.WriteLine(Environment.NewLine + "Retrieving data from database..." + Environment.NewLine);
                    Console.WriteLine("Retrieved result:");


                    //display retrieved result
                    Console.WriteLine("Current DateTime Value: {0}", functionResult.ToString());


                    //close connection
                    conn.Close();
                }
            }
            catch (Exception ex)
            {
                //display error message
                Console.WriteLine("Exception: " + ex.Message);
            }

        }
    }

}

Explanation

As you can see in the above code, the main query text is:

string query = @"SELECT GETDATE() AS CurrentDateTime;";

This query is similar to the previous one where we called the scalar-valued function "fnGetTotalEmployees". Again, it returns just one value. The only difference is the data type which requires us to cast the function's result to the DateTime type.

We compile again and run the program and this is the output:

Program output for third example (system function call).

Conclusion

In this tip, we discussed how you can work with different types of SQL Server functions from within .NET applications. To this end, we worked with table-valued and scalar-valued SQL Server functions using C# code, as well as with the SQL Server system function GETDATE().

Stay tuned, we will be continuing this interesting series of tips on SQL Server and .NET.

Next Steps


Last Updated: 2018-12-13


next webcast button


next tip button



About the author
MSSQLTips author Artemakis Artemiou Artemakis Artemiou is a Senior SQL Server and Software Architect, Author, and a former Microsoft Data Platform MVP (2009-2018).

View all my tips




Post a comment or let the author know this tip helped.

All comments are reviewed, so stay on subject or we may delete your comment. Note: your email address is not published. Required fields are marked with an asterisk (*).

*Name    *Email    Email me updates 


Signup for our newsletter
 I agree by submitting my data to receive communications, account updates and/or special offers about SQL Server from MSSQLTips and/or its Sponsors. I have read the privacy statement and understand I may unsubscribe at any time.



    



Monday, December 17, 2018 - 6:27:26 AM - Artemakis Artemiou Back To Top

Hi Brad,

Thank you for the excellent discussion!

Cheers,

Artemakis


Friday, December 14, 2018 - 9:50:54 AM - Brad Back To Top

Thanks for that response. Ya, you are right it is up to the shop you work at to define how you want to handle it as there is no right or wrong way and yes, you do have to write the basic code to call the stored proc passing the necessary params regardless of which way you are using.  I have worked at shops that have done it both ways and have just found it much easier, mainly for mods after the fact to call a stored proc instead. And I agree , sometimes for a really light query, embedded would be fine and we do have some of that here for sure - mostly in older apps  where someone else just did it differently and we have not bothered to modify that code as it just works as is.

We have made sure to use good naming conventions for our SP' so it's really easy to figure out what SP is doing what for the most part and it's kinda of self documenting in the code because you can see right there exactly which SP is being called for what and as well, if you ned to make a change to an sp for whatever reason that may affect the number of params etc... (as you mentioned), you can jsut do a quick search to located all the places where that particular sp is being called so you cna adjust your code accordingly... Believe me, that part really coems in handy as a side benefit!!  

Thanks again for taking the time to respond to my question!

Brad


Thursday, December 13, 2018 - 4:15:31 PM - Artemakis Artemiou Back To Top

Hi Brad, thank you for your comment and for taking the time to read my tip.

The short answer to your question is: it depends

Please read below for the long answer.

In this tip, the purpose is to show how it is possible to call SQL Server functions from a .NET application and process the results. The SQL code included in the specific tip's example, is just the necessary SQL code to make the calls to the SQL Server functions. You cannot avoid writing this code, otherwise, how could you call the SQL Server funtions?

Now, as a general discussion topic, again, I believe that you cannot fully avoid embedding SQL code in an application (well, it also depends on what type of application you write). The way I see it, it is a mixture of techniques. For example, you could have calls to stored procedures for the heavy database operations, and for some other "lighter" functionality, you could embedd some SQL queries in the .NET code. It's really up to the developer's or organization's program design standards to define these things.

As a last example, imagine having a .NET application that worked only by calling SQL Server stored procedures. Still, you would need to make the stored procedure calls via the .NET application, as well as you would have to retrieve the results and process them. Moreover, if a stored procedure's structure changed (i.e. a new input parameter was added), again you would also need to update the application's code in order to handle the changed stored procedure accordingly (i.e. pass that additional input parameter via the stored procedure call).

Cheers,

Artemakis


Thursday, December 13, 2018 - 9:10:39 AM - Brad Back To Top

Hi Artemakis, we don't use c# that much as most of our code is in vb.net but it doesn;t really matter what language is being used for this example. I noticed that you are writing the actual sql query within the code meaning if that changes you have to recompile and re-deploy into the production environment. We do have some apps that do have the sql code embedded like that (older apps we just haven't bothered to change) but the majority of our stuff, instead of embedding sql in the code just calls stored procs and that way there is never a need to recomplie - you just change the stored proc, run your app again and presto, all fixed/changed. Is that typically what you do when you are writing production code? Maybe you could also post the code to do things that way instead?

Thanks, Brad 


Learn more about SQL Server tools