Dapper Dynamic Parameters with Table Valued Parameters

Question

I was trying to create a generic method, which can read the parameters name and value from a class at Runtime and create parameter collection for Dapper query execution. Realized that till the point all parameters are Input type it works well, but if I have to add an Output / ReturnValue type parameters, then I need to work with DynamicParameters, else I cannot fetch the value of Output / ReturnValue parameters

SP has following parameters:

PersonList - TableValued - Input
TestOutput - Int - Output

I am not able to make following piece of code work:

var dynamicParameters = new DynamicParameters();
dynamicParameters.Add("PersonList", <DataTable PersonList>);
dynamicParameters.Add("TestOutput", 0, Dbtype.Int32, ParameterDirection.Output);

Exception is:

System.Data.SqlClient.SqlException: The incoming tabular data stream (TDS) remote procedure call (RPC) protocol stream is incorrect. Parameter 1 ("@PersonList"): Data type 0x62 (sql_variant) has an invalid type for type- specific metadata.

Issue as I can understand is there's no valid DbType available for adding a TVP to the Dynamic Parameters, since I am not using the SqlDbType, so there's no replacement for SqlDbType.Structured in the DbType.

Any pointer or workaround to resolve the issue

Answer 1

I found Rohit Shetty's answer helpful, but still had trouble as it did not provide a complete example. Here is an example with more sample code.

In SQL, define your User Defined Table Type:

CREATE TYPE [dbo].[IntListTableType]
AS TABLE
(
    [Value] INT NOT NULL
);

Then in C# you would do something like this:

List<int> employeeIds = GetEmployeeIds();
...
// Create and populate the table type for the stored procedure.
DataTable employeeIdsTable = new DataTable();
employeeIdsTable.Columns.Add("Value", typeof(int));
foreach (var employeeId in employeeIds)
{
    employeeIdsTable.Rows.Add(employeeId);
}

var storedProcedure = "[dbo].[GetEmployeesById]";
var storedProcedureParameters = new DynamicParameters();
storedProcedureParameters.Add("@EmployeeIds", employeeIdsTable.AsTableValuedParameter());

using (var connection = new SqlConnection(databaseConnectionString))
{
    var results = await connection.QueryAsync<IEnumerable<Employee>>(storedProcedure, storedProcedureParameters, commandType: System.Data.CommandType.StoredProcedure, commandTimeout: 60);
    return results;
}

You can optionally provide the name of the User Defined Table Type (e.g. [dbo].[IntListTableType]) in the AsTableValuedParameter method, but I found it worked fine without it in my scenario, so I'm not certain when it would be required.

Answer 2

Inside the add function of parameter object pass argument DbType.Object

eg:

DynamicParameters parameterObject = new DynamicParameters();
parameterObject.Add("@table", dtTable, DbType.Object);

Answer 3

I reached here as I had the same problem - Wanted to call a StoredProcedure passing in a TABLE TYPE parameter to implement BulkInsert

The solution makes use of the Dapper.ParameterExtensions NuGet.

Steps for me were:

1. Create the UserDefinedTableType

    CREATE TYPE [core].[DataErrorType] AS TABLE (
    [Id] [int] NULL,
    [SubmissionResponseErrorId] [int] NOT NULL,
    [ErrorCode] [nvarchar](30) NOT NULL,
    [Severity] [int] NULL,
    [RecordReferenceId] [int] NOT NULL) 
   

2. Create the Stored Procedure

    CREATE OR ALTER PROCEDURE [core].[spBulkInsertDataErrors]
    (
        @dataErrors [core].[DataErrorType] READONLY
    )
    AS
    INSERT INTO [core].[DataError] ([SubmissionResponseErrorId], [ErrorCode], [Severity], [RecordReferenceId])
    SELECT [SubmissionResponseErrorId], [ErrorCode], [Severity], [RecordReferenceId] 
    FROM @dataErrors
   

3. In the C# code, make use of the AddTable() extension method of DynamicParameters. The method can be found in DapperParameters NuGet package: Dapper.ParameterExtensions

    var parameters = new DynamicParameters();
    parameters.AddTable("@dataErrors", "core.DataErrorType", dataErrors);
    await sql.ExecuteAsync("[core].[spBulkInsertDataErrors]", parameters, transaction, commandType: CommandType.StoredProcedure);
   

Answer 4

CREATE TYPE [Common].[IDList] AS TABLE([ID] [int] NULL)
GO

using Microsoft.Data.SqlClient;
using Microsoft.Data.SqlClient.Server;
using System;
using System.Collections;
using System.Collections.Generic;
using System.Data;
using static Dapper.SqlMapper;

namespace Test
{
    public class TableOfIntegersTypeHandler : ITypeHandler
    {
        public object Parse(Type destinationType, object value)
        {
            throw new NotImplementedException();
        }

        public void SetValue(IDbDataParameter parameter, object value)
        {
            var p = (SqlParameter)parameter;
            p.TypeName = "Common.IDList";
            p.SqlDbType = SqlDbType.Structured;
            p.Value = AsSqlDataRecords((IEnumerable)value);
        }

        private static List<SqlDataRecord> AsSqlDataRecords(IEnumerable collection, string columnName = "ID")
        {
            var records = new List<SqlDataRecord>();
            var meta = new SqlMetaData[] { new SqlMetaData(columnName, SqlDbType.Int) };
            foreach (var num in collection)
            {
                var record = new SqlDataRecord(meta);
                if (num is null)
                {
                    record.SetDBNull(0);
                }
                else
                {
                    record.SetInt32(0, (int)num);
                }

                records.Add(record);
            }

            return records;
        }

        private static DataTable AsDataTable(IEnumerable collection, string columnName = "ID")
        {
            var tvp = new DataTable();
            var enumerator = collection.GetEnumerator();
            if (enumerator.MoveNext())
            {
                tvp.Columns.Add(new DataColumn(columnName, enumerator.Current.GetType()));

                do
                {
                    tvp.Rows.Add(enumerator.Current);
                }
                while (enumerator.MoveNext());
            }

            return tvp;
        }
    }
}

using Microsoft.Data.SqlClient;
using System;
using System.Collections.Generic;
using Dapper;

namespace Test
{
    public static class Program
    {
        public static void Main(string[] args)
        {
            var typeHandler = new TableOfIntegersTypeHandler();
            SqlMapper.AddTypeHandler(typeof(int[]), typeHandler);
            SqlMapper.AddTypeHandler(typeof(int?[]), typeHandler);
            SqlMapper.AddTypeHandler(typeof(List<int>), typeHandler);
            SqlMapper.AddTypeHandler(typeof(List<int?>), typeHandler);
            SqlMapper.AddTypeHandler(typeof(IEnumerable<int>), typeHandler);
            SqlMapper.AddTypeHandler(typeof(IEnumerable<int?>), typeHandler);

            try
            {
                using var con = new SqlConnection(...);
                con.Open();

                var ps = new DynamicParameters();
                ps.Add("@Ids", new List<int>(new[] { 1, 2, 3, 4, 5 }));

                var ids = con.Query<int>("select * from @Ids", ps);

                ids = con.Query<int>("select * from @Ids", new { Ids = new[] { 1, 2 } });
            }
            catch (Exception ex)
            {
                Console.WriteLine(ex);
            }

            Console.WriteLine("Hello, World!");
            Console.ReadLine();
        }
    }
}

Answer 5

First create a User Defined Table type in Database

CREATE TYPE udtt_PersonList AS TABLE 
(
    ...
)
GO

In your code

var dynamicParameters = new DynamicParameters();
dynamicParameters.Add("@PersonList", PersonList.AsTableValuedParameter("[dbo].[udtt_PersonList]"));
dynamicParameters.Add("TestOutput", 0, Dbtype.Int32, ParameterDirection.Output);

Answer 6

As I can understand that this requirement is not supported out of the box and I may need to code the specific helper. I have resolved it using a custom base abstract class TypeMap, which can be extended by all kinds of providers, to implement the API, which are not out of he box possible using the Dapper, I am pasting my implementation related to SQL-Server, similar can be done for other ADO.Net compliant providers:

namespace Dapper
{
    #region NameSpaces

    using System;
    using System.Collections.Generic;
    using System.Data;
    using System.Data.SqlClient;
    using System.Linq;

    #endregion


        /// <summary>
        ///     Type Map class for database provider specific code
        /// </summary>
        internal abstract class TypeMap
        {
            /// <summary>
            /// Only Non Input Parameters collection
            /// </summary>
            public abstract Dictionary<string, object> NonInputParameterCollection { get; set; } 

            /// <summary>
            /// Method to execute the DML via TypeMap
            /// </summary>
            /// <param name="connection"></param>
            /// <param name="sql"></param>
            /// <param name="commandType"></param>
            /// <param name="dapperParams"></param>
            /// <returns></returns>
            public abstract int Execute(IDbConnection connection, 
                                        string sql, 
                                        CommandType commandType,
                                        IEnumerable<DapperParam> dapperParams );

            /// <summary>
            /// Method to execute the Select to fetch IEnumerable via TypeMap
            /// </summary>
            /// <typeparam name="T"></typeparam>
            /// <param name="connection"></param>
            /// <param name="sql"></param>
            /// <param name="commandType"></param>
            /// <param name="dapperParams"></param>
            /// <returns></returns>
            public abstract IEnumerable<T> Query<T>(IDbConnection connection,
                                                    string sql,
                                                    CommandType commandType,
                                                    IEnumerable<DapperParam> dapperParams) where T : new();

            /// <summary>
            /// Fetch the relevant TypeMap
            /// </summary>
            /// <param name="provider"></param>
            /// <returns></returns>
            public static TypeMap GetTypeMap(string provider)
            {
                TypeMap typeMap = null;

                switch (provider)
                {
                    case "System.Data.SqlClient":
                        typeMap = new SqlTypeMap();
                        break;
                    default:
                        // SQl Server TypeMap
                        typeMap = new SqlTypeMap();
                        break;
                }

                return (typeMap);
            }
        }

        /// <summary>
        ///     SQL Server provider type map
        /// </summary>
        internal class SqlTypeMap : TypeMap
        {
            public SqlTypeMap()
            {
                NonInputParameterCollection = new Dictionary<string, object>();
            }

            public override sealed Dictionary<string, object> NonInputParameterCollection { get; set; } 

            public override int Execute(IDbConnection connection,
                                        string sql,
                                        CommandType commandType,
                                        IEnumerable<DapperParam> dapperParams)
            {
                int returnValue = -1;

                var sqlConnection = (connection as SqlConnection) ?? new SqlConnection();

                using (sqlConnection)
                {
                    SqlCommand sqlCommand = null;

                    sqlCommand = sqlConnection.CreateCommand();

                    using (sqlCommand)
                    {
                        // public SqlParameter(string parameterName, SqlDbType dbType, int size, ParameterDirection direction, byte precision, byte scale, string sourceColumn, DataRowVersion sourceVersion, bool sourceColumnNullMapping, object value, string xmlSchemaCollectionDatabase, string xmlSchemaCollectionOwningSchema, string xmlSchemaCollectionName);
                        foreach (var param in dapperParams)
                        {
                            sqlCommand.Parameters.Add(new SqlParameter
                            {
                                ParameterName = param.ParamName,
                                SqlValue = param.ParamValue ?? DBNull.Value,
                                SqlDbType = TypeToSqlDbType[param.ParamType],
                                Direction = Map.DirectionMap[param.ParamDirection]
                            });
                        }

                        sqlCommand.CommandText = sql; // Assign Sql Text
                        sqlCommand.CommandType = commandType; // Assign CommandType
                        sqlCommand.Connection.Open(); // Explicitly open connection to use it with SqlCommand object
                        returnValue = sqlCommand.ExecuteNonQuery(); // Execute Query

                        foreach (SqlParameter param in sqlCommand.Parameters.Cast<SqlParameter>().Where(param => param.Direction != ParameterDirection.Input))
                            NonInputParameterCollection.Add(param.ParameterName, param.Value);
                    }
                }

                return (returnValue);
            }

            public override IEnumerable<T> Query<T>(IDbConnection connection,
                                      string sql,
                                      CommandType commandType,
                                      IEnumerable<DapperParam> dapperParams)
            {
                IEnumerable<T> returnEnumerable = null;

                var sqlConnection = (connection as SqlConnection) ?? new SqlConnection();

                using (sqlConnection)
                {
                    var sqlCommand = sqlConnection.CreateCommand();

                    using (sqlCommand)
                    {
                        foreach (var param in dapperParams)
                        {
                            sqlCommand.Parameters.Add(new SqlParameter
                            {
                                ParameterName = param.ParamName,
                                SqlValue = param.ParamValue ?? DBNull.Value,
                                SqlDbType = TypeToSqlDbType[param.ParamType],
                                Direction = Map.DirectionMap[param.ParamDirection]
                            });
                        }

                        sqlCommand.CommandText = sql; // Assign Sql Text
                        sqlCommand.CommandType = commandType; // Assign CommandType

                        var sqlDataAdapter = new SqlDataAdapter(sqlCommand);

                        var returnDataTable = new DataTable();

                        sqlDataAdapter.Fill(returnDataTable);

                        returnEnumerable = Common.ToList<T>(returnDataTable);

                        foreach (SqlParameter param in sqlCommand.Parameters.Cast<SqlParameter>()
                                                                 .Where(param => param.Direction != ParameterDirection.Input))
                            NonInputParameterCollection.Add(param.ParameterName, param.Value);
                    }
                }

                return (returnEnumerable);
            }

            /// <summary>
            ///     Data Type to Db Type mapping dictionary for SQL Server
            /// https://msdn.microsoft.com/en-us/library/cc716729(v=vs.110).aspx
            /// </summary>

            public static readonly Dictionary<Type, SqlDbType> TypeToSqlDbType = new Dictionary<Type, SqlDbType>
            {
              // Mapping C# types to Ado.net SqlDbType enumeration
                {typeof (byte), SqlDbType.TinyInt},
                {typeof (sbyte), SqlDbType.TinyInt},
                {typeof (short), SqlDbType.SmallInt},
                {typeof (ushort), SqlDbType.SmallInt},
                {typeof (int), SqlDbType.Int},
                {typeof (uint), SqlDbType.Int},
                {typeof (long), SqlDbType.BigInt},
                {typeof (ulong), SqlDbType.BigInt},
                {typeof (float), SqlDbType.Float},
                {typeof (double), SqlDbType.Float},
                {typeof (decimal), SqlDbType.Decimal},
                {typeof (bool), SqlDbType.Bit},
                {typeof (string), SqlDbType.VarChar},
                {typeof (char), SqlDbType.Char},
                {typeof (Guid), SqlDbType.UniqueIdentifier},
                {typeof (DateTime), SqlDbType.DateTime},
                {typeof (DateTimeOffset), SqlDbType.DateTimeOffset},
                {typeof (byte[]), SqlDbType.VarBinary},
                {typeof (byte?), SqlDbType.TinyInt},
                {typeof (sbyte?), SqlDbType.TinyInt},
                {typeof (short?), SqlDbType.SmallInt},
                {typeof (ushort?), SqlDbType.SmallInt},
                {typeof (int?), SqlDbType.Int},
                {typeof (uint?), SqlDbType.Int},
                {typeof (long?), SqlDbType.BigInt},
                {typeof (ulong?), SqlDbType.BigInt},
                {typeof (float?), SqlDbType.Float},
                {typeof (double?), SqlDbType.Float},
                {typeof (decimal?), SqlDbType.Decimal},
                {typeof (bool?), SqlDbType.Bit},
                {typeof (char?), SqlDbType.Char},
                {typeof (Guid?), SqlDbType.UniqueIdentifier},
                {typeof (DateTime?), SqlDbType.DateTime},
                {typeof (DateTimeOffset?), SqlDbType.DateTimeOffset},
                {typeof (System.Data.Linq.Binary), SqlDbType.Binary},
                {typeof (IEnumerable<>), SqlDbType.Structured},
                {typeof (List<>), SqlDbType.Structured},
                {typeof (DataTable), SqlDbType.Structured},

            };



        }

        /// <summary>
        /// 
        /// </summary>
        public static class Map
    {
        /// <summary>
        /// 
        /// </summary>
        public static Dictionary<Type, DbType> TypeToDbType = new Dictionary<Type, DbType>()
        {
            {typeof (byte), DbType.Byte},
            {typeof (sbyte), DbType.Byte},
            {typeof (short), DbType.Int16},
            {typeof (ushort), DbType.Int16},
            {typeof (int), DbType.Int32},
            {typeof (uint), DbType.Int32},
            {typeof (long), DbType.Int64},
            {typeof (ulong), DbType.Int64},
            {typeof (float), DbType.Single},
            {typeof (double), DbType.Double},
            {typeof (decimal), DbType.Decimal},
            {typeof (bool), DbType.Boolean},
            {typeof (string), DbType.String},
            {typeof (char), DbType.StringFixedLength},
            {typeof (Guid), DbType.Guid},
            {typeof (DateTime), DbType.DateTime},
            {typeof (DateTimeOffset), DbType.DateTimeOffset},
            {typeof (byte[]), DbType.Binary},
            {typeof (byte?), DbType.Byte},
            {typeof (sbyte?), DbType.Byte},
            {typeof (short?), DbType.Int16},
            {typeof (ushort?), DbType.Int16},
            {typeof (int?), DbType.Int32},
            {typeof (uint?), DbType.Int32},
            {typeof (long?), DbType.Int64},
            {typeof (ulong?), DbType.Int64},
            {typeof (float?), DbType.Single},
            {typeof (double?), DbType.Double},
            {typeof (decimal?), DbType.Decimal},
            {typeof (bool?), DbType.Boolean},
            {typeof (char?), DbType.StringFixedLength},
            {typeof (Guid?), DbType.Guid},
            {typeof (DateTime?), DbType.DateTime},
            {typeof (DateTimeOffset?), DbType.DateTimeOffset},
            {typeof (System.Data.Linq.Binary), DbType.Binary}
        };

        /// <summary>
        ///     Parameter Direction for Stored Procedure
        /// </summary>
        public static readonly Dictionary<string, ParameterDirection> DirectionMap =
               new Dictionary<string, ParameterDirection>(StringComparer.InvariantCultureIgnoreCase)
            {
                {ParamDirectionConstants.Input, ParameterDirection.Input},
                {ParamDirectionConstants.Output, ParameterDirection.Output},
                {ParamDirectionConstants.InputOutput, ParameterDirection.InputOutput},
                {ParamDirectionConstants.ReturnValue, ParameterDirection.ReturnValue}
            };
    }
}

Supporting classes and API, to make the above code work:

using System;
using System.Collections.Generic;

namespace Dapper
{
    public class DapperParam
    {
        /// <summary>
        ///     Parameter Type Constructor
        /// </summary>
        /// <param name="paramName"></param>
        /// <param name="paramType"></param>
        /// <param name="paramDirection"></param>
        /// <param name="paramValue"></param>
        public DapperParam(string paramName,
                        Type paramType,
                        string paramDirection,
                        object paramValue)
        {
            ParamName = paramName;
            ParamType = paramType;
            ParamDirection = paramDirection;
            ParamValue = paramValue;
        }

        /// <summary>
        ///     Parameter name
        /// </summary>
        public string ParamName { get; set; }

        /// <summary>
        ///     Parameter Type
        /// </summary>
        public Type ParamType { get; set; }

        /// <summary>
        ///     Parameter Direction
        /// </summary>
        public string ParamDirection { get; set; }

        /// <summary>
        ///     Parameter Value
        /// </summary>
        public object ParamValue { get; set; }

    }

    internal static class DataConversionMap
    {
        /// <summary>
        ///     Type conversion, handles null
        /// </summary>
        /// <param name="obj"></param>
        /// <param name="func"></param>
        /// <returns></returns>
        private static object ConvertDbData(object obj, Func<object> func)
        {
            return (!Convert.IsDBNull(obj)) ? func() : null;
        }

        /// <summary>
        ///     Dictionary map to convert to a given DataType. Returns a Func of object,object.
        ///     Internally calls ConvertDbData for Data Type conversion
        /// </summary>
        public static readonly Dictionary<Type, Func<object, object>> Map =
            new Dictionary<Type, Func<object, object>>
            {
                {
                    typeof(Int16),
                    objectValue => ConvertDbData(objectValue, () => Convert.ToInt16(objectValue))
                },

                {
                    typeof(Int32),
                    objectValue => ConvertDbData(objectValue, () => Convert.ToInt32(objectValue))
                },

                {
                    typeof(Int64),
                    objectValue => ConvertDbData(objectValue, () => Convert.ToInt64(objectValue))
                },

                {
                    typeof(Boolean),
                    objectValue => ConvertDbData(objectValue, () => Convert.ToBoolean(objectValue))
                },

                {
                    typeof(string),
                    objectValue => ConvertDbData(objectValue, () => Convert.ToString(objectValue))
                },

                {
                   typeof(DateTime), objectValue =>

                        ConvertDbData(objectValue, () =>
                        {
                            DateTime dateTime = Convert.ToDateTime(objectValue);

                            if (dateTime.TimeOfDay.Equals(TimeSpan.Zero))
                                return dateTime.ToShortDateString();

                            return dateTime.ToString("MM/dd/yyyy HH:mm");
                        })

                },

                {
                    typeof(Byte),
                    objectValue => ConvertDbData(objectValue, () => Convert.ToByte(objectValue))
                },

                {
                    typeof(Double),
                    objectValue => ConvertDbData(objectValue, () => Convert.ToDouble(objectValue))
                },

                {
                    typeof(Decimal),
                    objectValue => ConvertDbData(objectValue, () => Convert.ToDecimal(objectValue))
                },

                {
                    typeof(TimeSpan),
                    objectValue => ConvertDbData(objectValue, () => TimeSpan.Parse(objectValue.ToString()))
                },

                {
                    typeof(Guid),
                    objectValue => ConvertDbData(objectValue, () => new Guid(objectValue.ToString()))
                },

                {
                    typeof(Byte[]),
                    objectValue => ConvertDbData(objectValue, () => (Byte[])(objectValue))
                }
            };
    }
}

Common APIs

public static class Common
    {
        /// <summary>
        ///  Convert IEnumerable<T> to DataTable
        /// </summary>
        /// <typeparam name="T"></typeparam>
        /// <param name="collection"></param>
        /// <returns></returns>
        public static DataTable CreateTable<T>(this IEnumerable<T> collection)
        {
            // Fetch the type of List contained in the ParamValue
            var tableType = typeof(T);

            // Create DataTable which will contain data from List<T>
            var dataTable = new DataTable();

            // Fetch the Type fields count
            int columnCount = tableType.GetProperties().Count();

            var columnNameMappingDictionary = new Dictionary<string, string>();

            // Create DataTable Columns using table type field name and their types
            // Traversing through Column Collection
            for (int counter = 0; counter < columnCount; counter++)
            {
                var propertyInfo = tableType.GetProperties()[counter];

                var parameterAttribute = propertyInfo.GetParameterAttribute();

                string columnName = (parameterAttribute != null) ? parameterAttribute.Name : propertyInfo.Name;

                columnNameMappingDictionary.Add(propertyInfo.Name,
                    (parameterAttribute != null) ? parameterAttribute.Name : propertyInfo.Name);

                dataTable.Columns.Add(columnName, tableType.GetProperties()[counter].PropertyType);
            }

            // Return parameter with null value
            if (collection == null)
                return dataTable;

            // Traverse through number of entries / rows in the List
            foreach (var item in collection)
            {
                // Create a new DataRow
                DataRow dataRow = dataTable.NewRow();

                // Traverse through type fields or column names
                for (int counter = 0; counter < columnCount; counter++)
                {
                    // Fetch Column Name
                    string columnName = columnNameMappingDictionary[tableType.GetProperties()[counter].Name];

                    //Fetch Value for each column for each element in the List<T>
                    dataRow[columnName] = item
                        .GetType().GetProperties()[counter]
                        .GetValue(item);
                }
                // Add Row to Table
                dataTable.Rows.Add(dataRow);
            }

            return (dataTable);
        }

        /// <summary>
        /// Convert IEnumerable<T> to DataTable
        /// </summary>
        /// <param name="paramValue"></param>
        /// <returns></returns>
        public static DataTable CreateTable(object paramValue)
        {
            // Fetch the type of List contained in the ParamValue
            Type tableType = paramValue.GetType().GetGenericArguments()[0];

            // Create DataTable which will contain data from List<T>
            var genericDataTable = new DataTable();

            // Fetch the Type fields count
            int fieldCount = tableType.GetProperties().Count();

            // Create DataTable Columns using table type field name and their types
            // Traversing through Column Collection
            for (int counter = 0; counter < fieldCount; counter++)
            {
                genericDataTable.Columns.Add(tableType.GetProperties()[counter].Name,
                    tableType.GetProperties()[counter].PropertyType);
            }

            // Traverse through number of entries / rows in the List
            foreach (var item in (IEnumerable)paramValue)
            {
                // Create a new DataRow
                DataRow dataRow = genericDataTable.NewRow();

                // Traverse through type fields or column names
                for (int counter = 0; counter < fieldCount; counter++)
                {
                    // Fetch Column Name
                    string columnName = tableType.GetProperties()[counter].Name;

                    //Fetch Value for each column for each element in the List<T>
                    dataRow[columnName] = item
                        .GetType().GetProperties()[counter]
                        .GetValue(item);
                }
                // Add Row to Table
                genericDataTable.Rows.Add(dataRow);
            }
            return genericDataTable;
        }

        /// <summary>
        /// Convert DataTable to List<T>
        /// </summary>
        /// <typeparam name="T"></typeparam>
        /// <param name="dataTable"></param>
        /// <returns></returns>
        public static List<T> ToList<T>(DataTable dataTable) where T : new()
        {
            // Final result List (Converted from DataTable)
            var convertedList = new List<T>();

            // Traverse through Rows in the DataTable
            foreach (DataRow row in dataTable.Rows)
            {
                // Type T of generic list object
                var dataObject = new T();

                // Traverse through Columns in the DataTable
                foreach (DataColumn column in dataTable.Columns)
                {
                    // Fetch column name
                    string fieldName = column.ColumnName;

                    // Fetch type PropertyInfo using reflection
                    var propertyInfo = dataObject.GetType()
                        .GetProperty(fieldName,
                            BindingFlags.IgnoreCase | BindingFlags.Public | BindingFlags.Instance);

                    // For Null PropertyInfo, check whether ViewrColumn attribute is applied
                    propertyInfo = propertyInfo ?? Parameter.GetColumnAttribute(dataObject.GetType(), fieldName);

                    // Set the value for not null property Info
                    // Continue the loop for a null PropertyInfo (needs correction either in type description or DataTable selection)
                    if (propertyInfo == null) continue;

                    // Property value
                    var value = row[column];

                    // New - Work for Nullable Types
                    propertyInfo.SetValue(dataObject,
                        DataConversionMap.Map[propertyInfo.PropertyType](value), null);
                }

                // Add type object to the List
                convertedList.Add(dataObject);
            }

            return (convertedList);
        }
    }