MSDN Documentation

Home / Documentation / .NET / Connecting to Databases

Connecting to Databases with .NET

This document provides a comprehensive guide on how to connect to and interact with various databases using the .NET framework. We will explore different data access technologies and best practices for efficient and secure database operations.

Introduction to Data Access in .NET

The .NET ecosystem offers several powerful mechanisms for data access, allowing developers to choose the best approach for their specific needs. Key technologies include:

Using ADO.NET

ADO.NET provides a set of classes that expose data access services, enabling you to consume data from data sources and integrate data from multiple data sources into a unified dataset. The core components include:

Example: Connecting to SQL Server with ADO.NET


using System;
using System.Data.SqlClient;

public class DatabaseConnector
{
    public static void Main(string[] args)
    {
        string connectionString = "Server=myServerAddress;Database=myDataBase;User Id=myUsername;Password=myPassword;";

        using (SqlConnection connection = new SqlConnection(connectionString))
        {
            try
            {
                connection.Open();
                Console.WriteLine("Connection opened successfully!");

                // Example: Executing a simple query
                string query = "SELECT COUNT(*) FROM YourTable";
                using (SqlCommand command = new SqlCommand(query, connection))
                {
                    object result = command.ExecuteScalar();
                    Console.WriteLine($"Number of rows: {result}");
                }
            }
            catch (SqlException e)
            {
                Console.WriteLine($"Error: {e.Message}");
            }
        }
    }
}
Note: Always use parameterized queries to prevent SQL injection vulnerabilities.

Leveraging Entity Framework Core

Entity Framework Core (EF Core) abstracts away much of the low-level data access code. It allows you to define your database schema using C# classes and interact with your database using LINQ (Language Integrated Query).

Key Concepts in EF Core:

Example: Basic EF Core Usage


using Microsoft.EntityFrameworkCore;

public class MyDbContext : DbContext
{
    public DbSet<Product> Products { get; set; }

    protected override void OnConfiguring(DbContextOptionsBuilder optionsBuilder)
    {
        optionsBuilder.UseSqlServer("Server=myServerAddress;Database=myDataBase;User Id=myUsername;Password=myPassword;");
    }
}

public class Product
{
    public int ProductId { get; set; }
    public string Name { get; set; }
    public decimal Price { get; set; }
}

public class ProductRepository
{
    public void AddProduct(Product product)
    {
        using (var context = new MyDbContext())
        {
            context.Products.Add(product);
            context.SaveChanges();
        }
    }

    public Product GetProductById(int id)
    {
        using (var context = new MyDbContext())
        {
            return context.Products.Find(id);
        }
    }
}
Tip: Explore EF Core's performance optimization techniques, such as lazy loading, eager loading, and explicit loading, to improve application responsiveness.

Working with Dapper

Dapper is an excellent choice when you need high performance and fine-grained control over your SQL queries. It maps query results to .NET objects with minimal overhead.

Example: Using Dapper


using Dapper;
using System.Data;
using System.Data.SqlClient;

public class DapperProductRepository
{
    private string _connectionString;

    public DapperProductRepository(string connectionString)
    {
        _connectionString = connectionString;
    }

    public Product GetProduct(int id)
    {
        using (IDbConnection db = new SqlConnection(_connectionString))
        {
            return db.Query<Product>("SELECT * FROM Products WHERE ProductId = @Id", new { Id = id }).FirstOrDefault();
        }
    }

    public void InsertProduct(Product product)
    {
        using (IDbConnection db = new SqlConnection(_connectionString))
        {
            string sql = "INSERT INTO Products (Name, Price) VALUES (@Name, @Price)";
            db.Execute(sql, product);
        }
    }
}

Best Practices