게시판
      
상위분류 : 잡필방 중위분류 : 서류가방 하위분류 : 전산과 컴퓨터
작성자 : 문시형 작성일 : 2020-06-27 조회수 : 26,188
제 목 : 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.

댓글 보기
이 름 입력일 수정

Edwardfum 2024-02-11
Did you know about the new AI capabilities?
Use them for your business before your competitors do.

https://www.youtube.com/watch?v=cz2A9q9PVuU

Edwardfum 2024-02-11
Did you know about the new AI capabilities?
Use them for your business before your competitors do.

https://www.youtube.com/watch?v=cz2A9q9PVuU

Edwardfum 2024-02-11
Did you know about the new AI capabilities?
Use them for your business before your competitors do.

https://www.youtube.com/watch?v=cz2A9q9PVuU

Edwardfum 2024-02-11
Did you know about the new AI capabilities?
Use them for your business before your competitors do.

https://www.youtube.com/watch?v=cz2A9q9PVuU

Edwardfum 2024-02-11
Did you know about the new AI capabilities?
Use them for your business before your competitors do.

https://www.youtube.com/watch?v=cz2A9q9PVuU

Anthonyhep 2024-02-09
Dear Sir,

Discover the future of earning through our groundbreaking AI-powered Passive Income System! This innovative solution offers you an effortless way to generate passive income by leveraging the power of Artificial Intelligence to identify and capitalize on lucrative opportunities in real-time. Whether you're new to passive income or looking to diversify your portfolio, our system is designed to maximize returns with minimal effort on your part. Experience the simplicity of setting up, the thrill of watching your earnings grow, and the peace of mind that comes with secure, AI-driven investments. Don't miss out on this chance to transform your financial future. Contact us now to learn more and embark on your journey to financial freedom with AI!

learn More: https://hornoselectricos.online/PayPerCallAI/

Best regards,

Bill Cooper.

Anthonyhep 2024-02-09
Dear Sir,

Discover the future of earning through our groundbreaking AI-powered Passive Income System! This innovative solution offers you an effortless way to generate passive income by leveraging the power of Artificial Intelligence to identify and capitalize on lucrative opportunities in real-time. Whether you're new to passive income or looking to diversify your portfolio, our system is designed to maximize returns with minimal effort on your part. Experience the simplicity of setting up, the thrill of watching your earnings grow, and the peace of mind that comes with secure, AI-driven investments. Don't miss out on this chance to transform your financial future. Contact us now to learn more and embark on your journey to financial freedom with AI!

learn More: https://hornoselectricos.online/PayPerCallAI/

Best regards,

Bill Cooper.

Anthonyhep 2024-02-09
Dear Sir,

Discover the future of earning through our groundbreaking AI-powered Passive Income System! This innovative solution offers you an effortless way to generate passive income by leveraging the power of Artificial Intelligence to identify and capitalize on lucrative opportunities in real-time. Whether you're new to passive income or looking to diversify your portfolio, our system is designed to maximize returns with minimal effort on your part. Experience the simplicity of setting up, the thrill of watching your earnings grow, and the peace of mind that comes with secure, AI-driven investments. Don't miss out on this chance to transform your financial future. Contact us now to learn more and embark on your journey to financial freedom with AI!

learn More: https://hornoselectricos.online/PayPerCallAI/

Best regards,

Bill Cooper.

Anthonyhep 2024-02-09
Dear Sir,

Discover the future of earning through our groundbreaking AI-powered Passive Income System! This innovative solution offers you an effortless way to generate passive income by leveraging the power of Artificial Intelligence to identify and capitalize on lucrative opportunities in real-time. Whether you're new to passive income or looking to diversify your portfolio, our system is designed to maximize returns with minimal effort on your part. Experience the simplicity of setting up, the thrill of watching your earnings grow, and the peace of mind that comes with secure, AI-driven investments. Don't miss out on this chance to transform your financial future. Contact us now to learn more and embark on your journey to financial freedom with AI!

learn More: https://hornoselectricos.online/PayPerCallAI/

Best regards,

Bill Cooper.

Anthonyhep 2024-02-09
Dear Sir,

Discover the future of earning through our groundbreaking AI-powered Passive Income System! This innovative solution offers you an effortless way to generate passive income by leveraging the power of Artificial Intelligence to identify and capitalize on lucrative opportunities in real-time. Whether you're new to passive income or looking to diversify your portfolio, our system is designed to maximize returns with minimal effort on your part. Experience the simplicity of setting up, the thrill of watching your earnings grow, and the peace of mind that comes with secure, AI-driven investments. Don't miss out on this chance to transform your financial future. Contact us now to learn more and embark on your journey to financial freedom with AI!

learn More: https://hornoselectricos.online/PayPerCallAI/

Best regards,

Bill Cooper.

MarkStrong 2024-02-07
Hi,

NEW A.I App Exploits Elon Musk & ClickBank
Every 60 Seconds For $426/Paydays &
Unlimited FREE Automated Traffic!

If you are interested, write to me or follow the link: http://hornoselectricos.online/tesler/

Best Regards, Mark

| | 목록으로