C# types de données types de données SQL Server

Essayez Ceci:
Ses une Extension de la classe, de sorte que le fichier que vous voulez utiliser ces méthodes sur ajouter:

using Utility;

Voici le code:

using System;
using System.Data;
using Microsoft.SqlServer.Server;
namespace Utility
{
public static class TypeExtension
{
public static SqlDbType ToSqlDbType(this Type clrType)
{
var s = new SqlMetaData("", SqlDbType.NVarChar, clrType);
return s.SqlDbType;
}
public static Type ToClrType(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");
}
}
}
}

OriginalL'auteur GregoryBrad

Je ne sais pas comment faire les conversions comme ça, mais il y a probablement une meilleure façon:

Je suppose que vous voulez insérer une valeur à l'aide de C# dans une requête SQL.

Pourquoi vous n'utilisez pas les paramètres?

Au lieu de:

SqlCommand command = 
new SqlCommand(String.Format(
"SELECT * FROM MyTable WHERE mycolumn = {0}", 
ConvertValueToSql(whateverobject))); 
//ConvertValueToSql is what you are asking basically

Essayer de faire ceci:

//Conversion list of used types
Dictionary<Type, SqlDbType> typeConversion = new Dictionary<Type, SqlDbType>();
typeConversion.Add(typeof(String), SqlDbType.NVarChar);
typeConversion.Add(typeof(Int32), SqlDbType.Integer);
//you can even do this if you want
typeConversion.Add(typeof(MyCustomImageClass), SqlDbType.VarBinary);
typeConversion.Add(typeof(MyOtherNiceClass), SqlDbType.NVarChar);
//In the method
SqlCommand command = new SqlCommand("SELECT * FROM MyTable WHERE mycolumn = @Value");
command.Parameters.Add(new SqlParameter("@Value", typeConversion[whateverobject.GetType()]) { Value = whateverobject } );

OriginalL'auteur hwcverwe