protected override Sql GetSql <T>(string tableName, string primaryKeyName, bool autoincrement, T poco)
{
var dictionary = poco.AsDictionary();
var ignored = poco.GetIgnoredColumns();
List <string> columns;
if (autoincrement)
{
columns = dictionary.Keys.Where(x => x.ToUnderscoreLowerCase() != primaryKeyName)
.Where(x => !ignored.Contains(x))
.Select(key => $"\"{key.ToUnderscoreLowerCase()}\"").ToList();
}
else
{
columns = dictionary.Keys
.Where(x => !ignored.Contains(x))
.Select(key => $"\"{key.ToUnderscoreLowerCase()}\"").ToList();
}
var sql = new Sql();
if (!string.IsNullOrWhiteSpace(primaryKeyName))
{
string output = "DECLARE @OUTPUT TABLE(id national character varying(1000));";
sql.Append(output);
}
sql.Append($"INSERT INTO {tableName} ({string.Join(",", columns)})");
sql.Append(!string.IsNullOrWhiteSpace(primaryKeyName) ? $"OUTPUT CAST(INSERTED.\"{primaryKeyName}\" AS national character varying(1000)) INTO @OUTPUT" : "");
sql.Append($"SELECT {string.Join(",", Enumerable.Range(0, columns.Count).Select(x => "@" + x))};");
if (!string.IsNullOrWhiteSpace(primaryKeyName))
{
sql.Append("SELECT * FROM @OUTPUT;");
}
List <object> values;
if (autoincrement)
{
values = dictionary.Where(x => x.Key.ToUnderscoreLowerCase() != primaryKeyName)
.Where(x => !ignored.Contains(x.Key))
.Select(x => x.Value).ToList();
}
else
{
values = dictionary.Values
.Where(x => !ignored.Contains(x))
.Select(x => x).ToList();
}
sql.AppendParameters(values);
return(sql);
}
protected virtual Sql GetSql(string tableName, string primaryKeyName, object primaryKeyValue)
{
var sql = new Sql($"DELETE FROM {tableName}");
sql.Append($"WHERE \"{primaryKeyName}\"=@0;", primaryKeyValue);
var values = new List <object>
{
primaryKeyValue
};
sql.AppendParameters(values);
return(sql);
}
protected virtual Sql GetSql <T>(string tableName, string primaryKeyName, bool autoincrement, T poco)
{
var dictionary = poco.AsDictionary();
var ignored = poco.GetIgnoredColumns();
List <string> columns;
if (autoincrement)
{
columns = dictionary.Keys.Where(x => x.ToUnderscoreLowerCase() != primaryKeyName)
.Where(x => !ignored.Contains(x))
.Select(key => $"\"{key.ToUnderscoreLowerCase()}\"").ToList();
}
else
{
columns = dictionary.Keys
.Where(x => !ignored.Contains(x))
.Select(key => $"\"{key.ToUnderscoreLowerCase()}\"").ToList();
}
var sql = new Sql($"INSERT INTO {tableName} ({string.Join(",", columns)})");
sql.Append($"SELECT {string.Join(",", Enumerable.Range(0, columns.Count).Select(x => "@" + x))}");
sql.Append(!string.IsNullOrWhiteSpace(primaryKeyName) ? $"RETURNING \"{primaryKeyName}\";" : ";");
List <object> values;
if (autoincrement)
{
values = dictionary.Where(x => x.Key.ToUnderscoreLowerCase() != primaryKeyName)
.Where(x => !ignored.Contains(x.Key))
.Select(x => x.Value).ToList();
}
else
{
values = dictionary.Values
.Where(x => !ignored.Contains(x))
.Select(x => x).ToList();
}
sql.AppendParameters(values);
return(sql);
}
protected virtual Sql GetSql <T>(T poco, string tableName, string primaryKeyName, object primaryKeyValue, bool isIdentity)
{
var dictionary = poco.AsDictionary();
var ignored = poco.GetIgnoredColumns();
var columns = dictionary.Keys
.Where(x => !ignored.Contains(x))
.Select(key => $"\"{key.ToUnderscoreLowerCase()}\"").ToList();
var sql = new Sql($"UPDATE {tableName}");
sql.Append("SET");
int columnCount = columns.Count;
for (int i = 0; i < columnCount; i++)
{
string column = columns[i];
string token = $"\t{column}=@0";
if (i != columnCount - 1)
{
token += ",";
}
sql.Append(token);
}
sql.Append($"WHERE \"{primaryKeyName}\"=@0;");
var values = dictionary
.Where(x => !ignored.Contains(x.Key))
.Select(x => x.Value).ToList();
values.Add(primaryKeyValue);
sql.AppendParameters(values);
return(sql);
}
