Connect to SQL Server in .NET with C#
SQL Server is one of the most widely used relational databases in enterprise applications. Whether you're building internal tools or data-driven web apps, the ability to work with SQL Server in .NET is essential for any C# developer.
We'll go through how to establish a connection, run a query, and perform basic database operations—all with straightforward C# code you can easily expand as your projects grow.
Why dotConnect for SQL Server?
dotConnect for SQL Server is designed for .NET developers who need reliable, high-performance access to SQL Server data without external dependencies. It's built for enterprise-scale applications and combines security, compatibility, and seamless Visual Studio integration to streamline development across all .NET platforms.
- No client libraries required: Connect to SQL Server directly over TCP/IP without installing any SQL Server client software.
- Fully ADO.NET compliant: Leverage familiar ADO.NET interfaces and components for smooth integration and predictable behavior.
- Secure and stable: Supports encrypted connections and undergoes continuous performance and security testing to meet enterprise standards.
- Broad compatibility: Works across all major versions of SQL Server and supports .NET Framework, .NET Core, and .NET 5/6/7+.
- Visual Studio integration: Includes wizards and design-time tools that simplify connection setup, data access, and schema design.
dotConnect for SQL Server includes priority support, regular updates, and thorough documentation, and provides a complete, production-ready solution for building data-driven .NET applications.
Download and activate dotConnect for SQL Server
30-day free trial version
You can start using dotConnect for SQL Server immediately with a 30-day free trial. Choose one of the following installation options:
- Installer: Download and install dotConnect for SQL Server directly on your machine.
- NuGet: Add the Devart.Data.SqlServer package to your project.
No license key is required for the trial version — just install and explore.
Full version
After you've purchased the full version, follow these steps:
1. Go to the Products section in your Devart profile.
2. Click the product name, or hover over it and click Details. The License details page will open, where you can find the Activation Key.
3. To activate dotConnect for SQL Server in your application, copy the Activation Key and paste it into your connection string as the License Key parameter.
Check SQL Server database objects
To connect to SQL Server using the built-in Data Explorer, click Tools, select Connect to Database, and click Microsoft SQL Server.
Then, enter your server details and credentials, and click Test Connection.
Once connected, you can browse tables, execute queries, and manage data directly within the Data Explorer.
Connect to the database and retrieve data
Let's connect to your SQL Server database and run a simple SELECT query. This will confirm that your application is connected properly and able to pull data from the database.
Create DatabaseConnection.cs (connection strings)
This class keeps the connection details separate for better maintainability.
using Devart.Data.SqlServer;
public static class DatabaseConnection
{
public static SqlConnection CreateConnection()
{
string connectionString = "" +
"Data Source=127.0.0.1;" +
"User Id=TestUser;" +
"Password=TestPassword;" +
"Initial Catalog=TestDb;" +
"License key=**********";
return new SqlConnection(connectionString);
}
}
Update the connection string
Replace the placeholders in the connection string with your relevant SQL Server database credentials.
Add the code below to the Program.cs file.
using System;
using Devart.Data.SqlServer;
public sealed class Program
{
public static void Main(string[] args)
{
try
{
using (SqlConnection connection = DatabaseConnection.CreateConnection())
{
connection.Open();
Console.WriteLine("Connection successful!");
const string sql = "SELECT TOP (10) * FROM actor";
using (SqlCommand command = new SqlCommand(sql, connection))
using (var reader = command.ExecuteReader())
{
int fields = reader.FieldCount;
// Print header
for (int i = 0; i < fields; i++)
{
Console.Write(reader.GetName(i) + (i < fields - 1 ? "\t" : ""));
}
Console.WriteLine();
// Print rows
while (reader.Read())
{
for (int i = 0; i < fields; i++)
{
object value = reader.IsDBNull(i) ? "(null)" : reader.GetValue(i);
Console.Write(value + (i < fields - 1 ? "\t" : ""));
}
Console.WriteLine();
}
}
}
}
catch (Exception ex)
{
Console.WriteLine("An error occurred: " + ex.Message);
}
}
}
Run the application
Build and run your application by pressing F5 or selecting Start from the menu.
Insert new records in SQL Server
Since the connection is working, you can begin adding data. In this section, you'll insert a new row into a test table using a SQL statement to perform the operation.
using System;
using Devart.Data.SqlServer;
public sealed class Program
{
public static void Main(string[] args)
{
try
{
using (SqlConnection connection = DatabaseConnection.CreateConnection())
{
connection.Open();
Console.WriteLine("Connection successful!\n");
const string sql = "INSERT INTO actor (FirstName, LastName) VALUES ('John', 'Doe'); SELECT * FROM actor WHERE ActorID = SCOPE_IDENTITY();";
using (SqlCommand command = new SqlCommand(sql, connection))
using (var reader = command.ExecuteReader())
{
if (reader.Read())
{
int actorId = reader.GetInt32(reader.GetOrdinal("ActorID"));
string firstName = reader.GetString(reader.GetOrdinal("FirstName"));
string lastName = reader.GetString(reader.GetOrdinal("LastName"));
Console.WriteLine($"Inserted actor: {actorId}, {firstName} {lastName}");
}
else
{
Console.WriteLine("No actor was inserted or found.");
}
}
}
}
catch (Exception ex)
{
Console.WriteLine("An error occurred: " + ex.Message);
}
}
}
You can verify the result in the application.
Update rows in SQL Server
Next, we'll update an existing record. It's required to modify a row by changing values in one or more columns, demonstrating how to apply updates from within your C# application.
using System;
using Devart.Data.SqlServer;
public sealed class Program
{
public static void Main(string[] args)
{
try
{
using (SqlConnection connection = DatabaseConnection.CreateConnection())
{
connection.Open();
Console.WriteLine("Connection successful!\n");
const string sql = "UPDATE actor SET FirstName = 'Jane', LastName = 'Smith' WHERE ActorID = 11; SELECT * FROM actor WHERE ActorID = 11;";
using (SqlCommand command = new SqlCommand(sql, connection))
using (var reader = command.ExecuteReader())
{
if (reader.Read())
{
int actorId = reader.GetInt32(reader.GetOrdinal("ActorID"));
string firstName = reader.GetString(reader.GetOrdinal("FirstName"));
string lastName = reader.GetString(reader.GetOrdinal("LastName"));
Console.WriteLine($"Updated actor: {actorId}, {firstName}, {lastName}");
}
else
{
Console.WriteLine("No actor was inserted or found.");
}
}
}
}
catch (Exception ex)
{
Console.WriteLine("An error occurred: " + ex.Message);
}
}
}
Check the result in the application.
Delete rows in SQL Server
To complete the data cycle, we'll delete the row that we've previously added. This section shows how to use a simple DELETE command to remove data from the SQL Server table.
using System;
using Devart.Data.SqlServer;
public sealed class Program
{
public static void Main(string[] args)
{
try
{
using (SqlConnection connection = DatabaseConnection.CreateConnection())
{
connection.Open();
Console.WriteLine("Connection successful!\n");
const string sql = "DELETE FROM actor WHERE ActorID = 11;";
using (SqlCommand command = new SqlCommand(sql, connection))
{
int rowsAffected = command.ExecuteNonQuery();
if (rowsAffected > 0)
{
Console.WriteLine("Actor with ActorID = 11 deleted successfully.");
}
else
{
Console.WriteLine("No actor found with ActorID = 11.");
}
}
}
}
catch (Exception ex)
{
Console.WriteLine("An error occurred: " + ex.Message);
}
}
}
The output in the application confirms that the operation is successful.
