/// <summary>
/// Gets the rows total.
/// </summary>
/// <typeparam name="T"></typeparam>
/// <param name="whereClause">The where clause.</param>
/// <returns></returns>
public int GetRowsTotal <T>(string whereClause = "") where T : new()
{
int returnValue = 0;
SQLiteDataReader reader = null;
try
{
var objectType = typeof(T);
var properties = objectType.GetProperties(BindingFlags.Public | BindingFlags.Instance);
string sqlQuery = SQLiteQuery.CountTotal(objectType.Name, whereClause);
reader = SQLiteQuery.ExecuteReader(sqlQuery, this.Connection);
while (reader.Read())
{
returnValue = Convert.ToInt32(reader[Constant.TotalCount]);
}
}
catch (Exception exception)
{
APILog.Error(this, "GetRowsTotal", exception);
}
finally
{
reader.Close();
reader = null;
}
return(returnValue);
}
/// <summary>
/// Inserts the row.
/// </summary>
/// <param name="insertObj">The insert object.</param>
/// <returns></returns>
public int InsertRow(object obj)
{
int result = 0;
InsertObject insertObject = null;
string sqlInsert = null;
try
{
insertObject = InsertObject.CreateInstance(obj);
sqlInsert = string.Format(StringFormat.SqlInsert, insertObject.Table, insertObject.Columns, insertObject.Values);
result = SQLiteQuery.ExecuteNonQuery(sqlInsert, this.Connection);
if (result > 0)
{
this.HandleByteArrayList(insertObject.Table, insertObject.ByteArrayColumns, insertObject.ByteArrayValues, insertObject.KeyString);
}
}
catch (Exception exception)
{
APILog.Error(this, "InsertRow", exception);
}
finally
{
insertObject = null;
sqlInsert = null;
}
return(result);
}
public async Task SingleInsertToContainer()
{
var aa = new APILog()
{
APIName = "1",
CreateTime = DateTime.Now
};
await logRep.InsertAsync(aa);
}
/// <summary>
/// Executes the non query.
/// </summary>
/// <param name="sqlQuery">The SQL query.</param>
/// <param name="connection">The connection.</param>
/// <returns></returns>
public static int ExecuteNonQuery(string sqlQuery, SQLiteConnection connection)
{
using (var command = new SQLiteCommand(sqlQuery, connection))
{
APILog.Debug(typeof(SQLiteQuery), "ExecuteNonQuery", sqlQuery);
return(command.ExecuteNonQuery());
}
}
/// <summary>
/// Executes the reader.
/// </summary>
/// <param name="sqlQuery">The SQL query.</param>
/// <param name="connection">The connection.</param>
/// <returns></returns>
public static SQLiteDataReader ExecuteReader(string sqlQuery, SQLiteConnection connection)
{
using (var command = new SQLiteCommand(sqlQuery, connection))
{
APILog.Debug(typeof(SQLiteQuery), "ExecuteReader", sqlQuery);
return(command.ExecuteReader());
}
}
/// <summary>
/// Gets the column maximum value.
/// </summary>
/// <typeparam name="T"></typeparam>
/// <param name="tableName">Name of the table.</param>
/// <param name="columnName">Name of the column.</param>
/// <returns></returns>
public object GetColumnMaxValue <T>(string columnName)
{
object returnValue = null;
SQLiteDataReader reader = null;
try
{
Type type = typeof(T);
var propertyList = type.GetProperties(BindingFlags.Public | BindingFlags.Instance);
string sqlQuery = SQLiteQuery.MaxFromTable(type.Name, columnName);
reader = SQLiteQuery.ExecuteReader(sqlQuery, this.Connection);
while (reader.Read())
{
returnValue = reader[Constant.MaxValue].ToString();
}
var property = propertyList.FirstOrDefault(x => x.Name == columnName);
if (property != null)
{
if (string.IsNullOrWhiteSpace(returnValue.ToString()))
{
if (property.PropertyType.IsNumber())
{
returnValue = 0;
}
else if (property.PropertyType.IsDateTime())
{
returnValue = DateTime.MinValue;
}
}
returnValue = Convert.ChangeType(returnValue, property.PropertyType);
}
}
catch (Exception exception)
{
APILog.Error(this, "GetColumnMaxValue", exception);
}
finally
{
if (reader != null)
{
reader.Close();
reader = null;
}
}
return(returnValue);
}
/// <summary>
/// Selects all rows.
/// </summary>
/// <typeparam name="T"></typeparam>
/// <param name="whereClause">The where clause.</param>
/// <returns></returns>
public List <T> SelectAllRows <T>(string whereClause = "") where T : new()
{
var objectList = new List <T>();
SQLiteDataReader reader = null;
try
{
var objectType = typeof(T);
var properties = objectType.GetProperties(BindingFlags.Public | BindingFlags.Instance);
string sqlQuery = SQLiteQuery.SelectAllFrom(objectType.Name, whereClause);
reader = SQLiteQuery.ExecuteReader(sqlQuery, this.Connection);
while (reader.Read())
{
var newObject = new T();
foreach (var property in properties)
{
if (property.IsDataMember())
{
if (reader[property.Name].HaveContent() || property.PropertyType.IsString())
{
Type type = property.PropertyType;
TypeExtension.GetDataType(type, out type);
object propertyValue = reader[property.Name];
dynamic changedObject = Convert.ChangeType(propertyValue, type);
property.SetValue(newObject, changedObject, null);
}
}
}
objectList.Add(newObject);
}
}
catch (Exception exception)
{
APILog.Error(this, "SelectAllRows", exception);
}
finally
{
if (reader != null)
{
reader.Close();
reader = null;
}
}
return(objectList);
}
public static void InsertIntoDatabase(APILog log)
{
try
{
var _ApplicationName = CommonHelper.GetApplicationName; //ConfigurationManager.AppSettings["ApplicationName"];
log.ApplicationName = _ApplicationName;
string dbConn = ConfigurationManager.ConnectionStrings["ApiLogger"].ToString();
SqlHelper.ExecuteNonQuery(dbConn, "LogApiCall", log.ApplicationName, log.ClientIP, log.CorrelationId,
log.Header, log.Id, log.RequestBody, log.RequestInfo, log.RequestTime, log.Response, log.ResponseStatusCode);
}
catch (Exception ex)
{
// Elmah.ErrorLog.GetDefault(HttpContext.Current).Log(new Elmah.Error(ex));
}
}
/// <summary>
/// Saves an API log
/// </summary>
/// <param name="dto">API log to save</param>
public async System.Threading.Tasks.Task SaveLog(APILog dto)
{
var timestamp = DateTime.UtcNow;
if (dto.APILogId == 0)
{
dto.TransmissionDateTime = timestamp;
LoggingDb.APILogs.Add(dto);
}
else
{
LoggingDb.APILogs.Attach(dto);
dto.ResponseDateTime = timestamp;
LoggingDb.Entry(dto).State = Microsoft.EntityFrameworkCore.EntityState.Modified;
}
await LoggingDb.SaveChangesAsync();
}
/// <summary>
/// 保存错误日志
/// </summary>
/// <param name="model"></param>
public void SaveLog(APILogModel model)
{
APILog log = new APILog()
{
SiteID = 0,
Game = model.Game,
Url = model.Url,
PostData = model.PostData,
ResultData = model.ResultData,
Status = model.Status,
Time = model.Time,
CreateAt = model.CreateAt
};
using (DbExecutor db = NewExecutor())
{
log.Add(db);
}
}
/// <summary>
/// Closes the database connection.
/// </summary>
/// <returns></returns>
public bool CloseDbConnection()
{
bool result = true;
try
{
if (this.Connection.State != ConnectionState.Closed)
{
this.Connection.Close();
}
}
catch (Exception exception)
{
APILog.Error(this, "CloseDbConnection", exception);
result = false;
}
finally
{
this.Connection.Dispose();
this.Connection = null;
}
return(result);
}
/// <summary>
/// Creates the table.
/// </summary>
/// <typeparam name="T"></typeparam>
public void CreateTable <T>()
{
try
{
int aux = 1;
List <string> keyColumnList = new List <string>();
var objectType = typeof(T);
var propertyList = objectType.GetProperties(BindingFlags.Public | BindingFlags.Instance);
StringBuilder sbCreateTable = new StringBuilder();
sbCreateTable.Append("CREATE TABLE IF NOT EXISTS ");
sbCreateTable.Append(objectType.Name);
sbCreateTable.Append("(");
foreach (var property in propertyList)
{
if (property.IsDataMember())
{
if (property.IsKey())
{
keyColumnList.Add(property.Name);
}
sbCreateTable.Append(property.Name);
sbCreateTable.Append(" ");
sbCreateTable.Append(property.PropertyType.ToSQLiteDataType());
if (property.PropertyType.IsNull())
{
sbCreateTable.Append(" NULL");
}
else
{
sbCreateTable.Append(" NOT NULL");
}
sbCreateTable.Append(",");
}
}
if (keyColumnList.Count > 0)
{
sbCreateTable.Append("PRIMARY KEY (");
foreach (string key in keyColumnList)
{
sbCreateTable.Append(key);
if (aux < keyColumnList.Count)
{
sbCreateTable.Append(",");
}
aux++;
}
sbCreateTable.Append("));");
}
else
{
sbCreateTable.Remove((sbCreateTable.Length - 1), 1);
sbCreateTable.Append(");");
}
SQLiteQuery.ExecuteNonQuery(sbCreateTable.ToString(), this.Connection);
}
catch (Exception exception)
{
APILog.Error(this, "CreateTable", exception);
}
}
public async Task <APILog> LogResponse(APILog log)
{
return(await LogDataProvider.LogResponse(log));
}
public async Task <APILog> LogRequest(APILog log)
{
return(await LogDataProvider.LogRequest(log));
}
/// <summary>
/// Deactivates the logs.
/// </summary>
public static void DeactivateLogs()
{
APILog.DeactivateLog();
}
/// <summary>
/// Activates the logs.
/// </summary>
public static void ActivateLogs()
{
APILog.ActivateLog();
}
