public void Update(Course course, UnitOfWork uow)
{
try
{
using (var command = uow.CreateCommand())
{
command.CommandText = $"UPDATE {CoursesContract.TABLE_NAME}" +
$"SET [{CoursesContract.DESCRIPTION}] = @DESCRIPTION, [{CoursesContract.REF_YEAR}] = @REF_YEAR," +
$"[{CoursesContract.START_DATE}] = @START_DATE, [{CoursesContract.END_DATE}] = @END_DATE, " +
$"[{CoursesContract.IS_PERIODIC}] = @IS_PERIODIC, [{CoursesContract.COORDINATOR_ID}] = @COORDINATOR_ID " +
$"WHERE [{CoursesContract.ID}] = @ID";
command.AddParameter("DESCRIPTION", course.Description ?? (object)DBNull.Value);
command.AddParameter("REF_YEAR", course.RefYear);
command.AddParameter("START_DATE", course.StartDate ?? (object)DBNull.Value);
command.AddParameter("END_DATE", course.EndDate ?? (object)DBNull.Value);
command.AddParameter("IS_PERIODIC", course.IsPeriodic);
command.AddParameter("COORDINATOR_ID", course.CoordinatorId);
command.AddParameter("ID", course.Id);
command.ExecuteNonQuery();
}
}
catch (Exception ex)
{
DbLog.LogError($"Error updating course! {course.ToString()}", ex);
throw ex;
}
}
/// <summary>
/// 根据SQL返回影响行数,带参数
/// </summary>
/// <param name="sql">sql语句</param>
/// <param name="param">参数化</param>
/// <returns></returns>
public object GetObjectValue(StringBuilder sql, SqlParam[] param)
{
object result = null;
//创建连接
SQLiteConnection conn = this.GetInstance();
//创建指令
SQLiteCommand cmd = new SQLiteCommand(sql.ToString(), conn);
cmd.Parameters.AddRange(param);
try
{
//打开连接
conn.Open();
result = cmd.ExecuteScalar();
}
catch (Exception e)
{
DbLog.WriteException(e);
}
finally
{
conn.Close();
conn.Dispose();
}
return(result);
}
/// <summary>
/// Return null if not found
/// </summary>
/// <param name="id"></param>
/// <param name="uow"></param>
/// <returns></returns>
public Course GetCourseByID(int id, UnitOfWork uow)
{
try
{
using (var command = uow.CreateCommand())
{
command.CommandText = $"SELECT * FROM {CoursesContract.TABLE_NAME} WHERE [{CoursesContract.ID}] = @id";
command.AddParameter("ID", id);
using (var reader = command.ExecuteReader())
{
if (reader.Read())
{
var result = Map(reader);
return(result);
}
return(null);
}
}
}
catch (Exception ex)
{
DbLog.LogError($"Error retriveing course by id {id}", ex);
throw ex;
}
}
public void Create(Course course, UnitOfWork uow)
{
try
{
using (var command = uow.CreateCommand())
{
command.CommandText = $"INSERT INTO {CoursesContract.TABLE_NAME} ([{CoursesContract.DESCRIPTION}], [{CoursesContract.REF_YEAR}]," +
$"[{CoursesContract.START_DATE}],[{CoursesContract.END_DATE}]," +
$"[{CoursesContract.IS_PERIODIC}],[{CoursesContract.COORDINATOR_ID}])" +
$"VALUES(@DESCRIPTION, @REF_YEAR, @START_DATE, @END_DATE, @IS_PERIODIC, @COORDINATOR_ID)";
command.AddParameter("DESCRIPTION", course.Description ?? (object)DBNull.Value);
command.AddParameter("REF_YEAR", course.RefYear);
command.AddParameter("START_DATE", course.StartDate ?? (object)DBNull.Value);
command.AddParameter("END_DATE", course.EndDate ?? (object)DBNull.Value);
command.AddParameter("IS_PERIODIC", course.IsPeriodic);
command.AddParameter("COORDINATOR_ID", course.CoordinatorId);
command.ExecuteNonQuery();
}
}
catch (Exception ex)
{
DbLog.LogError($"Error inserting the new course: {course.ToString()}", ex);
throw ex;
}
}
/// <summary>
/// 返回分页Dictionary<string, object>
/// </summary>
/// <param name="item"></param>
/// <param name="pModel"></param>
/// <returns></returns>
public PageResult ToPageDic(PageModel pModel, DataContext db = null, bool isOutSql = false)
{
var result = new DataReturn();
var stopwatch = new Stopwatch();
if (Query.Predicate.Exists(a => a.IsSuccess == false))
{
return(result.PageResult);
}
stopwatch.Start();
if (db == null)
{
using (var tempDb = new DataContext(Query.Key))
{
result = tempDb.GetPage(Query, pModel);
}
}
else
{
result = db.GetPage(Query, pModel);
}
stopwatch.Stop();
Query.Config.IsOutSql = Query.Config.IsOutSql ? Query.Config.IsOutSql : isOutSql;
DbLog.LogSql(Query.Config.IsOutSql, result.Sql, Query.Config.DbType, stopwatch.Elapsed.TotalMilliseconds);
return(result.PageResult);
}
/// <summary>
/// 返回List<Dictionary<string, object>>
/// </summary>
/// <param name="item"></param>
/// <returns></returns>
public List <Dictionary <string, object> > ToDics(DataContext db = null, bool isOutSql = false)
{
var result = new DataReturn();
var stopwatch = new Stopwatch();
if (Query.Predicate.Exists(a => a.IsSuccess == false))
{
return(result.DicList);
}
stopwatch.Start();
if (db == null)
{
using (var tempDb = new DataContext(Query.Key))
{
result = tempDb.GetDic(Query);
}
}
else
{
result = db.GetDic(Query);
}
stopwatch.Stop();
Query.Config.IsOutSql = Query.Config.IsOutSql ? Query.Config.IsOutSql : isOutSql;
DbLog.LogSql(Query.Config.IsOutSql, result.Sql, Query.Config.DbType, stopwatch.Elapsed.TotalMilliseconds);
return(result.DicList);
}
/// <summary>
/// 返回item<R>
/// </summary>
/// <typeparam name="T"></typeparam>
/// <param name="item"></param>
/// <returns></returns>
public R ToItem <R>(DataContext db = null, bool isOutSql = false) where R : class, new()
{
var result = new DataReturn <R>();
var stopwatch = new Stopwatch();
if (Query.Predicate.Exists(a => a.IsSuccess == false))
{
return(result.item);
}
stopwatch.Start();
Query.Take = 1;
if (db == null)
{
using (var tempDb = new DataContext(Query.Key))
{
result = tempDb.GetList <R>(Query);
}
}
else
{
result = db.GetList <R>(Query);
}
stopwatch.Stop();
Query.Config.IsOutSql = Query.Config.IsOutSql ? Query.Config.IsOutSql : isOutSql;
DbLog.LogSql(Query.Config.IsOutSql, result.sql, Query.Config.DbType, stopwatch.Elapsed.TotalMilliseconds);
return(result.item);
}
public static string DealException(Exception ex, bool writeLog)
{
if (ex == null)
{
return("unknown");
}
var msg = "unknown";
if (ex is HttpException)
{
int code = (ex as HttpException).GetHttpCode();
if (code != 500)
{
writeLog = false;
}
if (ex.InnerException != null)
{
ex = ex.InnerException;
}
msg = code.ToString();
}
if (ex is HttpRequestValidationException)
{
ex = new XSSException(GetClientIP(HttpContext.Current), ex);
msg = "xss";
}
if (writeLog)
{
DbLog.Append(ex);
}
return(msg);
}
public Vector3 GetSpawnPointOffset()
{
Vector3 spawnPoint = currentBaseAttachment.transform.position - transform.position;
DbLog.LogFormat("local offset is {0}", spawnPoint);
return(spawnPoint);
}
/// <summary>
/// 根据 SQL 返回 IDataReader
/// </summary>
/// <param name="sql">sql语句</param>
/// <param name="param">参数化</param>
/// <returns>IDataReader</returns>
public IDataReader GetIDataReaderBySql(StringBuilder sql, SqlParam[] param)
{
try
{
using (SQLiteConnection conn = this.GetInstance())
{
SQLiteCommand cmd = new SQLiteCommand(sql.ToString(), conn);
try
{
cmd.CommandTimeout = CommandTimeOut;
cmd.CommandType = CommandType.Text;
cmd.Parameters.AddRange(param);
return(cmd.ExecuteReader(CommandBehavior.CloseConnection));
}
catch
{
return(null);
}
finally
{
cmd.Dispose();
conn.Close();
conn.Dispose();
}
}
}
catch (Exception e)
{
DbLog.WriteException(e);
return(null);
}
}
/// <summary>
/// 根据SQL返回影响行数,带参数
/// </summary>
/// <param name="sql">sql语句</param>
/// <param name="param">参数化</param>
/// <returns></returns>
public object GetObjectValue(StringBuilder sql, SqlParam[] param)
{
object result = null;
//创建连接
MySqlConnection conn = this.GetInstance();
//创建指令
dbCommand = new MySqlCommand(sql.ToString(), conn);
DbCommon.MySqlAddInParameter(dbCommand, param);
try
{
//打开连接
conn.Open();
result = dbCommand.ExecuteScalar();
}
catch (Exception e)
{
DbLog.WriteException(e);
}
finally
{
conn.Close();
conn.Dispose();
}
return(result);
}
/// <summary>
/// Unused Currently.
/// Extracts xml formatted properties from serilog mssql logs
/// </summary>
/// <param name="log"></param>
protected string ExtractPropertiesFromLog(DbLog log)
{
var settings = new XmlReaderSettings
{
Async = true
};
var propertyDict = new Dictionary <string, string>();
try
{
propertyDict = RegexUtilities.DirtyXmlPropertyParser(log.Properties);
if (propertyDict.Count == 0)
{
return(log.MessageTemplate);
}
}
catch (Exception ex)
{
Console.WriteLine(ex);
}
var s = new StringBuilder(log.MessageTemplate);
var match = Helpers.RegexUtilities.StringInterpolationHelper.Match(log.MessageTemplate);
while (match.Success)
{
if (propertyDict.ContainsKey(match.Value))
{
s.Replace($"{{{match.Value}}}", propertyDict[match.Value]);
}
match = match.NextMatch();
}
return(s.ToString());
}
// Main code middleware code.
public async Task InvokeAsync(HttpContext httpContext, DbCtx ctx)
{
string apiKey = httpContext.Request.Headers["ApiKey"];
string requestedEndpoint = httpContext.Request.Path;
DbLog log = new DbLog(requestedEndpoint);
// Log the request.
User user = ctx.Users.Find(apiKey);
if (user != null)
{
// Log request against a registered user.
user.DbLogs.Add(log);
}
else
{
// Log request from unrecognised users.
string guid = Guid.NewGuid().ToString();
log.Id = "unknown-" + guid;
ctx.DbLogs.Add(log);
}
ctx.SaveChanges();
await _next(httpContext);
}
internal static async Task AddLogMessageAsync(GlobeChatContext _context, string Message)
{
var dblog = new DbLog();
dblog.Message = Message;
_context.DbLog.Add(dblog);
await _context.SaveChangesAsync();
}
protected void OnDestroy()
{
if (ClusterDestroyed != null)
{
ClusterDestroyed(this);
DbLog.Log(string.Format("Cluster {0} destroyed", this), Color.green, this);
}
}
internal static void AddLogMessage(GlobeChatContext _context, string Message)
{
var dblog = new DbLog();
dblog.Message = Message;
_context.DbLog.Add(dblog);
_context.SaveChanges();
}
public void SwitchFreeze()
{
isFreeze = !isFreeze;
foreach (BlockCluster blockCluster in clusterList)
{
blockCluster.rigidbodyComponent.isKinematic = isFreeze;
}
DbLog.Log(string.Format("Freeze switched to {0} state", isFreeze), Color.blue, this);
}
public void SwitchGravity()
{
useGravity = !useGravity;
foreach (BlockCluster blockCluster in clusterList)
{
blockCluster.rigidbodyComponent.useGravity = useGravity;
}
DbLog.Log(string.Format("Gravity switched to {0} state", useGravity), Color.blue, this);
}
protected Block[] LoadBlockPrefabs()
{
Block[] blockArray = Resources.LoadAll <Block>(blockPrefabsPath);
if (blockArray == null || blockArray.Length == 0)
{
DbLog.LogWarning(string.Format("Block prefabs not found at path {0}", blockPrefabsPath), this);
}
return(blockArray);
}
public IEnumerable <string> GetErrors()
{
var log = new DbLog(Settings.Settings.Current.Building.BuilderConnectionString);
foreach (var error in log.GetErrors(Settings.Settings.Current.Building.Id.Value))
{
yield return(error);
}
}
private void UpdateScope(User source, ToDoTask task, SCOPE scope)
{
task.Scope = scope;
this.Update(null, task);
var log = new DbLog(DateTime.Now, ACTION_TARGET.TASK, ACTION.CHANGE_SCOPE, CHANGE_FIELD.TITLE, source.Id, task.Id, task.Title, null, EnumConverter.Convert(scope));
_context.DbLogs.Add(log);
_context.SaveChanges();
}
private void UpdateStatus(User source, ToDoTask task, STATUS status)
{
task.Status = status;
this.Update(null, task);
var log = new DbLog(DateTime.Now, ACTION_TARGET.TASK, ACTION.CHANGE_STATUS, CHANGE_FIELD.TITLE, source.Id, task.Id, task.Title, EnumConverter.Convert(status), null);
_context.DbLogs.Add(log);
_context.SaveChanges();
}
private void UpdateStatus(User source, User entity, USER_STATUS status)
{
entity.Status = status;
this.Update(null, entity);
var log = new DbLog(DateTime.Now, ACTION_TARGET.USER, ACTION.CHANGE_STATUS, CHANGE_FIELD.TITLE, source.Id, entity.Id, entity.Name, EnumConverter.Convert(status), null);
_context.DbLogs.Add(log);
_context.SaveChanges();
}
protected BlockConfigPanelController LoadPanelPrefab(BlockConfigPanelParameters parameters)
{
string path = string.Format("Prefabs/ModalWindows/BlockConfigModalPanels/{0}", parameters.Type);
BlockConfigPanelController panelController = Resources.Load <BlockConfigPanelController>(path);
if (panelController == null)
{
DbLog.LogWarningFormat("Block config panel prefab not found at path ({0})", path);
}
return(panelController);
}
public void SpawnBlock(Attachment targetAttachment)
{
if (blockPrefab == null)
{
DbLog.LogWarning("Select block type first", this);
return;
}
Block block = Instantiate(blockPrefab);
block.Init(targetAttachment, spawnBaseAttachmentIndex, spawnRotation);
}
public async Task <IActionResult> Create([Bind("Id,Message")] DbLog dbLog)
{
if (ModelState.IsValid)
{
_context.Add(dbLog);
await _context.SaveChangesAsync();
return(RedirectToAction(nameof(Index)));
}
return(View(dbLog));
}
/// <summary>
/// 批量调用存储过程
/// </summary>
/// <param name="text"></param>
/// <param name="param"></param>
/// <returns></returns>
public int BatchExecuteByProc(object[] text, object[] param)
{
int num = 0;
try
{
using (DbConnection connection = this.GetInstance().CreateConnection())
{
connection.Open();
dbTransaction = connection.BeginTransaction();
try
{
for (int i = 0; i < text.Length; i++)
{
string strtext = text[i].ToString().ToUpper().Replace("@", ":");
if (strtext != null)
{
SqlParam[] paramArray = (SqlParam[])param[i];
DbCommand command = null;
if (strtext.StartsWith("PROC_"))
{
command = this.db.GetStoredProcCommand(strtext);
}
else
{
command = this.db.GetSqlStringCommand(strtext);
}
DbCommon.AddInParameter(db, command, paramArray);
this.db.ExecuteNonQuery(command, dbTransaction);
}
}
dbTransaction.Commit();
num = 1;
}
catch (Exception e)
{
num = -1;
dbTransaction.Rollback();
DbLog.WriteException(e);
}
finally
{
connection.Close();
connection.Dispose();
dbTransaction.Dispose();
}
}
}
catch (Exception e)
{
DbLog.WriteException(e);
}
return(num);
}
public void ResetErrors()
{
var log = new DbLog(Settings.Current.Building.BuilderConnectionString);
log.Reset(Settings.Current.Building.Id.Value);
if (Builder.State == BuilderState.Error)
{
builderController.UpdateState(BuilderState.Stopped);
}
}
public void Delete(User source, T entity)
{
var log = new DbLog();
log.ExecUserId = source.Id;
log.Action = ACTION.DELETE;
this.Logging(log, entity);
_context.Set <T>().Remove(entity);
_context.SaveChanges();
}
/// <summary>
/// 批量调用存储过程
/// </summary>
/// <param name="arrayprocName"></param>
/// <param name="param"></param>
/// <returns></returns>
public int BatchExecuteByProc(object[] arrayprocName, object[] param)
{
int num = 0;
//创建连接
SQLiteConnection conn = this.GetInstance();
try
{
conn.Open();
SQLiteTransaction DbTrans = conn.BeginTransaction();
try
{
for (int i = 0; i < arrayprocName.Length; i++)
{
string procName = arrayprocName[i].ToString();
if (procName != null)
{
SqlParam[] paramArray = (SqlParam[])param[i];
//创建指令
SQLiteCommand cmd = new SQLiteCommand(procName, conn);
cmd.CommandTimeout = CommandTimeOut;
cmd.CommandType = CommandType.StoredProcedure;
cmd.Parameters.AddRange(paramArray);
cmd.Transaction = DbTrans;
num = cmd.ExecuteNonQuery();
}
}
DbTrans.Commit();
num = 1;
}
catch (Exception e)
{
DbTrans.Rollback();
num = -1;
DbLog.WriteException(e);
}
finally
{
conn.Close();
conn.Dispose();
}
}
catch (Exception e)
{
DbLog.WriteException(e);
}
finally
{
conn.Close();
conn.Dispose();
}
return(num);
}
