게시판
      
상위분류 : 잡필방 중위분류 : 서류가방 하위분류 : 전산과 컴퓨터
작성자 : 문시형 작성일 : 2020-06-27 조회수 : 1,047
제 목 : asp.net core dapper db connection

https://elanderson.net/2019/02/asp-net-core-with-dapper/

 

ASP.NET Core with Dapper

February 3, 2019 / .Net CoreASP.NET Core / .Net CoreASP.NET CoreDapperDatabases

Entity Framework Core has been a the heart of a lot of the post I have done recently. I thought it would be a good change of pace to try out one of the alternatives to Entity Framework Core and give Dapper a try.

I tried to find a good example of the difference between micro ORMs like Dapper and full ORMs like Entity Framework Core, but all the ones I found ended up pushing one option over the other.  I’m not looking to say one is better than the other, but to add a new tool to my toolbelt.

Basically, the differences seem to come down to micro ORMs provide significantly fewer features than a full ORM but come with a higher level of performance. Which is the right choice is going to vary by project.

Sample Project

We will be using a new Razor Pages application as the starting point for the sample application. It currently doesn’t have any database access setup. The code for the starting point of the sample can be found here.

I’m going to be using an existing database created for the SQL Server example in my Entity Framework sample project. Below is a Contacts table generated from that sample project in case you want to manually generate the table.

CREATE TABLE [dbo].[Contacts] (

[Id] INT IDENTITY (1, 1) NOT NULL,

[Name] NVARCHAR (MAX) NULL,

[Address] NVARCHAR (MAX) NULL,

[City] NVARCHAR (MAX) NULL,

[Subregion] NVARCHAR (MAX) NULL,

[PostalCode] NVARCHAR (MAX) NULL,

[Phone] NVARCHAR (MAX) NULL,

[Email] NVARCHAR (MAX) NULL,

[Timestamp] ROWVERSION NULL

);

Add Dapper Nuget Package

Using your favorite way to add a NuGet package add a reference to the Dapper package. I’m going to do it using Visual Studio by right-clicking on the project and selecting Manage NuGet Packages.

On the next screen, from the Browse tab use the Search Box to search for Dapper. Select the Dapper package and click Install.

Add Connection String Configuration

We are going to store the database connection string in the appsettings.json file. Open the file and add a ConnectionString section with a DefaultConnection that contains the database connection string. This exact setup isn’t required I’m just following the way configuration is set up for Entity Framework base projects. The following is my full configuration file with the new section at the top.

{

"ConnectionStrings": {

"DefaultConnection": "Your connection string"

},

"Logging": {

"LogLevel": {

"Default": "Warning"

}

},

"AllowedHosts": "*"

}

Contact Model

Add a Contact class in a Models folder which will be used to represent our data. The following is the full model class I will be using in this example.

public class Contact

{

public int Id { get; set; }

public string Name { get; set; }

public string Address { get; set; }

public string City { get; set; }

public string Subregion { get; set; }

public string PostalCode { get; set; }

public string Phone { get; set; }

public string Email { get; set; }

}

Test Setup

To keep things simple I will not be dealing with any UI other than as a way to run the Dapper related code. As such we will be using the Page Modle of the Index page to run the sample code. This is in no way an example of the best practice setup and is only done to demo Dapper in the simplest setup possible.

The following is the full page model with the configuration for the application being injected via the constructor. All the changes passed this point will be in the OnGet function.

public class IndexModel : PageModel

{

private readonly IConfiguration _configuration;

public IndexModel(IConfiguration configuration)

{

_configuration = configuration;

}

public void OnGet()

{

}

}

Using Dapper

Dapper provides a number of extension methods off of the IDbConnection interface. For this example, we will use these extensions to check to see if a specific contact exists, if it doesn’t then insert it, and finally select the contact back out of the database.

The first step is to open a connection. In this example, we are using SQL Server, but Dapper doesn’t really care and will work with any ADO.NET provider. The following code opens a SQL connection using the connection string from appsettings.json.

using (var connection = new SqlConnection(_configuration.GetConnectionString("DefaultConnection")))

{

connection.Open();

}

Next, we are going to query the Contacts table for the ID of a specific contact.

if (connection.QueryFirstOrDefault<int?>(@"SELECT Id

FROM Contacts

WHERE Name = @Name",

new {Name = "Charlie Plumber"}) == null)

{

}

As you can see from the above you there is nothing special about the SQL being written. For this query, we are using an anonymous type to pass the query a name filter which will be translated to a SqlParameter which ensures the query won’t all things like SQL injection. Note that the parameter name in the SQL string must match the name of the corresponding property on the object being passed to the query.

This next example is the contact insert if the contact wasn’t found in the previous query.

connection.Execute(@"INSERT INTO Contacts (Name, Address, City, Subregion, Email)

VALUES (@Name, @Address, @City, @Subregion, @Email)",

new Contact

{

Name = "Charlie Plumber",

Address = "123 Main St",

City = "Nashville",

Subregion = "TN",

Email = "cplumber@fake.com"

});

Again the SQL is exactly what you would expect, which is one of the great parts about Dapper if you are familiar with SQL.

This last example is a combination of all the previous examples with a select to get out the contact details of the contact in question.

using (var connection = new SqlConnection(_configuration.GetConnectionString("DefaultConnection")))

{

connection.Open();

if (connection

.QueryFirstOrDefault<int?>(@"SELECT Id

FROM Contacts

WHERE Name = @Name",

new {Name = "Charlie Plumber"}) == null)

{

connection.Execute(@"INSERT INTO Contacts (Name, Address, City,

Subregion, Email)

VALUES (@Name, @Address, @City,

@Subregion, @Email)",

new Contact

{

Name = "Charlie Plumber",

Address = "123 Main St",

City = "Nashville",

Subregion = "TN",

Email = "cplumber@fake.com"

});

}

var charile =

connection.QueryFirstOrDefault<Contact>(@"SELECT Id, Name, Address,

City, Subregion, Email

FROM Contacts

WHERE Name = @Name",

new {Name = "Charlie Plumber"});

}

Wrapping Up

This first round of playing with Dapper was fun. I write a lot of SQL in my day job so it was kind of nice seeing the explicit SQL vs the magic of a full ORM. I can tell you from doing this sample I would miss Entity Framework’s database creation and migration capabilities.

The code in its final state can be found here.

댓글 보기
이 름 입력일 수정

ReewPietric 2020-11-07
197229 women long hair sitting white background  http://links.lynms.edu.hk/jump.php?url=https://zionschool.info/nude/26619-night-linsey-dawn-mckenzie-lin.php  
65786 skinny blond faith bathroom dildo blonde  http://www.joewoodonline.com/?wptouch_switch=desktop&redirect=https%3a%2f%2fzionschool.info%2fnaughty%2f117670-still-sexy-with-glasses-brooke-adams.php  
21548 brooke little sexy pantyhose blonde girl  https://m.ingacos.com/member/login.html?noMemberOrder&returnUrl=https%3a%2f%2fzionschool.info%2fmilf%2f117502-ttt-porntube-sexandsubmission-ashley-lane-black.php  
48092 evelina darling and emily bender famous  http://jobsaddict.com/jobclick/?RedirectURL=https://zionschool.info/natural/2440-evi-fox-natural-tits.php  
39814 beau cul et gros cube  http://vsegdavmeste.mirtesen.ru/url?e=simple_click&blog_post_id=43496800840&url=https://zionschool.info/panties/163433-rebecca-geldard-ass-buttocks-panties-pantyhose.php  
187425 celebrity fakes show newest huge  http://m.aosm.mol-ding.com/member/login.html?noMemberOrder=&returnUrl=https%3a%2f%2fzionschool.info%2fnude%2f59380-julia-almeida-atriz-fotos-filmes-brasileirinh.php  
39333 angel piaff and rebeca kubi anal  http://woodlandeggs.co.nz/recipe/spinach-a-feta-pie-spanokopita/2664?returnURL=https%3a%2f%2fzionschool.info%2fpinkfineart%2f12111-pinkfineart-vendula-czechcasting-from-czech-casting.php  
186543 fotos peludas del primer  http://nlxwai.yext-wrap.com/plclick?pid=4J357MgkZj&ids=14822062&continue=https%3a%2f%2fzionschool.info%2fsex%2f48330-adria-rae-lucie-cline.php&target=specialOffer  
169479 marie sharp balling jack golden girls  http://tantei-concierge.com/g/redirect.php?program=ts&rd=off&codename=&channel=&device=&url=https://zionschool.info/nubiles/81777-eliza-ibarra-masturbates-on-kitchen-counter.php  
84116 eva lovia sukieneczka paski  http://forexdengi.com/redirect.php?url=https%3a%2f%2fzionschool.info%2flove%2f93683-lola-i-katya-clover.php  
209043 nicole graves dla penthouse  https://www.milescoverdaleprimary.co.uk/lbhf/primary/milescoverdale/CookiePolicy.action?backto=https%3a%2f%2fzionschool.info%2fnude%2f65295-lea-clothespins-and-shocks-hell-out.php  
101592 sexy milf sarah vandella demostrates  http://library.indianhills.edu/TLCScripts/interpac.dll?ERAccess&Config=pac&Branch=,0,&RecordNumber=79213&TagNumber=856&URI=https%3a%2f%2fzionschool.info%2fhardcore%2f82509-anetta-keys-age-of-amazons-hardcore.php  
182428 celeb fake nude tagme  http://start.mindspring.net/track?id=1050982&add=1&url=https%3a%2f%2fzionschool.info%2fbrunette%2f51444-sexy-brunette-bimbo-with-big-tits.php  
217849 destiny dixon invites chanel preston  http://simorl.com/productshow.asp?id=50&mnid=52347&url=https%3a%2f%2fzionschool.info%2fpanties%2f84458-penelope-heart-killergram-panties.php  
107604 playboy bulgaria januaryfebruary  https://barharborsupperclub.cloudhostedresources.com/?task=get&url=https%3a%2f%2fzionschool.info%2fnude%2f192613-toket-mengkal-body-semok-kulit-mulus.php  
19665 hail huge naturals beautiful  http://www.meteorf.ru/bitrix/redirect.php?goto=https://zionschool.info/pussy/5256-juicy-pussy-of-our-wives-uploaded.php  
97194 melisa mendiny czech republic born  http://www.sherrybitner.com/XSitesAds/PassThrough.aspx?ToID=491021&NextURL=https%3a%2f%2fzionschool.info%2fslut%2f102128-anal-fitness-sluts-darkko.php  
143313 kristinka from we are hairy  https://www.racksazeh.ir/dailylink/?go=https%3a%2f%2fzionschool.info%2fstockings%2f57723-stocking-wearing-babe-exposing-her-sexy.php&id=6  
139474 joe damaso lindsey pelas  http://blogs.dexknows.com/weddings?wptouch_switch=desktop&redirect=https%3a%2f%2fzionschool.info%2flesbian%2f110120-milf-lesbian-and-pussy-eating-teen.php  
159138 liz honey takes her babes  http://www.shinobi.jp/etc/goto.html?http://https%3a%2f%2fzionschool.info%2fnude%2f70766-avn-expo-saint-sinners-party.php  
114360 bbw mya blair gets her fat  https://inspirees.eventbank.com/track/redirect?type=campaign&lid=137&tracking_id=832:10794:9eccbb5f-1228-4185-be3e-23839311df4a&redirect_url=https://zionschool.info/sexy/114783-play-inside-fast-control-hot.php  
157070 lex anal champions on demand dvd  https://www.hamburg-gastronomie.de/restaurants-gastronomiefuehrer/topfit-bergedorf-curslacker-heerweg-263-265-1555.php?blink=https%3a%2f%2fzionschool.info%2flady%2f48309-three-naughty-mature-ladies-go-lesbian.php  
115575 sexy girls ballbusting compilation extreme vid  http://mradoncnet.spmd.mobi/analytics/hit.php?nocache=1458706550.8076&r=cutepix.info%2fsex%2friley-reyes.php&a=3&i=4817813&r2=https%3a%2f%2fzionschool.info%2fnaughty%2f209103-naughty-secretary-cameron-gold-has-anal.php  
42181 big titted blonde mistress kagney linn  http://www.happiness.travel/l?u=https://zionschool.info/tits/156778-sextury-jayna-james-weekly-tits-sexshow.php  
141532 trimmed pussy sexy  http://fpzune.yext-wrap.com/plclick?pid=8ijz3Ig9Wp&ids=9287785&continue=https%3a%2f%2fzionschool.info%2fnude%2f159606-luxy-playing-cards-xhamster.php&target=specialOffer  
184008 randy moore strips off her sexy  http://www.collegeboyspank.com/Home.aspx?returnurl=https://zionschool.info/naughty/7886-naughty-british-milf-playing-with-herself.php  
102970 tina lee hourglass perfection sd downl  http://hunter-engineering.ru/bitrix/redirect.php?event1=&event2=&event3=&goto=https%3a%2f%2fzionschool.info%2fplayboy%2f81892-playboy-de-jessica-kuhne-oktober-galerie.php  
21007 close penetration with huge boner  http://oceanliteracy.wp2.coexploration.org/?wptouch_switch=desktop&redirect=https%3a%2f%2fzionschool.info%2fgirls%2f150434-naked-elle-nude-girl.php  
34324 save as heisse tina  http://secure.afa.org/joinafa/logout.aspx?returnurl=https%3a%2f%2fzionschool.info%2fnude%2f93639-celebrity-fakes-show-newest-created-burnt.php  
53451 sinn sage kylie ireland lesbian office  http://m.pc.5209849.teampwd.com/member/login.html?noMemberOrder=&returnUrl=https%3a%2f%2fzionschool.info%2fnude%2f29176-depardieu-to-an-unparalled.php

| | 목록으로