public MsSqlDialect(MsSqlDbDriver driver) : base(driver)
{
this.Driver = driver;
base.MaxParamCount = 2100;
base.MaxRecordsInInsertMany = 500; //actual is 1000, but just to be careful
base.DynamicSqlParameterPrefix = "@P";
base.BatchBeginTransaction = new TextSqlFragment("BEGIN TRANSACTION;");
base.BatchCommitTransaction = new TextSqlFragment("COMMIT TRANSACTION;");
base.DDLSeparator = Environment.NewLine + "GO" + Environment.NewLine;
//Change Count() to COUNT_BIG - COUNT is not allowed inside views, so we change default to Count_BIG
base.SqlCountStar = new TextSqlFragment("COUNT_BIG(*)");
base.OffsetTemplate = new SqlTemplate("OFFSET {0} ROWS");
base.OffsetLimitTemplate = new SqlTemplate("OFFSET {0} ROWS FETCH NEXT {1} ROWS ONLY");
}
public override void SetDbParameterValue(IDbDataParameter parameter, Type type, object value)
{
if (value == null)
{
return;
}
//Check for array
Type elemType;
// quick check for array or gen type, then deeper check for list of db primitives
if ((type.IsArray || type.IsGenericType) && type.IsListOfDbPrimitive(out elemType))
{
var sqlParam = (SqlParameter)parameter;
sqlParam.SqlDbType = SqlDbType.Structured;
sqlParam.TypeName = GetArrayAsTableTypeName();
sqlParam.Value = MsSqlDbDriver.ConvertListToRecordList(value as IEnumerable);
return;
}
base.SetDbParameterValue(parameter, type, value);
if (parameter.DbType == DbType.DateTime)
{
parameter.DbType = DbType.DateTime2;
}
}
public MsSqlTypeRegistry(MsSqlDbDriver driver)
: base(driver)
{
//numerics
AddType("real", DbType.Single, typeof(Single), SqlDbType.Real);
AddType("float", DbType.Double, typeof(Double), SqlDbType.Float);
AddType("decimal", DbType.Decimal, typeof(Decimal), SqlDbType.Decimal, args: "({precision},{scale})", aliases: "numeric");
//integers
// Note: for int types that do not have direct match in database type set, we assign bigger int type in database
// For example, signed byte (CLR sbyte) does not have direct match, so we use 'smallint' to handle it in the database.
// For ulong (UInt64) CLR type there's no bigger type, so we handle it using 'bigint' which is signed 64-bit int; as a result there might be overflows
// in some cases.
AddType("int", DbType.Int32, typeof(int), SqlDbType.Int, clrTypes: new Type[] { typeof(UInt16) });
AddType("smallint", DbType.Int16, typeof(Int16), SqlDbType.SmallInt, clrTypes: new Type[] { typeof(sbyte) });
AddType("tinyint", DbType.Byte, typeof(byte), SqlDbType.TinyInt);
AddType("bigint", DbType.Int64, typeof(Int64), SqlDbType.BigInt, clrTypes: new Type[] { typeof(UInt32), typeof(UInt64) });
// Bool
AddType("bit", DbType.Boolean, typeof(bool), SqlDbType.Bit);
// Strings
AddType("nchar", DbType.StringFixedLength, typeof(string), SqlDbType.NChar, args: "({size})", isDefault: false, clrTypes: new Type[] { typeof(char) });
AddType("varchar", DbType.AnsiString, typeof(string), SqlDbType.VarChar, args: "({size})", isDefault: false, memoArgs: "(max)");
AddType("char", DbType.AnsiStringFixedLength, typeof(string), SqlDbType.Char, args: "({size})", isDefault: false);
AddType("ntext", DbType.String, typeof(string), SqlDbType.NText, supportsMemo: true, isDefault: false);
AddType("text", DbType.AnsiString, typeof(string), SqlDbType.Text, supportsMemo: true, isDefault: false);
// just to constantly verify that it works correctly - we add default db type for 'String' last. Nevertheless, it should be picked up for strings by default, so order should not matter.
AddType("nvarchar", DbType.String, typeof(string), SqlDbType.NVarChar, supportsMemo: true, args: "({size})", memoArgs: "(max)");
// Datetime
AddType("datetime2", DbType.DateTime2, typeof(DateTime), SqlDbType.DateTime2);
AddType("date", DbType.Date, typeof(DateTime), SqlDbType.Date, isDefault: false);
AddType("time", DbType.Time, typeof(TimeSpan), SqlDbType.Time);
AddType("datetime", DbType.DateTime, typeof(DateTime), SqlDbType.DateTime, isDefault: false, valueToLiteral: DateTimeToLiteralMsSql);
AddType("smalldatetime", DbType.DateTime, typeof(DateTime), SqlDbType.SmallDateTime, isDefault: false,
valueToLiteral: DbValueToLiteralConverters.DateTimeToLiteralNoMs);
// Binaries
var binTypes = new Type[] { typeof(Vita.Common.Binary) };
AddType("varbinary", DbType.Binary, typeof(byte[]), SqlDbType.VarBinary, supportsMemo: true, args: "({size})", memoArgs: "(max)", clrTypes: binTypes);
AddType("binary", DbType.Binary, typeof(byte[]), SqlDbType.Binary, isDefault: false, args: "({size})", clrTypes: binTypes);
AddType("image", DbType.Binary, typeof(byte[]), SqlDbType.Image, supportsMemo: true, isDefault: false, clrTypes: binTypes);
AddType("uniqueidentifier", DbType.Guid, typeof(Guid), SqlDbType.UniqueIdentifier);
AddType("DateTimeOffset", DbType.DateTimeOffset, typeof(DateTimeOffset), SqlDbType.DateTimeOffset);
AddType("money", DbType.Currency, typeof(Decimal), SqlDbType.Money, isDefault: false);
// MS SQL specific types.
// Note: DbType.Object is for "... general type representing any reference or value type not explicitly represented by another DbType value."
// So we use DbType.Object for such cases
AddType("smallmoney", DbType.Currency, typeof(Decimal), SqlDbType.SmallMoney, isDefault: false);
AddType("hierarchyid", DbType.Object, typeof(byte[]), SqlDbType.Binary, isDefault: false, clrTypes: binTypes);
AddType("geography", DbType.Object, typeof(byte[]), SqlDbType.Udt, isDefault: false, supportsMemo: true, clrTypes: binTypes);
AddType("geometry", DbType.Object, typeof(byte[]), SqlDbType.Udt, isDefault: false, supportsMemo: true, clrTypes: binTypes);
AddType("timestamp", DbType.Binary, typeof(byte[]), SqlDbType.Timestamp, isDefault: false, aliases: "rowversion");
AddType("xml", DbType.Object, typeof(string), SqlDbType.Xml, isDefault: false, supportsMemo: true);
AddType("sql_variant", DbType.Object, typeof(object), SqlDbType.Variant, isDefault: false, valueToLiteral: DbValueToLiteralConverters.DefaultValueToLiteral);
// the following types should be treated as auto-memo - whenevery property specifies this type explicitly through [Column(TypeName=?)] spec,
// the column is set to Unlimited (unlimited size) - unless size is specified explicitly.
RegisterAutoMemoTypes("nvarchar(max)", "image", "ntext", "text", "geography", "geometry", "xml");
UserDefinedVendorDbType = new VendorDbTypeInfo("UserDefined", DbType.Object, typeof(object), null, null, null, typeof(object), VendorDbTypeFlags.UserDefined, null, null, (int)SqlDbType.Structured);
}
public MsSqlTypeRegistry(MsSqlDbDriver driver) : base(driver)
{
//numerics
AddType("real", DbType.Single, typeof(Single), SqlDbType.Real);
AddType("float", DbType.Double, typeof(Double), SqlDbType.Float);
AddType("decimal", DbType.Decimal, typeof(Decimal), SqlDbType.Decimal, args: "({precision},{scale})", aliases: "numeric");
//integers
// Note: for int types that do not have direct match in database type set, we assign bigger int type in database
// For example, signed byte (CLR sbyte) does not have direct match, so we use 'smallint' to handle it in the database.
// For ulong (UInt64) CLR type there's no bigger type, so we handle it using 'bigint' which is signed 64-bit int; as a result there might be overflows
// in some cases.
AddType("int", DbType.Int32, typeof(int), SqlDbType.Int, clrTypes: new Type[] { typeof(UInt16) });
AddType("smallint", DbType.Int16, typeof(Int16), SqlDbType.SmallInt, clrTypes: new Type[] { typeof(sbyte) });
AddType("tinyint", DbType.Byte, typeof(byte), SqlDbType.TinyInt);
AddType("bigint", DbType.Int64, typeof(Int64), SqlDbType.BigInt, clrTypes: new Type[] { typeof(UInt32), typeof(UInt64) });
// Bool
AddType("bit", DbType.Boolean, typeof(bool), SqlDbType.Bit);
// Strings
AddType("nchar", DbType.StringFixedLength, typeof(string), SqlDbType.NChar, args: "({size})", isDefault: false, clrTypes: new Type[] { typeof(char) });
AddType("varchar", DbType.AnsiString, typeof(string), SqlDbType.VarChar, args: "({size})", isDefault: false, memoArgs: "(max)");
AddType("char", DbType.AnsiStringFixedLength, typeof(string), SqlDbType.Char, args: "({size})", isDefault: false);
AddType("ntext", DbType.String, typeof(string), SqlDbType.NText, supportsMemo: true, isDefault: false);
AddType("text", DbType.AnsiString, typeof(string), SqlDbType.Text, supportsMemo: true, isDefault: false);
// just to constantly verify that it works correctly - we add default db type for 'String' last. Nevertheless, it should be picked up for strings by default, so order should not matter.
AddType("nvarchar", DbType.String, typeof(string), SqlDbType.NVarChar, supportsMemo: true, args: "({size})", memoArgs: "(max)");
// Datetime
AddType("datetime2", DbType.DateTime2, typeof(DateTime), SqlDbType.DateTime2);
AddType("date", DbType.Date, typeof(DateTime), SqlDbType.Date, isDefault: false);
AddType("time", DbType.Time, typeof(TimeSpan), SqlDbType.Time);
AddType("datetime", DbType.DateTime, typeof(DateTime), SqlDbType.DateTime, isDefault: false, valueToLiteral: DateTimeToLiteralMsSql);
AddType("smalldatetime", DbType.DateTime, typeof(DateTime), SqlDbType.SmallDateTime, isDefault: false,
valueToLiteral: DbValueToLiteralConverters.DateTimeToLiteralNoMs);
// Binaries
var binTypes = new Type[] { typeof(Vita.Common.Binary) };
AddType("varbinary", DbType.Binary, typeof(byte[]), SqlDbType.VarBinary, supportsMemo: true, args: "({size})", memoArgs: "(max)", clrTypes: binTypes);
AddType("binary", DbType.Binary, typeof(byte[]), SqlDbType.Binary, isDefault: false, args: "({size})", clrTypes: binTypes);
AddType("image", DbType.Binary, typeof(byte[]), SqlDbType.Image, supportsMemo: true, isDefault: false, clrTypes: binTypes);
AddType("uniqueidentifier", DbType.Guid, typeof(Guid), SqlDbType.UniqueIdentifier);
AddType("DateTimeOffset", DbType.DateTimeOffset, typeof(DateTimeOffset), SqlDbType.DateTimeOffset);
AddType("money", DbType.Currency, typeof(Decimal), SqlDbType.Money, isDefault: false);
// MS SQL specific types.
// Note: DbType.Object is for "... general type representing any reference or value type not explicitly represented by another DbType value."
// So we use DbType.Object for such cases
AddType("smallmoney", DbType.Currency, typeof(Decimal), SqlDbType.SmallMoney, isDefault: false);
AddType("hierarchyid", DbType.Object, typeof(byte[]), SqlDbType.Binary, isDefault: false, clrTypes: binTypes);
AddType("geography", DbType.Object, typeof(byte[]), SqlDbType.Udt, isDefault: false, supportsMemo: true, clrTypes: binTypes);
AddType("geometry", DbType.Object, typeof(byte[]), SqlDbType.Udt, isDefault: false, supportsMemo: true, clrTypes: binTypes);
AddType("timestamp", DbType.Binary, typeof(byte[]), SqlDbType.Timestamp, isDefault: false, aliases: "rowversion");
AddType("xml", DbType.Object, typeof(string), SqlDbType.Xml, isDefault: false, supportsMemo: true);
AddType("sql_variant", DbType.Object, typeof(object), SqlDbType.Variant, isDefault: false, valueToLiteral: DbValueToLiteralConverters.DefaultValueToLiteral);
// the following types should be treated as auto-memo - whenevery property specifies this type explicitly through [Column(TypeName=?)] spec,
// the column is set to Unlimited (unlimited size) - unless size is specified explicitly.
RegisterAutoMemoTypes("nvarchar(max)", "image", "ntext", "text", "geography", "geometry", "xml");
UserDefinedVendorDbType = new VendorDbTypeInfo("UserDefined", DbType.Object, typeof(object), null, null, null, typeof(object), VendorDbTypeFlags.UserDefined, null, null, (int)SqlDbType.Structured);
}
public MsSqlTypeRegistry(MsSqlDbDriver driver) : base(driver)
{
var none = new Type[] { };
//numerics
AddDbTypeDef("real", typeof(Single));
AddDbTypeDef("float", typeof(Double));
var tsDec = base.AddDbTypeDef("decimal", typeof(Decimal), Data.Driver.TypeSystem.DbTypeFlags.Precision | Data.Driver.TypeSystem.DbTypeFlags.Scale, aliases: "numeric");
// we do not have default mapping for decimal; but we set it to (18,4) if not provided
//integers
// Note: for int types that do not have direct match in database type set, we assign bigger int type in database
// For example, signed byte (CLR sbyte) does not have direct match, so we use 'smallint' to handle it in the database.
// For ulong (UInt64) CLR type there's no bigger type, so we handle it using 'bigint' which is signed 64-bit int; as a result there might be overflows
// in some cases.
var tsInt = AddDbTypeDef("int", typeof(int));
MapMany(new[] { typeof(Int32), typeof(UInt16) }, tsInt);
var tsSmallInt = AddDbTypeDef("smallint", typeof(Int16));
Map(typeof(sbyte), tsSmallInt);
AddDbTypeDef("tinyint", typeof(byte));
var tsBigInt = AddDbTypeDef("bigint", typeof(Int64));
MapMany(new Type[] { typeof(UInt32), typeof(UInt64) }, tsBigInt);
// Bool
AddDbTypeDef("bit", typeof(bool), toLiteral: BoolToBitLiteral);
base.AddDbTypeDef("nvarchar", typeof(string), Data.Driver.TypeSystem.DbTypeFlags.Size, specialType: DbSpecialType.String);
base.AddDbTypeDef("nvarchar(max)", typeof(string), flags: Data.Driver.TypeSystem.DbTypeFlags.Unlimited, specialType: DbSpecialType.StringUnlimited);
base.AddDbTypeDef("varchar", typeof(string), Data.Driver.TypeSystem.DbTypeFlags.Size | Data.Driver.TypeSystem.DbTypeFlags.Ansi, specialType: DbSpecialType.StringAnsi);
base.AddDbTypeDef("varchar(max)", typeof(string), flags: Data.Driver.TypeSystem.DbTypeFlags.Unlimited | Data.Driver.TypeSystem.DbTypeFlags.Ansi,
specialType: DbSpecialType.StringAnsiUnlimited);
var stNChar = base.AddDbTypeDef("nchar", typeof(string), Data.Driver.TypeSystem.DbTypeFlags.Size);
Map(typeof(char), stNChar, size: 1);
base.AddDbTypeDef("char", typeof(string), Data.Driver.TypeSystem.DbTypeFlags.Size | Data.Driver.TypeSystem.DbTypeFlags.Ansi);
//obsolete
base.AddDbTypeDef("ntext", typeof(string), Data.Driver.TypeSystem.DbTypeFlags.Unlimited | Data.Driver.TypeSystem.DbTypeFlags.Obsolete);
base.AddDbTypeDef("text", typeof(string), Data.Driver.TypeSystem.DbTypeFlags.Unlimited | Data.Driver.TypeSystem.DbTypeFlags.Obsolete);
// Datetime
// datetime2 may have optional #of-digits parameter (scale). Setting ArgsOptional makes allows it to register as default for
var datetime2 = base.AddDbTypeDef("datetime2", typeof(DateTime), Data.Driver.TypeSystem.DbTypeFlags.Precision, defaultPrecision: 7);
base.AddDbTypeDef("datetime", typeof(DateTime), mapColumnType: false, toLiteral: DateTimeToLiteralMsSql);
base.AddDbTypeDef("date", typeof(DateTime), mapColumnType: false);
AddDbTypeDef("time", typeof(TimeSpan));
base.AddDbTypeDef("smalldatetime", typeof(DateTime), mapColumnType: false, toLiteral: DbValueToLiteralConverters.DateTimeToLiteralNoMs);
// Binaries
var tbinary = typeof(Vita.Entities.Binary);
var stVarBin = base.AddDbTypeDef("varbinary", typeof(byte[]), Data.Driver.TypeSystem.DbTypeFlags.Size, specialType: DbSpecialType.Binary);
Map(tbinary, stVarBin);
var stVarBinMax = base.AddDbTypeDef("varbinary(max)", typeof(byte[]), Data.Driver.TypeSystem.DbTypeFlags.Unlimited, specialType: DbSpecialType.BinaryUnlimited);
Map(tbinary, stVarBinMax);
var tsBin = base.AddDbTypeDef("binary", typeof(byte[]), Data.Driver.TypeSystem.DbTypeFlags.Size);
Map(tbinary, tsBin);
base.AddDbTypeDef("image", typeof(byte[]), Data.Driver.TypeSystem.DbTypeFlags.Unlimited | Data.Driver.TypeSystem.DbTypeFlags.Obsolete);
AddDbTypeDef("uniqueidentifier", typeof(Guid));
// MS SQL specific types.
AddDbTypeDef("datetimeoffset", typeof(DateTimeOffset));
base.AddDbTypeDef("money", typeof(Decimal), mapColumnType: false);
base.AddDbTypeDef("smallmoney", typeof(Decimal), mapColumnType: false);
base.AddDbTypeDef("rowversion", typeof(byte[]), aliases: "timestamp", mapColumnType: false);
base.AddDbTypeDef("xml", typeof(string), flags: Data.Driver.TypeSystem.DbTypeFlags.Unlimited);
AddDbTypeDef("sql_variant", typeof(object), toLiteral: DbValueToLiteralConverters.DefaultValueToLiteral);
// we register these exotic binary types as unlimited
base.AddDbTypeDef("hierarchyid", typeof(byte[]), flags: Data.Driver.TypeSystem.DbTypeFlags.Unlimited);
// for geography, geometry SqlDbType.Udt does not work
base.AddDbTypeDef("geography", typeof(byte[]), flags: Data.Driver.TypeSystem.DbTypeFlags.Unlimited);
base.AddDbTypeDef("geometry", typeof(byte[]), flags: Data.Driver.TypeSystem.DbTypeFlags.Unlimited);
}