您现在的位置：程式碼之家 >> 編程語言 > >> .NET 網頁程式設計 >> 關於.NET

NHibernate 直連 SQL 封裝：自定義結果集與執行器

編輯：關於.NET

背景與動機

用戶需自定義 SQL 查詢，NHibernate 原生 API 不便直接映射任意結果集。作者封裝了三個核心類：MySQLField（字段）、MySQLRow（行）、MySQLRst（結果集），並基于 NHibernate ISessionManager 實現 SQLHandle 執行器。

核心類設計

MySQLField：字段元數據

public class MySQLField {
 public string Name { get; set; }
 public string Value { get; set; }
 public Type FieldType { get; set; }
 public override string ToString() => Value;
 public override int GetHashCode() => Name.GetHashCode();
 }

MySQLRow：行集合，支持索引器按名取值

public class MySQLRow : List {
 private Hashtable _strField = new Hashtable();
 public new MySQLField this[string index] { get => GetField(index); set => Add(value); }
 public string GetFieldValues(string key) => _strField.ContainsKey(key) ? (string)_strField[key] : string.Empty;
 public MySQLField GetField(string key) => _strField.ContainsKey(key) ? new MySQLField { Name = key, Value = GetFieldValues(key) } : null;
 public new void Add(MySQLField item) { /* 同步 Hashtable 與 List */ }
 }

MySQLRst：結果集，提供行/字段便捷訪問

public class MySQLRst : List {
 public int RstNums { get; set; }
 public MySQLField GetRowField(int row, string key) => row >= Count ? null : this[row][key];
 public MySQLRow GetRow(int row) => row >= Count ? null : this[row];
 }

SQLHandle：執行與查詢入口

public class SQLHandle {
 protected NHibernateISessionManager sessionFactory = NHibernateISessionManager.Instance;
 public enum EQueryInfo { OK, QUERYER }
 public EQueryInfo Query(string sqlStr) => Query(sqlStr, CommandType.Text);
 public EQueryInfo Query(string sqlStr, CommandType type) {
   var trans = sessionFactory.GetSession().BeginTransaction();
   try {
     var cmd = sessionFactory.GetSession().Connection.CreateCommand();
     cmd.CommandText = sqlStr; cmd.CommandType = type;
     trans.Enlist(cmd); cmd.Prepare(); cmd.ExecuteNonQuery();
     trans.Commit(); return EQueryInfo.OK;
   } catch { trans.Rollback(); return EQueryInfo.QUERYER; }
 }
 public MySQLRst GetRst(string sqlStr) {
   var cmd = sessionFactory.GetSession().Connection.CreateCommand();
   cmd.CommandText = sqlStr;
   var rst = new MySQLRst();
   try {
     using (var rs = cmd.ExecuteReader()) {
       while (rs.Read()) {
         var row = new MySQLRow();
         for (int i=0; i           row.Add(new MySQLField { Name = rs.GetName(i), Value = rs.GetValue(i).ToString() });
         rst.Add(row);
       }
       rst.RstNums = rst.Count;
     }
   } catch (Exception e) { throw new Exception(e.ToString()); }
   finally { sessionFactory.CloseSession(); }
   return rst;
 }
 }

單元測試示例

[TestFixture] public class CommSQLTest {
 [Test] public void Select測試() { var h=new SQLHandle(); var r=h.GetRst("SELECT * FROM catalog"); ... }
 [Test] public void Query測試() { var h=new SQLHandle(); var r1=h.GetRst("SELECT * FROM catalog WHERE id=1"); h.Query("UPDATE catalog SET intro='yyy' WHERE id=1"); var r2=h.GetRst("SELECT * FROM catalog WHERE id=1"); ... }
 }