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

 
Failover Clustering in the Cloud - Understanding Your Options - Free Webinar
 

How to Get Started with SQL Server and .NET


By:   |   Last Updated: 2018-10-18   |   Comments (2)   |   Related Tips: More > Application Development

Problem

SQL Server is one of the most powerful data platforms in the world and the .NET Framework is one of the most popular software frameworks for developing software that runs primarily on Microsoft Windows.  Imagine what you can do if you combine these two technologies. The possibilities are endless.

This tip helps you get started with SQL Server and .NET (C#). You will learn how you can connect from a C# application to SQL Server and how you can retrieve the version of the SQL Server instance, thus running a simple query.

Solution

In order to demonstrate a connection to SQL Server via a .NET application, in this case a C# application, we need to start a new project in Visual Studio. For this demo, I’m using Visual Studio 2017.

Create New Visual Studio Project

So, in Visual Studio, I’m starting a new project and select Visual C# - Console App (.NET Framework) and calling it TestApp1.

We start a new project in Visual Studio 2017, of the type "Console App (.NET Framework)".

And this is the development environment I get, in order to work on my project:

This is the development environment for our demo project.

The purpose is to try to connect to a named SQL Server instance "SQL2K17" on the local machine and retrieve the SQL Server version information.

Here’s a screenshot of the SQL Server instance, as it can be seen in SQL Server Management Studio (SSMS):

This is the demo SQL Server named instance "SQL2K17" onto which we will connect via our C# application.

Connecting Application to SQL Server

In order to connect to a database from a client (i.e. in this case, our C# application), we need to make use of a Data Provider. For example, there are ODBC drivers/providers, OLE DB providers, specific .NET Framework Data Providers, etc. Without the use of a Data Provider, you cannot connect to the database. Data Providers act like intermediaries between the database server and the application/client.

In this demo, we are going to use the ".NET Framework Data Provider for SQL Server". We make use of this data provider by including in our project, the System.Data.SqlClient namespace with the below line of code at the top of our code class:

using System.Data.SqlClient;

Then we need to write the proper C# code that tries to establish a connection to the SQL Server instance, using the above data provider, along with executing the query that returns the SQL Server version.

At this point it must be mentioned that when writing data access code, you always need to include exception handling code/logic, in order to handle any issues that may have to do with the communication between the client (i.e. your C# application) and the database server.

In order to connect to SQL Server using the .NET Framework Data Provider for SQL Server and retrieve information, you will need to create the below objects:

Creating a SQL Server Database Connection String

Also, in order to establish the connection to SQL Server, you will need to specify the connection string, in the format expected by the data provider you are using.

In the connection string, you can specify that you want to have either a trusted connection to SQL Server, that is using Windows Authentication, or a SQL authentication-based connection, by using a username/password.

Below, you can find examples of connection strings. The first one, uses a trusted connection, and the second one uses an SQL connection.

Trusted connection:

string connString = @"Server=INSTANCE_NAME;Database=DATABASE_NAME;Trusted_Connection = True;";

SQL Authentication-based connection:

string connString = @"Server=INSTANCE_NAME;Database=DATABASE_NAME;User ID=USERNAME;Password=PASSWORD";

In this demo, we are going to use a Trusted connection.

C# Code Blocks to Access SQL Server

The below code block, shows how you can set the connection string in the SqlConnection object, along with applying exception handling logic while trying to connect to the SQL Server instance:

try
{
    using (SqlConnection conn = new SqlConnection(connString))
    {
        //access SQL Server and run your command
    }
}
catch (Exception ex)
{
    //display error message
    Console.WriteLine("Exception: " + ex.Message);
}

The below code block, shows how you can make use of the SqlCommand object:

SqlCommand cmd = new SqlCommand(QUERY, conn);

Last, the below code block shows how you can make use of the SqlDataReader object for the purposes of this demo:

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

//check if there are records
if (dr.HasRows)
{
    while (dr.Read())
    {
        //display retrieved record (first column only/string value)
        Console.WriteLine(dr.GetString(0));
    }
}
else
{
    Console.WriteLine("No data found.");
}
dr.Close();

OK, now we can put everything together and write the proper C# code to access the named SQL Server instance ".\SQL2K17", retrieve version information, and display it on the screen via the command line.

Complete Code Listing

Here’s the full code for the Program.cs class:

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

namespace TestApp1
{
    class Program
    {
        static void Main(string[] args)
        {
            string connString = @"Server =.\SQL2K17; Database = master; Trusted_Connection = True;";

            try
            {
                using (SqlConnection conn = new SqlConnection(connString))
                {
                    //retrieve the SQL Server instance version
                    string query = @"SELECT @@VERSION";

                    SqlCommand cmd = new SqlCommand(query, conn);

                    //open connection
                    conn.Open();

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

                    //check if there are records
                    if (dr.HasRows)
                    {
                        while (dr.Read())
                        {
                            //display retrieved record (first column only/string value)
                            Console.WriteLine(dr.GetString(0));
                        }
                    }
                    else
                    {
                        Console.WriteLine("No data found.");
                    }
                    dr.Close();
                }
            }
            catch (Exception ex)
            {
                //display error message
                Console.WriteLine("Exception: " + ex.Message);
            }


        }
    }
}

Compile and Test

Now that we finished writing the source code, it is time to compile it and run the program.

To compile the program, within our project in Visual Studio, press the F6 key or click 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.

compile program

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\TestApp1”. Therefore, from the command prompt, we need to navigate to “C:\temp\demos\TestApp1\TestApp1\bin\Debug” and execute the “TestApp1.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 version information by running the T-SQL statement: SELECT @@VERSION.

This is the output when running our demo C# program "TestApp1".

Conclusion

In this tip, we discussed about how you can get started with .NET and SQL Server. We developed a simple C# application which makes use of the ".NET Framework Data Provider for SQL Server", and we successfully connected and retrieved version information from a SQL Server instance.

In subsequent tips, we will discuss more about .NET and SQL Server, and we are going to build more complex .NET data applications.

Next Steps


Last Updated: 2018-10-18


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.



    



Friday, October 26, 2018 - 2:45:38 PM - Artemakis Artemiou Back To Top

Hi,

 

Thank you for your kind words.

 

Please find my answers below. 

 

Answer to Question 1:

Because in this example I'm performing all database operations within a "using" block, .NET will automatically close the connection to the database. However, there's nothing wrong if you wish to explicitely close the connection. In such case, yes, you could add conn.Close(); right after dr.Close();

 

Answer to Question 2:

Yes of course. As a best practice, you can create a different SqlCommand object for each SQL statement. 

 

Best Regards, 

Artemakis


Friday, October 26, 2018 - 1:53:52 PM - Maria Halt Back To Top

 Hi, Thanks for writing a clear and concise article! I have a couple of questions.

1. Does the connection to the db need to be terminated?  If so, when?  After dr.Close()?  

2. If I need to execute multiple commands, can I use the same db connection?  

Looking forward to your response!

Sincerely,

Maria Halt

 


Learn more about SQL Server tools