protected virtual void ContributeParameter(IDbCommand command, string name, object value, DocumentMap mapping = null)
{
if (value == null)
{
command.Parameters.Add(new SqlParameter(name, DBNull.Value));
return;
}
if (value is IEnumerable && (value is string) == false && (value is byte[]) == false)
{
var inClauseNames = new List <string>();
var i = 0;
foreach (var inClauseValue in (IEnumerable)value)
{
var inClauseName = name + "_" + i;
inClauseNames.Add(inClauseName);
ContributeParameter(command, inClauseName, inClauseValue);
i++;
}
if (i == 0)
{
var inClauseName = name + "_" + i;
inClauseNames.Add(inClauseName);
ContributeParameter(command, inClauseName, null);
}
var originalParameter = Regex.Escape("@" + name.TrimStart('@'));
var replacementParameters = "(" + string.Join(", ", inClauseNames.Select(x => "@" + x)) + ")";
command.CommandText = Regex.Replace(command.CommandText, originalParameter, match => replacementParameters, RegexOptions.IgnoreCase);
return;
}
var columnType = DatabaseTypeConverter.AsDbType(value.GetType());
var param = new SqlParameter();
param.ParameterName = name;
param.DbType = columnType;
param.Value = value;
// To assist SQL's query plan caching, assign a parameter size for our
// common id lookups where possible.
if (mapping != null &&
mapping.IdColumn != null &&
mapping.IdColumn.MaxLength > 0 &&
columnType == DbType.String &&
string.Equals(name, "Id", StringComparison.OrdinalIgnoreCase))
{
param.Size = mapping.IdColumn.MaxLength;
}
command.Parameters.Add(param);
}
protected virtual void ContributeParameter(IDbCommand command, ITypeHandlerRegistry typeHandlers, string name, object value, DocumentMap mapping = null)
{
if (value == null)
{
command.Parameters.Add(new SqlParameter(name, DBNull.Value));
return;
}
var typeHandler = typeHandlers.Resolve(value.GetType());
if (typeHandler != null)
{
var p = new SqlParameter(name, SqlDbType.NVarChar);
typeHandler.WriteDatabase(p, value);
command.Parameters.Add(p);
return;
}
if (value is TableValuedParameter tvp && command is SqlCommand sqlCommand)
{
var p = sqlCommand.Parameters.Add(name, SqlDbType.Structured);
p.Value = tvp.DataRecords;
p.TypeName = tvp.TypeName;
return;
}
if (value is IEnumerable && (value is string) == false && (value is byte[]) == false)
{
var inClauseNames = new List <string>();
var i = 0;
var inClauseValues = ((IEnumerable)value).Cast <object>().ToList();
ListExtender.ExtendListRepeatingLastValue(inClauseValues);
foreach (var inClauseValue in inClauseValues)
{
i++;
var inClauseName = name + "_" + i;
inClauseNames.Add(inClauseName);
ContributeParameter(command, typeHandlers, inClauseName, inClauseValue);
}
if (i == 0)
{
var inClauseName = name + "_" + i;
inClauseNames.Add(inClauseName);
ContributeParameter(command, typeHandlers, inClauseName, null);
}
var originalParameter = Regex.Escape("@" + name.TrimStart('@')) + @"(?=[^\w\$@#_]|$)";
var replacementParameters = "(" + string.Join(", ", inClauseNames.Select(x => "@" + x)) + ")";
command.CommandText = Regex.Replace(command.CommandText, originalParameter, match => replacementParameters, RegexOptions.IgnoreCase);
return;
}
var columnType = DatabaseTypeConverter.AsDbType(value.GetType());
if (columnType == null)
{
throw new InvalidOperationException($"Cannot map type '{value.GetType().FullName}' to a DbType. Consider providing a custom ITypeHandler.");
}
var param = new SqlParameter();
param.ParameterName = name;
param.DbType = columnType.Value;
param.Value = value;
if (columnType == DbType.String && value is string text)
{
var size = GetBestSizeBucket(text);
if (size > 0)
{
param.Size = size;
}
}
// To assist SQL's query plan caching, assign a parameter size for our
// common id lookups where possible.
if (mapping != null &&
mapping.IdColumn != null &&
mapping.IdColumn.MaxLength > 0 &&
columnType == DbType.String &&
string.Equals(name, mapping.IdColumn.ColumnName, StringComparison.OrdinalIgnoreCase))
{
if (mapping.IdColumn.MaxLength != null)
{
param.Size = mapping.IdColumn.MaxLength.Value;
}
}
if (columnType == DbType.String && mapping != null)
{
var indexed = mapping.WritableIndexedColumns();
var columnMap = indexed.FirstOrDefault(i => string.Equals(i.ColumnName, param.ParameterName, StringComparison.OrdinalIgnoreCase));
if (columnMap != null && columnMap.MaxLength != null)
{
param.Size = columnMap.MaxLength.Value;
}
}
command.Parameters.Add(param);
}
static string GetDatabaseType(ColumnMapping column)
{
var dbType = DatabaseTypeConverter.AsDbType(column.Type);
switch (dbType)
{
case DbType.AnsiString:
return("varchar" + GetLength(column));
case DbType.AnsiStringFixedLength:
return("char");
case DbType.Binary:
return("varbinary(max)");
case DbType.Byte:
return("tinyint");
case DbType.Boolean:
return("bit");
case DbType.Currency:
return("decimal");
case DbType.Date:
return("date");
case DbType.DateTime:
return("datetime");
case DbType.Decimal:
return("decimal");
case DbType.Double:
return("float");
case DbType.Guid:
return("uniqueidentifier");
case DbType.Int16:
return("smallint");
case DbType.Int32:
return("int");
case DbType.Int64:
return("bigint");
case DbType.SByte:
break;
case DbType.Single:
break;
case DbType.StringFixedLength:
return("nchar");
case DbType.String:
return("nvarchar" + GetLength(column));
case DbType.Time:
return("time");
case DbType.DateTime2:
case DbType.DateTimeOffset:
return("datetimeoffset");
}
return("nvarchar(max)");
}