public String ImportFromShapefile(String shapeFilePath, String connectionString)
{
String shapeFileName = Path.GetFileNameWithoutExtension(shapeFilePath);
//Define destination table
DataTable bulkSqlTable = new DataTable();
var newNames = _textBoxPanel.Children.OfType <TextBox>().ToList();
int rowNo = 0;
foreach (DataColumn col in _table.Columns)
{
bulkSqlTable.Columns.Add(newNames[rowNo++].Text, col.DataType);
}
if (_geometry.IsChecked == true)
{
bulkSqlTable.Columns.Add("Geom", typeof(SqlGeometry));
}
else
{
bulkSqlTable.Columns.Add("Geom", typeof(SqlGeography));
}
//Populate destination table
foreach (FeatureDataRow row in _table)
{
rowNo = 0;
DataRow newTableRow = bulkSqlTable.NewRow();
foreach (DataColumn col in _table.Columns)
{
newTableRow[newNames[rowNo++].Text] = row[col.ColumnName];
}
int srid = row.Geometry.SRID;
if (_UseSRID.IsChecked == true)
{
int tempSRID = 0;
if (int.TryParse(_sRID.Text, out tempSRID))
{
srid = tempSRID;
}
}
if (_geometry.IsChecked == true)
{
_sRID.Text = srid.ToString();
newTableRow["Geom"] = SqlGeometry.STGeomFromWKB(new SqlBytes(row.Geometry.AsBinary()), srid);
}
else
{
if (srid == 0)
{
srid = 4623;
}
_sRID.Text = srid.ToString();
newTableRow["Geom"] = SqlGeography.STGeomFromWKB(new SqlBytes(row.Geometry.AsBinary()), srid);
}
bulkSqlTable.Rows.Add(newTableRow);
}
try
{
bulkSqlTable.PrimaryKey = new DataColumn[] { bulkSqlTable.Columns[_primaryKey.Text] };
if (bulkSqlTable.Columns[_primaryKey.Text].DataType == typeof(String))
{
bulkSqlTable.Columns[_primaryKey.Text].MaxLength = 400;
}
}
catch
{
_status.Text = "Wrong primary key, bad column choice";
return(String.Empty);
}
//Save destination table
using (SqlConnection connection = new SqlConnection(connectionString))
{
SqlTransaction transaction = null;
connection.Open();
try
{
transaction = connection.BeginTransaction();
SqlTableCreator tableCreator = new SqlTableCreator(connection, transaction);
tableCreator.DestinationTableName = _tableName.Text;
tableCreator.CreateFromDataTable(bulkSqlTable);
using (var sqlBulkCopy = new SqlBulkCopy(connection, SqlBulkCopyOptions.TableLock, transaction))
{
sqlBulkCopy.DestinationTableName = _tableName.Text;
sqlBulkCopy.BatchSize = 10000;
sqlBulkCopy.WriteToServer(bulkSqlTable);
}
transaction.Commit();
}
catch (Exception e)
{
if (transaction != null)
{
transaction.Rollback();
}
_status.Text = "Bad table name. Probabbly already exists. Check the primary key too.";
return(String.Empty);
}
finally
{
connection.Close();
SqlConnection.ClearPool(connection);
}
}
return(shapeFileName);
}
public String ImportFromShapefile(String shapeFilePath, String connectionString)
{
String shapeFileName = Path.GetFileNameWithoutExtension(shapeFilePath);
//Define destination table
DataTable bulkSqlTable = new DataTable();
var newNames = _textBoxPanel.Children.OfType<TextBox>().ToList();
int rowNo = 0;
foreach (DataColumn col in _table.Columns)
{
bulkSqlTable.Columns.Add(newNames[rowNo++].Text, col.DataType);
}
bulkSqlTable.Columns.Add("Geom", typeof(SqlGeometry));
//Populate destination table
foreach (FeatureDataRow row in _table)
{
rowNo = 0;
DataRow newTableRow = bulkSqlTable.NewRow();
foreach (DataColumn col in _table.Columns)
{
newTableRow[newNames[rowNo++].Text] = row[col.ColumnName];
}
newTableRow["Geom"] = SqlGeometry.STGeomFromWKB(new SqlBytes(row.Geometry.AsBinary()), row.Geometry.SRID);
bulkSqlTable.Rows.Add(newTableRow);
}
try
{
bulkSqlTable.PrimaryKey = new DataColumn[] { bulkSqlTable.Columns[_primaryKey.Text] };
if (bulkSqlTable.Columns[_primaryKey.Text].DataType == typeof(String)) bulkSqlTable.Columns[_primaryKey.Text].MaxLength = 400;
}
catch
{
_status.Text = "Wrong primary key, bad column choice";
return String.Empty;
}
//Save destination table
using (SqlConnection connection = new SqlConnection(connectionString))
{
SqlTransaction transaction = null;
connection.Open();
try
{
transaction = connection.BeginTransaction();
SqlTableCreator tableCreator = new SqlTableCreator(connection, transaction);
tableCreator.DestinationTableName = _tableName.Text;
tableCreator.CreateFromDataTable(bulkSqlTable);
using (var sqlBulkCopy = new SqlBulkCopy(connection, SqlBulkCopyOptions.TableLock, transaction))
{
sqlBulkCopy.DestinationTableName = _tableName.Text;
sqlBulkCopy.BatchSize = 10000;
sqlBulkCopy.WriteToServer(bulkSqlTable);
}
transaction.Commit();
}
catch (Exception e)
{
if (transaction != null)
{
transaction.Rollback();
}
_status.Text = "Bad table name. Probabbly already exists. Check the primary key too.";
return String.Empty;
}
finally
{
connection.Close();
SqlConnection.ClearPool(connection);
}
}
return shapeFileName;
}
