SqlDataAdapter, SqlBulkCopy and DataTable with NetTopologySuite.IO.SqlServerBytes

user_4275854 0
Viewed 266

I am trying to use SqlBulkCopy with .net core, but since I use a geometry column the following code requires Microsoft.SqlServer.Types which is not fully .net core compatible, especially on Linux.

using (SqlDataAdapter adapter = new SqlDataAdapter("SELECT TOP 0 * FROM " + tableName, sqlConnection))
{
    DataTable dt = new DataTable();
    adapter.Fill(dt);
    return dt;
}

Without the dependency the Fill() fails as it cannot find the type.

Normally I use NetTopologySuite.IO.SqlServerBytes, but in this case the magic happens somewhere in the SqlDataAdapter and I don't know how to overwrite it.

I tried to create the DataTable columns manually without using Fill(), but it seems whatever type I specify I later get an error in the SqlBulkCopy.

The given value of type XYZ from the data source cannot be converted to type udt of the specified target column

I tried with SqlBytes and byte[], but nothing seems to work.

Update 1: I got it working with a manually created DataTable with byte[] as the type for that column.

Still it would be nice to have a way using adapter.Fill(dt); or similar so that I do not have to manually list all the columns.

sql-server c#
1 Answers

    using (SqlDataAdapter da = new SqlDataAdapter("SELECT TOP 5 * FROM " + tableName, sqlConnection)
    
    {
        using (SqlCommandBuilder builder = new SqlCommandBuilder(da))
        {

            DataTable dt = new DataTable();
            DataSet dataSet = new DataSet();
            dt = dgPurchase.DataSource as DataTable;
            //here assign the data table
                da.Fill(dataSet, dt);
            
            da.UpdateCommand = builder.GetUpdateCommand(true);

            //da.InsertCommand = builder.GetInsertCommand(true);
            //If new row then open it
                    da.Update(dataSet, dt);

        }
     }

 // Can update though parameters .Follow the link
https://docs.microsoft.com/en-us/dotnet/framework/data/adonet/updating-data-sources-with-dataadapters

//Useing inline code .Follow the link
http://csharp.net-informations.com/dataadapter/updatecommand-sqlserver.htm

user_104601060
Related