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