For the following SQL Server datatypes, what would be the corresponding datatype in C#?
Exact Numerics
bigint
numeric
bit
smallint
decimal
smallmoney
int
tinyint
money
Approximate Numerics
float
real
Date and Time
date
datetimeoffset
datetime2
smalldatetime
datetime
time
Character Strings
char
varchar
text
Unicode Character Strings
nchar
nvarchar
ntext
Binary Strings
binary
varbinary
image
Other Data Types
cursor
timestamp
hierarchyid
uniqueidentifier
sql_variant
xml
table
(source: MSDN)
This question is related to
c#
.net
sql-server
public static string FromSqlType(string sqlTypeString)
{
if (! Enum.TryParse(sqlTypeString, out Enums.SQLType typeCode))
{
throw new Exception("sql type not found");
}
switch (typeCode)
{
case Enums.SQLType.varbinary:
case Enums.SQLType.binary:
case Enums.SQLType.filestream:
case Enums.SQLType.image:
case Enums.SQLType.rowversion:
case Enums.SQLType.timestamp://?
return "byte[]";
case Enums.SQLType.tinyint:
return "byte";
case Enums.SQLType.varchar:
case Enums.SQLType.nvarchar:
case Enums.SQLType.nchar:
case Enums.SQLType.text:
case Enums.SQLType.ntext:
case Enums.SQLType.xml:
return "string";
case Enums.SQLType.@char:
return "char";
case Enums.SQLType.bigint:
return "long";
case Enums.SQLType.bit:
return "bool";
case Enums.SQLType.smalldatetime:
case Enums.SQLType.datetime:
case Enums.SQLType.date:
case Enums.SQLType.datetime2:
return "DateTime";
case Enums.SQLType.datetimeoffset:
return "DateTimeOffset";
case Enums.SQLType.@decimal:
case Enums.SQLType.money:
case Enums.SQLType.numeric:
case Enums.SQLType.smallmoney:
return "decimal";
case Enums.SQLType.@float:
return "double";
case Enums.SQLType.@int:
return "int";
case Enums.SQLType.real:
return "Single";
case Enums.SQLType.smallint:
return "short";
case Enums.SQLType.uniqueidentifier:
return "Guid";
case Enums.SQLType.sql_variant:
return "object";
case Enums.SQLType.time:
return "TimeSpan";
default:
throw new Exception("none equal type");
}
}
public enum SQLType
{
varbinary,//(1)
binary,//(1)
image,
varchar,
@char,
nvarchar,//(1)
nchar,//(1)
text,
ntext,
uniqueidentifier,
rowversion,
bit,
tinyint,
smallint,
@int,
bigint,
smallmoney,
money,
numeric,
@decimal,
real,
@float,
smalldatetime,
datetime,
sql_variant,
table,
cursor,
timestamp,
xml,
date,
datetime2,
datetimeoffset,
filestream,
time,
}
SQL Server and the .NET Framework are based on different type systems. For example, the .NET Framework Decimal structure has a maximum scale of 28, whereas the SQL Server decimal and numeric data types have a maximum scale of 38. Click Here's a link! for detail
https://msdn.microsoft.com/en-us/library/cc716729(v=vs.110).aspx
public static string FromSqlType(string sqlTypeString)
{
if (! Enum.TryParse(sqlTypeString, out Enums.SQLType typeCode))
{
throw new Exception("sql type not found");
}
switch (typeCode)
{
case Enums.SQLType.varbinary:
case Enums.SQLType.binary:
case Enums.SQLType.filestream:
case Enums.SQLType.image:
case Enums.SQLType.rowversion:
case Enums.SQLType.timestamp://?
return "byte[]";
case Enums.SQLType.tinyint:
return "byte";
case Enums.SQLType.varchar:
case Enums.SQLType.nvarchar:
case Enums.SQLType.nchar:
case Enums.SQLType.text:
case Enums.SQLType.ntext:
case Enums.SQLType.xml:
return "string";
case Enums.SQLType.@char:
return "char";
case Enums.SQLType.bigint:
return "long";
case Enums.SQLType.bit:
return "bool";
case Enums.SQLType.smalldatetime:
case Enums.SQLType.datetime:
case Enums.SQLType.date:
case Enums.SQLType.datetime2:
return "DateTime";
case Enums.SQLType.datetimeoffset:
return "DateTimeOffset";
case Enums.SQLType.@decimal:
case Enums.SQLType.money:
case Enums.SQLType.numeric:
case Enums.SQLType.smallmoney:
return "decimal";
case Enums.SQLType.@float:
return "double";
case Enums.SQLType.@int:
return "int";
case Enums.SQLType.real:
return "Single";
case Enums.SQLType.smallint:
return "short";
case Enums.SQLType.uniqueidentifier:
return "Guid";
case Enums.SQLType.sql_variant:
return "object";
case Enums.SQLType.time:
return "TimeSpan";
default:
throw new Exception("none equal type");
}
}
public enum SQLType
{
varbinary,//(1)
binary,//(1)
image,
varchar,
@char,
nvarchar,//(1)
nchar,//(1)
text,
ntext,
uniqueidentifier,
rowversion,
bit,
tinyint,
smallint,
@int,
bigint,
smallmoney,
money,
numeric,
@decimal,
real,
@float,
smalldatetime,
datetime,
sql_variant,
table,
cursor,
timestamp,
xml,
date,
datetime2,
datetimeoffset,
filestream,
time,
}
SQL Server and the .NET Framework are based on different type systems. For example, the .NET Framework Decimal structure has a maximum scale of 28, whereas the SQL Server decimal and numeric data types have a maximum scale of 38. Click Here's a link! for detail
https://msdn.microsoft.com/en-us/library/cc716729(v=vs.110).aspx
In case anybody is looking for methods to convert from/to C# and SQL Server formats, here goes a simple implementation:
private readonly string[] SqlServerTypes = { "bigint", "binary", "bit", "char", "date", "datetime", "datetime2", "datetimeoffset", "decimal", "filestream", "float", "geography", "geometry", "hierarchyid", "image", "int", "money", "nchar", "ntext", "numeric", "nvarchar", "real", "rowversion", "smalldatetime", "smallint", "smallmoney", "sql_variant", "text", "time", "timestamp", "tinyint", "uniqueidentifier", "varbinary", "varchar", "xml" };
private readonly string[] CSharpTypes = { "long", "byte[]", "bool", "char", "DateTime", "DateTime", "DateTime", "DateTimeOffset", "decimal", "byte[]", "double", "Microsoft.SqlServer.Types.SqlGeography", "Microsoft.SqlServer.Types.SqlGeometry", "Microsoft.SqlServer.Types.SqlHierarchyId", "byte[]", "int", "decimal", "string", "string", "decimal", "string", "Single", "byte[]", "DateTime", "short", "decimal", "object", "string", "TimeSpan", "byte[]", "byte", "Guid", "byte[]", "string", "string" };
public string ConvertSqlServerFormatToCSharp(string typeName)
{
var index = Array.IndexOf(SqlServerTypes, typeName);
return index > -1
? CSharpTypes[index]
: "object";
}
public string ConvertCSharpFormatToSqlServer(string typeName)
{
var index = Array.IndexOf(CSharpTypes, typeName);
return index > -1
? SqlServerTypes[index]
: null;
}
Edit: fixed typo
Source: Stackoverflow.com