Asp.net Core的代码移植技巧，半天将SqlSugarORM转成Core
.net core中有哪些被抛弃的类
1、DataTable DataRow SqlDataAdapter DataRow DataColumn DataColumn
虽然这些类不是我ORM核心功能，但是为了保证非Core版本的ORM和Core的语法要一致方便以后升级
于是我就有了一个想法将他们复活，打造一个小巧的DataTable，让更多的老程可以方便的移植到Core。
/// <summary> /// 作者：sunkaixuan /// 创建时间：2016/7/31 /// 修改时间：- /// 说明：让.netCore支持DataTable /// </summary> public class DataTable { public DataColumnCollection Columns = new DataColumnCollection(); public DataRowCollection Rows = new DataRowCollection(); }
public class DataColumn { public DataColumn() { } public DataColumn(string columnName) { this.ColumnName = columnName; } public DataColumn(string columnName, object dataType) { this.ColumnName = columnName; this.DataType = dataType; } public string ColumnName { get; internal set; } public object DataType { get; internal set; } }
public class DataColumnCollection : IEnumerable, ICollection, IEnumerator { public DataColumn this[int thisIndex] { get { return cols[thisIndex]; } } private int index = -1; private List<DataColumn> cols; public int Count { get { if (this.cols == null) { this.cols = new List<DataColumn>(); } return this.cols.Count; } } public void Add(DataColumn col) { if (this.cols == null) { this.cols = new List<DataColumn>(); } this.cols.Add(col); } public bool IsSynchronized { get { return true; } } public object SyncRoot { get { return null; } } public object Current { get { return cols[index]; } } public void CopyTo(Array array, int index) { throw new NotImplementedException(); } // // 摘要: // 获取该集合的 System.Collections.IEnumerator。 // // 返回结果: // 该集合的 System.Collections.IEnumerator。 public IEnumerator GetEnumerator() { return (IEnumerator)this; ; } public bool MoveNext() { index++; var isNext = index < cols.Count; if (!isNext) Reset(); return isNext; } public void Reset() { index = -1; } public bool ContainsKey(string name) { if (this.cols == null) return false; return (this.cols.Any(it => it.ColumnName == name)); } }
public class DataRowCollection : IEnumerable, ICollection, IEnumerator { public DataRow this[int thisIndex] { get { return Rows[thisIndex]; } } private int index = -1; private List<DataRow> Rows = null; public int Count { get { if (this.Rows == null) { this.Rows = new List<DataRow>(); } return Rows.Count; } } public object Current { get { if (this.Rows == null) { this.Rows = new List<DataRow>(); } return Rows[index]; } } public bool IsSynchronized { get { return true; } } public object SyncRoot { get { return null; } } public void CopyTo(Array array, int index) { throw new NotImplementedException(); } // // 摘要: // 获取该集合的 System.Collections.IEnumerator。 // // 返回结果: // 该集合的 System.Collections.IEnumerator。 public IEnumerator GetEnumerator() { return (IEnumerator)this; ; } public bool MoveNext() { index++; var isNext = index < Rows.Count; if (!isNext) Reset(); return isNext; } public void Reset() { index = -1; } internal void Add(DataRow daRow) { if (Rows == null) { Rows = new List<DataRow>(); } Rows.Add(daRow); } } public class DataRow { private Dictionary<string, object> obj = new Dictionary<string, object>(); public void Add(string key, object value) { obj.Add(key, value); } public object this[string name] { get { return obj[name]; } } public object this[int index] { get { int i = 0; object reval = null; foreach (var item in obj) { if (i == index) { reval = item.Value; break; } i++; } return reval; } } public bool ContainsKey(string columnName) { if (this.obj == null) return false; return (this.obj.ContainsKey(columnName)); } } public class SqlDataAdapter { private SqlCommand command; private string sql; private SqlConnection _sqlConnection; public SqlDataAdapter(SqlCommand command) { this.command = command; } public SqlDataAdapter(string sql, SqlConnection _sqlConnection) { this.sql = sql; this._sqlConnection = _sqlConnection; } public SqlCommand SelectCommand { get { if (this.command == null) { this.command = new SqlCommand(this.sql, this._sqlConnection); } return this.command; } } public void Fill(DataTable dt) { if (dt == null) { dt = new DataTable(); } var columns = dt.Columns; var rows = dt.Rows; using (SqlDataReader dr = command.ExecuteReader()) { for (int i = 0; i < dr.FieldCount; i++) { string name = dr.GetName(i).Trim(); if (!columns.ContainsKey(name)) columns.Add(new DataColumn(name, dr.GetFieldType(i))); } while (dr.Read()) { DataRow daRow = new DataRow(); for (int i = 0; i < columns.Count; i++) { if (!daRow.ContainsKey(columns[i].ColumnName)) daRow.Add(columns[i].ColumnName, dr.GetValue(i)); } dt.Rows.Add(daRow); } } } }
using System; using System.Collections.Generic; using System.Linq; using System.Text; using System.Data.SqlClient; using System.Data; namespace SqlSugar { /// <summary> /// ** 描述：底层SQL辅助函数 /// ** 创始时间：2015-7-13 /// ** 修改时间：- /// ** 作者：sunkaixuan /// ** 使用说明： /// </summary> public class SqlHelper : IDisposable { SqlConnection _sqlConnection; SqlTransaction _tran = null; /// <summary> /// 是否清空SqlParameters /// </summary> public bool isClearParameters = true; public int CommandTimeOut = 30000; /// <summary> /// 将页面参数自动填充到SqlParameter []，无需在程序中指定，这种情况需要注意是否有重复参数 /// 例如： /// var list = db.Queryable《Student》().Where("id=@id").ToList(); /// 以前写法 /// var list = db.Queryable《Student》().Where("id=@id", new { id=Request["id"] }).ToList(); /// </summary> public bool IsGetPageParas = false; public SqlHelper(string connectionString) { _sqlConnection = new SqlConnection(connectionString); _sqlConnection.Open(); } public SqlConnection GetConnection() { return _sqlConnection; } public void BeginTran() { _tran = _sqlConnection.BeginTransaction(); } public void BeginTran(IsolationLevel iso) { _tran = _sqlConnection.BeginTransaction(iso); } public void BeginTran(string transactionName) { _tran = _sqlConnection.BeginTransaction(transactionName); } public void BeginTran(IsolationLevel iso, string transactionName) { _tran = _sqlConnection.BeginTransaction(iso, transactionName); } public void RollbackTran() { if (_tran != null) { _tran.Rollback(); _tran = null; } } public void CommitTran() { if (_tran != null) { _tran.Commit(); _tran = null; } } public string GetString(string sql, object pars) { return GetString(sql, SqlSugarTool.GetParameters(pars)); } public string GetString(string sql, params SqlParameter[] pars) { return Convert.ToString(GetScalar(sql, pars)); } public int GetInt(string sql, object pars) { return GetInt(sql, SqlSugarTool.GetParameters(pars)); } public int GetInt(string sql, params SqlParameter[] pars) { return Convert.ToInt32(GetScalar(sql, pars)); } public object GetScalar(string sql, object pars) { return GetScalar(sql, SqlSugarTool.GetParameters(pars)); } public object GetScalar(string sql, params SqlParameter[] pars) { SqlCommand sqlCommand = new SqlCommand(sql, _sqlConnection); if (_tran != null) { sqlCommand.Transaction = _tran; } sqlCommand.CommandTimeout = this.CommandTimeOut; if (pars != null) sqlCommand.Parameters.AddRange(pars); if (IsGetPageParas) { SqlSugarToolExtensions.RequestParasToSqlParameters(sqlCommand.Parameters); } object scalar = sqlCommand.ExecuteScalar(); scalar = (scalar == null ? 0 : scalar); sqlCommand.Parameters.Clear(); return scalar; } public int ExecuteCommand(string sql, object pars) { return ExecuteCommand(sql, SqlSugarTool.GetParameters(pars)); } public int ExecuteCommand(string sql, params SqlParameter[] pars) { SqlCommand sqlCommand = new SqlCommand(sql, _sqlConnection); sqlCommand.CommandTimeout = this.CommandTimeOut; if (_tran != null) { sqlCommand.Transaction = _tran; } if (pars != null) sqlCommand.Parameters.AddRange(pars); if (IsGetPageParas) { SqlSugarToolExtensions.RequestParasToSqlParameters(sqlCommand.Parameters); } int count = sqlCommand.ExecuteNonQuery(); sqlCommand.Parameters.Clear(); return count; } public SqlDataReader GetReader(string sql, object pars) { return GetReader(sql, SqlSugarTool.GetParameters(pars)); } public SqlDataReader GetReader(string sql, params SqlParameter[] pars) { SqlCommand sqlCommand = new SqlCommand(sql, _sqlConnection); sqlCommand.CommandTimeout = this.CommandTimeOut; if (_tran != null) { sqlCommand.Transaction = _tran; } if (pars != null) sqlCommand.Parameters.AddRange(pars); if (IsGetPageParas) { SqlSugarToolExtensions.RequestParasToSqlParameters(sqlCommand.Parameters); } SqlDataReader sqlDataReader = sqlCommand.ExecuteReader(); if (isClearParameters) sqlCommand.Parameters.Clear(); return sqlDataReader; } public List<T> GetList<T>(string sql, object pars) { return GetList<T>(sql, SqlSugarTool.GetParameters(pars)); } public List<T> GetList<T>(string sql, params SqlParameter[] pars) { var reval = SqlSugarTool.DataReaderToList<T>(typeof(T), GetReader(sql, pars), null); return reval; } public T GetSingle<T>(string sql, object[] pars) { return GetSingle<T>(sql, SqlSugarTool.GetParameters(pars)); } public T GetSingle<T>(string sql, params SqlParameter[] pars) { var reval = SqlSugarTool.DataReaderToList<T>(typeof(T), GetReader(sql, pars), null).Single(); return reval; } public DataTable GetDataTable(string sql, object pars) { return GetDataTable(sql, SqlSugarTool.GetParameters(pars)); } public DataTable GetDataTable(string sql, params SqlParameter[] pars) { SqlDataAdapter _sqlDataAdapter = new SqlDataAdapter(sql, _sqlConnection); _sqlDataAdapter.SelectCommand.Parameters.AddRange(pars); if (IsGetPageParas) { SqlSugarToolExtensions.RequestParasToSqlParameters(_sqlDataAdapter.SelectCommand.Parameters); } _sqlDataAdapter.SelectCommand.CommandTimeout = this.CommandTimeOut; if (_tran != null) { _sqlDataAdapter.SelectCommand.Transaction = _tran; } DataTable dt = new DataTable(); _sqlDataAdapter.Fill(dt); _sqlDataAdapter.SelectCommand.Parameters.Clear(); return dt; } public void Dispose() { if (_sqlConnection != null) { if (_sqlConnection.State != ConnectionState.Closed) { if (_tran != null) _tran.Commit(); _sqlConnection.Close(); } } } } }
2.GetType的扩展属性发生变更
例如 Type.IsEnum在Core中要写成 Type.GetTypeInfo().IsEnum
于是我将代码进行封装，让语法不变
using System; using System.Collections.Generic; using System.Linq; using System.Threading.Tasks; using System.Reflection; namespace SqlSugar { public static class TypeExtensions { public static PropertyInfo[] GetProperties(this Type type) { var reval = type.GetTypeInfo().GetProperties(); return reval; } public static PropertyInfo GetProperty(this Type type, string name) { var reval = type.GetTypeInfo().GetProperty(name); return reval; } public static FieldInfo GetField(this Type type, string name) { var reval = type.GetTypeInfo().GetField(name); return reval; } public static bool IsEnum(this Type type) { var reval = type.GetTypeInfo().IsEnum; return reval; } public static MethodInfo GetMethod(this Type type, string name) { var reval = type.GetTypeInfo().GetMethod(name); return reval; } public static MethodInfo GetMethod(this Type type, string name, Type[] types) { var reval = type.GetTypeInfo().GetMethod(name, types); return reval; } public static ConstructorInfo GetConstructor(this Type type, Type[] types) { var reval = type.GetTypeInfo().GetConstructor(types); return reval; } } }
3、System.Web类
因为SqlSugar ORM并没有用到System.Web的功能，所以基本没什么事儿
SqlSugar一款轻量级高性能ORM框架 Core版只有80K功能强大
将Demo进行了整理方便大家使用
using System; using System.Collections.Generic; using System.Linq; using System.Threading.Tasks; using SqlSugar; using SqlSugarTest.Demos; namespace SqlSugarTest { public class Program { public static void Main(string[] args) { using (SqlSugarClient db = new SqlSugarClient("server=.;uid=sa;pwd=sasa;database=SqlSugarTest")) { var dt = db.GetDataTable("select * from student where id=@id", new { id = 1 }); //设置执行的DEMO string switch_on = "EnumType"; IDemos demo = null; switch (switch_on) { //ADO.NET基本功能 case "Ado": demo = new Ado(); break; //查询 case "Select": demo = new Select(); break; //插入 case "Insert": demo = new Insert(); break; //更新 case "Update": demo = new Update(); break; //删除 case "Delete": demo = new Delete(); break; //事务 case "Tran": demo = new Tran(); break; //生成实体 case "CreateClass": demo = new CreateClass(); break; //枚举类型的支持 case "EnumType": demo = new EnumType(); break; //除了多库并行计算外的所有功能都已经移植成功更多例子请关注我的博客 } //执行DEMO demo.Init(); Console.WriteLine("执行成功请关闭窗口"); Console.ReadKey(); } } } }
目录更加简洁明了
查询：
using SqlSugar;
using System;
using System.Collections.Generic;
using System.Linq;
using System.Threading.Tasks;
namespace SqlSugarTest.Demos
{
public class Select:IDemos
{
public void Init()
{
//单表查询
QueryableDemo();
//多表查询
SqlableDemo();
//Sql查询
SqlQuery();
//新容器转换
NewSelect();
}
/// <summary>
/// 新容器转换
/// </summary>
private void NewSelect()
{
using (SqlSugarClient db = SugarDao.GetInstance())
{
var list2 = db.Queryable<Student>().Where(c => c.id < 10).Select(c => new classNew { newid = c.id, newname = c.name, xx_name = c.name }).ToList();//不支持匿名类转换,也不建议使用
var list3 = db.Queryable<Student>().Where(c => c.id < 10).Select(c => new { newid = c.id, newname = c.name, xx_name = c.name }).ToDynamic();//匿名类转换
}
}
/// <summary>
/// Sql查询
/// </summary>
private void SqlQuery()
{
using (var db = SugarDao.GetInstance())
{
//转成list
List<Student> list1 = db.SqlQuery<Student>("select * from Student");
//转成list带参
List<Student> list2 = db.SqlQuery<Student>("select * from Student where id=@id", new { id = 1 });
//转成dynamic
dynamic list3 = db.SqlQueryDynamic("select * from student");
//转成json
string list4 = db.SqlQueryJson("select * from student");
//返回int
var list5 = db.SqlQuery<int>("select top 1 id from Student").Single();
//反回键值
Dictionary<string, string> list6 = db.SqlQuery<KeyValuePair<string, string>>("select id,name from Student").ToDictionary(it => it.Key, it => it.Value);
//反回List<string[]>
var list7 = db.SqlQuery<string[]>("select top 1 id,name from Student").Single();
//存储过程
var spResult = db.SqlQuery<School>("exec sp_school @p1,@p2", new { p1 = 1, p2 = 2 });
}
}
/// <summary>
/// 多表查询
/// </summary>
private void SqlableDemo()
{
using (var db = SugarDao.GetInstance())
{
//---------Sqlable,创建多表查询---------//
//多表查询
List<School> dataList = db.Sqlable()
.From("school", "s")
.Join("student", "st", "st.id", "s.id", JoinType.INNER)
.Join("student", "st2", "st2.id", "st.id", JoinType.LEFT)
.Where("s.id>100 and s.id<@id")
.Where("1=1")//可以多个WHERE
.SelectToList<School/*新的Model我这里没有所以写的School*/>("st.*", new { id = 1 });
//多表分页
List<School> dataPageList = db.Sqlable()
.From("school", "s")
.Join("student", "st", "st.id", "s.id", JoinType.INNER)
.Join("student", "st2", "st2.id", "st.id", JoinType.LEFT)
.Where("s.id>100 and s.id<100")
.SelectToPageList<School>("st.*", "s.id", 1, 10);
//多表分页WHERE加子查询
List<School> dataPageList2 = db.Sqlable()
.From("school", "s")
.Join("student", "st", "st.id", "s.id", JoinType.INNER)
.Join("student", "st2", "st2.id", "st.id", JoinType.LEFT)
.Where("s.id>100 and s.id<100 and s.id in (select 1 )" /*这里面写子查询都可以*/)
.SelectToPageList<School>("st.*", "s.id", 1, 10);
//--------转成List Dynmaic 或者 Json-----//
//不分页
var list1 = db.Sqlable().From("student", "s").Join("school", "l", "s.sch_id", "l.id and l.id=@id", JoinType.INNER).SelectToDynamic("*", new { id = 1 });
var list2 = db.Sqlable().From("student", "s").Join("school", "l", "s.sch_id", "l.id and l.id=@id", JoinType.INNER).SelectToJson("*", new { id = 1 });
var list3 = db.Sqlable().From("student", "s").Join("school", "l", "s.sch_id", "l.id and l.id=@id", JoinType.INNER).SelectToDataTable("*", new { id = 1 });
//分页
var list4 = db.Sqlable().From("student", "s").Join("school", "l", "s.sch_id", "l.id and l.id=@id", JoinType.INNER).SelectToPageDynamic("s.*", "l.id", 1, 10, new { id = 1 });
var list5 = db.Sqlable().From("student", "s").Join("school", "l", "s.sch_id", "l.id and l.id=@id", JoinType.INNER).SelectToPageTable("s.*", "l.id", 1, 10, new { id = 1 });
var list6 = db.Sqlable().From("student", "s").Join("school", "l", "s.sch_id", "l.id and l.id=@id", JoinType.INNER).SelectToPageDynamic("s.*", "l.id", 1, 10, new { id = 1 });
//--------拼接-----//
Sqlable sable = db.Sqlable().From<Student>("s").Join<School>("l", "s.sch_id", "l.id", JoinType.INNER);
string name = "a";
int id = 1;
if (!string.IsNullOrEmpty(name))
{
sable = sable.Where("s.name=@name");
}
if (!string.IsNullOrEmpty(name))
{
sable = sable.Where("s.id=@id or s.id=100");
}
if (id > 0)
{
sable = sable.Where("l.id in (select top 10 id from school)");//where加子查询
}
var pars = new { id = id, name = name };
int pageCount = sable.Count(pars);
var list7 = sable.SelectToPageList<Student>("s.*", "l.id desc", 1, 20, pars);
}
}
/// <summary>
/// 单表查询
/// </summary>
private void QueryableDemo()
{
using (var db = SugarDao.GetInstance())
{
//---------Queryable<T>,扩展函数查询---------//
//针对单表或者视图查询
//查询所有
var student = db.Queryable<Student>().ToList();
var studentDynamic = db.Queryable<Student>().ToDynamic();
var studentJson = db.Queryable<Student>().ToJson();
//查询单条
var single = db.Queryable<Student>().Single(c => c.id == 1);
//查询单条没有记录返回空对象
var single2 = db.Queryable<Student>().Where(c => c.id == 1).SingleOrDefault();
//查询第一条
var first = db.Queryable<Student>().Where(c => c.id == 1).First();
var first2 = db.Queryable<Student>().Where(c => c.id == 1).FirstOrDefault();
//取10-20条
var page1 = db.Queryable<Student>().Where(c => c.id > 10).OrderBy("id").Skip(10).Take(20).ToList();
//上一句的简化写法，同样取10-20条
var page2 = db.Queryable<Student>().Where(c => c.id > 10).OrderBy("id").ToPageList(2, 10);
//查询条数
var count = db.Queryable<Student>().Where(c => c.id > 10).Count();
//从第2条开始以后取所有
var skip = db.Queryable<Student>().Where(c => c.id > 10).OrderBy("id").Skip(2).ToList();
//取前2条
var take = db.Queryable<Student>().Where(c => c.id > 10).OrderBy("id").Take(2).ToList();
// Not like
string conval = "a";
var notLike = db.Queryable<Student>().Where(c => !c.name.Contains(conval.ToString())).ToList();
//Like
conval = "三";
var like = db.Queryable<Student>().Where(c => c.name.Contains(conval)).ToList();
// 可以在拉姆达使用 ToString和 Convert,比EF出色的地方
var convert1 = db.Queryable<Student>().Where(c => c.name == "a".ToString()).ToList();
var convert2 = db.Queryable<Student>().Where(c => c.id == Convert.ToInt32("1")).ToList();//
var convert3 = db.Queryable<Student>().Where(c => DateTime.Now > Convert.ToDateTime("2015-1-1")).ToList();
var convert4 = db.Queryable<Student>().Where(c => DateTime.Now > DateTime.Now).ToList();
//支持字符串Where 让你解决，更复杂的查询
var student12 = db.Queryable<Student>().Where(c => "a" == "a").Where("id>100").ToList();
var student13 = db.Queryable<Student>().Where(c => "a" == "a").Where("id>100 and id in( select 1)").ToList();
//存在记录反回true，则否返回false
bool isAny100 = db.Queryable<Student>().Any(c => c.id == 100);
bool isAny1 = db.Queryable<Student>().Any(c => c.id == 1);
int maxId = db.Queryable<Student>().Max<Student, int>("id");
int minId = db.Queryable<Student>().Where(c => c.id > 0).Min<Student, int>("id");
//In
var list1 = db.Queryable<Student>().In("id", "1", "2", "3").ToList();
var list2 = db.Queryable<Student>().In("id", new string[] { "1", "2", "3" }).ToList();
var list3 = db.Queryable<Student>().In("id", new List<string> { "1", "2", "3" }).ToList();
var list4 = db.Queryable<Student>().Where(it => it.id < 10).In("id", new List<string> { "1", "2", "3" }).ToList();
//分组查询
var list5 = db.Queryable<Student>().Where(c => c.id < 20).GroupBy("sex").Select<Student, SexTotal>("Sex,Count=count(*)").ToList();
//SELECT Sex,Count=count(*) FROM Student WHERE 1=1 AND (id < 20) GROUP BY Sex --生成结果
}
}
}
}
更多用法：
http://www.cnblogs.com/sunkaixuan/p/5654695.html 除了并行计算的功能其它都移植成功
源代码：
Core版本
https://github.com/sunkaixuan/ASP_NET_CORE_ORM_SqlSugar
.net 版本
https://github.com/sunkaixuan/SqlSugar
