学步园

Dapper结合Repository模式的应用，包括如何在数据访问层（DAL）使用Dapper组件。

Dapper在真实项目中使用，扩展IDbConnection的功能,支持Oracle、MS SQL Server 2005数据库

1）定义统一的IDbConnection访问入口

public class Database
    {
        /// 得到web.config里配置项的数据库连接字符串。
        private static readonly string ConnectionString = ConfigurationManager.ConnectionStrings["ConnectionString"].ToString();
        /// 得到工厂提供器类型
        private static readonly string ProviderFactoryString = ConfigurationManager.AppSettings["DBProvider"].ToString();
        private static DbProviderFactory df = null;
        /// <summary>
        /// 创建工厂提供器并且
        /// </summary>
        public static IDbConnection DbService()
        {
            if (df == null)
                df = DbProviderFactories.GetFactory(ProviderFactoryString);
            var connection = df.CreateConnection();
            connection.ConnectionString = ConnectionString;
            connection.Open();
            return connection;
        }
}

2）app.config配置

<?xml version="1.0"?>
<configuration>
  <configSections>
  </configSections>
  <appSettings>
    <add key="DBProvider" value="System.Data.SqlClient"/>
  </appSettings>
  <connectionStrings>
    <add name="ConnectionString" connectionString="Data Source=.;Initial Catalog=PlanDb;User ID=sa;Password=manager;" providerName="System.Data.SqlClient" />
  </connectionStrings>
  <startup>
    <supportedRuntime version="v4.0" sku=".NETFramework,Version=v4.0"/>
  </startup>
</configuration>

3）Repository模式实现

/// <summary>
    /// 产品管理
    /// </summary>
    public class ProductRepository
    {
        public static Product GetById(int id)
        {
            var sqlstr = "select * from dbo.Product where Product_ID=@id";
            using (var conn = Database.DbService())
            {
                return conn.Query<Product>(sqlstr, new { id }).Single();
            }
        }

        public static List<Product> GetByPid(int pid)
        {
            var sqlstr = "select * from dbo.Product where Parent_ID=@pid order by Product_no";
            using (var conn = Database.DbService())
            {
                return conn.Query<Product>(sqlstr, new { pid }).ToList();
            }
        }

        /// <summary>
        /// 获取所有产品--机型
        /// </summary>
        /// <returns></returns>
        public static List<Product> GetAllTop()
        {
            var sqlstr = "select * from dbo.Product where Parent_ID=0 order by Product_no";
            using (var conn = Database.DbService())
            {
                return conn.Query<Product>(sqlstr).ToList();
            }
        }

        public static void Insert(Product model)
        {
            if (model.Product_ID == 0)
                model.Product_ID = NextId;
            string sqlstr = "INSERT INTO dbo.Product" +
                "(Product_ID, Parent_ID, Product_No, Product_Name, Product_Type, Remark, Creater, Create_Date, Data_Availability) " +
                "values(@Product_ID,@Parent_ID,@Product_No,@Product_Name,@Product_Type,@Remark,@Creater,@Create_Date,@Data_Availability)";
            using (var conn = Database.DbService())
            {               
                conn.Execute(sqlstr, model);
            }
        }

        public static void Delete(int id)
        {
            var sqlstr = "delete from dbo.Product where Product_ID=@id";
            using (var conn = Database.DbService())
            {
                conn.Execute(sqlstr, new { id });
            }
        }
        public static void Update(Product model)
        {
            string sqlstr = "UPDATE dbo.Product " +
                "SET Product_No = @Product_No," +
                "    Product_Name = @Product_Name, " +
                "    Product_Type = @Product_Type, " +
                "    Remark = @Remark" +
                " WHERE Product_ID = @Product_ID";
            using (var conn = Database.DbService())
            {
                conn.Execute(sqlstr,  model);
            }
        }
        /// <summary>
        /// 下一个ID
        /// </summary>
        public static int NextId
        {
            get
            {
                return Database.NextId("Product");
            }
        }
        public static bool Exists(string no)
        {
            var sqlstr = "select count(*) from dbo.Product where Product_No=@no";
            using (var conn = Database.DbService())
            {
                return conn.Query<int>(sqlstr, new { no }).Single() > 0;
            }
        }
    }

http://blog.csdn.net/dacong 转载请注明出处

public class Product
    {
        #region Fields
        private int _product_id;
        private int _parent_id;
        private string _product_no = "";
        private string _product_name = "";
        private string _product_type = "";
        private string _remark = "";
        private string _creater = "";
        private DateTime _create_date;
        private string _data_availability = "";
        #endregion

        public Product()
        {
            _parent_id = 0;
            _data_availability = "Y";
        }
        #region Public Properties

        public int Product_ID
        {
            get { return _product_id; }
            set
            {
                _product_id = value;
            }
        }

        /// <summary>
        /// 父产品ID,0为最顶层产品
        /// </summary>
        public int Parent_ID
        {
            get { return _parent_id; }
            set
            {
                _parent_id = value;
            }
        }

        public string Product_No
        {
            get { return _product_no; }
            set
            {
                _product_no = value;
            }
        }

        public string Product_Name
        {
            get { return _product_name; }
            set
            {
                _product_name = value;
            }
        }

        public string Product_Type
        {
            get { return _product_type; }
            set
            {
                _product_type = value;
            }
        }

        public string Remark
        {
            get { return _remark; }
            set
            {
                _remark = value;
            }
        }

        public string Creater
        {
            get { return _creater; }
            set
            {
                _creater = value;
            }
        }

        public DateTime Create_Date
        {
            get { return _create_date; }
            set
            {
                _create_date = value;
            }
        }

        public string Data_Availability
        {
            get { return _data_availability; }
            set
            {
                _data_availability = value;
            }
        }


        #endregion
}