Implementing CURD Using Dapper(ORM) With C# WebAPI

Object Relational Mapper(ORM)

Object Relational Mapping is the concept of writing queries using the object-oriented paradigm of the preferred programming language. ORM is used to interact with the SQL databases using our preferred language instead of SQL. For example, Entity Framework for ASP.NET.

So basically, an ORM creates objects based on database queries.

Now, there is another thing called Micro-ORM, which is a lightweight version of a full ORM. It does not come with all the heavy features of a full ORM and saves a lot of work, but it still does the mapping between the database and our preferred programming language.

Dapper is an example of Micro ORM; in fact, it is called the King of Micro ORM because of its speed and ease of work,

• First, it creates an IDbConnection object and allows us to write queries to perform CRUD operations on the database.
• Then, it passes the queries as one of its various parameters through one of its various methods.

Methods of Dapper

Declare the Connection String and initiate the IDbConnection

static string connectionString = ConnectionString.CName;
string query = null;
static IDbConnection db = new SqlConnection(connectionString);

1. Execute

Execute is a method called from an IDbConnection type object which can execute a command one or multiple times and return the number of affected rows in the database tables. It can execute Stored Procedures, SELECT/INSERT/DELETE statements, etc.

//execute
public int AddStudent(Student student) {
  int cnt = 0;
  using(SqlConnection con = new SqlConnection(connectionString)) {
    var dp = new DynamicParameters();

    dp.Add("@FirstName", student.FirstName, DbType.String, direction: ParameterDirection.Input);
    dp.Add("@LastName", student.LastName, DbType.String, direction: ParameterDirection.Input);
    dp.Add("@Email", student.Email, DbType.String, direction: ParameterDirection.Input);
    dp.Add("@Mobile", student.Mobile, DbType.String, direction: ParameterDirection.Input);
    dp.Add("@Address", student.Address, DbType.String, direction: ParameterDirection.Input);
    con.Open();

    query = "Insert into Student (FirstName,LastName,Email, Mobile,Address) Values(@FirstName, @LastName, @Email, @Mobile, @Address)";
    cnt = con.Execute(query, dp);
    con.Close();

  }
  return cnt;
}

//execute
public int UpdateStudent(Student student) {
  int cnt = 0;
  using(SqlConnection con = new SqlConnection(connectionString)) {
    var dp = new DynamicParameters();
    query = "update student set Lastname ='" + student.LastName + "', FirstName='" + student.FirstName + "' where id =" + student.Id;
    cnt = con.Execute(query, dp);

  }
  return cnt;
}

Execute the logic by calling the store procedure and passing the input parameter to it.

//Execute (proc)
public int UpdateStudentBySP(Student student) {
  int cnt = 0;
  using(SqlConnection con = new SqlConnection(connectionString)) {
    var proc = "spUpdateStudent";
    var dp = new DynamicParameters();
    dp.Add("@LastName", student.LastName, DbType.String, direction: ParameterDirection.Input);
    dp.Add("@id", student.Id);

    cnt = con.Execute(proc, dp, commandType: CommandType.StoredProcedure);

  }
  return cnt;
}

2. Query

The query is a method called from IDbConnection type object which can execute a query and map the result.

//Query (proc)
public List < Student > AllStudent() {
  List < Student > lstStudent = new List < Student > ();
  var proc = "spGetAllStudent";
  lstStudent = db.Query < Student > (proc, null, commandType: CommandType.StoredProcedure).ToList();

  return lstStudent;
}

//Query (proc)
public List < Student > AllStudent() {
  List < Student > lstStudent = new List < Student > ();
  var proc = "spGetAllStudent";
  lstStudent = db.Query < Student > (proc, null, commandType: CommandType.StoredProcedure).ToList();

  return lstStudent;
}

3. QueryFirst

QueryFirst is a method called from IDbConnection type object which can execute a query and map the first result.

4. QuerySingle

QuerySingle is a method called from IDbConnection type object which can execute a query and map the first result, and throws an exception if there is not exactly one element in the sequence.

//QuerySingleOrDefault
public Student GetStudentData(int ? id) {
  Student student = new Student();

  using(SqlConnection con = new SqlConnection(connectionString)) {
    query = "SELECT * FROM Student WHERE Id= " + id;
    student = con.QuerySingleOrDefault < Student > (query);

  }
  return student;
}

5. ExecuteScalar

It returns the first column of the first row from the result set returned by the query. Any additional columns or rows are ignored.

In the code below, we use a stored procedure to return a single record or value using an output parameter.

//ExecuteScalar
public int checkStudent(string location) {
  int status = 0;
  using(SqlConnection con = new SqlConnection(connectionString)) {
    var proc = "spFetchStudentByLocation";
    var dp = new DynamicParameters();
    dp.Add("@location", location, DbType.String, direction: ParameterDirection.Input);
    dp.Add("@status", 0, DbType.Int32, direction: ParameterDirection.Output);
    con.ExecuteScalar(proc, dp, commandType: CommandType.StoredProcedure);
    status = dp.Get < int > ("@status");
  }
  return status;
}

I hope you got a basic idea or scenario of micro ORM and its use with C# WebAPI.