Dapper (edit)

Asynchronous methods in Dapper

Dapper contains several asynchronous methods that you can use to perform asynchronous CRUD operations. Here is the list of asynchronous methods in Dapper:

• ExecuteAsync
• QueryAsync
• QueryFirstAsync
• QueryFirstOrDefaultAsync
• QuerySingleAsync
• QuerySingleOrDefaultAsync
• QueryMultipleAsync

How to perform async operations using Dapper | InfoWorld

Using Entity Framework Core and Dapper in ASP.NET Core - Safe Transactions (codewithmukesh.com) (HAY HAY HAY)

gtechsltn/EFCoreAndDapper: Learn to Work with Dapper and Entity Framework within the Same ASP.NET Core Solutions. This is an implementation taking Transactions into concern as well. Probably a Facade Pattern too! (github.com)

Guideline: http://dapper-tutorial.net/

https://www.infoworld.com/article/3025784/application-development/how-to-work-with-dapper-in-c.html

https://www.codeproject.com/Articles/889668/SQL-Server-Dapper

https://www.c-sharpcorner.com/article/display-multiple-tables-data-in-single-view-from-database-in/

https://dotnetarchitect.wordpress.com/2012/03/26/fast-simple-data-access-using-dapper-net/

Transaction

http://dapper-tutorial.net/transaction

Multi Result

http://dapper-tutorial.net/querymultiple

https://stackoverflow.com/questions/6751052/how-to-map-multiple-records-from-a-single-sp-with-dapper-dot-net

https://medium.com/dapper-net/handling-multiple-resultsets-4b108a8c5172

Unit Test

https://www.intertech.com/Blog/unit-test-dapper-with-dapperparameters/

http://thesenilecoder.blogspot.com/2012/08/dapper-dot-net-dapperwrapper-and.html

Simple Sample

Nuget: Install-package dapper -version 1.50.2

1) App.config

<?xml version="1.0" encoding="utf-8" ?>
<configuration>
 <appSettings>
   <add key="Server" value="192.168.200.179" />
   <add key="DB" value="WEB" />
   <add key="UserId" value="sa" />
   <add key="Password" value="123456" />
   <add key="ConnectTimeout" value="2" />
 </appSettings>
 <startup>
   <supportedRuntime version="v4.0" sku=".NETFramework,Version=v4.5" />
 </startup>
</configuration>

2) Code Sample

using Dapper;
using System;
using System.Configuration;
using System.Data;
using System.Data.SqlClient;
using System.Linq;

namespace QuicklyCheckASQLConnection
{
    internal class Program
    {
        private static void Main(string[] args)
        {
            var server = ConfigurationManager.AppSettings["Server"];
            var db = ConfigurationManager.AppSettings["DB"];
            var userId = ConfigurationManager.AppSettings["UserId"];
            var password = ConfigurationManager.AppSettings["Password"];
            var connectTimeout = 0;

            try
            {
                try
                {
                    connectTimeout = Convert.ToInt32(ConfigurationManager.AppSettings["ConnectTimeout"]);
                }
                catch
                {
                    connectTimeout = 2;
                }

                var connstr = new SqlConnectionStringBuilder
                {
                    DataSource = server,
                    InitialCatalog = db,
                    UserID = userId,
                    Password = password,
                    ConnectTimeout = connectTimeout
                }.ConnectionString;

                //using (var conn = new SqlConnection(connstr))
                //{
                //    conn.Open();
                //}

                TestProc(connstr);

                Console.WriteLine("Connect DB Successfully!");
                Console.ReadKey();
            }
            catch (Exception ex)
            {
                Console.WriteLine("Cannot Connect to DB. Exception: ");
                Console.WriteLine(ex.ToString());
                Console.ReadKey();
            }
        }

        ///
        /// ConnectTimeout
        ///
        ///
        private static void TestProc(string connstr)
        {
            var parameter = new DynamicParameters();
            parameter.Add("@userid", "username", DbType.String, ParameterDirection.Input);
            parameter.Add("@RowCount", dbType: DbType.Int32, direction: ParameterDirection.ReturnValue);
            using (var conn = new SqlConnection(connstr))
            {
                var list = conn.Query("sp_USER_INFO_GetUserInfo_ById", parameter, commandType: CommandType.StoredProcedure).ToList();

                //var list = conn.Query("sp_USER_INFO_GetUserInfo_ById", parameter, commandTimeout: 1, commandType: CommandType.StoredProcedure).ToList();
            }
        }

        /// 
        /// https://stackoverflow.com/questions/22353881/passing-output-parameters-to-stored-procedure-using-dapper-in-c-sharp-code
        /// 
        private static void TestProcSupport(string connstr)
        {
            var p = new DynamicParameters();
            p.Add("a", 11);
            p.Add("b", dbType: DbType.Int32, direction: ParameterDirection.Output);
            p.Add("c", dbType: DbType.Int32, direction: ParameterDirection.ReturnValue);

            using (var conn = new SqlConnection(connstr))
            {
                conn.Execute(@"create proc #TestProc
                         @a int,
                             @b int output
                             as
                             begin
                                 set @b = 999
                                 select 1111
                                 return @a
                             end");
                //conn.Query("#TestProc", p, commandType: CommandType.StoredProcedure).First().IsEqualTo(1111);
                //p.Get("c").IsEqualTo(11);
                //p.Get("b").IsEqualTo(999);
            }
        }

        /// 
        /// https://stackoverflow.com/questions/22353881/passing-output-parameters-to-stored-procedure-using-dapper-in-c-sharp-code
        /// 
        private static void InsertData(string connstr)
        {
            var p = new DynamicParameters();
            p.Add("VAR1", "John");
            p.Add("VAR2", "McEnroe");
            p.Add("BASEID", 1);
            p.Add("NEWID", dbType: DbType.Int32, direction: ParameterDirection.Output);
            using (var conn = new SqlConnection(connstr))
            {
                conn.Query("SP_MYTESTpROC", p, commandType: CommandType.StoredProcedure);
                int newID = p.Get("NEWID");
            }
        }
    }

    public class USER_INFO_GetUserInfo_ById
    {
        public string UserID { get; set; }
        public string NickName { get; set; }
        public string UserName { get; set; }
        public decimal? SEX { get; set; }
        public decimal? OnCash { get; set; }
        public string EMAIL { get; set; }
        public string PhotoPath { get; set; }
        public string PhotoNM { get; set; }
        public string PhotoNMNew { get; set; }
        public string PhotoPathNew { get; set; }
        public string PhotoSetYN { get; set; }
        public DateTime? LastLoginDate { get; set; }
        public DateTime? LastGiftDate { get; set; }
        public string StateMessage { get; set; }
        public decimal? LikeCnt { get; set; }
    }
}