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