How to connect a SQL Server database in a .NET application
SQL Server is often chosen as a part of the backend for .NET applications. It works excellently with .NET technologies, integrates with .NET Framework and .NET Core, and delivers the high performance and scalability essential for robust .NET applications. In this aspect, ensuring smooth and efficient database connection becomes one of the key demands for database developers.
This article will review the most efficient methods of connecting .NET applications to SQL Server.
Prerequisites
To follow this tutorial and implement its recommendations efficiently, you will need the following:
- Visual Studio 2022 – a free Community edition.
- SQL Server – a free Express edition of this Microsoft’s relational database management system (RDBMS).
- dotConnect for SQL Server – an enhanced data provider that builds on ADO.NET and SqlClient to present a complete solution for developing SQL Server-based database applications.
- SQL Server Management Studio (SSMS) which is the application you can use to manage database.
- A test database – we create it to illustrate connecting to our .NET application as shown further.
After installing and configuring both SQL Server and SQL Server Management Studio, open SSMS and establish the connection.
In the Server name field, type localhost. The Authentication type should be Windows Authentication. Click Connect.
A new query window opens. Execute the below script to create a test database for our tutorial.
CREATE DATABASE sakila;
GO
USE sakila;
GO
CREATE TABLE actor (
actor_id INT PRIMARY KEY IDENTITY,
first_name NVARCHAR(45) NOT NULL,
last_name NVARCHAR(45) NOT NULL,
last_update DATETIME NOT NULL DEFAULT GETDATE()
);
INSERT INTO actor (first_name, last_name) VALUES
('John', 'Doe'),
('Jane', 'Smith'),
('Robert', 'Brown'),
('Emily', 'Davis'),
('Michael', 'Wilson'),
('Jessica', 'Garcia'),
('David', 'Martinez'),
('Laura', 'Hernandez'),
('Daniel', 'Lopez'),
('Sarah', 'Gonzalez'),
('Matthew', 'Perez');
The test database is ready.
Install and configure dotConnect for SQL Server
The first step is setting up a new .NET Application project in Visual Studio.
Open Visual Studio, click Create new project, and search for ASP.NET Core Web Application. Select it. Give this project a name. In our scenario, the test project is SQLServer_ASP.NET. Select .NET 8 as the target framework and click Create.
Next, we need to install dotConnect for SQL Server via NuGet Package Manager.
In Visual Studio, click Tools > NuGet Package Manager > Manage NuGet Packages for Solution.
On the NuGet Package Manager page, click Browse and search for Devart.Data.SqlServer. Select it and click Install for your project.
Alternatively, you can download dotConnect for SQL Server from the official website. After installing the NuGet package, proceed to activate the trial version or enter your license:
- Visit the dotConnect for SQL Server download page.
- Download the dcsqlserver.exe installer.
- Execute the .exe file and follow the instructions to install dotConnect for SQL Server on your machine.
This procedure installs the software and ensures that all features are available for use in your project. Please notice that registered users download the full version from their accounts, not the trial version.
Now, let us set up the database connection class. Right-click your project, select Add > Class:
We call this class DatabaseConnectionClass and will use it to simulate connecting our application to a SQL Server database.
For this tutorial, we are going to use the table Actor from our sample database. The table schema is:
- | ACTOR_ID: int
- | FIRST_NAME: nvarchar(45)
- | LAST_NAME: nvarchar(45)
- | LAST_UPDATE: datetime
We can create a class based on this information.
Right-click your solution and add a class named Actor.cs. Insert the below code into that class:
public class Actor
{
public int ActorId { get; set; }
public string FirstName { get; set; }
public string LastName { get; set; }
public DateTime LastUpdate { get; set; }
}
Connect to SQL Server with C#
C# and .NET often pair together and are commonly used with Microsoft SQL Server. Let's explore how we can establish a connection to SQL Server using C#. Typically, there are two main approaches to achieve this.
Run-time creation
We need to add a method to our DatabaseConnectionClass. Copy the below piece of code into your class:
public static ListSQLServer_Connection() { List actors = new List (); try { // Open a connection to the SQL Server database string connectionString = "DataSource=localhost;InitialCatalog=sakila;IntegratedSecurity=True;TrustServerCertificate=True"; Devart.Data.SqlServer.SqlConnection connection = new Devart.Data.SqlServer.SqlConnection(); connection.ConnectionString = connectionString; connection.Open(); var command = "SELECT * FROM actor;"; var results = new Devart.Data.SqlServer.SqlCommand(command, connection); using (Devart.Data.SqlServer.SqlDataReader reader = (Devart.Data.SqlServer.SqlDataReader)results.ExecuteReader()) { if (reader.HasRows) { while (reader.Read()) { int id = reader.GetInt32(0); string firstname = reader.GetString(1); string lastname = reader.GetString(2); DateTime lastupdate = reader.GetDateTime(3); Actor actor = new Actor(); actor.ActorId = id; actor.FirstName = firstname; actor.LastName = lastname; actor.LastUpdate = lastupdate; actors.Add(actor); } } else { Console.WriteLine("No rows found."); } } } catch (Exception ex) { Console.WriteLine(ex.Message); } return actors; }
Important! Don’t forget to specify using the statement using Devart.Data.SQLServer at the top of your file.
Since we are using a local database, we simply specify the Data Source as localhost.
Also, we can specify the login credentials if our database uses them. Have a look at the below code and notice that is uses UserId and Password. Make sure to provide your actual UserID and password there.
public static ListSQLServer_User_Credentials() { List actors = new List (); try { // Open a connection to the SQL Server database string connectionString = "DataSource=localhost;UserId=username;Password=password;InitialCatalog=sakila;IntegratedSecurity=True;TrustServerCertificate=True"; Devart.Data.SqlServer.SqlConnection connection = new Devart.Data.SqlServer.SqlConnection(); connection.ConnectionString = connectionString; connection.Open(); var command = "SELECT * FROM actor;"; var results = new Devart.Data.SqlServer.SqlCommand(command, connection); using (Devart.Data.SqlServer.SqlDataReader reader = (Devart.Data.SqlServer.SqlDataReader)results.ExecuteReader()) { if (reader.HasRows) { while (reader.Read()) { int id = reader.GetInt32(0); string firstname = reader.GetString(1); string lastname = reader.GetString(2); DateTime lastupdate = reader.GetDateTime(3); Actor actor = new Actor(); actor.ActorId = id; actor.FirstName = firstname; actor.LastName = lastname; actor.LastUpdate = lastupdate; actors.Add(actor); } } else { Console.WriteLine("No rows found."); } } } catch (Exception ex) { Console.WriteLine(ex.Message); } return actors; }
Using Connection String Builder
Another approach to establishing a connection is by assigning values to several properties.
We will use the SqlConnectionStringBuilder class for this. Add the below piece of code into your DatabaseConnectionClass:
public static List SQLserver_String_Builder()
{
List actors = new List();
try
{
Devart.Data.SqlServer.SqlConnectionStringBuilder connection = new Devart.Data.SqlServer.SqlConnectionStringBuilder();
connection.DataSource = "localhost";
connection.UserID = "username";
connection.Password = "password";
connection.InitialCatalog = "sakila";
connection.MaxPoolSize = 150;
connection.ConnectTimeout = 30;
Devart.Data.SqlServer.SqlConnection connection = new Devart.Data.SqlServer.SqlConnection();
connection.Open();
var command = "SELECT * FROM actor LIMIT 10";
var results = new Devart.Data.SqlServer.SqlCommand(command, connection);
using (Devart.Data.SqlServer.SqlDataReader reader = (Devart.Data.SqlServer.SqlDataReader)results.ExecuteReader())
{
if (reader.HasRows)
{
while (reader.Read())
{
int id = reader.GetInt32(0);
string firstname = reader.GetString(1);
string lastname = reader.GetString(2);
DateTime lastupdate = reader.GetDateTime(3);
Actor actor = new Actor();
actor.ActorId = id;
actor.FirstName = firstname;
actor.LastName = lastname;
actor.LastUpdate = lastupdate;
actors.Add(actor);
}
}
else
{
Console.WriteLine("No rows found.");
}
}
}
catch (Exception ex)
{
Console.WriteLine(ex.Message);
}
return actors;
}
We have done basically the same implementation, but this time we could specify different parameters that our connection should have, including MaxPoolSize and ConnectionTimeout.
Now, we need to be able to visualize the data we have just retrieved. Thus, in the Solution Explorer window, click Controllers and then Add new class. We need to add a class called ActorsController.
Inside the SQLServer_ASP.NET.Controllers namespace, modify the class to use the code below:
[ApiController]
[Route("api/[controller]/[action]")]
public class ActorsController : ControllerBase
{
[HttpGet]
public IEnumerable GetActors()
{
var actors = DatabaseConnectionClass.SQLServer_RunTime_Connection();
return actors;
}
}
When we run the project by clicking F5, the following screen should appear:
Click the GetActors drop-down, select Try it now, and click Execute. After that, we can see the list we want.
This way, we have successfully established connection with our demo SQL Server database.