Beispiel #1
0
        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;
            }
        }
Beispiel #3
0
        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);
        }
Beispiel #4
0
        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);
        }
Beispiel #5
0
        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);
        }