In part 2 of this series we created a simple database. In this part we’ll look at how to perform CRUD operations against that DataBase in anticipation of creating APIs for these operations.
Dapper is a micro-ORM (Object Relational Mapper) for .NET that simplifies data access against a database. In this blog post, we’ll focus on a code example that employs Dapper to manage a Car entity. This example uses a custom DatabaseConnectionFactory to handle database connections. Let’s dive right in.
Database configuration
You will need a working MS Sql database running to test the code. In the sample code, you can set your connections details in the appsettings.json file.
Create the database and car table
Create your database and then you can use the following script to initialize the database
USE [Cars]
GO
/****** Object: Table [dbo].[car] ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE TABLE [dbo].[car](
[id] [int] IDENTITY(1,1) NOT NULL,
[make] [varchar](50) NULL,
[model] [varchar](50) NULL,
[model_year] [int] NULL,
[price] [decimal](18, 0) NULL,
[deleted] [int] NULL,
CONSTRAINT [add_identity] PRIMARY KEY CLUSTERED
(
[id] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON, OPTIMIZE_FOR_SEQUENTIAL_KEY = OFF) ON [PRIMARY]
) ON [PRIMARY]
GORequired NuGet Packages
We are using the following NuGet packages:
- Dapper - Dapper.SqlBuilder
You can install these packages using the NuGet Package Manager Console:
Install-Package Dapper Install-Package Dapper.SqlBuilder
Setting Up the Database Connection Factory
Before we get into the CarRepository class, let’s discuss the DatabaseConnectionFactory class. This class is responsible for creating and opening database connections. It leverages DbSettings to get the connection string:
DatabaseConnectionFactory takes an IOptions<DbSettings> dependency, which allows it to read the database settings from a configuration source (like appsettings.json). The GetConnection method uses this information to create and open a SQL Server connection, which it then returns.
The Car Repository
Initialization and Dependency Injection
The CarRepository class takes a DatabaseConnectionFactory as a constructor argument, indicating dependency injection:
Methods in Car Repository
GetAll Method
This method returns all the car records. Optionally, it can also return records marked as deleted.
The GetAll method uses Dapper.SqlBuilder to construct the SQL query dynamically. This allows for more flexibility, as you can conditionally add WHERE clauses, joins, or other SQL constructs.
Get Method
This method retrieves a single car record based on its ID:
UpsertAsync Method
The UpsertAsync method handles both insert and update operations:
DeleteAsync Method
The DeleteAsync method marks the record as deleted:
Resources
Dapper GitHub Repository Dapper SqlBuilder
Conclusion
Dapper offers a powerful, yet simple way to perform CRUD operations in .NET applications. The code example in this post demonstrates how to make the most of Dapper in a practical scenario involving a Car entity. The DatabaseConnectionFactory class helps to manage database connections efficiently, ensuring that the rest of the code can focus on business logic rather than connection management. Whether you need to build SQL queries dynamically or perform simple operations, Dapper has tools to help you get the job done efficiently.
Rodrigo Juarez is a full-stack developer who has specialized in Xamarin in recent years and is now focusing on MAUI.
He is also a book author. With over 25 years of experience, Rodrigo has contributed to a diverse array of projects, developing applications for web, desktop, and mobile platforms. Specialized in Microsoft technologies, he has expertise across various sectors, including management, services, insurance, pharmacy, and banking. Rodrigo Juarez can be reached at info@rodrigojuarez.com
yürük
murad çeltik
crabs
buk
güncel öztürk
Hey! Do you know if they make any plugins to assist with Search Engine Optimization? I’m trying to get my blog to rank for some targeted keywords but I’m not seeing very good success. If you know of any please share. Appreciate it!
kandulu
deniz küçükkaya
Thank you for your sharing. I am worried that I lack creative ideas. It is your article that makes me full of hope. Thank you. But, I have a question, can you help me?
Thanks for the suggestions about credit repair on all of this blog. A few things i would tell people is to give up the actual mentality that they’ll buy at this point and fork out later. Being a society we all tend to do that for many factors. This includes holidays, furniture, and items we would like. However, you should separate your own wants out of the needs. When you are working to boost your credit score make some trade-offs. For example you are able to shop online to save money or you can turn to second hand outlets instead of expensive department stores for clothing.
dr basat
doktorkbb
nişanaşı
güncel öztürk
taner
Youre so cool! I dont suppose Ive read something like this before. So good to seek out somebody with some authentic thoughts on this subject. realy thank you for starting this up. this website is one thing that is wanted on the net, somebody with a little originality. useful job for bringing something new to the web!
Thanks for your publication. I also believe laptop computers have become more and more popular these days, and now tend to be the only type of computer included in a household. This is due to the fact that at the same time potentially they are becoming more and more reasonably priced, their processing power keeps growing to the point where they may be as strong as desktop from just a few years back.
http://www.factorytinsigns.com is 100 Trusted Global Metal Vintage Tin Signs Online Shop. We have been selling art and décor online worldwide since 2008. Started in Sydney, Australia. 2000+ Tin Beer Signs, Outdoor Metal Wall Art, Business Tin Signs, Vintage Metal Signs to choose from. 100 Premium Quality Artwork. Up-to 40 OFF Sale Store-wide. Fast Shipping USA, Canada, UK, Australia, New Zealand, Europe.
My spouse and I absolutely love your blog and find almost all of your post’s to be exactly I’m looking for. Would you offer guest writers to write content to suit your needs? I wouldn’t mind creating a post or elaborating on some of the subjects you write regarding here. Again, awesome website!
Its such as you learn my mind! You seem to know so much about this, such as you wrote the ebook in it or something. I think that you can do with a few to drive the message home a little bit, but instead of that, this is wonderful blog. A fantastic read. I’ll definitely be back.
I will right away snatch your rss feed as I can’t find your email subscription link or newsletter service. Do you’ve any? Please let me understand in order that I may subscribe. Thanks.
Hey very nice site!! Man .. Excellent .. Amazing .. I will bookmark your blog and take the feeds also?I am happy to find numerous useful information here in the post, we need develop more techniques in this regard, thanks for sharing. . . . . .
For transactional mail, our guaranteed inbox rate minimizes customer support load.
Simply wish to say your article is as surprising. The clearness in your post is simply great and i can assume you are an expert on this subject. Fine with your permission let me to grab your RSS feed to keep up to date with forthcoming post. Thanks a million and please carry on the gratifying work.
Great web site. A lot of useful information here. I am sending it to some friends ans also sharing in delicious. And certainly, thanks for your effort!
Hello There. I found your blog using msn. This is an extremely well written article. I will make sure to bookmark it and return to read more of your useful information. Thanks for the post. I?ll definitely return.
güncel
เล่นสล็อตฟรี ผู้เล่นมือใหม่หรือแม้แต่เซียนที่อยากทดลองเกมใหม่ สามารถใช้ระบบทดลองเล่นได้ฟรีโดยไม่ต้องสมัครสมาชิก คุณจะได้เรียนรู้ระบบเกม สัญลักษณ์ ฟีเจอร์ต่าง ๆ รวมถึงทดสอบความถี่ของโบนัสว่าคุ้มค่า ทดลองเล่นสล็อต pg ฟรี 2025
of course like your website however you need to check the spelling on quite a few of your posts. Many of them are rife with spelling issues and I find it very troublesome to inform the reality then again I will surely come back again.
I’m really inspired together with your writing talents as well as with the structure to your blog. Is that this a paid subject matter or did you customize it your self? Anyway stay up the nice quality writing, it?s uncommon to look a great weblog like this one today..
ahd clinic
I like the valuable info you provide in your articles. I?ll bookmark your blog and check again here frequently. I am quite certain I will learn many new stuff right here! Best of luck for the next!
clinicplast
medart
mehmet emre dinç
maral
dokkr bbb
nişantaşı hastanesi
sapphire hair clinic
salih emre
istanbul hair
noyan
nişantaşı hastanesi
deniz küçükkaya
hair neva
smile hair clinic
doku clinic
certainly like your web-site however you have to test the spelling on several of your posts. Several of them are rife with spelling issues and I to find it very troublesome to inform the truth however I will surely come again again.
maral
hairneva