SQL Server Integration with .NET Applications Using ADO.NET
Quick Answer: ADO.NET is a .NET data access technology. We use it to connect applications with SQL Server, execute SQL queries, and read or update database records. Microsoft.Data.SqlClient provides the SQL Server connection classes for modern .NET applications.
1. Why Do We Need ADO.NET?
Imagine Lakshmi works as a receptionist in a hospital. The hospital manages 5,000 patient records and hundreds of appointments every day.
Every patient has a registration number. Each patient also has different medical details and appointments.
How can Lakshmi's hospital application retrieve patient records from SQL Server?
The problem without ADO.NET
We could keep patient information inside a C# collection. This works temporarily, but the application cannot retrieve permanent database records this way.
Example 1: Store patient records in application memory
This C# example stores three patient records without database access.
var patients = new[]
{
(101, "Ananya", "Cardiology"),
(102, "Rahul", "Neurology"),
(103, "Priya", "Orthopedics")
};
foreach (var patient in patients)
{
Console.WriteLine(
$"{patient.Item1} - {patient.Item2}");
}
Expected output
101 - Ananya 102 - Rahul 103 - Priya
This example displays patient records correctly. However, the design has problems.
- Records exist only inside application memory.
- Different application instances cannot share this collection directly.
- Changes are not automatically saved into SQL Server.
- Managing thousands of patient records becomes difficult.
Now think about the same application across multiple hospital departments.
We need permanent database storage. We also need a reliable way for .NET applications to communicate with SQL Server.
The solution: ADO.NET
ADO.NET = a .NET technology for accessing and managing data sources.
SqlConnection = a class that manages a connection to SQL Server.
SqlCommand = a class that executes SQL statements and stored procedures.
SqlDataReader = a class that reads query results one record at a time.
SQL Parameter = a separate value supplied to a SQL command.
We use these classes to retrieve Lakshmi's patient records. Each class handles a specific part of database communication.
Animation 1: How ADO.NET communicates with SQL Server
Follow Lakshmi's request to retrieve patient number 101.
Lakshmi requests patient 101. ADO.NET sends the query and reads Ananya's record from SQL Server.
2. Implement SQL Server Integration in C# Using ADO.NET
Let's connect a .NET application to Lakshmi's hospital database.
NuGet Package = a reusable library that we add to a .NET project.
Connection String = configuration that identifies the server, database and authentication method.
ExecuteReaderAsync = executes a query and reads its returned records asynchronously.
Install the SQL Server data provider
Modern .NET applications should use Microsoft.Data.SqlClient for SQL Server access.
Run this command inside your .NET project directory:
dotnet add package Microsoft.Data.SqlClient
Next, create a Patients table inside the HospitalDB database. Assume it contains patient 101, Ananya, from Cardiology.
Connect and retrieve patient information
Example 2: Retrieve a patient using ADO.NET
This complete example assumes .NET 8 or later and an existing HospitalDB database. The sample uses Windows authentication on a local SQL Server instance.
using System.Data;
using Microsoft.Data.SqlClient;
var connectionString =
"Server=localhost;Database=HospitalDB;" +
"Integrated Security=True;Encrypt=True;" +
"TrustServerCertificate=True;";
await using var connection =
new SqlConnection(connectionString);
await connection.OpenAsync();
const string sql =
"SELECT PatientId, PatientName, Department " +
"FROM Patients WHERE PatientId = @PatientId;";
await using var command =
new SqlCommand(sql, connection);
command.Parameters.Add("@PatientId", SqlDbType.Int).Value = 101;
await using var reader =
await command.ExecuteReaderAsync();
while (await reader.ReadAsync())
{
Console.WriteLine(
$"{reader.GetInt32(0)} - {reader.GetString(1)} - {reader.GetString(2)}");
}
Expected output
101 - Ananya - Cardiology
Notice how SqlConnection opens the database connection. The SqlCommand executes the SQL statement.
The @PatientId parameter safely supplies the patient number. The SqlDataReader reads the returned patient information.
Development note: TrustServerCertificate=True is used here for a local learning environment. It skips server certificate validation. Production applications should use trusted certificates and proper connection configuration.
3. Quick Summary
Without ADO.NET vs with ADO.NET
| Without ADO.NET | With ADO.NET |
|---|---|
| Data remains in application memory. | Applications retrieve database records. |
| No direct SQL Server communication. | SqlConnection manages database connections. |
| Manual data handling is needed. | SqlDataReader reads query results. |
| Query values need manual handling. | Parameters safely supply query values. |
The important terms to remember
- ADO.NET: .NET data access technology.
- Microsoft.Data.SqlClient: SQL Server data provider for .NET.
- SqlConnection: Manages database connections.
- SqlCommand: Executes SQL statements and procedures.
- SqlDataReader: Reads query results efficiently.
4. Common Mistakes and Best Practices
1. Concatenating values into SQL queries
Building SQL statements with user input can create SQL injection risks.
Better approach: Use parameters such as @PatientId for all query values.
2. Forgetting to close database connections
Unused connections can consume resources and affect application performance.
Better approach: Use using or await using to dispose database objects.
3. Hardcoding production connection strings
Connection strings may contain sensitive authentication information.
Better approach: Store configuration securely using environment variables or a secret manager.
5. How ADO.NET Integration Comes Up in Interviews
Question 1: What is ADO.NET?
What the interviewer expects: Explain how .NET applications access databases.
Strong answer: "ADO.NET is a .NET data access technology. I use it to connect applications with SQL Server, execute queries, and retrieve or update records."
Question 2: What is the difference between SqlDataReader and DataSet?
What the interviewer expects: Explain connected and disconnected data access.
Strong answer: "SqlDataReader reads records in a forward-only, read-only manner while the connection remains open. DataSet stores data in memory and can work independently after loading."
Question 3: How do you prevent SQL injection in ADO.NET?
What the interviewer expects: Explain parameterized SQL commands.
Strong answer: "I use SqlParameter objects instead of joining user input into SQL statements. This separates query values from SQL commands and helps prevent SQL injection."
6. Hands-On Practice
Beginner: School management application
Create a .NET application that connects to SchoolDB and retrieves student records using ADO.NET.
Expected result: Display student IDs and names from SQL Server.
Intermediate: Banking application
Build an account lookup feature for Ravi's banking application. Retrieve account details using a parameterized query.
Hint: Use SqlCommand and SqlDataReader.
Advanced: Hospital appointment management
Extend Lakshmi's hospital application to manage patient appointments.
- Insert new appointment records.
- Retrieve appointments using patient IDs.
- Update appointment information.
- Use parameterized commands and asynchronous methods.
Challenge: Implement a complete CRUD workflow using ADO.NET.
7. Next Steps and Community
Previous article: Real-World SQL Queries – Solve Business Reporting Problems.
Before starting, remember how SQL queries retrieve business information. Now we connect those queries to a .NET application using ADO.NET.
Next article: SQL Server Production Best Practices – Security, Backup and Recovery.
For more details, read the official Microsoft documentation:
- Microsoft Learn: Microsoft.Data.SqlClient for SQL Server
- Microsoft Learn: Configure Parameters with Microsoft.Data.SqlClient
- Microsoft Learn: Connection Strings
Try Lakshmi's hospital example and add appointment management. Share your implementation and discuss different database access approaches with other learners.