C# SqlDbType.ToString方法代码示例

 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 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" : "");
        }

//.........这里部分代码省略.........
        {
          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 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>
        /// 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);
            }
        }

 internal static Exception InvalidSqlDbTypeForConstructor(SqlDbType type)
 {
     return ADP.Argument(Res.GetString("SqlMetaData_InvalidSqlDbTypeForConstructorFormat", new object[] { type.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 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) : "");
        }

        /**
         * 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>
        /// 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);
              }
        }