常用的帮助类和相关扩展函数·mark一下，用到时直接Copy
EF TO DataTable:
public static class EFExtendMethod
{
public static DataTable SqlQueryToDataTable(this Database db, string sql, CommandType type = CommandType.Text, params SqlParameter[] param)
{
DataTable ret_dt = new DataTable();
SqlConnection conn = db.Connection as SqlConnection;
if (conn == null)
{
conn = new SqlConnection(db.Connection.ConnectionString);
}
if (conn.State != ConnectionState.Open)
{
conn.Open();
}
try
{
SqlCommand cmd = new SqlCommand(sql, conn);
cmd.CommandType = type;
if (param != null && param.Length > 0)
{
foreach (SqlParameter p in param)
{
cmd.Parameters.Add(p);
}
}
SqlDataAdapter adapter = new SqlDataAdapter(cmd);
adapter.Fill(ret_dt);
conn.Close();
return ret_dt;
}
catch (Exception ex)
{
conn.Close();
return ret_dt;
}
}
public static DataTable SqlQueryToDataTable(this Database db, string sql, params SqlParameter[] param)
{
return SqlQueryToDataTable(db,sql,CommandType.Text,param);
}
}
DataTable Excel 导出：
public static class CSVFileHelper { public static string ToHtmlTable(this DataTable target) { return DataTableToHtmlTable(target); } public static string ToRootExcelFile(this DataTable target,string _fileName) { return SaveDataTableToRootExcelFile(_fileName, target); } public static string DataTableToHtmlTable(DataTable sourceData) { // var sbHtml = new StringBuilder(); sbHtml.Append("<table border='1' cellspacing='0' cellpadding='0'>"); sbHtml.Append("<tr>"); DataColumnCollection columns = sourceData.Columns; foreach (DataColumn col in columns) { sbHtml.AppendFormat("<td style='font-size: 14px;text-align:center;background-color: #DCE0E2; font-weight:bold;' height='25'>{0}</td>", col.ColumnName); } sbHtml.Append("</tr>"); foreach (DataRow row in sourceData.Rows) { sbHtml.Append("<tr>"); foreach (DataColumn col in columns) { sbHtml.AppendFormat("<td style='font-size: 12px;height:20px;'>{0}</td>", row[col]); } sbHtml.Append("</tr>"); } sbHtml.Append("</table>"); return sbHtml.ToString(); } /// <summary> /// /// </summary> /// <param name="_fileName"></param> /// <param name="sourceData"></param> /// <returns></returns> public static string SaveDataTableToRootExcelFile(string _fileName,DataTable sourceData) { string FileName = _fileName; string dir = HttpRuntime.AppDomainAppPath.ToString() + "/SaveExcelFile/"; FileName = dir + FileName; if (!Directory.Exists(dir)) { Directory.CreateDirectory(dir); } int numWriter = 1000; int numRecord = sourceData.Rows.Count; var sbHtml = new StringBuilder(); sbHtml.Append("<table border='1' cellspacing='0' cellpadding='0'>"); sbHtml.Append("<tr>"); DataColumnCollection columns = sourceData.Columns; foreach (DataColumn col in columns) { sbHtml.AppendFormat("<td style='font-size: 14px;text-align:center;background-color: #DCE0E2; font-weight:bold;' height='25'>{0}</td>", col.ColumnName); } sbHtml.Append("</tr>"); using (FileStream fs = File.Open(FileName, FileMode.CreateNew, FileAccess.Write)) { using (StreamWriter sw = new StreamWriter(fs)) { try { sw.Write(sbHtml.ToString()); sbHtml.Clear(); int i = 0; foreach (DataRow row in sourceData.Rows) { sbHtml.Append("<tr>"); foreach (DataColumn col in columns) { sbHtml.AppendFormat("<td style='font-size: 12px;height:20px;'>{0}</td>", row[col]); } sbHtml.Append("</tr>"); i++; if (i == 1000) { i = 0; sw.Write(sbHtml.ToString()); sbHtml.Clear(); } } sw.Write("</table>"); sw.Close(); fs.Close(); } catch (Exception ex) { sw.Close(); fs.Close(); } } } return FileName; } /// <summary> /// 将DataTable中数据写入到CSV文件中 /// </summary> /// <param name="dt">提供保存数据的DataTable</param> /// <param name="fullPath">CSV的文件路径</param> public static void SaveCSV(DataTable dt, string fullPath) { FileInfo fi = new FileInfo(fullPath); if (!fi.Directory.Exists) { fi.Directory.Create(); } FileStream fs = new FileStream(fullPath, System.IO.FileMode.Create, System.IO.FileAccess.Write); StreamWriter sw = new StreamWriter(fs, System.Text.Encoding.UTF8); string data = ""; //写出列名称 for (int i = 0; i < dt.Columns.Count; i++) { data += dt.Columns[i].ColumnName.ToString(); if (i < dt.Columns.Count - 1) { data += ","; } } sw.WriteLine(data); //写出各行数据 for (int i = 0; i < dt.Rows.Count; i++) { data = ""; for (int j = 0; j < dt.Columns.Count; j++) { string str = dt.Rows[i][j].ToString(); str = str.Replace(""", """");//替换英文冒号 英文冒号需要换成两个冒号 if (str.Contains(',') || str.Contains('"') || str.Contains(' ') || str.Contains(' ')) //含逗号 冒号 换行符的需要放到引号中 { str = string.Format(""{0}"", str); } data += str; if (j < dt.Columns.Count - 1) { data += ","; } } sw.WriteLine(data); } sw.Close(); fs.Close(); } /// <summary> /// 将CSV文件的数据读取到DataTable中 /// </summary> /// <param name="filePath">CSV文件路径</param> /// <returns>返回读取了CSV数据的DataTable</returns> public static DataTable OpenCSV(string filePath) { Encoding encoding = Encoding.GetEncoding("GB2312"); DataTable dt = new DataTable(); FileStream fs = new FileStream(filePath, System.IO.FileMode.Open, System.IO.FileAccess.Read); StreamReader sr = new StreamReader(fs, encoding); //记录每次读取的一行记录 string strLine = ""; //记录每行记录中的各字段内容 string[] aryLine = null; string[] tableHead = null; //标示列数 int columnCount = 0; //标示是否是读取的第一行 bool IsFirst = true; //逐行读取CSV中的数据 while ((strLine = sr.ReadLine()) != null) { if (IsFirst == true) { tableHead = strLine.Split(','); IsFirst = false; columnCount = tableHead.Length; //创建列 for (int i = 0; i < columnCount; i++) { DataColumn dc = new DataColumn(tableHead[i]); dt.Columns.Add(dc); } } else { aryLine = strLine.Split(','); DataRow dr = dt.NewRow(); for (int j = 0; j < columnCount; j++) { dr[j] = aryLine[j]; } dt.Rows.Add(dr); } } if (aryLine != null && aryLine.Length > 0) { dt.DefaultView.Sort = tableHead[0] + " " + "asc"; } sr.Close(); fs.Close(); return dt; } }
stackexchange.redis 帮助类
public class RedisExChangeHelper : IDisposable { private ConnectionMultiplexer RedisMgr = null; public IDatabase Redis { get; private set; } static string RedisIPAddress = "127.0.0.1:6379"; //默认缓存过期时间单位秒 public int secondsTimeOut = 20 * 60; /// <summary> /// /// </summary> /// <param name="RedisAddressList"></param> /// <param name="Password"></param> /// <param name="DbIndex"></param> public RedisExChangeHelper(int DbIndex = -1) { RedisMgr = ConnectionMultiplexer.Connect(RedisIPAddress); Redis = RedisMgr.GetDatabase(DbIndex); } public RedisExChangeHelper(IList<string> RedisAddressList = null, string Password = "", int DbIndex = -1) { ConfigurationOptions opt = new ConfigurationOptions { CommandMap = CommandMap.Create(new HashSet<string> { "INFO", "CONFIG", "CLUSTER", "PING", "ECHO", "CLIENT" }, available: false), KeepAlive = 120 }; if (RedisAddressList == null ) { RedisAddressList.Add("127.0.0.1:6379"); } if (!string.IsNullOrEmpty(Password)) { opt.Password = Password; } foreach (var item in RedisAddressList) { opt.EndPoints.Add(item); } RedisMgr = ConnectionMultiplexer.Connect(opt); Redis = RedisMgr.GetDatabase(DbIndex); } /// <summary> /// 切换当前DB /// </summary> /// <param name="Index"></param> /// <returns></returns> public bool SwitchDB(int Index) { IDatabase _redis = RedisMgr.GetDatabase(Index); if (_redis != null) { Redis = _redis; return true; } else { return false; } } /// <summary> /// 距离过期时间还有多少时间 /// </summary> /// <param name="key"></param> /// <returns></returns> public TimeSpan TTL(string key) { return Redis.KeyTimeToLive(key).Value; } /// <summary> /// 设置过期时间(秒) /// </summary> /// <param name="key"></param> /// <param name="timeout">秒</param> public void Expire(string key, int timeout = 0) { if (timeout >= 0) { if (timeout > 0) { secondsTimeOut = timeout; } var timespan = TTL(key).Add(new TimeSpan(0,0, timeout)); Redis.KeyExpire(key, timespan); } } /// <summary> /// 设置过期时间 /// </summary> /// <param name="key"></param> /// <param name="timeout"></param> public void Expire(string key, TimeSpan timeout) { if (timeout.TotalMilliseconds >= 0) { if (timeout.TotalMilliseconds > 0) { secondsTimeOut = (int)timeout.TotalMilliseconds; } Redis.KeyExpire(key, timeout); } } #region Key/Value存储 /// <summary> /// 添加缓存 /// </summary> /// <param name="key">缓存建</param> /// <param name="value">缓存值</param> /// <param name="timeout">过期时间，单位秒,-1：不过期，0：默认过期时间</param> /// <returns></returns> public bool Set(string key, string value, int timeout = 0) { int def_time = secondsTimeOut; if (timeout >= 0) { if (timeout > 0) { def_time = timeout; } } TimeSpan timespan = new TimeSpan(0, 0, def_time); return Redis.StringSet(key, value, timespan); } /// <summary> /// 添加缓存 /// </summary> /// <param name="key"></param> /// <param name="value"></param> /// <param name="timeout"></param> /// <returns></returns> public Task<bool> SetAsync(string key, string value, int timeout = 0) { int def_time = secondsTimeOut; if (timeout >= 0) { if (timeout > 0) { def_time = timeout; } } TimeSpan timespan = new TimeSpan(0, 0, def_time); return Redis.StringSetAsync(key, value, timespan); } /// <summary> /// 添加 /// </summary> /// <typeparam name="T"></typeparam> /// <param name="key">缓存建</param> /// <param name="t">缓存值</param> /// <param name="timeout">过期时间，单位秒,-1：不过期，0：默认过期时间</param> /// <returns></returns> public bool Set<T>(string key, T value, int timeout = 0) { int def_time = secondsTimeOut; if (timeout >= 0) { if (timeout > 0) { def_time = timeout; } } string val_json = JsonConvert.SerializeObject(value); TimeSpan timespan = new TimeSpan(0, 0, def_time); return Redis.StringSet(key,val_json, timespan); } /// <summary> /// 添加 /// </summary> /// <param name="key"></param> /// <param name="sec"></param> /// <param name="value"></param> public void SetEX(string key, int sec, string value) { SetEX<string>(key, sec, value); } /// <summary> /// 添加 /// </summary> /// <typeparam name="T"></typeparam> /// <param name="key"></param> /// <param name="sec"></param> /// <param name="value"></param> public void SetEX<T>(string key,int sec, T value) { TimeSpan timespan = new TimeSpan(0, 0, sec); var str = JsonConvert.SerializeObject(value); Redis.StringSet(key, str, timespan); } /// <summary> /// 获取缓存 /// </summary> /// <param name="key">建</param> /// <returns></returns> public string Get(string key) { string data = Redis.StringGet(key); return data; } /// <summary> /// 获取缓存 /// </summary> /// <typeparam name="T"></typeparam> /// <param name="key"></param> /// <returns></returns> public T Get<T>(string key) { string data = Redis.StringGet(key); if (data != null) { return JsonConvert.DeserializeObject<T>(data); } return default(T); } /// <summary> /// 删除 /// </summary> /// <param name="key"></param> /// <returns></returns> public bool Remove(string key) { return Redis.KeyDelete(key); } /// <summary> /// 删除 /// </summary> /// <param name="key"></param> /// <returns></returns> public Task<bool> RemoveAsync(string key) { return Redis.KeyDeleteAsync(key); } /// <summary> /// 删除 /// </summary> /// <param name="key"></param> /// <returns></returns> public bool Delete(string key) { return Redis.KeyDelete(key); } /// <summary> /// 删除 /// </summary> /// <param name="key"></param> /// <returns></returns> public Task<bool> DeleteAsync(string key) { return Redis.KeyDeleteAsync(key); } #endregion //释放资源 public void Dispose() { if (RedisMgr != null) { RedisMgr.Close(); } if (Redis != null) { Redis = null; } GC.Collect(); } }
ServiceStack.Redis 帮助类
public class RedisHelper : IDisposable { private RedisClient Redis = new RedisClient(RedisIPAddress); //缓存池 PooledRedisClientManager prcm = new PooledRedisClientManager(); static string RedisIPAddress = "127.0.0.1:6379"; static string RedisPassport = ""; static string RedisPassword = ""; /// <summary> /// 这里根据情况自己编写初始化 /// </summary> static public void Init() { RedisIPAddress = ConfigurationManager.AppSettings["RedisIPAddress"]; RedisPassport = ConfigurationManager.AppSettings["RedisPassport"]; RedisPassword = ConfigurationManager.AppSettings["RedisPassword"]; } //默认缓存过期时间单位秒 public int secondsTimeOut = 20 * 60; public RedisHelper(bool OpenPooledRedis = false) { if (OpenPooledRedis) { prcm = CreateManager(new string[] { RedisIPAddress }, new string[] { RedisIPAddress }); Redis = prcm.GetClient() as RedisClient; } } /// <summary> /// 缓冲池 /// </summary> /// <param name="readWriteHosts"></param> /// <param name="readOnlyHosts"></param> /// <returns></returns> public static PooledRedisClientManager CreateManager(string[] readWriteHosts, string[] readOnlyHosts) { return new PooledRedisClientManager(readWriteHosts, readOnlyHosts, new RedisClientManagerConfig { MaxWritePoolSize = readWriteHosts.Length * 5, MaxReadPoolSize = readOnlyHosts.Length * 5, AutoStart = true, }); } /// <summary> /// 距离过期时间还有多少秒 /// </summary> /// <param name="key"></param> /// <returns></returns> public long TTL(string key) { return Redis.Ttl(key); } /// <summary> /// 设置过期时间 /// </summary> /// <param name="key"></param> /// <param name="timeout"></param> public void Expire(string key, int timeout = 0) { if (timeout >= 0) { if (timeout > 0) { secondsTimeOut = timeout; } Redis.Expire(key, secondsTimeOut); } } #region Key/Value存储 /// <summary> /// 设置缓存 /// </summary> /// <param name="key">缓存建</param> /// <param name="value">缓存值</param> /// <param name="timeout">过期时间，单位秒,-1：不过期，0：默认过期时间</param> /// <returns></returns> public bool Set(string key, string value, int timeout = 0) { Redis.Set(key, value); if (timeout >= 0) { if (timeout > 0) { secondsTimeOut = timeout; } Redis.Expire(key, secondsTimeOut); } return true; } /// <summary> /// 设置缓存 /// </summary> /// <typeparam name="T"></typeparam> /// <param name="key">缓存建</param> /// <param name="t">缓存值</param> /// <param name="timeout">过期时间，单位秒,-1：不过期，0：默认过期时间</param> /// <returns></returns> public bool Set<T>(string key, T t, int timeout = 0) { Redis.Set<T>(key, t); if (timeout >= 0) { if (timeout > 0) { secondsTimeOut = timeout; } Redis.Expire(key, secondsTimeOut); } return true; } /// <summary> /// /// </summary> /// <param name="key"></param> /// <param name="sec"></param> /// <param name="value"></param> public void SetEX(string key, int sec, string value) { SetEX<string>(key, sec, value); } /// <summary> /// /// </summary> /// <typeparam name="T"></typeparam> /// <param name="key"></param> /// <param name="sec"></param> /// <param name="value"></param> public void SetEX<T>(string key,int sec, T value) { var str = JsonConvert.SerializeObject(value); Redis.SetEx(key, sec, Encoding.UTF8.GetBytes(str)); } /// <summary> /// 获取缓存 /// </summary> /// <param name="key">建</param> /// <returns></returns> public string Get(string key) { var data = Redis.Get(key); if (data != null) { return Encoding.UTF8.GetString(data); } return String.Empty; } /// <summary> /// 获取缓存 /// </summary> /// <typeparam name="T"></typeparam> /// <param name="key"></param> /// <returns></returns> public T Get<T>(string key) { return Redis.Get<T>(key); } /// <summary> /// 删除 /// </summary> /// <param name="key"></param> /// <returns></returns> public bool Remove(string key) { return Redis.Remove(key); } #endregion //释放资源 public void Dispose() { if (Redis != null) { Redis.Dispose(); Redis = null; } GC.Collect(); } }
netCore Session 读写Object扩展:
public static class SessionExtensions { public static void SetObject(this ISession session, string key, object value) { session.SetString(key, JsonConvert.SerializeObject(value)); } public static T GetObject<T>(this ISession session, string key) { var value = session.GetString(key); return value == null ? default(T) : JsonConvert.DeserializeObject<T>(value); } }