.NET Core database connection Methods, Steps, and Best Practices

.NET Core database connection Learn how to connect SQL Server with .NET Core through ADO.NET, Entity Framework Core, and Dapper. This comprehensive guide covers step-by-step instructions and best practices for each method.

Connecting SQL Server with .NET Core is a common task for developers looking to build robust, data-driven applications. .NET Core, now known as .NET 5 and later versions, provides several ways to connect to SQL Server. This article outlines the primary methods for connecting SQL Server with .NET Core and explains each method step by step.

1. Using ADO.NET

ADO.NET is a data access technology that provides a set of classes for connecting to databases, executing commands, and managing data. .NET Core database connection Here’s how to use ADO.NET with .NET Core:

Nx Monorepo: The Future of Web Development

.NET Core database connection Methods, Steps, and Best Practices Step-by-Step Guide:

1. Install the SQL Server NuGet Package:
   Open your terminal or package manager console and install the System.Data.SqlClient package, which is necessary for SQL Server interactions.

   dotnet add package System.Data.SqlClient

2. Configure Your Connection String:
   Add your connection string to the appsettings.json file.

   {
     "ConnectionStrings": {
       "DefaultConnection": "Server=myServerAddress;Database=myDataBase;User Id=myUsername;Password=myPassword;"
     }
   }

3. Create a Data Access Class:
   Create a class that uses SqlConnection, SqlCommand, and SqlDataReader to interact with SQL Server.

   using System;
   using System.Data.SqlClient;
   using Microsoft.Extensions.Configuration;

   public class DataAccess
   {
       private readonly string _connectionString;

       public DataAccess(IConfiguration configuration)
       {
           _connectionString = configuration.GetConnectionString("DefaultConnection");
       }

       public void GetData()
       {
           using (SqlConnection connection = new SqlConnection(_connectionString))
           {
               connection.Open();
               SqlCommand command = new SqlCommand("SELECT * FROM YourTable", connection);
               SqlDataReader reader = command.ExecuteReader();

               while (reader.Read())
               {
                   Console.WriteLine(reader["ColumnName"].ToString());
               }
           }
       }
   }

4. Use the Data Access Class:
   Inject the IConfiguration service and use your DataAccess class in a controller or service.

   public class MyController : ControllerBase
   {
       private readonly DataAccess _dataAccess;

       public MyController(DataAccess dataAccess)
       {
           _dataAccess = dataAccess;
       }

       [HttpGet]
       public IActionResult Get()
       {
           _dataAccess.GetData();
           return Ok();
       }
   }

2. Using Entity Framework Core

Entity Framework Core (EF Core) is an Object-Relational Mapper (ORM) that simplifies data access by allowing you to interact with your database using C# objects. .NET Core database connection

Step-by-Step Guide:

1. Install the EF Core Packages:
   Add the necessary NuGet packages for EF Core and SQL Server.

   dotnet add package Microsoft.EntityFrameworkCore
   dotnet add package Microsoft.EntityFrameworkCore.SqlServer

2. Create a DbContext:
   Define a DbContext class to represent your database context.

   using Microsoft.EntityFrameworkCore;

   public class AppDbContext : DbContext
   {
       public AppDbContext(DbContextOptions<AppDbContext> options)
           : base(options)
       {
       }

       public DbSet<YourEntity> YourEntities { get; set; }
   }

   public class YourEntity
   {
       public int Id { get; set; }
       public string Name { get; set; }
   }

3. Configure the DbContext in Startup:
   Register your DbContext in the Startup.cs file.

   public class Startup
   {
       public void ConfigureServices(IServiceCollection services)
       {
           services.AddDbContext<AppDbContext>(options =>
               options.UseSqlServer(Configuration.GetConnectionString("DefaultConnection")));
           services.AddControllers();
       }
   }

4. Perform Database Operations:
   Use dependency injection to access the DbContext and perform CRUD operations.

   public class YourService
   {
       private readonly AppDbContext _context;

       public YourService(AppDbContext context)
       {
           _context = context;
       }

       public async Task<List<YourEntity>> GetEntitiesAsync()
       {
           return await _context.YourEntities.ToListAsync();
       }
   }

5. Use the Service in a Controller:
   Inject your service and use it in your controller.

   public class YourController : ControllerBase
   {
       private readonly YourService _service;

       public YourController(YourService service)
       {
           _service = service;
       }

       [HttpGet]
       public async Task<IActionResult> Get()
       {
           var entities = await _service.GetEntitiesAsync();
           return Ok(entities);
       }
   }

3. Using Dapper

.NET Core database connection: Dapper is a lightweight ORM that provides a faster alternative to Entity Framework Core by mapping SQL queries to C# objects.

Step-by-Step Guide:

1. Install the Dapper Package:
   Add the Dapper NuGet package to your project.

   dotnet add package Dapper

2. Create a Repository Class:
   Use SqlConnection along with Dapper’s extension methods to execute queries.

   using System.Collections.Generic;
   using System.Data.SqlClient;
   using System.Threading.Tasks;
   using Dapper;
   using Microsoft.Extensions.Configuration;

   public class DapperRepository
   {
       private readonly string _connectionString;

       public DapperRepository(IConfiguration configuration)
       {
           _connectionString = configuration.GetConnectionString("DefaultConnection");
       }

       public async Task<IEnumerable<YourEntity>> GetEntitiesAsync()
       {
           using (SqlConnection connection = new SqlConnection(_connectionString))
           {
               string query = "SELECT * FROM YourTable";
               return await connection.QueryAsync<YourEntity>(query);
           }
       }
   }

3. Use the Repository:
   Inject your repository into a service or controller to use it.

   public class YourController : ControllerBase
   {
       private readonly DapperRepository _repository;

       public YourController(DapperRepository repository)
       {
           _repository = repository;
       }

       [HttpGet]
       public async Task<IActionResult> Get()
       {
           var entities = await _repository.GetEntitiesAsync();
           return Ok(entities);
       }
   }

Conclusion

.NET Core database connection can be achieved through several methods, each suitable for different scenarios:

• ADO.NET offers a straightforward and low-level approach, giving you complete control over SQL queries and connection management.
• Entity Framework Core provides an ORM-based solution with rich features for managing database entities and relationships.
• Dapper is a high-performance micro-ORM ideal for scenarios where you need a balance between raw SQL and ORM features.

.NET documentation