.NET 常用ORM之SubSonic
一、SubSonic简单介绍
SubSonic是一个类似Rails的开源.NET项目。你可以把它看作是一把瑞士军刀，它可以用来构建Website和通过ORM方式来访问数据。Rob Conery和Eric Kemp是推动SubSonic的主要开发人员，与项目良好的发展有着密切的关系。是一个优秀的、开源的ORM映射框架。
另外官方有提供符合自身需要的代码生成器sonic.exe，但是笔者在SubSonic并未下载到类代码生成器，而是按照SubSonic的映射规则，在之前的用过的SubSonic的类上面做的修改，为下文的demo所使用，如果哪位朋友有SubSonic代码生成器连接，欢迎共享一下给大家。
二、SubSonic使用步骤
1、新增SubSonic配置文件并引入SubSonic.dll
配置文件并不多，有三处分别加入到web.config
<configSections>
<section name="SubSonicService" type="SubSonic.SubSonicSection, SubSonic" allowDefinition="MachineToApplication" restartOnExternalChanges="true" requirePermission="false"/>
</configSections>
<connectionStrings>
<add name ="SubSonicConn" connectionString="Data Source=.;Initial Catalog=Test;Integrated Security=true;uid=sa;password=XXXXXX;"/>
</connectionStrings>
<SubSonicService defaultProvider="SubSonicConn" enableTrace="false" templateDirectory="">
<providers>
<clear/>
<add name="SubSonicConn" type="SubSonic.SqlDataProvider, SubSonic"
connectionStringName="SubSonicConn" generatedNamespace="SubSonicConn" removeUnderscores="false" />
</providers>
</SubSonicService>
配置文件就这样，注意数据连接串的name值，包括后面项目中映射类文件的name值，一定要保持一致。
2、项目下新建Generated文件夹，保存SubSonic所需的类映射文件
其中Rolexxx相关为Role表的使用类，AllStructs.cs为主要的控制器文件，StoredProcedures.cs为存储过程相关的类。具体类代码如下：
using System; using System.Text; using System.Data; using System.Data.SqlClient; using System.Data.Common; using System.Collections; using System.Collections.Generic; using System.ComponentModel; using System.Configuration; using System.Xml; using System.Xml.Serialization; using SubSonic; using SubSonic.Utilities; // <auto-generated /> namespace ORMSubSonic.Generated { #region Tables Struct public partial struct Tables { public static readonly string Boy = @"boy"; } #endregion #region Schemas public partial class Schemas { public static TableSchema.Table Boy { get { return DataService.GetSchema("boy", "SubSonicConn"); } } } #endregion #region View Struct public partial struct Views { } #endregion #region Query Factories public static partial class DB { public static DataProvider _provider = DataService.Providers["SubSonicConn"]; static ISubSonicRepository _repository; public static ISubSonicRepository Repository { get { if (_repository == null) return new SubSonicRepository(_provider); return _repository; } set { _repository = value; } } public static Select SelectAllColumnsFrom<T>() where T : RecordBase<T>, new() { return Repository.SelectAllColumnsFrom<T>(); } public static Select Select() { return Repository.Select(); } public static Select Select(params string[] columns) { return Repository.Select(columns); } public static Select Select(params Aggregate[] aggregates) { return Repository.Select(aggregates); } public static Update Update<T>() where T : RecordBase<T>, new() { return Repository.Update<T>(); } public static Insert Insert() { return Repository.Insert(); } public static Delete Delete() { return Repository.Delete(); } public static InlineQuery Query() { return Repository.Query(); } } #endregion } #region Databases public partial struct Databases { public static readonly string SubSonicConn = @"SubSonicConn"; } #endregion
using System; using System.Text; using System.Data; using System.Data.SqlClient; using System.Data.Common; using System.Collections; using System.Collections.Generic; using System.ComponentModel; using System.Configuration; using System.Xml; using System.Xml.Serialization; using SubSonic; using SubSonic.Utilities; // <auto-generated /> namespace ORMSubSonic.Generated { /// <summary> /// Strongly-typed collection for the Role class. /// </summary> [Serializable] public partial class RoleCollection : ActiveList<Role, RoleCollection> { public RoleCollection() { } /// <summary> /// Filters an existing collection based on the set criteria. This is an in-memory filter /// Thanks to developingchris for this! /// </summary> /// <returns>RoleCollection</returns> public RoleCollection Filter() { for (int i = this.Count - 1; i > -1; i--) { Role o = this[i]; foreach (SubSonic.Where w in this.wheres) { bool remove = false; System.Reflection.PropertyInfo pi = o.GetType().GetProperty(w.ColumnName); if (pi.CanRead) { object val = pi.GetValue(o, null); switch (w.Comparison) { case SubSonic.Comparison.Equals: if (!val.Equals(w.ParameterValue)) { remove = true; } break; } } if (remove) { this.Remove(o); break; } } } return this; } } /// <summary> /// This is an ActiveRecord class which wraps the Role table. /// </summary> [Serializable] public partial class Role : ActiveRecord<Role>, IActiveRecord { #region .ctors and Default Settings public Role() { SetSQLProps(); InitSetDefaults(); MarkNew(); } private void InitSetDefaults() { SetDefaults(); } public Role(bool useDatabaseDefaults) { SetSQLProps(); if (useDatabaseDefaults) ForceDefaults(); MarkNew(); } public Role(object keyID) { SetSQLProps(); InitSetDefaults(); LoadByKey(keyID); } public Role(string columnName, object columnValue) { SetSQLProps(); InitSetDefaults(); LoadByParam(columnName, columnValue); } protected static void SetSQLProps() { GetTableSchema(); } #endregion #region Schema and Query Accessor public static Query CreateQuery() { return new Query(Schema); } public static TableSchema.Table Schema { get { if (BaseSchema == null) SetSQLProps(); return BaseSchema; } } private static void GetTableSchema() { if (!IsSchemaInitialized) { //Schema declaration TableSchema.Table schema = new TableSchema.Table("Role", TableType.Table, DataService.GetInstance("SubSonicConn")); schema.Columns = new TableSchema.TableColumnCollection(); schema.SchemaName = @"dbo"; //columns TableSchema.TableColumn colvarId = new TableSchema.TableColumn(schema); colvarId.ColumnName = "id"; colvarId.DataType = DbType.Int32; colvarId.MaxLength = 0; colvarId.AutoIncrement = true; colvarId.IsNullable = false; colvarId.IsPrimaryKey = true; colvarId.IsForeignKey = false; colvarId.IsReadOnly = false; colvarId.DefaultSetting = @""; colvarId.ForeignKeyTableName = ""; schema.Columns.Add(colvarId); TableSchema.TableColumn colvarUid = new TableSchema.TableColumn(schema); colvarUid.ColumnName = "uid"; colvarUid.DataType = DbType.Int32; colvarUid.MaxLength = 0; colvarUid.AutoIncrement = false; colvarUid.IsNullable = true; colvarUid.IsPrimaryKey = false; colvarUid.IsForeignKey = false; colvarUid.IsReadOnly = false; colvarUid.DefaultSetting = @""; colvarUid.ForeignKeyTableName = ""; schema.Columns.Add(colvarUid); TableSchema.TableColumn colvarRoleName = new TableSchema.TableColumn(schema); colvarRoleName.ColumnName = "rolename"; colvarRoleName.DataType = DbType.AnsiString; colvarRoleName.MaxLength = 250; colvarRoleName.AutoIncrement = false; colvarRoleName.IsNullable = true; colvarRoleName.IsPrimaryKey = false; colvarRoleName.IsForeignKey = false; colvarRoleName.IsReadOnly = false; colvarRoleName.DefaultSetting = @""; colvarRoleName.ForeignKeyTableName = ""; schema.Columns.Add(colvarRoleName); TableSchema.TableColumn colvarRemark = new TableSchema.TableColumn(schema); colvarRemark.ColumnName = "remark"; colvarRemark.DataType = DbType.AnsiString; colvarRemark.MaxLength = 50; colvarRemark.AutoIncrement = false; colvarRemark.IsNullable = true; colvarRemark.IsPrimaryKey = false; colvarRemark.IsForeignKey = false; colvarRemark.IsReadOnly = false; colvarRemark.DefaultSetting = @""; colvarRemark.ForeignKeyTableName = ""; schema.Columns.Add(colvarRemark); BaseSchema = schema; //add this schema to the provider //so we can query it later DataService.Providers["SubSonicConn"].AddSchema("Role", schema); } } #endregion #region Props [XmlAttribute("Id")] [Bindable(true)] public int Id { get { return GetColumnValue<int>(Columns.Id); } set { SetColumnValue(Columns.Id, value); } } [XmlAttribute("Uid")] [Bindable(true)] public int? Uid { get { return GetColumnValue<int?>(Columns.Uid); } set { SetColumnValue(Columns.Uid, value); } } [XmlAttribute("RoleName")] [Bindable(true)] public string RoleName { get { return GetColumnValue<string>(Columns.RoleName); } set { SetColumnValue(Columns.RoleName, value); } } [XmlAttribute("Remark")] [Bindable(true)] public string Remark { get { return GetColumnValue<string>(Columns.Remark); } set { SetColumnValue(Columns.Remark, value); } } #endregion //no foreign key tables defined (0) //no ManyToMany tables defined (0) #region ObjectDataSource support /// <summary> /// Inserts a record, can be used with the Object Data Source /// </summary> public static void Insert(int? varUid, string varRoleName, string varRemark) { Role item = new Role(); item.Uid = varUid; item.RoleName = varRoleName; item.Remark = varRemark; if (System.Web.HttpContext.Current != null) item.Save(System.Web.HttpContext.Current.User.Identity.Name); else item.Save(System.Threading.Thread.CurrentPrincipal.Identity.Name); } /// <summary> /// Updates a record, can be used with the Object Data Source /// </summary> public static void Update(int varId, int? varUid, string varRoleName, string varRemark) { Role item = new Role(); item.Id = varId; item.Uid = varUid; item.RoleName = varRoleName; item.Remark = varRemark; item.IsNew = false; if (System.Web.HttpContext.Current != null) item.Save(System.Web.HttpContext.Current.User.Identity.Name); else item.Save(System.Threading.Thread.CurrentPrincipal.Identity.Name); } #endregion #region Typed Columns public static TableSchema.TableColumn IdColumn { get { return Schema.Columns[0]; } } public static TableSchema.TableColumn UidColumn { get { return Schema.Columns[1]; } } public static TableSchema.TableColumn RoleNameColumn { get { return Schema.Columns[2]; } } public static TableSchema.TableColumn RemarkColumn { get { return Schema.Columns[3]; } } #endregion #region Columns Struct public struct Columns { public static string Id = @"id"; public static string Uid = @"uid"; public static string RoleName = @"rolename"; public static string Remark = @"remark"; } #endregion #region Update PK Collections #endregion #region Deep Save #endregion } }
using System; using System.Text; using System.Data; using System.Data.SqlClient; using System.Data.Common; using System.Collections; using System.Collections.Generic; using System.ComponentModel; using System.Configuration; using System.Xml; using System.Xml.Serialization; using SubSonic; using SubSonic.Utilities; // <auto-generated /> namespace ORMSubSonic.Generated { /// <summary> /// Controller class for Role /// </summary> [System.ComponentModel.DataObject] public partial class RoleController { // Preload our schema.. Role thisSchemaLoad = new Role(); private string userName = String.Empty; protected string UserName { get { if (userName.Length == 0) { if (System.Web.HttpContext.Current != null) { userName=System.Web.HttpContext.Current.User.Identity.Name; } else { userName=System.Threading.Thread.CurrentPrincipal.Identity.Name; } } return userName; } } [DataObjectMethod(DataObjectMethodType.Select, true)] public RoleCollection FetchAll() { RoleCollection coll = new RoleCollection(); Query qry = new Query(Role.Schema); coll.LoadAndCloseReader(qry.ExecuteReader()); return coll; } [DataObjectMethod(DataObjectMethodType.Select, false)] public RoleCollection FetchByID(object Id) { RoleCollection coll = new RoleCollection().Where("id", Id).Load(); return coll; } [DataObjectMethod(DataObjectMethodType.Select, false)] public RoleCollection FetchByQuery(Query qry) { RoleCollection coll = new RoleCollection(); coll.LoadAndCloseReader(qry.ExecuteReader()); return coll; } [DataObjectMethod(DataObjectMethodType.Delete, true)] public bool Delete(object Id) { return (Role.Delete(Id) == 1); } [DataObjectMethod(DataObjectMethodType.Delete, false)] public bool Destroy(object Id) { return (Role.Destroy(Id) == 1); } /// <summary> /// Inserts a record, can be used with the Object Data Source /// </summary> [DataObjectMethod(DataObjectMethodType.Insert, true)] public void Insert(int? Uid,string RoleName,string Remark) { Role item = new Role(); item.Uid = Uid; item.RoleName = RoleName; item.Remark = Remark; item.Save(UserName); } /// <summary> /// Updates a record, can be used with the Object Data Source /// </summary> [DataObjectMethod(DataObjectMethodType.Update, true)] public void Update(int Id, int? Uid, string RoleName, string Remark) { Role item = new Role(); item.MarkOld(); item.IsLoaded = true; item.Id = Id; item.Uid = Uid; item.RoleName = RoleName; item.Remark = Remark; item.Save(UserName); } } }
using System; using System.Text; using System.Data; using System.Data.SqlClient; using System.Data.Common; using System.Collections; using System.Collections.Generic; using System.ComponentModel; using System.Configuration; using System.Xml; using System.Xml.Serialization; using SubSonic; using SubSonic.Utilities; // <auto-generated /> namespace ORMSubSonic.Generated { public partial class SPs{ } }
3、实际使用
using System; using System.Collections.Generic; using System.Linq; using System.Web; using System.Web.Mvc; using SubSonic; using ORMSubSonic.Generated; using System.Data; namespace ORMSubSonic.Controllers { public class HomeController : Controller { // // GET: /Home/ public ActionResult Index() { //1.增加 Generated.Role modRole = new Generated.Role(); modRole.Uid = 6; modRole.RoleName = "Subsonic操作手"; modRole.Remark = "Subsonic操作手备注信息"; modRole.Save(); //2.删除 int result = DB.Delete().From(Role.Schema) .Where(Role.Columns.Id).IsEqualTo("6").Execute(); //3.修改 int result2 = new Update(Role.Schema) .Set(Role.Columns.RoleName).EqualTo("Subsonic操作手(修改)") .Where(Role.Columns.Id).IsEqualTo("5").Execute(); ////4.查询 //DataTable dt= DB.Select().From(Role.Schema).ExecuteDataSet().Tables[0]; DataTable dt = new Select().From(Role.Schema).ExecuteDataSet().Tables[0]; ////5.分页查询 DataTable dt2 = new Select().From(Role.Schema) .Paged(2,2,Role.Columns.Id) .OrderAsc(Role.Columns.Id) .ExecuteDataSet().Tables[0]; return View(); } } }
4、SubSonic语法
SubSonic语法有点特别，用过SubSonic的人都比较喜欢SubSonic的语法，因人而异。在这里笔者就简单的介绍下SubSonic常用的方法和关键字
4.1、常用方法
Ø ExecuteReader(); 返回DataReader
Ø ExecuteScalar(); 返回对象
Ø ExecuteScalar<string>(); 返回泛型对象
Ø ExecuteSingle<Product>(); 返回表实体对象
Ø ExecuteTypedList<Product>(); 返回泛型表实休数据集
Ø ExecuteDataSet(); 返回DataSet
Ø ExecuteJoinedDataSet<强数型数据集>(); 返回关联查询 DataSet
Ø Execute(); 返回执行后数据更新数目
4.2、常用关键字
Ø IsEqualTo(obj) // 等于 value
Ø IsBetweenAnd(obj1, obj2) // [字段1] BETWEEN 值1 AND 值2
Ø StartsWith // LIEK '1%‘
Ø EndsWith // LIEK '%1‘
Ø IsGreaterThan // [字段1] > 值1
Ø IsGreaterThanOrEqualToIsGreaterThan // [字段1] >= 值1
Ø IsLessThan // [字段1] < 值1
Ø IsLessThanOrEqualToIsLessThan // [字段1] <= 值1
Ø WhereExpression / AndExpression // Expression 表示括号
eg：
.Where("1").IsGreaterThan(1)
.And("2").IsGreaterThanOrEqualTo(2)
.AndExpression("3").IsLessThan(3)
.AndExpression("4").IsLessThanOrEqualTo(4).And("5").StartsWith("5")
.AndExpression("6").EndsWith("6")
.ExecuteSingle<Product>();
实际在SQL中执行语句则是：
where 1>1 and 2>=2 and (3<3) and (4<=4 and 5 like '5%') and (6 like '%6')
4.3、多表查询
eg：查询Product表中产品关联的种类名称,并且CategoryID大于4的记录
DataSet ds = new Select(Product.ProductNameColumn, Category.CategoryIDColumn,Category.CategoryNameColumn)
.From<Product>()
.InnerJoin(Category.CategoryIDColumn, Product.CategoryIDColumn)
.Where(Category.CategoryIDColumn) .IsGreaterThan(4)
.ExecuteDataSet().Table[0];
Select中的列则是在在DataGridView显示的列ProductName,CategoryID,CategoryName
4.4、分页查询
在标题三代码使用中已经使用到，在这里就不举例说明，具体语法就是：
SqlQuery Paged(int currentPage, int pageSize);
SqlQuery Paged(int currentPage, int pageSize, string idColumn);
