This class can be useful when you make conversion between types .The class supports conversion between .Net Type , SqlDbType and DbType .
using System;
using System.Collections;
using System.Data;
namespace Devintelligence.Common.Data
{
/// <summary>
/// Convert a base data type to another base data type
/// </summary>
public sealed class TypeConvertor
{
private struct DbTypeMapEntry
{
public Type Type;
public DbType DbType;
public SqlDbType SqlDbType;
public DbTypeMapEntry( Type type, DbType dbType, SqlDbType sqlDbType )
{
this.Type = type;
this.DbType = dbType;
this.SqlDbType = sqlDbType;
}
};
private static ArrayList _DbTypeList = new ArrayList();
#region Constructors
static TypeConvertor()
{
DbTypeMapEntry dbTypeMapEntry
= new DbTypeMapEntry(typeof(bool), DbType.Boolean , SqlDbType.Bit);
_DbTypeList.Add( dbTypeMapEntry );
dbTypeMapEntry
= new DbTypeMapEntry(typeof(byte), DbType.Double , SqlDbType.TinyInt);
_DbTypeList.Add( dbTypeMapEntry );
dbTypeMapEntry
= new DbTypeMapEntry(typeof(byte[]), DbType.Binary , SqlDbType.Image);
_DbTypeList.Add( dbTypeMapEntry );
dbTypeMapEntry
= new DbTypeMapEntry(typeof(DateTime), DbType.DateTime , SqlDbType.DateTime);
_DbTypeList.Add( dbTypeMapEntry );
dbTypeMapEntry
= new DbTypeMapEntry(typeof(Decimal), DbType.Decimal , SqlDbType.Decimal);
_DbTypeList.Add( dbTypeMapEntry );
dbTypeMapEntry
= new DbTypeMapEntry(typeof(double), DbType.Double , SqlDbType.Float);
_DbTypeList.Add( dbTypeMapEntry );
dbTypeMapEntry
= new DbTypeMapEntry(typeof(Guid), DbType.Guid , SqlDbType.UniqueIdentifier);
_DbTypeList.Add( dbTypeMapEntry );
dbTypeMapEntry
= new DbTypeMapEntry(typeof(Int16), DbType.Int16 , SqlDbType.SmallInt);
_DbTypeList.Add( dbTypeMapEntry );
dbTypeMapEntry
= new DbTypeMapEntry(typeof(Int32), DbType.Int32 , SqlDbType.Int);
_DbTypeList.Add( dbTypeMapEntry );
dbTypeMapEntry
= new DbTypeMapEntry(typeof(Int64), DbType.Int64 , SqlDbType.BigInt);
_DbTypeList.Add( dbTypeMapEntry );
dbTypeMapEntry
= new DbTypeMapEntry(typeof(object), DbType.Object , SqlDbType.Variant);
_DbTypeList.Add( dbTypeMapEntry );
dbTypeMapEntry
= new DbTypeMapEntry(typeof(string), DbType.String , SqlDbType.VarChar);
_DbTypeList.Add( dbTypeMapEntry );
}
private TypeConvertor()
{
}
#endregion
#region Methods
/// <summary>
/// Convert db type to .Net data type
/// </summary>
/// <param name="dbType"></param>
/// <returns></returns>
public static Type ToNetType(DbType dbType)
{
DbTypeMapEntry entry = Find( dbType );
return entry.Type;
}
/// <summary>
/// Convert TSQL type to .Net data type
/// </summary>
/// <param name="sqlDbType"></param>
/// <returns></returns>
public static Type ToNetType(SqlDbType sqlDbType)
{
DbTypeMapEntry entry = Find( sqlDbType );
return entry.Type;
}
/// <summary>
/// Convert .Net type to Db type
/// </summary>
/// <param name="type"></param>
/// <returns></returns>
public static DbType ToDbType( Type type )
{
DbTypeMapEntry entry = Find( type );
return entry.DbType;
}
/// <summary>
/// Convert TSQL data type to DbType
/// </summary>
/// <param name="sqlDbType"></param>
/// <returns></returns>
public static DbType ToDbType( SqlDbType sqlDbType )
{
DbTypeMapEntry entry = Find( sqlDbType );
return entry.DbType;
}
/// <summary>
/// Convert .Net type to TSQL data type
/// </summary>
/// <param name="type"></param>
/// <returns></returns>
public static SqlDbType ToSqlDbType( Type type )
{
DbTypeMapEntry entry = Find( type );
return entry.SqlDbType;
}
/// <summary>
/// Convert DbType type to TSQL data type
/// </summary>
/// <param name="dbType"></param>
/// <returns></returns>
public static SqlDbType ToSqlDbType( DbType dbType )
{
DbTypeMapEntry entry = Find( dbType );
return entry.SqlDbType;
}
private static DbTypeMapEntry Find( Type type )
{
object retObj = null;
for( int i=0; i<_DbTypeList.Count; i++ )
{
DbTypeMapEntry entry = (DbTypeMapEntry)_DbTypeList[i];
if( entry.Type == type )
{
retObj = entry;
break;
}
}
if(retObj==null)
{
throw
new ApplicationException("Referenced an unsupported Type");
}
return (DbTypeMapEntry)retObj;
}
private static DbTypeMapEntry Find( DbType dbType )
{
object retObj = null;
for( int i=0; i<_DbTypeList.Count; i++ )
{
DbTypeMapEntry entry = (DbTypeMapEntry)_DbTypeList[i];
if( entry.DbType == dbType )
{
retObj = entry;
break;
}
}
if(retObj==null)
{
throw
new ApplicationException("Referenced an unsupported DbType");
}
return (DbTypeMapEntry)retObj;
}
private static DbTypeMapEntry Find( SqlDbType sqlDbType )
{
object retObj = null;
for( int i=0; i<_DbTypeList.Count; i++ )
{
DbTypeMapEntry entry = (DbTypeMapEntry)_DbTypeList[i];
if( entry.SqlDbType == sqlDbType )
{
retObj = entry;
break;
}
}
if(retObj==null)
{
throw
new ApplicationException("Referenced an unsupported SqlDbType");
}
return (DbTypeMapEntry)retObj;
}
#endregion
}
}
12 comments:
Excellent. Just what I needed!
Note: You might need to add support for NVarChar and the other 'N' types.
Thanks.
Fletch
Thanks,
Excellent!
Tobbbe
I'm frustrated that this isn't included in .NET by default. Many thanks.
How (or could I) apply that to this...
//*** delcare command type, definition, stored procedure and parameters ***
comm.CommandType = CommandType.StoredProcedure;
comm.CommandText = "sp_ReportMaster";
...
_db.AddInParameter(comm, "@evaluatorId", DbType.String, evaluatorID);
...
What I am trying to do is get past the 4000 character limit of DbType and take advantage ot the SqlDbType (8000 characters).
Any thoughts...
Thanks in advance!
You can pass to the stored procedure two DbType.String parameters instead one .
Thanks for idea... but I'm already doing that (if we are thinking the same).
I should have reveailed more of the code...
_db.AddInParameter(comm, "@evaluatorId", DbType.String, evaluatorID);
_db.AddInParameter(comm, "@employeeID", DbType.String, employeeID);
_db.AddInParameter(comm, "@evalID", DbType.String, evalID);
_db.AddInParameter(comm, "@eval_Loc", DbType.String, eval_Loc);
_db.AddInParameter(comm, "@eval_ProCent", DbType.String, eval_ProCent);
_db.AddInParameter(comm, "@eval_Depart", DbType.String, eval_Depart);
_db.AddInParameter(comm, "@eval_PractGroup", DbType.String, eval_PractGroup);
_db.AddInParameter(comm, "@status", DbType.String, status);
_db.AddInParameter(comm, "@classPart", DbType.String, classPart);
...
Each individual and a few grouped parameters work fine on their own, however, it seems when I pass all parameters at once I get the problem. It's like the reader is treating it as on big string.
The params ar long because I'm using an "IN" claus in the sp.
in other words evaluatorID and employeeId can be really long strings (and work fine alone when all the values are passed). but when I send all valuse for both it blows up.
I can see from the debugger it's a string length issue for sure.
Thanks again for your input.
Any other ideas?
Try to play with Size property of SqlParameter class.You need to create
SqlParameter separately and set the size of the string.
Actually I found the problem... As it would turn out, .NET is not the problem.
The problem was with the stored procedure.
I was originally using...
DECLARE @sql AS VARCHAR(8000)
...//some code//
EXEC (@sql)
I extended the values using a concatenation...
DECLARE @sql_1 AS VARCHAR(8000),
@sql_2 AS VARCHAR(8000)
...//some code//
EXEC (@sql_1 + @sql_2)
Thanks for inspiring the solution, your input prompted the idea.
Thanks again!
Excellent, thanks very much!!...
Only 1 obs, at line say:
dbTypeMapEntry
= new DbTypeMapEntry(typeof(byte), DbType.Double , SqlDbType.TinyInt);
_DbTypeList.Add( dbTypeMapEntry );
DbType.Double ? and DbType.Byte ? that's correct not ?
Sorry my english :( ...
You need add a Char type to this code.
There is two DbType.Double entered into the list. It will create an exception.
I'm also frustrated that this is not included in .NET Framework.
Thank you very much!
Post a Comment