{"id":1870,"date":"2023-06-15T08:17:42","date_gmt":"2023-06-15T08:17:42","guid":{"rendered":"http:\/\/waqar-arshad.com\/?p=1870"},"modified":"2023-06-15T08:17:42","modified_gmt":"2023-06-15T08:17:42","slug":"dapper-orm","status":"publish","type":"post","link":"http:\/\/waqar-arshad.com\/index.php\/2023\/06\/15\/dapper-orm\/","title":{"rendered":"Dapper &#8211; ORM"},"content":{"rendered":"<div class=\"pld-like-dislike-wrap pld-template-1\">\r\n    <div class=\"pld-like-wrap  pld-common-wrap\">\r\n    <a href=\"javascript:void(0)\" class=\"pld-like-trigger pld-like-dislike-trigger  \" title=\"\" data-post-id=\"1870\" data-trigger-type=\"like\" data-restriction=\"cookie\" data-already-liked=\"0\">\r\n                        <i class=\"fas fa-thumbs-up\"><\/i>\r\n                <\/a>\r\n    <span class=\"pld-like-count-wrap pld-count-wrap\">    <\/span>\r\n<\/div><div class=\"pld-dislike-wrap  pld-common-wrap\">\r\n    <a href=\"javascript:void(0)\" class=\"pld-dislike-trigger pld-like-dislike-trigger  \" title=\"\" data-post-id=\"1870\" data-trigger-type=\"dislike\" data-restriction=\"cookie\" data-already-liked=\"0\">\r\n                        <i class=\"fas fa-thumbs-down\"><\/i>\r\n                <\/a>\r\n    <span class=\"pld-dislike-count-wrap pld-count-wrap\"><\/span>\r\n<\/div><\/div>\n<p>There are many ORM tools available. One of them is Dapper. Dapper is known as the king of ORM.<\/p>\n\n\n\n<p>The following are the key features of Dapper:<\/p>\n\n\n\n<ul class=\"wp-block-list\">\n<li>Speed and fast in performance.<\/li>\n\n\n\n<li>Fewer lines of code.<\/li>\n\n\n\n<li>Object Mapper.<\/li>\n\n\n\n<li>Static Object Binding.<\/li>\n\n\n\n<li>Dynamic Object Binding.<\/li>\n\n\n\n<li>Easy Handling of SQL Query.<\/li>\n\n\n\n<li>Easy Handling of Stored Procedure.<\/li>\n\n\n\n<li>Operating directly to the IDBConnection class that provides smoothness and running queries directly to the database instead of passing data using various objects as we do in EF and ADO.NET.<\/li>\n\n\n\n<li>Multiple Query Support.<\/li>\n\n\n\n<li>Support for Stored Procedure.<\/li>\n\n\n\n<li>Bulk Data insert functionality.<\/li>\n\n\n\n<li>Dapper also allows fetching multiple data based on multiple inputs.<\/li>\n<\/ul>\n\n\n\n<p>Why Dapper<\/p>\n\n\n\n<ul class=\"wp-block-list\">\n<li>Dapper is the second-fastest ORM.<\/li>\n<\/ul>\n\n\n\n<p>Image reference:\u00a0<a href=\"https:\/\/github.com\/StackExchange\/dapper-dot-net\">Dapper dotnet<\/a>.<\/p>\n\n\n\n<figure class=\"wp-block-image size-full\"><img loading=\"lazy\" decoding=\"async\" width=\"937\" height=\"582\" src=\"http:\/\/waqar-arshad.com\/wp-content\/uploads\/2023\/06\/image-39.png\" alt=\"\" class=\"wp-image-1871\" srcset=\"http:\/\/waqar-arshad.com\/wp-content\/uploads\/2023\/06\/image-39.png 937w, http:\/\/waqar-arshad.com\/wp-content\/uploads\/2023\/06\/image-39-300x186.png 300w, http:\/\/waqar-arshad.com\/wp-content\/uploads\/2023\/06\/image-39-768x477.png 768w\" sizes=\"auto, (max-width: 937px) 100vw, 937px\" \/><\/figure>\n\n\n\n<ul class=\"wp-block-list\">\n<li>Perform CRUD operations directly using the IDBConnection object.<\/li>\n\n\n\n<li>Provide querying static and dynamic data over the database.<\/li>\n\n\n\n<li>Get generic results for simple or complex data types.<\/li>\n\n\n\n<li>Dapper allows storing bulk data at once.<\/li>\n<\/ul>\n\n\n\n<p>How to Install Dapper<\/p>\n\n\n\n<p>There are two ways to install Dapper:<\/p>\n\n\n\n<ol class=\"wp-block-list\" type=\"1\">\n<li><a href=\"https:\/\/github.com\/StackExchange\/dapper-dot-net\/blob\/master\/Dapper%20NET45\/SqlMapperAsync.cs\">DapperMappingFileForC#4.5.cs<\/a>.<\/li>\n<\/ol>\n\n\n\n<p>Add this SqlMapperAsync.cs file to your project and get started with Dapper functionality.<\/p>\n\n\n\n<p>You can SqlMapperAsync.cs depending on the .Net framework you use.&nbsp;<\/p>\n\n\n\n<ul class=\"wp-block-list\">\n<li><strong>NuGet Package Manager.<\/strong><\/li>\n<\/ul>\n\n\n\n<p>In Visual Studio, create a new console project, and in Solution Explorer&nbsp;right-click References and select Manage NuGet Package Manager and search for Dapper and using the NuGet Package Manager Console command for the Nugget Package Manager \u201cinstall-package dapper\u201d, and this will install Dapper into your project.<\/p>\n\n\n\n<p>How Dapper Works<\/p>\n\n\n\n<p>\u201c<em>Dapper Majorly Include Three Steps<\/em>\u201d<\/p>\n\n\n\n<p><strong>Step 1<\/strong><\/p>\n\n\n\n<p>Create an IDBConnection object with Connection String.<\/p>\n\n\n\n<p><strong>Step 2<\/strong><\/p>\n\n\n\n<p>Write&nbsp;a query and store it in a normal string variable.<\/p>\n\n\n\n<p><strong>Step 3<\/strong><\/p>\n\n\n\n<p>Call db.execute() and pass the query and it&#8217;s done.<\/p>\n\n\n\n<p>There are many other ways as well that we will explore in the following example.<\/p>\n\n\n\n<p>For this example, I have one database named &#8220;ContactDB&#8221; and include one table called Contacts. Using Dapper let&#8217;s perform CRUD operations on this contacts table.<\/p>\n\n\n\n<figure class=\"wp-block-image size-full\"><img loading=\"lazy\" decoding=\"async\" width=\"814\" height=\"734\" src=\"http:\/\/waqar-arshad.com\/wp-content\/uploads\/2023\/06\/image-40.png\" alt=\"\" class=\"wp-image-1872\" srcset=\"http:\/\/waqar-arshad.com\/wp-content\/uploads\/2023\/06\/image-40.png 814w, http:\/\/waqar-arshad.com\/wp-content\/uploads\/2023\/06\/image-40-300x271.png 300w, http:\/\/waqar-arshad.com\/wp-content\/uploads\/2023\/06\/image-40-768x693.png 768w\" sizes=\"auto, (max-width: 814px) 100vw, 814px\" \/><\/figure>\n\n\n\n<p>Create a Console application and install Dapper and include the namespace for Dapper.<\/p>\n\n\n\n<p><strong>Example 1<\/strong><\/p>\n\n\n\n<p>Let&#8217;s first start by getting all the data and printing it onto the console.<\/p>\n\n\n\n<p>So:<\/p>\n\n\n\n<p><strong>Step 1<\/strong><\/p>\n\n\n\n<p>Create an object of an IDbConnection class and a new instance of SqlConnection.<\/p>\n\n\n\n<ol class=\"wp-block-list\" type=\"1\">\n<li><strong>static<\/strong>&nbsp;IDbConnection&nbsp;db&nbsp;=&nbsp;<strong>new<\/strong>&nbsp;SqlConnection(ConfigurationManager.ConnectionStrings[&#8220;SqlServerConnString&#8221;].ConnectionString);&nbsp;&nbsp;<\/li>\n<\/ol>\n\n\n\n<p>SqlServerConnString is a connection string name that you can write in the app.config of your console application.<\/p>\n\n\n\n<ol class=\"wp-block-list\" type=\"1\">\n<li>&lt;connectionStrings&gt;&nbsp;&nbsp;<\/li>\n\n\n\n<li>&nbsp;&nbsp;&nbsp;&lt;add&nbsp;name=&#8221;SqlServerConnString&#8221;&nbsp;providerName=&#8221;System.Data.SqlClient&#8221;&nbsp;connectionString=&#8221;Data&nbsp;Source=Friyank\\MSSQLSERVER2012;Initial&nbsp;Catalog=ContactDB;Integrated&nbsp;Security=True;MultipleActiveResultSets=True&#8221;&nbsp;\/&gt;&nbsp;&nbsp;<\/li>\n\n\n\n<li>&lt;\/connectionStrings&gt;&nbsp;&nbsp;<\/li>\n<\/ol>\n\n\n\n<p><strong>Step 2<\/strong><\/p>\n\n\n\n<p>Write a Query and store it into a string.<\/p>\n\n\n\n<ol class=\"wp-block-list\" type=\"1\">\n<li>String&nbsp;query&nbsp;=&nbsp;&#8220;select&nbsp;*&nbsp;from&nbsp;contacts&#8221;;&nbsp;&nbsp;<\/li>\n<\/ol>\n\n\n\n<p><strong>Step 3<\/strong><\/p>\n\n\n\n<p>Fire a query on db instance and typecast the generic return type into a list of contacts.<\/p>\n\n\n\n<ol class=\"wp-block-list\" type=\"1\">\n<li>(List&lt;Contact&gt;)db.Query&lt;Contact&gt;(query);&nbsp;&nbsp;<\/li>\n<\/ol>\n\n\n\n<p>You have successfully retrieved all the data from the contact table just by using these three simple steps to populate this data wherever you need it.<\/p>\n\n\n\n<p>Print the entire list onto Grid view of a Windows Forms form or WPF or pass the entire List of data to any external WCF.<\/p>\n\n\n\n<p>For demo purposes, I will just print this data onto the console.<\/p>\n\n\n\n<figure class=\"wp-block-image size-full\"><img loading=\"lazy\" decoding=\"async\" width=\"940\" height=\"436\" src=\"http:\/\/waqar-arshad.com\/wp-content\/uploads\/2023\/06\/image-41.png\" alt=\"\" class=\"wp-image-1873\" srcset=\"http:\/\/waqar-arshad.com\/wp-content\/uploads\/2023\/06\/image-41.png 940w, http:\/\/waqar-arshad.com\/wp-content\/uploads\/2023\/06\/image-41-300x139.png 300w, http:\/\/waqar-arshad.com\/wp-content\/uploads\/2023\/06\/image-41-768x356.png 768w\" sizes=\"auto, (max-width: 940px) 100vw, 940px\" \/><\/figure>\n\n\n\n<p><strong>Example 2<\/strong><\/p>\n\n\n\n<p>Now let&#8217;s insert data into a Contact table. This also includes the same three steps.<\/p>\n\n\n\n<p><strong>Step 1<\/strong><\/p>\n\n\n\n<p>Create an object of the IDbConnection class and a new instance of SqlConnection.<\/p>\n\n\n\n<ol class=\"wp-block-list\" type=\"1\">\n<li><strong>static<\/strong>&nbsp;IDbConnection&nbsp;db.&nbsp;=&nbsp;<strong>new<\/strong>&nbsp;SqlConnection(ConfigurationManager.ConnectionStrings[&#8220;SqlServerConnString&#8221;].ConnectionString);&nbsp;&nbsp;<\/li>\n<\/ol>\n\n\n\n<p><strong>Step 2<\/strong><\/p>\n\n\n\n<ol class=\"wp-block-list\" type=\"1\">\n<li><strong>string<\/strong>&nbsp;query&nbsp;=&nbsp;@&#8221;Insert&nbsp;into&nbsp;contacts&nbsp;values&nbsp;(@FirstName,&nbsp;@LastName,&nbsp;@Email,&nbsp;@Company,&nbsp;@Title);&nbsp;&nbsp;<\/li>\n\n\n\n<li>Select&nbsp;Cast&nbsp;(Scope_Identity()&nbsp;<strong>as<\/strong>&nbsp;<strong>int<\/strong>)&#8221;;&nbsp;&nbsp;<\/li>\n<\/ol>\n\n\n\n<p>Here, we include two queries to get the last inserted id of the contact table.<\/p>\n\n\n\n<p><strong>Step 3<\/strong><\/p>\n\n\n\n<p>Now fire the query over the db instance and for the return type, we will get a single value and that will be an int. So, store it into an int variable and pass the contact object with the query as follows.<\/p>\n\n\n\n<ol class=\"wp-block-list\" type=\"1\">\n<li><strong>int<\/strong>&nbsp;id&nbsp;=&nbsp;db.Query&lt;<strong>int<\/strong>&gt;(query,contact).Single();&nbsp;&nbsp;<\/li>\n<\/ol>\n\n\n\n<p>And again in these three simple steps, the data will be inserted into the database.<\/p>\n\n\n\n<figure class=\"wp-block-image size-full\"><img loading=\"lazy\" decoding=\"async\" width=\"940\" height=\"458\" src=\"http:\/\/waqar-arshad.com\/wp-content\/uploads\/2023\/06\/image-43.png\" alt=\"\" class=\"wp-image-1875\" srcset=\"http:\/\/waqar-arshad.com\/wp-content\/uploads\/2023\/06\/image-43.png 940w, http:\/\/waqar-arshad.com\/wp-content\/uploads\/2023\/06\/image-43-300x146.png 300w, http:\/\/waqar-arshad.com\/wp-content\/uploads\/2023\/06\/image-43-768x374.png 768w\" sizes=\"auto, (max-width: 940px) 100vw, 940px\" \/><\/figure>\n\n\n\n<p><strong>Example 3<\/strong><\/p>\n\n\n\n<p>Now let&#8217;s get a single amount of data from the database contact table by passing an id in the where clause.<\/p>\n\n\n\n<p><strong>Step 1<\/strong><\/p>\n\n\n\n<p>Create an object of the IDbConnection class and a new instance of SqlConnection.<\/p>\n\n\n\n<ol class=\"wp-block-list\" type=\"1\">\n<li><strong>static<\/strong>&nbsp;IDbConnection&nbsp;db&nbsp;=&nbsp;<strong>new<\/strong>&nbsp;SqlConnection(ConfigurationManager.ConnectionStrings[&#8220;SqlServerConnString&#8221;].ConnectionString);&nbsp;&nbsp;<\/li>\n<\/ol>\n\n\n\n<p><strong>Step 2<\/strong><\/p>\n\n\n\n<ol class=\"wp-block-list\" type=\"1\">\n<li><strong>string<\/strong>&nbsp;query&nbsp;=&nbsp;&#8220;select&nbsp;*&nbsp;from&nbsp;contacts&nbsp;where&nbsp;id&nbsp;=&nbsp;@id&#8221;;&nbsp;&nbsp;<\/li>\n<\/ol>\n\n\n\n<p><strong>Step 3<\/strong><\/p>\n\n\n\n<p>Now fire the query over the db instance and for the return type, we will get a single value and that will be a contact. So, store it into the contact object.<\/p>\n\n\n\n<ol class=\"wp-block-list\" type=\"1\">\n<li>Contact&nbsp;cont&nbsp;=&nbsp;(Contact)db.Query&lt;Contact&gt;(query,&nbsp;<strong>new<\/strong>&nbsp;{id&nbsp;=&nbsp;id&nbsp;}).SingleOrDefault();&nbsp;&nbsp;<\/li>\n<\/ol>\n\n\n\n<p>Again in these three simple steps, data will be fetched from the database using id.<\/p>\n\n\n\n<figure class=\"wp-block-image size-full\"><img loading=\"lazy\" decoding=\"async\" width=\"940\" height=\"458\" src=\"http:\/\/waqar-arshad.com\/wp-content\/uploads\/2023\/06\/image-45.png\" alt=\"\" class=\"wp-image-1877\" srcset=\"http:\/\/waqar-arshad.com\/wp-content\/uploads\/2023\/06\/image-45.png 940w, http:\/\/waqar-arshad.com\/wp-content\/uploads\/2023\/06\/image-45-300x146.png 300w, http:\/\/waqar-arshad.com\/wp-content\/uploads\/2023\/06\/image-45-768x374.png 768w\" sizes=\"auto, (max-width: 940px) 100vw, 940px\" \/><\/figure>\n\n\n\n<p><strong>Note:<\/strong>&nbsp;In the same way we can update and delete records of the database.<\/p>\n\n\n\n<p>We can even use db.Execute().<\/p>\n\n\n\n<p>The main difference between db.query and db.execute is, in db.query we get a return value as we desired since it&#8217;s a generic method and db.execute is not a generic method so it always returns an int.<\/p>\n\n\n\n<p>Working With Advanced Dapper&nbsp;<\/p>\n\n\n\n<ul class=\"wp-block-list\">\n<li>We may have a requirement to deal with multiple tables, for example querying more than two tables at once or fetching data based on the foreign key.<\/li>\n\n\n\n<li>We can access multiple tables at once in Dapper and that is also very smooth.<\/li>\n\n\n\n<li>Pass a list of objects and Dapper itself will identify the insertion as a bulk insert.<\/li>\n\n\n\n<li>Fetch data based on various parameters and Dapper will automatically convert the array into CSV and return all in a list of objects.<\/li>\n<\/ul>\n\n\n\n<p><strong>Example 4<\/strong><strong><\/strong><\/p>\n\n\n\n<p>Let&#8217;s say I have one more table called to address as in the following:<\/p>\n\n\n\n<figure class=\"wp-block-image size-full\"><img loading=\"lazy\" decoding=\"async\" width=\"903\" height=\"766\" src=\"http:\/\/waqar-arshad.com\/wp-content\/uploads\/2023\/06\/image-46.png\" alt=\"\" class=\"wp-image-1878\" srcset=\"http:\/\/waqar-arshad.com\/wp-content\/uploads\/2023\/06\/image-46.png 903w, http:\/\/waqar-arshad.com\/wp-content\/uploads\/2023\/06\/image-46-300x254.png 300w, http:\/\/waqar-arshad.com\/wp-content\/uploads\/2023\/06\/image-46-768x651.png 768w\" sizes=\"auto, (max-width: 903px) 100vw, 903px\" \/><\/figure>\n\n\n\n<p>We will perform CRUD operations on both tables together using the same contact id as the primary key for the contact table and foreign key for the address table.<\/p>\n\n\n\n<p>Now let&#8217;s get multiple data from the database contact table and database address table passing an id.<\/p>\n\n\n\n<p><strong>Step 1<\/strong><\/p>\n\n\n\n<p>Create an object for the IDbConnection class and a new instance of SqlConnection.<\/p>\n\n\n\n<ol class=\"wp-block-list\" type=\"1\">\n<li><strong>static<\/strong>&nbsp;IDbConnection&nbsp;db&nbsp;=&nbsp;<strong>new<\/strong>&nbsp;SqlConnection(ConfigurationManager.ConnectionStrings[&#8220;SqlServerConnString&#8221;].ConnectionString);&nbsp;&nbsp;<\/li>\n<\/ol>\n\n\n\n<p><strong>Step 2<\/strong><\/p>\n\n\n\n<p>Now create a string for multiple queries.<\/p>\n\n\n\n<ol class=\"wp-block-list\" type=\"1\">\n<li><strong>string<\/strong>&nbsp;query&nbsp;=&nbsp;&#8220;select&nbsp;*&nbsp;from&nbsp;contacts&nbsp;where&nbsp;id&nbsp;=&nbsp;@id&nbsp;;&nbsp;select&nbsp;*&nbsp;from&nbsp;addresses&nbsp;where&nbsp;ContactId&nbsp;=&nbsp;@id;&#8221;;&nbsp;&nbsp;<\/li>\n<\/ol>\n\n\n\n<p><strong>Step 3<\/strong><\/p>\n\n\n\n<p>Now fire a query on db instance as follows:<\/p>\n\n\n\n<ol class=\"wp-block-list\" type=\"1\">\n<li><strong>using<\/strong>&nbsp;(var&nbsp;multipleresult&nbsp;=&nbsp;db.QueryMultiple(query,&nbsp;<strong>new<\/strong>&nbsp;{&nbsp;id&nbsp;=&nbsp;id&nbsp;}))&nbsp;&nbsp;<\/li>\n\n\n\n<li>{&nbsp;&nbsp;<\/li>\n\n\n\n<li>&nbsp;&nbsp;&nbsp;var&nbsp;contact&nbsp;=&nbsp;multipleresult.Read&lt;Contact&gt;().SingleOrDefault();&nbsp;&nbsp;<\/li>\n\n\n\n<li>&nbsp;&nbsp;&nbsp;var&nbsp;Addresses&nbsp;=&nbsp;multipleresult.Read&lt;Address&gt;().ToList();&nbsp;&nbsp;<\/li>\n\n\n\n<li><strong>&nbsp;&nbsp;&nbsp;if<\/strong>&nbsp;(contact&nbsp;!=&nbsp;<strong>null<\/strong>&nbsp;&amp;&amp;&nbsp;Addresses&nbsp;!=&nbsp;<strong>null<\/strong>)&nbsp;&nbsp;<\/li>\n\n\n\n<li>&nbsp;&nbsp;&nbsp;{&nbsp;&nbsp;<\/li>\n\n\n\n<li>&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;contact.Addresses.AddRange(Addresses);&nbsp;&nbsp;<\/li>\n\n\n\n<li>&nbsp;&nbsp;&nbsp;}&nbsp;&nbsp;<\/li>\n\n\n\n<li>}&nbsp;&nbsp;<\/li>\n<\/ol>\n\n\n\n<p>And it&#8217;s done, you have successfully retrieved multiple records from multiple tables just using three steps.<\/p>\n\n\n\n<figure class=\"wp-block-image size-full\"><img loading=\"lazy\" decoding=\"async\" width=\"940\" height=\"728\" src=\"http:\/\/waqar-arshad.com\/wp-content\/uploads\/2023\/06\/image-47.png\" alt=\"\" class=\"wp-image-1879\" srcset=\"http:\/\/waqar-arshad.com\/wp-content\/uploads\/2023\/06\/image-47.png 940w, http:\/\/waqar-arshad.com\/wp-content\/uploads\/2023\/06\/image-47-300x232.png 300w, http:\/\/waqar-arshad.com\/wp-content\/uploads\/2023\/06\/image-47-768x595.png 768w\" sizes=\"auto, (max-width: 940px) 100vw, 940px\" \/><\/figure>\n\n\n\n<p><strong>Example 5<\/strong><\/p>\n\n\n\n<p>Let&#8217;s use a Stored Procedure for data access from the database.<\/p>\n\n\n\n<p>I have one Stored Procedure that excepts one parameter (ID) and returns contact data and address data based on that ID.<\/p>\n\n\n\n<ol class=\"wp-block-list\" type=\"1\">\n<li>USE&nbsp;[ContactDB]&nbsp;&nbsp;<\/li>\n\n\n\n<li>GO&nbsp;&nbsp;<\/li>\n\n\n\n<li>\/******&nbsp;Object:&nbsp;&nbsp;StoredProcedure&nbsp;[dbo].[sp_GetContact_Address]&nbsp;&nbsp;&nbsp;&nbsp;Script&nbsp;Date:&nbsp;3\/3\/2015&nbsp;3:29:25&nbsp;PM&nbsp;******\/&nbsp;&nbsp;<\/li>\n\n\n\n<li>SET&nbsp;ANSI_NULLS&nbsp;ON&nbsp;&nbsp;<\/li>\n\n\n\n<li>GO&nbsp;&nbsp;<\/li>\n\n\n\n<li>SET&nbsp;QUOTED_IDENTIFIER&nbsp;ON&nbsp;&nbsp;<\/li>\n\n\n\n<li>GO&nbsp;&nbsp;<\/li>\n\n\n\n<li>Create&nbsp;proc&nbsp;[dbo].[sp_GetContact_Address]&nbsp;&nbsp;<\/li>\n\n\n\n<li>&nbsp;@id&nbsp;<strong>int<\/strong>&nbsp;&nbsp;&nbsp;<\/li>\n\n\n\n<li><strong>as<\/strong>&nbsp;&nbsp;<\/li>\n\n\n\n<li>begin&nbsp;&nbsp;&nbsp;<\/li>\n\n\n\n<li>select&nbsp;*&nbsp;from&nbsp;contacts&nbsp;where&nbsp;id&nbsp;=&nbsp;@id&nbsp;;&nbsp;&nbsp;&nbsp;<\/li>\n\n\n\n<li>select&nbsp;*&nbsp;from&nbsp;addresses&nbsp;where&nbsp;ContactId&nbsp;=&nbsp;@id;&nbsp;&nbsp;<\/li>\n\n\n\n<li>&nbsp;&nbsp;<\/li>\n\n\n\n<li>end&nbsp;&nbsp;<\/li>\n<\/ol>\n\n\n\n<p>To call a Stored Procedure using Dapper.<\/p>\n\n\n\n<p><strong>Step 1<\/strong><\/p>\n\n\n\n<p>Create an object for the IDbConnection class and a new instance of SqlConnection.<\/p>\n\n\n\n<ol class=\"wp-block-list\" type=\"1\">\n<li><strong>static<\/strong>&nbsp;IDbConnection&nbsp;db&nbsp;=&nbsp;<strong>new<\/strong>&nbsp;SqlConnection(ConfigurationManager.ConnectionStrings[&#8220;SqlServerConnString&#8221;].ConnectionString);&nbsp;&nbsp;<\/li>\n<\/ol>\n\n\n\n<p>Here we will not write any query and then pass it to the database.<\/p>\n\n\n\n<p>Instead, we will call a Stored Procedure that is situated inside the database.<\/p>\n\n\n\n<p>The changes we will do here is to pass a Stored Procedure name instead of query and will pass one additional parameter called command type.<\/p>\n\n\n\n<p><strong>Step 2<\/strong><\/p>\n\n\n\n<p>Now fire a query on db instance as follows:<\/p>\n\n\n\n<ol class=\"wp-block-list\" type=\"1\">\n<li><strong>using<\/strong>&nbsp;(var&nbsp;multipleresult&nbsp;=&nbsp;db.QueryMultiple(\u201csp_GetContact_Address\u201d,&nbsp;<strong>new<\/strong>&nbsp;{&nbsp;id&nbsp;=&nbsp;id&nbsp;},&nbsp;commandType:&nbsp;CommandType.StoredProcedure))&nbsp;&nbsp;<\/li>\n\n\n\n<li>{&nbsp;&nbsp;<\/li>\n\n\n\n<li>&nbsp;&nbsp;&nbsp;var&nbsp;contact&nbsp;=&nbsp;multipleresult.Read&lt;Contact&gt;().SingleOrDefault();&nbsp;&nbsp;<\/li>\n\n\n\n<li>&nbsp;&nbsp;&nbsp;var&nbsp;Addresses&nbsp;=&nbsp;multipleresult.Read&lt;Address&gt;().ToList();&nbsp;&nbsp;<\/li>\n\n\n\n<li><strong>&nbsp;&nbsp;&nbsp;if<\/strong>&nbsp;(contact&nbsp;!=&nbsp;<strong>null<\/strong>&nbsp;&amp;&amp;&nbsp;Addresses&nbsp;!=&nbsp;<strong>null<\/strong>)&nbsp;&nbsp;<\/li>\n\n\n\n<li>&nbsp;&nbsp;&nbsp;{&nbsp;&nbsp;<\/li>\n\n\n\n<li>&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;contact.Addresses.AddRange(Addresses);&nbsp;&nbsp;<\/li>\n\n\n\n<li>&nbsp;&nbsp;&nbsp;}&nbsp;&nbsp;<\/li>\n\n\n\n<li>}&nbsp;&nbsp;<\/li>\n<\/ol>\n\n\n\n<p>And in just two simple steps, you have successfully made a call to a database Stored Procedure.<\/p>\n\n\n\n<p><strong>Example 6<\/strong><\/p>\n\n\n\n<p>We can also pass a dynamic object or values to a Stored Procedure when we need to deal with runtime objects.<\/p>\n\n\n\n<p>The following is a sample Stored Procedure that adds a contact into the contact table:<\/p>\n\n\n\n<ol class=\"wp-block-list\" type=\"1\">\n<li><strong>create<\/strong>&nbsp;<strong>procedure<\/strong>&nbsp;[dbo].[SaveContact]&nbsp;&nbsp;<\/li>\n\n\n\n<li>&nbsp;&nbsp;&nbsp;&nbsp;@Id&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;<strong>int<\/strong>&nbsp;<strong>output<\/strong>,&nbsp;&nbsp;<\/li>\n\n\n\n<li>&nbsp;&nbsp;&nbsp;&nbsp;@FirstName&nbsp;&nbsp;<strong>varchar<\/strong>(50),&nbsp;&nbsp;<\/li>\n\n\n\n<li>&nbsp;&nbsp;&nbsp;&nbsp;@LastName&nbsp;&nbsp;&nbsp;<strong>varchar<\/strong>(50),&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;<\/li>\n\n\n\n<li>&nbsp;&nbsp;&nbsp;&nbsp;@Company&nbsp;&nbsp;&nbsp;&nbsp;<strong>varchar<\/strong>(50),&nbsp;&nbsp;<\/li>\n\n\n\n<li>&nbsp;&nbsp;&nbsp;&nbsp;@Title&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;<strong>varchar<\/strong>(50),&nbsp;&nbsp;<\/li>\n\n\n\n<li>&nbsp;&nbsp;&nbsp;&nbsp;@Email&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;<strong>varchar<\/strong>(50)&nbsp;&nbsp;<\/li>\n\n\n\n<li><strong>AS<\/strong>&nbsp;&nbsp;<\/li>\n\n\n\n<li><strong>BEGIN<\/strong>&nbsp;&nbsp;<\/li>\n\n\n\n<li><strong>INSERT<\/strong>&nbsp;<strong>INTO<\/strong>&nbsp;[dbo].[Contacts]&nbsp;&nbsp;<\/li>\n\n\n\n<li>&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;([FirstName],[LastName],[Company],[Title],[Email])&nbsp;<strong>VALUES<\/strong>&nbsp;&nbsp;<\/li>\n\n\n\n<li>&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;(@FirstName,&nbsp;@LastName,&nbsp;@Company,&nbsp;@Title,&nbsp;@Email);&nbsp;&nbsp;<\/li>\n\n\n\n<li>&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;<strong>SET<\/strong>&nbsp;@Id&nbsp;=&nbsp;cast(scope_identity()&nbsp;<strong>as<\/strong>&nbsp;<strong>int<\/strong>)&nbsp;&nbsp;<\/li>\n\n\n\n<li><strong>END<\/strong>;&nbsp;&nbsp;<\/li>\n<\/ol>\n\n\n\n<p>The following shows how to call a Stored Procedure using Dapper and pass dynamic values.&nbsp;<\/p>\n\n\n\n<p><strong>Step 1<\/strong><\/p>\n\n\n\n<p>Create an object of the&nbsp;<strong>IDbConnection<\/strong>&nbsp;class and a new instance of&nbsp;<strong>SqlConnection<\/strong>.<\/p>\n\n\n\n<ol class=\"wp-block-list\" type=\"1\">\n<li><strong>static<\/strong>&nbsp;IDbConnection&nbsp;db&nbsp;=&nbsp;<strong>new<\/strong>&nbsp;SqlConnection(ConfigurationManager.ConnectionStrings[&#8220;SqlServerConnString&#8221;].ConnectionString);&nbsp;&nbsp;<\/li>\n<\/ol>\n\n\n\n<p><strong>Step 2<\/strong><\/p>\n\n\n\n<p>Create a dynamic object and pass a value to that object.<\/p>\n\n\n\n<ol class=\"wp-block-list\" type=\"1\">\n<li>var&nbsp;parameter&nbsp;=&nbsp;<strong>new<\/strong>&nbsp;DynamicParameters();&nbsp;&nbsp;<\/li>\n\n\n\n<li>parameter.Add(&#8220;@Id&#8221;,con.Id,dbType:&nbsp;DbType.Int32,direction:ParameterDirection.InputOutput);&nbsp;&nbsp;<\/li>\n\n\n\n<li>parameter.Add(&#8220;@FirstName&#8221;,&nbsp;con.FirstName);&nbsp;&nbsp;<\/li>\n\n\n\n<li>parameter.Add(&#8220;@LastName&#8221;,&nbsp;con.LastName);&nbsp;&nbsp;<\/li>\n\n\n\n<li>parameter.Add(&#8220;@Company&#8221;,&nbsp;con.Company);&nbsp;&nbsp;<\/li>\n\n\n\n<li>parameter.Add(&#8220;@Title&#8221;,&nbsp;con.Title);&nbsp;&nbsp;<\/li>\n\n\n\n<li>parameter.Add(&#8220;@Email&#8221;,&nbsp;con.Email);&nbsp;&nbsp;<\/li>\n<\/ol>\n\n\n\n<p><strong>Step 3<\/strong><\/p>\n\n\n\n<p>Call a Stored Procedure using the db.execute method.<\/p>\n\n\n\n<ol class=\"wp-block-list\" type=\"1\">\n<li>db.Execute(&#8220;SaveContact&#8221;,parameter,commandType:CommandType.StoredProcedure);&nbsp;&nbsp;<\/li>\n\n\n\n<li>&nbsp;&nbsp;<\/li>\n\n\n\n<li>\/\/To&nbsp;get&nbsp;newly&nbsp;created&nbsp;ID&nbsp;back&nbsp;&nbsp;<\/li>\n\n\n\n<li>con.Id&nbsp;=&nbsp;parameter.Get&lt;<strong>int<\/strong>&gt;(&#8220;@Id&#8221;);&nbsp;&nbsp;<\/li>\n<\/ol>\n\n\n\n<p>Passing data to the Stored Procedure.<\/p>\n\n\n\n<ol class=\"wp-block-list\" type=\"1\">\n<li>#region&nbsp;Insert&nbsp;Dynamic&nbsp;Object&nbsp;To&nbsp;Database&nbsp;&nbsp;<\/li>\n\n\n\n<li>dynamic&nbsp;c&nbsp;=&nbsp;<strong>new<\/strong>&nbsp;Contact();&nbsp;&nbsp;<\/li>\n\n\n\n<li>Program&nbsp;p&nbsp;=&nbsp;<strong>new<\/strong>&nbsp;Program();&nbsp;&nbsp;<\/li>\n\n\n\n<li>Console.WriteLine(&#8220;Enter&nbsp;First&nbsp;Name&nbsp;:&nbsp;&#8220;);&nbsp;&nbsp;<\/li>\n\n\n\n<li>c.FirstName&nbsp;=&nbsp;Console.ReadLine();&nbsp;&nbsp;<\/li>\n\n\n\n<li>Console.WriteLine(&#8220;Enter&nbsp;Last&nbsp;Name&nbsp;:&nbsp;&#8220;);&nbsp;&nbsp;<\/li>\n\n\n\n<li>c.LastName&nbsp;=&nbsp;Console.ReadLine();&nbsp;&nbsp;<\/li>\n\n\n\n<li>Console.WriteLine(&#8220;Enter&nbsp;Email&nbsp;Address&nbsp;:&nbsp;&#8220;);&nbsp;&nbsp;<\/li>\n\n\n\n<li>c.Email&nbsp;=&nbsp;Console.ReadLine();&nbsp;&nbsp;<\/li>\n\n\n\n<li>Console.WriteLine(&#8220;Enter&nbsp;Company&nbsp;Name:&nbsp;&#8220;);&nbsp;&nbsp;<\/li>\n\n\n\n<li>c.Company&nbsp;=&nbsp;Console.ReadLine();&nbsp;&nbsp;<\/li>\n\n\n\n<li>Console.WriteLine(&#8220;Enter&nbsp;Title&nbsp;:&nbsp;&#8220;);&nbsp;&nbsp;<\/li>\n\n\n\n<li>c.Title&nbsp;=&nbsp;Console.ReadLine();&nbsp;&nbsp;<\/li>\n\n\n\n<li>Console.WriteLine(&#8220;New&nbsp;Contact&nbsp;Created&nbsp;With&nbsp;ID&nbsp;{0}&nbsp;&#8220;,&nbsp;p.dynamicspcall(c).Id);&nbsp;&nbsp;<\/li>\n\n\n\n<li>#endregion&nbsp;&nbsp;<\/li>\n<\/ol>\n\n\n\n<p>And these are the ways to manipulate a database using C# dynamic objects.<\/p>\n","protected":false},"excerpt":{"rendered":"<p>There are many ORM tools available. One of them is Dapper. Dapper is known as the king of ORM. The following are the key features of Dapper: Why Dapper Image reference:\u00a0Dapper dotnet. How to Install Dapper There are two ways to install Dapper: Add this SqlMapperAsync.cs file to your project and get started with Dapper [&hellip;]<\/p>\n","protected":false},"author":1,"featured_media":0,"comment_status":"open","ping_status":"open","sticky":false,"template":"","format":"standard","meta":{"_uag_custom_page_level_css":"","site-sidebar-layout":"default","site-content-layout":"","ast-site-content-layout":"default","site-content-style":"default","site-sidebar-style":"default","ast-global-header-display":"","ast-banner-title-visibility":"","ast-main-header-display":"","ast-hfb-above-header-display":"","ast-hfb-below-header-display":"","ast-hfb-mobile-header-display":"","site-post-title":"","ast-breadcrumbs-content":"","ast-featured-img":"","footer-sml-layout":"","theme-transparent-header-meta":"","adv-header-id-meta":"","stick-header-meta":"","header-above-stick-meta":"","header-main-stick-meta":"","header-below-stick-meta":"","astra-migrate-meta-layouts":"default","ast-page-background-enabled":"default","ast-page-background-meta":{"desktop":{"background-color":"","background-image":"","background-repeat":"repeat","background-position":"center center","background-size":"auto","background-attachment":"scroll","background-type":"","background-media":"","overlay-type":"","overlay-color":"","overlay-opacity":"","overlay-gradient":""},"tablet":{"background-color":"","background-image":"","background-repeat":"repeat","background-position":"center center","background-size":"auto","background-attachment":"scroll","background-type":"","background-media":"","overlay-type":"","overlay-color":"","overlay-opacity":"","overlay-gradient":""},"mobile":{"background-color":"","background-image":"","background-repeat":"repeat","background-position":"center center","background-size":"auto","background-attachment":"scroll","background-type":"","background-media":"","overlay-type":"","overlay-color":"","overlay-opacity":"","overlay-gradient":""}},"ast-content-background-meta":{"desktop":{"background-color":"var(--ast-global-color-5)","background-image":"","background-repeat":"repeat","background-position":"center center","background-size":"auto","background-attachment":"scroll","background-type":"","background-media":"","overlay-type":"","overlay-color":"","overlay-opacity":"","overlay-gradient":""},"tablet":{"background-color":"var(--ast-global-color-5)","background-image":"","background-repeat":"repeat","background-position":"center center","background-size":"auto","background-attachment":"scroll","background-type":"","background-media":"","overlay-type":"","overlay-color":"","overlay-opacity":"","overlay-gradient":""},"mobile":{"background-color":"var(--ast-global-color-5)","background-image":"","background-repeat":"repeat","background-position":"center center","background-size":"auto","background-attachment":"scroll","background-type":"","background-media":"","overlay-type":"","overlay-color":"","overlay-opacity":"","overlay-gradient":""}},"footnotes":""},"categories":[12,13,11,20,14],"tags":[],"class_list":["post-1870","post","type-post","status-publish","format-standard","hentry","category-asp-net","category-asp-net-core","category-csharp","category-database","category-orm"],"uagb_featured_image_src":{"full":false,"thumbnail":false,"medium":false,"medium_large":false,"large":false,"1536x1536":false,"2048x2048":false},"uagb_author_info":{"display_name":"admin","author_link":"http:\/\/waqar-arshad.com\/index.php\/author\/waqar_29_1\/"},"uagb_comment_info":19,"uagb_excerpt":"There are many ORM tools available. One of them is Dapper. Dapper is known as the king of ORM. The following are the key features of Dapper: Why Dapper Image reference:\u00a0Dapper dotnet. How to Install Dapper There are two ways to install Dapper: Add this SqlMapperAsync.cs file to your project and get started with Dapper&hellip;","_links":{"self":[{"href":"http:\/\/waqar-arshad.com\/index.php\/wp-json\/wp\/v2\/posts\/1870","targetHints":{"allow":["GET"]}}],"collection":[{"href":"http:\/\/waqar-arshad.com\/index.php\/wp-json\/wp\/v2\/posts"}],"about":[{"href":"http:\/\/waqar-arshad.com\/index.php\/wp-json\/wp\/v2\/types\/post"}],"author":[{"embeddable":true,"href":"http:\/\/waqar-arshad.com\/index.php\/wp-json\/wp\/v2\/users\/1"}],"replies":[{"embeddable":true,"href":"http:\/\/waqar-arshad.com\/index.php\/wp-json\/wp\/v2\/comments?post=1870"}],"version-history":[{"count":1,"href":"http:\/\/waqar-arshad.com\/index.php\/wp-json\/wp\/v2\/posts\/1870\/revisions"}],"predecessor-version":[{"id":1880,"href":"http:\/\/waqar-arshad.com\/index.php\/wp-json\/wp\/v2\/posts\/1870\/revisions\/1880"}],"wp:attachment":[{"href":"http:\/\/waqar-arshad.com\/index.php\/wp-json\/wp\/v2\/media?parent=1870"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"http:\/\/waqar-arshad.com\/index.php\/wp-json\/wp\/v2\/categories?post=1870"},{"taxonomy":"post_tag","embeddable":true,"href":"http:\/\/waqar-arshad.com\/index.php\/wp-json\/wp\/v2\/tags?post=1870"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}