public SQL_STATUS DeleteTable(string name)
{
SQL_STATUS stat = SQL_STATUS.SQL_OKAY;
string sql = String.Format("DROP TABLE IF EXISTS {0};", name);
OpenConnection();
SQLiteCommand cmd = new SQLiteCommand(sql, db_connection);
try
{
cmd.ExecuteNonQuery();
}catch(SQLiteException sql_e)
{
logger.writeErrReport(String.Format("ERROR WHILE DELETING TABLE *({0})* ::{1}", name, sql_e.Message));
stat = SQL_STATUS.SQL_ERR;
}
CloseConnection();
return stat;
}
public SQL_STATUS CreateTable(string name, Dictionary<string, string> columns)
{
SQL_STATUS stat = SQL_STATUS.SQL_OKAY;
string sql = String.Format("CREATE TABLE IF NOT EXISTS {0} (", name);
//We need to retrieve the amount of elements withint the Dictionary so we can
//know when the last element has been reached in our foreach loop
int columnCount = columns.Count();
int i = 0;
foreach(KeyValuePair<string, string> pair in columns)
{
bool lastElement = false;
if (i == columnCount - 1)
{
lastElement = true;
}
//We need to add the current element in the pair to the end of the sql statement
//and verify whether or not we should enclose the statement in its ending paranthesis
//or just add another comma because the current element is not the last element
sql += pair.Key.ToString() + " " + pair.Value.ToString() + (lastElement ? ");" : ", ");
i++;
}
OpenConnection();
SQLiteCommand cmd = new SQLiteCommand(sql, db_connection);
try
{
cmd.ExecuteNonQuery();
}catch(SQLiteException sql_e)
{
logger.writeErrReport(String.Format("ERROR WHILE CREATING TABLE *({0})* ::{1}", name, sql_e.Message));
stat = SQL_STATUS.SQL_ERR;
}
CloseConnection();
return stat;
}
public SQL_STATUS DeleteRecords(string table_name, string where_condition)
{
SQL_STATUS stat = SQL_STATUS.SQL_OKAY;
string sql = String.Format("DELETE FROM {0} WHERE {1};", table_name, where_condition);
SQLiteCommand cmd = new SQLiteCommand(sql, db_connection);
OpenConnection();
try
{
cmd.ExecuteNonQuery();
}catch(SQLiteException sql_e)
{
stat = SQL_STATUS.SQL_ERR;
logger.writeErrReport(String.Format("ERROR WHILE DELETING RECORDS ON TABLE *({0})* ::{1}", table_name, sql_e.Message));
}
CloseConnection();
return stat;
}
//You will need to specify an alter type when calling this method since there are several different possibilities
public SQL_STATUS Alter(string table_name, string alter_type, string column)
{
SQL_STATUS stat = SQL_STATUS.SQL_OKAY;
string sql = String.Format("ALTER TABLE {0} {1} {2}", table_name, alter_type, column);
SQLiteCommand cmd = new SQLiteCommand(sql, db_connection);
OpenConnection();
try
{
cmd.ExecuteNonQuery();
}catch(SQLiteException sql_e)
{
stat = SQL_STATUS.SQL_ERR;
logger.writeErrReport(String.Format("ERROR WHILE ALTERING ({0}) ON TABLE *({1})* ::{2}", column, table_name, sql_e.Message));
}
CloseConnection();
return stat;
}
public SQL_STATUS Update(string table_name, Dictionary<string, string> columns_values, string conditions)
{
SQL_STATUS stat = SQL_STATUS.SQL_OKAY;
string sql = String.Format("UPDATE {0} ", table_name);
int columnCount = columns_values.Count;
int i = 0;
foreach (KeyValuePair<string, string> pair in columns_values)
{
bool lastElement = false;
if (i == columnCount - 1)
{
lastElement = true;
}
sql += pair.Key.ToString() + " = " + pair.Value.ToString() + (lastElement ? String.Format(" {0};", conditions) : ", ");
i++;
}
SQLiteCommand cmd = new SQLiteCommand(sql, db_connection);
OpenConnection();
try
{
cmd.ExecuteNonQuery();
}catch(SQLiteException sql_e)
{
stat = SQL_STATUS.SQL_ERR;
logger.writeErrReport(String.Format("ERROR WHILE UPDATING TABLE *({0})* ::{1}", table_name, sql_e.Message));
}
CloseConnection();
return stat;
}
