SqlClient Bulk Insert Without DataTable in .Net Core

Question

I'm investigating porting our API's to .Net Core 1.0.

Unfortunately, datatable is not supported, and we use them to bulk insert data into SQL Server.

For example:

const string sqlText = @"
                INSERT INTO MyTable (ID, Name)
                SELECT ID, Name
                FROM @MyList n
                ";      
var dataTable = new DataTable("MyTable");
dataTable.Columns.Add("ID", typeof(int));
dataTable.Columns.Add("Name", typeof(string));

using (var connection = new SqlConnection(_tbsConnection))
{
    await connection.OpenAsync();
    try
    {
        var cmd = new SqlCommand(sqlText, connection, transaction);
        var sqlFixture = cmd.Parameters.AddWithValue("@MyList", dataTable);
        sqlFixture.SqlDbType = SqlDbType.Structured;
        sqlFixture.TypeName = "InsertList";
        var rowsAffected = await cmd.ExecuteNonQueryAsync();
    }
}

How can I easily change this to allow bulk insert, without using datatables?

What did you end up doing to address this? I'm facing the same issue. — codeConcussion, Aug 24 '16 at 14:53
Try this library: https://github.com/borisdj/EFCore.BulkExtensions — borisdj, May 15 '17 at 11:11
Just migrate to .Net Core 2.0 - `Datatable` is supported there — Paval, Jul 24 '17 at 11:15

score 2 · Answer 1 · edited May 28 '17 at 09:00

2

Your sample code does not use SqlBulkCopy ("bulk insert") right now, so I'm not sure we're talking about the same things here; but anyway it looks as if .NET Core does support the WriteToServer(DbDataReader) (note link is for the full .NET framework, but should give you an idea) overloads. So you could use those instead, by wrapping your "data source" with a DbDataReader, i.e. have it implement it.

There are a couple of answers related to this on SO already. This might be one of particular interest.

edited May 28 '17 at 09:00

Nic

12,220
20
77
105

answered Jun 24 '16 at 12:18

Christian.K

47,778
10
99
143

Does SQLBulkCopy have the same flexibility when adding a list of items to the database? For example we use `INSERT INTO using SELECT with OUTPUT INTO` etc. – Nic Jul 10 '16 at 07:21
1

As alternative to custom DbDataReader implementation it is possible to use [RecordSetReader](https://github.com/nreco/data/blob/master/src/NReco.Data/RecordSetReader.cs) from [NReco.Data](https://github.com/nreco/data) library (supports .NET Core). – Vitaliy Fedorchenko Aug 18 '16 at 18:25

score 2 · Accepted Answer · answered Aug 01 '17 at 23:20

Datatables have been added to .Net Core 2.0 - Exploring DataTable and SqlDBAdapter in ASP.NET Core 2.0

public static DataTable ExecuteDataTable(SqlConnection conn, CommandType cmdType, string cmdText, SqlParameter[] cmdParms)
{
    DataTable dt = new DataTable();
    // just doing this cause dr.load fails
    dt.Columns.Add("CustomerID");
    dt.Columns.Add("CustomerName");
    SqlDataReader dr = ExecuteReader(conn, cmdType, cmdText, cmdParms);
    // as of now dr.Load throws a big nasty exception saying its not supported. wip.
    // dt.Load(dr);
    while (dr.Read())
    {
        dt.Rows.Add(dr[0], dr[1]);
    }
    return dt;
}

public static DataTable ExecuteDataTableSqlDA(SqlConnection conn, CommandType cmdType, string cmdText, SqlParameter[] cmdParms)
{
    System.Data.DataTable dt = new DataTable();
    System.Data.SqlClient.SqlDataAdapter da = new SqlDataAdapter(cmdText, conn);
    da.Fill(dt);
    return dt;
}

SqlClient Bulk Insert Without DataTable in .Net Core

2 Answers2