当前位置：Gxlcms > 数据库问题 > SqlServer数据类型、C#SqlDbType对应关系及转换

SqlServer数据类型、C#SqlDbType对应关系及转换

时间：2021-07-01 10:21:17 帮助过：10人阅读 

  1. // SqlDbType转换为C#数据类型
  2. public static Type SqlType2CsharpType(SqlDbType sqlType)
  3. {
  4. switch (sqlType)
  5. {
  6.        case SqlDbType.BigInt:
  7.          return typeof(Int64);
  8.        case SqlDbType.Binary:
  9.          return typeof(Object);
  10.        case SqlDbType.Bit:
  11.          return typeof(Boolean);
  12.        case SqlDbType.Char:
  13.          return typeof(String);
  14.        case SqlDbType.DateTime:
  15.          return typeof(DateTime);
  16.        case SqlDbType.Decimal:
  17.          return typeof(Decimal);
  18.        case SqlDbType.Float:
  19.          return typeof(Double);
  20.        case SqlDbType.Image:
  21.          return typeof(Object);
  22.        case SqlDbType.Int:
  23.          return typeof(Int32);
  24.        case SqlDbType.Money:
  25.          return typeof(Decimal);
  26.        case SqlDbType.NChar:
  27.          return typeof(String);
  28.        case SqlDbType.NText:
  29.          return typeof(String);
  30.        case SqlDbType.NVarChar:
  31.          return typeof(String);
  32.        case SqlDbType.Real:
  33.          return typeof(Single);
  34.        case SqlDbType.SmallDateTime:
  35.          return typeof(DateTime);
  36.        case SqlDbType.SmallInt:
  37.          return typeof(Int16);
  38.        case SqlDbType.SmallMoney:
  39.          return typeof(Decimal);
  40.        case SqlDbType.Text:
  41.          return typeof(String);
  42.        case SqlDbType.Timestamp:
  43.          return typeof(Object);
  44.        case SqlDbType.TinyInt:
  45.          return typeof(Byte);
  46.        case SqlDbType.Udt://自定义的数据类型
  47.          return typeof(Object);
  48.        case SqlDbType.UniqueIdentifier:
  49.          return typeof(Object);
  50.        case SqlDbType.VarBinary:
  51.          return typeof(Object);
  52.        case SqlDbType.VarChar:
  53.          return typeof(String);
  54.        case SqlDbType.Variant:
  55.          return typeof(Object);
  56.        case SqlDbType.Xml:
  57.          return typeof(Object);
  58.        default:
  59.          return null;
  60. }
  61. }

　　

  1. // sql server数据类型（如：varchar）
  2. // 转换为SqlDbType类型
  3. public static SqlDbType SqlTypeString2SqlType(string sqlTypeString)
  4. {
  5. SqlDbType dbType = SqlDbType.Variant;//默认为Object
  6. switch (sqlTypeString)
  7. {
  8.        case "int":
  9.          dbType = SqlDbType.Int;
  10.          break;
  11.        case "varchar":
  12.          dbType = SqlDbType.VarChar;
  13.          break;
  14.        case "bit":
  15.          dbType = SqlDbType.Bit;
  16.          break;
  17.        case "datetime":
  18.          dbType = SqlDbType.DateTime;
  19.          break;
  20.        case "decimal":
  21.          dbType = SqlDbType.Decimal;
  22.          break;
  23.        case "float":
  24.          dbType = SqlDbType.Float;
  25.          break;
  26.        case "image":
  27.          dbType = SqlDbType.Image;
  28.          break;
  29.        case "money":
  30.          dbType = SqlDbType.Money;
  31.          break;
  32.        case "ntext":
  33.          dbType = SqlDbType.NText;
  34.          break;
  35.        case "nvarchar":
  36.          dbType = SqlDbType.NVarChar;
  37.          break;
  38.        case "smalldatetime":
  39.          dbType = SqlDbType.SmallDateTime;
  40.          break;
  41.        case "smallint":
  42.          dbType = SqlDbType.SmallInt;
  43.          break;
  44.        case "text":
  45.          dbType = SqlDbType.Text;
  46.          break;
  47.        case "bigint":
  48.          dbType = SqlDbType.BigInt;
  49.          break;
  50.        case "binary":
  51.          dbType = SqlDbType.Binary;
  52.          break;
  53.        case "char":
  54.          dbType = SqlDbType.Char;
  55.          break;
  56.        case "nchar":
  57.          dbType = SqlDbType.NChar;
  58.          break;
  59.        case "numeric":
  60.          dbType = SqlDbType.Decimal;
  61.          break;
  62.        case "real":
  63.          dbType = SqlDbType.Real;
  64.          break;
  65.        case "smallmoney":
  66.          dbType = SqlDbType.SmallMoney;
  67.          break;
  68.        case "sql_variant":
  69.          dbType = SqlDbType.Variant;
  70.          break;
  71.        case "timestamp":
  72.          dbType = SqlDbType.Timestamp;
  73.          break;
  74.        case "tinyint":
  75.          dbType = SqlDbType.TinyInt;
  76.          break;
  77.        case "uniqueidentifier":
  78.          dbType = SqlDbType.UniqueIdentifier;
  79.          break;
  80.        case "varbinary":
  81.          dbType = SqlDbType.VarBinary;
  82.          break;
  83.        case "xml":
  84.          dbType = SqlDbType.Xml;
  85.          break;
  86. }
  87. return dbType;
  88. }

　　

  1. // sql server中的数据类型，转换为C#中的类型类型
  2. public static Type SqlTypeString2CsharpType(string sqlTypeString)
  3. {
  4. SqlDbType dbTpe = SqlTypeString2SqlType(sqlTypeString);
  5. return SqlType2CsharpType(dbTpe);
  6. }
  7. // 将sql server中的数据类型，转化为C#中的类型的字符串
  8. public static string SqlTypeString2CsharpTypeString(string sqlTypeString)
  9. {
  10. Type type = SqlTypeString2CsharpType(sqlTypeString);
  11. return type.Name;
  12. }

　　

 

  1. 有如下类型的映射对照：
  2. System.Data.SqlClient.SqlDbType
  3. System.Data.OleDb.OleDbType
  4. System.Data.Odbc.OdbcType
  5. System.Data.OracleClient.OracleType
  6. Oracle.ManagedDataAccess.ClientOracleDbType
  7. 格式：
  8. DbType.枚举名（枚举值） = DbType.枚举名（枚举值）
  9. 回到顶部
  10. 1、与 System.Data.SqlClient.SqlDbType转换
  11. System.Data.SqlClient.SqlDbType.BigInt(0)   =  System.Data.DbType.Int64(12)
  12. System.Data.SqlClient.SqlDbType.Binary(1)   =  System.Data.DbType.Binary(1)
  13. System.Data.SqlClient.SqlDbType.Bit(2)   =  System.Data.DbType.Boolean(3)
  14. System.Data.SqlClient.SqlDbType.Char(3)   =  System.Data.DbType.AnsiStringFixedLength(22)
  15. System.Data.SqlClient.SqlDbType.DateTime(4)   =  System.Data.DbType.DateTime(6)
  16. System.Data.SqlClient.SqlDbType.Decimal(5)   =  System.Data.DbType.Decimal(7)
  17. System.Data.SqlClient.SqlDbType.Float(6)   =  System.Data.DbType.Double(8)
  18. System.Data.SqlClient.SqlDbType.Image(7)   =  System.Data.DbType.Binary(1)
  19. System.Data.SqlClient.SqlDbType.Int(8)   =  System.Data.DbType.Int32(11)
  20. System.Data.SqlClient.SqlDbType.Money(9)   =  System.Data.DbType.Currency(4)
  21. System.Data.SqlClient.SqlDbType.NChar(10)   =  System.Data.DbType.StringFixedLength(23)
  22. System.Data.SqlClient.SqlDbType.NText(11)   =  System.Data.DbType.String(16)
  23. System.Data.SqlClient.SqlDbType.NVarChar(12)   =  System.Data.DbType.String(16)
  24. System.Data.SqlClient.SqlDbType.Real(13)   =  System.Data.DbType.Single(15)
  25. System.Data.SqlClient.SqlDbType.UniqueIdentifier(14)   =  System.Data.DbType.Guid(9)
  26. System.Data.SqlClient.SqlDbType.SmallDateTime(15)   =  System.Data.DbType.DateTime(6)
  27. System.Data.SqlClient.SqlDbType.SmallInt(16)   =  System.Data.DbType.Int16(10)
  28. System.Data.SqlClient.SqlDbType.SmallMoney(17)   =  System.Data.DbType.Currency(4)
  29. System.Data.SqlClient.SqlDbType.Text(18)   =  System.Data.DbType.AnsiString(0)
  30. System.Data.SqlClient.SqlDbType.Timestamp(19)   =  System.Data.DbType.Binary(1)
  31. System.Data.SqlClient.SqlDbType.TinyInt(20)   =  System.Data.DbType.Byte(2)
  32. System.Data.SqlClient.SqlDbType.VarBinary(21)   =  System.Data.DbType.Binary(1)
  33. System.Data.SqlClient.SqlDbType.VarChar(22)   =  System.Data.DbType.AnsiString(0)
  34. System.Data.SqlClient.SqlDbType.Variant(23)   =  System.Data.DbType.Object(13)
  35. System.Data.SqlClient.SqlDbType.Xml(25)   =  System.Data.DbType.Xml(25)
  36. System.Data.SqlClient.SqlDbType.Udt(29)   =  System.Data.DbType.Object(13)
  37. System.Data.SqlClient.SqlDbType.Structured(30)   =  System.Data.DbType.Object(13)
  38. System.Data.SqlClient.SqlDbType.Date(31)   =  System.Data.DbType.Date(5)
  39. System.Data.SqlClient.SqlDbType.Time(32)   =  System.Data.DbType.Time(17)
  40. System.Data.SqlClient.SqlDbType.DateTime2(33)   =  System.Data.DbType.DateTime2(26)
  41. System.Data.SqlClient.SqlDbType.DateTimeOffset(34)   =  System.Data.DbType.DateTimeOffset(27)
  42. 回到顶部
  43. 2、与 System.Data.OleDb.OleDbType转换
  44. System.Data.OleDb.OleDbType.Empty(0)   =  System.Data.DbType.Object(13)
  45. System.Data.OleDb.OleDbType.SmallInt(2)   =  System.Data.DbType.Int16(10)
  46. System.Data.OleDb.OleDbType.Integer(3)   =  System.Data.DbType.Int32(11)
  47. System.Data.OleDb.OleDbType.Single(4)   =  System.Data.DbType.Single(15)
  48. System.Data.OleDb.OleDbType.Double(5)   =  System.Data.DbType.Double(8)
  49. System.Data.OleDb.OleDbType.Currency(6)   =  System.Data.DbType.Currency(4)
  50. System.Data.OleDb.OleDbType.Date(7)   =  System.Data.DbType.DateTime(6)
  51. System.Data.OleDb.OleDbType.BSTR(8)   =  System.Data.DbType.String(16)
  52. System.Data.OleDb.OleDbType.IDispatch(9)   =  System.Data.DbType.Object(13)
  53. System.Data.OleDb.OleDbType.Error(10)   =  System.Data.DbType.Int32(11)
  54. System.Data.OleDb.OleDbType.Boolean(11)   =  System.Data.DbType.Boolean(3)
  55. System.Data.OleDb.OleDbType.Variant(12)   =  System.Data.DbType.Object(13)
  56. System.Data.OleDb.OleDbType.IUnknown(13)   =  System.Data.DbType.Object(13)
  57. System.Data.OleDb.OleDbType.Decimal(14)   =  System.Data.DbType.Decimal(7)
  58. System.Data.OleDb.OleDbType.TinyInt(16)   =  System.Data.DbType.SByte(14)
  59. System.Data.OleDb.OleDbType.UnsignedTinyInt(17)   =  System.Data.DbType.Byte(2)
  60. System.Data.OleDb.OleDbType.UnsignedSmallInt(18)   =  System.Data.DbType.UInt16(18)
  61. System.Data.OleDb.OleDbType.UnsignedInt(19)   =  System.Data.DbType.UInt32(19)
  62. System.Data.OleDb.OleDbType.BigInt(20)   =  System.Data.DbType.Int64(12)
  63. System.Data.OleDb.OleDbType.UnsignedBigInt(21)   =  System.Data.DbType.UInt64(20)
  64. System.Data.OleDb.OleDbType.Filetime(64)   =  System.Data.DbType.DateTime(6)
  65. System.Data.OleDb.OleDbType.Guid(72)   =  System.Data.DbType.Guid(9)
  66. System.Data.OleDb.OleDbType.Binary(128)   =  System.Data.DbType.Binary(1)
  67. System.Data.OleDb.OleDbType.Char(129)   =  System.Data.DbType.AnsiStringFixedLength(22)
  68. System.Data.OleDb.OleDbType.WChar(130)   =  System.Data.DbType.StringFixedLength(23)
  69. System.Data.OleDb.OleDbType.Numeric(131)   =  System.Data.DbType.Decimal(7)
  70. System.Data.OleDb.OleDbType.DBDate(133)   =  System.Data.DbType.Date(5)
  71. System.Data.OleDb.OleDbType.DBTime(134)   =  System.Data.DbType.Time(17)
  72. System.Data.OleDb.OleDbType.DBTimeStamp(135)   =  System.Data.DbType.DateTime(6)
  73. System.Data.OleDb.OleDbType.PropVariant(138)   =  System.Data.DbType.Object(13)
  74. System.Data.OleDb.OleDbType.VarNumeric(139)   =  System.Data.DbType.VarNumeric(21)
  75. System.Data.OleDb.OleDbType.VarChar(200)   =  System.Data.DbType.AnsiString(0)
  76. System.Data.OleDb.OleDbType.LongVarChar(201)   =  System.Data.DbType.AnsiString(0)
  77. System.Data.OleDb.OleDbType.VarWChar(202)   =  System.Data.DbType.String(16)
  78. System.Data.OleDb.OleDbType.LongVarWChar(203)   =  System.Data.DbType.String(16)
  79. System.Data.OleDb.OleDbType.VarBinary(204)   =  System.Data.DbType.Binary(1)
  80. System.Data.OleDb.OleDbType.LongVarBinary(205)   =  System.Data.DbType.Binary(1)
  81. 回到顶部
  82. 3、与System.Data.Odbc.OdbcType转换
  83. System.Data.Odbc.OdbcType.BigInt(1)   =  System.Data.DbType.Int64(12)
  84. System.Data.Odbc.OdbcType.Binary(2)   =  System.Data.DbType.Binary(1)
  85. System.Data.Odbc.OdbcType.Bit(3)   =  System.Data.DbType.Boolean(3)
  86. System.Data.Odbc.OdbcType.Char(4)   =  System.Data.DbType.AnsiStringFixedLength(22)
  87. System.Data.Odbc.OdbcType.DateTime(5)   =  System.Data.DbType.DateTime(6)
  88. System.Data.Odbc.OdbcType.Decimal(6)   =  System.Data.DbType.Decimal(7)
  89. System.Data.Odbc.OdbcType.Numeric(7)   =  System.Data.DbType.Decimal(7)
  90. System.Data.Odbc.OdbcType.Double(8)   =  System.Data.DbType.Double(8)
  91. System.Data.Odbc.OdbcType.Image(9)   =  System.Data.DbType.Binary(1)
  92. System.Data.Odbc.OdbcType.Int(10)   =  System.Data.DbType.Int32(11)
  93. System.Data.Odbc.OdbcType.NChar(11)   =  System.Data.DbType.StringFixedLength(23)
  94. System.Data.Odbc.OdbcType.NText(12)   =  System.Data.DbType.String(16)
  95. System.Data.Odbc.OdbcType.NVarChar(13)   =  System.Data.DbType.String(16)
  96. System.Data.Odbc.OdbcType.Real(14)   =  System.Data.DbType.Single(15)
  97. System.Data.Odbc.OdbcType.UniqueIdentifier(15)   =  System.Data.DbType.Guid(9)
  98. System.Data.Odbc.OdbcType.SmallDateTime(16)   =  System.Data.DbType.DateTime(6)
  99. System.Data.Odbc.OdbcType.SmallInt(17)   =  System.Data.DbType.Int16(10)
  100. System.Data.Odbc.OdbcType.Text(18)   =  System.Data.DbType.AnsiString(0)
  101. System.Data.Odbc.OdbcType.Timestamp(19)   =  System.Data.DbType.Binary(1)
  102. System.Data.Odbc.OdbcType.TinyInt(20)   =  System.Data.DbType.Byte(2)
  103. System.Data.Odbc.OdbcType.VarBinary(21)   =  System.Data.DbType.Binary(1)
  104. System.Data.Odbc.OdbcType.VarChar(22)   =  System.Data.DbType.AnsiString(0)
  105. System.Data.Odbc.OdbcType.Date(23)   =  System.Data.DbType.Date(5)
  106. System.Data.Odbc.OdbcType.Time(24)   =  System.Data.DbType.Time(17)
  107. 回到顶部
  108. 4、与System.Data.OracleClient.OracleType转换
  109. System.Data.OracleClient.OracleType.BFile(1)   =  System.Data.DbType.Binary(1)
  110. System.Data.OracleClient.OracleType.Blob(2)   =  System.Data.DbType.Binary(1)
  111. System.Data.OracleClient.OracleType.Char(3)   =  System.Data.DbType.AnsiStringFixedLength(22)
  112. System.Data.OracleClient.OracleType.Clob(4)   =  System.Data.DbType.AnsiString(0)
  113. System.Data.OracleClient.OracleType.Cursor(5)   =  System.Data.DbType.Object(13)
  114. System.Data.OracleClient.OracleType.DateTime(6)   =  System.Data.DbType.DateTime(6)
  115. System.Data.OracleClient.OracleType.IntervalDayToSecond(7)   =  System.Data.DbType.Object(13)
  116. System.Data.OracleClient.OracleType.IntervalYearToMonth(8)   =  System.Data.DbType.Int32(11)
  117. System.Data.OracleClient.OracleType.LongRaw(9)   =  System.Data.DbType.Binary(1)
  118. System.Data.OracleClient.OracleType.LongVarChar(10)   =  System.Data.DbType.AnsiString(0)
  119. System.Data.OracleClient.OracleType.NChar(11)   =  System.Data.DbType.StringFixedLength(23)
  120. System.Data.OracleClient.OracleType.NClob(12)   =  System.Data.DbType.String(16)
  121. System.Data.OracleClient.OracleType.Number(13)   =  System.Data.DbType.VarNumeric(21)
  122. System.Data.OracleClient.OracleType.NVarChar(14)   =  System.Data.DbType.String(16)
  123. System.Data.OracleClient.OracleType.Raw(15)   =  System.Data.DbType.Binary(1)
  124. System.Data.OracleClient.OracleType.RowId(16)   =  System.Data.DbType.AnsiString(0)
  125. System.Data.OracleClient.OracleType.Timestamp(18)   =  System.Data.DbType.DateTime(6)
  126. System.Data.OracleClient.OracleType.TimestampLocal(19)   =  System.Data.DbType.DateTime(6)
  127. System.Data.OracleClient.OracleType.TimestampWithTZ(20)   =  System.Data.DbType.DateTime(6)
  128. System.Data.OracleClient.OracleType.VarChar(22)   =  System.Data.DbType.AnsiString(0)
  129. System.Data.OracleClient.OracleType.Byte(23)   =  System.Data.DbType.Byte(2)
  130. System.Data.OracleClient.OracleType.UInt16(24)   =  System.Data.DbType.UInt16(18)
  131. System.Data.OracleClient.OracleType.UInt32(25)   =  System.Data.DbType.UInt32(19)
  132. System.Data.OracleClient.OracleType.SByte(26)   =  System.Data.DbType.SByte(14)
  133. System.Data.OracleClient.OracleType.Int16(27)   =  System.Data.DbType.Int16(10)
  134. System.Data.OracleClient.OracleType.Int32(28)   =  System.Data.DbType.Int32(11)
  135. System.Data.OracleClient.OracleType.Float(29)   =  System.Data.DbType.Single(15)
  136. System.Data.OracleClient.OracleType.Double(30)   =  System.Data.DbType.Double(8)
  137. 回到顶部
  138. 5、与Oracle.ManagedDataAccess.ClientOracleDbType转换
  139. Oracle.ManagedDataAccess.ClientOracleDbType.BFile =  System.Data.DbType.Binary(1)
  140. Oracle.ManagedDataAccess.ClientOracleDbType.Blob = 102,
  141. Oracle.ManagedDataAccess.ClientOracleDbType.Byte = 103,
  142. Oracle.ManagedDataAccess.ClientOracleDbType.Char = 104,
  143. Oracle.ManagedDataAccess.ClientOracleDbType.Clob = 105,
  144. Oracle.ManagedDataAccess.ClientOracleDbType.Date = 106,
  145. Oracle.ManagedDataAccess.ClientOracleDbType.Decimal = 107,
  146. Oracle.ManagedDataAccess.ClientOracleDbType.Double = 108,
  147. Oracle.ManagedDataAccess.ClientOracleDbType.Long = 109,
  148. Oracle.ManagedDataAccess.ClientOracleDbType.LongRaw = 110,
  149. Oracle.ManagedDataAccess.ClientOracleDbType.Int16 = 111,
  150. Oracle.ManagedDataAccess.ClientOracleDbType.Int32 = 112,
  151. Oracle.ManagedDataAccess.ClientOracleDbType.Int64 = 113,
  152. Oracle.ManagedDataAccess.ClientOracleDbType.IntervalDS = 114,
  153. Oracle.ManagedDataAccess.ClientOracleDbType.IntervalYM = 115,
  154. Oracle.ManagedDataAccess.ClientOracleDbType.NClob = 116,
  155. Oracle.ManagedDataAccess.ClientOracleDbType.NChar = 117,
  156. Oracle.ManagedDataAccess.ClientOracleDbType.NVarchar2 = 119,
  157. Oracle.ManagedDataAccess.ClientOracleDbType.Raw = 120,
  158. Oracle.ManagedDataAccess.ClientOracleDbType.RefCursor = 121,
  159. Oracle.ManagedDataAccess.ClientOracleDbType.Single = 122,
  160. Oracle.ManagedDataAccess.ClientOracleDbType.TimeStamp = 123,
  161. Oracle.ManagedDataAccess.ClientOracleDbType.TimeStampLTZ = 124,
  162. Oracle.ManagedDataAccess.ClientOracleDbType.TimeStampTZ = 125,
  163. Oracle.ManagedDataAccess.ClientOracleDbType.Varchar2 = 126,
  164. Oracle.ManagedDataAccess.ClientOracleDbType.XmlType = 127,
  165. Oracle.ManagedDataAccess.ClientOracleDbType.BinaryDouble = 132,     
  166. Oracle.ManagedDataAccess.ClientOracleDbType.BinaryFloat = 133,
  167. Oracle.ManagedDataAccess.ClientOracleDbType.Boolean = 134,

　　

SqlServer数据类型、C#SqlDbType对应关系及转换

标签：lld   nvarchar   text   patch   lse   字符串   blob   man   定义   

人气教程排行

本站所有资源全部来源于网络，若本站发布的内容侵害到您的隐私或者利益，请联系我们删除！