SqlBulkCopy DataTable with WellKnownText spatial data column

Question

I'm trying to bulk copy a DataTable which has the following columns:

• "ID" - System.Int32
• "Geom" - System.String

Into a SQL database with the following columns:

• "Id" - int
• "Shape" - geometry

Can anyone advise on the best way to do this?

Some test code if it helps...

DataTable dataTable = new DataTable();
dataTable.Columns.Add("ID", typeof(Int32));
dataTable.Columns.Add("Geom", typeof(String));

dataTable.Rows.Add(1, "POINT('20,20')");
dataTable.Rows.Add(1, "POINT('40,25')");
dataTable.Rows.Add(1, "POINT('60,30')");

SqlBulkCopy sqlBulkCopy = new SqlBulkCopy(connection);
sqlBulkCopy.DestinationTableName = "MySpatialDataTable";
sqlBulkCopy.WriteToServer(dataTable);

---

My original post failed to explain that performing the above causes the following Exception to be thrown.

InvalidOperationException: The given value of type String from the data source cannot be converted to type udt of the specified target column.

I'm assuming from this that SqlBulkCopy doesn't know about the geometry column type and therefore doesn't know how to convert to it from a string. Can anyone confirm this?

Sabba

Answer 1

Your "Geom" column needs to be type SqlGeometry, not a string. Sql Server will expect a user defined type (UDT) for a geometry column on an insert. This is what I would use:

DataTable dataTable = new DataTable();
dataTable.Columns.Add("ID", typeof(Int32));
dataTable.Columns.Add("Geom", typeof(SqlGeometry));

dataTable.Rows.Add(1, SqlGeometry.STGeomFromText("POINT('20,20')"));
dataTable.Rows.Add(2, SqlGeometry.STGeomFromText("POINT('40,25')"));
dataTable.Rows.Add(3, SqlGeometry.STGeomFromText("POINT('60,30')"));

SqlBulkCopy sqlBulkCopy = new SqlBulkCopy(connection);
sqlBulkCopy.DestinationTableName = "MySpatialDataTable";
sqlBulkCopy.WriteToServer(dataTable);

Note that we construct the actual SqlGeometry type from your string. The bulk insert will take care of converting it to a binary format that SqlServer will recognize.

Also, I'm not sure why you want to insert multiple records with the same ID (you have all ID 1 in your sample).

Good luck!

Narration

What DLL does SqlGeometry exist in? – Laroy

Microsoft.SqlServer.Types. See: msdn.microsoft.com/en-us/library/…. Most people find it somewhere in their Sql Server install SDK. It may be the GAC already too. And I think there are some NuGet packages for it. Be careful, it has some native requirements, so make sure you're pulling either x32 or x64 libraries correctly. – Narration

NuGet is a good way to go: nuget.org/packages/Microsoft.SqlServer.Types – Laroy

To make this work well, you may want to manually set column orders like this: https://mcmap.net/q/1327596/-sql-bulk-copy-quot-the-given-value-of-type-string-from-the-data-source-cannot-be-converted-to-type-datetime-of-the-specified-target-column-quot-using-asp-net – Laroy

Answer 2

If the desintation table has the same column structure as your DataTable, you do not need to map the columns. If the desintation table differs in structure from your DataTable, than you have to map each column.

public void BulkLoadToTemp(DataTable dt, String tableName, int bulkLoadBatchSize)
    {
        using (SqlBulkCopy bulkCopy = new SqlBulkCopy(this._connection))
        {
            bulkCopy.DestinationTableName = tableName;
            bulkCopy.BulkCopyTimeout = 120;
            bulkCopy.BatchSize = bulkLoadBatchSize;
            bulkCopy.WriteToServer(dt);
            bulkCopy.Close();
        }            
    }

Just realized, that you are not using a varchar for your sql column. In this case, I think best bet is to use a sp to populate the table, and execute the stored procedure using array binding. Here's an example that I did using Oracle. You can modify it and simplify it a lot more when using sql server.

public void BulkLoadWithArrayBinding(System.Data.DataTable dt)
    {
        StringBuilder sb = new StringBuilder();
        List<OracleParameter> parameters = new List<OracleParameter>(dt.Columns.Count);

        OracleCommand cmd = new OracleCommand();
        cmd.Connection = conn;

        sb.Append("INSERT INTO \"" + dt.TableName + "\" (");
        foreach (DataColumn dc in dt.Columns)
        {
            sb.Append("\"" + dc.ColumnName.ToUpper() + "\"");
            if (dc.Ordinal < dt.Columns.Count - 1)
                sb.AppendLine(",");
        }
        sb.Append(") VALUES(");
        foreach (DataColumn dc in dt.Columns)
        {
            string parameterName = dc.ColumnName.ToUpper();

            sb.Append(":" + parameterName);
            if (dc.Ordinal < dt.Columns.Count - 1)
                sb.AppendLine(",");

            OracleString[] sArray = null;
            OracleDate[] dArray = null;
            OracleDecimal[] dbArray = null;

            OracleParameter p = null;
            if (dc.DataType.Name == "String")
            {
                sArray = new OracleString[dt.Rows.Count];
                for (int i = 0; i < dt.Rows.Count; i++)
                {
                    if (dt.Rows[i][dc.Ordinal] != DBNull.Value)
                        sArray[i] = dt.Rows[i][dc.Ordinal].ToString();
                    else
                        sArray[i] = OracleString.Null;
                }

                p = new OracleParameter(parameterName,OracleDbType.Varchar2, dt.Rows.Count, ParameterDirection.Input);
                p.Size = sArray.Length;
                p.Value = sArray;
            }
            else if (dc.DataType.Name == "DateTime")
            {
                dArray = new OracleDate[dt.Rows.Count];
                for (int i = 0; i < dt.Rows.Count; i++)
                {
                    if (dt.Rows[i][dc.Ordinal] != DBNull.Value)
                        try
                        {
                            dArray[i] = (OracleDate)Convert.ToDateTime(dt.Rows[i][dc.Ordinal]);
                        }
                        catch
                        {
                            object o = dt.Rows[i][dc.Ordinal];
                            dArray[i] = OracleDate.Null;
                        }
                    else
                    {
                        dArray[i] = OracleDate.Null;
                    }
                }

                p = new OracleParameter(parameterName,OracleDbType.Date, dt.Rows.Count, ParameterDirection.Input);
                p.Size = dArray.Length;
                p.Value = dArray;
            }
            else if (dc.DataType.Name == "Double")
            {
                dbArray = new OracleDecimal[dt.Rows.Count]; ;
                for (int i = 0; i < dt.Rows.Count; i++)
                {
                    if (dt.Rows[i][dc.Ordinal] != DBNull.Value)
                        dbArray[i] = Convert.ToDecimal(dt.Rows[i][dc.Ordinal]);
                    else
                        dbArray[i] = OracleDecimal.Null;
                }

                p = new OracleParameter(parameterName, OracleDbType.Decimal, dt.Rows.Count, ParameterDirection.Input);
                p.Value = dbArray;
            }
            else if (dc.DataType.Name == "Boolean")
            {
                dbArray = new OracleDecimal[dt.Rows.Count]; ;
                for (int i = 0; i < dt.Rows.Count; i++)
                {
                    if (dt.Rows[i][dc.Ordinal] != DBNull.Value)
                        dbArray[i] = Convert.ToDecimal(dt.Rows[i][dc.Ordinal]);
                    else
                        dbArray[i] = OracleDecimal.Null;
                }

                p = new OracleParameter(parameterName, OracleDbType.Decimal, dt.Rows.Count, ParameterDirection.Input);
                p.Value = dbArray;
            }

            cmd.Parameters.Add(p);
        }

        sb.AppendLine(")");

        cmd.CommandText = sb.ToString();
        cmd.CommandType = CommandType.Text;
        cmd.ArrayBindCount = dt.Rows.Count;
        cmd.BindByName = true;
        cmd.AddToStatementCache = true;

        cmd.ExecuteNonQuery();

        foreach (OracleParameter p in cmd.Parameters)
        {                
            p.Dispose();
        }

        cmd.Dispose();                       
    }

Where I build up an Insert statement, you can put a stored procedure call, and parameterize as necessary.

Erin