36、在C#项目中使用Dapper

第一部分：项目使用dapper流程；

1）、首先需要在网上下载dapper.dll放到lib中（笔者全路径：svn\Src\Wind.BlackList\dev\lib），作为项目引用的dll，然后在需要用到的地方引用命名空间：using Dapper;

2）、以连接oracle数据库为例：oracle数据库的连接需要用到：Oracle.ManagedDataAccess.dll，并在要用到的地方引用命名空间：using Oracle.ManagedDataAccess.Client;（注意这里是引用.client），数据库的连接使用IDbConnection conn = new OracleConnection(connectionString)，事务方式使用IDbTransaction tran = conn.BeginTransaction()

           说明：Oracle.ManagedDataAccess.dll的位置：在正确安装了oracle客户端之后，在客户端目录下可以找到，笔者的全路径为：D:\app\szxu\product\12.1.0\client_2\odp.net\managed\common\Oracle.ManagedDataAccess.dll，可以直接使用，无需再次复制dll到lib中

3）、直接使用

第二部分：dapper和数据库的交互

1）、Dapper和Oracle数据库交互（注意sql中参数的前导符是分号）

using System.Data;
using System.Data.SqlClient;
using Dapper;

string connectionString = "DATA SOURCE=ORCL;USER ID=crm;PASSWORD=crm";
            using (IDbConnection connection = new OracleConnection(connectionString))
            { 
                string sql = @"select * from crm.Tb_Cus_Customer where customerid = :CustomerID";  //OracleConnection：赋值参数只能使用“：CustomerID”
                 
                connection.Open();
                var result = connection.Query<CustomerEntity>(sql, new { CustomerID = 38839 });
                connection.Close();
            }

2）、Dapper和SQL server数据库交互（注意sql中参数的前导符是@号）

using System.Data;
using System.Data.SqlClient;
using Dapper;

string connectionString = "DATA SOURCE=eipdbtest;database=WindEmployee;USER ID=windtechw1;pwd=windtechw1;Connection Timeout=360";
            using (IDbConnection connection = new SqlConnection(connectionString))
            {
                string sql = @"select * from crm.Tb_Cus_Customer where customerid = @CustomerID";  //SqlConnection：赋值参数只能使用“@CustomerID”

                connection.Open();
                var result = connection.Query<CustomerEntity>(sql, new { CustomerID = 38839 });
                connection.Close();
            }

第三部分：示例

DACustomer.cs文件：

using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using Wind.BlackList.Model;
using Dapper;
using System.Data;
using Oracle.ManagedDataAccess.Client;

namespace Wind.BlackList.DataAccess
{
    public partial class DACustomer
    {
        private static DACustomer _instance;

        public static DACustomer Instance
        {
            get 
            {
                if (_instance == null)
                {
                    _instance = new DACustomer();
                }
                return _instance;
            }
        }

        /// <summary>
        /// 通过代码或名称获取客户信息
        /// </summary>
        /// <param name="customerKey"></param>
        /// <returns></returns>
        public void GetCustomerByCodeOrName(string customerKey)
        {
            string connectionString = "DATA SOURCE=ORCL;USER ID=xxx;PASSWORD=xxx";
            string sql = @"select * from crm.Tb_Cus_Customer where customercode = :customercode";

            using (IDbConnection conn = new OracleConnection(connectionString))
            {
                conn.Open();
                var result = conn.Query<Customer>(sql, new { customercode = customerKey });
                conn.Close();
            }
        }

        /// <summary>
        /// 更新客户地址信息  
        /// </summary>
        public bool UpdateCustomerAddress(int customerID, string address)
        {
            string connectionString = "DATA SOURCE=ORCL;USER ID=xxx;PASSWORD=xxx";
            
            //事务方式（注意使用事务方式时，必须使用open将数据库连接打开；如果不使用事务方式，则open不是必须的）
            using (IDbConnection conn = new OracleConnection(connectionString))
            {
                conn.Open();
                using (IDbTransaction tran = conn.BeginTransaction())
                {
                    try
                    {
                        string sql = @"update xxx.Tb_Cus_Customer set address = :address where customerid = :customerid";
                        conn.Execute(sql, new { address = address, customerid = customerID }, tran);
                        
                        tran.Commit();
                    }
                    catch(Exception ex)
                    {
                        tran.Rollback();
                        throw new Exception(ex.Message);
                    }
                }

                conn.Close();
            }

            return true;
        }
    }
}

主文件：

using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using Wind.BlackList.DataAccess;

namespace Wind.BlackList
{
    class Program
    {
        static void Main(string[] args)
        {
            //Dapper使用示例
            DACustomer customer = DACustomer.Instance;
            string customerKey = "0001";
            string address = "xxx市xxx区xxx路2000号";
            int customerID = 38839;

            //通过代码或名称获取客户信息
            customer.GetCustomerByCodeOrName(customerKey);

            //更新客户地址信息  
            customer.UpdateCustomerAddress(customerID, address);
        }
    }
}