public static object ExecuteScalar(SqlConnection conn, string cmdText,
params SqlParameter [] parameters)
{
try
{
//if ( checkInter ( ) == false )
// return null;
//if ( QuickOpen ( conn ,500 ) == false )
// return null;
//if ( conn . State != ConnectionState . Open )
// conn . Open ( );
using (SqlCommand cmd = conn.CreateCommand( ))
{
cmd.CommandTimeout = 60;
cmd.CommandText = cmdText;
cmd.Parameters.AddRange(parameters);
resultObj = cmd.ExecuteScalar( );
try
{
LogHelperToSql.SaveLog(cmdText, parameters);
}
catch (Exception ex) { Utility.LogHelper.WriteLog(ex.Message + "\n\r" + ex.StackTrace); }
}
}
catch (Exception ex)
{
resultObj = 0;
Utility.LogHelper.WriteLog(ex.Message + "\n\r" + ex.StackTrace);
Utility.LogHelper.WriteLog(cmdText);
throw new Exception(ex.Message);
}
return(resultObj);
}
/// <summary>
/// 执行没有参数的SQL语句 返回是否成功
/// </summary>
/// <param name="cmdText"></param>
/// <returns></returns>
public static bool ExecuteNonQueryBool(string cmdText)
{
try
{
using (SqlConnection conn = new SqlConnection(Connstr))
{
conn.Open( );
int row = ExecuteNonQuery(conn, cmdText);
if (row > 0)
{
result = true;
try
{
LogHelperToSql.SaveLog(cmdText, string.Empty);
}
catch (Exception ex) { Utility.LogHelper.WriteLog(ex.Message); }
}
else
{
result = false;
}
}
}
catch (Exception ex)
{
Utility.LogHelper.WriteLog(ex.StackTrace);
Utility.LogHelper.WriteLog(cmdText);
result = false;
throw new Exception(ex.Message);
}
return(result);
}
public static int ExecuteNonQuery(SqlConnection conn, string cmdText,
params SqlParameter[] parameters)
{
using (SqlCommand cmd = conn.CreateCommand( ))
{
try
{
cmd.CommandText = cmdText;
cmd.Parameters.AddRange(parameters);
try
{
LogHelperToSql.SaveLog(cmdText, parameters);
}
catch (Exception ex) { AutoUpdate.LogHelper.WriteLog(ex.Message + "\n\r" + ex.StackTrace); }
return(cmd.ExecuteNonQuery( ));
}
catch (Exception ex)
{
AutoUpdate.LogHelper.WriteLog(cmdText);
AutoUpdate.LogHelper.WriteLog(ex.Message + "\n\r" + ex.StackTrace);
throw new Exception(ex.Message);
//return 0;
}
}
}
public static object ExecuteScalar(SqlConnection conn, string cmdText,
params SqlParameter [] parameters)
{
try
{
using (SqlCommand cmd = conn.CreateCommand( ))
{
cmd.CommandText = cmdText;
cmd.Parameters.AddRange(parameters);
resultObj = cmd.ExecuteScalar( );
try
{
LogHelperToSql.SaveLog(cmdText, parameters);
}
catch (Exception ex) { Utility.LogHelper.WriteLog(ex.Message); }
}
}
catch (Exception ex)
{
resultObj = 0;
Utility.LogHelper.WriteLog(ex.StackTrace);
Utility.LogHelper.WriteLog(cmdText);
throw new Exception(ex.Message);
}
return(resultObj);
}
public static int ExecuteNonQuery(SqlConnection conn, string cmdText)
{
try
{
using (SqlCommand cmd = conn.CreateCommand( ))
{
cmd.CommandText = cmdText;
resultCount = cmd.ExecuteNonQuery( );
try
{
LogHelperToSql.SaveLog(cmdText, string.Empty);
}
catch (Exception ex) { Utility.LogHelper.WriteLog(ex.Message); }
}
}
catch (Exception ex)
{
resultCount = 0;
Utility.LogHelper.WriteLog(ex.StackTrace);
Utility.LogHelper.WriteLog(cmdText);
throw new Exception(ex.Message);
}
return(resultCount);
}
public static DataSet ExecuteDataSet(SqlConnection conn, string SQLString, params object[] parameter)
{
using (SqlCommand cmd = conn.CreateCommand( ))
{
PrepareCommand(cmd, conn, null, SQLString, parameter);
using (SqlDataAdapter da = new SqlDataAdapter(cmd))
{
DataSet ds = new DataSet( );
try
{
da.Fill(ds, "ds");
cmd.Parameters.Clear( );
try
{
LogHelperToSql.SaveLog(SQLString, parameter);
}
catch (Exception ex) { Utility.LogHelper.WriteLog(ex.Message + "\n\r" + ex.StackTrace); }
}
catch (SqlException ex) {
Utility.LogHelper.WriteLog(ex.Message + "\n\r" + ex.StackTrace);
throw new Exception(ex.Message);
}
finally {
cmd.Dispose( );
conn.Close( );
}
return(ds);
}
}
}
public static DataTable ExecuteDataTable(SqlConnection conn, string cmdText,
params SqlParameter [] parameters)
{
try
{
using (SqlCommand cmd = conn.CreateCommand( ))
{
cmd.CommandText = cmdText;
cmd.Parameters.AddRange(parameters);
using (SqlDataAdapter adapter = new SqlDataAdapter(cmd))
{
DataTable dt = new DataTable( );
adapter.Fill(dt);
try
{
LogHelperToSql.SaveLog(cmdText, parameters);
}
catch (Exception ex) { Utility.LogHelper.WriteLog(ex.Message + "\n\r" + ex.StackTrace); }
return(dt);
}
}
}
catch (Exception ex)
{
Utility.LogHelper.WriteLog(ex.Message + "\n\r" + ex.StackTrace);
Utility.LogHelper.WriteLog(cmdText);
throw new Exception(ex.Message);
}
}
/// <summary>
/// 执行多条SQL语句 实现数据库事务
/// </summary>
/// <param name="SQLStringList"></param>
/// <returns>此事务的实现 可以有parameter[]参数列表</returns>
public static bool ExecuteSqlTran(Hashtable SQLStringList)
{
if (checkInter( ) == false)
{
return(false);
}
using (SqlConnection conn = new SqlConnection(Connstr))
{
//if ( QuickOpen ( conn ,500 ) == false )
// return false;
bool result = false;
if (conn.State != ConnectionState.Open)
{
conn.Open( );
}
using (SqlTransaction trans = conn.BeginTransaction( ))
{
SqlCommand cmd = new SqlCommand( );
cmd.CommandTimeout = 60;
string cmdText = string.Empty;
try
{
//循环
foreach (DictionaryEntry myDE in SQLStringList)
{
cmdText = myDE.Key.ToString( );
Object [] cmdParms = ( Object [] )myDE.Value;
PrepareCommand(cmd, conn, trans, cmdText, cmdParms);
int val = cmd.ExecuteNonQuery( );
cmd.Parameters.Clear( );
try
{
LogHelperToSql.SaveLog(cmdText, param);
}
catch (Exception ex) { Utility.LogHelper.WriteLog(ex.Message + "\n\r" + ex.StackTrace); }
}
trans.Commit( );
result = true;
}
catch (Exception ex)
{
trans.Rollback( );
Utility.LogHelper.WriteLog(ex.Message + "\n\r" + ex.StackTrace);
Utility.LogHelper.WriteLog(cmdText);
throw new Exception(ex.Message);
}
finally
{
cmd.Dispose( );
conn.Close( );
}
}
return(result);
}
}
/// <summary>
/// 执行多条SQL语句，实现数据库事务。
/// </summary>
/// <param name="SQLStringList"></param>
public static bool ExecuteSqlTran(ArrayList SQLStringList)
{
if (checkInter( ) == false)
{
return(false);
}
using (SqlConnection conn = new SqlConnection(Connstr))
{
//if ( QuickOpen ( conn ,500 ) == false )
// return false;
if (conn.State != ConnectionState.Open)
{
conn.Open( );
}
SqlCommand cmd = new SqlCommand( );
cmd.CommandTimeout = 60;
cmd.Connection = conn;
SqlTransaction tran = conn.BeginTransaction( );
cmd.Transaction = tran;
string strsql = string.Empty;
try
{
for (int i = 0; i < SQLStringList.Count; i++)
{
strsql = SQLStringList[i].ToString( );
if (strsql.Trim( ).Length > 1)
{
cmd.CommandText = strsql;
cmd.ExecuteNonQuery( );
try
{
LogHelperToSql.SaveLog(strsql, param);
}
catch (Exception ex) { Utility.LogHelper.WriteLog(ex.Message + "\n\r" + ex.StackTrace); }
}
}
tran.Commit( );
return(true);
}
catch (Exception ex)
{
Utility.LogHelper.WriteLog(ex.Message + "\n\r" + ex.StackTrace);
Utility.LogHelper.WriteLog(strsql);
tran.Rollback( );
throw new Exception(ex.Message);
}
finally
{
cmd.Dispose( );
conn.Close( );
}
}
}
public static int ExecuteNonQuery(SqlConnection conn, string cmdText)
{
using (SqlCommand cmd = conn.CreateCommand())
{
cmd.CommandTimeout = conn.ConnectionTimeout;
cmd.CommandText = cmdText;
try
{
LogHelperToSql.SaveLog(cmdText, string.Empty);
}
catch (Exception ex) { AutoUpdate.LogHelper.WriteLog(ex.Message + "\n\r" + ex.StackTrace); }
return(cmd.ExecuteNonQuery( ));
}
}
public static bool ExecuteSqlTran(Dictionary <object, object> SQLStringList)
{
using (SqlConnection conn = new SqlConnection(connstr))
{
bool result = false;
conn.Open( );
using (SqlTransaction trans = conn.BeginTransaction( ))
{
SqlCommand cmd = new SqlCommand( );
try
{
string cmdText = string.Empty;
Object [] cmdParms = null;
//循环
foreach (object key in SQLStringList.Keys)
{
cmdText = key.ToString( );
cmdParms = ( Object [] )SQLStringList [key];
PrepareCommand(cmd, conn, trans, cmdText, cmdParms);
AutoUpdate.LogHelper.WriteLog(cmdText);
try
{
LogHelperToSql.SaveLog(cmdText, cmdParms);
}
catch (Exception ex) { AutoUpdate.LogHelper.WriteLog(ex.Message + "\n\r" + ex.StackTrace); }
int val = cmd.ExecuteNonQuery( );
cmd.Parameters.Clear( );
}
trans.Commit( );
result = true;
}
catch (Exception ex)
{
trans.Rollback( );
AutoUpdate.LogHelper.WriteLog(ex.Message + "\n\r" + ex.StackTrace);
result = false;
}
finally
{
cmd.Dispose( );
conn.Close( );
}
}
return(result);
}
}
public static object ExecuteScalar(SqlConnection conn, string cmdText,
params SqlParameter[] parameters)
{
using (SqlCommand cmd = conn.CreateCommand( ))
{
cmd.CommandText = cmdText;
cmd.Parameters.AddRange(parameters);
try
{
LogHelperToSql.SaveLog(cmdText, parameters);
}
catch (Exception ex) { AutoUpdate.LogHelper.WriteLog(ex.Message + "\n\r" + ex.StackTrace); }
return(cmd.ExecuteScalar( ));
}
}
/// <summary>
/// 执行多条SQL语句 实现数据库事物
/// </summary>
/// <param name="SQLString"></param>
/// <returns></returns>
public static bool ExecuteSqlTranDic(Dictionary <object, object> SQLString)
{
using (SqlConnection conn = new SqlConnection(Connstr))
{
bool result = false;
conn.Open( );
using (SqlTransaction trans = conn.BeginTransaction( ))
{
SqlCommand cmd = new SqlCommand( );
string cmdText = string.Empty;
Object [] cmdParms = null;
try
{
//循环
foreach (object myDE in SQLString.Keys)
{
cmdText = myDE.ToString( );
cmdParms = ( Object [] )SQLString [myDE];
PrepareCommand(cmd, conn, trans, cmdText, cmdParms);
int val = cmd.ExecuteNonQuery( );
cmd.Parameters.Clear( );
try
{
LogHelperToSql.SaveLog(cmdText, param);
}
catch (Exception ex) { Utility.LogHelper.WriteLog(ex.Message + "\n\r" + ex.StackTrace); }
}
trans.Commit( );
result = true;
}
catch (Exception ex)
{
trans.Rollback( );
Utility.LogHelper.WriteLog(ex.Message + "\n\r" + ex.StackTrace);
Utility.LogHelper.WriteLog(cmdText);
throw new Exception(ex.Message);
}
finally
{
cmd.Dispose( );
conn.Close( );
}
}
return(result);
}
}
/// <summary>
/// 执行多条SQL语句，实现数据库事务。
/// </summary>
/// <param name="SQLStringList"></param>
public static bool ExecuteSqlTran(ArrayList SQLStringList)
{
using (SqlConnection conn = new SqlConnection(connstr))
{
conn.Open( );
SqlCommand cmd = new SqlCommand( );
cmd.Connection = conn;
SqlTransaction tran = conn.BeginTransaction( );
cmd.Transaction = tran;
string SGS = "", SX = "";
try
{
string strsql = string.Empty;
for (int i = 0; i < SQLStringList.Count; i++)
{
SGS = SQLStringList[i].ToString( );
strsql = SQLStringList[i].ToString( );
AutoUpdate.LogHelper.WriteLog(strsql);
if (strsql.Trim( ).Length > 1)
{
//AutoUpdate . LogHelper . WriteLog ( strsql . ToString ( ) );
cmd.CommandText = strsql;
cmd.ExecuteNonQuery( );
try
{
LogHelperToSql.SaveLog(strsql, string.Empty);
}
catch (Exception ex) { AutoUpdate.LogHelper.WriteLog(ex.Message + "\n\r" + ex.StackTrace); }
}
}
tran.Commit( );
return(true);
}
catch (Exception ex)
{
SX = SGS;
AutoUpdate.LogHelper.WriteLog(ex.Message + "\n\r" + ex.StackTrace);
tran.Rollback( );
return(false);
}
finally
{
cmd.Dispose( );
conn.Close( );
}
}
}
/// <summary>
/// 执行SQL语句，返回自增列值
/// </summary>
/// <param name="SQLString"></param>
/// <param name="parameter"></param>
/// <returns></returns>
public static int ExecuteSqlReturnId(string SQLString, params object[] parameter)
{
if (checkInter( ) == false)
{
return(0);
}
using (SqlConnection conn = new SqlConnection(Connstr))
{
//if ( QuickOpen ( conn ,500 ) == false )
// return 0;
using (SqlCommand cmd = new SqlCommand())
{
try
{
cmd.CommandTimeout = 60;
PrepareCommand(cmd, conn, null, SQLString, parameter);
object obj = cmd.ExecuteScalar( );
cmd.Parameters.Clear( );
if (obj == null)
{
return(0);
}
else
{
try
{
LogHelperToSql.SaveLog(SQLString, param);
}
catch (Exception ex) { Utility.LogHelper.WriteLog(ex.Message + "\n\r" + ex.StackTrace); }
return(Convert.ToInt32(obj));
}
}
catch (Exception E)
{
Utility.LogHelper.WriteLog(E.Message + "\n\r" + E.StackTrace);
Utility.LogHelper.WriteLog(SQLString);
throw new Exception(E.Message);
}
finally {
cmd.Dispose( );
conn.Close( );
}
}
}
}
/// <summary>
/// 生产单（订单）
/// </summary>
/// <returns></returns>
public string UpdateGrid(string sql)
{
string oddNum = string.Empty;
using (SqlConnection connection = new SqlConnection(SqlHelper.Connstr))
{
using (SqlCommand command = new SqlCommand(sql, connection))
{
command.CommandType = CommandType.Text;
connection.Open( );
SqlDependency dependency = new SqlDependency(command);
dependency.OnChange += new OnChangeEventHandler(dependency_OnChange);
using (SqlDataAdapter adapter = new SqlDataAdapter(command))
{
DataTable dt = new DataTable( );
adapter.Fill(dt);
try
{
LogHelperToSql.SaveLog(sql);
}
catch (Exception ex) { Utility.LogHelper.WriteLog(ex.Message); }
if (dt == null || dt.Rows.Count < 1)
{
return(string.Empty);
}
else
{
for (int i = 0; i < dt.Rows.Count; i++)
{
if (string.IsNullOrEmpty(oddNum))
{
oddNum = "'" + dt.Rows [i] ["PRO001"].ToString( ) + "'";
}
else
{
oddNum = oddNum + "," + "'" + dt.Rows [i] ["PRO001"].ToString( ) + "'";
}
}
return(oddNum);
}
}
}
}
}
/// <summary>
/// 执行一条计算查询结果语句，返回查询结果（object）
/// </summary>
/// <param name="SQLSting">计算查询结果语句</param>
/// <param name="parameter"></param>
/// <returns>查询结果（object）</returns>
public static object GetSingle(string SQLSting, params object[] parameter)
{
if (checkInter( ) == false)
{
return(null);
}
using (SqlConnection conn = new SqlConnection(Connstr))
{
//if ( QuickOpen ( conn ,500 ) == false )
// return null;
using (SqlCommand cmd = new SqlCommand( ))
{
try
{
cmd.CommandTimeout = 60;
PrepareCommand(cmd, conn, null, SQLSting, parameter);
object obj = cmd.ExecuteScalar( );
if ((object.Equals(obj, null)) || (object.Equals(obj, System.DBNull.Value)))
{
return(null);
}
else
{
try
{
LogHelperToSql.SaveLog(SQLSting, param);
}
catch (Exception ex) { Utility.LogHelper.WriteLog(ex.Message); }
return(obj);
}
}
catch (SqlException e)
{
Utility.LogHelper.WriteLog(e.Message + "\n\r" + e.StackTrace);
Utility.LogHelper.WriteLog(SQLSting);
throw new Exception(e.Message);
}
finally {
cmd.Dispose( );
conn.Close( );
}
}
}
}
public static DataTable ExecuteDataTable(SqlConnection conn, string cmdText,
params SqlParameter [] parameters)
{
try
{
//if ( checkInter ( ) == false )
// return null;
//if ( QuickOpen ( conn ,100 ) == false )
// return null;
//if ( conn . State != ConnectionState . Open )
// conn . Open ( );
using (SqlCommand cmd = conn.CreateCommand( ))
{
cmd.CommandTimeout = 60;
cmd.CommandText = cmdText;
cmd.Parameters.AddRange(parameters);
using (SqlDataAdapter adapter = new SqlDataAdapter(cmd))
{
DataTable dt = new DataTable( );
adapter.Fill(dt);
try
{
LogHelperToSql.SaveLog(cmdText, parameters);
}
catch (Exception ex) { Utility.LogHelper.WriteLog(ex.Message + "\n\r" + ex.StackTrace); }
//组装日计划排计划记录日志
//LogHelperToSql . SaveLog ( cmdText ,parameters );
return(dt);
}
}
}
catch (Exception ex)
{
Utility.LogHelper.WriteLog(ex.Message + "\n\r" + ex.StackTrace);
Utility.LogHelper.WriteLog(cmdText);
throw new Exception(ex.Message);
}
finally
{
conn.Close( );
conn.Dispose( );
}
}
/// <summary>
/// 执行没有参数的SQL语句 返回是否成功
/// </summary>
/// <param name="cmdText"></param>
/// <returns></returns>
public static bool ExecuteNonQueryBool(string cmdText)
{
try
{
if (checkInter( ) == false)
{
return(false);
}
using (SqlConnection conn = new SqlConnection(Connstr))
{
//if ( QuickOpen ( conn ,500 ) == false )
// return false ;
//if ( conn . State != ConnectionState . Open )
// conn . Open ( );
conn.Open( );
int row = ExecuteNonQuery(conn, cmdText);
if (row > 0)
{
result = true;
try
{
LogHelperToSql.SaveLog(cmdText, string.Empty);
}
catch (Exception ex) { Utility.LogHelper.WriteLog(ex.Message); }
}
else
{
result = false;
}
}
}
catch (Exception ex)
{
Utility.LogHelper.WriteLog(ex.Message + "\n\r" + ex.StackTrace);
Utility.LogHelper.WriteLog(cmdText);
result = false;
throw new Exception(ex.Message);
}
return(result);
}
/// <summary>
/// 执行SQL语句，返回自增列值
/// </summary>
/// <param name="SQLString"></param>
/// <param name="parameter"></param>
/// <returns></returns>
public static int ExecuteSqlReturnId(string SQLString, params object[] parameter)
{
using (SqlConnection conn = new SqlConnection(connstr))
{
using (SqlCommand cmd = new SqlCommand())
{
try
{
PrepareCommand(cmd, conn, null, SQLString, parameter);
object obj = cmd.ExecuteScalar( );
try
{
LogHelperToSql.SaveLog(SQLString, parameter);
}
catch (Exception ex) { AutoUpdate.LogHelper.WriteLog(ex.Message + "\n\r" + ex.StackTrace); }
cmd.Parameters.Clear( );
if (obj == null)
{
return(0);
}
else
{
return(Convert.ToInt32(obj));
}
}
catch (Exception E)
{
AutoUpdate.LogHelper.WriteLog(E.Message + "\n\r" + E.StackTrace);
throw new Exception(E.Message);
//return 0;
}
finally {
cmd.Dispose( );
conn.Close( );
}
}
}
}
public static int ExecuteNonQuery(SqlConnection conn, string cmdText)
{
try
{
//if ( checkInter ( ) == false )
// return 0;
//if ( QuickOpen ( conn ,500 ) == false )
// return 0;
//if ( conn . State != ConnectionState . Open )
// conn . Open ( );
using (SqlCommand cmd = conn.CreateCommand( ))
{
cmd.CommandTimeout = 60;
cmd.CommandText = cmdText;
//cmd . CommandTimeout = 0;
resultCount = cmd.ExecuteNonQuery( );
try
{
LogHelperToSql.SaveLog(cmdText, string.Empty);
}
catch (Exception ex)
{
Utility.LogHelper.WriteLog(ex.Message + "\n\r" + ex.Message);
}
}
}
catch (Exception ex)
{
resultCount = 0;
Utility.LogHelper.WriteLog(ex.Message + "\n\r" + ex.StackTrace);
Utility.LogHelper.WriteLog(cmdText);
throw new Exception(ex.Message);
}
return(resultCount);
}