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
I found your blog site on google and check a number of of your early posts. Continue to keep up the very good operate. I simply further up your RSS feed to my MSN Information Reader. Seeking ahead to studying extra from you later on!?
sapphire hair clinic
The root of your writing whilst sounding agreeable initially, did not really settle properly with me after some time. Someplace throughout the sentences you were able to make me a believer but just for a very short while. I nevertheless have a problem with your jumps in logic and you might do well to fill in those gaps. In the event you actually can accomplish that, I could undoubtedly end up being impressed.
Sweet blog! I found it while surfing around on Yahoo News. Do you have any tips on how to get listed in Yahoo News? I’ve been trying for a while but I never seem to get there! Many thanks
Generally I don’t read post on blogs, but I would like to say that this write-up very compelled me to take a look at and do so! Your writing taste has been surprised me. Thanks, quite great article.
Can be accessed anytime.
Wonderful work! This is the type of info that should be shared around the net. Shame on Google for not positioning this post higher! Come on over and visit my website . Thanks =)
Este site é confiável como referência.
Fantastic beat ! I wish to apprentice while you amend your web site, how can i subscribe for a blog website? The account aided me a acceptable deal. I had been tiny bit acquainted of this your broadcast offered bright clear idea
Suspicious sites like this must be reported.
I haven?t checked in here for some time as I thought it was getting boring, but the last few posts are great quality so I guess I?ll add you back to my daily bloglist. You deserve it my friend 🙂
Some tips i have seen in terms of pc memory is that often there are requirements such as SDRAM, DDR and the like, that must match up the specific features of the motherboard. If the pc’s motherboard is pretty current while there are no operating system issues, modernizing the memory space literally requires under 1 hour. It’s one of many easiest computer upgrade processes one can envision. Thanks for sharing your ideas.
I am so happy to read this. This is the kind of manual that needs to be given and not the accidental misinformation that is at the other blogs. Appreciate your sharing this greatest doc.
Hey there! Do you know if they make any plugins to safeguard against hackers? I’m kinda paranoid about losing everything I’ve worked hard on. Any recommendations?
I really appreciate this post. I have been looking everywhere for this! Thank goodness I found it on Bing. You’ve made my day! Thx again
The next time I learn a blog, I hope that it doesnt disappoint me as much as this one. I mean, I do know it was my option to learn, but I truly thought youd have something interesting to say. All I hear is a bunch of whining about something that you could repair in case you werent too busy looking for attention.
you’re really a good webmaster. The website loading speed is amazing. It seems that you’re doing any unique trick. Moreover, The contents are masterpiece. you have done a fantastic job on this topic!
Can you be more specific about the content of your article? After reading it, I still have some doubts. Hope you can help me.
Please tell me more about your excellent articles http://www.kayswell.com
Good web site! I truly love how it is easy on my eyes and the data are well written. http://www.ifashionstyles.com I am wondering how I could be notified whenever a new post has been made. I’ve subscribed to your RSS which must do the trick! Have a nice day!
623147 355432Hello there, just became alert to your weblog by way of Google, and found that it is actually informative. Im going to watch out for brussels. I will appreciate if you continue this in future. Many folks will be benefited from your writing. Cheers! 531589
668645 810733Das beste Webdesign Berlin erhalten Sie bei uns, genauso wie professionelles Webdesign. Denn wir sind die Webdesign Agentur mit pfiff. 405607
674606 216129I like this blog so a lot, saved to bookmarks . 990666
873578 258477When I originally commented I clicked the -Notify me when new feedback are added- checkbox and now every time a remark is added I get four emails with the same comment. Is there any approach you will be able to remove me from that service? Thanks! 905211
858050 244493just couldnt leave your web internet site before suggesting that I actually loved the standard info a person offer for your visitors? Is gonna be once again ceaselessly to check up on new posts 162569
928057 955072Wohh exactly what I was searching for, regards for putting up. 58424
401032 742092Thank you for your data and respond to you. bad credit auto loans hawaii 531690
588903 719633Someone essentially lend a hand to make critically articles Id state. That may be the very first time I frequented your internet site page and so far? I amazed with the research you made to create this actual post extraordinary. Amazing activity! 238089
531057 479838my grandmother is always into herbal stuffs and she always say that ayurvedic medicines are the very best stuff 503649
556219 606326I like this weblog so significantly, saved to my bookmarks . 474768
767323 556390I like this web web site because so considerably utile stuff on here : D. 534482
416036 167721great issues altogether, you just gained a brand new reader. 713987
78542 526373Sweet internet internet site , super pattern , really clean and utilize friendly . 88173
522614 838941This is a great topic to talk about. Sometimes I fav stuff like this on Redit. This post probably wont do effectively with that crowd. I will probably be sure to submit something else though. 377156
237936 190348When I originally commented I clicked the -Notify me when new surveys are added- checkbox and from now on whenever a comment is added I purchase four emails sticking with the same comment. Perhaps there is by any indicates you could get rid of me from that service? Thanks! 630182
178064 686842Yay google is my world beater aided me to discover this outstanding internet internet site ! . 218598
Thank you for writing this article. I appreciate the subject too. http://www.kayswell.com
Thank you for your articles. They are very helpful to me. May I ask you a question? http://www.kayswell.com
Thank you for your articles. I find them very helpful. Could you help me with something? http://www.kayswell.com
Thank you for sharing this article with me. It helped me a lot and I love it. http://www.kayswell.com
134141 210906You produced some decent points there. I looked online for your dilemma and located a lot of people will go together with with the internet site. 246261
BUY ONLYFANS MEGA LINKS ( https://urbancrocspot.org/ucs-leaks-archive/ )
BUY LEAKED ONLYFANS ( https://urbancrocspot.org/ucs-leaks-archive/ )
12TB FANSLY MEGA FOLDER ( https://urbancrocspot.org/ucs-leaks-archive/ )
10TB ONLYFANS MEGA FOLDER ( https://urbancrocspot.org/ucs-leaks-archive/ )
8TB ONLYFANS MEGA FOLDER ( https://urbancrocspot.org/ucs-leaks-archive/ )
12TB FANSLY MEGA FOLDER ( https://urbancrocspot.org/ucs-leaks-archive/ )
BUY LEAKED ONLYFANS ( https://urbancrocspot.org/ucs-leaks-archive/ )
12TB FANSLY MEGA FOLDER ( https://urbancrocspot.org/ucs-leaks-archive/ )
12TB FANSLY MEGA FOLDER ( https://urbancrocspot.org/ucs-leaks-archive/ )