获取sqlserver字段类型和字段最大长度
-- nvarchar 等 长度需要除以2
SELECT ColumnType=t.name,Length=c.max_length
FROM sys.columns c
left join systypes t on c.system_type_id=t.xtype
WHERE t.name<>'sysname'
and OBJECT_NAME(c.object_id)='TableName'
and c.name='ColumnName'
sqlserver数据类型与C#类型对照
static DbType? GetDbType(string SqlType)
{
if (string.IsNullOrWhiteSpace(SqlType)) return null;
switch (SqlType.ToLower())
{
case "bigint":
return DbType.Int64;
case "binary":
return DbType.Binary;
case "bit":
return DbType.Boolean;
case "char":
return DbType.AnsiStringFixedLength;
case "datetime":
return DbType.DateTime;
case "decimal":
return DbType.Decimal;
case "float":
return DbType.Double;
case "image":
return DbType.Binary;
case "int":
return DbType.Int32;
case "money":
return DbType.Currency;
case "nchar":
return DbType.StringFixedLength;
case "ntext":
return DbType.String;
case "nvarchar":
return DbType.String;
case "real":
return DbType.Single;
case "uniqueidentifier":
return DbType.Guid;
case "smalldatetime":
return DbType.DateTime;
case "smallint":
return DbType.Int16;
case "smallmoney":
return DbType.Currency;
case "text":
return DbType.AnsiString;
case "timestamp":
return DbType.Binary;
case "tinyint":
return DbType.Byte;
case "varbinary":
return DbType.Binary;
case "varchar":
return DbType.AnsiString;
case "variant":
return DbType.Object;
case "xml":
return DbType.Xml;
case "udt":
return DbType.Object;
case "structured":
return DbType.Object;
case "date":
return DbType.Date;
case "time":
return DbType.Time;
case "datetime2":
return DbType.DateTime2;
case "datetimeoffset":
return DbType.DateTimeOffset;
}
return null;
}