/// <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();
}
}