CODEWITHSUNDEEP
c#.netdatatableasp.net-core.net-core
Nic
Nic

Reputation: 12875

SqlClient Bulk Insert Without DataTable in .Net Core

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?

Upvotes: 1

Views: 4696

Answers (2)

Nic
Nic

Reputation: 12875

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

Upvotes: 2

Christian.K
Christian.K

Reputation: 49280

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.

Upvotes: 2

Related Questions