@manhnguyenv

Welcome to my blog!

Using Dapper

March 25, 2018 11:09

Using Dapper

var data = new List<uspGetCustomersDynamicSearchPagingSorting>();
using (IDbConnection db = new SqlConnection(ConfigurationManager.ConnectionStrings["DbPaging"].ConnectionString))
{
    //var dynamicParams = new DynamicParameters();
    //Dictionary<string, object> parameters = new Dictionary<string, object>();                
    //foreach (var key in parameters.Keys)
    //{
    //    dynamicParams.Add(key, parameters[key]);
    //}

    //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);
    //db.Execute(@"create proc #TestProc @a int, @b int output as begin set @b = 999 select 1111 return @a end");
    //db.Query("#TestProc", p, commandType: CommandType.StoredProcedure).FirstOrDefault();
    //db.Query("#TestProc", p, commandType: CommandType.StoredProcedure).First().IsEqualTo(1111);
    //p.Get("c").IsEqualTo(11);
    //p.Get("b").IsEqualTo(999);

    var p = new DynamicParameters();
    p.Add("CustomersID", null);
    p.Add("Name", null);
    p.Add("City", null);
    p.Add("Email", null);
    p.Add("Company", null);
    p.Add("PageNbr", startRec / pageSize + 1);
    p.Add("PageSize", pageSize);
    p.Add("SortCol", string.Empty);
    p.Add("totalRecords", dbType: DbType.Int32, direction: ParameterDirection.Output);
    data = db.Query<uspGetCustomersDynamicSearchPagingSorting>("uspGetCustomersDynamicSearchPagingSorting", p, commandType: CommandType.StoredProcedure).ToList();

    // Total record count.
    totalRecords = p.Get("totalRecords");
}

 

Dapper Log4net AutoMapper

March 13, 2018 08:35

How to use the Dapper (edit)

- Create new ASP.NET Web Application called WebApplication1 (MVC: Web Forms, MVC, Web API) based on .NET Framework 4.5.2

- Nuget packages:

  + Install-Package log4net

  + Install-Package Dapper

  + Install-Package MySql.Data

  + Install-Package AutoMapper

  + Install-Package Newtonsoft.Json

- Web.config/App.config

  + Define connection string in <connectionStrings> or <appSettings>

- Controllers/HomeController.cs

- Documentation

Dapper with MS SQL Server

- Dapper with raw SQL

 

- Dapper with Stored Procedure

- Sample code

Dapper with MySQL

Nuget notes

+ .NET Framework 4.5
+ ASP.NET MVC 5.2
+ ASP.NET Web API 2.2
+ Web Pages 3.2

Update-Package
Install-Package jQuery -Version 1.12.4
Install-Package Bootstrap -version 3.3.7
Install-Package modernizr
Install-Package Newtonsoft.json
Install-Package log4net
Install-Package Dapper -Version 1.50.2 (.NET Framework 4.5)
Install-Package Microsoft.AspNet.WebApi (-Version 5.2.4)
Install-Package Swashbuckle (-Version 5.6.0)

Web API & Swagger + OAuth2
http://wmpratt.com/swagger-and-asp-net-web-api-part-1/

http://wmpratt.com/part-ii-swagger-and-asp-net-web-api-enabling-oauth2/

https://www.codeproject.com/Articles/1187872/Token-Based-Authentication-for-Web-API-where-Legac

http://bitoftech.net/2014/06/01/token-based-authentication-asp-net-web-api-2-owin-asp-net-identity/ 

Dapper

December 9, 2017 16:58

Dapper (edit)

Guidelinehttp://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="ONGAME_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", "anhht6886", 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; }
    }
}

Categories

Recent posts