完整代码:
using System; using System.Collections.Specialized; using System.IO; using System.Net; using System.Text; using System.Text.RegularExpressions; using System.Collections.Generic; using System.Configuration; using System.Data; using System.Data.SqlClient; using System.Security.Cryptography.X509Certificates; using System.Net.Security; using System.Reflection; using System.ComponentModel; namespace DBUtility { public class DButility { private static Random ran = new Random(); private static readonly int TIMEOUT = 5000; #region DBNull类型转换 public static long ToInt64(object value) { return (Convert.IsDBNull(value)) ? 0 : Convert.ToInt64(value); } public static int ToInt32(object value) { return (Convert.IsDBNull(value)) ? 0 : Convert.ToInt32(value); } public static short ToInt16(object value) { return (Convert.IsDBNull(value)) ? (short)0 : Convert.ToInt16(value); } public static string ToString(object value) { return value.ToString(); } public static decimal ToDecimal(object value) { return (Convert.IsDBNull(value)) ? 0 : Convert.ToDecimal(value); } public static DateTime ToDateTime(object value) { return (Convert.IsDBNull(value)) ? DateTime.MinValue : Convert.ToDateTime(value); } #endregion #region AES 加密/解密 /// <summary> /// AES 加密 /// </summary> /// <param name="str">明文（待加密）</param> /// <param name="key">密文</param> /// <returns></returns> public static string AesEncryptToHex(string str, string key) { if (string.IsNullOrEmpty(str)) return null; Byte[] toEncryptArray = Encoding.UTF8.GetBytes(str); //命名空间： using System.Text; System.Security.Cryptography.RijndaelManaged rm = new System.Security.Cryptography.RijndaelManaged { Key = Encoding.UTF8.GetBytes(key), Mode = System.Security.Cryptography.CipherMode.ECB, Padding = System.Security.Cryptography.PaddingMode.PKCS7 }; System.Security.Cryptography.ICryptoTransform cTransform = rm.CreateEncryptor(); Byte[] resultArray = cTransform.TransformFinalBlock(toEncryptArray, 0, toEncryptArray.Length); var hex = BitConverter.ToString(resultArray, 0).Replace("-", string.Empty).ToLower(); return hex; } /// <summary> /// AES 解密 /// </summary> /// <param name="str">明文（待解密）</param> /// <param name="key">密文</param> /// <returns></returns> public static string AesDecryptFromHex(string str, string key) { if (string.IsNullOrEmpty(str)) return null; var toEncryptArray = new byte[str.Length / 2]; for (var x = 0; x < toEncryptArray.Length; x++) { var i = Convert.ToInt32(str.Substring(x * 2, 2), 16); toEncryptArray[x] = (byte)i; } //Byte[] toEncryptArray = Convert.FromBase64String(str); System.Security.Cryptography.RijndaelManaged rm = new System.Security.Cryptography.RijndaelManaged { Key = Encoding.UTF8.GetBytes(key), Mode = System.Security.Cryptography.CipherMode.ECB, Padding = System.Security.Cryptography.PaddingMode.PKCS7 }; System.Security.Cryptography.ICryptoTransform cTransform = rm.CreateDecryptor(); Byte[] resultArray = cTransform.TransformFinalBlock(toEncryptArray, 0, toEncryptArray.Length); return Encoding.UTF8.GetString(resultArray); } #endregion #region 获取时间戳，取随机数 /// <summary> /// 获取时间戳 /// </summary> /// <returns></returns> public static long GetTimeStamp() { TimeSpan ts = DateTime.UtcNow - new DateTime(1970, 1, 1, 0, 0, 0, 0); return Convert.ToInt64(ts.TotalSeconds); } /// <summary> /// 取随机数 /// </summary> /// <param name="min"></param> /// <param name="max"></param> /// <returns></returns> public static int GetRandom(int min, int max) { return ran.Next(min, max); } /// <summary> /// 获取当前本地时间戳 /// </summary> /// <returns></returns> public static long GetCurrentTimeUnix() { TimeSpan cha = (DateTime.Now - TimeZone.CurrentTimeZone.ToLocalTime(new System.DateTime(1970, 1, 1))); long t = (long)cha.TotalSeconds; return t; } /// <summary> /// 时间戳转换为本地时间对象 /// </summary> /// <returns></returns> public static DateTime GetUnixDateTime(long unix) { //long unix = 1500863191; DateTime dtStart = TimeZone.CurrentTimeZone.ToLocalTime(new DateTime(1970, 1, 1)); DateTime newTime = dtStart.AddSeconds(unix); return newTime; } /// <summary> /// Unicode转字符串 /// </summary> /// <param name="source">经过Unicode编码的字符串</param> /// <returns>正常字符串</returns> public static string UnicodeToString(string source) { return new Regex(@"\u([0-9A-F]{4})", RegexOptions.IgnoreCase | RegexOptions.Compiled).Replace( source, x => string.Empty + Convert.ToChar(Convert.ToUInt16(x.Result("$1"), 16))); } /// <summary> /// Stream流转化为字符串 /// </summary> /// <returns></returns> public static string StreamToString(Stream stream) { if (stream == null || stream.Length == 0) { return string.Empty; } StreamReader sr = new StreamReader(stream); return sr.ReadToEnd(); } /// <summary> /// RequestForm转换成String, key=value格式 /// </summary> /// <returns></returns> public static string RequestFormToString(NameValueCollection form) { if (form == null) { return null; } string strTemp = string.Empty; String[] requestItem = form.AllKeys; for (int i = 0; i < requestItem.Length; i++) { strTemp += requestItem[i] + "=" + form[requestItem[i]] + "&"; } strTemp = strTemp.TrimEnd('&'); return strTemp; } #endregion #region HttpUtils public static string HttpPost(string Url, string mobiles, string content) { string postData = string.Format("mobiles={0}&content={1}", mobiles, content); HttpWebRequest request = (HttpWebRequest)WebRequest.Create(Url); System.Net.ServicePointManager.DefaultConnectionLimit = 200; request.Method = "POST"; request.KeepAlive = false; request.ContentType = "application/x-www-form-urlencoded"; request.ContentLength = Encoding.UTF8.GetByteCount(postData); Stream myRequestStream = request.GetRequestStream(); StreamWriter myStreamWriter = new StreamWriter(myRequestStream, Encoding.GetEncoding("gb2312")); myStreamWriter.Write(postData, 0, postData.Length); myStreamWriter.Close(); HttpWebResponse response = (HttpWebResponse)request.GetResponse(); Stream myResponseStream = response.GetResponseStream(); StreamReader myStreamReader = new StreamReader(myResponseStream, Encoding.GetEncoding("utf-8")); string retString = myStreamReader.ReadToEnd(); myStreamReader.Close(); myResponseStream.Close(); response = null; request = null; return retString; } /// <summary> /// 指定Post地址使用Get 方式获取全部字符串 /// </summary> /// <param name="url">请求后台地址</param> /// <returns></returns> public static string Post(string url, Dictionary<string, string> dic) { string result = ""; HttpWebRequest req = (HttpWebRequest)WebRequest.Create(url); string mobiles = dic["mobiles"]; string content = dic["content"]; req.Method = "POST"; req.ContentType = "application/x-www-form-urlencoded"; #region 添加Post 参数 StringBuilder builder = new StringBuilder(); int i = 0; foreach (var item in dic) { if (i > 0) builder.Append("&"); builder.AppendFormat("{0}={1}", item.Key, item.Value); i++; } byte[] data = Encoding.UTF8.GetBytes(builder.ToString()); req.ContentLength = data.Length; using (Stream reqStream = req.GetRequestStream()) { reqStream.Write(data, 0, data.Length); reqStream.Close(); } #endregion HttpWebResponse resp = (HttpWebResponse)req.GetResponse(); Stream stream = resp.GetResponseStream(); //获取响应内容 using (StreamReader reader = new StreamReader(stream, Encoding.UTF8)) { result = reader.ReadToEnd(); } return result; } /// <summary> /// 指定地址使用Get 方式获取全部字符串 /// </summary> /// <param name="url">请求后台地址</param> /// <returns></returns> public static string Get(string url, Dictionary<string, string> dic) { string result = ""; HttpWebRequest req = (HttpWebRequest)WebRequest.Create(url); string uname = dic["username"]; string pwd = dic["password"]; req.Method = "GET"; req.ContentType = "application/x-www-form-urlencoded"; HttpWebResponse resp = (HttpWebResponse)req.GetResponse(); Stream stream = resp.GetResponseStream(); //获取响应内容 using (StreamReader reader = new StreamReader(stream, Encoding.UTF8)) { result = reader.ReadToEnd(); } return result; } public static string PostSend(string url, string param) { return PostSend(url, "UTF-8", param); } public static string PostSend(string url, string encoding, string param, string contentType = "application/x-www-form-urlencoded") { try { byte[] postData = Encoding.UTF8.GetBytes(param); HttpWebRequest request = WebRequest.Create(url) as HttpWebRequest; Encoding myEncoding = Encoding.UTF8; request.Method = "POST"; request.KeepAlive = false; request.AllowAutoRedirect = true; request.ContentType = contentType; request.UserAgent = "Mozilla/4.0 (compatible; MSIE 6.0; Windows NT 5.1; SV1; .NET CLR 2.0.50727; .NET CLR 3.0.04506.648; .NET CLR 3.5.21022; .NET CLR 3.0.4506.2152; .NET CLR 3.5.30729)"; request.ContentLength = postData.Length; request.Timeout = TIMEOUT; System.IO.Stream outputStream = request.GetRequestStream(); outputStream.Write(postData, 0, postData.Length); outputStream.Close(); HttpWebResponse response = request.GetResponse() as HttpWebResponse; Stream responseStream = response.GetResponseStream(); StreamReader reader = new System.IO.StreamReader(responseStream, Encoding.GetEncoding("UTF-8")); string retVal = reader.ReadToEnd(); reader.Close(); return retVal; } catch (Exception ex) { LogHelper.Error("PostSend [url:]" + url + " [params:]" + param + " [error:]" + ex.Message); return ex.Message; } } public static string PostSendWithCert(string url, string param) { return PostSendWithCert(url, "UTF-8", param); } public static string PostSendWithCert(string url, string encoding, string param, string contentType = "application/x-www-form-urlencoded") { try { string cert = System.Configuration.ConfigurationManager.AppSettings["CertPath"]; string password = System.Configuration.ConfigurationManager.AppSettings["xcxMchId"]; ServicePointManager.ServerCertificateValidationCallback = new RemoteCertificateValidationCallback(CheckValidationResult); X509Certificate2 cer = new X509Certificate2(cert, password, X509KeyStorageFlags.PersistKeySet | X509KeyStorageFlags.MachineKeySet); byte[] postData = Encoding.UTF8.GetBytes(param); HttpWebRequest request = WebRequest.Create(url) as HttpWebRequest; Encoding myEncoding = Encoding.UTF8; request.ClientCertificates.Add(cer); request.Method = "POST"; request.KeepAlive = false; request.AllowAutoRedirect = true; request.ContentType = contentType; request.UserAgent = "Mozilla/4.0 (compatible; MSIE 6.0; Windows NT 5.1; SV1; .NET CLR 2.0.50727; .NET CLR 3.0.04506.648; .NET CLR 3.5.21022; .NET CLR 3.0.4506.2152; .NET CLR 3.5.30729)"; request.ContentLength = postData.Length; request.Timeout = TIMEOUT; System.IO.Stream outputStream = request.GetRequestStream(); outputStream.Write(postData, 0, postData.Length); outputStream.Close(); HttpWebResponse response = request.GetResponse() as HttpWebResponse; Stream responseStream = response.GetResponseStream(); StreamReader reader = new System.IO.StreamReader(responseStream, Encoding.GetEncoding("UTF-8")); string retVal = reader.ReadToEnd(); reader.Close(); return retVal; } catch (Exception ex) { LogHelper.Error("PostSend [url:]" + url + " [params:]" + param + " [error:]" + ex.Message); return ex.Message; } } /// <summary> /// 发请求获取图片到本地路径 /// </summary> /// <param name="url"></param> /// <param name="param"></param> /// <param name="pathName"></param> /// <param name="contentType"></param> public static void PostSaveToFile(string url, string param, string pathName, string contentType = "application/x-www-form-urlencoded") { try { byte[] postData = Encoding.UTF8.GetBytes(param); HttpWebRequest request = WebRequest.Create(url) as HttpWebRequest; Encoding myEncoding = Encoding.UTF8; request.Method = "POST"; request.KeepAlive = false; request.AllowAutoRedirect = true; request.ContentType = contentType; request.UserAgent = "Mozilla/4.0 (compatible; MSIE 6.0; Windows NT 5.1; SV1; .NET CLR 2.0.50727; .NET CLR 3.0.04506.648; .NET CLR 3.5.21022; .NET CLR 3.0.4506.2152; .NET CLR 3.5.30729)"; request.ContentLength = postData.Length; request.Timeout = TIMEOUT; System.IO.Stream outputStream = request.GetRequestStream(); outputStream.Write(postData, 0, postData.Length); outputStream.Close(); HttpWebResponse response = request.GetResponse() as HttpWebResponse; Stream responseStream = response.GetResponseStream(); System.Drawing.Image.FromStream(responseStream).Save(pathName); } catch (Exception ex) { LogHelper.Error("PostSend [url:]" + url + " [params:]" + param + " [error:]" + ex.Message); } } public static string HttpGet(string Url, string postDataStr = "") { try { HttpWebRequest request = (HttpWebRequest)WebRequest.Create(Url + (postDataStr == "" ? "" : "?") + postDataStr); // https if (Url.StartsWith("https", StringComparison.OrdinalIgnoreCase)) { ServicePointManager.ServerCertificateValidationCallback = new RemoteCertificateValidationCallback(CheckValidationResult); request.ProtocolVersion = HttpVersion.Version10; } request.Method = "GET"; request.ContentType = "text/html;charset=UTF-8"; request.Timeout = 3000; HttpWebResponse response = (HttpWebResponse)request.GetResponse(); Stream myResponseStream = response.GetResponseStream(); StreamReader myStreamReader = new StreamReader(myResponseStream, Encoding.GetEncoding("utf-8")); string retString = myStreamReader.ReadToEnd(); myStreamReader.Close(); myResponseStream.Close(); return retString; } catch (Exception e) { Console.WriteLine(e.Message); return null; } } private static bool CheckValidationResult(object sender, X509Certificate certificate, X509Chain chain, SslPolicyErrors errors) { return true; //总是接受 } #endregion #region MD5加密 /// <summary> /// /// </summary> /// <param name="sDataIn"></param> /// <param name="move">给空即可</param> /// <returns></returns> public static string GetMD532(string sDataIn, string move) { System.Security.Cryptography.MD5CryptoServiceProvider md5 = new System.Security.Cryptography.MD5CryptoServiceProvider(); byte[] bytValue, bytHash; bytValue = System.Text.Encoding.UTF8.GetBytes(move + sDataIn); bytHash = md5.ComputeHash(bytValue); md5.Clear(); string sTemp = ""; for (int i = 0; i < bytHash.Length; i++) { sTemp += bytHash[i].ToString("x").PadLeft(2, '0'); } return sTemp; } public static string GetMD516(string ConvertString) { System.Security.Cryptography.MD5CryptoServiceProvider md5 = new System.Security.Cryptography.MD5CryptoServiceProvider(); string t2 = BitConverter.ToString(md5.ComputeHash(UTF8Encoding.Default.GetBytes(ConvertString)), 4, 8); t2 = t2.Replace("-", ""); t2 = t2.ToLower(); return t2; } #endregion } #region log4net 日志类 public class LogHelper { //在 <configuration></configuration>里面添加下面配置 // <configSections> // <section name="log4net" type="log4net.Config.Log4NetConfigurationSectionHandler,log4net"/> //</configSections> // <log4net> // <logger name="logerror"> // <level value="ERROR"/> // <appender-ref ref="ErrorAppender"/> // </logger> // <logger name="loginfo"> // <level value="INFO"/> // <appender-ref ref="InfoAppender"/> // </logger> // <appender name="ErrorAppender" type="log4net.Appender.RollingFileAppender"> // <param name="File" value="LogError"/> // <param name="AppendToFile" value="true"/> // <param name="CountDirection" value="-1"/> // <param name="MaxSizeRollBackups" value="15"/> // <param name="MaximumFileSize" value="5MB"/> // <param name="lockingModel" type="log4net.Appender.FileAppender+MinimalLock"/> // <rollingStyle value="Date"/> // <datePattern value="yyyyMMdd'.txt'"/> // <staticLogFileName value="false"/> // <layout type="log4net.Layout.PatternLayout"> // <conversionPattern value="%d [%t]%c %m%n"/> // </layout> // </appender> // <appender name="InfoAppender" type="log4net.Appender.RollingFileAppender"> // <param name="File" value="LogInfo"/> // <param name="AppendToFile" value="true"/> // <param name="CountDirection" value="-1"/> // <param name="MaxSizeRollBackups" value="15"/> // <param name="MaximumFileSize" value="5MB"/> // <param name="lockingModel" type="log4net.Appender.FileAppender+MinimalLock"/> // <rollingStyle value="Date"/> // <datePattern value="yyyyMMdd'.txt'"/> // <staticLogFileName value="false"/> // <layout type="log4net.Layout.PatternLayout"> // <conversionPattern value="%d [%t]%c %m%n"/> // </layout> // </appender> //</log4net> private static readonly log4net.ILog ILogInfo = log4net.LogManager.GetLogger("loginfo"); //添加log4net 引用 public static readonly log4net.ILog ILogError = log4net.LogManager.GetLogger("logerror"); public static void Info(string msg) { ILogInfo.Info(msg); } public static void Error(string msg) { ILogError.Error(msg); } /// <summary> /// 废弃 改用FileNameError /// </summary> /// <param name="type"></param> /// <param name="msg"></param> public static void TypeError(string type, string msg) { // eg: type=Order, msg=数据库更新失败, OrderId:111222333 // msg:[Order] 数据库更新失败, OrderId:111222333 ILogError.Error("[" + type + "] " + msg); } public static void FileNameInfo(string fileName, string msg) { ILogInfo.Info(fileName + " " + msg); } public static void FileNameError(string fileName, string msg) { ILogError.Error(fileName + " " + msg); } } #endregion #region DataTable,DataSet,list集合 互转 public class ModelHandler<T> where T : new() { #region DataSet=>List /// <summary> /// 填充对象列表：用DataSet的第一个表填充实体类 /// </summary> /// <param name="ds">DataSet</param> /// <returns></returns> public List<T> FillModelByDataSet(DataSet ds) { if (ds == null || ds.Tables[0] == null || ds.Tables[0].Rows.Count == 0) { return null; } else { return FillModelByDataTable(ds.Tables[0]); } } #endregion #region DataTable=>List #region /// <summary> /// 类型枚举 /// </summary> private enum ModelType { //值类型 Struct, Enum, //引用类型 String, Object, Else } private static ModelType GetModelType(Type modelType) { //值类型 if (modelType.IsEnum) { return ModelType.Enum; } //值类型 if (modelType.IsValueType) { return ModelType.Struct; } //引用类型 特殊类型处理 if (modelType == typeof(string)) { return ModelType.String; } //引用类型 特殊类型处理 return modelType == typeof(object) ? ModelType.Object : ModelType.Else; } #endregion /// <summary> /// 填充对象列表：用DataTable填充实体类 /// </summary> public List<T> FillModelByDataTable(DataTable dt) { if (dt == null || dt.Rows.Count == 0) { return null; } List<T> modelList = new List<T>(); foreach (DataRow dr in dt.Rows) { //T model = (T)Activator.CreateInstance(typeof(T)); T model = new T(); for (int i = 0; i < dr.Table.Columns.Count; i++) { PropertyInfo propertyInfo = model.GetType().GetProperty(dr.Table.Columns[i].ColumnName); if (propertyInfo != null && dr[i] != DBNull.Value) propertyInfo.SetValue(model, dr[i], null); } modelList.Add(model); } return modelList; } /// <summary> /// datatable转换为List<T>集合 /// </summary> /// <typeparam name="T"></typeparam> /// <param name="table"></param> /// <returns></returns> public static List<T> DataTableToList<T>(DataTable table) { var list = new List<T>(); foreach (DataRow item in table.Rows) { list.Add(DataRowToModel<T>(item)); } return list; } #endregion #region DataRow=>Model /// <summary> /// 填充对象：用DataRow填充实体类 /// </summary> public T FillModelByDataRow(DataRow dr) { if (dr == null) { return default(T); } //T model = (T)Activator.CreateInstance(typeof(T)); T model = new T(); for (int i = 0; i < dr.Table.Columns.Count; i++) { PropertyInfo propertyInfo = model.GetType().GetProperty(dr.Table.Columns[i].ColumnName); if (propertyInfo != null && dr[i] != DBNull.Value) propertyInfo.SetValue(model, dr[i], null); } return model; } public static T DataRowToModel<T>(DataRow row) { T model; var type = typeof(T); var modelType = GetModelType(type); switch (modelType) { //值类型 case ModelType.Struct: { model = default(T); if (row[0] != null) model = (T)row[0]; } break; //值类型 case ModelType.Enum: { model = default(T); if (row[0] != null) { var fiType = row[0].GetType(); if (fiType == typeof(int)) { model = (T)row[0]; } else if (fiType == typeof(string)) { model = (T)Enum.Parse(typeof(T), row[0].ToString()); } } } break; //引用类型 c#对string也当做值类型处理 case ModelType.String: { model = default(T); if (row[0] != null) model = (T)row[0]; } break; //引用类型 直接返回第一行第一列的值 case ModelType.Object: { model = default(T); if (row[0] != null) model = (T)row[0]; } break; //引用类型 case ModelType.Else: { //引用类型 必须对泛型实例化 model = Activator.CreateInstance<T>(); //获取model中的属性 var modelPropertyInfos = type.GetProperties(); //遍历model每一个属性并赋值DataRow对应的列 foreach (var pi in modelPropertyInfos) { //获取属性名称 var name = pi.Name; if (!row.Table.Columns.Contains(name) || row[name] == null || row[name] == DBNull.Value) continue; var piType = GetModelType(pi.PropertyType); switch (piType) { case ModelType.Struct: { object value; if (!pi.PropertyType.Name.ToLower().Contains("nullable")) value = Convert.ChangeType(row[name], pi.PropertyType); else value = new NullableConverter(pi.PropertyType).ConvertFromString(row[name].ToString()); pi.SetValue(model, value, null); } break; case ModelType.Enum: { var fiType = row[0].GetType(); if (fiType == typeof(int)) { pi.SetValue(model, row[name], null); } else if (fiType == typeof(string)) { var value = (T)Enum.Parse(typeof(T), row[name].ToString()); if (value != null) pi.SetValue(model, value, null); } } break; case ModelType.String: { var value = Convert.ChangeType(row[name], pi.PropertyType); pi.SetValue(model, value, null); } break; case ModelType.Object: { pi.SetValue(model, row[name], null); } break; case ModelType.Else: throw new Exception("不支持该类型转换"); default: throw new Exception("未知类型"); } } } break; default: model = default(T); break; } return model; } #endregion } #endregion #region SqlHelper public class SqlHelper { /// <summary> /// 从配置文件中读取数据库连接字符串 /// </summary> public static string ConnectionString = ConfigurationManager.ConnectionStrings["constr"].ConnectionString; private static SqlConnection conn; #region 公共静态方法 /// <summary> /// 填充DataTable /// </summary> /// <param name="spname">存储过程名</param> /// <param name="sqlParams">参数集</param> /// <returns>结果datatable</returns> public static DataTable FillDataTable(string spname, params SqlParameter[] sqlParams) { conn = new SqlConnection(ConnectionString); using (SqlDataAdapter adapter = new SqlDataAdapter(spname, conn)) { adapter.SelectCommand.CommandType = CommandType.StoredProcedure; if (sqlParams != null && sqlParams.Length > 0) { for (int i = 0; i < sqlParams.Length; i++) { adapter.SelectCommand.Parameters.Add(sqlParams[i]); } } DataTable table = new DataTable(); adapter.Fill(table); return table; } } /// <summary> /// 执行一条SQL的select语句，用返回的结果填充DataTable /// </summary> /// <param name="sql">要执行的SQL语句</param> /// <param name="oParams">SQL语句中的参数</param> /// <returns>已填充数据的DataTable</returns> public static DataTable FillSqlDataTable(string sql, params SqlParameter[] oParams) { using (conn = new SqlConnection(ConnectionString)) { SqlDataAdapter adapter = new SqlDataAdapter(sql, conn); if (oParams != null && oParams.Length > 0) { foreach (SqlParameter prm in oParams) { adapter.SelectCommand.Parameters.Add(prm); } } DataTable table = new DataTable(); adapter.Fill(table); return table; } } /// <summary> /// 执行一条存储过程，返回SqlDataReader /// </summary> /// <param name="spname">存储过程名称</param> /// <param name="sqlParams">存储过程参数集合</param> /// <returns>SqlDataReader</returns> public static SqlDataReader ExecuteReader(string spname, params SqlParameter[] sqlParams) { return ExecuteReader(CommandType.StoredProcedure, spname, sqlParams); } /// <summary> /// 执行一条存储过程或SQL语句，返回SqlDataReader /// </summary> /// <param name="cmdText">SQL语句或存储过程名称</param> /// <param name="sqlParams">SQL参数集合</param> /// <returns>SqlDataReader</returns> public static SqlDataReader ExecuteReader(CommandType cmdType, string cmdText, params SqlParameter[] sqlParams) { conn = new SqlConnection(ConnectionString); using (SqlCommand cmd = new SqlCommand(cmdText, conn)) { cmd.CommandType = cmdType; if (sqlParams != null && sqlParams.Length > 0) { for (int i = 0; i < sqlParams.Length; i++) { cmd.Parameters.Add(sqlParams[i]); } } try { cmd.Connection.Open(); return cmd.ExecuteReader(CommandBehavior.CloseConnection); } catch { cmd.Connection.Close(); throw; } } } /// <summary> /// 执行存储过程 /// </summary> /// <param name="spname">存储过程名称</param> /// <param name="sqlParams">存储过程参数集合</param> /// <returns>受影响的记录行数</returns> public static int ExecuteNonQuery(string spname, params SqlParameter[] sqlParams) { return ExecuteNonQuery(CommandType.StoredProcedure, spname, sqlParams); } /// <summary> /// 执行一条SQL语句，获取受SQL语句中delete、update和insert语句影响的行数 /// </summary> /// <param name="sql">要执行的SQL语句</param> /// <param name="oParams">SQL语句中的参数</param> /// <returns>受SQL语句中delete、update和insert语句影响的行数</returns> public static int ExecuteSqlNonQuery(string sql, params SqlParameter[] oParams) { using (conn = new SqlConnection(ConnectionString)) { SqlCommand cmd = new SqlCommand(sql, conn); conn.Open(); if (oParams != null && oParams.Length > 0) { foreach (SqlParameter prm in oParams) { cmd.Parameters.Add(prm); } } int reslt = cmd.ExecuteNonQuery(); conn.Close(); return reslt; } } /// <summary> /// 执行SQL语句或存储过程 /// </summary> /// <param name="cmdType">执行类型：SQL语句或存储过程</param> /// <param name="cmdText">要执行的SQL语句或存储过程名</param> /// <param name="sqlParams">SQL参数集合</param> /// <returns>受影响的记录行数</returns> public static int ExecuteNonQuery(CommandType cmdType, string cmdText, params SqlParameter[] sqlParams) { conn = new SqlConnection(ConnectionString); using (SqlCommand cmd = new SqlCommand(cmdText, conn)) { cmd.CommandType = cmdType; if (sqlParams != null && sqlParams.Length > 0) { for (int i = 0; i < sqlParams.Length; i++) { cmd.Parameters.Add(sqlParams[i]); } } conn.Open(); int j = cmd.ExecuteNonQuery(); conn.Close(); return j; } } /// <summary> /// 执行SQL语句或存储过程返回第一行第一列的数据 /// </summary> /// <param name="cmdType">执行类型：SQL语句或存储过程</param> /// <param name="sqlParams">参数集</param> /// <param name="cmdText">执行语句</param> /// <returns>返回第一行第一列的数据</returns> public static object ExecuteScalar(CommandType cmdType, string cmdText, params SqlParameter[] sqlParams) { conn = new SqlConnection(ConnectionString); using (SqlCommand cmd = new SqlCommand(cmdText, conn)) { cmd.CommandType = cmdType; if (sqlParams != null && sqlParams.Length > 0) { for (int i = 0; i < sqlParams.Length; i++) { cmd.Parameters.Add(sqlParams[i]); } } conn.Open(); object obj = null; try { obj = cmd.ExecuteScalar(); } catch { conn.Close(); conn.Open(); obj = cmd.ExecuteScalar(); } conn.Close(); return obj; } } /// <summary> /// 执行存储过程，填充DataRow，若无数据则返回null /// </summary> /// <param name="spname">执行语句</param> /// <param name="sqlParams">参数集</param> /// <returns>返回DataRow</returns> public static DataRow FillDataRow(string spname, params SqlParameter[] sqlParams) { DataTable table = FillDataTable(spname, sqlParams); if (table.Rows.Count > 0) return table.Rows[0]; return null; } /// <summary> /// 向数据库中插入或更新数据时，设置存储过程参数 /// </summary> /// <param name="pName">存储过程参数名</param> /// <param name="pValue">参数值</param> /// <returns>返回SqlParameter</returns> public static SqlParameter SetParam(string pName, object pValue) { //如果pValue为null，则直接返回 2004-12-6 if (pValue == null) return new SqlParameter(pName, pValue); switch (Type.GetTypeCode(pValue.GetType())) { case TypeCode.String: string tempStr = (string)pValue; if (tempStr.Equals(null) || tempStr.Trim().Length.Equals(0)) { return new SqlParameter(pName, DBNull.Value); } return new SqlParameter(pName, tempStr); case TypeCode.DateTime: DateTime tempdt = (DateTime)pValue; if (tempdt.Equals(DateTime.MinValue)) { return new SqlParameter(pName, DBNull.Value); } return new SqlParameter(pName, tempdt); default: return new SqlParameter(pName, pValue); } } /// <summary> /// 获取SqlDataReader中指定字符串字段的值 /// </summary> /// <param name="reader">包含数据的SqlDataReader</param> /// <param name="fieldName">字段名</param> /// <returns>字段值，若字段为空则返回空字符串</returns> public static string GetStringFieldValue(SqlDataReader reader, string fieldName) { if (Convert.IsDBNull(reader[fieldName])) { return String.Empty; } return reader[fieldName].ToString(); } /// <summary> /// 获取SqlDataReader中指定Int字段的值，若该字段为空则返回指定的替换值 /// </summary> /// <param name="reader">包含数据的SqlDataReader</param> /// <param name="fieldName">字段名</param> /// <param name="replaceValue">要替换值为空的字段的值</param> /// <returns>字段值，若该字段为空则返回replaceValue</returns> public static int GetIntFieldValue(SqlDataReader reader, string fieldName, int replaceValue) { if (Convert.IsDBNull(reader[fieldName])) { return replaceValue; } object obj = reader[fieldName]; return Convert.ToInt32(reader[fieldName]); } /// <summary> /// 获取SqlDataReader中指定Int字段的值 /// </summary> /// <param name="reader">包含数据的SqlDataReader</param> /// <param name="fieldName">字段名</param> /// <returns>字段值，若该字段为空则返回0</returns> public static int GetIntFieldValue(SqlDataReader reader, string fieldName) { return GetIntFieldValue(reader, fieldName, 0); } /// <summary> /// 获取SqlDataReader中指定byte字段（SQL Server中为tinyint类型）的值 /// </summary> /// <param name="reader">包含数据的SqlDataReader</param> /// <param name="fieldName">要取值的字段名</param> /// <returns>返回byte类型结果</returns> public static byte GetByteFieldValue(SqlDataReader reader, string fieldName) { if (Convert.IsDBNull(reader[fieldName])) return 0; return (byte)reader[fieldName]; } /// <summary> /// 获取SqlDataReader中指定decimal字段的值，若该字段为空则返回指定的替换值 /// </summary> /// <param name="reader">包含数据的SqlDataReader</param> /// <param name="fieldName">要取值的字段名</param> /// <param name="replaceValue">要替换值为空的字段的值</param> /// <returns>返回double类型结果</returns> public static Double GetDoubleFieldValue(SqlDataReader reader, string fieldName, Double replaceValue) { if (Convert.IsDBNull(reader[fieldName])) return replaceValue; return (Double)reader[fieldName]; } /// <summary> /// 获取SqlDataReader中指定Int字段的值，若该字段为空则返回0 /// </summary> /// <param name="reader">包含数据的SqlDataReader</param> /// <param name="fieldName">要取值的字段名</param> /// <returns>返回double类型结果</returns> public static Double GetDoubleFieldValue(SqlDataReader reader, string fieldName) { return GetDoubleFieldValue(reader, fieldName, 0); } /// <summary> /// 获取SqlDataReader中指定float字段的值，若该字段为空则返回0 /// </summary> /// <param name="reader">包含数据的SqlDataReader</param> /// <param name="fieldName">要取值的字段名</param> /// <returns>返回double类型结果</returns> public static float GetFloatFieldValue(SqlDataReader reader, string fieldName) { return GetFloatFieldValue(reader, fieldName, 0); } /// <summary> /// 获取SqlDataReader中指定decimal字段的值，若该字段为空则返回指定的替换值 /// </summary> /// <param name="reader">包含数据的SqlDataReader</param> /// <param name="fieldName">要取值的字段名</param> /// <param name="replaceValue">要替换值为空的字段的值</param> /// <returns>返回double类型结果</returns> public static float GetFloatFieldValue(SqlDataReader reader, string fieldName, float replaceValue) { if (Convert.IsDBNull(reader[fieldName])) return replaceValue; return float.Parse(reader[fieldName].ToString()); } /// <summary> /// 获取SqlDataReader中指定Int字段的值，若该字段为空则返回DateTime的最小值 /// </summary> /// <param name="reader">包含数据的SqlDataReader</param> /// <param name="fieldName">要取值的字段名</param> /// <returns>返回DateTime类型结果</returns> public static DateTime GetDateTimeFieldValue(SqlDataReader reader, string fieldName) { if (Convert.IsDBNull(reader[fieldName])) return DateTime.MinValue; return (DateTime)reader[fieldName]; } #region 仅用于综合查询 /// <summary> /// 执行查询语句，返回DataSet(zhaibl 09-11-04添加此方法) /// </summary> /// <param name="SQLString">查询语句</param> /// <returns>DataSet</returns> public static DataSet Query(string SQLString) { using (SqlConnection connection = new SqlConnection(ConnectionString)) { DataSet ds = new DataSet(); try { connection.Open(); SqlDataAdapter command = new SqlDataAdapter(SQLString, connection); command.Fill(ds, "ds"); } catch (System.Data.SqlClient.SqlException ex) { throw new Exception(ex.Message); } return ds; } } /// <summary> /// 执行一条计算查询结果语句，返回查询结果（object）。 /// </summary> /// <param name="SQLString">计算查询结果语句</param> /// <returns>查询结果（object）</returns> public static object GetSingle(string SQLString) { using (SqlConnection connection = new SqlConnection(ConnectionString)) { using (SqlCommand cmd = new SqlCommand(SQLString, connection)) { try { connection.Open(); object obj = cmd.ExecuteScalar(); if ((Object.Equals(obj, null)) || (Object.Equals(obj, System.DBNull.Value))) { return null; } else { return obj; } } catch (System.Data.SqlClient.SqlException e) { connection.Close(); throw new Exception(e.Message); } } } } /// <summary> /// 填充数据集(执行带参的sql),返回DataSet /// </summary> /// <param name="sql">sql语句</param> /// <param name="oParams">参数集</param> /// <returns>datasat</returns> public static DataSet FillDataSet(string sql, params SqlParameter[] oParams) { using (SqlConnection conn = new SqlConnection(ConnectionString)) { SqlDataAdapter adapter = new SqlDataAdapter(sql, conn); if (oParams != null && oParams.Length > 0) { foreach (SqlParameter prm in oParams) { adapter.SelectCommand.Parameters.Add(prm); } } DataSet ds = new DataSet(); adapter.Fill(ds); return ds; } } /// <summary> /// 执行一条SQL语句，获取受SQL语句中delete、update和insert语句影响的行数 /// </summary> /// <param name="sql">要执行的SQL语句</param> /// <param name="oParams">SQL语句中的参数</param> /// <returns>受SQL语句中delete、update和insert语句影响的行数</returns> public static int ExecuteSqlNonQuery1(string sql, params SqlParameter[] oParams) { int reslt = 0; try { using (conn = new SqlConnection(ConnectionString)) { SqlCommand cmd = new SqlCommand(sql, conn); conn.Open(); cmd.CommandType = CommandType.StoredProcedure; cmd.Parameters.AddRange(oParams); reslt = cmd.ExecuteNonQuery(); conn.Close(); } } catch (Exception e) { throw new Exception(e.Message); } return reslt; } /// <summary> /// 获取查询结果 /// </summary> /// <param name="sql">要执行的SQL语句</param> /// <returns>DataTable</returns> public static DataTable GetQueryResult(string sql) { DataTable tbl = new DataTable(); conn = new SqlConnection(ConnectionString); using (SqlDataAdapter adapter = new SqlDataAdapter(sql, conn)) { adapter.Fill(tbl); } conn.Close(); return tbl; } #endregion 仅用于综合查询 #endregion 公共静态方法 #region 事务--Bao--2017-08-17 /// <summary> /// 创建一个事务 /// </summary> /// <returns></returns> public static SqlTransaction CreateTrans() { conn = new SqlConnection(ConnectionString); conn.Open(); return conn.BeginTransaction(); } /// <summary> /// 关闭并释放与事务有关的资源 /// </summary> /// <param name="trans"></param> public static void CloseTrans(SqlTransaction trans) { if (trans == null) return; trans.Dispose(); if (conn == null || conn.State != ConnectionState.Open) return; conn.Close(); conn.Dispose(); } /// <summary> /// 执行一条不返回结果的SqlCommand，通过一个已经存在的数据库事物处理 /// 使用参数数组提供参数 /// </summary> /// <param name="trans">一个存在的 sql 事物处理</param> /// <param name="commandType">SqlCommand命令类型 (存储过程， T-SQL语句， 等等。)</param> /// <param name="commandText">存储过程的名字或者 T-SQL 语句</param> /// <param name="sqlParams">以数组形式提供SqlCommand命令中用到的参数列表</param> /// <returns>返回一个数值表示此SqlCommand命令执行后影响的行数</returns> public static int ExecuteNonQuery(SqlTransaction trans, CommandType cmdType, string cmdText, params SqlParameter[] sqlParams) { if (trans == null) return ExecuteNonQuery(cmdType, cmdText, sqlParams); using (SqlCommand cmd = new SqlCommand()) { PrepareCommand(cmd, trans.Connection, trans, cmdType, cmdText, sqlParams); int val = cmd.ExecuteNonQuery(); cmd.Parameters.Clear(); return val; } } /// <summary> /// 执行SQL语句或存储过程返回第一行第一列的数据 /// </summary> /// <param name="trans">一个存在的 sql 事物处理</param> /// <param name="cmdType">执行类型：SQL语句或存储过程</param> /// <param name="sqlParams">参数集</param> /// <param name="cmdText">执行语句</param> /// <returns>返回第一行第一列的数据</returns> public static object ExecuteScalar(SqlTransaction trans, CommandType cmdType, string cmdText, params SqlParameter[] sqlParams) { if (trans == null) return ExecuteScalar(cmdType, cmdText, sqlParams); using (SqlCommand cmd = new SqlCommand()) { PrepareCommand(cmd, trans.Connection, trans, cmdType, cmdText, sqlParams); object val = cmd.ExecuteScalar(); cmd.Parameters.Clear(); return val; } } /// <summary> /// 为执行命令准备参数 /// </summary> /// <param name="cmd">SqlCommand 命令</param> /// <param name="conn">已经存在的数据库连接</param> /// <param name="trans">数据库事物处理</param> /// <param name="cmdType">SqlCommand命令类型 (存储过程， T-SQL语句， 等等。)</param> /// <param name="cmdText">Command text，T-SQL语句 例如 Select * from Products</param> /// <param name="cmdParms">返回带参数的命令</param> private static void PrepareCommand(SqlCommand cmd, SqlConnection conn, SqlTransaction trans, CommandType cmdType, string cmdText, SqlParameter[] cmdParms) { //判断数据库连接状态 if (conn.State != ConnectionState.Open) conn.Open(); cmd.Connection = conn; cmd.CommandText = cmdText; //判断是否需要事物处理 if (trans != null) cmd.Transaction = trans; cmd.CommandType = cmdType; if (cmdParms != null) { foreach (SqlParameter parm in cmdParms) cmd.Parameters.Add(parm); } } #endregion 事务--Bao--2017-08-17 public static void LogInfo(string msg) { string FilePath = AppDomain.CurrentDomain.SetupInformation.ApplicationBase + "log\"; if (!System.IO.Directory.Exists(FilePath)) { System.IO.Directory.CreateDirectory(FilePath); } try { string fileName = FilePath + DateTime.Now.ToString("yyyyMMdd") + ".log"; var logStreamWriter = new System.IO.StreamWriter(fileName, true, Encoding.GetEncoding("gb2312")); logStreamWriter.WriteLine(msg); logStreamWriter.Close(); } catch { } } #region 标准MD5加密 /// <summary> /// MD5加密字符串 /// </summary> /// <param name="str"></param> /// <returns></returns> public static string Md5String(string str) { string pwd = String.Empty; MD5 md5 = MD5.Create(); // 编码UTF8/Unicode byte[] s = md5.ComputeHash(Encoding.UTF8.GetBytes(str)); // 转换成字符串 for (int i = 0; i < s.Length; i++) { //格式后的字符是小写的字母 //如果使用大写（X）则格式后的字符是大写字符 pwd = pwd + s[i].ToString("x2"); } return pwd; } #endregion #region 分页 /// <summary> /// 获取分页数据 /// </summary> /// <param name="tableName">表名</param> /// <param name="primaryKey">主键字段名</param> /// <param name="pageIndex">页码</param> /// <param name="pageSize">页尺寸</param> /// <param name="isReCount">返回记录总数, 非 0 值则返回</param> /// <param name="orderType">设置排序类型, 非 0 值则降序</param> /// <param name="where">查询条件 (注意: 不要加 where)</param> /// <param name="recount">返回记录总数</param> /// <param name="pageCount">返回总页数</param> /// <returns></returns> public static DataTable GetPage(string tableName, string primaryKey, int pageIndex, int pageSize, int isReCount, int orderType, string where, out int recount, out int pageCount) { string procName = "SP_Page"; SqlParameter[] paras = new SqlParameter[]{ new SqlParameter("@Recount",SqlDbType.Int), new SqlParameter("@PageCount",SqlDbType.Int), new SqlParameter("@tblName",tableName), new SqlParameter("@fldName",primaryKey), new SqlParameter("@PageSize",pageSize), new SqlParameter("@PageIndex",pageIndex), new SqlParameter("@IsReCount",isReCount), new SqlParameter("@OrderType",orderType), new SqlParameter("@strWhere",where) }; paras[0].Direction = ParameterDirection.Output; paras[1].Direction = ParameterDirection.Output; DataTable dt = SqlHelper.FillDataTable(procName, paras); recount = int.Parse(paras[0].Value.ToString()); pageCount = int.Parse(paras[1].Value.ToString()); return dt; } public static string newShowPageNavNew(int pageCurrent, int totalCount, int pageSize = 5, string methodName = "getlist") { var totalPage = Math.Max((totalCount + pageSize - 1) / pageSize, 1); //if (totalPage >= 1) //{ //要输出的超链接字符串 var pageNav = new StringBuilder(); //左边代码 //无数据 if (totalCount == 0) { pageNav.AppendFormat("<div class='col-sm-4'><div class='dataTables_info' id='editable_info' role='alert' aria-live='polite' aria-relevant='all'>显示 0 项，共 0 项</div></div>"); } else { //最后一页文件数量 if (pageCurrent == totalPage) { pageNav.AppendFormat("<div class='col-sm-4'><div class='dataTables_info' id='editable_info' role='alert' aria-live='polite' aria-relevant='all'>显示 " + ((pageCurrent - 1) * pageSize + 1) + " 到 " + totalCount + " 项，共 " + totalCount + " 项</div></div>"); } else { pageNav.AppendFormat("<div class='col-sm-4'><div class='dataTables_info' id='editable_info' role='alert' aria-live='polite' aria-relevant='all'>显示 " + ((pageCurrent - 1) * pageSize + 1) + " 到 " + (pageCurrent * pageSize) + " 项，共 " + totalCount + " 项</div></div>"); } } //右边代码 pageNav.AppendFormat("<div class='col-sm-8'><div class='dataTables_paginate paging_simple_numbers' id='editable_paginate'><ul class='pagination'>"); //如果当前是第一页，则“上一页”不可点 if (pageCurrent == 1) { pageNav.AppendFormat("<li class='paginate_button previous disabled' aria-controls='editable' tabindex='0' id='editable_previous'><a href='javascript:void(0)'>上一页</a></li>"); } else { pageNav.AppendFormat("<li class='paginate_button previous' aria-controls='editable' tabindex='0' id='editable_previous'><a href='javascript:void(0)' onclick='" + methodName + "({0},{1})'>{2}</a></li>", pageCurrent - 1, pageSize, "上一页"); } //中间页码 if (pageCurrent <= 3) { for (int i = 1; i < 8; i++) { if (i <= totalPage) { if (pageCurrent == i) //当前页处理 { pageNav.AppendFormat("<li class='paginate_button active' aria-controls='editable' tabindex='0'><a href='javascript:void(0)'>{0}</a></li>", pageCurrent); } else //一般页处理 { pageNav.AppendFormat("<li class='paginate_button previous' aria-controls='editable' tabindex='0' id='editable_previous'><a href='javascript:void(0)' onclick='" + methodName + "({0},{1})'>{2}</a></li>", i, pageSize, i); } } } } else if (pageCurrent > 3 && pageCurrent < totalPage - 3) { int current = 4; for (int i = 1; i < 8; i++) { if ((pageCurrent + i - current) >= 1 && (pageCurrent + i - current) <= totalPage) { if (current == i) //当前页处理 { pageNav.AppendFormat("<li class='paginate_button active' aria-controls='editable' tabindex='0'><a href='javascript:void(0)'>{0}</a></li>", pageCurrent); } else //一般页处理 { pageNav.AppendFormat("<li class='paginate_button previous' aria-controls='editable' tabindex='0' id='editable_previous'><a href='javascript:void(0)' onclick='" + methodName + "({0},{1})'>{2}</a></li>", pageCurrent + i - current, pageSize, pageCurrent + i - current); } } } } else { for (int i = totalPage - 6; i <= totalPage; i++) { if (i <= totalPage && i > 0) { if (pageCurrent == i) //当前页处理 { pageNav.AppendFormat("<li class='paginate_button active' aria-controls='editable' tabindex='0'><a href='javascript:void(0)'>{0}</a></li>", pageCurrent); } else //一般页处理 { pageNav.AppendFormat("<li class='paginate_button previous' aria-controls='editable' tabindex='0' id='editable_previous'><a href='javascript:void(0)' onclick='" + methodName + "({0},{1})'>{2}</a></li>", i, pageSize, i); } } } } //如果当前是最后一页，则“下一页”不可点 if (pageCurrent == totalPage) { pageNav.AppendFormat("<li class='paginate_button next disabled' aria-controls='editable' tabindex='0' id='editable_next'><a href='javascript:void(0)'>下一页</a></li>"); } else { pageNav.AppendFormat("<li class='paginate_button next' aria-controls='editable' tabindex='0' id='editable_next'><a href='javascript:void(0)' onclick='" + methodName + "({0},{1})'>{2}</a></li>", pageCurrent + 1, pageSize, "下一页"); } pageNav.AppendFormat("</ul></div></div>"); return pageNav.ToString(); //} //else //{ // return string.Empty; //} } #endregion } #endregion }