Introducing Dapper

What is a Dapper? Dapper is a small and very fast ORM developed by Stackoverflow. Dapper works with any type of database and is not specific to a specific database. Using Dapper, you can execute SQL statements as Stored Procedure or as direct SQL statements. If you have to write Stored Procedure in a project or use direct SQL commands instead of using ORMs like EF and NHibernate, Dapper is the best option for you. Using Dapper is very simple. You can write SQL queries and keep the results in C # classes. To use Dapper, you must first enter the following command through the package console manager.

Install-Package Dapper

Next we want to add a new record to the database using Dapper  

class Program
{
    public static async Task Main(string[] args)
    {
        var connectionString = "Data Source=.;Initial Catalog=Dapper;Integrated Security=True";
        using (SqlConnection connection = new SqlConnection(connectionString))
        {
            DynamicParameters parameters = new DynamicParameters();
            parameters.Add("Name", "Farhad", DbType.String);
            parameters.Add("Family", "Zamani", DbType.String);
            parameters.Add("Age", 21, DbType.Int32);
            var affectedRows = await connection.ExecuteAsync("INSERT INTO USERS (Name,Family,Age) Values(@Name,@Family,@Age)", parameters);
            Console.WriteLine(affectedRows);
            Console.ReadLine();
        }
    }
}

In the above code, we use DynamicParameters to specify the parameters needed to add a new record to the user table. By doing this, we have specified the type of data, for example, we have used Int32 for the Age field, and we have also prevented SQL Injection. Finally, using the ExecuteAsync method extension written in the Dapper library, we send the query to the database, and if we run the following code, the value 1 is returned and stored in the affectedRows variable, which means that a record is added to the Users table. Has been. Next we want to read the list of users from the database using Dapper. First we create a model to store users whose properties are equal to the columns of the user table:

public class Users
{
    public int Id { get; set; }
    public string Name { get; set; }
    public string Family { get; set; }
    public int Age { get; set; }
}

And then we read the list of users using Dapper and save it in a list of user model types.

public static async Task Main(string[] args)
{
    var connectionString = "Data Source=.;Initial Catalog=Dapper;Integrated Security=True";
    using (SqlConnection connection = new SqlConnection(connectionString))
    {
        IEnumerable<Users> users = (await connection.QueryAsync<Users>("SELECT * FROM Users")).AsEnumerable();
        foreach (var user in users)
        {
            Console.WriteLine(user.Name + "\t" + user.Family + "\t" + user.Age);
        }
        Console.ReadLine();
    }
}

The only difference between reading the data code and adding information in the query and method was that we used QueryAsync instead of ExecuteAsync to read the data because in the above code we sent a query to the database and received a series of information from the database (Query) but in adding the user We added a record to the database and this is an Execute Command (Command). Finally, if you run the above code, you will get the following output.

Farhad  Zamani  21

If we want to get only the number of users, we can do the following:

public static async Task Main(string[] args)
{
    var connectionString = "Data Source=.;Initial Catalog=Dapper;Integrated Security=True";
    using (SqlConnection connection = new SqlConnection(connectionString))
    {
        int userCount = await connection.QueryFirstAsync<int>("SELECT COUNT(1) FROM Users");
        Console.WriteLine(userCount);
        Console.ReadLine();
    }
}

If you use QueryFirstAsync, it returns the first record received from the database, and if you run the above query in SQL, it returns only one record for you, and its value is the number of users in the Users table. You can specify the output type using QueryFirstAsync, which accepts a generic type. For example, in the code above, by specifying the int type in the QueryFirstAsync method, we specify that the return value must be of type int.

Powered by Froala Editor