Convert C# SQL Parameters list to C# DataTable

Here is the code snippet to convert C# SQL parameters list to C# DataTable.

Happy Coding.

        private DataTable GetDataTableFromParamResults(List<SqlParameter> sqlParameterCollection)

        {

            DataTable paramResultsTable = new DataTable("ParamResultsTable");

            foreach(SqlParameter eachParam in sqlParameterCollection)

            {

                DataColumn newCol = newDataColumn(eachParam.ParameterName.TrimStart(new char[] { '@' }), GetDBType( eachParam.DbType) );

                paramResultsTable.Columns.Add(newCol);

            }

            DataRow newRow = paramResultsTable.NewRow();

            foreach (SqlParameter eachParam in sqlParameterCollection)

            {

                string colName = eachParam.ParameterName.TrimStart(new char[] { '@' });

                newRow[colName] = eachParam.Value;

            }

            paramResultsTable.Rows.Add(newRow);

 

            return paramResultsTable;

        }

 

        private Type GetDBType(DbType inDbType)

        {

            Type togoType = null;

            switch (inDbType)

            {

                case DbType.AnsiString:

                    togoType = Type.GetType("System.String");

                    break;

                case DbType.Binary:

                    togoType = Type.GetType("System.Byte");

                    break;

                case DbType.Byte:

                    togoType = Type.GetType("System.Byte");

                    break;

                case DbType.Boolean:

                    togoType = Type.GetType("System.Boolean");

                    break;

                case DbType.Currency:

                    togoType = Type.GetType("System.Decimal");

                    break;

                case DbType.Date:

                    togoType = Type.GetType("System.DateTime");

                    break;

                case DbType.DateTime:

                    togoType = Type.GetType("System.DateTime");

                    break;

                case DbType.Decimal:

                    togoType = Type.GetType("System.Decimal");

                    break;

                case DbType.Double:

                    togoType = Type.GetType("System.Double");

                    break;

                case DbType.Guid:

                    togoType = Type.GetType("System.Guid");

                    break;

                case DbType.Int16:

                    togoType = Type.GetType("System.Int16");

                    break;

                case DbType.Int32:

                    togoType = Type.GetType("System.Int32");

                    break;

                case DbType.Int64:

                    togoType = Type.GetType("System.Int64");

                    break;

                case DbType.Object:

                    togoType = Type.GetType("System.Object");

                    break;

                case DbType.SByte:

                    togoType = Type.GetType("System.SByte");

                    break;

                case DbType.Single:

                    togoType = Type.GetType("System.Single");

                    break;

                case DbType.String:

                    togoType = Type.GetType("System.String");

                    break;

                case DbType.Time:

                    togoType = Type.GetType("System.DateTime");

                    break;

                case DbType.UInt16:

                    togoType = Type.GetType("System.UInt16");

                    break;

                case DbType.UInt32:

                    togoType = Type.GetType("System.UInt32");

                    break;

                case DbType.UInt64:

                    togoType = Type.GetType("System.UInt64");

                    break;

                case DbType.VarNumeric:

                    togoType = Type.GetType("System.Decimal");

                    break;

                case DbType.AnsiStringFixedLength:

                    togoType = Type.GetType("System.String");

                    break;

                case DbType.StringFixedLength:

                    togoType = Type.GetType("System.String");

                    break;

                case DbType.Xml:

                    togoType = Type.GetType("System.String");

                    break;

                case DbType.DateTime2:

                    togoType = Type.GetType("System.DateTime");

                    break;

                case DbType.DateTimeOffset:

                    togoType = Type.GetType("System.DateTimeOffset");

                    break;

                default:

                    throw new Exception("Unable to convert the type  " + inDbType.ToString() +".");

            }

            return togoType;

        }