Querying SQL Server Tables from .NET

By:   |   Comments   |   Related: > Application Development


Problem

In one of my previous tips, I've talked about how you can get started with SQL Server and .NET. In this tip, we are going to see how to query SQL Server tables from .NET, get the results, and process them.  More specifically, we are going to continue and further develop the example presented in the previous tip, and see how you can query SQL Server tables via a C# console application.

Solution

As mentioned above, in this tip we will see how you can run SQL Server queries from within the C# application, as well as how you can process the results.  This tip's examples, are related to the example in my previous tip How to Get Started with SQL Server and .NET.

SQL Server Sample Database

Our example will be based on the database "SampleDB" which is a sample database I created on a test SQL Server named instance on my local machine. The name of the instance is "SQL2K17".

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

This is the demo SQL Server named instance SQL2K17 onto which we will connect via our C# application and run SELECT and UPDATE T-SQL statements

Also, here's the database diagram of the tables in the "SampleDB" database which will be used for this tip's examples:

This is the database diagram for the sampledb database illustrating the two tables that will be used in this tip

As you can see, there is a table named "employees" and another table named "location".

The "employees" table has a foreign key (locationID) pointing to the "location" table and more specifically, to the "id" column.

Sample Data

The sample data in the "location" table are:

This is a screenshot of the sample data in the location table

The sample data in the "employees" table are:

This is a screenshot of the sample data in the employees table

Connecting to the SQL Server Instance – Connection String

We will connect to the SQL Server instance by using a trusted connection. The connection string to be used in this example is:

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

The above connection string, will allow me to connect to the named instance "SQL2K17" on my local machine, using a trusted connection, which will be established using my Windows account.

Executing a SELECT SQL Statement

Now, let's run a simple SELECT query, get the results and display them.

The SQL statement that we will execute is the below:

SELECT e.id,e.code,e.firstName,e.lastName,l.code,l.descr 
FROM employees e 
INNER JOIN location l on e.locationID=l.id;

The above SELECT query, returns all the employee records from the sample database, along with the code and description of their location.

So, let's write the required .NET code for executing the above query against the database "SampleDB" on the local named SQL server instance "SQL2K17". Below you can find the code.

Note: Prior to start writing the code, similarly to my previous tip, 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 "TestApp2-SELECT" 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;

namespace TestApp2_SELECT
{
    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;
            string empCode, empFirstName, empLastName, locationCode, locationDescr;

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

                    //retrieve the SQL Server instance version
                    string query = @"SELECT e.id,e.code,e.firstName,e.lastName,l.code,l.descr
                                     FROM employees e
                                     INNER JOIN location l on e.locationID=l.id;
                                     ";
                    //define the SqlCommand object
                    SqlCommand cmd = new SqlCommand(query, conn);

                    //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);
                            locationCode = dr.GetString(4);
                            locationDescr = dr.GetString(5);

                            //display retrieved record
                            Console.WriteLine("{0},{1},{2},{3},{4},{5}", empID.ToString(), empCode, empFirstName, empLastName, 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);
            }


        }
    }
}

As you can see in the above code, I have used an SqlConnection object in order to set the connection based on the connection string. Then, I used an SqlCommand object and passed as parameters the query and the SqlConnection object. Finally, I used an SqlDataReader object in order to store and then display the query's results.

OK, now that the source code is complete, it's time to compile and run the program. To compile the program, 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\TestApp2-SELECT". Therefore, from the command prompt, we need to navigate to "C:\temp\demos\TestApp2-SELECT\TestApp2-SELECT\bin\Debug" and execute the "TestApp2-SELECT.exe" program.

Right after we execute the program, we can see that our C# application managed to successfully connect to the named SQL Server instance ".\SQL2K17" and to return our SELECT SQL statement's results:

Our sample program output for the SELECT SQL query
Executing an UPDATE SQL Statement

Now, let's run a simple UPDATE query against the same database ("SampleDB").

Here's the data in the "employees" table before the update:

This is the data in the employees table before the UPDATE SQL operation

The SQL statement that we will execute is the below:

UPDATE employees
SET locationID=1
WHERE id=4;

The above query, will update the location for the employee record with id = 4.

In this case, our C# code is different than the previous one (SELECT query) since we do not need to make use of the SqlDataReader object. Instead, we just call the "ExecuteNonQuery" method of the SqlCommand object.

Note: Prior to start writing the code, similarly to my previous tip, 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 "TestApp2-UPDATE" and saved it in the "C:\temp\demos" folder on my local machine (a new subfolder with the name of the project was created).

See below how our code changes in order to run the UPDATE statement:

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

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


            try
            {
                //create the SqlConnection object
                using (SqlConnection conn = new SqlConnection(connString))
                {

                    //retrieve the SQL Server instance version
                    string query = @"UPDATE employees SET locationID=1 WHERE id=4;";
                    //create the SqlCommand object
                    SqlCommand cmd = new SqlCommand(query, conn);

                    //open connection
                    conn.Open();

                    //execute the SQL Command (UPDATE)
                    cmd.ExecuteNonQuery();

                    //close connection
                    conn.Close();

                    Console.WriteLine("UPDATE statement successfully executed.");
                }
            }
            catch (Exception ex)
            {
                //display error message
                Console.WriteLine("Exception: " + ex.Message);
            }


        }
    }
}

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\TestApp2-UPDATE". Therefore, from the command prompt, we need to navigate to "C:\temp\demos\TestApp2-UPDATE\TestApp2-UPDATE\bin\Debug" and execute the "TestApp2-UPDATE.exe" program.

Our sample program output for the UPDATE SQL query

We can see the program was executed successfully.

Here's the data in the "employees" table after the update:

This is the data in the employees table after the UPDATE SQL operation

Conclusion

In this tip, we discussed how you can connect to SQL Server from a .NET application, run SELECT and UPDATE statements, and process the results. We developed a simple C# application which makes use of the ".NET Framework Data Provider for SQL Server", and successfully performed the abovementioned operations.

In subsequent tips, we will see more examples of performing different data operations from .NET.

Next Steps


sql server categories

sql server webinars

subscribe to mssqltips

sql server tutorials

sql server white papers

next tip



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).

This author pledges the content of this article is based on professional experience and not AI generated.

View all my tips


Comments For This Article

















get free sql tips
agree to terms