/// <summary>
/// Execute a SELECT query.
/// </summary>
/// <param name="tableName">The table from which you wish to SELECT.</param>
/// <param name="indexStart">The starting index for retrieval; used for pagination in conjunction with maxResults and orderByClause. orderByClause example: ORDER BY created DESC.</param>
/// <param name="maxResults">The maximum number of results to retrieve.</param>
/// <param name="returnFields">The fields you wish to have returned. Null returns all.</param>
/// <param name="filter">The expression containing the SELECT filter (i.e. WHERE clause data).</param>
/// <param name="orderByClause">Specify an ORDER BY clause if desired.</param>
/// <returns>A DataTable containing the results.</returns>
public DataTable Select(string tableName, int?indexStart, int?maxResults, List <string> returnFields, Expression filter, string orderByClause)
{
if (String.IsNullOrEmpty(tableName))
{
throw new ArgumentNullException(nameof(tableName));
}
string query = "";
DataTable result;
List <Column> tableDetails = DescribeTable(tableName);
switch (DbType)
{
case DbTypes.MsSql:
query = MssqlHelper.SelectQuery(tableName, indexStart, maxResults, returnFields, filter, orderByClause);
break;
case DbTypes.MySql:
query = MysqlHelper.SelectQuery(tableName, indexStart, maxResults, returnFields, filter, orderByClause);
break;
case DbTypes.PgSql:
query = PgsqlHelper.SelectQuery(tableName, indexStart, maxResults, returnFields, filter, orderByClause);
break;
}
result = RawQuery(query);
return(result);
}
private string SanitizeString(string s)
{
if (String.IsNullOrEmpty(s))
{
return(String.Empty);
}
string ret = "";
switch (DbType)
{
case DbTypes.MsSql:
ret = MssqlHelper.SanitizeString(s);
break;
case DbTypes.MySql:
ret = MysqlHelper.SanitizeString(s);
break;
case DbTypes.PgSql:
ret = PgsqlHelper.SanitizeString(s);
break;
}
return(ret);
}
/// <summary>
/// Drop the specified table.
/// </summary>
/// <param name="tableName">The table to drop.</param>
public void DropTable(string tableName)
{
if (String.IsNullOrEmpty(tableName))
{
throw new ArgumentNullException(nameof(tableName));
}
string query = null;
switch (_DbType)
{
case DbTypes.MsSql:
query = MssqlHelper.DropTableQuery(tableName);
break;
case DbTypes.MySql:
query = MysqlHelper.DropTableQuery(tableName);
break;
case DbTypes.PgSql:
query = PgsqlHelper.DropTableQuery(tableName);
break;
}
DataTable result = Query(query);
}
/// <summary>
/// Create a table with a specified name.
/// </summary>
/// <param name="tableName">The name of the table.</param>
/// <param name="columns">Columns.</param>
public void CreateTable(string tableName, List <Column> columns)
{
if (String.IsNullOrEmpty(tableName))
{
throw new ArgumentNullException(nameof(tableName));
}
if (columns == null || columns.Count < 1)
{
throw new ArgumentNullException(nameof(columns));
}
string query = null;
switch (_DbType)
{
case DbTypes.MsSql:
query = MssqlHelper.CreateTableQuery(tableName, columns);
break;
case DbTypes.MySql:
query = MysqlHelper.CreateTableQuery(tableName, columns);
break;
case DbTypes.PgSql:
query = PgsqlHelper.CreateTableQuery(tableName, columns);
break;
}
DataTable result = Query(query);
}
/// <summary>
/// List all tables in the database.
/// </summary>
/// <returns>List of strings, each being a table name.</returns>
public List <string> ListTables()
{
string query = null;
DataTable result = null;
List <string> tableNames = new List <string>();
switch (_DbType)
{
case DbTypes.MsSql:
query = MssqlHelper.LoadTableNamesQuery(_DatabaseName);
break;
case DbTypes.MySql:
query = MysqlHelper.LoadTableNamesQuery();
break;
case DbTypes.PgSql:
query = PgsqlHelper.LoadTableNamesQuery();
break;
}
result = Query(query);
if (result != null && result.Rows.Count > 0)
{
switch (_DbType)
{
case DbTypes.MsSql:
foreach (DataRow curr in result.Rows)
{
tableNames.Add(curr["TABLE_NAME"].ToString());
}
break;
case DbTypes.MySql:
foreach (DataRow curr in result.Rows)
{
tableNames.Add(curr["Tables_in_" + _DatabaseName].ToString());
}
break;
case DbTypes.PgSql:
foreach (DataRow curr in result.Rows)
{
tableNames.Add(curr["tablename"].ToString());
}
break;
}
}
return(tableNames);
}
private string PreparedStringValue(string s)
{
switch (DbType)
{
case DbTypes.MsSql:
return("'" + MssqlHelper.SanitizeString(s) + "'");
case DbTypes.MySql:
return("'" + MysqlHelper.SanitizeString(s) + "'");
case DbTypes.PgSql:
// uses $xx$ escaping
return(PgsqlHelper.SanitizeString(s));
}
return(null);
}
/// <summary>
/// Execute a DELETE query.
/// </summary>
/// <param name="tableName">The table in which you wish to DELETE.</param>
/// <param name="filter">The expression containing the DELETE filter (i.e. WHERE clause data).</param>
/// <returns>A DataTable containing the results.</returns>
public DataTable Delete(string tableName, Expression filter)
{
if (String.IsNullOrEmpty(tableName))
{
throw new ArgumentNullException(nameof(tableName));
}
if (filter == null)
{
throw new ArgumentNullException(nameof(filter));
}
#region Variables
string query = "";
DataTable result;
List <Column> tableDetails = DescribeTable(tableName);
List <string> columnNames = GetColumnNames(tableName);
string primaryKeyColumn = GetPrimaryKeyColumn(tableName);
#endregion
#region Build-DELETE-Query-and-Submit
switch (DbType)
{
case DbTypes.MsSql:
query = MssqlHelper.DeleteQuery(tableName, filter);
break;
case DbTypes.MySql:
query = MysqlHelper.DeleteQuery(tableName, filter);
break;
case DbTypes.PgSql:
query = PgsqlHelper.DeleteQuery(tableName, filter);
break;
}
result = RawQuery(query);
#endregion
return(result);
}
/// <summary>
/// Execute a DELETE query.
/// </summary>
/// <param name="tableName">The table in which you wish to DELETE.</param>
/// <param name="filter">The expression containing the DELETE filter (i.e. WHERE clause data).</param>
/// <returns>A DataTable containing the results.</returns>
public DataTable Delete(string tableName, Expression filter)
{
if (String.IsNullOrEmpty(tableName))
{
throw new ArgumentNullException(nameof(tableName));
}
if (filter == null)
{
throw new ArgumentNullException(nameof(filter));
}
#region Variables
string query = "";
DataTable result;
#endregion
#region Build-DELETE-Query-and-Submit
switch (_DbType)
{
case DbTypes.MsSql:
query = MssqlHelper.DeleteQuery(tableName, filter);
break;
case DbTypes.MySql:
query = MysqlHelper.DeleteQuery(tableName, filter);
break;
case DbTypes.PgSql:
query = PgsqlHelper.DeleteQuery(tableName, filter);
break;
}
result = Query(query);
#endregion
return(result);
}
private void PopulateConnectionString()
{
ConnectionString = "";
switch (DbType)
{
case DbTypes.MsSql:
ConnectionString = MssqlHelper.ConnectionString(ServerIp, ServerPort, Username, Password, Instance, Database);
break;
case DbTypes.MySql:
ConnectionString = MysqlHelper.ConnectionString(ServerIp, ServerPort, Username, Password, Database);
break;
case DbTypes.PgSql:
ConnectionString = PgsqlHelper.ConnectionString(ServerIp, ServerPort, Username, Password, Database);
break;
}
return;
}
private void PopulateConnectionString()
{
ConnectionString = "";
switch (_DbType)
{
case DbTypes.MsSql:
ConnectionString = MssqlHelper.ConnectionString(_ServerIp, _ServerPort, _Username, _Password, _Instance, _DatabaseName);
break;
case DbTypes.MySql:
ConnectionString = MysqlHelper.ConnectionString(_ServerIp, _ServerPort, _Username, _Password, _DatabaseName);
break;
case DbTypes.PgSql:
ConnectionString = PgsqlHelper.ConnectionString(_ServerIp, _ServerPort, _Username, _Password, _DatabaseName);
break;
}
return;
}
/// <summary>
/// Sanitize an input string.
/// </summary>
/// <param name="val">The value to sanitize.</param>
/// <returns>A sanitized string.</returns>
public string SanitizeString(string val)
{
if (String.IsNullOrEmpty(val))
{
return(val);
}
switch (_DbType)
{
case DbTypes.MsSql:
return(MssqlHelper.SanitizeString(val));
case DbTypes.MySql:
return(MysqlHelper.SanitizeString(val));
case DbTypes.PgSql:
return(PgsqlHelper.SanitizeString(val));
}
throw new Exception("Unknown database type");
}
private void LoadTableDetails()
{
lock (LoadingTablesLock)
{
string query = "";
DataTable result = new DataTable();
Dictionary <string, List <Column> > tableDetails = new Dictionary <string, List <Column> >();
foreach (string currTable in TableNames)
{
#region Gather-Schema
List <Column> columns = new List <Column>();
switch (DbType)
{
case DbTypes.MsSql:
query = MssqlHelper.LoadTableColumnsQuery(Database, currTable);
break;
case DbTypes.MySql:
query = MysqlHelper.LoadTableColumnsQuery(Database, currTable);
break;
case DbTypes.PgSql:
query = PgsqlHelper.LoadTableColumnsQuery(Database, currTable);
break;
}
#endregion
#region Process-Schema
result = RawQuery(query);
if (result != null && result.Rows.Count > 0)
{
foreach (DataRow currColumn in result.Rows)
{
#region Process-Each-Column
/*
* public bool IsPrimaryKey;
* public string Name;
* public string DataType;
* public int? MaxLength;
* public bool Nullable;
*/
Column tempColumn = new Column();
int maxLength = 0;
switch (DbType)
{
case DbTypes.MsSql:
#region Mssql
tempColumn.Name = currColumn["COLUMN_NAME"].ToString();
if (currColumn["CONSTRAINT_NAME"].ToString().StartsWith("PK_"))
{
tempColumn.IsPrimaryKey = true;
}
else
{
tempColumn.IsPrimaryKey = false;
}
tempColumn.DataType = currColumn["DATA_TYPE"].ToString();
if (!Int32.TryParse(currColumn["CHARACTER_MAXIMUM_LENGTH"].ToString(), out maxLength))
{
tempColumn.MaxLength = null;
}
else
{
tempColumn.MaxLength = maxLength;
}
if (String.Compare(currColumn["IS_NULLABLE"].ToString(), "YES") == 0)
{
tempColumn.Nullable = true;
}
else
{
tempColumn.Nullable = false;
}
break;
#endregion
case DbTypes.MySql:
#region Mysql
tempColumn.Name = currColumn["COLUMN_NAME"].ToString();
if (String.Compare(currColumn["COLUMN_KEY"].ToString(), "PRI") == 0)
{
tempColumn.IsPrimaryKey = true;
}
else
{
tempColumn.IsPrimaryKey = false;
}
tempColumn.DataType = currColumn["DATA_TYPE"].ToString();
if (!Int32.TryParse(currColumn["CHARACTER_MAXIMUM_LENGTH"].ToString(), out maxLength))
{
tempColumn.MaxLength = null;
}
else
{
tempColumn.MaxLength = maxLength;
}
if (String.Compare(currColumn["IS_NULLABLE"].ToString(), "YES") == 0)
{
tempColumn.Nullable = true;
}
else
{
tempColumn.Nullable = false;
}
break;
#endregion
case DbTypes.PgSql:
#region Pgsql
tempColumn.Name = currColumn["column_name"].ToString();
if (String.Compare(currColumn["is_primary_key"].ToString(), "YES") == 0)
{
tempColumn.IsPrimaryKey = true;
}
else
{
tempColumn.IsPrimaryKey = false;
}
tempColumn.DataType = currColumn["DATA_TYPE"].ToString();
if (!Int32.TryParse(currColumn["max_len"].ToString(), out maxLength))
{
tempColumn.MaxLength = null;
}
else
{
tempColumn.MaxLength = maxLength;
}
if (String.Compare(currColumn["IS_NULLABLE"].ToString(), "YES") == 0)
{
tempColumn.Nullable = true;
}
else
{
tempColumn.Nullable = false;
}
break;
#endregion
}
columns.Add(tempColumn);
#endregion
}
tableDetails.Add(currTable, columns);
}
#endregion
}
#region Replace-Table-Details
TableDetails = new ConcurrentDictionary <string, List <Column> >();
foreach (KeyValuePair <string, List <Column> > curr in tableDetails)
{
TableDetails.TryAdd(curr.Key, curr.Value);
}
#endregion
return;
}
}
private void LoadTableNames()
{
lock (LoadingTablesLock)
{
string query = "";
DataTable result = new DataTable();
#region Build-Query
switch (DbType)
{
case DbTypes.MsSql:
query = MssqlHelper.LoadTableNamesQuery(Database);
break;
case DbTypes.MySql:
query = MysqlHelper.LoadTableNamesQuery();
break;
case DbTypes.PgSql:
query = PgsqlHelper.LoadTableNamesQuery();
break;
}
#endregion
#region Process-Results
result = RawQuery(query);
List <string> tableNames = new List <string>();
if (result != null && result.Rows.Count > 0)
{
switch (DbType)
{
case DbTypes.MsSql:
foreach (DataRow curr in result.Rows)
{
tableNames.Add(curr["TABLE_NAME"].ToString());
}
break;
case DbTypes.MySql:
foreach (DataRow curr in result.Rows)
{
tableNames.Add(curr["Tables_in_" + Database].ToString());
}
break;
case DbTypes.PgSql:
foreach (DataRow curr in result.Rows)
{
tableNames.Add(curr["tablename"].ToString());
}
break;
}
}
if (tableNames != null && tableNames.Count > 0)
{
TableNames = new ConcurrentList <string>();
foreach (string curr in tableNames)
{
TableNames.Add(curr);
}
}
#endregion
return;
}
}
/// <summary>
/// Execute an UPDATE query.
/// </summary>
/// <param name="tableName">The table in which you wish to UPDATE.</param>
/// <param name="keyValuePairs">The key-value pairs for the data you wish to UPDATE.</param>
/// <param name="filter">The expression containing the UPDATE filter (i.e. WHERE clause data).</param>
/// <returns>A DataTable containing the results.</returns>
public DataTable Update(string tableName, Dictionary <string, object> keyValuePairs, Expression filter)
{
if (String.IsNullOrEmpty(tableName))
{
throw new ArgumentNullException(nameof(tableName));
}
if (keyValuePairs == null || keyValuePairs.Count < 1)
{
throw new ArgumentNullException(nameof(keyValuePairs));
}
#region Variables
string query = "";
string keyValueClause = "";
DataTable result;
List <Column> tableDetails = DescribeTable(tableName);
List <string> columnNames = GetColumnNames(tableName);
string primaryKeyColumn = GetPrimaryKeyColumn(tableName);
#endregion
#region Build-Key-Value-Clause
int added = 0;
foreach (KeyValuePair <string, object> curr in keyValuePairs)
{
if (String.IsNullOrEmpty(curr.Key))
{
continue;
}
if (!columnNames.Contains(curr.Key))
{
throw new ArgumentException("Column " + curr.Key + " does not exist in table " + tableName);
}
if (added == 0)
{
if (curr.Value != null)
{
if (curr.Value is DateTime || curr.Value is DateTime?)
{
keyValueClause += PreparedFieldname(curr.Key) + "='" + DbTimestamp(DbType, (DateTime)curr.Value) + "'";
}
else if (curr.Value is int || curr.Value is long || curr.Value is decimal)
{
keyValueClause += PreparedFieldname(curr.Key) + "=" + curr.Value.ToString();
}
else
{
if (Helper.IsExtendedCharacters(curr.Value.ToString()))
{
keyValueClause += PreparedFieldname(curr.Key) + "=" + PreparedUnicodeValue(curr.Value.ToString());
}
else
{
keyValueClause += PreparedFieldname(curr.Key) + "=" + PreparedStringValue(curr.Value.ToString());
}
}
}
else
{
keyValueClause += PreparedFieldname(curr.Key) + "= null";
}
}
else
{
if (curr.Value != null)
{
if (curr.Value is DateTime || curr.Value is DateTime?)
{
keyValueClause += "," + PreparedFieldname(curr.Key) + "='" + DbTimestamp(DbType, (DateTime)curr.Value) + "'";
}
else if (curr.Value is int || curr.Value is long || curr.Value is decimal)
{
keyValueClause += "," + PreparedFieldname(curr.Key) + "=" + curr.Value.ToString();
}
else
{
if (Helper.IsExtendedCharacters(curr.Value.ToString()))
{
keyValueClause += "," + PreparedFieldname(curr.Key) + "=" + PreparedUnicodeValue(curr.Value.ToString());
}
else
{
keyValueClause += "," + PreparedFieldname(curr.Key) + "=" + PreparedStringValue(curr.Value.ToString());
}
}
}
else
{
keyValueClause += "," + PreparedFieldname(curr.Key) + "= null";
}
}
added++;
}
#endregion
#region Build-UPDATE-Query-and-Submit
switch (DbType)
{
case DbTypes.MsSql:
query = MssqlHelper.UpdateQuery(tableName, keyValueClause, filter);
break;
case DbTypes.MySql:
query = MysqlHelper.UpdateQuery(tableName, keyValueClause, filter);
break;
case DbTypes.PgSql:
query = PgsqlHelper.UpdateQuery(tableName, keyValueClause, filter);
break;
}
result = RawQuery(query);
#endregion
return(result);
}
/// <summary>
/// Execute an INSERT query.
/// </summary>
/// <param name="tableName">The table in which you wish to INSERT.</param>
/// <param name="keyValuePairs">The key-value pairs for the row you wish to INSERT.</param>
/// <returns>A DataTable containing the results.</returns>
public DataTable Insert(string tableName, Dictionary <string, object> keyValuePairs)
{
if (String.IsNullOrEmpty(tableName))
{
throw new ArgumentNullException(nameof(tableName));
}
if (keyValuePairs == null || keyValuePairs.Count < 1)
{
throw new ArgumentNullException(nameof(keyValuePairs));
}
#region Variables
string keys = "";
string values = "";
string query = "";
int insertedId = 0;
string retrievalQuery = "";
DataTable result;
List <Column> tableDetails = DescribeTable(tableName);
List <string> columnNames = GetColumnNames(tableName);
string primaryKeyColumn = GetPrimaryKeyColumn(tableName);
#endregion
#region Build-Key-Value-Pairs
int added = 0;
foreach (KeyValuePair <string, object> curr in keyValuePairs)
{
if (String.IsNullOrEmpty(curr.Key))
{
continue;
}
if (!columnNames.Contains(curr.Key))
{
throw new ArgumentException("Column " + curr.Key + " does not exist in table " + tableName);
}
if (added == 0)
{
#region First
keys += PreparedFieldname(curr.Key);
if (curr.Value != null)
{
if (curr.Value is DateTime || curr.Value is DateTime?)
{
values += "'" + DbTimestamp(DbType, (DateTime)curr.Value) + "'";
}
else if (curr.Value is int || curr.Value is long || curr.Value is decimal)
{
values += curr.Value.ToString();
}
else
{
if (Helper.IsExtendedCharacters(curr.Value.ToString()))
{
values += PreparedUnicodeValue(curr.Value.ToString());
}
else
{
values += PreparedStringValue(curr.Value.ToString());
}
}
}
else
{
values += "null";
}
#endregion
}
else
{
#region Subsequent
keys += "," + PreparedFieldname(curr.Key);
if (curr.Value != null)
{
if (curr.Value is DateTime || curr.Value is DateTime?)
{
values += ",'" + DbTimestamp(DbType, (DateTime)curr.Value) + "'";
}
else if (curr.Value is int || curr.Value is long || curr.Value is decimal)
{
values += "," + curr.Value.ToString();
}
else
{
if (Helper.IsExtendedCharacters(curr.Value.ToString()))
{
values += "," + PreparedUnicodeValue(curr.Value.ToString());
}
else
{
values += "," + PreparedStringValue(curr.Value.ToString());
}
}
}
else
{
values += ",null";
}
#endregion
}
added++;
}
#endregion
#region Build-INSERT-Query-and-Submit
switch (DbType)
{
case DbTypes.MsSql:
query = MssqlHelper.InsertQuery(tableName, keys, values);
break;
case DbTypes.MySql:
query = MysqlHelper.InsertQuery(tableName, keys, values);
break;
case DbTypes.PgSql:
query = PgsqlHelper.InsertQuery(tableName, keys, values);
break;
}
result = RawQuery(query);
#endregion
#region Post-Retrieval
switch (DbType)
{
case DbTypes.MsSql:
#region MsSql
//
// built into the query
//
break;
#endregion
case DbTypes.MySql:
#region MySql
if (!Helper.DataTableIsNullOrEmpty(result))
{
bool idFound = false;
foreach (DataRow curr in result.Rows)
{
if (Int32.TryParse(curr["id"].ToString(), out insertedId))
{
idFound = true;
break;
}
}
if (!idFound)
{
result = null;
}
else
{
retrievalQuery = "SELECT * FROM " + tableName + " WHERE " + primaryKeyColumn + "=" + insertedId;
result = RawQuery(retrievalQuery);
}
}
break;
#endregion
case DbTypes.PgSql:
#region PgSql
//
// built into the query
//
break;
#endregion
}
#endregion
return(result);
}
/// <summary>
/// Show the columns and column metadata from a specific table.
/// </summary>
/// <param name="tableName">The table to view.</param>
/// <returns>A list of column objects.</returns>
public List <Column> DescribeTable(string tableName)
{
if (String.IsNullOrEmpty(tableName))
{
throw new ArgumentNullException(nameof(tableName));
}
string query = null;
DataTable result = null;
List <Column> columns = new List <Column>();
switch (_DbType)
{
case DbTypes.MsSql:
query = MssqlHelper.LoadTableColumnsQuery(_DatabaseName, tableName);
break;
case DbTypes.MySql:
query = MysqlHelper.LoadTableColumnsQuery(_DatabaseName, tableName);
break;
case DbTypes.PgSql:
query = PgsqlHelper.LoadTableColumnsQuery(_DatabaseName, tableName);
break;
}
result = Query(query);
if (result != null && result.Rows.Count > 0)
{
foreach (DataRow currColumn in result.Rows)
{
#region Process-Each-Column
/*
* public bool PrimaryKey;
* public string Name;
* public string DataType;
* public int? MaxLength;
* public bool Nullable;
*/
Column tempColumn = new Column();
tempColumn.Name = currColumn["COLUMN_NAME"].ToString();
int maxLength = 0;
if (!Int32.TryParse(currColumn["CHARACTER_MAXIMUM_LENGTH"].ToString(), out maxLength))
{
tempColumn.MaxLength = null;
}
else
{
tempColumn.MaxLength = maxLength;
}
tempColumn.Type = DataTypeFromString(currColumn["DATA_TYPE"].ToString());
if (String.Compare(currColumn["IS_NULLABLE"].ToString(), "YES") == 0)
{
tempColumn.Nullable = true;
}
else
{
tempColumn.Nullable = false;
}
switch (_DbType)
{
case DbTypes.MsSql:
if (currColumn["CONSTRAINT_NAME"] != null &&
currColumn["CONSTRAINT_NAME"] != DBNull.Value &&
!String.IsNullOrEmpty(currColumn["CONSTRAINT_NAME"].ToString()))
{
if (currColumn["CONSTRAINT_NAME"].ToString().ToLower().StartsWith("pk"))
{
tempColumn.PrimaryKey = true;
}
}
break;
case DbTypes.MySql:
if (currColumn["COLUMN_KEY"] != null &&
currColumn["COLUMN_KEY"] != DBNull.Value &&
!String.IsNullOrEmpty(currColumn["COLUMN_KEY"].ToString()))
{
if (currColumn["COLUMN_KEY"].ToString().ToLower().Equals("pri"))
{
tempColumn.PrimaryKey = true;
}
}
break;
case DbTypes.PgSql:
if (currColumn["IS_PRIMARY_KEY"] != null &&
currColumn["IS_PRIMARY_KEY"] != DBNull.Value &&
!String.IsNullOrEmpty(currColumn["IS_PRIMARY_KEY"].ToString()))
{
if (currColumn["IS_PRIMARY_KEY"].ToString().ToLower().Equals("yes"))
{
tempColumn.PrimaryKey = true;
}
}
break;
}
if (!columns.Exists(c => c.Name.Equals(tempColumn.Name)))
{
columns.Add(tempColumn);
}
#endregion
}
}
return(columns);
}