/// <summary> /// 添加/更新/删除 /// </summary> /// <param name="strsql">SQL语句</param> /// <returns>返回影响行数</returns> public static int UpdateDelele(string strsql) { int i = 0; SqlConnection con = new SqlConnection(connectionString); DataSet ds = new DataSet(); try { con.Open(); SqlCommand com = new SqlCommand(strsql, con); SqlDataAdapter da = new SqlDataAdapter(com); da.Fill(ds); i = 1; } catch (Exception ex) { string stringError = ex.ToString(); i = 0; WriteLogHelper wl = new WriteLogHelper(ex, strsql); wl.StartWriteLog(); } finally { con.Close(); } return(i); }
/// <summary> /// 获取一个dataset /// </summary> /// <param name="strsql">SQL语句</param> /// <returns></returns> public static DataSet GetDataSet(string strsql) { //实例化Mysql连接类 using (MySqlConnection con = new MySqlConnection(connectionString)) { DataSet ds = new DataSet(); try { con.Open(); //实例化Mysql操作类 MySqlCommand com = new MySqlCommand(strsql, con); //实例化Mysqlqiao类 MySqlDataAdapter da = new MySqlDataAdapter(com); //将数据填充到dataset da.Fill(ds); return(ds); } catch (Exception ex) { WriteLogHelper wl = new WriteLogHelper(ex, strsql); wl.StartWriteLog(); return(null); } finally { con.Close(); } } }
/// <summary> /// 添加/更新/删除 /// </summary> /// <param name="sql">SQL语句</param> /// <returns>返回bool值</returns> public static bool _ExcuteSQL(string strsql) { //实例化Mysql连接类 using (MySqlConnection con = new MySqlConnection(connectionString)) { DataSet ds = new DataSet(); try { con.Open(); //实例化Mysql操作类 MySqlCommand com = new MySqlCommand(strsql, con); if (com.ExecuteNonQuery() > -1) { return(true); } return(false); } catch (Exception ex) { WriteLogHelper wl = new WriteLogHelper(ex, strsql); wl.StartWriteLog(); return(false); } finally { con.Close(); } } }
/// <summary> /// 执行SQL语句,返回影响的记录数 /// </summary> /// <param name="SQLString">SQL语句</param> /// <returns>影响的记录数</returns> public static int ExecuteSql(string SQLString) { SqlConnection con = null; SqlCommand command = null; try { using (con = new SqlConnection(connectionString)) { using (command = new SqlCommand(SQLString, con)) { if (con.State != ConnectionState.Open) { con.Open(); } int rows = command.ExecuteNonQuery(); return(rows); } } } catch (Exception ex) { string stringError = ex.ToString(); WriteLogHelper wl = new WriteLogHelper(ex, SQLString); wl.StartWriteLog(); return(-1); } finally { command.Dispose(); con.Close(); } }
/// <summary> /// 获取一个datatable /// </summary> /// <param name="strsql">SQL语句</param> /// <returns></returns> public static DataTable GetDataTable(string strsql) { SqlConnection con = new SqlConnection(connectionString); DataSet ds = new DataSet(); try { con.Open(); SqlCommand com = new SqlCommand(strsql, con); SqlDataAdapter da = new SqlDataAdapter(com); da.Fill(ds); return(ds.Tables[0]); } catch (Exception ex) { string stringError = ex.ToString(); WriteLogHelper wl = new WriteLogHelper(ex, strsql); wl.StartWriteLog(); return(null); } finally { con.Close(); } }
/// <summary> /// 执行存储过程,返回结果集合DataSet /// </summary> /// <param name="storedProcName">存储过程名</param> /// <param name="parameters">存储过程参数</param> /// <param name="tableName">DataSet结果中的表名</param> /// <returns>DataSet</returns> public static DataSet RunProcedure(string storedProcName, IDataParameter[] parameters, string tableName) { using (SqlConnection connection = new SqlConnection(connectionString)) { try { DataSet dataSet = new DataSet(); connection.Open(); SqlDataAdapter sqlDA = new SqlDataAdapter(); sqlDA.SelectCommand = BuildQueryCommand(connection, storedProcName, parameters); sqlDA.Fill(dataSet, tableName); connection.Close(); return(dataSet); } catch (Exception ex) { StringBuilder sb = new StringBuilder(); sb.Append("call ").Append(storedProcName).Append("("); if (parameters != null) { foreach (IDataParameter parameter in parameters) { if (parameter != null) { if (((parameter.Direction == ParameterDirection.InputOutput) || (parameter.Direction == ParameterDirection.Input)) && (parameter.Value == null)) { parameter.Value = DBNull.Value; } sb.Append(parameter.ParameterName).Append("=>").Append(parameter.Value).Append(" "); } } } sb.Append(")"); WriteLogHelper wl = new WriteLogHelper(ex, sb.ToString()); wl.StartWriteLog(); return(null); } finally { connection.Close(); } } }
/// <summary> /// 控制层Controllers异常记录日记 /// </summary> /// <param name="filterContext"></param> /// <param name="type"></param> public static void WriteControllerExLog(ExceptionContext filterContext, Type type) { System.Text.StringBuilder sb = new System.Text.StringBuilder(); try { string strFullName = type.FullName; string strMethodName = filterContext.RouteData.Values["action"].ToString().ToLower(); MethodInfo oMethodInfo = null; foreach (var oMethod in type.GetMethods()) { if (oMethod.Name.ToLower().Equals(strMethodName)) { oMethodInfo = oMethod; break; } } if (oMethodInfo != null) { var arParameters = oMethodInfo.GetParameters(); sb.Append("类名:").Append(strFullName); sb.Append("方法名:").Append(oMethodInfo.Name); if (arParameters.Length > 0) { sb.Append("参数列表:"); foreach (var oParameterInfo in arParameters) { sb.Append(oParameterInfo.Name).Append("->").Append(filterContext.HttpContext.Request.Params[oParameterInfo.Name]); sb.Append(" "); } } else { sb.Append("参数列表:无"); } } } catch { } WriteLogHelper wl = new WriteLogHelper(filterContext.Exception, "Controller->" + sb.ToString(), false); wl.StartWriteLog(); }
/// <summary> /// 执行存储过程,返回影响的行数 /// </summary> /// <param name="storedProcName">存储过程名</param> /// <param name="parameters">存储过程参数</param> /// <param name="rowsAffected">影响的行数</param> /// <returns></returns> public static bool RunProcedure(string storedProcName, IDataParameter[] parameters, out int rowsAffected) { rowsAffected = -1;//默认-1 添加失败 using (MySqlConnection connection = new MySqlConnection(connectionString)) { try { connection.Open(); MySqlCommand command = BuildQueryCommand(connection, storedProcName, parameters); rowsAffected = command.ExecuteNonQuery(); command.Parameters.Clear(); return(rowsAffected > -1 ? true : false); } catch (Exception ex) { StringBuilder sb = new StringBuilder(); sb.Append("call ").Append(storedProcName).Append("("); if (parameters != null) { foreach (IDataParameter parameter in parameters) { if (parameter != null) { if (((parameter.Direction == ParameterDirection.InputOutput) || (parameter.Direction == ParameterDirection.Input)) && (parameter.Value == null)) { parameter.Value = DBNull.Value; } sb.Append(parameter.ParameterName).Append("=>").Append(parameter.Value).Append(" "); } } } sb.Append(")"); WriteLogHelper wl = new WriteLogHelper(ex, sb.ToString()); wl.StartWriteLog(); return(false); } finally { connection.Close(); } } }
/// <summary> /// 执行SQL语句,返回影响的记录数 /// </summary> /// <param name="SQLString">SQL语句</param> /// <returns>影响的记录数</returns> public static int ExecuteSql(string SQLString, params SqlParameter[] cmdParms) { using (SqlConnection connection = new SqlConnection(connectionString)) { using (SqlCommand cmd = new SqlCommand()) { try { PrepareCommand(cmd, connection, null, SQLString, cmdParms); int rows = cmd.ExecuteNonQuery(); cmd.Parameters.Clear(); return(rows); } catch (System.Data.SqlClient.SqlException ex) { StringBuilder sb = new StringBuilder(); sb.Append("call ").Append(SQLString).Append("("); if (cmdParms != null) { foreach (IDataParameter parameter in cmdParms) { if (parameter != null) { if (((parameter.Direction == ParameterDirection.InputOutput) || (parameter.Direction == ParameterDirection.Input)) && (parameter.Value == null)) { parameter.Value = DBNull.Value; } sb.Append(parameter.ParameterName).Append("=>").Append(parameter.Value).Append(" "); } } } sb.Append(")"); WriteLogHelper wl = new WriteLogHelper(ex, sb.ToString()); wl.StartWriteLog(); throw ex; } } } }
/// <summary> /// 执行多条SQL语句,实现数据库事务。 /// </summary> /// <param name="SQLStringList">多条SQL语句</param> /// public static void ExecuteSqlTran(ArrayList SQLStringList) { using (SqlConnection con = new SqlConnection(connectionString)) { if (con.State != ConnectionState.Open) { con.Open(); } SqlCommand command = new SqlCommand(); command.Connection = con; SqlTransaction tx = con.BeginTransaction(); command.Transaction = tx; try { for (int n = 0; n < SQLStringList.Count; n++) { string strsql = SQLStringList[n].ToString(); if (strsql.Trim().Length > 1) { command.CommandText = strsql; command.ExecuteNonQuery(); } } tx.Commit(); } catch (Exception ex) { string stringError = ex.ToString(); WriteLogHelper wl = new WriteLogHelper(ex, SQLStringList.ToString()); wl.StartWriteLog(); tx.Rollback(); } finally { command.Dispose(); con.Close(); } } }
/// <summary> /// 添加/更新/删除 /// </summary> /// <param name="sql">SQL语句</param> /// <returns>返回bool值</returns> public static bool ExcuteSQL(string strsql) { SqlConnection con = new SqlConnection(connectionString); DataSet ds = new DataSet(); try { con.Open(); SqlCommand com = new SqlCommand(strsql, con); com.ExecuteNonQuery(); return(true); } catch (Exception ex) { string stringError = ex.ToString(); WriteLogHelper wl = new WriteLogHelper(ex, strsql); wl.StartWriteLog(); return(false); } finally { con.Close(); } }
/// <summary> /// 存储过程事务处理 阿彩 2015-10-23 /// </summary> /// <param name="storedProcName">存储过程名</param> /// <param name="parameters">参数</param> /// <returns></returns> public static int BuildQueryTran(List <string> storedProcName, List <IDataParameter[]> parameters) { int rowsAffected = 0; int intCount = 0; using (SqlConnection con = new SqlConnection(connectionString)) { if (con.State != ConnectionState.Open) { con.Open(); } SqlCommand command = new SqlCommand(); command.Connection = con; SqlTransaction objtran = con.BeginTransaction(); command.Transaction = objtran; StringBuilder sbProcName = new StringBuilder(); try { string strProcName = string.Empty; IDataParameter[] Para; for (int i = 0; i < storedProcName.Count; i++) { strProcName = (string)storedProcName[i]; sbProcName.Append(strProcName).Append(@";"); Para = (IDataParameter[])parameters[i]; command.CommandText = strProcName; command.CommandType = CommandType.StoredProcedure; if (Para != null) { foreach (SqlParameter parameter in Para) { command.Parameters.Add(parameter); } intCount = command.ExecuteNonQuery(); //-1为没有影响 if (intCount == -1) { intCount = 0; } rowsAffected = rowsAffected + intCount; command.Parameters.Clear(); } } objtran.Commit(); } catch (Exception ex) { string stringError = ex.ToString(); objtran.Rollback(); WriteLogHelper wl = new WriteLogHelper(ex, sbProcName.ToString()); wl.StartWriteLog(); rowsAffected = -1;//2016-07-11 卢德伟加 } finally { command.Dispose(); con.Close(); } } return(rowsAffected); }
/// <summary> /// 无损压缩图片 /// </summary> /// <param name="sFile">原图片</param> /// <param name="dFile">压缩后保存位置</param> /// <param name="dHeight">高度</param> /// <param name="dWidth"></param> /// <param name="flag">压缩质量 1-100</param> /// <returns></returns> public static bool CompressPicture(string sFile, string dFile, int dHeight, int dWidth, int flag) { System.Drawing.Image iSource = System.Drawing.Image.FromFile(sFile); ImageFormat tFormat = iSource.RawFormat; int sW = 0, sH = 0; //按比例缩放 Size tem_size = new Size(iSource.Width, iSource.Height); if (tem_size.Width > dHeight || tem_size.Width > dWidth) //将**改成c#中的或者操作符号 { if ((tem_size.Width * dHeight) > (tem_size.Height * dWidth)) { sW = dWidth; sH = (dWidth * tem_size.Height) / tem_size.Width; } else { sH = dHeight; sW = (tem_size.Width * dHeight) / tem_size.Height; } } else { sW = tem_size.Width; sH = tem_size.Height; } Bitmap ob = new Bitmap(dWidth, dHeight); Graphics g = Graphics.FromImage(ob); g.Clear(Color.WhiteSmoke); g.CompositingQuality = CompositingQuality.HighQuality; g.SmoothingMode = SmoothingMode.HighQuality; g.InterpolationMode = InterpolationMode.HighQualityBicubic; g.DrawImage(iSource, new Rectangle((dWidth - sW) / 2, (dHeight - sH) / 2, sW, sH), 0, 0, iSource.Width, iSource.Height, GraphicsUnit.Pixel); g.Dispose(); //以下代码为保存图片时,设置压缩质量 EncoderParameters ep = new EncoderParameters(); long[] qy = new long[1]; qy[0] = flag;//设置压缩的比例1-100 EncoderParameter eParam = new EncoderParameter(System.Drawing.Imaging.Encoder.Quality, qy); ep.Param[0] = eParam; try { ImageCodecInfo[] arrayICI = ImageCodecInfo.GetImageEncoders(); ImageCodecInfo jpegICIinfo = null; for (int x = 0; x < arrayICI.Length; x++) { if (arrayICI[x].FormatDescription.Equals("JPEG")) { jpegICIinfo = arrayICI[x]; break; } } if (jpegICIinfo != null) { ob.Save(dFile, jpegICIinfo, ep);//dFile是压缩后的新路径 } else { ob.Save(dFile, tFormat); } return(true); } catch (Exception ex) { WriteLogHelper wl = new WriteLogHelper(ex, "创建压缩图失败11"); wl.StartWriteLog(); return(false); } finally { iSource.Dispose(); ob.Dispose(); } }