public override void PrepareParameterizedInsertStatement <T>(IDbCommand cmd, ICollection <string> insertFields = null,
Func <FieldDefinition, bool> shouldInclude = null)
{
var sbColumnNames = StringBuilderCache.Allocate();
var sbColumnValues = StringBuilderCacheAlt.Allocate();
var sbReturningColumns = StringBuilderCacheAlt.Allocate();
var modelDef = OrmLiteUtils.GetModelDefinition(typeof(T));
cmd.Parameters.Clear();
var fieldDefs = GetInsertFieldDefinitions(modelDef, insertFields);
foreach (var fieldDef in fieldDefs)
{
if (ShouldReturnOnInsert(modelDef, fieldDef))
{
if (sbReturningColumns.Length > 0)
{
sbReturningColumns.Append(",");
}
sbReturningColumns.Append("INSERTED." + GetQuotedColumnName(fieldDef.FieldName));
}
if ((ShouldSkipInsert(fieldDef) && !fieldDef.AutoId) &&
shouldInclude?.Invoke(fieldDef) != true)
{
continue;
}
if (sbColumnNames.Length > 0)
{
sbColumnNames.Append(",");
}
if (sbColumnValues.Length > 0)
{
sbColumnValues.Append(",");
}
try
{
sbColumnNames.Append(GetQuotedColumnName(fieldDef.FieldName));
if (SupportsSequences(fieldDef))
{
sbColumnValues.Append("NEXT VALUE FOR " + Sequence(NamingStrategy.GetSchemaName(modelDef), fieldDef.Sequence));
}
else
{
sbColumnValues.Append(this.GetParam(SanitizeFieldNameForParamName(fieldDef.FieldName), fieldDef.CustomInsert));
AddParameter(cmd, fieldDef);
}
}
catch (Exception ex)
{
Log.Error("ERROR in PrepareParameterizedInsertStatement(): " + ex.Message, ex);
throw;
}
}
foreach (var fieldDef in modelDef.AutoIdFields) // need to include any AutoId fields that weren't included
{
if (fieldDefs.Contains(fieldDef))
{
continue;
}
if (sbReturningColumns.Length > 0)
{
sbReturningColumns.Append(",");
}
sbReturningColumns.Append("INSERTED." + GetQuotedColumnName(fieldDef.FieldName));
}
var strReturning = StringBuilderCacheAlt.ReturnAndFree(sbReturningColumns);
strReturning = strReturning.Length > 0 ? "OUTPUT " + strReturning + " " : "";
cmd.CommandText = sbColumnNames.Length > 0
? $"INSERT INTO {GetQuotedTableName(modelDef)} ({StringBuilderCache.ReturnAndFree(sbColumnNames)}) {strReturning}" +
$"VALUES ({StringBuilderCacheAlt.ReturnAndFree(sbColumnValues)})"
: $"INSERT INTO {GetQuotedTableName(modelDef)}{strReturning} DEFAULT VALUES";
}
public override List <string> ToCreateSequenceStatements(Type tableType)
{
var modelDef = GetModel(tableType);
return(SequenceList(tableType).Select(seq => $"CREATE SEQUENCE {Sequence(NamingStrategy.GetSchemaName(modelDef), seq)} AS BIGINT START WITH 1 INCREMENT BY 1 NO CACHE;").ToList());
}
public override string ToCreateSequenceStatement(Type tableType, string sequenceName)
{
var modelDef = GetModel(tableType);
return($"CREATE SEQUENCE {Sequence(NamingStrategy.GetSchemaName(modelDef), sequenceName)} AS BIGINT START WITH 1 INCREMENT BY 1 NO CACHE;");
}
public override string ToTableNamesWithRowCountsStatement(bool live, string schema)
{
if (live)
{
return(null);
}
return(schema == null
? "SELECT table_name, table_rows FROM information_schema.tables WHERE table_type='BASE TABLE' AND table_schema = DATABASE()"
: "SELECT table_name, table_rows FROM information_schema.tables WHERE table_type='BASE TABLE' AND table_schema = DATABASE() AND table_name LIKE {0}".SqlFmt(this, NamingStrategy.GetSchemaName(schema) + "\\_%"));
}
public override string GetSchemaName(string schema)
{
return(schema != null
? NamingStrategy.GetSchemaName(schema).Replace(".", "_")
: NamingStrategy.GetSchemaName(schema));
}
public override string ToTableNamesStatement(string schema)
{
return(schema == null
? "SELECT table_name FROM information_schema.tables WHERE table_type='BASE TABLE' AND table_schema = DATABASE()"
: "SELECT table_name FROM information_schema.tables WHERE table_type='BASE TABLE' AND table_schema = DATABASE() AND table_name LIKE {0}".SqlFmt(this, NamingStrategy.GetSchemaName(schema) + "\\_%"));
}
public override string GetTableName(string table, string schema = null)
{
return(schema != null
? $"{NamingStrategy.GetSchemaName(schema)}_{NamingStrategy.GetTableName(table)}"
: NamingStrategy.GetTableName(table));
}
public override void PrepareParameterizedInsertStatement <T>(IDbCommand cmd, ICollection <string> insertFields = null)
{
var sbColumnNames = StringBuilderCache.Allocate();
var sbColumnValues = StringBuilderCacheAlt.Allocate();
var sbReturningColumns = StringBuilderCacheAlt.Allocate();
var modelDef = OrmLiteUtils.GetModelDefinition(typeof(T));
cmd.Parameters.Clear();
foreach (var fieldDef in modelDef.FieldDefinitionsArray)
{
//insertFields contains Property "Name" of fields to insert
var includeField = insertFields == null || insertFields.Contains(fieldDef.Name, StringComparer.OrdinalIgnoreCase);
if (ShouldReturnOnInsert(modelDef, fieldDef) && (!fieldDef.AutoId || !includeField))
{
if (sbReturningColumns.Length > 0)
{
sbReturningColumns.Append(",");
}
sbReturningColumns.Append("INSERTED." + GetQuotedColumnName(fieldDef.FieldName));
}
if (ShouldSkipInsert(fieldDef) && (!fieldDef.AutoId || !includeField))
{
continue;
}
//insertFields contains Property "Name" of fields to insert ( that's how expressions work )
if (insertFields != null && !insertFields.Contains(fieldDef.Name, StringComparer.OrdinalIgnoreCase))
{
continue;
}
if (sbColumnNames.Length > 0)
{
sbColumnNames.Append(",");
}
if (sbColumnValues.Length > 0)
{
sbColumnValues.Append(",");
}
try
{
sbColumnNames.Append(GetQuotedColumnName(fieldDef.FieldName));
if (SupportsSequences(fieldDef))
{
sbColumnValues.Append("NEXT VALUE FOR " + Sequence(NamingStrategy.GetSchemaName(modelDef), fieldDef.Sequence));
}
else
{
sbColumnValues.Append(this.GetParam(SanitizeFieldNameForParamName(fieldDef.FieldName)));
AddParameter(cmd, fieldDef);
}
}
catch (Exception ex)
{
Log.Error("ERROR in PrepareParameterizedInsertStatement(): " + ex.Message, ex);
throw;
}
}
var strReturning = StringBuilderCacheAlt.ReturnAndFree(sbReturningColumns);
strReturning = strReturning.Length > 0 ? "OUTPUT " + strReturning + " " : "";
cmd.CommandText = $"INSERT INTO {GetQuotedTableName(modelDef)} ({StringBuilderCache.ReturnAndFree(sbColumnNames)}) " +
strReturning +
$"VALUES ({StringBuilderCacheAlt.ReturnAndFree(sbColumnValues)})";
}
/// <summary>
///
/// </summary>
/// <param name="schema"></param>
/// <returns></returns>
public virtual string GetSchemaName(string schema)
{
return(NamingStrategy.GetSchemaName(schema));
}
/// <summary>
///
/// </summary>
/// <param name="table"></param>
/// <param name="schema"></param>
/// <returns></returns>
public virtual string GetTableName(string table, string schema = null)
{
return(schema != null
? string.Format("{0}.{1}", NamingStrategy.GetSchemaName(schema), NamingStrategy.GetTableName(table))
: NamingStrategy.GetTableName(table));
}