Je me suis assis pour écrire un massif instruction switch() pour activer les types de données SQL dans CLR types de données afin de générer des classes de MSSQL des procédures stockées. Je suis l'aide de ce tableau comme référence. Avant d'aller trop loin dans ce qui sera probablement prendre tous les jours, et une immense douleur de tester, je voudrais faire appel à la communauté pour voir si quelqu'un d'autre a déjà écrit ou trouvé quelque chose en C# pour accomplir ce qui semble être commun et assurément tâche fastidieuse.
Réponses
Trop de publicités?
C'est celui que nous utilisons. Vous voudrez peut-être le modifier (par exemple, types nullable / non nullable, etc.), mais cela devrait vous éviter la plupart des saisies.
public static Type GetClrType(SqlDbType sqlType)
{
switch (sqlType)
{
case SqlDbType.BigInt:
return typeof(long?);
case SqlDbType.Binary:
case SqlDbType.Image:
case SqlDbType.Timestamp:
case SqlDbType.VarBinary:
return typeof(byte[]);
case SqlDbType.Bit:
return typeof(bool?);
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.Time:
case SqlDbType.DateTime2:
return typeof(DateTime?);
case SqlDbType.Decimal:
case SqlDbType.Money:
case SqlDbType.SmallMoney:
return typeof(decimal?);
case SqlDbType.Float:
return typeof(double?);
case SqlDbType.Int:
return typeof(int?);
case SqlDbType.Real:
return typeof(float?);
case SqlDbType.UniqueIdentifier:
return typeof(Guid?);
case SqlDbType.SmallInt:
return typeof(short?);
case SqlDbType.TinyInt:
return typeof(byte?);
case SqlDbType.Variant:
case SqlDbType.Udt:
return typeof(object);
case SqlDbType.Structured:
return typeof(DataTable);
case SqlDbType.DateTimeOffset:
return typeof(DateTimeOffset?);
default:
throw new ArgumentOutOfRangeException("sqlType");
}
}
YordanGeorgiev
Points
1222
/****** Object: Table [dbo].[DbVsCSharpTypes] Script Date: 03/20/2010 03:07:56 ******/
IF EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[DbVsCSharpTypes]')
AND type in (N'U'))
DROP TABLE [dbo].[DbVsCSharpTypes]
GO
/****** Object: Table [dbo].[DbVsCSharpTypes] Script Date: 03/20/2010 03:07:56 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE TABLE [dbo].[DbVsCSharpTypes](
[DbVsCSharpTypesId] [int] IDENTITY(1,1) NOT NULL,
[Sql2008DataType] [varchar](200) NULL,
[CSharpDataType] [varchar](200) NULL,
[CLRDataType] [varchar](200) NULL,
[CLRDataTypeSqlServer] [varchar](2000) NULL,
CONSTRAINT [PK_DbVsCSharpTypes] PRIMARY KEY CLUSTERED
(
[DbVsCSharpTypesId] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY]
GO
SET NOCOUNT ON;
SET XACT_ABORT ON;
GO
SET IDENTITY_INSERT [dbo].[DbVsCSharpTypes] ON;
BEGIN TRANSACTION;
INSERT INTO [dbo].[DbVsCSharpTypes]([DbVsCSharpTypesId], [Sql2008DataType], [CSharpDataType], [CLRDataType], [CLRDataTypeSqlServer])
SELECT 1, N'bigint', N'short', N'Int64, Nullable<Int64>', N'SqlInt64' UNION ALL
SELECT 2, N'binary', N'byte[]', N'Byte[]', N'SqlBytes, SqlBinary' UNION ALL
SELECT 3, N'bit', N'bool', N'Boolean, Nullable<Boolean>', N'SqlBoolean' UNION ALL
SELECT 4, N'char', N'char', NULL, NULL UNION ALL
SELECT 5, N'cursor', NULL, NULL, NULL UNION ALL
SELECT 6, N'date', N'DateTime', N'DateTime, Nullable<DateTime>', N'SqlDateTime' UNION ALL
SELECT 7, N'datetime', N'DateTime', N'DateTime, Nullable<DateTime>', N'SqlDateTime' UNION ALL
SELECT 8, N'datetime2', N'DateTime', N'DateTime, Nullable<DateTime>', N'SqlDateTime' UNION ALL
SELECT 9, N'DATETIMEOFFSET', N'DateTimeOffset', N'DateTimeOffset', N'DateTimeOffset, Nullable<DateTimeOffset>' UNION ALL
SELECT 10, N'decimal', N'decimal', N'Decimal, Nullable<Decimal>', N'SqlDecimal' UNION ALL
SELECT 11, N'float', N'double', N'Double, Nullable<Double>', N'SqlDouble' UNION ALL
SELECT 12, N'geography', NULL, NULL, N'SqlGeography is defined in Microsoft.SqlServer.Types.dll, which is installed with SQL Server and can be downloaded from the SQL Server 2008 feature pack.' UNION ALL
SELECT 13, N'geometry', NULL, NULL, N'SqlGeometry is defined in Microsoft.SqlServer.Types.dll, which is installed with SQL Server and can be downloaded from the SQL Server 2008 feature pack.' UNION ALL
SELECT 14, N'hierarchyid', NULL, NULL, N'SqlHierarchyId is defined in Microsoft.SqlServer.Types.dll, which is installed with SQL Server and can be downloaded from the SQL Server 2008 feature pack.' UNION ALL
SELECT 15, N'image', NULL, NULL, NULL UNION ALL
SELECT 16, N'int', N'int', N'Int32, Nullable<Int32>', N'SqlInt32' UNION ALL
SELECT 17, N'money', N'decimal', N'Decimal, Nullable<Decimal>', N'SqlMoney' UNION ALL
SELECT 18, N'nchar', N'string', N'String, Char[]', N'SqlChars, SqlString' UNION ALL
SELECT 19, N'ntext', NULL, NULL, NULL UNION ALL
SELECT 20, N'numeric', N'decimal', N'Decimal, Nullable<Decimal>', N'SqlDecimal' UNION ALL
SELECT 21, N'nvarchar', N'string', N'String, Char[]', N'SqlChars, SqlStrinG SQLChars is a better match for data transfer and access, and SQLString is a better match for performing String operations.' UNION ALL
SELECT 22, N'nvarchar(1), nchar(1)', N'string', N'Char, String, Char[], Nullable<char>', N'SqlChars, SqlString' UNION ALL
SELECT 23, N'real', N'single', N'Single, Nullable<Single>', N'SqlSingle' UNION ALL
SELECT 24, N'rowversion', N'byte[]', N'Byte[]', NULL UNION ALL
SELECT 25, N'smallint', N'smallint', N'Int16, Nullable<Int16>', N'SqlInt16' UNION ALL
SELECT 26, N'smallmoney', N'decimal', N'Decimal, Nullable<Decimal>', N'SqlMoney' UNION ALL
SELECT 27, N'sql_variant', N'object', N'Object', NULL UNION ALL
SELECT 28, N'table', NULL, NULL, NULL UNION ALL
SELECT 29, N'text', N'string', NULL, NULL UNION ALL
SELECT 30, N'time', N'TimeSpan', N'TimeSpan, Nullable<TimeSpan>', N'TimeSpan' UNION ALL
SELECT 31, N'timestamp', NULL, NULL, NULL UNION ALL
SELECT 32, N'tinyint', N'byte', N'Byte, Nullable<Byte>', N'SqlByte' UNION ALL
SELECT 33, N'uniqueidentifier', N'Guid', N'Guid, Nullable<Guid>', N'SqlGuidUser-defined type(UDT)The same class that is bound to the user-defined type in the same assembly or a dependent assembly.' UNION ALL
SELECT 34, N'varbinary ', N'byte[]', N'Byte[]', N'SqlBytes, SqlBinary' UNION ALL
SELECT 35, N'varbinary(1), binary(1)', N'byte', N'byte, Byte[], Nullable<byte>', N'SqlBytes, SqlBinary' UNION ALL
SELECT 36, N'varchar', NULL, NULL, NULL UNION ALL
SELECT 37, N'xml', NULL, NULL, N'SqlXml'
COMMIT;
RAISERROR (N'[dbo].[DbVsCSharpTypes]: Insert Batch: 1.....Done!', 10, 1) WITH NOWAIT;
GO
SET IDENTITY_INSERT [dbo].[DbVsCSharpTypes] OFF;
Tarik H.
Points
11
internal Type type(SqlDbType sqltype)
{
Type resulttype = null;
Dictionary<SqlDbType, Type> Types = new Dictionary<SqlDbType, Type>();
Types.Add(SqlDbType.BigInt, typeof(Int64));
Types.Add(SqlDbType.Binary, typeof(Byte[]));
Types.Add(SqlDbType.Bit, typeof(Boolean));
Types.Add(SqlDbType.Char, typeof(String));
Types.Add(SqlDbType.Date, typeof(DateTime));
Types.Add(SqlDbType.DateTime, typeof(DateTime));
Types.Add(SqlDbType.DateTime2, typeof(DateTime));
Types.Add(SqlDbType.DateTimeOffset, typeof(DateTimeOffset));
Types.Add(SqlDbType.Decimal, typeof(Decimal));
Types.Add(SqlDbType.Float, typeof(Double));
Types.Add(SqlDbType.Image, typeof(Byte[]));
Types.Add(SqlDbType.Int, typeof(Int32));
Types.Add(SqlDbType.Money, typeof(Decimal));
Types.Add(SqlDbType.NChar, typeof(String));
Types.Add(SqlDbType.NText, typeof(String));
Types.Add(SqlDbType.NVarChar, typeof(String));
Types.Add(SqlDbType.Real, typeof(Single));
Types.Add(SqlDbType.SmallDateTime, typeof(DateTime));
Types.Add(SqlDbType.SmallInt, typeof(Int16));
Types.Add(SqlDbType.SmallMoney, typeof(Decimal));
Types.Add(SqlDbType.Text, typeof(String));
Types.Add(SqlDbType.Time, typeof(TimeSpan));
Types.Add(SqlDbType.Timestamp, typeof(Byte[]));
Types.Add(SqlDbType.TinyInt, typeof(Byte));
Types.Add(SqlDbType.UniqueIdentifier, typeof(Guid));
Types.Add(SqlDbType.VarBinary, typeof(Byte[]));
Types.Add(SqlDbType.VarChar, typeof(String));
Types.TryGetValue(sqltype, out resulttype);
return resulttype;
}
internal SqlDbType type(Type systype)
{
SqlDbType resulttype = SqlDbType.NVarChar;
Dictionary<Type, SqlDbType> Types = new Dictionary<Type, SqlDbType>();
Types.Add(typeof(Boolean), SqlDbType.Bit);
Types.Add(typeof(String), SqlDbType.NVarChar);
Types.Add(typeof(DateTime), SqlDbType.DateTime);
Types.Add(typeof(Int16), SqlDbType.Int);
Types.Add(typeof(Int32), SqlDbType.Int);
Types.Add(typeof(Int64), SqlDbType.Int);
Types.Add(typeof(Decimal), SqlDbType.Float);
Types.Add(typeof(Double), SqlDbType.Float);
Types.TryGetValue(systype, out resulttype);
return resulttype;
}
Doug McClean
Points
6355
Cela ne répond pas directement à la question telle qu'elle est posée, mais répond à une question commune. Une fois que vous avez un IDataReader vous pouvez appeler IDataRecord.GetFieldType(int) pour "[obtenir] les informations Type correspondant au type de Object qui serait retourné de GetValue . "
jared.g
Points
31
J'inclus cette extension (vous pouvez facilement échanger la clé de chaîne dans le dictionnaire contre SqlDbType tel que Greg l'a implémenté - ou même prendre en charge les deux) dans mon modèle et exposer une propriété qui convertit le type CLR:
namespace X.Domain.Model
{
using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
public class StoredProcedureParameter : DomainObject
{
public StoredProcedureParameter() { }
public string StoredProcedure { get; set; }
public string ProcedureSchema { get; set; }
public string ProcedureName { get; set; }
public string ParameterName { get; set; }
public string ParameterOrder { get; set; }
public string ParameterMode { get; set; }
public string SqlDataType { get; set; }
public Type DataType { get { return this.SqlDataType.ToClrType(); } }
}
static class StoredProcedureParameterExtensions
{
private static Dictionary<string, Type> Mappings;
public static StoredProcedureParameterExtensions()
{
Mappings = new Dictionary<string, Type>();
Mappings.Add("bigint", typeof(Int64));
Mappings.Add("binary", typeof(Byte[]));
Mappings.Add("bit", typeof(Boolean));
Mappings.Add("char", typeof(String));
Mappings.Add("date", typeof(DateTime));
Mappings.Add("datetime", typeof(DateTime));
Mappings.Add("datetime2", typeof(DateTime));
Mappings.Add("datetimeoffset", typeof(DateTimeOffset));
Mappings.Add("decimal", typeof(Decimal));
Mappings.Add("float", typeof(Double));
Mappings.Add("image", typeof(Byte[]));
Mappings.Add("int", typeof(Int32));
Mappings.Add("money", typeof(Decimal));
Mappings.Add("nchar", typeof(String));
Mappings.Add("ntext", typeof(String));
Mappings.Add("numeric", typeof(Decimal));
Mappings.Add("nvarchar", typeof(String));
Mappings.Add("real", typeof(Single));
Mappings.Add("rowversion", typeof(Byte[]));
Mappings.Add("smalldatetime", typeof(DateTime));
Mappings.Add("smallint", typeof(Int16));
Mappings.Add("smallmoney", typeof(Decimal));
Mappings.Add("text", typeof(String));
Mappings.Add("time", typeof(TimeSpan));
Mappings.Add("timestamp", typeof(Byte[]));
Mappings.Add("tinyint", typeof(Byte));
Mappings.Add("uniqueidentifier", typeof(Guid));
Mappings.Add("varbinary", typeof(Byte[]));
Mappings.Add("varchar", typeof(String));
}
public static Type ToClrType(this string sqlType)
{
Type datatype = null;
if (Mappings.TryGetValue(sqlType, out datatype))
return datatype;
throw new TypeLoadException(string.Format("Can not load CLR Type from {0}", sqlType));
}
}
}
