By: Artemakis Artemiou | Updated: 2018-10-18 | Comments (3) | Related: > 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.
And this is the development environment I get, in order to work on my 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):
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:
- SqlConnection - Connecting to SQL Server
- SqlCommand - Running a command against the SQL Server instance
- SqlDataReader - Retrieving your query's results)
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.
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.
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
- Check out my other tip: Understanding SQL Server Connection Pooling in ADO.NET
- Check out this tip "Script to check SQL Server connection pooling"
- Check out this tip "How To Create an ADO.NET Data Access Utility Class for SQL Server"
- Check out the MS article: .NET Framework Data Providers
- Check out the MS article: SqlConnection Class
About the author
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
Article Last Updated: 2018-10-18
