Dapper Data Access Layer (Extension for Visual Studio)

Introduction

This extension adds a Visual Studio C# library project template (DAL) Data Access Layer that helps with working and connecting to the SQL server database using an “app.config” file connection string. It also contains methods that create and gets the data from the database and saves data to the database using data model classes and Dapper ORM.

Dapper DAL Project

This is a Visual Studio project template that creates a C# library project with classes and interfaces and methods to work as “Data Access Layer” using Dapper ORM.

v1.0

Support for Visual Studio 2017.

V1.1

Support for Visual Studio 2019.

Preview

Part One — Setup Projects

First, set up your project

1. Create your project.
2. Open your project app.config file add the connection string of your server.

Next, set up your Dapper Project

Open “DataAccess\SqlConnector.cs” File and change the db const varibale value to your connection string name in your app.config file.

Part Two — Wire Up Projects

1. Add the dapper DAL library as a reference in your project.
2. Open your program.cs or any file you want to use as connection Initializer in your project, and add this
3. code :

Note

First, add a using statement for the dapper library.

GlobalConfig.InitializeConnection(new SqlConnector());

Part Three — Perform Action on Data Base

Use Create(dataModel,commandText,commandType) method to insert , update , delete in your database, and use Get (commandText, commandType) method to get data from database.

Those methods are implemented from IDataConnection interface in SqlConnector class.

This is how they look:

public T Create<T>(T model, string commandText, CommandType commandType = CommandType.StoredProcedure)

{

using (IDbConnection connection = new System.Data.SqlClient.SqlConnection(GlobalConfig.GetConnectionString(db)))

{

connection.Execute(commandText, DapperHelper.GenerateParameters(model,false), commandType: commandType);

return model;

}

}

public List<T> Get<T>(string commandText, CommandType commandType = CommandType.StoredProcedure)

{

List<T> output = new List<T>();

using (IDbConnection connection = new System.Data.SqlClient.SqlConnection(GlobalConfig.GetConnectionString(db)))

{

output = connection.Query<T>(commandText, commandType: commandType).AsList();

return output;

}

}

To use them, for example, you have person model class look like this:

public class PersonModel

{

public int Id {get;set;}

public string FullName {get;set;}

public string Address {get;set;}

}

Say that we have stored procedure called spPerson_Insert and an other one called spPerson_Get, to create new person in your database you have to do this:

PersonModel p = new PersonModel { Id = 1 , FullName = “Abubakr” , Address = “Sudan” };

GlobalConfig.Connection.Create(p,”spPerson_Insert”,CommandType.StoredProcedure);

Use it the same way with the Get() method:

List<PersonModel> people = GlobalConfig.Connection.Get(“spPerson_Get”,CommandType.StoredProcedure)

GitHub Repository — Dapper DAL Project

Extension In Visual Studio Market Place — DapperDALProject

License

MIT license