/// <summary>
/// Execute the insert statement, and return the assigned primary key
/// </summary>
/// <param name="databaseAssignsKey">if true, returns newly assigned key</param>
/// <returns>null or a newly assigned key</returns>
public Task <object> Execute(IDbConnection db, string tableName, string pkColName, bool databaseAssignsKey)
{
string nonKeyColNames = string.Join(",", Cols.Select(c => c.Name));
string valuesList = string.Join(",", Cols.Select(c => c.LiteralExpression));
using (var cmd = db.CreateCommand())
{
foreach (var c in Cols)
{
cmd.Parameters.Add(c.AsParameter(cmd));
}
string coreCommand = $"insert into {tableName} ({nonKeyColNames}) values ({valuesList})";
if (databaseAssignsKey)
{
cmd.CommandText = coreCommand + SqlFlavor.BuildGetIdentityClause(pkColName);
cmd.CommandText = SqlCustomizer(cmd.CommandText);
return(Task.FromResult(cmd.ExecuteScalar()));
}
else
{
cmd.CommandText = SqlCustomizer(coreCommand);
cmd.ExecuteNonQuery();
return(Task.FromResult <object>(null));
}
}
}
protected override string GenerateInsertScript()
{
var sql = $@"
INSERT INTO {TableName} ({string.Join(", ", Cols.Select(c => c.Key))})
VALUES(@{string.Join(", @", Cols.Select(c => c.Key))});
";
return(sql);
}
protected override string GenerateTableScript()
{
var sql = $@"
CREATE TABLE IF NOT EXISTS {TableName} (
Id INTEGER PRIMARY KEY AUTOINCREMENT,
{string.Join(",", Cols.Select(c => $"{c.Key} {c.Value.ToString().Replace("_", " ")}"))}
) ;
";
return(sql);
}
public string ToEscapedDelimitedString(string delimiter, string escapePrefix, string escapeSuffix, string lineSeperator)
{
StringBuilder builder = new StringBuilder();
Func <string, string> escape = name => name.Contains(delimiter) ? String.Format("{0}{1}{2}", escapePrefix, name, escapeSuffix) : name;
builder.Append(String.Join(delimiter, Cols.Select(escape)));
builder.Append(lineSeperator);
builder.Append(String.Join(lineSeperator, _rows.Select(row => String.Join(delimiter, row.Select(escape)))));
return(builder.ToString());
}
/// <summary>
/// Execute the update statement
/// </summary>
public Task Execute(IDbConnection db, string tableName, string pkColName, object pkValue)
{
string colClauses = string.Join(",", Cols.Select(c => $"{c.Name}={c.LiteralExpression}"));
using (var cmd = db.CreateCommand())
{
foreach (var c in Cols)
{
cmd.Parameters.Add(c.AsParameter(cmd));
}
cmd.CommandText = $"update {tableName} set {colClauses} where {pkColName}=@pk";
cmd.CommandText = SqlCustomizer(cmd.CommandText);
Utils.AddParameterWithValue(cmd, "pk", pkValue);
cmd.ExecuteNonQuery();
}
return(Task.CompletedTask);
}