protected override void AddSourceValue(
ValueToSqlConverter valueConverter,
ColumnDescriptor column,
SqlDataType columnType,
object value,
bool isFirstRow,
bool isLastRow)
{
// informix have really hard times to recognize it's own types, so in source we need to specify type
// hint for most of types
if (value != null)
{
if (!valueConverter.TryConvert(Command, columnType, value))
{
AddSourceValueAsParameter(column.DataType, value);
// even for parameters
WriteTypeHint(column, columnType);
}
else
{
if (value is decimal && columnType.Precision == null &&
(columnType.DataType == DataType.Decimal || columnType.DataType == DataType.Undefined))
{
var decValue = (decimal)value;
var precision = 0;
var str = decValue.ToString(CultureInfo.InvariantCulture);
var dotIndex = str.IndexOf(".");
if (dotIndex >= 0)
{
precision = str.Substring(0, dotIndex).TrimStart('0').Length;
}
var scale = BitConverter.GetBytes(decimal.GetBits(decValue)[3])[2];
precision += scale;
columnType = new SqlDataType(DataType.Decimal, columnType.Type, null, precision, scale);
}
// this is the only place where hint is not required for some types but it doesn't make sense to
// write extra logic to detect when it could be skipped
WriteTypeHint(column, columnType);
}
}
else
{
Command.Append("NULL");
WriteTypeHint(column, columnType);
}
}
public MappingSchema(string configuration, params MappingSchema[] schemas)
{
var schemaInfo = new MappingSchemaInfo(configuration);
if (schemas == null || schemas.Length == 0)
{
Schemas = new[] { schemaInfo, Default.Schemas[0] };
ValueToSqlConverter = new ValueToSqlConverter(Default.ValueToSqlConverter);
}
else if (schemas.Length == 1)
{
Schemas = new MappingSchemaInfo[1 + schemas[0].Schemas.Length];
Schemas[0] = schemaInfo;
Array.Copy(schemas[0].Schemas, 0, Schemas, 1, schemas[0].Schemas.Length);
var baseConverters = new ValueToSqlConverter[1 + schemas[0].ValueToSqlConverter.BaseConverters.Length];
baseConverters[0] = schemas[0].ValueToSqlConverter;
Array.Copy(schemas[0].ValueToSqlConverter.BaseConverters, 0, baseConverters, 1, schemas[0].ValueToSqlConverter.BaseConverters.Length);
ValueToSqlConverter = new ValueToSqlConverter(baseConverters);
}
else
{
var schemaList = new Dictionary <MappingSchemaInfo, int>(schemas.Length);
var baseConverters = new Dictionary <ValueToSqlConverter, int>(10);
var i = 0;
var j = 0;
schemaList[schemaInfo] = i++;
foreach (var schema in schemas)
{
foreach (var sc in schema.Schemas)
{
schemaList[sc] = i++;
}
baseConverters[schema.ValueToSqlConverter] = j++;
foreach (var bc in schema.ValueToSqlConverter.BaseConverters)
{
baseConverters[bc] = j++;
}
}
Schemas = schemaList.OrderBy(_ => _.Value).Select(_ => _.Key).ToArray();
ValueToSqlConverter = new ValueToSqlConverter(baseConverters.OrderBy(_ => _.Value).Select(_ => _.Key).ToArray());
}
}
public DefaultMappingSchema()
: base(new MappingSchemaInfo("") { MetadataReader = Metadata.MetadataReader.Default })
{
AddScalarType(typeof(char), DataType.NChar);
AddScalarType(typeof(char?), DataType.NChar);
AddScalarType(typeof(string), DataType.NVarChar);
AddScalarType(typeof(decimal), DataType.Decimal);
AddScalarType(typeof(decimal?), DataType.Decimal);
AddScalarType(typeof(DateTime), DataType.DateTime2);
AddScalarType(typeof(DateTime?), DataType.DateTime2);
AddScalarType(typeof(DateTimeOffset), DataType.DateTimeOffset);
AddScalarType(typeof(DateTimeOffset?), DataType.DateTimeOffset);
AddScalarType(typeof(TimeSpan), DataType.Time);
AddScalarType(typeof(TimeSpan?), DataType.Time);
AddScalarType(typeof(byte[]), DataType.VarBinary);
AddScalarType(typeof(Binary), DataType.VarBinary);
AddScalarType(typeof(Guid), DataType.Guid);
AddScalarType(typeof(Guid?), DataType.Guid);
AddScalarType(typeof(object), DataType.Variant);
#if !SILVERLIGHT && !NETFX_CORE
AddScalarType(typeof(XmlDocument), DataType.Xml);
#endif
AddScalarType(typeof(XDocument), DataType.Xml);
AddScalarType(typeof(bool), DataType.Boolean);
AddScalarType(typeof(bool?), DataType.Boolean);
AddScalarType(typeof(sbyte), DataType.SByte);
AddScalarType(typeof(sbyte?), DataType.SByte);
AddScalarType(typeof(short), DataType.Int16);
AddScalarType(typeof(short?), DataType.Int16);
AddScalarType(typeof(int), DataType.Int32);
AddScalarType(typeof(int?), DataType.Int32);
AddScalarType(typeof(long), DataType.Int64);
AddScalarType(typeof(long?), DataType.Int64);
AddScalarType(typeof(byte), DataType.Byte);
AddScalarType(typeof(byte?), DataType.Byte);
AddScalarType(typeof(ushort), DataType.UInt16);
AddScalarType(typeof(ushort?), DataType.UInt16);
AddScalarType(typeof(uint), DataType.UInt32);
AddScalarType(typeof(uint?), DataType.UInt32);
AddScalarType(typeof(ulong), DataType.UInt64);
AddScalarType(typeof(ulong?), DataType.UInt64);
AddScalarType(typeof(float), DataType.Single);
AddScalarType(typeof(float?), DataType.Single);
AddScalarType(typeof(double), DataType.Double);
AddScalarType(typeof(double?), DataType.Double);
AddScalarType(typeof(BitArray), DataType.BitArray);
ValueToSqlConverter.SetDefauls();
}
public MultipleRowsHelper(DataConnection dataConnection, BulkCopyOptions options, bool enforceKeepIdentity)
{
DataConnection = dataConnection;
Options = options;
SqlBuilder = dataConnection.DataProvider.CreateSqlBuilder();
ValueConverter = dataConnection.MappingSchema.ValueToSqlConverter;
Descriptor = dataConnection.MappingSchema.GetEntityDescriptor(typeof(T));
Columns = Descriptor.Columns
.Where(c => !c.SkipOnInsert || enforceKeepIdentity && options.KeepIdentity == true && c.IsIdentity)
.ToArray();
ColumnTypes =
Columns.Select(c => new SqlDataType(c.DataType, c.MemberType, c.Length, c.Precision, c.Scale)).ToArray();
ParameterName = SqlBuilder.Convert("p", ConvertType.NameToQueryParameter).ToString();
TableName = BasicBulkCopy.GetTableName(SqlBuilder, options, Descriptor);
BatchSize = Math.Max(10, Options.MaxBatchSize ?? 1000);
}
protected override void AddSourceValue(ValueToSqlConverter valueConverter, ColumnDescriptor column, SqlDataType columnType, object value, bool isFirstRow, bool isLastRow)
{
if (!_columnTypedTracker.ContainsKey(column))
{
_columnTypedTracker.Add(column, value != null);
}
if (value == null)
{
/* DB2 doesn't like NULLs without type information
* : ERROR [42610] [IBM][DB2/NT64] SQL0418N The statement was not processed because the statement
* contains an invalid use of one of the following: an untyped parameter marker, the DEFAULT keyword
* , or a null value.
*
* See https://stackoverflow.com/questions/13381898
*
* Unfortunatelly, just use typed parameter doesn't help
*
* To fix it we need to cast at least one NULL in column if all column values are null.
* We will do it for last row, when we know that there is no non-null values in column and type hint
* needed.
*
* One thing I don't like is that in some cases DB2 can process query without type hints
*/
if (isLastRow && !_columnTypedTracker[column])
{
// we type only NULLs in last row and only if there is no non-NULL values in other rows
// adding casts in other cases not required and could even lead to type conflicts if type in
// cast not compatible with non-null values in column
Command.Append("CAST(NULL AS ");
BuildColumnType(column, columnType);
Command.Append(")");
return;
}
}
else
{
_columnTypedTracker[column] = true;
}
base.AddSourceValue(valueConverter, column, columnType, value, isFirstRow, isLastRow);
}
protected override void AddSourceValue(ValueToSqlConverter valueConverter, ColumnDescriptor column, SqlDataType columnType, object value, bool isFirstRow, bool isLastRow)
{
if (value == null || value is INullable && ((INullable)value).IsNull)
{
var casttype = (columnType.DataType == DataType.Undefined) ?
columnType.Type.GetTypeForCast(MapGuidAsString) :
SqlDataType.GetDataType(columnType.DataType).GetiSeriesType(MapGuidAsString);
Command.Append($"CAST(NULL AS {casttype})");
return;
}
// avoid parameters in source due to low limits for parameters number in providers
if (!valueConverter.TryConvert(Command, columnType, value))
{
var colType = value.GetType().GetTypeForCast(MapGuidAsString);
// we have to use parameter wrapped in a cast
var name = GetNextParameterName();
var fullName = SqlBuilder.Convert(name, ConvertType.NameToQueryParameter).ToString();
Command.Append($"CAST({fullName} as {colType})");
AddParameter(new DataParameter(name, value, column.DataType));
}
}
public override ISqlExpression GetIdentityExpression(SqlTable table)
{
if (!table.SequenceAttributes.IsNullOrEmpty())
{
var attr = GetSequenceNameAttribute(table, false);
if (attr != null)
{
var name = Convert(attr.SequenceName, ConvertType.NameToQueryTable).ToString();
var database = GetTableDatabaseName(table);
var schema = attr.Schema != null
? Convert(attr.Schema, ConvertType.NameToSchema).ToString()
: GetTableSchemaName(table);
var sb = new StringBuilder();
sb.Append("nextval(");
ValueToSqlConverter.Convert(sb, BuildTableName(new StringBuilder(), database, schema, name).ToString());
sb.Append(")");
return(new SqlExpression(sb.ToString(), Precedence.Primary));
}
}
return(base.GetIdentityExpression(table));
}
protected override void AddSourceValue(
ValueToSqlConverter valueConverter,
ColumnDescriptor column,
SqlDataType columnType,
object value,
bool isFirstRow,
bool isLastRow)
{
if (value != null)
{
var dataType = columnType.DataType != DataType.Undefined
? columnType.DataType
: DataContext.MappingSchema.GetDataType(column.MemberType).DataType;
if (dataType == DataType.Binary || dataType == DataType.VarBinary)
{
// don't generate binary literal in source, as it could lead to huge SQL
AddSourceValueAsParameter(dataType, column.DbType, value);
return;
}
}
base.AddSourceValue(valueConverter, column, columnType, value, isFirstRow, isLastRow);
}
internal MappingSchema(MappingSchemaInfo mappingSchemaInfo)
{
Schemas = new[] { mappingSchemaInfo };
ValueToSqlConverter = new ValueToSqlConverter();
}
public MappingSchema(string configuration, params MappingSchema[] schemas)
{
var schemaInfo = new MappingSchemaInfo(configuration);
if (schemas == null || schemas.Length == 0)
{
Schemas = new[] { schemaInfo, Default.Schemas[0] };
ValueToSqlConverter = new ValueToSqlConverter(Default.ValueToSqlConverter);
}
else if (schemas.Length == 1)
{
Schemas = new MappingSchemaInfo[1 + schemas[0].Schemas.Length];
Schemas[0] = schemaInfo;
Array.Copy(schemas[0].Schemas, 0, Schemas, 1, schemas[0].Schemas.Length);
var baseConverters = new ValueToSqlConverter[1 + schemas[0].ValueToSqlConverter.BaseConverters.Length];
baseConverters[0] = schemas[0].ValueToSqlConverter;
Array.Copy(schemas[0].ValueToSqlConverter.BaseConverters, 0, baseConverters, 1, schemas[0].ValueToSqlConverter.BaseConverters.Length);
ValueToSqlConverter = new ValueToSqlConverter(baseConverters);
}
else
{
var schemaList = new List <MappingSchemaInfo>(10)
{
schemaInfo
};
var baseConverters = new List <ValueToSqlConverter>(10);
foreach (var schema in schemas)
{
foreach (var sc in schema.Schemas)
{
if (schemaList.Contains(sc))
{
schemaList.Remove(sc);
}
schemaList.Add(sc);
}
if (baseConverters.Contains(schema.ValueToSqlConverter))
{
baseConverters.Remove(schema.ValueToSqlConverter);
}
baseConverters.Add(schema.ValueToSqlConverter);
foreach (var bc in schema.ValueToSqlConverter.BaseConverters)
{
if (baseConverters.Contains(bc))
{
baseConverters.Remove(bc);
}
baseConverters.Add(bc);
}
}
Schemas = schemaList.ToArray();
ValueToSqlConverter = new ValueToSqlConverter(baseConverters.ToArray());
}
}
public SapHanaOdbcSqlBuilder(ISqlOptimizer sqlOptimizer, SqlProviderFlags sqlProviderFlags, ValueToSqlConverter valueToSqlConverter)
: base(sqlOptimizer, sqlProviderFlags, valueToSqlConverter)
{
}
protected DB2SqlBuilderBase(ISqlOptimizer sqlOptimizer, SqlProviderFlags sqlProviderFlags, ValueToSqlConverter valueToSqlConverter)
: base(sqlOptimizer, sqlProviderFlags, valueToSqlConverter)
{
}
SybaseSqlBuilder(bool skipAliases, ISqlOptimizer sqlOptimizer, SqlProviderFlags sqlProviderFlags, ValueToSqlConverter valueToSqlConverter)
: base(sqlOptimizer, sqlProviderFlags, valueToSqlConverter)
{
_skipAliases = skipAliases;
}
public AccessSqlBuilder_OleDb(ISqlOptimizer sqlOptimizer, SqlProviderFlags sqlProviderFlags, ValueToSqlConverter valueToSqlConverter)
: base(sqlOptimizer, sqlProviderFlags, valueToSqlConverter)
{
}
public FirebirdSqlBuilder(ISqlOptimizer sqlOptimizer, SqlProviderFlags sqlProviderFlags, ValueToSqlConverter valueToSqlConverter)
: base(sqlOptimizer, sqlProviderFlags, valueToSqlConverter)
{
}
public DB2iSeriesSqlBuilder7_2(ISqlOptimizer sqlOptimizer, SqlProviderFlags sqlProviderFlags, ValueToSqlConverter valueToSqlConverter)
: base(sqlOptimizer, sqlProviderFlags, valueToSqlConverter)
{
}
protected override void AddSourceValue(
ValueToSqlConverter valueConverter,
ColumnDescriptor column,
SqlDataType columnType,
object value,
bool isFirstRow)
{
// strange thing, that real type needs explicit typing only on some combinations of columns and values
// from other side, for Sybase it is not surprising
if (column.DataType == DataType.Single || (column.DataType == DataType.Undefined && column.MemberType.ToNullableUnderlying() == typeof(float)))
{
Command.Append("CAST(");
base.AddSourceValue(valueConverter, column, columnType, value, isFirstRow);
Command.Append(" AS REAL)");
}
else if (column.DataType == DataType.DateTime || column.DataType == DataType.DateTime2)
{
Command.Append("CAST(");
base.AddSourceValue(valueConverter, column, columnType, value, isFirstRow);
Command.Append(" AS DATETIME)");
}
else if (column.DataType == DataType.Date)
{
Command.Append("CAST(");
base.AddSourceValue(valueConverter, column, columnType, value, isFirstRow);
Command.Append(" AS DATE)");
}
else if (column.DataType == DataType.Time)
{
Command.Append("CAST(");
base.AddSourceValue(valueConverter, column, columnType, value, isFirstRow);
Command.Append(" AS TIME)");
}
else if (isFirstRow && value == null)
{
string type;
if (column.DbType != null)
{
type = column.DbType;
}
else
{
var dataType = column.DataType != DataType.Undefined ?
column.DataType :
DataContext.MappingSchema.GetDataType(column.MemberType).DataType;
switch (dataType)
{
case DataType.Double: type = "FLOAT"; break;
case DataType.Single: type = "REAL"; break;
case DataType.SByte: type = "TINYINT"; break;
case DataType.UInt16: type = "INT"; break;
case DataType.UInt32: type = "BIGINT"; break;
case DataType.UInt64: type = "DECIMAL"; break;
case DataType.Byte: type = "TINYINT"; break;
case DataType.Int16: type = "SMALLINT"; break;
case DataType.Int32: type = "INT"; break;
case DataType.Int64: type = "BIGINT"; break;
case DataType.Boolean: type = "BIT"; break;
default: type = null; break;
}
}
if (type != null)
{
Command
.Append("CAST(NULL AS ")
.Append(type)
;
if (column.Length > 0)
{
Command
.Append('(')
.Append(column.Length)
.Append(')')
;
}
if (column.Precision > 0)
{
Command
.Append('(')
.Append(column.Precision)
.Append(',')
.Append(column.Scale)
.Append(')')
;
}
Command
.Append(")")
;
}
else
{
base.AddSourceValue(valueConverter, column, columnType, value, isFirstRow);
}
}
else
{
base.AddSourceValue(valueConverter, column, columnType, value, isFirstRow);
}
}
SybaseSqlBuilder(bool skipAliases, ISqlOptimizer sqlOptimizer, SqlProviderFlags sqlProviderFlags, ValueToSqlConverter valueToSqlConverter)
: base(sqlOptimizer, sqlProviderFlags, valueToSqlConverter)
{
_skipAliases = skipAliases;
}
public DB2iSeriesSqlBuilder(ISqlOptimizer sqlOptimizer, SqlProviderFlags sqlProviderFlags,
ValueToSqlConverter valueToSqlConverter)
: base(sqlOptimizer, sqlProviderFlags, valueToSqlConverter)
{
this.mapGuidAsString = sqlProviderFlags.CustomFlags.Contains(DB2iSeriesTools.MapGuidAsString);
}
public Npgsql4PostgreSQLSqlBuilder(Npgsql4PostgreSQLDataProvider provider, ISqlOptimizer sqlOptimizer, SqlProviderFlags sqlProviderFlags, ValueToSqlConverter valueToSqlConverter)
: base(provider, sqlOptimizer, sqlProviderFlags, valueToSqlConverter)
{
_provider = provider;
}
public DB2iSeriesSqlBuilder7_2(ISqlOptimizer sqlOptimizer, SqlProviderFlags sqlProviderFlags,
ValueToSqlConverter valueToSqlConverter, DB2iSeriesNamingConvention namingConvention = default)
: base(sqlOptimizer, sqlProviderFlags, valueToSqlConverter, namingConvention)
{
}
public PostgreSQLSqlBuilder(ISqlOptimizer sqlOptimizer, SqlProviderFlags sqlProviderFlags, ValueToSqlConverter valueToSqlConverter)
: base(sqlOptimizer, sqlProviderFlags, valueToSqlConverter)
{
}
public PostgreSQLSqlBuilder(PostgreSQLDataProvider provider, ISqlOptimizer sqlOptimizer, SqlProviderFlags sqlProviderFlags, ValueToSqlConverter valueToSqlConverter)
: this(sqlOptimizer, sqlProviderFlags, valueToSqlConverter)
{
_provider = provider;
}
public SqlCeSqlBuilder(ISqlOptimizer sqlOptimizer, SqlProviderFlags sqlProviderFlags, ValueToSqlConverter valueToSqlConverter)
: base(sqlOptimizer, sqlProviderFlags, valueToSqlConverter)
{
}
protected SqlServerSqlBuilder(ISqlOptimizer sqlOptimizer, SqlProviderFlags sqlProviderFlags, ValueToSqlConverter valueToSqlConverter)
: base(sqlOptimizer, sqlProviderFlags, valueToSqlConverter)
{
}
