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;

        }

 

Convert DataTable to C# Class

Here is the code snippet to convert a C# DataTable to an existing C# class. If the column name matches with C# field name, then the class object will be created and pushed out.

public static UserInfo GetUserInfoFromDataTable(DataTable userInfoTable)

        {
  if (userInfoTable.Rows.Count == 0) throw new Exception("No UserInfo records found.");

            if (userInfoTable.Rows.Count > 1) throw new Exception("More than one UserInfo records found.");

            DataRow oneRow = userInfoTable.Rows[0];

            UserInfo togoUserInfo = new UserInfo();

            var props = typeof(UserInfo).GetProperties();

            foreach (var prop in props)

            {
  string propName = prop.Name;
 if (oneRow.Table.Columns.Contains(propName))

                {

                    object propNewValue = oneRow[propName];

                    prop.SetValue(togoUserInfo, propNewValue);

                }

            }

            return togoUserInfo;

        }

Happy Coding.

Cheers
Adam