Dapper在真实项目中使用,扩展IDbConnection的功能,支持Oracle、MS SQL Server 2005数据库
1)定义统一的IDbConnection访问入口
- publicclassDatabase
- {
- ///得到web.config里配置项的数据库连接字符串。
- privatestaticreadonlystringConnectionString=ConfigurationManager.ConnectionStrings["ConnectionString"].ToString();
- ///得到工厂提供器类型
- privatestaticreadonlystringProviderFactoryString=ConfigurationManager.AppSettings["DBProvider"].ToString();
- privatestaticDbProviderFactorydf=null;
- ///<summary>
- ///创建工厂提供器并且
- ///</summary>
- publicstaticIDbConnectionDbService()
- {
- if(df==null)
- df=DbProviderFactories.GetFactory(ProviderFactoryString);
- varconnection=df.CreateConnection();
- connection.ConnectionString=ConnectionString;
- connection.Open();
- returnconnection;
- }
- }
2)app.config配置
- <?xmlversion="1.0"?>
- <configuration>
- <configSections>
- </configSections>
- <appSettings>
- <addkey="DBProvider"value="System.Data.SqlClient"/>
- </appSettings>
- <connectionStrings>
- <addname="ConnectionString"connectionString="DataSource=.;InitialCatalog=PlanDb;UserID=sa;Password=manager;"providerName="System.Data.SqlClient"/>
- </connectionStrings>
- <startup>
- <supportedRuntimeversion="v4.0"sku=".NETFramework,Version=v4.0"/>
- </startup>
- </configuration>
3)Repository模式实现
- ///<summary>
- ///产品管理
- ///</summary>
- publicclassProductRepository
- {
- publicstaticProductGetById(intid)
- {
- varsqlstr="select*fromdbo.ProductwhereProduct_ID=@id";
- using(varconn=Database.DbService())
- {
- returnconn.Query<Product>(sqlstr,new{id}).Single();
- }
- }
- publicstaticList<Product>GetByPid(intpid)
- {
- varsqlstr="select*fromdbo.ProductwhereParent_ID=@pidorderbyProduct_no";
- using(varconn=Database.DbService())
- {
- returnconn.Query<Product>(sqlstr,new{pid}).ToList();
- }
- }
- ///<summary>
- ///获取所有产品--机型
- ///</summary>
- ///<returns></returns>
- publicstaticList<Product>GetAllTop()
- {
- varsqlstr="select*fromdbo.ProductwhereParent_ID=0orderbyProduct_no";
- using(varconn=Database.DbService())
- {
- returnconn.Query<Product>(sqlstr).ToList();
- }
- }
- publicstaticvoidInsert(Productmodel)
- {
- if(model.Product_ID==0)
- model.Product_ID=NextId;
- stringsqlstr="INSERTINTOdbo.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(varconn=Database.DbService())
- {
- conn.Execute(sqlstr,model);
- }
- }
- publicstaticvoidDelete(intid)
- {
- varsqlstr="deletefromdbo.ProductwhereProduct_ID=@id";
- using(varconn=Database.DbService())
- {
- conn.Execute(sqlstr,new{id});
- }
- }
- publicstaticvoidUpdate(Productmodel)
- {
- stringsqlstr="UPDATEdbo.Product"+
- "SETProduct_No=@Product_No,"+
- "Product_Name=@Product_Name,"+
- "Product_Type=@Product_Type,"+
- "Remark=@Remark"+
- "WHEREProduct_ID=@Product_ID";
- using(varconn=Database.DbService())
- {
- conn.Execute(sqlstr,model);
- }
- }
- ///<summary>
- ///下一个ID
- ///</summary>
- publicstaticintNextId
- {
- get
- {
- returnDatabase.NextId("Product");
- }
- }
- publicstaticboolExists(stringno)
- {
- varsqlstr="selectcount(*)fromdbo.ProductwhereProduct_No=@no";
- using(varconn=Database.DbService())
- {
- returnconn.Query<int>(sqlstr,new{no}).Single()>0;
- }
- }
- }
http://blog.csdn.net/dacong转载请注明出处
- publicclassProduct
- {
- #regionFields
- privateint_product_id;
- privateint_parent_id;
- privatestring_product_no="";
- privatestring_product_name="";
- privatestring_product_type="";
- privatestring_remark="";
- privatestring_creater="";
- privateDateTime_create_date;
- privatestring_data_availability="";
- #endregion
- publicProduct()
- {
- _parent_id=0;
- _data_availability="Y";
- }
- #regionPublicProperties
- publicintProduct_ID
- {
- get{return_product_id;}
- set
- {
- _product_id=value;
- }
- }
- ///<summary>
- ///父产品ID,0为最顶层产品
- ///</summary>
- publicintParent_ID
- {
- get{return_parent_id;}
- set
- {
- _parent_id=value;
- }
- }
- publicstringProduct_No
- {
- get{return_product_no;}
- set
- {
- _product_no=value;
- }
- }
- publicstringProduct_Name
- {
- get{return_product_name;}
- set
- {
- _product_name=value;
- }
- }
- publicstringProduct_Type
- {
- get{return_product_type;}
- set
- {
- _product_type=value;
- }
- }
- publicstringRemark
- {
- get{return_remark;}
- set
- {
- _remark=value;
- }
- }
- publicstringCreater
- {
- get{return_creater;}
- set
- {
- _creater=value;
- }
- }
- publicDateTimeCreate_Date
- {
- get{return_create_date;}
- set
- {
- _create_date=value;
- }
- }
- publicstringData_Availability
- {
- get{return_data_availability;}
- set
- {
- _data_availability=value;
- }
- }
- #endregion
- }