DotNetIntroduction

SQL database access in the C# using Dapper

Dapper is module for C# that makes it easy to use SQL with C#.

It is a thin layer of C# extension methods for the IDbConnection interface.

Some of the methods it adds are:

• Query - return a list of objects from a SQL query.
• Execute - run a SQL query that returns nothing
• Query - run a command multiple times

How to use Dapper with SQLite

To use it add the using Dapper; statement to your class file.

You will also need to add the to your .csproj file.

<ItemGroup>
    <PackageReference Include="Dapper"
Version="1.50.2" />
  </ItemGroup>

Dapper works with any SQL database supported by C# that implement the IDbConnection interface.

To use SQLite add this to your .csproj file’s <ItemGroup> tag:

<PackageReference Include="Microsoft.Data.Sqlite"
Version="2.0.0" />

You can use Dapper with PostgreSQL by using the Npgsql module

You can learn more about using Dapper here as well.

Using Dapper

Try out Dapper by completing the steps below.

Create a new dotnet project using this command:

dotnet new console -o TryingDapper

The -o means that a new folder called TryingDapper will be created.

Change into the folder using ./TryingDapper.

Reference the Dapper and Microsoft.Data.Sqlite module in your project by adding this to your TryingDapper.csproj file

<ItemGroup>
    <PackageReference Include="Microsoft.Data.Sqlite" Version="2.0.0" />
    <PackageReference Include="Dapper" Version="1.50.2" />
  </ItemGroup>

Create a class:

We will create a Pizza class in a Pizza.cs file.

namespace TryingDapper
{
    public class Pizza
    {
        string Type {
            get;
            set;
        }

        string Size {
            get;
            set
        }

        double Price {
            get;
            set
        }
    }
}

Setup Dapper and add these 2 import statements to your Program.cs class file:

using Microsoft.Data.Sqlite;
using Dapper;

Creating a table

Create a table using this ddl command:

create table if not exists pizza (
	id integer primary key AUTOINCREMENT,
	type text,
	size text,
	price real
);

Create IDBConnection instance using this command:

using (var connection = new SqliteConnection("Data Source=./trying_dapper.db;")) {
    connection.Open();
}

If this command is successfull a trying_dapper.db file will be created in the root of your project directory.

Run the create table script using an Execute statement:

string CREATE_PIZZA_TABLE = @"create table if not exists pizza (
	id integer primary key AUTOINCREMENT,
	type text,
	size text,
	price real
);" ;

connection.Execute(CREATE_PIZZA_TABLE);

Creating data in the table

Insert data in the pizza table:

connection.Execute(@"
	insert into 
		pizza (type, size, price)
	values 
		(@Type, @Size, @Price);",
		new Pizza() {
		Type = "Regina",
		Size = "small",
		Price = 31.75
	});

Add a list of Pizza’s like this:

connection.Execute(@"
	insert into 
		pizza (type, size, price)
	values 
		(@Type, @Size, @Price);",
	new Object[] {
		new Pizza() {
		Type = "Regina",
		Size = "small",
		Price = 31.75
	}, new Pizza {
		Type = "Regina",
		Size = "medium",
		Price = 51.75
	}, new Pizza {
		Type = "Regina",
		Size = "large",
		Price = 89.75
	}
});

Querying the table

Read the data in the table using this code:

var pizzas = connection.Query<Pizza>(@"select * from pizza");
Console.WriteLine(pizzas.Count());

Note: add using System.Linq; to make pizzas.Count() work.

To group data

To group data using a group by SQL query you can use an approach like this.

Create a class like this:

namespace TryingDapper
{
    public class PizzaGrouped
    {
        public string Grouping {
            get;
            set;
        }

        public double Total {
            get;
            set;
        }

        
    }
}

And run a Query like this with Dapper:

var GROUP_BY_SIZE = @"select size as grouping, sum(price) as total from pizza group by size";

var pizzaTotalPerSize = connection.Query<PizzaGrouped>(GROUP_BY_SIZE);

foreach (var pizza in pizzaTotalPerSize)
{
	Console.WriteLine($"size : {pizza.Grouping} - total @ {pizza.Total} ");
}