/// <summary>
/// Insert values into a Database table
/// </summary>
/// <param name="tableName">Table Name</param>
/// <param name="values">Name and Value pairs to insert into new row</param>
/// <param name="uuidField">The UUID field name</param>
/// <returns>Object query result</returns>
public object Insert(string tableName, DbFields values, string uuidField = null)
{
/* NOTE! This code prevents SQL Injection */
var command = this.GetCommand();
command.CommandType = CommandType.Text;
command.CommandText = string.Format(
string.IsNullOrWhiteSpace(uuidField)
? "INSERT INTO [{0}] ({1}) VALUES ({2}); SELECT @@IDENTITY"
: "INSERT INTO [{0}] ({1}) OUTPUT inserted.[" + uuidField + "] VALUES ({2});",
tableName,
string.Join(", ", values.Select(x => $"[{x.Key}]")),
string.Join(", ", values.Select(x => $"@{x.Key}")));
command.AddParameters(values);
if (this.Connection.State == ConnectionState.Closed || this.Connection.State == ConnectionState.Broken)
{
this.Connection.Open();
}
return(command.ExecuteScalar());
}
public void CreateTable(string tablename, DbFields fields)
{
var fieldsTxt = fields.Select(x => $"{x.Key} {x.Value}");
this.ExecuteNonQuery(
$"CREATE TABLE {tablename} ({string.Join(",", fieldsTxt)})");
}
public int Update(string tableName, DbFields values, string whereCondition, DbParameters parameters = null)
{
if (parameters == null)
{
parameters = new DbParameters();
}
if (!string.IsNullOrWhiteSpace(whereCondition) && !whereCondition.Trim().ToUpper().StartsWith("WHERE"))
{
whereCondition = $"WHERE {whereCondition}";
}
var queryParameters = new DbParameters(
values.ToDictionary(x => x.Key, x => x.Value));
queryParameters.Concat(
parameters.ToDictionary(x => x.Key, x => x.Value));
return(this.ExecuteNonQuery(
$"UPDATE [{tableName}] SET {string.Join(", ", values.Select(x => $"[{x.Key}]=@{x.Key}"))} {whereCondition}",
queryParameters));
}