/// <summary>
/// Convert a DateTime to a formatted string.
/// </summary>
/// <param name="ts">The timestamp.</param>
/// <returns>A string formatted for use with the specified database.</returns>
public static string DbTimestamp(DateTime ts)
{
return(SqlServerHelper.DbTimestamp(ts));
}
/// <summary>
/// Execute an UPDATE query.
/// The updated rows are returned.
/// </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>DataTable containing the updated rows.</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 Build-Key-Value-Clause
string keyValueClause = "";
int added = 0;
foreach (KeyValuePair <string, object> curr in keyValuePairs)
{
if (String.IsNullOrEmpty(curr.Key))
{
continue;
}
if (added == 0)
{
if (curr.Value != null)
{
if (curr.Value is DateTime || curr.Value is DateTime?)
{
keyValueClause += SqlServerHelper.PreparedFieldName(curr.Key) + "='" + DbTimestamp((DateTime)curr.Value) + "'";
}
else if (curr.Value is int || curr.Value is long || curr.Value is decimal)
{
keyValueClause += SqlServerHelper.PreparedFieldName(curr.Key) + "=" + curr.Value.ToString();
}
else
{
if (Helper.IsExtendedCharacters(curr.Value.ToString()))
{
keyValueClause += SqlServerHelper.PreparedFieldName(curr.Key) + "=" + SqlServerHelper.PreparedUnicodeValue(curr.Value.ToString());
}
else
{
keyValueClause += SqlServerHelper.PreparedFieldName(curr.Key) + "=" + SqlServerHelper.PreparedStringValue(curr.Value.ToString());
}
}
}
else
{
keyValueClause += SqlServerHelper.PreparedFieldName(curr.Key) + "= null";
}
}
else
{
if (curr.Value != null)
{
if (curr.Value is DateTime || curr.Value is DateTime?)
{
keyValueClause += "," + SqlServerHelper.PreparedFieldName(curr.Key) + "='" + DbTimestamp((DateTime)curr.Value) + "'";
}
else if (curr.Value is int || curr.Value is long || curr.Value is decimal)
{
keyValueClause += "," + SqlServerHelper.PreparedFieldName(curr.Key) + "=" + curr.Value.ToString();
}
else
{
if (Helper.IsExtendedCharacters(curr.Value.ToString()))
{
keyValueClause += "," + SqlServerHelper.PreparedFieldName(curr.Key) + "=" + SqlServerHelper.PreparedUnicodeValue(curr.Value.ToString());
}
else
{
keyValueClause += "," + SqlServerHelper.PreparedFieldName(curr.Key) + "=" + SqlServerHelper.PreparedStringValue(curr.Value.ToString());
}
}
}
else
{
keyValueClause += "," + SqlServerHelper.PreparedFieldName(curr.Key) + "= null";
}
}
added++;
}
#endregion
#region Build-UPDATE-Query-and-Submit
return(Query(SqlServerHelper.UpdateQuery(tableName, keyValueClause, filter)));
#endregion
}
/// <summary>
/// Execute an INSERT query with multiple values within a transaction.
/// </summary>
/// <param name="tableName">The table in which you wish to INSERT.</param>
/// <param name="keyValuePairList">List of dictionaries containing key-value pairs for the rows you wish to INSERT.</param>
public void InsertMultiple(string tableName, List <Dictionary <string, object> > keyValuePairList)
{
if (String.IsNullOrEmpty(tableName))
{
throw new ArgumentNullException(nameof(tableName));
}
if (keyValuePairList == null || keyValuePairList.Count < 1)
{
throw new ArgumentNullException(nameof(keyValuePairList));
}
#region Validate-Inputs
Dictionary <string, object> reference = keyValuePairList[0];
if (keyValuePairList.Count > 1)
{
foreach (Dictionary <string, object> dict in keyValuePairList)
{
if (!(reference.Count == dict.Count) || !(reference.Keys.SequenceEqual(dict.Keys)))
{
throw new ArgumentException("All supplied dictionaries must contain exactly the same keys.");
}
}
}
#endregion
#region Build-Keys
string keys = "";
int keysAdded = 0;
foreach (KeyValuePair <string, object> curr in reference)
{
if (keysAdded > 0)
{
keys += ",";
}
keys += SqlServerHelper.PreparedFieldName(curr.Key);
keysAdded++;
}
#endregion
#region Build-Values
List <string> values = new List <string>();
foreach (Dictionary <string, object> currDict in keyValuePairList)
{
string vals = "";
int valsAdded = 0;
foreach (KeyValuePair <string, object> currKvp in currDict)
{
if (valsAdded > 0)
{
vals += ",";
}
if (currKvp.Value != null)
{
if (currKvp.Value is DateTime || currKvp.Value is DateTime?)
{
vals += "'" + DbTimestamp((DateTime)currKvp.Value) + "'";
}
else if (currKvp.Value is int || currKvp.Value is long || currKvp.Value is decimal)
{
vals += currKvp.Value.ToString();
}
else
{
if (Helper.IsExtendedCharacters(currKvp.Value.ToString()))
{
vals += SqlServerHelper.PreparedUnicodeValue(currKvp.Value.ToString());
}
else
{
vals += SqlServerHelper.PreparedStringValue(currKvp.Value.ToString());
}
}
}
else
{
vals += "null";
}
valsAdded++;
}
values.Add(vals);
}
#endregion
#region Build-INSERT-Query-and-Submit
Query(SqlServerHelper.InsertMultipleQuery(tableName, keys, values));
#endregion
}
/// <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 Build-Key-Value-Pairs
string keys = "";
string values = "";
int added = 0;
foreach (KeyValuePair <string, object> curr in keyValuePairs)
{
if (String.IsNullOrEmpty(curr.Key))
{
continue;
}
if (added == 0)
{
#region First
keys += SqlServerHelper.PreparedFieldName(curr.Key);
if (curr.Value != null)
{
if (curr.Value is DateTime || curr.Value is DateTime?)
{
values += "'" + DbTimestamp((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 += SqlServerHelper.PreparedUnicodeValue(curr.Value.ToString());
}
else
{
values += SqlServerHelper.PreparedStringValue(curr.Value.ToString());
}
}
}
else
{
values += "null";
}
#endregion
}
else
{
#region Subsequent
keys += "," + SqlServerHelper.PreparedFieldName(curr.Key);
if (curr.Value != null)
{
if (curr.Value is DateTime || curr.Value is DateTime?)
{
values += ",'" + DbTimestamp((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 += "," + SqlServerHelper.PreparedUnicodeValue(curr.Value.ToString());
}
else
{
values += "," + SqlServerHelper.PreparedStringValue(curr.Value.ToString());
}
}
}
else
{
values += ",null";
}
#endregion
}
added++;
}
#endregion
#region Build-INSERT-Query-and-Submit
return(Query(SqlServerHelper.InsertQuery(tableName, keys, values)));
#endregion
}
/// <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));
}
List <Column> columns = new List <Column>();
DataTable result = Query(SqlServerHelper.LoadTableColumnsQuery(_Settings.DatabaseName, tableName));
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();
tempColumn.MaxLength = null;
if (currColumn.Table.Columns.Contains("CHARACTER_MAXIMUM_LENGTH"))
{
int maxLength = 0;
if (Int32.TryParse(currColumn["CHARACTER_MAXIMUM_LENGTH"].ToString(), out maxLength))
{
tempColumn.MaxLength = maxLength;
}
}
tempColumn.Type = Helper.DataTypeFromString(currColumn["DATA_TYPE"].ToString());
if (currColumn.Table.Columns.Contains("IS_NULLABLE"))
{
if (String.Compare(currColumn["IS_NULLABLE"].ToString(), "YES") == 0)
{
tempColumn.Nullable = true;
}
else
{
tempColumn.Nullable = false;
}
}
else if (currColumn.Table.Columns.Contains("IS_NOT_NULLABLE"))
{
tempColumn.Nullable = !(Convert.ToBoolean(currColumn["IS_NOT_NULLABLE"]));
}
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;
}
}
if (!columns.Exists(c => c.Name.Equals(tempColumn.Name)))
{
columns.Add(tempColumn);
}
#endregion
}
}
return(columns);
}
internal static string PreparedStringValue(string s)
{
return("'" + SqlServerHelper.SanitizeString(s) + "'");
}
