LearnAI ToolsCareerPractice BuildsPlayContact
Lesson 4120 min read

SQL Server with .NET Applications

Learn how to connect to SQL Server from a C# application using a connection string, with short ADO.NET and Entity Framework Core examples.

Introduction

Everything covered so far has run inside SSMS, but in real applications, T-SQL is almost always driven from application code. This lesson shows how a C# application connects to SQL Server, first with ADO.NET's lower-level SqlConnection and SqlCommand classes, then with the higher-level Entity Framework Core object-relational mapper (ORM). Both talk to the same SQL Server database - they just offer different levels of control and convenience.

What You Will Learn
  • How a connection string identifies a SQL Server database.
  • How to query SQL Server with ADO.NET's SqlConnection and SqlCommand.
  • How to read results with SqlDataReader.
  • Why parameterized queries matter from application code too.
  • A basic Entity Framework Core query example.

Connection Strings

A connection string tells the .NET data provider which server and database to connect to, and how to authenticate. It is typically stored in configuration (like appsettings.json), not hardcoded in source files.

// appsettings.json
// {
//   "ConnectionStrings": {
//     "SalesDb": "Server=localhost;Database=SalesDB;Trusted_Connection=True;TrustServerCertificate=True;"
//   }
// }


string connectionString =
    "Server=localhost;Database=SalesDB;Trusted_Connection=True;TrustServerCertificate=True;";
PartMeaning
ServerThe SQL Server instance to connect to
DatabaseThe specific database to use once connected
Trusted_Connection=TrueUse Windows Authentication instead of a SQL login/password
User Id / PasswordUsed instead of Trusted_Connection for SQL Server Authentication

ADO.NET: SqlConnection and SqlCommand

SqlConnection represents a connection to the database, and SqlCommand represents a T-SQL statement to run against it. Both implement IDisposable, so they should always be wrapped in a using statement to guarantee the connection is closed and released back to the pool.

using Microsoft.Data.SqlClient;


string connectionString =
    "Server=localhost;Database=SalesDB;Trusted_Connection=True;TrustServerCertificate=True;";


using (var connection = new SqlConnection(connectionString))
{
    connection.Open();


    var command = new SqlCommand("SELECT COUNT(*) FROM dbo.Customers", connection);
    int customerCount = (int)command.ExecuteScalar();


    Console.WriteLine($"Total customers: {customerCount}");
}
Console Output

Click Run to see what this code prints.

Reading Results with SqlDataReader

For queries that return multiple rows, ExecuteReader() returns a SqlDataReader, a fast, forward-only cursor you loop through with Read().

using (var connection = new SqlConnection(connectionString))
{
    connection.Open();
    var command = new SqlCommand(
        "SELECT CustomerID, FirstName, LastName FROM dbo.Customers WHERE City = 'Seattle'",
        connection);


    using (SqlDataReader reader = command.ExecuteReader())
    {
        while (reader.Read())
        {
            int id = reader.GetInt32(0);
            string firstName = reader.GetString(1);
            string lastName = reader.GetString(2);
            Console.WriteLine($"{id}: {firstName} {lastName}");
        }
    }
}
Console Output

Click Run to see what this code prints.

Parameterized Queries in ADO.NET

Just as with sp_executesql in T-SQL, application code should never concatenate user input into a SQL string. SqlCommand.Parameters provides the same protection against SQL injection.

string city = GetCityFromUserInput(); // untrusted input


var command = new SqlCommand(
    "SELECT CustomerID, FirstName, LastName FROM dbo.Customers WHERE City = @City",
    connection);
command.Parameters.AddWithValue("@City", city);


using SqlDataReader reader = command.ExecuteReader();
Never String-Concatenate SQL from C#

Building SQL with string interpolation or concatenation of user input (e.g. $"...WHERE City = '{city}'") is exactly as dangerous from C# as it is inside T-SQL, and just as exploitable. Always use SqlParameter.

Entity Framework Core Basics

Entity Framework Core (EF Core) is an ORM that maps C# classes to database tables, letting you write queries in C# (LINQ) instead of raw SQL. A DbContext represents a session with the database, and DbSet<T> properties represent tables.

public class Customer
{
    public int CustomerID { get; set; }
    public string FirstName { get; set; } = string.Empty;
    public string LastName { get; set; } = string.Empty;
    public string City { get; set; } = string.Empty;
}


public class SalesDbContext : DbContext
{
    public DbSet<Customer> Customers => Set<Customer>();


    protected override void OnConfiguring(DbContextOptionsBuilder options)
    {
        options.UseSqlServer(
            "Server=localhost;Database=SalesDB;Trusted_Connection=True;TrustServerCertificate=True;");
    }
}

Querying with EF Core

EF Core translates LINQ queries into parameterized T-SQL automatically, so filters like .Where() are safe from injection by default.

using var context = new SalesDbContext();


List<Customer> seattleCustomers = context.Customers
    .Where(c => c.City == "Seattle")
    .OrderBy(c => c.LastName)
    .ToList();


foreach (var customer in seattleCustomers)
{
    Console.WriteLine($"{customer.CustomerID}: {customer.FirstName} {customer.LastName}");
}
Console Output

Click Run to see what this code prints.

ADO.NET vs EF Core

AspectADO.NETEntity Framework Core
ControlFull control over exact SQL executedSQL is generated by EF from LINQ
ProductivityMore boilerplate per queryFaster to write typical CRUD code
Best forHigh-performance, fine-tuned queriesGeneral application development, rapid iteration
Learning curveLower-level, closer to raw SQLRequires learning EF's conventions and LINQ translation

Common Mistakes

Avoid These Mistakes
  • Not wrapping SqlConnection and SqlCommand in using statements, leaking connections from the pool.
  • Concatenating user input into SQL strings in C#, the same SQL injection risk covered in T-SQL.
  • Storing connection strings (with credentials) directly in source code instead of configuration or a secrets manager.
  • Calling .ToList() too early in an EF Core query, pulling the entire table into memory before filtering.
  • Not disposing of a DbContext, which can lead to connection and memory issues in long-running applications.

Best Practices

  • Always wrap SqlConnection, SqlCommand, and SqlDataReader in using statements.
  • Store connection strings in configuration, not hardcoded, and keep credentials out of source control.
  • Always use parameters (SqlParameter or LINQ expressions) instead of building SQL strings by hand.
  • Let EF Core translate filters (.Where(), .OrderBy()) into SQL rather than fetching everything and filtering in memory.
  • Choose ADO.NET for performance-critical paths and EF Core for everyday application CRUD, mixing both where appropriate.

Frequently Asked Questions

Microsoft.Data.SqlClient is the actively maintained, recommended package for new .NET applications; System.Data.SqlClient is the older, now largely legacy provider.

Yes, EF Core can call stored procedures directly (via FromSqlRaw/FromSqlInterpolated for queries, or ExecuteSqlRaw for commands), which is useful when you need to reuse logic already defined at the database level.

EF Core adds some overhead for translating LINQ to SQL and mapping results back to objects, but for most typical application workloads the difference is negligible; performance-critical hot paths can still drop down to raw ADO.NET or raw SQL through EF.

Both ADO.NET and EF Core reuse underlying physical connections from a pool behind the scenes when you Open()/Dispose() a connection with the same connection string, which is why opening and closing connections frequently in code is still efficient.

Key Takeaways

  • A connection string identifies the server, database, and authentication method for a .NET application.
  • ADO.NET's SqlConnection/SqlCommand/SqlDataReader give direct, low-level control over T-SQL execution.
  • Always use parameters from application code, exactly as with sp_executesql in T-SQL.
  • Entity Framework Core maps C# classes to tables and translates LINQ into parameterized SQL.
  • Choose ADO.NET or EF Core (or both) based on how much control versus productivity a given task needs.

Summary

Whether through ADO.NET or Entity Framework Core, .NET applications ultimately rely on the same safe, parameterized T-SQL principles covered throughout this course. In the final lesson, you will look at Azure SQL Database and wrap up with a set of general best practices for SQL Server and database design.

Next Lesson →

Azure SQL & Best Practices