SqlBulkCopy DataTable со столбцом пространственных данных WellKnownText
Я пытаюсь скопировать DataTable который имеет следующие столбцы:
- "Я БЫ" -
System.Int32 - "Геом" -
System.String
В базу данных SQL со следующими столбцами:
- "Я бы" -
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, Кто-нибудь может это подтвердить?
2 ответа
Ваш столбец "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 1 в вашем образце).
Удачи!
Если таблица описания имеет ту же структуру столбцов, что и ваша таблица данных, вам не нужно отображать столбцы. Если таблица назначения отличается по структуре от вашей таблицы данных, то вы должны отобразить каждый столбец.
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();
}
}
Просто понял, что вы не используете varchar для вашего столбца sql. В этом случае, я думаю, лучше всего использовать sp для заполнения таблицы и выполнить хранимую процедуру, используя привязку массива. Вот пример, который я сделал с помощью Oracle. Вы можете изменить его и упростить гораздо больше при использовании сервера SQL.
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, вы можете поместить вызов хранимой процедуры и параметризоваться по мере необходимости.