public string Create(string name, SqlDbType sqlDbType, long? length = null) { var lStr = length.HasValue ? string.Format("({0})", length.Value) : string.Empty; return string.Format("\"{0}\" {1}{2}", name.Trim(), sqlDbType.ToString().ToLower(), lStr); }
public Column(string name, SqlDbType dataType, int length = 0, bool nullable = true, bool identity = false, string computed = null) { Name = name; DataType = dataType.ToString(); Length = length; IsNullable = nullable; Identity = identity; ComputedDefinition = computed; }
/// <summary> /// returns the native sql type of string value /// </summary> /// <param name="t">SqlDbType</param> /// <returns>returns the native sql type of string value</returns> internal static string GetSqlType(SqlDbType t) { switch (t) { case SqlDbType.Variant: return "sql_variant"; default: return t.ToString(); } }
internal MetadataField(Guid moduleId, string name, string description, SqlDbType sqlType, uint sqlTypeLength) { _moduleId = moduleId; _name = name; _description = description; _sqlType = sqlType; if (!MetadataField.ValidateFieldType(_sqlType)) throw new ArgumentException("SQL тип '" + _sqlType.ToString() + "' не поддерживается"); _sqlTypeLength = sqlTypeLength; }
public string Create(string name, SqlDbType sqlDbType, byte leftLength, byte rightLength) { var lStr = string.Format("({0}, {1})", leftLength, rightLength); return string.Format("\"{0}\" {1}{2}", name.Trim(), sqlDbType.ToString().ToLower(), lStr); }
public static string CreateColumn(string name, SqlDbType type, string udtTypeName, int? size, int? scale, bool nullable, bool identity, string @default) { Connector.Current.FixType(ref type, ref size, ref scale); return "{0} {1}{2} {3}{4}{5}".FormatWith( name.SqlEscape(), type == SqlDbType.Udt ? udtTypeName : type.ToString().ToUpper(), GetSizeScale(size, scale), identity ? "IDENTITY " : "", nullable ? "NULL" : "NOT NULL", @default != null ? " DEFAULT " + Quote(type, @default) : ""); }
/// <summary> /// Конвертирует значение параметра из строки /// </summary> public Object ValueFromString(String value, SqlDbType type, ValueDbStyle style) { try { if (style == ValueDbStyle.Xml) switch (type) { case SqlDbType.BigInt: return new SqlInt64(XmlConvert.ToInt64(value)); case SqlDbType.Binary: return new SqlBytes(Convert.FromBase64String(value)); case SqlDbType.Bit: return new SqlBoolean(XmlConvert.ToBoolean(value)); case SqlDbType.Char: return new SqlString(value); case SqlDbType.Date: return XmlConvert.ToDateTime(value, XmlDateTimeSerializationMode.RoundtripKind).Date; case SqlDbType.DateTime: return new SqlDateTime(XmlConvert.ToDateTime(value, XmlDateTimeSerializationMode.RoundtripKind)); case SqlDbType.DateTime2: return XmlConvert.ToDateTime(value, XmlDateTimeSerializationMode.RoundtripKind); case SqlDbType.Time: TimeSpan resultTime; return TimeSpan.TryParse(value, out resultTime) ? resultTime : XmlConvert.ToTimeSpan(value); case SqlDbType.DateTimeOffset: return XmlConvert.ToDateTimeOffset(value); // Not support SqlDbType.Timestamp case SqlDbType.Decimal: return new SqlDecimal(XmlConvert.ToDecimal(value)); case SqlDbType.Float: return new SqlDouble(XmlConvert.ToDouble(value)); // Not support SqlDbType.Image case SqlDbType.Int: return new SqlInt32(XmlConvert.ToInt32(value)); case SqlDbType.Money: return new SqlMoney(XmlConvert.ToDecimal(value)); case SqlDbType.NChar: return new SqlString(value); // Not support SqlDbType.NText case SqlDbType.NVarChar: return new SqlString(value); case SqlDbType.Real: return new SqlSingle(XmlConvert.ToDouble(value)); case SqlDbType.SmallDateTime: return new SqlDateTime(XmlConvert.ToDateTime(value, XmlDateTimeSerializationMode.RoundtripKind)); case SqlDbType.SmallInt: return new SqlInt16(XmlConvert.ToInt16(value)); case SqlDbType.SmallMoney: return new SqlMoney(XmlConvert.ToDecimal(value)); // Not support SqlDbType.Structured // Not support SqlDbType.Text case SqlDbType.TinyInt: return new SqlByte(XmlConvert.ToByte(value)); case SqlDbType.UniqueIdentifier: return new SqlGuid(XmlConvert.ToGuid(value)); case SqlDbType.VarBinary: return new SqlBytes(Convert.FromBase64String(value)); case SqlDbType.VarChar: return new SqlString(value); // Not support SqlDbType.Variant case SqlDbType.Xml: { XmlReader r = XmlReader.Create(new System.IO.StringReader(value)); return new SqlXml(r); } //case SqlDbType.Udt: // { // TParams result = (TParams)System.Activator.CreateInstance(this.GetType()); // XmlReader r = XmlReader.Create(new System.IO.StringReader(value)); // result.ReadXml(r); // return result; // } } else if (style == ValueDbStyle.Text) { switch (type) { case SqlDbType.BigInt: return (SqlInt64)Int64.Parse(value, CultureInfo); case SqlDbType.Binary: return new SqlBytes(Convert.FromBase64String(value)); case SqlDbType.Bit: return SqlBoolean.Parse(value); case SqlDbType.Char: return new SqlString(value); case SqlDbType.Date: return DateTime.ParseExact(value, DateTimeFormats, CultureInfo, DateTimeStyles.RoundtripKind).Date; case SqlDbType.DateTime: return (SqlDateTime)DateTime.ParseExact(value, DateTimeFormats, CultureInfo, DateTimeStyles.RoundtripKind); case SqlDbType.DateTime2: return DateTime.ParseExact(value, DateTimeFormats, CultureInfo, DateTimeStyles.RoundtripKind); case SqlDbType.Decimal: return (SqlDecimal)Decimal.Parse(value, CultureInfo); case SqlDbType.Float: return (SqlDouble)Double.Parse(value, CultureInfo); // Not support SqlDbType.Image case SqlDbType.Int: return (SqlInt32)Int32.Parse(value, CultureInfo); case SqlDbType.Money: return (SqlMoney)Decimal.Parse(value, CultureInfo); case SqlDbType.NChar: return new SqlString(value); // Not support SqlDbType.NText case SqlDbType.NVarChar: return new SqlString(value); case SqlDbType.Real: return (SqlSingle)Double.Parse(value, CultureInfo); case SqlDbType.SmallDateTime: return (SqlDateTime)DateTime.ParseExact(value, DateTimeFormats, CultureInfo, DateTimeStyles.RoundtripKind); case SqlDbType.SmallInt: return (SqlInt16)Int16.Parse(value, cultureInfo); case SqlDbType.SmallMoney: return (SqlMoney)Decimal.Parse(value, CultureInfo); // Not support SqlDbType.Structured // Not support SqlDbType.Text case SqlDbType.Time: return TimeSpan.Parse(value); // Not support SqlDbType.Timestamp case SqlDbType.TinyInt: return (SqlByte)Byte.Parse(value, CultureInfo); case SqlDbType.UniqueIdentifier: return SqlGuid.Parse(value); case SqlDbType.VarBinary: return new SqlBytes(Convert.FromBase64String(value)); case SqlDbType.VarChar: return new SqlString(value); // Not support SqlDbType.Variant case SqlDbType.Xml: XmlReader r = XmlReader.Create(new System.IO.StringReader(value)); return new SqlXml(r); //case SqlDbType.Udt: // TParams result = (TParams)System.Activator.CreateInstance(this.GetType()); // result.FromString(value); // return result; } } throw new Exception("Системная ошибка"); // Сюда никогда не должно попасть } catch { throw new Exception(String.Format("Не удалось сконвертировать значение '{0}' в тип {1}", value, type.ToString())); } }
public static string CreateField(string name, SqlDbType type, string udtTypeName, int? size, int? scale, bool nullable, bool primaryKey, bool identity) { Schema.Current.Settings.FixType(ref type, ref size, ref scale); return "{0} {1}{2} {3}{4}{5}".Formato( name.SqlEscape(), type == SqlDbType.Udt ? udtTypeName : type.ToString().ToUpper(), GetSizeScale(size, scale), identity ? "IDENTITY " : "", nullable ? "NULL" : "NOT NULL", primaryKey ? " PRIMARY KEY" : ""); }
/// <summary> /// Не поддерживаемый тип /// </summary> /// <param name="type">Тип</param> public UnsuportedDbTypeException(SqlDbType type) : base(string.Format("Тип столбца [{0}] на данный момент не поддерживается\nЕсли он Вам нужен - сообщите об этом: https://github.com/deltasem/dbullet/issues", type.ToString())) { }
public static string GetDbType(SqlDbType dbType, bool isNullable) { string s = dbType.ToString().ToLowerInvariant(); if (s == "varchar") s = "nvarchar"; return string.Format("{0}{1}", s, isNullable ? "" : " NOT NULL"); }
/// <summary> /// The add profile column. /// </summary> /// <param name="name"> /// The name. /// </param> /// <param name="columnType"> /// The column type. /// </param> /// <param name="size"> /// The size. /// </param> public static void AddProfileColumn([NotNull] string name, SqlDbType columnType, int size) { // get column type... string type = columnType.ToString(); if (size > 0) { type += "(" + size + ")"; } string sql = "ALTER TABLE {0} ADD [{1}] {2} NULL".FormatWith( DbHelpers.GetObjectName("UserProfile"), name, type); using (var cmd = DbHelpers.GetCommand(sql, true)) { cmd.CommandType = CommandType.Text; DbAccess.ExecuteNonQuery(cmd); } }
/// <summary> /// Helper to generate a tsql fragement that will produce a random value of the given type to insert into the test database. /// </summary> /// <param name="dataTypeInfo">The datatype of the desired value.</param> /// <returns>The tsql fragment that will generate a random value of the desired type.</returns> private static string GetTestFieldValue(MutliShardTestCaseColumn dataTypeInfo) { SqlDbType dbType = dataTypeInfo.DbType; int? length = dataTypeInfo.FieldLength; switch (dbType) { case SqlDbType.BigInt: // SQL Types: bigint return(GetRandomIntCastAsArg(dataTypeInfo)); case SqlDbType.Binary: // SQL Types: image return(GetRandomSqlBinaryValue(length.Value)); case SqlDbType.Bit: // SQL Types: bit return(GetRandomSqlBitValue()); case SqlDbType.Char: // SQL Types: char[(n)] return(GetRandomSqlCharValue(length.Value)); case SqlDbType.Date: // SQL Types: date return(GetRandomSqlDateValue()); case SqlDbType.DateTime: // SQL Types: datetime, smalldatetime return(GetRandomSqlDatetimeCastAsArg(dataTypeInfo)); case SqlDbType.DateTime2: // SQL Types: datetime2 return(GetRandomSqlDatetimeCastAsArg(dataTypeInfo)); case SqlDbType.DateTimeOffset: // SQL Types: datetimeoffset return(GetRandomSqlDatetimeoffsetValue()); case SqlDbType.Decimal: // SQL Types: decimal, numeric // These are the same. return(GetRandomSqlDecimalValue(dataTypeInfo)); case SqlDbType.Float: // SQL Types: float return(GetRandomSqlFloatValue(dataTypeInfo)); case SqlDbType.Image: // SQL Types: image return(GetRandomSqlBinaryValue(dataTypeInfo.FieldLength)); case SqlDbType.Int: // SQL Types: int return(GetRandomSqlIntValue()); case SqlDbType.Money: // SQL Types: money return(GetRandomSqlMoneyValue(dataTypeInfo)); case SqlDbType.NChar: // SQL Types: nchar[(n)] return(GetRandomSqlNCharValue(length.Value)); case SqlDbType.NText: // SQL Types: ntext return(GetRandomSqlNCharValue(length.Value)); case SqlDbType.NVarChar: // SQL Types: nvarchar[(n)] return(GetRandomSqlNCharValue(length.Value)); case SqlDbType.Real: // SQL Types: real return(GetRandomSqlRealValue(dataTypeInfo)); case SqlDbType.SmallDateTime: // SQL Types: smalldatetime return(GetRandomSqlDatetimeCastAsArg(dataTypeInfo)); case SqlDbType.SmallInt: // SQL Types: smallint return(GetRandomSqlSmallIntValue(dataTypeInfo)); case SqlDbType.SmallMoney: // SQL Types: smallmoney return(GetRandomSqlSmallMoneyValue(dataTypeInfo)); case SqlDbType.Text: // SQL Types: text return(GetRandomSqlCharValue(length.Value)); case SqlDbType.Time: // SQL Types: time return(GetRandomSqlDatetimeCastAsArg(dataTypeInfo)); case SqlDbType.Timestamp: // SQL Types: rowversion, timestamp //exclding it should happen automatically. should not be here. throw. throw new ArgumentException(SqlDbType.Timestamp.ToString()); case SqlDbType.TinyInt: // SQL Types: tinyint return(GetRandomSqlTinyIntValue()); case SqlDbType.UniqueIdentifier: // SQL Types: uniqueidentifier return(GetRandomSqlUniqueIdentifierValue()); case SqlDbType.VarBinary: // SQL Types: binary[(n)], varbinary[(n)] return(GetRandomSqlBinaryValue(length.Value)); case SqlDbType.VarChar: // SQL Types: varchar[(n)] return(GetRandomSqlCharValue(length.Value)); default: throw new ArgumentException(dbType.ToString()); } }
/// <summary> /// The add profile column. /// </summary> /// <param name="name"> /// The name. /// </param> /// <param name="columnType"> /// The column type. /// </param> /// <param name="size"> /// The size. /// </param> public void AddProfileColumn([NotNull] string name, SqlDbType columnType, int size) { // get column type... string type = columnType.ToString(); if (size > 0) { type += "(" + size + ")"; } string sql = "ALTER TABLE {0} ADD [{1}] {2} NULL".FormatWith( MsSqlDbAccess.GetObjectName("prov_Profile"), name, type); using (var cmd = new SqlCommand(sql)) { cmd.CommandType = CommandType.Text; this._msSqlDbAccess.ExecuteNonQuery(cmd); } }
internal static Exception InvalidSqlDbTypeForConstructor(SqlDbType type) { return ADP.Argument(Res.GetString("SqlMetaData_InvalidSqlDbTypeForConstructorFormat", new object[] { type.ToString() })); }
/** * Given a SqlDbType, this will return the equivalent CLR Type. */ public static Type GetClrType(SqlDbType sqlType) { switch (sqlType) { case SqlDbType.BigInt: return typeof(Int64); case SqlDbType.Int: return typeof(Int32); case SqlDbType.SmallInt: return typeof(Int16); case SqlDbType.Decimal: case SqlDbType.Money: case SqlDbType.SmallMoney: return typeof(Decimal); case SqlDbType.Float: return typeof(Double); case SqlDbType.Real: return typeof(Single); case SqlDbType.TinyInt: return typeof(Byte); case SqlDbType.Binary: case SqlDbType.Image: case SqlDbType.Timestamp: case SqlDbType.VarBinary: return typeof(Byte[]); case SqlDbType.Bit: return typeof(Boolean); case SqlDbType.Char: case SqlDbType.NChar: case SqlDbType.NText: case SqlDbType.NVarChar: case SqlDbType.Text: case SqlDbType.VarChar: case SqlDbType.Xml: return typeof(String); case SqlDbType.DateTime: case SqlDbType.SmallDateTime: case SqlDbType.Date: case SqlDbType.DateTime2: return typeof(DateTime); case SqlDbType.DateTimeOffset: return typeof(DateTimeOffset); case SqlDbType.Time: return typeof(TimeSpan); case SqlDbType.UniqueIdentifier: return typeof(Guid); case SqlDbType.Variant: case SqlDbType.Udt: return typeof(Object); case SqlDbType.Structured: return typeof(DataTable); default: throw new ArgumentOutOfRangeException ( "sqlType => " + sqlType.ToString() ); } }
/// <summary> /// SQLs the type of the db type to db. /// </summary> /// <param name="sqlDbType">Type of the SQL db.</param> /// <returns>Db Type.</returns> public static DbType SqlDbTypeToDbType(SqlDbType sqlDbType) { switch (sqlDbType) { case SqlDbType.BigInt: return DbType.Int64; case SqlDbType.Binary: return DbType.Binary; case SqlDbType.Bit: return DbType.Boolean; case SqlDbType.Char: return DbType.AnsiStringFixedLength; case SqlDbType.DateTime: return DbType.DateTime; case SqlDbType.Decimal: return DbType.Decimal; case SqlDbType.Float: return DbType.Double; case SqlDbType.Image: return DbType.Binary; case SqlDbType.Int: return DbType.Int32; case SqlDbType.Money: return DbType.Currency; case SqlDbType.NChar: return DbType.StringFixedLength; case SqlDbType.NText: return DbType.String; case SqlDbType.NVarChar: return DbType.String; case SqlDbType.Real: return DbType.Single; case SqlDbType.UniqueIdentifier: return DbType.Guid; case SqlDbType.SmallDateTime: return DbType.DateTime; case SqlDbType.SmallInt: return DbType.Int16; case SqlDbType.SmallMoney: return DbType.Currency; case SqlDbType.Text: return DbType.AnsiString; case SqlDbType.Timestamp: return DbType.Binary; case SqlDbType.TinyInt: return DbType.Byte; case SqlDbType.VarBinary: return DbType.Binary; case SqlDbType.VarChar: return DbType.AnsiString; case SqlDbType.Variant: return DbType.Object; default: throw new NotSupportedException(sqlDbType.ToString()); } }
private Type SqlDbTypeToType(SqlDbType dbType) { switch (dbType) { case SqlDbType.BigInt: return typeof(Int64); case SqlDbType.Bit: return typeof(bool); case SqlDbType.Char: return typeof(char); case SqlDbType.Date: return typeof(DateTime); case SqlDbType.DateTime: return typeof(DateTime); case SqlDbType.DateTime2: return typeof(DateTime); case SqlDbType.Decimal: return typeof(Decimal); case SqlDbType.Float: return typeof(float); case SqlDbType.Int: return typeof(int); case SqlDbType.NChar: return typeof(char); case SqlDbType.NText: return typeof(String); case SqlDbType.NVarChar: return typeof(String); case SqlDbType.Real: return typeof(decimal); case SqlDbType.SmallDateTime: return typeof(DateTime); case SqlDbType.SmallInt: return typeof(Int16); case SqlDbType.Text: return typeof(String); case SqlDbType.TinyInt: return typeof(Int16); case SqlDbType.UniqueIdentifier: return typeof(Guid); case SqlDbType.VarChar: return typeof(String); case SqlDbType.Xml: return typeof(String); default: throw new System.Exception("Unsupported type:" + dbType.ToString()); } }
/// <summary> /// Converts the value. /// </summary> /// <param name="type"></param> /// <returns></returns> public static Type ToNetType(SqlDbType type) { var entry = Find(type); if (entry == null) { throw new InvalidCastException(string.Format("SqlDbType.{0} cannot be converted to a .Net type.", type.ToString())); } return(entry.Type); }
public static string ToCsvColumnsList(this ReceiveCommandColumns self, SqlDbType messageBodyType = SqlDbType.NVarChar) { var builder = new StringBuilder(); AppendColumn(builder, "priority", (self | ReceiveCommandColumns.Priority) == ReceiveCommandColumns.Priority); AppendColumn(builder, "queuing_order", (self | ReceiveCommandColumns.QueuingOrder) == ReceiveCommandColumns.QueuingOrder); AppendColumn(builder, "conversation_group_id ", (self | ReceiveCommandColumns.ConversationGroupId ) == ReceiveCommandColumns.ConversationGroupId ); AppendColumn(builder, "conversation_handle", (self | ReceiveCommandColumns.ConversationHandle) == ReceiveCommandColumns.ConversationHandle); AppendColumn(builder, "message_sequence_number", (self | ReceiveCommandColumns.MessageSequenceNumber) == ReceiveCommandColumns.MessageSequenceNumber); AppendColumn(builder, "service_name", (self | ReceiveCommandColumns.ServiceName) == ReceiveCommandColumns.ServiceName); AppendColumn(builder, "service_id", (self | ReceiveCommandColumns.ServiceId) == ReceiveCommandColumns.ServiceId); AppendColumn(builder, "service_contract_name", (self | ReceiveCommandColumns.ServiceContractName) == ReceiveCommandColumns.ServiceContractName); AppendColumn(builder, "service_contract_id", (self | ReceiveCommandColumns.ServiceContractId) == ReceiveCommandColumns.ServiceContractId); AppendColumn(builder, "message_type_name", (self | ReceiveCommandColumns.MessageTypeName) == ReceiveCommandColumns.MessageTypeName); AppendColumn(builder, "message_type_id", (self | ReceiveCommandColumns.MessageTypeId) == ReceiveCommandColumns.MessageTypeId); AppendColumn(builder, "validation", (self | ReceiveCommandColumns.Validation) == ReceiveCommandColumns.Validation); if((self | ReceiveCommandColumns.MessageBody) == ReceiveCommandColumns.MessageBody) { if(builder.Length > 0) builder.Append(", "); switch (messageBodyType) { case SqlDbType.VarBinary: builder.Append("[message_body]"); break; case SqlDbType.VarChar: case SqlDbType.NVarChar: builder.Append("CAST([message_body] AS NVARCHAR(MAX))"); break; default: throw new NotSupportedException(messageBodyType.ToString()); } } return builder.ToString(); }
/// <summary>Gets the primitive CLR type.</summary> private static Type GetClrPrimitiveType(SqlDbType t) { switch (t) { case SqlDbType.BigInt: return typeof(Int64); case SqlDbType.Binary: return typeof(Byte[]); case SqlDbType.Bit: return typeof(Boolean); case SqlDbType.Char: return typeof(String); case SqlDbType.DateTime: return typeof(DateTime); case SqlDbType.Decimal: return typeof(Decimal); case SqlDbType.Float: return typeof(Double); case SqlDbType.Image: return typeof(Byte[]); case SqlDbType.Int: return typeof(Int32); case SqlDbType.Money: return typeof(Decimal); case SqlDbType.NChar: return typeof(String); case SqlDbType.NText: return typeof(String); case SqlDbType.NVarChar: return typeof(String); case SqlDbType.Real: return typeof(Single); case SqlDbType.SmallDateTime: return typeof(DateTime); case SqlDbType.SmallInt: return typeof(Int16); case SqlDbType.SmallMoney: return typeof(Decimal); case SqlDbType.Text: return typeof(String); case SqlDbType.Timestamp: return typeof(Byte[]); case SqlDbType.TinyInt: return typeof(Byte); case SqlDbType.Udt: return typeof(Object); case SqlDbType.UniqueIdentifier: return typeof(Guid); case SqlDbType.VarBinary: return typeof(Byte[]); case SqlDbType.VarChar: return typeof(String); case SqlDbType.Variant: return typeof(Object); case SqlDbType.Xml: return typeof(String); default: throw new NotImplementedException(String.Format( "Cannot get the CLR type of SqlDbType {0}", t.ToString())); } }
/// <summary> /// Retrieve the Sql data type in a string format. /// </summary> /// <param name="dotNetDataType">Sql data type.</param> /// <returns>Sql data type as a string.</returns> public string GetSqlDataTypeString(SqlDbType sqlDataType) { #region Constants const string SQL_DATA_TYPE_CHAR_LENGTH = "(1)"; const string SQL_DATA_TYPE_VARCHAR_LENGTH = "(255)"; const string SQL_DATA_TYPE_DECIMAL_PREC_SCALE = "(30,10)"; #endregion Constants string sqlDataTypeString = sqlDataType.ToString(); if (sqlDataType == SqlDbType.Char) { // VarChar Sql data types require a length parameter sqlDataTypeString += SQL_DATA_TYPE_CHAR_LENGTH; } else if (sqlDataType == SqlDbType.VarChar) { // VarChar Sql data types require a length parameter sqlDataTypeString += SQL_DATA_TYPE_VARCHAR_LENGTH; } else if (sqlDataType == SqlDbType.Decimal) { // Decimal Sql data types require a precision and scale sqlDataTypeString += SQL_DATA_TYPE_DECIMAL_PREC_SCALE; } return sqlDataTypeString; }
public static DbType SqlDbType2DbType(SqlDbType sqlDbType_in) { switch (sqlDbType_in) { case SqlDbType.BigInt: { return DbType.Int64; } case SqlDbType.Bit: { return DbType.Boolean; } case SqlDbType.Char: case SqlDbType.NChar: case SqlDbType.NText: case SqlDbType.NVarChar: case SqlDbType.Text: case SqlDbType.VarChar: { return DbType.String; } case SqlDbType.DateTime: case SqlDbType.SmallDateTime: { return DbType.DateTime; } case SqlDbType.Decimal: case SqlDbType.Money: case SqlDbType.SmallMoney: { return DbType.Decimal; } case SqlDbType.Float: { return DbType.Double; } case SqlDbType.Int: { return DbType.Int32; } case SqlDbType.Real: { return DbType.Single; } case SqlDbType.UniqueIdentifier: { return DbType.Guid; } case SqlDbType.SmallInt: { return DbType.Int16; } case SqlDbType.TinyInt: { return DbType.Byte; } case SqlDbType.Variant: { return DbType.Object; } case SqlDbType.Binary: { return DbType.Binary; } case SqlDbType.Image: case SqlDbType.Timestamp: case SqlDbType.VarBinary: #region default: throw new Exception("..."); default: { throw new Exception(string.Format( "undefined variable type: {0}", sqlDbType_in.ToString() )); } #endregion } }
/// <summary> /// Конвертирует значение параметра из строки /// </summary> public static Object ValueFromString(String value, SqlDbType type, ValueDbStyle style) { try { // if (style == ValueDbStyle.XML) switch (type) { case SqlDbType.Bit : return new SqlBoolean(XmlConvert.ToBoolean(value)); case SqlDbType.TinyInt : return new SqlByte(XmlConvert.ToByte(value)); case SqlDbType.SmallInt : return new SqlInt16(XmlConvert.ToInt16(value)); case SqlDbType.Int : return new SqlInt32(XmlConvert.ToInt32(value)); case SqlDbType.BigInt : return new SqlInt64(XmlConvert.ToInt64(value)); case SqlDbType.Char : case SqlDbType.VarChar : //return new SqlAnsiString(value); case SqlDbType.NChar : case SqlDbType.NVarChar : return new SqlString(value); case SqlDbType.Binary : case SqlDbType.VarBinary: return new SqlBytes(Convert.FromBase64String(value)); case SqlDbType.Date : return (style == ValueDbStyle.XML)? XmlConvert.ToDateTime(value, XmlDateTimeSerializationMode.RoundtripKind).Date : (style == ValueDbStyle.SQL)? DateTime.ParseExact(value, SQLDateFormats, CultureInfo.InvariantCulture, DateTimeStyles.RoundtripKind).Date : DateTime.ParseExact(value, DateTimeFormats, CultureInfo.InvariantCulture, DateTimeStyles.RoundtripKind).Date; case SqlDbType.SmallDateTime : case SqlDbType.DateTime : case SqlDbType.DateTime2 : DateTime LDateTime = ( (style == ValueDbStyle.XML)? XmlConvert.ToDateTime(value, XmlDateTimeSerializationMode.RoundtripKind) : (style == ValueDbStyle.SQL)? DateTime.ParseExact(value, SQLDateTimeFormats, CultureInfo.InvariantCulture, DateTimeStyles.RoundtripKind) : DateTime.ParseExact(value, DateTimeFormats, CultureInfo.InvariantCulture, DateTimeStyles.RoundtripKind) ); if(type == SqlDbType.DateTime2) return LDateTime; else return new SqlDateTime(LDateTime); case SqlDbType.DateTimeOffset: return (style == ValueDbStyle.XML) ? XmlConvert.ToDateTimeOffset(value) : (style == ValueDbStyle.SQL) ? DateTimeOffset.ParseExact(value, SQLDateTimeOffsetPattern, CultureInfo.InvariantCulture, DateTimeStyles.RoundtripKind | DateTimeStyles.AssumeUniversal) : DateTimeOffset.ParseExact(value, DateTimeFormats, CultureInfo.InvariantCulture, DateTimeStyles.RoundtripKind | DateTimeStyles.AssumeUniversal); case SqlDbType.Time : TimeSpan resultTime; return TimeSpan.TryParse(value, out resultTime) ? resultTime : XmlConvert.ToTimeSpan(value); case SqlDbType.Decimal : return new SqlDecimal(XmlConvert.ToDecimal(value)); case SqlDbType.Float : return new SqlDouble(XmlConvert.ToDouble(value)); case SqlDbType.Real : return new SqlSingle(XmlConvert.ToDouble(value)); case SqlDbType.SmallMoney : return new SqlMoney(XmlConvert.ToDecimal(value)); case SqlDbType.Money : return new SqlMoney(XmlConvert.ToDecimal(value)); case SqlDbType.UniqueIdentifier: return new SqlGuid(XmlConvert.ToGuid(value)); case SqlDbType.Xml: { XmlReader r = XmlReader.Create(new System.IO.StringReader(value)); return new SqlXml(r); } //case SqlDbType.Udt: // { // TParams result = (TParams)System.Activator.CreateInstance(this.GetType()); // XmlReader r = XmlReader.Create(new System.IO.StringReader(value)); // result.ReadXml(r); // return result; // } // Not support SqlDbType.Variant // Not support SqlDbType.Structured // Not support SqlDbType.Text // Not support SqlDbType.Timestamp // Not support SqlDbType.Image // Not support SqlDbType.NText } throw new Exception("Системная ошибка"); // Сюда никогда не должно попасть } catch { throw new Exception(String.Format("Не удалось сконвертировать значение '{0}' в тип {1}", value, type.ToString())); } }
/// <summary> /// The method gets the SQL CE type name for use in SQL Statements such as CREATE TABLE /// </summary> /// <param name="dbType">The SqlDbType to get the type name for</param> /// <param name="size">The size where applicable e.g. to create a nchar(n) type where n is the size passed in.</param> /// <returns>The SQL CE compatible type for use in SQL Statements</returns> public static string GetSqlServerCETypeName(SqlDbType dbType, int size) { // Conversions according to: http://msdn.microsoft.com/en-us/library/ms173018.aspx bool max = (size == int.MaxValue) ? true : false; bool over4k = (size > 4000) ? true : false; switch (dbType) { case SqlDbType.BigInt: return "bigint"; case SqlDbType.Binary: return String.Format("binary({0})",size); case SqlDbType.Bit: return "bit"; case SqlDbType.Char: return (over4k) ? "ntext" : String.Format("nchar({0})",size); case SqlDbType.Date: return "nchar(10)"; case SqlDbType.DateTime: return "datetime"; case SqlDbType.DateTime2: return "nvarchar(27)"; case SqlDbType.DateTimeOffset: return "nvarchar(34)"; case SqlDbType.Decimal: break; case SqlDbType.Float: return "float"; case SqlDbType.Image: return "image"; case SqlDbType.Int: return "int"; case SqlDbType.Money: return "money"; case SqlDbType.NChar: return String.Format("nchar({0})",size); case SqlDbType.NText: return "ntext"; case SqlDbType.NVarChar: return String.Format("nvarchar({0})",size); case SqlDbType.Real: return "real"; case SqlDbType.SmallDateTime: return "datetime"; case SqlDbType.SmallInt: return "smallint"; case SqlDbType.SmallMoney: return "money"; case SqlDbType.Structured: break; case SqlDbType.Text: return "ntext"; case SqlDbType.Time: return "nvarchar(16)"; case SqlDbType.Timestamp: break; case SqlDbType.TinyInt: return "tinyint"; case SqlDbType.Udt: break; case SqlDbType.UniqueIdentifier: break; case SqlDbType.VarBinary: break; case SqlDbType.VarChar: break; case SqlDbType.Variant: break; case SqlDbType.Xml: return "ntext"; default: break; } throw new ArgumentException (String.Format("Could Not Convert SQL DataType {0} to SQLCE DataType",dbType.ToString())); }
/// <summary> /// Converts a <see cref="SqlDbType"/> to a <see cref="Type"/>. /// </summary> /// <param name="sqlDbType">The type</param> /// <returns>The <see cref="Type"/></returns> protected static Type SqlDbTypeToClrType(SqlDbType sqlDbType) { switch (sqlDbType) { case SqlDbType.BigInt: return(typeof(long)); case SqlDbType.Binary: break; case SqlDbType.Bit: return(typeof(bool)); case SqlDbType.Char: break; case SqlDbType.Date: case SqlDbType.DateTime: case SqlDbType.DateTime2: case SqlDbType.SmallDateTime: case SqlDbType.Time: return(typeof(DateTime)); case SqlDbType.DateTimeOffset: return(typeof(DateTimeOffset)); case SqlDbType.Decimal: return(typeof(decimal)); case SqlDbType.Float: break; case SqlDbType.Image: break; case SqlDbType.Int: return(typeof(int)); case SqlDbType.Money: break; case SqlDbType.NChar: case SqlDbType.NText: case SqlDbType.VarChar: case SqlDbType.NVarChar: case SqlDbType.Text: return(typeof(string)); case SqlDbType.Real: return(typeof(double)); case SqlDbType.SmallInt: return(typeof(short)); case SqlDbType.SmallMoney: break; case SqlDbType.Structured: return(typeof(object)); case SqlDbType.Timestamp: return(typeof(byte[])); case SqlDbType.TinyInt: return(typeof(byte)); case SqlDbType.Udt: break; case SqlDbType.UniqueIdentifier: return(typeof(Guid)); case SqlDbType.VarBinary: return(typeof(Stream)); case SqlDbType.Variant: break; case SqlDbType.Xml: break; } throw new NotSupportedException(sqlDbType.ToString()); }