Я пытаюсь навалом скопировать 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
. Может ли кто-нибудь подтвердить это?
Столбец «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 в вашем примере).
Удачи!
Если таблица дескрипции имеет ту же структуру столбцов, что и ваш 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();
}
}
Просто понял, что вы не используете 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, вы можете поместить вызов хранимой процедуры и, при необходимости, параметризовать.