public override string GetTableName(string table, string schema, bool useStrategy)
{
if (useStrategy)
{
return(schema != null && !table.StartsWithIgnoreCase(schema + "_")
? QuoteIfRequired(NamingStrategy.GetSchemaName(schema) + "_" + NamingStrategy.GetTableName(table))
: QuoteIfRequired(NamingStrategy.GetTableName(table)));
}
return(schema != null && !table.StartsWithIgnoreCase(schema + "_")
? QuoteIfRequired(schema + "_" + table)
: QuoteIfRequired(table));
}
public override string GetQuotedTableName(string tableName)
{
return(Quote(NamingStrategy.GetTableName(tableName)));
}
/// <summary>Gets quoted table name.</summary>
/// <param name="tableName">Name of the table.</param>
/// <param name="schemaName">Name of the schema (optional)</param>
/// <returns>The quoted table name.</returns>
public override string GetQuotedTableName(string tableName, string schemaName = null)
{
return(string.IsNullOrEmpty(schemaName)
? $"{EscapeChar}{NamingStrategy.GetTableName(tableName)}{EscapeChar}"
: $"{EscapeChar}{NamingStrategy.GetTableName(schemaName)}_{NamingStrategy.GetTableName(tableName)}{EscapeChar}");
}
public override string GetQuotedTableName(ModelDefinition modelDef)
{
return(Quote(NamingStrategy.GetTableName(modelDef)));
}
public override string ToInsertRowStatement(IDbCommand dbCommand, object objWithProperties, ICollection <string> insertFields = null)
{
if (insertFields == null)
{
insertFields = new List <string>();
}
var sbColumnNames = new StringBuilder();
var sbColumnValues = new StringBuilder();
var tableType = objWithProperties.GetType();
var modelDef = GetModel(tableType);
foreach (var fieldDef in modelDef.FieldDefinitions)
{
if (fieldDef.IsComputed)
{
continue;
}
if (insertFields.Count > 0 && !insertFields.Contains(fieldDef.Name))
{
continue;
}
if ((fieldDef.AutoIncrement || !string.IsNullOrEmpty(fieldDef.Sequence) ||
fieldDef.Name == OrmLiteConfig.IdField) &&
dbCommand != null)
{
if (fieldDef.AutoIncrement && string.IsNullOrEmpty(fieldDef.Sequence))
{
fieldDef.Sequence = Sequence(
(modelDef.IsInSchema
? modelDef.Schema + "_" + NamingStrategy.GetTableName(modelDef.ModelName)
: NamingStrategy.GetTableName(modelDef.ModelName)),
fieldDef.FieldName, fieldDef.Sequence);
}
PropertyInfo pi = tableType.GetProperty(fieldDef.Name,
BindingFlags.Instance | BindingFlags.NonPublic | BindingFlags.Public | BindingFlags.FlattenHierarchy);
var result = GetNextValue(dbCommand, fieldDef.Sequence, pi.GetValue(objWithProperties, new object[] { }));
if (pi.PropertyType == typeof(String))
{
pi.SetProperty(objWithProperties, result.ToString());
}
else if (pi.PropertyType == typeof(Int16))
{
pi.SetProperty(objWithProperties, Convert.ToInt16(result));
}
else if (pi.PropertyType == typeof(Int32))
{
pi.SetProperty(objWithProperties, Convert.ToInt32(result));
}
else if (pi.PropertyType == typeof(Guid))
{
pi.SetProperty(objWithProperties, result);
}
else
{
pi.SetProperty(objWithProperties, Convert.ToInt64(result));
}
}
if (sbColumnNames.Length > 0)
{
sbColumnNames.Append(",");
}
if (sbColumnValues.Length > 0)
{
sbColumnValues.Append(",");
}
try
{
sbColumnNames.Append(string.Format("{0}", GetQuotedColumnName(fieldDef.FieldName)));
if (!string.IsNullOrEmpty(fieldDef.Sequence) && dbCommand == null)
{
sbColumnValues.Append(string.Format("@{0}", fieldDef.Name));
}
else
{
sbColumnValues.Append(fieldDef.GetQuotedValue(objWithProperties));
}
}
catch (Exception)
{
throw;
}
}
var sql = string.Format("INSERT INTO {0} ({1}) VALUES ({2});",
GetQuotedTableName(modelDef), sbColumnNames, sbColumnValues);
return(sql);
}
public override string GetQuotedTableName(ModelDefinition modelDef)
{
return(string.Format("`{0}`", NamingStrategy.GetTableName(modelDef.ModelName)));
}
//TODO: Change to parameterized query to match all other ToInsertRowStatement() impls
public override string ToInsertRowStatement(IDbCommand dbCommand, object objWithProperties, ICollection <string> insertFields = null)
{
var sbColumnNames = StringBuilderCache.Allocate();
var sbColumnValues = StringBuilderCacheAlt.Allocate();
var tableType = objWithProperties.GetType();
var modelDef = GetModel(tableType);
var fieldDefs = GetInsertFieldDefinitions(modelDef, insertFields);
foreach (var fieldDef in fieldDefs)
{
if (fieldDef.IsComputed)
{
continue;
}
if ((fieldDef.AutoIncrement || !string.IsNullOrEmpty(fieldDef.Sequence)) &&
dbCommand != null)
{
if (fieldDef.AutoIncrement && string.IsNullOrEmpty(fieldDef.Sequence))
{
fieldDef.Sequence = Sequence(NamingStrategy.GetTableName(modelDef), fieldDef.FieldName, fieldDef.Sequence);
}
var pi = tableType.GetProperty(fieldDef.Name,
BindingFlags.Instance | BindingFlags.NonPublic | BindingFlags.Public | BindingFlags.FlattenHierarchy);
var result = GetNextValue(dbCommand.Connection, dbCommand.Transaction, fieldDef.Sequence,
pi.GetValue(objWithProperties, new object[] { }), isInsert: true);
if (pi.PropertyType == typeof(String))
{
pi.SetProperty(objWithProperties, result.ToString());
}
else if (pi.PropertyType == typeof(Int16))
{
pi.SetProperty(objWithProperties, Convert.ToInt16(result));
}
else if (pi.PropertyType == typeof(Int32))
{
pi.SetProperty(objWithProperties, Convert.ToInt32(result));
}
else if (pi.PropertyType == typeof(Guid))
{
pi.SetProperty(objWithProperties, result);
}
else
{
pi.SetProperty(objWithProperties, Convert.ToInt64(result));
}
}
if (sbColumnNames.Length > 0)
{
sbColumnNames.Append(",");
}
if (sbColumnValues.Length > 0)
{
sbColumnValues.Append(",");
}
try
{
sbColumnNames.Append(string.Format("{0}", GetQuotedColumnName(fieldDef.FieldName)));
if (!string.IsNullOrEmpty(fieldDef.Sequence) && dbCommand == null)
{
sbColumnValues.Append(string.Format(":{0}", fieldDef.Name));
}
else
{
sbColumnValues.Append(fieldDef.GetQuotedValue(objWithProperties));
}
}
catch (Exception ex)
{
Log.ErrorFormat("Error in ToInsertRowStatement on column {0}: {1}", fieldDef.FieldName, ex);
throw;
}
}
var sql = string.Format("INSERT INTO {0} ({1}) VALUES ({2}) ",
GetQuotedTableName(modelDef),
StringBuilderCache.ReturnAndFree(sbColumnNames),
StringBuilderCacheAlt.ReturnAndFree(sbColumnValues));
return(sql);
}
/// <summary>Creates parameterized insert statement.</summary>
/// <exception cref="Exception">Thrown when an exception error condition occurs.</exception>
/// <param name="connection"> The connection.</param>
/// <param name="objWithProperties">The object with properties.</param>
/// <param name="insertFields"> The insert fields.</param>
/// <returns>The new parameterized insert statement.</returns>
public override IDbCommand CreateParameterizedInsertStatement(IDbConnection connection, object objWithProperties, ICollection <string> insertFields = null)
{
if (insertFields == null)
{
insertFields = new List <string>();
}
var sbColumnNames = new StringBuilder();
var sbColumnValues = new StringBuilder();
var modelDef = GetModel(objWithProperties.GetType());
var dbCommand = connection.CreateCommand();
dbCommand.CommandTimeout = OrmLiteConfig.CommandTimeout;
foreach (var fieldDef in modelDef.FieldDefinitions)
{
if (fieldDef.IsComputed)
{
continue;
}
if (insertFields.Count > 0 && !insertFields.Contains(fieldDef.Name))
{
continue;
}
if ((fieldDef.AutoIncrement || !string.IsNullOrEmpty(fieldDef.Sequence) ||
fieldDef.Name == OrmLiteConfig.IdField) &&
dbCommand != null)
{
if (fieldDef.AutoIncrement && string.IsNullOrEmpty(fieldDef.Sequence))
{
fieldDef.Sequence = Sequence(
(modelDef.IsInSchema
? modelDef.Schema + "_" + NamingStrategy.GetTableName(modelDef.ModelName)
: NamingStrategy.GetTableName(modelDef.ModelName)),
fieldDef.FieldName, fieldDef.Sequence);
}
PropertyInfo pi = objWithProperties.GetType().GetProperty(fieldDef.Name,
BindingFlags.Instance | BindingFlags.NonPublic | BindingFlags.Public | BindingFlags.FlattenHierarchy);
var result = GetNextValue(dbCommand, fieldDef.Sequence, pi.GetValue(objWithProperties, new object[] { }));
if (pi.PropertyType == typeof(String))
{
ReflectionUtils.SetProperty(objWithProperties, pi, result.ToString());
}
else if (pi.PropertyType == typeof(Int16))
{
ReflectionUtils.SetProperty(objWithProperties, pi, Convert.ToInt16(result));
}
else if (pi.PropertyType == typeof(Int32))
{
ReflectionUtils.SetProperty(objWithProperties, pi, Convert.ToInt32(result));
}
else if (pi.PropertyType == typeof(Guid))
{
ReflectionUtils.SetProperty(objWithProperties, pi, result);
}
else
{
ReflectionUtils.SetProperty(objWithProperties, pi, Convert.ToInt64(result));
}
}
//insertFields contains Property "Name" of fields to insert ( that's how expressions work )
if (insertFields.Count > 0 && !insertFields.Contains(fieldDef.Name))
{
continue;
}
if (sbColumnNames.Length > 0)
{
sbColumnNames.Append(",");
}
if (sbColumnValues.Length > 0)
{
sbColumnValues.Append(",");
}
try
{
sbColumnNames.Append(GetQuotedColumnName(fieldDef.FieldName));
sbColumnValues.Append(ParamString)
.Append(fieldDef.FieldName);
AddParameterForFieldToCommand(dbCommand, fieldDef, objWithProperties);
}
catch (Exception ex)
{
Log.Error("ERROR in CreateParameterizedInsertStatement(): " + ex.Message, ex);
throw;
}
}
dbCommand.CommandText = string.Format("INSERT INTO {0} ({1}) VALUES ({2})",
GetQuotedTableName(modelDef), sbColumnNames, sbColumnValues);
return(dbCommand);
}
public override string GetQuotedTableName(string tableName)
{
return(string.Format("`{0}`", NamingStrategy.GetTableName(tableName)));
}
public override string ToChangeColumnNameStatement(Type modelType, FieldDefinition fieldDef, string oldColumnName)
{
var objectName = $"{NamingStrategy.GetTableName(GetModel(modelType).ModelName)}.{oldColumnName}";
return($"sp_rename ({GetQuotedValue(objectName)}, {GetQuotedValue(fieldDef.FieldName)}, {GetQuotedValue("COLUMN")});");
}
public override string GetTableName(string table, string schema = null)
{
return(schema != null
? $"{NamingStrategy.GetSchemaName(schema)}_{NamingStrategy.GetTableName(table)}"
: NamingStrategy.GetTableName(table));
}
public override string GetCreateSchemaStatement(string schema, bool ignoreIfExists)
{
return($"CREATE SCHEMA {(ignoreIfExists ? "IF NOT EXISTS" : string.Empty)} {NamingStrategy.GetTableName(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));
}
public override string ToCreateTableStatement(Type tableType)
{
var sbColumns = new StringBuilder();
var sbConstraints = new StringBuilder();
var sbPk = new StringBuilder();
var modelDef = GetModel(tableType);
foreach (var fieldDef in modelDef.FieldDefinitions)
{
if (fieldDef.IsPrimaryKey)
{
sbPk.AppendFormat(sbPk.Length != 0 ? ",{0}" : "{0}", GetQuotedColumnName(fieldDef.FieldName));
}
if (sbColumns.Length != 0)
{
sbColumns.Append(", \n ");
}
var columnDefinition = GetColumnDefinition(
fieldDef.FieldName,
fieldDef.FieldType,
fieldDef.IsPrimaryKey,
fieldDef.AutoIncrement,
fieldDef.IsNullable,
fieldDef.FieldLength,
fieldDef.Scale,
fieldDef.DefaultValue);
sbColumns.Append(columnDefinition);
if (fieldDef.ReferencesType == null)
{
continue;
}
var refModelDef = GetModel(fieldDef.ReferencesType);
var modelName = modelDef.IsInSchema
? modelDef.Schema + "_" + NamingStrategy.GetTableName(modelDef.ModelName)
: NamingStrategy.GetTableName(modelDef.ModelName);
var refModelName = refModelDef.IsInSchema
? refModelDef.Schema + "_" + NamingStrategy.GetTableName(refModelDef.ModelName)
: NamingStrategy.GetTableName(refModelDef.ModelName);
sbConstraints.AppendFormat(", \n\n CONSTRAINT {0} FOREIGN KEY ({1}) REFERENCES {2} ({3})",
GetQuotedName(string.Format("FK_{0}_{1}_{2}", modelName, refModelName, fieldDef.FieldName)),
GetQuotedColumnName(fieldDef.FieldName),
GetQuotedTableName(refModelDef),
GetQuotedColumnName(refModelDef.PrimaryKey.FieldName));
}
if (sbPk.Length != 0)
{
sbColumns.AppendFormat(", \n PRIMARY KEY({0})", sbPk);
}
var sql = new StringBuilder(string.Format(
"CREATE TABLE {0} \n(\n {1}{2} \n); \n",
GetQuotedTableName(modelDef),
sbColumns,
sbConstraints));
return(sql.ToString());
}
public override List <string> SequenceList(Type tableType)
{
var gens = new List <string>();
var modelDef = GetModel(tableType);
foreach (var fieldDef in modelDef.FieldDefinitions)
{
if (fieldDef.AutoIncrement || !fieldDef.Sequence.IsNullOrEmpty())
{
var seqName = Sequence((modelDef.IsInSchema
? modelDef.Schema + "_" + NamingStrategy.GetTableName(modelDef.ModelName)
: NamingStrategy.GetTableName(modelDef.ModelName)), fieldDef.FieldName, fieldDef.Sequence);
if (gens.IndexOf(seqName) == -1)
{
gens.Add(seqName);
}
}
}
return(gens);
}
public override string ToCreateSequenceStatement(Type tableType, string sequenceName)
{
var result = "";
var modelDef = GetModel(tableType);
foreach (var fieldDef in modelDef.FieldDefinitions)
{
if (fieldDef.AutoIncrement || !fieldDef.Sequence.IsNullOrEmpty())
{
string seqName = Sequence((modelDef.IsInSchema
? modelDef.Schema + "_" + NamingStrategy.GetTableName(modelDef.ModelName)
: NamingStrategy.GetTableName(modelDef.ModelName)), fieldDef.FieldName,
fieldDef.Sequence);
if (seqName.EqualsIgnoreCase(sequenceName))
{
result = "CREATE SEQUENCE " + seqName;
break;
}
}
}
return(result);
}