Dapper Getting Started With Your First SQL Database
To perform CRUD operations in the database, we must establish a connection with the database using a database connection.
Create a Database
The first step is to create a database using the following script.
IF NOT EXISTS(SELECT * FROM sys.databases WHERE name = 'mydb')
BEGIN
CREATE DATABASE mydb
END
This script will add a database named mydb if it doesn't exist.
Create Table
Once the database is created, you can now create a table in the database.
CREATE TABLE [dbo].[Customers] ( [CustomerID] INT IDENTITY (1, 1) NOT NULL, [FirstName] NVARCHAR (MAX) NULL, [LastName] NVARCHAR (MAX) NULL, [Email] NVARCHAR (MAX) NULL, CONSTRAINT [PK_dbo.Customers] PRIMARY KEY CLUSTERED ([CustomerID] ASC) );
It will add a Customers table with the specified columns to the database.
You can also use the Dapper Plus - CreateTable extensions for quick tests.
Create Model Class
Create an entity or model class of Customer
type, this entity will be mapped to the Customers table in the database.
public class Customer { public int CustomerID { get; set; } public string FirstName { get; set; } public string LastName { get; set; } public string Email { get; set; } }
You can also use Dapper Entity to Table Converter to automatically convert your table to entity.
Database Operations
Dapper is an ORM library, which extends methods of the IDbConnection
interface. These extension methods have efficient code to perform insert, update delete, and select methods.
string sql = "INSERT INTO Customers (FirstName, LastName, Email) Values ('Carson', 'Alexander', 'carson.alexander@example.com');"; using (var connection = new SqlConnection(ConfigurationManager.ConnectionStrings["Connection"].ConnectionString)) { var affectedRows = connection.Execute(sql); }
In this example, the Dapper Execute extension method has been used to insert a new customer.
Related Articles
ZZZ Projects