When you searching .NET Core database via Google, all results in the first five page are EF Core. But I am a Dapper fans, I thought I should introduce about .NET Core and Dapper.
Add Connection String to appsettings.json
.Net Core can use appsettings.json to store database connection string. Open the file and add a ConnectionString section to the file. You can maintain multiple connection strings in this section if you have more than one connection string.
“ConnectionStrings”: {
“Dev”: “Server=WIN-TAUP5KNN93J\SQLEXPRESS;Database=test;User Id=sa;Password=sa;”,
“Test”: “Server=WIN-TAUP5KNN93J\SQLEXPRESS;Database=live;User Id=sa;Password=sa;”
}
Create generic interface and class to pass the connection string between service
Because the heart of .NET Core is DI. we are going to use DI to pass the connection strings between different classes and services.
public interface IDBInfo
{
string GetConnectionString();
}
public class DBInfo : IDBInfo
{
private string _dbString;
public DBInfo(string connString)
{
_dbString = connString;
}
public string GetConnectionString()
{
return _dbString;
}
}
DI passing connection string to DBinfo class
Using below code to get connection string from setting file and pass to DBInfo class.
services.AddTransient(x=> new DBInfo(Configuration.GetConnectionString(“Dev”)));
Add Nuget Package
Open you database access project and add below two Nuget packages:
Install System.Data.SqlClient for using SqlConnection
Install Dapper
Using Dapper
To make things simple, I am only show Dapper related code at below. Dapper has number of extension methods. For detail Dapper API, you can get from Dapper Gehub project page. In below code shows how to pass connection string from contractor to service and fetch the data from table.
public class UserService : IUserService
{
#region Fields
private readonly IDBInfo _db;
#endregion
public UserService(IDBInfo dbInfo) {
_db = dbInfo;
}
public List GetUserList()
{
using (var con = new SqlConnection(_db.GetConnectionString()))
{
var users = con.Query(
“SELECT [User_Id],[PC_Name],[PC_IP],[First_Name],[Last_Name],[Password],[Is_Manager] FROM [tb_users]”
)
.ToList();
return users;
}
}
}