Working with SQL Server Functions and .NET

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"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"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

Related Posts

Leave a Reply

Your email address will not be published. Required fields are marked *