Convert List 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;}

Maybe [this](http://andreyzavadskiy.com/2017/07/03/converting-list-to-idatareader/)? At a glance I see it even mentions bulk copy. — Crowcoder, Apr 28 '19 at 12:51

hassan.ef · Answer 1 · 2019-04-28T13:07:51.040

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);
    }

Alen.Toma · Answer 2 · 2019-04-28T13:55:19.543

0

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);
       }
}

edited Apr 28 '19 at 13:55

answered Apr 28 '19 at 13:24

Alen.Toma

4,684
2
14
31

I just pasted this method in my Class, and removed the static keyword. I get the following error `Severity Code Description Project File Line Suppression State Error CS1106 Extension method must be defined in a non-generic static class ` – Illep Apr 28 '19 at 13:34
Yepp is as it says, remove `this` from the parameter :) – Alen.Toma Apr 28 '19 at 13:36
I have updated my Post with the Person Model. I don't understand how to add the DataType and ColumnName for the Person Model. Please help me out. – Illep Apr 28 '19 at 13:41
What do you mean, DataType and ColumnName its already added. if the propertyName and DataTable is the same as the DataTable in the db it should work. look at the line `c.ColumnName = p.PropertyName;` there is where i assign columnName – Alen.Toma Apr 28 '19 at 13:45
How does the Autoincrement part work ? I only have `personId ` as the primary key here. WIll other fields get affected if I Uncomment that line ? – Illep Apr 28 '19 at 13:48
Hmm the best way is to mark personId as PrimaryId by adding an Attribute `[Key]` then in MakeTable you check if PropertyInfo have an Attribute Key then mark Autoincrement to true – Alen.Toma Apr 28 '19 at 13:52
Ok. It now says PropertyInfo does not contain the definition PropertyName. Sorry newbie. – Illep Apr 28 '19 at 13:53
Ok i wrote this here and not in visual studio :) i changed it to Name. and updated my post. good luck – Alen.Toma Apr 28 '19 at 13:56
I am still stuck. may be its beacuse of the primary key. Can you help me out include the primary key in DataColumn ? – Illep Apr 28 '19 at 15:11
This is what I get `The given ColumnMapping does not match up with any column in the source or destination.` – Illep Apr 28 '19 at 15:16

score 0 · Answer 3 · answered Jul 25 '22 at 10:42

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;
    }

Convert List to DBDataReader

3 Answers3