36、在C#项目中使用Dapper

来源：互联网发布：电竞椅知乎编辑：程序博客网时间：2024/06/05 02:24

第一部分：项目使用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);        }    }}

阅读全文

0 0