.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 - ; i > -; 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 = ;
                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 = ;
                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 = ;
                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 = ;
                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[]; }
        }

        public static TableSchema.TableColumn UidColumn
        {
            get { return Schema.Columns[]; }
        }

        public static TableSchema.TableColumn RoleNameColumn
        {
            get { return Schema.Columns[]; }
        }

        public static TableSchema.TableColumn RemarkColumn
        {
            get { return Schema.Columns[]; }
        }

        #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 == )
                {
                    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) == );
        }
        [DataObjectMethod(DataObjectMethodType.Delete, false)]
        public bool Destroy(object Id)
        {
            return (Role.Destroy(Id) == );
        }

        /// <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 = ;
            modRole.RoleName = "Subsonic操作手";
            modRole.Remark = "Subsonic操作手备注信息";
            modRole.Save();

            //2.删除
            int result = DB.Delete().From(Role.Schema)
                .Where(Role.Columns.Id).IsEqualTo("").Execute();

            //3.修改
            int result2 = new Update(Role.Schema)
                .Set(Role.Columns.RoleName).EqualTo("Subsonic操作手(修改)")
                .Where(Role.Columns.Id).IsEqualTo("").Execute();

            ////4.查询
            //DataTable dt= DB.Select().From(Role.Schema).ExecuteDataSet().Tables[0];
            DataTable dt = new Select().From(Role.Schema).ExecuteDataSet().Tables[];

            ////5.分页查询
            DataTable dt2 = new Select().From(Role.Schema)
                .Paged(,,Role.Columns.Id)
                .OrderAsc(Role.Columns.Id)
                .ExecuteDataSet().Tables[];

            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);