帶有WellKnownText空間數據列的SqlBulkCopy DataTable

.net c# sqlbulkcopy sqlgeometry sql-server

我正在嘗試批量複製具有以下列的DataTable

  • “ID” - System.Int32
  • “Geom” - System.String

使用以下列進入SQL數據庫:

  • “Id” - int
  • “形狀” - geometry

誰能建議最好的方法來做到這一點?

一些測試代碼,如果有幫助......

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

我的原始帖子未能解釋執行上述操作會導致拋出以下異常。

InvalidOperationException:數據源中String類型的給定值無法轉換為指定目標列的類型udt。

我假設SqlBulkCopy不知道geometry列類型,因此不知道如何從string轉換為它。誰能證實這一點?

一般承認的答案

您的“Geom”列需要是SqlGeometry類型,而不是字符串。 Sql Server將期望插入的幾何列的用戶定義類型(UDT)。這是我會用的:

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

請注意,我們從您的字符串構造實際的SqlGeometry類型。批量插入將負責將其轉換為SqlServer將識別的二進制格式。

另外,我不確定為什麼要插入具有相同ID的多個記錄(樣本中的所有ID都是1)。

祝你好運!


熱門答案

如果desintation表具有與DataTable相同的列結構,則無需映射列。如果desintation表的結構與DataTable不同,則必須映射每列。

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

剛才意識到,你沒有為你的sql列使用varchar。在這種情況下,我認為最好的辦法是使用sp填充表,並使用數組綁定執行存儲過程。這是我使用Oracle做的一個例子。使用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();                       
    }

在我構建Insert語句的地方,您可以進行存儲過程調用,並根據需要進行參數化。



許可下: CC-BY-SA with attribution
不隸屬於 Stack Overflow
這個KB合法嗎? 是的,了解原因
許可下: CC-BY-SA with attribution
不隸屬於 Stack Overflow
這個KB合法嗎? 是的,了解原因