一、目前EF Core的版本为V2.1
相比较EF Core v1.0 目前已经增加了不少功能。
EF Core除了常用的增删改模型操作，Sql语句在不少项目中是不能避免的。
在EF Core中上下文，可以返货DbConnection ，执行sql语句。这是最底层的操作方式，代码写起来还是挺多的。
初次之外 EF Core中还支持 FromSql,ExecuteSqlCommand 连个方法，用于更方便的执行Sql语句。
另外，目前版本的EF Core 不支持SqlQuery，但是我们可以自己扩展一个。坐等升级以后支持吧。
1.FromSql，执行列表查询
public static IQueryable<TEntity> FromSql<TEntity>([NotNullAttribute] this IQueryable<TEntity> source, [NotParameterized] RawSqlString sql, [NotNullAttribute] params object[] parameters) where TEntity : class;
这种方式，仅用于当前上线文中注册的 模型对象。
对于上下文DbSet<T>中没有定义的不起作用。
示例代码1：
//执行sql查询语句 FromSql()
QLLB_SWXContext _Context = new QLLB_SWXContext();
string sql = "select * from Article where CategoryID=1;";
List<Article> list = _Context.Article.FromSql(sql).ToList();
foreach (var item in list)
{
Console.WriteLine(item.Title);
}
示例代码2：视图中的查询
---创建视图，查询没有分配角色的菜单
create view view_NoRole
as
select * from Sys_Navigation
where NavID not in (
select distinct NavID from Sys_Role_Nav
)
//查询视图
string sql2 = "select * from view_NoRole";
List<SysNavigation> roleList = _Context.SysNavigation.FromSql(sql2).ToList();
foreach (var item in roleList)
{
Console.WriteLine(item.Title);
}
2.ExecuteSqlCommand，执行Sql操作处理
QLLB_SWXContext _Context = new QLLB_SWXContext();
//执行数据操作sql,返回受影响的行数
string sql = "update Sys_Role set SortValue=1 ;";
int count = _Context.Database.ExecuteSqlCommand(sql);
Console.WriteLine(count);
3.自定义SqlQuery，执行列表查询，在上线文中不存的对象。
示例代码1:
QLLB_SWXContext _Context = new QLLB_SWXContext();
//特别说明，自定义分装的不支持 单个值查询
//不支持object 查询
//自定义查询操作 SqlQuery
string sql = "select sum(ViewCount)*1.11 as allCount from Article;";
TempData result = _Context.Database.SqlQuery<TempData>(sql).FirstOrDefault();
Console.WriteLine(result.AllCount);
对象定义
public class TempData
{
public int CategoryID { get; set; }
public string Title { get; set; }
public int ArtCount { get; set; }
/// <summary>
/// 求和结果
/// </summary>
public decimal AllCount { get; set; }
}
示例代码2：
执行视图查询：
--定义视图，文章分类和对应分类的文章数量
create view view_CateCount
as
select C.CategoryID,C.Title, (
select count(*) from Article where CategoryID=C.CategoryID
) as ArtCount from ArticleCategory C;
C#代码：
//组合查询
string sql2 = "select * from view_CateCount;";
List<TempData> tempList = _Context.Database.SqlQuery<TempData>(sql2).ToList();
foreach (var item in tempList)
{
Console.WriteLine(item.Title);
}
SqlQuery扩展定义：
using Microsoft.EntityFrameworkCore; using Microsoft.EntityFrameworkCore.Infrastructure; using System; using System.Collections.Generic; using System.Data; using System.Data.Common; using System.Data.SqlClient; using System.Linq; using System.Reflection; using System.Text; namespace Som.Common { public static class DbContextExtensions { private static void CombineParams(ref DbCommand command, params object[] parameters) { if (parameters != null) { foreach (SqlParameter parameter in parameters) { if (!parameter.ParameterName.Contains("@")) parameter.ParameterName = $"@{parameter.ParameterName}"; command.Parameters.Add(parameter); } } } private static DbCommand CreateCommand(DatabaseFacade facade, string sql, out DbConnection dbConn, params object[] parameters) { DbConnection conn = facade.GetDbConnection(); dbConn = conn; conn.Open(); DbCommand cmd = conn.CreateCommand(); if (facade.IsSqlServer()) { cmd.CommandText = sql; CombineParams(ref cmd, parameters); } return cmd; } /// <summary> /// 执行SQL返回受影响的行数 /// </summary> public static int ExecSqlNoQuery<T>(this DatabaseFacade facade, string sql, SqlParameter[] sqlParams = null) where T : new() { return facade.ExecuteNoQuery<T>( sql, sqlParams); } /// <summary> /// 执行存储过程返回IEnumerable数据集 /// </summary> public static IEnumerable<T> ExecProcReader<T>(this DatabaseFacade facade, string sql, SqlParameter[] sqlParams = null) where T : new() { return facade.Execute<T>( sql, CommandType.StoredProcedure, sqlParams); } /// <summary> /// 执行sql返回IEnumerable数据集 /// </summary> public static IEnumerable<T> ExecSqlReader<T>(this DatabaseFacade facade, string sql, SqlParameter[] sqlParams = null) where T : new() { return facade.Execute<T>(sql,CommandType.Text, sqlParams); } /// <summary> /// 执行SQL 返回受影响的行数 用于update delete 等 /// </summary> /// <typeparam name="T"></typeparam> /// <param name="facade"></param> /// <param name="sql"></param> /// <param name="sqlParams"></param> /// <returns></returns> private static int ExecuteNoQuery<T>(this DatabaseFacade facade, string sql, SqlParameter[] sqlParams) where T : new() { DbCommand cmd = CreateCommand(facade, sql, out DbConnection conn, sqlParams); int result = 0; cmd.CommandText = sql; cmd.CommandType = CommandType.Text; if (sqlParams != null) { cmd.Parameters.AddRange(sqlParams); } result = cmd.ExecuteNonQuery(); conn.Close(); return result; } private static IEnumerable<T> Execute<T>(this DatabaseFacade facade, string sql, CommandType type, SqlParameter[] parameters) where T : new() { DbCommand cmd = CreateCommand(facade, sql, out DbConnection conn, parameters); cmd.CommandText = sql; cmd.CommandType = type; if (parameters != null) { cmd.Parameters.AddRange(parameters); } DataTable dt = new DataTable(); using (DbDataReader reader = cmd.ExecuteReader()) { dt.Load(reader); } conn.Close(); return dt.ToCollection<T>(); } /// <summary> /// DataTable和集合的扩展 /// </summary> /// <typeparam name="T"></typeparam> /// <param name="dt"></param> /// <returns></returns> public static IEnumerable<T> ToCollection<T>(this DataTable dt) where T : new() { if (dt == null || dt.Rows.Count == 0) { return Enumerable.Empty<T>(); } IList<T> ts = new List<T>(); // 获得此模型的类型 Type type = typeof(T); string tempName = string.Empty; foreach (DataRow dr in dt.Rows) { T t = new T(); PropertyInfo[] propertys = t.GetType().GetProperties(); foreach (PropertyInfo pi in propertys) { tempName = pi.Name; //检查DataTable是否包含此列（列名==对象的属性名） if (dt.Columns.Contains(tempName)) { // 判断此属性是否有Setter if (!pi.CanWrite) continue;//该属性不可写，直接跳出 object value = dr[tempName]; if (value != DBNull.Value) //pi.SetValue(t, value, null); pi.SetValue(t, StrHelper.ConvertType(value, pi.PropertyType), null); } } ts.Add(t); } return ts; } /// <summary> /// 执行SQL 返回DataTable /// </summary> /// <param name="facade"></param> /// <param name="sql"></param> /// <param name="parameters"></param> /// <returns></returns> public static DataTable SqlQuery(this DatabaseFacade facade, string sql, params object[] parameters) { DbCommand cmd = CreateCommand(facade, sql, out DbConnection conn, parameters); DbDataReader reader = cmd.ExecuteReader(); DataTable dt = new DataTable(); dt.Load(reader); reader.Close(); conn.Close(); return dt; } /// <summary> /// 执行SQL 返回 IEnumerable<T> /// </summary> /// <typeparam name="T"></typeparam> /// <param name="facade"></param> /// <param name="sql"></param> /// <param name="parameters"></param> /// <returns></returns> public static IEnumerable<T> SqlQuery<T>(this DatabaseFacade facade, string sql, params object[] parameters) where T : class, new() { DataTable dt = SqlQuery(facade, sql, parameters); return dt.ToEnumerable<T>(); } public static IEnumerable<T> ToEnumerable<T>(this DataTable dt) where T : class, new() { PropertyInfo[] propertyInfos = typeof(T).GetProperties(); T[] ts = new T[dt.Rows.Count]; int i = 0; //定义一个临时变量 存放属性名称 string tempName = string.Empty; foreach (DataRow row in dt.Rows) { T t = new T(); foreach (PropertyInfo p in propertyInfos) { tempName = p.Name;//将属性名称赋值给临时变量 //检查DataTable是否包含此列（列名==对象的属性名） string tpye1 = p.PropertyType.ToString(); if (dt.Columns.IndexOf(p.Name) != -1 && row[p.Name] != DBNull.Value) { // 判断此属性是否有Setter if (!p.CanWrite) continue;//该属性不可写，直接跳出 object value = row[tempName]; p.SetValue(t, StrHelper.ConvertType(value, p.PropertyType), null); } } ts[i] = t; i++; } return ts; } } }
using System; using System.Collections.Generic; using System.ComponentModel; using System.Linq; using System.Text; using System.Text.RegularExpressions; namespace Som.Common { /// <summary> /// 字符串操作 - 工具方法 /// </summary> public static partial class StrHelper { #region Empty(空字符串) /// <summary> /// 空字符串 /// </summary> public static string Empty { get { return string.Empty; } } /// <summary> /// 判断这个Object是否为空 /// </summary> /// <param name="obj"></param> /// <returns></returns> public static bool IsEmpty(object obj) { if (obj == null) { return true; } else if (obj.ToString() == "") { return true; } return false; } /// <summary> /// 将obj转换为Type类型 /// </summary> /// <param name="obj"></param> /// <param name="t"></param> /// <returns></returns> public static object ConvertType(object obj, Type t) { if (obj == null || obj.ToString() == "") { return null; } string tpye1 = t.ToString(); if (tpye1.IndexOf("System.String") != -1) { return Convert.ToString(obj); } else if (tpye1.IndexOf("System.Int32") != -1) { if (obj.ToString().ToLower()=="true") { obj = "1"; } else if (obj.ToString().ToLower() == "false") { obj = "0"; } return Convert.ToInt32(obj); } else if (tpye1.IndexOf("System.Int64") != -1) { return Convert.ToInt64(obj); } else if (tpye1.IndexOf("System.DateTime") != -1) { if (StrHelper.IsNumber(obj.ToString())) { return Convert.ToDateTime(Convert.ToDouble(obj)); } else { return Convert.ToDateTime(obj); } } else if (tpye1.IndexOf("System.Boolean") != -1) { if (obj.ToString() == "是") { obj = true; } else if (obj.ToString() == "否") { obj = false; } return Convert.ToBoolean(obj); } else if (tpye1.IndexOf("System.Decimal") != -1) { return Convert.ToDecimal(obj); } else if (tpye1.IndexOf("System.Double") != -1) { return Convert.ToDouble(obj); } //else if (tpye1.IndexOf("System.Collections.Generic.ICollection`1") != -1) //{ // string Type = tpye1.Replace("System.Collections.Generic.ICollection`1[Model.", "").Replace("]", ""); // using (var context = new MESContext()) // { // var postTitles = context.Database.SqlQuery<string>("SELECT ID FROM dbo." + Type + "s where ID in (" + obj.ToString() + ")").ToList(); // return postTitles; // } //} return obj; } #endregion #region PinYin(获取汉字的拼音简码) /// <summary> /// 获取汉字的拼音简码，即首字母缩写,范例：中国,返回zg /// </summary> /// <param name="chineseText">汉字文本,范例： 中国</param> public static string PinYin(string chineseText) { if (string.IsNullOrWhiteSpace(chineseText)) return string.Empty; var result = new StringBuilder(); foreach (char text in chineseText) result.AppendFormat("{0}", ResolvePinYin(text)); return result.ToString().ToLower(); } /// <summary> /// 解析单个汉字的拼音简码 /// </summary> /// <param name="text">单个汉字</param> private static string ResolvePinYin(char text) { byte[] charBytes = Encoding.Default.GetBytes(text.ToString()); if (charBytes[0] <= 127) return text.ToString(); var unicode = (ushort) (charBytes[0] * 256 + charBytes[1]); string pinYin = ResolvePinYinByCode(unicode); if (!string.IsNullOrWhiteSpace(pinYin)) return pinYin; return ResolvePinYinByFile(text.ToString()); } /// <summary> /// 使用字符编码方式获取拼音简码 /// </summary> private static string ResolvePinYinByCode(ushort unicode) { if (unicode >= 'uB0A1' && unicode <= 'uB0C4') return "A"; if (unicode >= 'uB0C5' && unicode <= 'uB2C0' && unicode != 45464) return "B"; if (unicode >= 'uB2C1' && unicode <= 'uB4ED') return "C"; if (unicode >= 'uB4EE' && unicode <= 'uB6E9') return "D"; if (unicode >= 'uB6EA' && unicode <= 'uB7A1') return "E"; if (unicode >= 'uB7A2' && unicode <= 'uB8C0') return "F"; if (unicode >= 'uB8C1' && unicode <= 'uB9FD') return "G"; if (unicode >= 'uB9FE' && unicode <= 'uBBF6') return "H"; if (unicode >= 'uBBF7' && unicode <= 'uBFA5') return "J"; if (unicode >= 'uBFA6' && unicode <= 'uC0AB') return "K"; if (unicode >= 'uC0AC' && unicode <= 'uC2E7') return "L"; if (unicode >= 'uC2E8' && unicode <= 'uC4C2') return "M"; if (unicode >= 'uC4C3' && unicode <= 'uC5B5') return "N"; if (unicode >= 'uC5B6' && unicode <= 'uC5BD') return "O"; if (unicode >= 'uC5BE' && unicode <= 'uC6D9') return "P"; if (unicode >= 'uC6DA' && unicode <= 'uC8BA') return "Q"; if (unicode >= 'uC8BB' && unicode <= 'uC8F5') return "R"; if (unicode >= 'uC8F6' && unicode <= 'uCBF9') return "S"; if (unicode >= 'uCBFA' && unicode <= 'uCDD9') return "T"; if (unicode >= 'uCDDA' && unicode <= 'uCEF3') return "W"; if (unicode >= 'uCEF4' && unicode <= 'uD188') return "X"; if (unicode >= 'uD1B9' && unicode <= 'uD4D0') return "Y"; if (unicode >= 'uD4D1' && unicode <= 'uD7F9') return "Z"; return string.Empty; } /// <summary> /// 从拼音简码文件获取 /// </summary> /// <param name="text">单个汉字</param> private static string ResolvePinYinByFile(string text) { int index = Const.ChinesePinYin.IndexOf(text, StringComparison.Ordinal); if (index < 0) return string.Empty; return Const.ChinesePinYin.Substring(index + 1, 1); } #endregion #region Splice(拼接集合元素) /// <summary> /// 拼接集合元素 /// </summary> /// <typeparam name="T">集合元素类型</typeparam> /// <param name="list">集合</param> /// <param name="quotes">引号，默认不带引号，范例：单引号 "'"</param> /// <param name="separator">分隔符，默认使用逗号分隔</param> public static string Splice<T>(IEnumerable<T> list, string quotes = "", string separator = ",") { if (list == null) return string.Empty; var result = new StringBuilder(); foreach (var each in list) result.AppendFormat("{0}{1}{0}{2}", quotes, each, separator); return result.ToString().TrimEnd(separator.ToCharArray()); } #endregion #region FirstUpper(将值的首字母大写) /// <summary> /// 将值的首字母大写 /// </summary> /// <param name="value">值</param> public static string FirstUpper(string value) { string firstChar = value.Substring(0, 1).ToUpper(); return firstChar + value.Substring(1, value.Length - 1); } #endregion #region ToCamel(将字符串转成驼峰形式) /// <summary> /// 将字符串转成驼峰形式 /// </summary> /// <param name="value">原始字符串</param> public static string ToCamel(string value) { return FirstUpper(value.ToLower()); } #endregion #region ContainsChinese(是否包含中文) /// <summary> /// 是否包含中文 /// </summary> /// <param name="text">文本</param> public static bool ContainsChinese(string text) { const string pattern = "[u4e00-u9fa5]+"; return Regex.IsMatch(text, pattern); } #endregion #region ContainsNumber(是否包含数字) /// <summary> /// 是否包含数字 /// </summary> /// <param name="text">文本</param> public static bool ContainsNumber(string text) { const string pattern = "[0-9]+"; return Regex.IsMatch(text, pattern); } #endregion #region Distinct(去除重复) /// <summary> /// 去除重复 /// </summary> /// <param name="value">值，范例1："5555",返回"5",范例2："4545",返回"45"</param> public static string Distinct(string value) { var array = value.ToCharArray(); return new string(array.Distinct().ToArray()); } #endregion #region Truncate(截断字符串) /// <summary> /// 截断字符串 /// </summary> /// <param name="text">文本</param> /// <param name="length">返回长度</param> /// <param name="endCharCount">添加结束符号的个数，默认0，不添加</param> /// <param name="endChar">结束符号，默认为省略号</param> public static string Truncate(string text, int length, int endCharCount = 0, string endChar = ".") { if (string.IsNullOrWhiteSpace(text)) return string.Empty; if (text.Length < length) return text; return text.Substring(0, length) + GetEndString(endCharCount, endChar); } /// <summary> /// 获取结束字符串 /// </summary> private static string GetEndString(int endCharCount, string endChar) { StringBuilder result = new StringBuilder(); for (int i = 0; i < endCharCount; i++) result.Append(endChar); return result.ToString(); } #endregion #region Unique(获取全局唯一值) /// <summary> /// 获取全局唯一值 /// </summary> public static string Unique() { return Guid.NewGuid().ToString().Replace("-", ""); } #endregion public static bool IsNumber(string strNumber) { Regex objNotNumberPattern = new Regex("[^0-9.-]"); Regex objTwoDotPattern = new Regex("[0-9]*[.][0-9]*[.][0-9]*"); Regex objTwoMinusPattern = new Regex("[0-9]*[-][0-9]*[-][0-9]*"); String strValidRealPattern = "^([-]|[.]|[-.]|[0-9])[0-9]*[.]*[0-9]+$"; String strValidIntegerPattern = "^([-]|[0-9])[0-9]*$"; Regex objNumberPattern = new Regex("(" + strValidRealPattern + ")|(" + strValidIntegerPattern + ")"); return !objNotNumberPattern.IsMatch(strNumber) && !objTwoDotPattern.IsMatch(strNumber) && !objTwoMinusPattern.IsMatch(strNumber) && objNumberPattern.IsMatch(strNumber); } /// <summary> /// 将字符串格式化成指定的数据类型 /// </summary> /// <param name="str"></param> /// <param name="type"></param> /// <returns></returns> public static object Format(this string str, Type type) { if (String.IsNullOrEmpty(str)) return null; if (type == null) return str; if (type.IsArray) { Type elementType = type.GetElementType(); String[] strs = str.Split(new char[] {';'}); Array array = Array.CreateInstance(elementType, strs.Length); for (int i = 0, c = strs.Length; i < c; ++i) { array.SetValue(ConvertSimpleType(strs[i], elementType), i); } return array; } return ConvertSimpleType(str, type); } private static object ConvertSimpleType(object value, Type destinationType) { object returnValue; if ((value == null) || destinationType.IsInstanceOfType(value)) { return value; } string str = value as string; if ((str != null) && (str.Length == 0)) { return null; } TypeConverter converter = TypeDescriptor.GetConverter(destinationType); bool flag = converter.CanConvertFrom(value.GetType()); if (!flag) { converter = TypeDescriptor.GetConverter(value.GetType()); } if (!flag && !converter.CanConvertTo(destinationType)) { throw new InvalidOperationException("无法转换成类型：" + value.ToString() + "==>" + destinationType); } try { returnValue = flag ? converter.ConvertFrom(null, null, value) : converter.ConvertTo(null, null, value, destinationType); } catch (Exception e) { throw new InvalidOperationException("类型转换出错：" + value.ToString() + "==>" + destinationType, e); } return returnValue; } /// <summary> /// /// </summary> /// <param name="s"></param> /// <param name="d">默认值</param> /// <returns></returns> public static string ToString(this decimal? s, string d) { return s.ToString(0, d); } /// <summary> /// /// </summary> /// <param name="s"></param> /// <param name="s">小数保留位数</param> /// <param name="d">默认值</param> /// <returns></returns> public static string ToString(this decimal? s, int i, string d) { if (!s.HasValue) return d; return s.Value.ToString("f" + i); } /// <summary> /// 获取中英文混排字符串的实际长度(字节数) /// </summary> /// <param name="str">要获取长度的字符串</param> /// <returns>字符串的实际长度值（字节数）</returns> public static int GetStringLength(string str) { if (str.Equals(string.Empty)) return 0; int strlen = 0; ASCIIEncoding strData = new ASCIIEncoding(); //将字符串转换为ASCII编码的字节数字 byte[] strBytes = strData.GetBytes(str); for (int i = 0; i <= strBytes.Length - 1; i++) { if (strBytes[i] == 63) //中文都将编码为ASCII编码63,即"?"号 strlen++; strlen++; } return strlen; } /// <summary> /// 查找字符串第N次位置 /// </summary> /// <param name=""></param> /// <param name=""></param> /// <param name=""></param> /// <returns></returns> //参数：字符串，要查找的字符串值，要查找第几个该字符串值 public static int GetStrFindIndexToN(string str, string cha, int num) { var x = str.IndexOf(cha); for (var i = 0; i < num - 1; i++) //从第0位开始，NUM需要-1 { x = str.IndexOf(cha, x + 1); } if (x==-1) { x = str.Length; } return x; } } }
using Microsoft.EntityFrameworkCore;using Microsoft.EntityFrameworkCore.Infrastructure;using System;using System.Collections.Generic;using System.Data;using System.Data.Common;using System.Data.SqlClient;using System.Linq;using System.Reflection;using System.Text;
namespace Som.Common{ public static class DbContextExtensions { private static void CombineParams(ref DbCommand command, params object[] parameters) { if (parameters != null) { foreach (SqlParameter parameter in parameters) { if (!parameter.ParameterName.Contains("@")) parameter.ParameterName = $"@{parameter.ParameterName}"; command.Parameters.Add(parameter); } } }
private static DbCommand CreateCommand(DatabaseFacade facade, string sql, out DbConnection dbConn, params object[] parameters) { DbConnection conn = facade.GetDbConnection(); dbConn = conn; conn.Open(); DbCommand cmd = conn.CreateCommand(); if (facade.IsSqlServer()) { cmd.CommandText = sql; CombineParams(ref cmd, parameters); } return cmd; } /// <summary> /// 执行SQL返回受影响的行数 /// </summary> public static int ExecSqlNoQuery<T>(this DatabaseFacade facade, string sql, SqlParameter[] sqlParams = null) where T : new() { return facade.ExecuteNoQuery<T>( sql, sqlParams); } /// <summary> /// 执行存储过程返回IEnumerable数据集 /// </summary> public static IEnumerable<T> ExecProcReader<T>(this DatabaseFacade facade, string sql, SqlParameter[] sqlParams = null) where T : new() { return facade.Execute<T>( sql, CommandType.StoredProcedure, sqlParams); } /// <summary> /// 执行sql返回IEnumerable数据集 /// </summary> public static IEnumerable<T> ExecSqlReader<T>(this DatabaseFacade facade, string sql, SqlParameter[] sqlParams = null) where T : new() { return facade.Execute<T>(sql,CommandType.Text, sqlParams); } /// <summary> /// 执行SQL 返回受影响的行数 用于update delete 等 /// </summary> /// <typeparam name="T"></typeparam> /// <param name="facade"></param> /// <param name="sql"></param> /// <param name="sqlParams"></param> /// <returns></returns> private static int ExecuteNoQuery<T>(this DatabaseFacade facade, string sql, SqlParameter[] sqlParams) where T : new() { DbCommand cmd = CreateCommand(facade, sql, out DbConnection conn, sqlParams); int result = 0; cmd.CommandText = sql; cmd.CommandType = CommandType.Text; if (sqlParams != null) { cmd.Parameters.AddRange(sqlParams); } result = cmd.ExecuteNonQuery(); conn.Close(); return result; } private static IEnumerable<T> Execute<T>(this DatabaseFacade facade, string sql, CommandType type, SqlParameter[] parameters) where T : new() { DbCommand cmd = CreateCommand(facade, sql, out DbConnection conn, parameters); cmd.CommandText = sql; cmd.CommandType = type; if (parameters != null) { cmd.Parameters.AddRange(parameters); } DataTable dt = new DataTable(); using (DbDataReader reader = cmd.ExecuteReader()) { dt.Load(reader); } conn.Close(); return dt.ToCollection<T>(); } /// <summary> /// DataTable和集合的扩展 /// </summary> /// <typeparam name="T"></typeparam> /// <param name="dt"></param> /// <returns></returns> public static IEnumerable<T> ToCollection<T>(this DataTable dt) where T : new() { if (dt == null || dt.Rows.Count == 0) { return Enumerable.Empty<T>(); } IList<T> ts = new List<T>(); // 获得此模型的类型 Type type = typeof(T); string tempName = string.Empty; foreach (DataRow dr in dt.Rows) { T t = new T(); PropertyInfo[] propertys = t.GetType().GetProperties(); foreach (PropertyInfo pi in propertys) { tempName = pi.Name; //检查DataTable是否包含此列（列名==对象的属性名） if (dt.Columns.Contains(tempName)) { // 判断此属性是否有Setter if (!pi.CanWrite) continue;//该属性不可写，直接跳出 object value = dr[tempName]; if (value != DBNull.Value) //pi.SetValue(t, value, null); pi.SetValue(t, StrHelper.ConvertType(value, pi.PropertyType), null); } } ts.Add(t); } return ts; }
/// <summary> /// 执行SQL 返回DataTable /// </summary> /// <param name="facade"></param> /// <param name="sql"></param> /// <param name="parameters"></param> /// <returns></returns> public static DataTable SqlQuery(this DatabaseFacade facade, string sql, params object[] parameters) { DbCommand cmd = CreateCommand(facade, sql, out DbConnection conn, parameters); DbDataReader reader = cmd.ExecuteReader(); DataTable dt = new DataTable(); dt.Load(reader); reader.Close(); conn.Close(); return dt; } /// <summary> /// 执行SQL 返回 IEnumerable<T> /// </summary> /// <typeparam name="T"></typeparam> /// <param name="facade"></param> /// <param name="sql"></param> /// <param name="parameters"></param> /// <returns></returns> public static IEnumerable<T> SqlQuery<T>(this DatabaseFacade facade, string sql, params object[] parameters) where T : class, new() { DataTable dt = SqlQuery(facade, sql, parameters); return dt.ToEnumerable<T>(); } public static IEnumerable<T> ToEnumerable<T>(this DataTable dt) where T : class, new() { PropertyInfo[] propertyInfos = typeof(T).GetProperties(); T[] ts = new T[dt.Rows.Count]; int i = 0; //定义一个临时变量 存放属性名称 string tempName = string.Empty; foreach (DataRow row in dt.Rows) { T t = new T(); foreach (PropertyInfo p in propertyInfos) { tempName = p.Name;//将属性名称赋值给临时变量 //检查DataTable是否包含此列（列名==对象的属性名） string tpye1 = p.PropertyType.ToString(); if (dt.Columns.IndexOf(p.Name) != -1 && row[p.Name] != DBNull.Value) { // 判断此属性是否有Setter if (!p.CanWrite) continue;//该属性不可写，直接跳出 object value = row[tempName]; p.SetValue(t, StrHelper.ConvertType(value, p.PropertyType), null); } } ts[i] = t; i++; } return ts; } }}