Learning Objectives
- Understand how to use ADO.NET for database interaction in .NET Core.
- Learn to perform CRUD operations with stored procedures in SQL Server.
- Learn best practices for SQL connection management and working with commands and parameters.
ADO.NET Basics
What is ADO.NET? ADO.NET is a data access technology that allows .NET applications to interact with databases. It provides the means to connect, execute commands, and retrieve results from databases such as SQL Server.
- In this chapter, we'll use
System.Data.SqlClientandMicrosoft.Data.SqlClientto interact with SQL Server. - ADO.NET is lightweight and high-performance, and it's a good alternative to Entity Framework (EF) for those who prefer direct database interaction.
Setting Up SQL Connection
To connect to a SQL Server database, we need to use SqlConnection:
using System.Data.SqlClient;
string connectionString = "Your_Connection_String_Here";
using (SqlConnection connection = new SqlConnection(connectionString))
{
connection.Open();
// Execute database commands
}
Real-Life Example: In an e-commerce application, use SqlConnection to fetch product details directly from the database.
Working with Stored Procedures
What are Stored Procedures? A stored procedure is a prepared SQL code that can be saved and reused. Instead of sending raw SQL commands, you call a stored procedure, which can help reduce SQL injection risks and improve maintainability.
Creating a Stored Procedure in SQL Server:
CREATE PROCEDURE GetProductById
@ProductId INT
AS
BEGIN
SELECT * FROM Products WHERE Id = @ProductId;
END
Calling a Stored Procedure Using ADO.NET: We use SqlCommand to execute stored procedures.
using (SqlConnection connection = new SqlConnection(connectionString))
{
connection.Open();
using (SqlCommand command = new SqlCommand("GetProductById", connection))
{
command.CommandType = CommandType.StoredProcedure;
command.Parameters.AddWithValue("@ProductId", 1);
using (SqlDataReader reader = command.ExecuteReader())
{
while (reader.Read())
{
Console.WriteLine($"Product Name: {reader["Name"]}, Price: {reader["Price"]}");
}
}
}
}
Real-Life Example: Use stored procedures in a financial application to execute transactions safely without exposing raw SQL to the application.
SQL Connection Management
Managing Connections and Commands
- Always open a connection right before executing a command and close it immediately afterward.
- Use
usingstatements to ensure connections are closed automatically, even in case of an error.
Example with Best Practices:
using (SqlConnection connection = new SqlConnection(connectionString))
{
try
{
connection.Open();
using (SqlCommand command = new SqlCommand("AddNewProduct", connection))
{
command.CommandType = CommandType.StoredProcedure;
command.Parameters.AddWithValue("@Name", "New Product");
command.Parameters.AddWithValue("@Price", 100.0m);
command.ExecuteNonQuery();
}
}
catch (Exception ex)
{
Console.WriteLine($"An error occurred: {ex.Message}");
}
}
Real-Life Example: In a user management system, use connection management best practices to create new user accounts and ensure the connection is properly closed.
Examples
Simple Example: Fetching Data with a Stored Procedure
Create a stored procedure to get a product by ID and call it using ADO.NET.
Code Snippet:
using (SqlConnection connection = new SqlConnection(connectionString))
{
connection.Open();
using (SqlCommand command = new SqlCommand("GetProductById", connection))
{
command.CommandType = CommandType.StoredProcedure;
command.Parameters.AddWithValue("@ProductId", 1);
using (SqlDataReader reader = command.ExecuteReader())
{
while (reader.Read())
{
Console.WriteLine($"Product: {reader["Name"]}, Price: {reader["Price"]}");
}
}
}
}
Real-Life Example: Fetch product information from an e-commerce database to display to customers.
Simple Example: Adding Data Using a Stored Procedure
Create a stored procedure for adding a new product and call it from your code.
Code Snippet:
using (SqlConnection connection = new SqlConnection(connectionString))
{
connection.Open();
using (SqlCommand command = new SqlCommand("AddNewProduct", connection))
{
command.CommandType = CommandType.StoredProcedure;
command.Parameters.AddWithValue("@Name", "Tablet");
command.Parameters.AddWithValue("@Price", 300.0m);
command.ExecuteNonQuery();
}
}
Real-Life Example: In an inventory management system, add new products to the database.
Simple Example: Updating Data Using a Stored Procedure
Create a stored procedure for updating a product's price and call it from your code.
Code Snippet:
using (SqlConnection connection = new SqlConnection(connectionString))
{
connection.Open();
using (SqlCommand command = new SqlCommand("UpdateProductPrice", connection))
{
command.CommandType = CommandType.StoredProcedure;
command.Parameters.AddWithValue("@ProductId", 1);
command.Parameters.AddWithValue("@Price", 1200.0m);
command.ExecuteNonQuery();
}
}
Real-Life Example: In a financial application, adjust the price of a product during a promotional campaign.
Key Takeaways
- ADO.NET allows you to directly interact with a SQL Server database, providing a high degree of control.
- Stored procedures are used for executing database commands more efficiently and securely.
- Connection management is crucial to ensure that database resources are used correctly and avoid potential issues like connection leaks.
Practical Questions
- How can you use a stored procedure in ADO.NET to fetch data from a SQL Server database?
- What are the best practices for managing SQL connections in ADO.NET?
- How can you use
SqlCommandto execute a stored procedure that updates a record in a database?
New Concepts in .NET 8
New in .NET 8:
- Improved integration for ADO.NET to support Native AOT Compilation, allowing you to create smaller and faster executables. This is particularly useful when deploying APIs to cloud environments where cold-start performance is crucial.
Real-World Application
These concepts are highly applicable in building real-world, scalable APIs where direct database access with high performance is required. You could use ADO.NET to interact directly with your database in scenarios where EF's abstraction is not necessary or might introduce performance overhead, such as:
- E-commerce systems: Directly retrieving product information.
- User management systems: Safely storing user credentials.
- Financial applications: Executing secure transactions via stored procedures.
e-commerce scenario example
This example demonstrates an e-commerce scenario where we have a product API that handles fetching product details. It follows a layered approach:
- Controller: Manages the incoming HTTP request and returns a response.
- Service (Business Logic Layer): Contains the business logic, including calling the database repository.
- Repository (Data Access Layer): Uses ADO.NET to interact with the SQL Server database.
Step-by-Step Full Example
Step 1: Create the Product Model
Define a Product model that will be used to represent the product data.
public class Product
{
public int Id { get; set; }
public string Name { get; set; }
public decimal Price { get; set; }
}
Step 2: Create the Repository Layer for Database Interaction
The repository will handle the direct interaction with the database using ADO.NET.
using System.Data.SqlClient;
public interface IProductRepository
{
Product GetProductById(int productId);
}
public class ProductRepository : IProductRepository
{
private readonly string _connectionString;
public ProductRepository(string connectionString)
{
_connectionString = connectionString;
}
public Product GetProductById(int productId)
{
Product product = null;
using (SqlConnection connection = new SqlConnection(_connectionString))
{
connection.Open();
using (SqlCommand command = new SqlCommand("GetProductById", connection))
{
command.CommandType = System.Data.CommandType.StoredProcedure;
command.Parameters.AddWithValue("@ProductId", productId);
using (SqlDataReader reader = command.ExecuteReader())
{
if (reader.Read())
{
product = new Product
{
Id = (int)reader["Id"],
Name = reader["Name"].ToString(),
Price = (decimal)reader["Price"]
};
}
}
}
}
return product;
}
}
Stored Procedure: We are calling a stored procedure named GetProductById to get the product details.Connection Management: The connection is managed using a using statement to ensure it is closed properly.
Step 3: Create the Service Layer for Business Logic
The service layer will handle any business logic required for product retrieval.
public interface IProductService
{
Product GetProductById(int productId);
}
public class ProductService : IProductService
{
private readonly IProductRepository _productRepository;
public ProductService(IProductRepository productRepository)
{
_productRepository = productRepository;
}
public Product GetProductById(int productId)
{
// Add any additional business logic here if needed
return _productRepository.GetProductById(productId);
}
}
The service simply calls the repository to get the product but can also have additional business logic if needed.
Step 4: Create the Product Controller
The controller will handle the HTTP request and interact with the service to get the product data.
using Microsoft.AspNetCore.Mvc;
[ApiController]
[Route("api/[controller]")]
public class ProductController : ControllerBase
{
private readonly IProductService _productService;
public ProductController(IProductService productService)
{
_productService = productService;
}
[HttpGet("{id}")]
public IActionResult GetProduct(int id)
{
var product = _productService.GetProductById(id);
if (product == null)
{
return NotFound("Product not found");
}
return Ok(product);
}
}
Routing: The controller route is defined as api/product, and the action route is /{id}, allowing clients to call GET /api/product/{id}.IActionResult: Returns either 200 OK with the product data or 404 Not Found if the product doesn’t exist.
Step 5: Configure Dependency Injection in Program.cs
Add the necessary services to the dependency injection container and configure the application.
var builder = WebApplication.CreateBuilder(args);
// Add services to the container.
builder.Services.AddControllers();
// Add connection string from appsettings.json or environment variable
string connectionString = builder.Configuration.GetConnectionString("DefaultConnection");
// Register repository and service for dependency injection
builder.Services.AddScoped<IProductRepository>(provider => new ProductRepository(connectionString));
builder.Services.AddScoped<IProductService, ProductService>();
var app = builder.Build();
// Configure the HTTP request pipeline.
app.UseRouting();
app.UseAuthorization();
app.UseEndpoints(endpoints =>
{
endpoints.MapControllers();
});
app.Run();
Connection String: The connection string is retrieved from the configuration and passed to the repository.DI Registration:
ProductRepository and ProductService are registered with the DI container so they can be injected into the controller.
Step 6: SQL Stored Procedure Example
Ensure you have a stored procedure in your SQL Server database to retrieve product details by ID.
CREATE PROCEDURE GetProductById
@ProductId INT
AS
BEGIN
SELECT Id, Name, Price FROM Products WHERE Id = @ProductId;
END
Summary
This example follows an end-to-end flow:
- Controller (
ProductController):- Receives a request (
GET /api/product/{id}) and calls the service layer. - Returns
200 OKwith product data or404 Not Foundif the product does not exist.
- Receives a request (
- Service Layer (
ProductService):- Acts as a bridge between the controller and repository.
- Contains business logic (in this case, it’s simple, but it can be expanded).
- Repository Layer (
ProductRepository):- Connects to SQL Server using ADO.NET.
- Executes the stored procedure
GetProductByIdto fetch the product from the database. - Manages SQL connections, commands, and parameters safely.
Real-Life Use Case
Imagine you have an e-commerce application:
- A client wants to fetch product details by providing a product ID.
- They call your endpoint (
GET /api/product/{id}). - The controller sends the request to the business layer (
ProductService). - The business layer calls the data access layer (
ProductRepository), which queries the SQL Server database via a stored procedure. - The response is returned through the controller as a JSON object to the client.
This pattern follows separation of concerns, where:
- Controllers are responsible for handling HTTP requests and responses.
- The service layer deals with business logic.
- The repository handles data access.
This approach is suitable for real-world projects where scalability, maintainability, and reusability are key considerations.