Convert List<X> to DBDataReader

Question

How can I Convert a List<Person> personList to DBDataReader ?

In the code that I have given below, I am trying to bulk insert personList. I have around 500k records, and the method WriteToServer expects a DBDataReader and I have a List<Person>. How can I convert List<Person> to DBDataReader

using (SqlBulkCopy bc= new SqlBulkCopy(constr)) {
      bc.DestinationTableName = "MyPersonTable";
       try
       {
             bc.WriteToServer(personList);
       }
       catch (Exception ex)
       {
             Console.WriteLine(ex.Message);
       }
}\

\

**Person Model**

public int personId {get;set;} // Primarykey
public string personName {get;set;} 
public int personAge {get;set;} 
public DateTime personCreatedDate {get;set;} 

Answer 1

you can use this one and after that with SqlBulkCopy insert all data to database:

 IList<Name> list = new List<Name>();
    list.Add(new Name{ Forename="Bert", Surname="Fred"});

    list.Add(new Name { Forename = "John", Surname = "Smith" });
    DataTable table = new DataTable();
    table.Columns.Add("Forename");
    table.Columns.Add("Surname");

    foreach (Name item in list)
    {
        var row = table.NewRow();

        row["Forename"] = item.Forename;
        row["Surname"] = item.Surname;

        table.Rows.Add(row);
    }

Answer 2

Here is a generic way in creating a table.

There is another way of doing this too by creating a custom class that inherit from IDataReader if you are intressted in it feel free to tell.

Anyway have a look below, this is one way.

public static DataTable MakeTable(this List<object> o)
{
  var data = new DataTable();
  var props = o.FirstOrDefault()?.GetType().GetProperties();
  if (props == null)
      return data;
  forEach(var p in props){
        DataColumn c = new DataColumn();
        c.DataType = p.PropertyType;
        c.ColumnName = p.Name;
        // c.AutoIncrement = true; // if this is a primaryKey 
        data.Columns.Add(c);
  }

  forEach(var item in o){
    var row = data.NewRow();
    forEach(var p in props){
     row[p.Name] = p.GetValue(item);
     }
     data.Rows.Add(row);
  }
  return data;
}
   // now all you need is to call MakeTable
   using (SqlBulkCopy bc= new SqlBulkCopy(constr)) {
      bc.DestinationTableName = "MyPersonTable";
       try
       {
             bc.WriteToServer(personList.MakeTable());
       }
       catch (Exception ex)
       {
             Console.WriteLine(ex.Message);
       }
}

Answer 3

Here is example of Generic of Bulk Insert

    public async Task CreateBulkAsync(IEnumerable<T> entities, CancellationToken cancellationToken = default)
    {
        var options = SqlBulkCopyOptions.KeepIdentity | SqlBulkCopyOptions.CheckConstraints |
                      SqlBulkCopyOptions.KeepNulls;
        var props = _context.Model.FindEntityType(typeof(T)).GetProperties();

        // Берем connection и !не закрываем! это connection контекста, поэтому он сам этим управляет
        if (_context.Database.GetDbConnection() is SqlConnection connection)
        {
            if (connection.State == ConnectionState.Closed)
            {
                await connection.OpenAsync(cancellationToken);
            }

            using var sqlBulkCopy = new SqlBulkCopy(connection, options, null);
            {
                sqlBulkCopy.DestinationTableName = $"dbo.{typeof(T).Name}";

                foreach (var property in props)
                {
                    sqlBulkCopy.ColumnMappings.Add(property.Name, property.Name);
                }

                var dt = entities.EntityToDataTable();
                await sqlBulkCopy.WriteToServerAsync(dt, cancellationToken);
            }
        }
        else
        {
            throw new ArgumentNullException(nameof(connection));
        }
    }

Here EntityToDataTable function code:

    public static DataTable EntityToDataTable<T>(this IEnumerable<T> source)
    {
        var dataTable = new DataTable(typeof(T).Name);
        var props = typeof(T).GetProperties(BindingFlags.Public | BindingFlags.Instance);

        foreach (var prop in props)
        {
            dataTable.Columns.Add(prop.Name, prop.PropertyType.Name.Contains("Nullable") ? typeof(string) : prop.PropertyType);
        }
        foreach (T item in source)
        {
            var values = new object[props.Length];
            for (var i = 0; i < props.Length; i++)
            {
                values[i] = props[i].GetValue(item, null);
            }
            dataTable.Rows.Add(values);
        }

        return dataTable;
    }