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) { 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); }