public static int ExecuteStoredProcNonQuery(string spName, SqlParameter[] parameters, string filename)
{
using (SqlConnection conn = new SqlConnection(ConnectionString))
{
try
{
conn.Open();
SqlCommand cmd = new SqlCommand(spName, conn);
cmd.CommandType = CommandType.StoredProcedure;
if (parameters != null)
{
for (int i = 0; i < parameters.Length; i++)
{
cmd.Parameters.Add(parameters[i]);
}
}
int num2 = cmd.ExecuteNonQuery();
return(num2);
}
catch (SqlException exception)
{
ErrLog.HandleException(filename, "ExecuteStoredProcNonQuery(" + spName + ")：" + exception.Message);
throw new SystemException(exception.Message);
}
finally
{
if (conn.State.Equals(ConnectionState.Open))
{
conn.Close();
}
}
}
}
/// <summary>
/// 取得查詢的指定匿名型別
/// </summary>
/// <typeparam name="T"></typeparam>
/// <param name="sqlstr"></param>
/// <returns></returns>
public async Task <IEnumerable <T> > GetQueryResult <T>(string sqlstr)
{
try
{
if (_dbe.DBType == DBType.MySql)
{
sqlstr = sqlstr.Replace("[", "`").Replace("]", "`");
}
var rst = await _dbe.DBConnection.QueryAsync <T>(sqlstr, commandType : System.Data.CommandType.Text, transaction : globalTrans);
return(rst);
}
catch (Exception err)
{
ErrLog.ExceptionLog(err, $"query: {sqlstr} occur error.");
return(null);
}
finally
{
if (globalTrans == null)
{
_dbe.DBConnection.Close();
}
}
}
/// <summary>
/// 新增資料列
/// </summary>
/// <param name="rowdata"></param>
/// <returns></returns>
public bool Insert(T rowdata)
{
InsertCmd <T> cmd = new InsertCmd <T>(dbEntity);
try
{
var str = cmd.GetInsertCmd(rowdata);
if (string.IsNullOrEmpty(str))
{
return(false);
}
try
{
CommandDefinition cd = new CommandDefinition(commandText: str.ToString(), transaction: globalTrans, commandTimeout: _cmdTimeout);
cmd.Connection.Execute(cd);
return(true);
}
catch (Exception err)
{
ErrLog.ExceptionLog(err, $"insert row into {rowdata.GetType().Name} occur error.");
throw err;
}
finally
{
if (globalTrans == null)
{
cmd.Connection.Close();
}
}
}
catch (Exception err)
{
throw err;
}
}
internal void WriteError(string error)
{
if (ErrLog != null)
{
ErrLog.Invoke(error);
}
}
/// <summary>
/// 取得多查詢的動態類別
/// </summary>
/// <param name="sqlstr"></param>
/// <returns></returns>
public async IAsyncEnumerable <dynamic> GetManyQueryResultAsync(IEnumerable <string> sqlstr)
{
SqlMapper.GridReader rst;
try
{
string combinesql = "";
if (_dbe.DBType == DBType.MySql)
{
foreach (var s in sqlstr)
{
combinesql += s.Replace("[", "`").Replace("]", "`") + (s.EndsWith(";") ? "" : ";");
}
}
rst = await _dbe.DBConnection.QueryMultipleAsync(combinesql, commandType : System.Data.CommandType.Text, transaction : globalTrans);
}
catch (Exception err)
{
ErrLog.ExceptionLog(err, $"query: {sqlstr} occur error.");
yield break;
}
for (int i = 0; i < sqlstr.Count(); i++)
{
yield return(rst.Read());
}
if (globalTrans == null)
{
_dbe.DBConnection.Close();
}
}
/// <summary>
/// Clears the temporary directory.
/// </summary>
private void ClearTempDir()
{
try
{
DirectoryInfo directoryInfo = new(AppDirs.TempDir);
if (directoryInfo.Exists)
{
foreach (DirectoryInfo subdirInfo in directoryInfo.EnumerateDirectories())
{
subdirInfo.Delete(true);
}
foreach (FileInfo fileInfo in directoryInfo.EnumerateFiles())
{
fileInfo.Delete();
}
}
}
catch (Exception ex)
{
ErrLog.WriteException(ex, Locale.IsRussian ?
"Ошибка при очистке директории временных файлов" :
"Error cleaning the directory of temporary files");
}
}
public int Insert(ErrLog entityToInsert)
{
using (Conn)
{
return(Conn.Insert <int>(entityToInsert));
}
}
/// <summary>
/// Writes the error to the log and displays a error message.
/// </summary>
public void ProcError(Exception ex, string text = "", params object[] args)
{
string msg = ScadaUtils.BuildErrorMessage(ex, text, args);
ErrLog.WriteMessage(msg, LogMessageType.Exception);
ScadaUiUtils.ShowError(msg);
}
public int Update(ErrLog entityToInsert)
{
using (Conn)
{
return(Conn.Update(entityToInsert));
}
}
/// <summary>
/// 刪除資料列
/// </summary>
/// <param name="rowdata"></param>
/// <returns></returns>
public bool Delete(T rowdata)
{
DeleteCmd <T> cmd = new DeleteCmd <T>(dbEntity);
string sql = cmd.GetDeleteCmd(rowdata);
if (string.IsNullOrEmpty(sql))
{
return(false);
}
try
{
CommandDefinition cd = new CommandDefinition(commandText: sql, commandTimeout: _cmdTimeout, transaction: globalTrans);
cmd.Connection.Execute(sql);
return(true);
}
catch (Exception err)
{
ErrLog.ExceptionLog(err, $"delete row from {rowdata.GetType().Name} occur error.");
return(false);
}
finally
{
if (globalTrans == null)
{
cmd.Connection.Close();
}
}
}
/// <summary>
/// 根据查询语句从数据库检索数据
/// </summary>
/// <param name="strSelect">查询语句</param>
/// <param name="SqlConn">数据库连接</param>
/// <returns>有数据则返回DataSet数据集（类似于ASP中的RecordSet），否则返回null</returns>
public static DataSet ExecuteDataSet(string sql, string filename)
{
using (SqlConnection conn = new SqlConnection(ConnectionString))
{
try
{
conn.Open();
SqlCommand cmd = new SqlCommand(sql, conn);
SqlDataAdapter mySqlDataAdapter = new SqlDataAdapter();
mySqlDataAdapter.SelectCommand = cmd;
DataSet myDS = new DataSet();
mySqlDataAdapter.Fill(myDS);
return(myDS);
}
catch (SystemException exception)
{
if (sql.Length > 500)
{
sql = sql.Substring(0, 500) + "...";
}
ErrLog.HandleException(filename, "ExecuteDataSet(" + sql + ")：" + exception.Message);
return(null);
}
finally
{
if (conn.State.Equals(ConnectionState.Open))
{
conn.Close();
}
}
}
}
public int Delete(ErrLog entityToInsert)
{
using (Conn)
{
return(Conn.Delete(entityToInsert));
}
}
/// <summary>
/// Writes the error to the log and displays a error message.
/// </summary>
public void ProcError(Exception ex, string message = null)
{
ErrLog.WriteException(ex, message);
ScadaUiUtils.ShowError(string.IsNullOrEmpty(message)
? ex.Message
: message + ":" + Environment.NewLine + ex.Message);
}
public void execNonQuery(object command)
{
OpenConn();
var cmd = command as SqlCommand;
if (cmd != null)
{
cmd.Connection = conn;
}
if (cmd == null)
{
cmd = new SqlCommand(command.ToString(), conn);
}
try
{
cmd.ExecuteNonQuery();
}
catch (Exception ex)
{
ErrLog.CreateLog(this, ex, cmd.CommandText);
throw;
}
CloseConn();
}
public override global::System.Data.DataSet Clone()
{
ErrLog cln = ((ErrLog)(base.Clone()));
cln.InitVars();
cln.SchemaSerializationMode = this.SchemaSerializationMode;
return(cln);
}
protected override void OnException(ExceptionContext filterContext)
{
filterContext.ExceptionHandled = true;
Exception ex = filterContext.Exception;
string controller = filterContext.RouteData.Values["controller"].ToString();
string action = filterContext.RouteData.Values["action"].ToString();
ErrLog.WriteLog(ex, controller, action);
filterContext.Result = View("Error");
}
public static global::System.Xml.Schema.XmlSchemaComplexType GetTypedDataSetSchema(global::System.Xml.Schema.XmlSchemaSet xs)
{
ErrLog ds = new ErrLog();
global::System.Xml.Schema.XmlSchemaComplexType type = new global::System.Xml.Schema.XmlSchemaComplexType();
global::System.Xml.Schema.XmlSchemaSequence sequence = new global::System.Xml.Schema.XmlSchemaSequence();
global::System.Xml.Schema.XmlSchemaAny any = new global::System.Xml.Schema.XmlSchemaAny();
any.Namespace = ds.Namespace;
sequence.Items.Add(any);
type.Particle = sequence;
global::System.Xml.Schema.XmlSchema dsSchema = ds.GetSchemaSerializable();
if (xs.Contains(dsSchema.TargetNamespace))
{
global::System.IO.MemoryStream s1 = new global::System.IO.MemoryStream();
global::System.IO.MemoryStream s2 = new global::System.IO.MemoryStream();
try {
global::System.Xml.Schema.XmlSchema schema = null;
dsSchema.Write(s1);
for (global::System.Collections.IEnumerator schemas = xs.Schemas(dsSchema.TargetNamespace).GetEnumerator(); schemas.MoveNext();)
{
schema = ((global::System.Xml.Schema.XmlSchema)(schemas.Current));
s2.SetLength(0);
schema.Write(s2);
if ((s1.Length == s2.Length))
{
s1.Position = 0;
s2.Position = 0;
for (; ((s1.Position != s1.Length) &&
(s1.ReadByte() == s2.ReadByte()));)
{
;
}
if ((s1.Position == s1.Length))
{
return(type);
}
}
}
}
finally {
if ((s1 != null))
{
s1.Close();
}
if ((s2 != null))
{
s2.Close();
}
}
}
xs.Add(dsSchema);
return(type);
}
/// <summary>
/// 新增資料集
/// </summary>
/// <param name="rowdata"></param>
/// <returns></returns>
public bool Insert(List <T> rowdata)
{
InsertCmd <T> cmd = new InsertCmd <T>(dbEntity);
IDbTransaction trans = globalTrans ?? cmd.Connection.BeginTransaction();
try
{
string str = "";
foreach (T row in rowdata)
{
str += cmd.GetInsertCmd(row);
}
if (string.IsNullOrEmpty(str))
{
return(false);
}
try
{
CommandDefinition cd = new CommandDefinition(commandText: str.ToString(), transaction: trans, commandTimeout: _cmdTimeout);
cmd.Connection.Execute(cd);
if (globalTrans == null)
{
trans.Commit();
}
return(true);
}
catch (Exception err)
{
ErrLog.ExceptionLog(err, $"insert rows to table {rowdata.GetType().Name} occur error.");
if (globalTrans == null)
{
trans.Rollback();
}
throw err;
}
finally
{
if (globalTrans == null)
{
trans.Dispose();
cmd.Connection.Close();
}
}
}
catch (Exception err)
{
ErrLog.ExceptionLog(err, $"insert commands occur error.");
throw err;
}
}
/// <summary>
///
/// </summary>
/// <param name="ErrType">错误类型</param>
/// <param name="ex">异常错误,如果此项为NULL，则需写后三项</param>
/// <param name="errClass">错误类型</param>
/// <param name="errMethod">错误方法</param>
/// <param name="errContent">错误内容</param>
public static void AddErrLog(ErrLogType ErrType, Exception ex, string errClass = null, string errMethod = null, string errContent = null)
{
try
{
ErrLogRepository repo = new ErrLogRepository();
ErrLog errLog = new ErrLog();
errLog.ErrTime = DateTime.Now;
errLog.ErrType = (int)ErrType;
errLog.ErrClass = ex == null ? errClass : ex.TargetSite.ReflectedType.ToString();
errLog.ErrMethod = ex == null ? errMethod : ex.TargetSite.Name;
errLog.ErrContent = ex == null ? errContent : ex.ToString();
repo.Insert(errLog);
}
catch
{
}
}
bool OpenConn()
{
try
{
sb.AsynchronousProcessing = true;
conn = new SqlConnection(sb.ToString());
conn.Open();
//throw new Exception("test ex");
}
catch (Exception e)
{
ErrLog.CreateLog(this, e, conn.ConnectionString);
CloseConn();
return(false);
}
return(true);
}
/// <summary>
/// 添加、更新、删除
/// </summary>
/// <param name="sql"></param>
/// <returns></returns>
public static int ExecuteNonQuery(string sql, string filename)
{
lock (LockAddOrUpdate)
{
if (sql.Trim().Length == 0)
{
return(0);
}
using (SqlConnection conn = new SqlConnection(ConnectionString))
{
conn.Open();
SqlCommand cmd = new SqlCommand(sql, conn);
SqlTransaction trans = conn.BeginTransaction();
cmd.Transaction = trans;
try
{
cmd.CommandType = CommandType.Text;
cmd.CommandTimeout = 90;
cmd.ExecuteNonQuery();
trans.Commit();
return(0);
}
catch (SystemException exception)
{
trans.Rollback();
if (sql.Length > 500)
{
sql = sql.Substring(0, 500) + "...";
}
ErrLog.HandleException(filename, "ExecuteNonQuery(" + sql + ")：" + exception.Message);
return(-1);
}
finally
{
if (conn.State.Equals(ConnectionState.Open))
{
conn.Close();
}
}
}
}
}
/// <summary>
/// 刪除資料集
/// </summary>
/// <param name="rowdata"></param>
/// <returns></returns>
public bool Delete(List <T> rowdata)
{
DeleteCmd <T> cmd = new DeleteCmd <T>(dbEntity);
IDbTransaction trans = globalTrans ?? cmd.Connection.BeginTransaction();
StringBuilder sql = new StringBuilder();
foreach (T row in rowdata)
{
sql.Append(cmd.GetDeleteCmd(row));
}
if (string.IsNullOrEmpty(sql.ToString()))
{
return(false);
}
try
{
CommandDefinition cd = new CommandDefinition(commandText: sql.ToString(), transaction: trans, commandTimeout: _cmdTimeout);
cmd.Connection.Execute(cd);
if (globalTrans == null)
{
trans.Commit();
}
return(true);
}
catch (Exception err)
{
ErrLog.ExceptionLog(err, $"delete rows from {rowdata.GetType().Name} occur error.");
if (globalTrans == null)
{
trans.Rollback();
}
return(false);
}
finally
{
if (globalTrans == null)
{
trans.Dispose();
cmd.Connection.Close();
}
}
}
/// <summary>
/// Gets a user interface object for the device.
/// </summary>
public bool TryGetKPView(Settings.KP kp, bool common, SortedList <string, string> customParams,
out KPView kpView, out string errMsg)
{
try
{
string dllPath = Path.Combine(AppDirs.KPDir, kp.Dll);
kpView = common ?
GetKPView(dllPath) :
GetKPView(dllPath, kp.Number, new KPView.KPProperties(customParams, kp.CmdLine));
errMsg = null;
return(true);
}
catch (Exception ex)
{
ErrLog.WriteException(ex);
kpView = null;
errMsg = ex.Message;
return(false);
}
}
/// <summary>
/// 呼叫預儲程序方法
/// </summary>
/// <typeparam name="T">回傳型別</typeparam>
/// <param name="spname">程序名</param>
/// <param name="param">參數</param>
/// <returns></returns>
public async Task <IEnumerable <T> > GetSPResult <T>(string spname, object param)
{
try
{
var rst = await _dbe.DBConnection.QueryAsync <T>(spname, param, commandType : System.Data.CommandType.StoredProcedure, transaction : globalTrans);
return(rst);
}
catch (Exception err)
{
ErrLog.ExceptionLog(err, $"exec sp:{spname} occur error.");
return(null);
}
finally
{
if (globalTrans == null)
{
_dbe.DBConnection.Close();
}
}
}
/// <summary>
/// Loads the application configuration.
/// </summary>
private void LoadAppConfig()
{
string fileName = Path.Combine(AppDirs.ConfigDir, AdminConfig.DefaultFileName);
if (File.Exists(fileName))
{
// load existing configuration
if (!AppConfig.Load(fileName, out string errMsg))
{
ErrLog.WriteError(errMsg);
}
}
else
{
// use default configuration
AppConfig.SetToDefault(AppDirs.InstanceDir);
if (!AppConfig.Save(fileName, out string errMsg))
{
ErrLog.WriteError(errMsg);
}
}
}
public static SqlDataReader ExecuteStoredProcWithReader(string spName, SqlParameter[] parameters, string filename)
{
try
{
SqlConnection conn = new SqlConnection(ConnectionString);
conn.Open();
SqlCommand cmd = new SqlCommand(spName, conn);
cmd.CommandType = CommandType.StoredProcedure;
if (parameters != null)
{
for (int i = 0; i < parameters.Length; i++)
{
cmd.Parameters.Add(parameters[i]);
}
}
return(cmd.ExecuteReader(CommandBehavior.CloseConnection));
}
catch (SqlException exception)
{
ErrLog.HandleException(filename, "ExecuteStoredProcWithReader(" + spName + ")：" + exception.Message);
throw new SystemException(exception.Message);
}
}
public List <T> Select(T rowdata)
{
SelectCmd <T> cmd = new SelectCmd <T>(dbEntity);
var aloneconn = dbEntity.db.CreateConnection();
var trans = aloneconn.BeginTransaction(IsolationLevel.ReadUncommitted);
string sql = cmd.GetSelectCmd(rowdata);
try
{
var rtn = aloneconn.Query <T>(sql, transaction: trans);
trans.Commit();
return(rtn.ToList());
}
catch (Exception err)
{
ErrLog.ExceptionLog(err, $"select commands occur error.");
throw err;
}
finally
{
aloneconn.Close();
}
}
public static DataSet ExecuteStoredProcWithDataSet(string spName, ref SqlParameter[] parameters, string filename)
{
using (SqlConnection conn = new SqlConnection(ConnectionString))
{
try
{
conn.Open();
SqlDataAdapter da = new SqlDataAdapter();
DataSet ds = new DataSet();
SqlCommand cmd = new SqlCommand(spName, conn);
cmd.CommandType = CommandType.StoredProcedure;
if (parameters != null)
{
for (int i = 0; i < parameters.Length; i++)
{
cmd.Parameters.Add(parameters[i]);
}
}
da.SelectCommand = cmd;
da.Fill(ds);
return(ds);
}
catch (SqlException exception)
{
ErrLog.HandleException(filename, "ExecuteStoredProcWithDataSet(" + spName + ")：" + exception.Message);
return(null);
//throw new SystemException(exception.Message);
}
finally
{
if (conn.State.Equals(ConnectionState.Open))
{
conn.Close();
}
}
}
}
/// <summary>
/// 查询（注意使用完需关闭dr）
/// </summary>
/// <param name="sql">查询语句</param>
/// <returns>SqlDataReader</returns>
public static SqlDataReader ExecuteReader(string sql, string filename)
{
SqlConnection conn = new SqlConnection(ConnectionString);
try
{
conn.Open();
SqlCommand cmd = new SqlCommand(sql, conn);
return(cmd.ExecuteReader(CommandBehavior.CloseConnection));
}
catch (SystemException exception)
{
if (conn.State == ConnectionState.Open)
{
conn.Close();
}
if (sql.Length > 500)
{
sql = sql.Substring(0, 500) + "...";
}
ErrLog.HandleException(filename, "ExecuteReader(" + sql + ")：" + exception.Message);
return(null);
}
}
/// <summary>
/// 更新資料列
/// </summary>
/// <param name="rowdata"></param>
/// <returns></returns>
public int Update(T rowdata)
{
UpdateCmd <T> cmd = new UpdateCmd <T>(dbEntity);
try
{
var str = cmd.GetUpdateCmd(rowdata);
if (string.IsNullOrEmpty(str))
{
return(-1);
}
try
{
CommandDefinition cd = new CommandDefinition(commandText: str.ToString(), transaction: globalTrans, commandTimeout: _cmdTimeout);
var row = cmd.Connection.Execute(cd);
return(row);
}
catch (Exception err)
{
ErrLog.ExceptionLog(err, $"update table {rowdata.GetType().Name} occur error.");
throw err;
}
finally
{
if (globalTrans == null)
{
cmd.Connection.Close();
}
}
}
catch (Exception err)
{
ErrLog.ExceptionLog(err, $"update command occur error.");
throw err;
}
}