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
}