private void Init()
{
MapBindTrace.Source.TraceInformation("Trace started");
// Check files
ShapeFileHelper.CheckFiles(_shapeFile);
// REVERSE
SqlServerHelper.REVERSE_GEOMETRIES = null;
// construct Sql table name
_tableName = SqlServerModel.CleanSQLName(Path.GetFileNameWithoutExtension(_shapeFile));
// Init reader
using (ShapefileDataReader reader = new ShapefileDataReader(_shapeFile, GeometryFactory.Default))
{
// Get Shape info
_bounds = reader.ShapeHeader.Bounds;
_shapeType = reader.ShapeHeader.ShapeType;
_recordCount = reader.RecordCount;
_fields = new Dictionary <string, Type>();
foreach (var field in reader.DbaseHeader.Fields)
{
_fields.Add(field.Name, field.Type);
}
_idField = SqlServerModel.GenerateUniqueColName("ID", ShapeFileHelper.TranslateDbfTypesToSql(reader.DbaseHeader.Fields), _tableName);
_geomField = SqlServerModel.GenerateUniqueColName("geom", ShapeFileHelper.TranslateDbfTypesToSql(reader.DbaseHeader.Fields), _tableName);
}
}
/// <summary>
/// Returns dictionnary with key: ColName, value: sqltype
/// </summary>
/// <param name="fieldDescriptors"></param>
/// <returns></returns>
public static List <SqlColumnDescriptor> TranslateDbfTypesToSql(DbaseFieldDescriptor[] fieldDescriptors)
{
List <SqlColumnDescriptor> ret = new List <SqlColumnDescriptor>();
foreach (DbaseFieldDescriptor desc in fieldDescriptors)
{
ret.Add(new SqlColumnDescriptor(SqlServerModel.CleanSQLName(desc.Name), GetSqlType(desc), desc.Type));
}
return(ret);
}
public void ImportShapeFile_Direct(string connectionString,
string targetCoordSystem,
bool recreateTable,
enSpatialType spatialType,
int SRID,
string tableName,
string schema,
string IdColName,
string geomcolName,
List <string> fieldsToImport)
{
_worker = new BackgroundWorker();
_worker.WorkerSupportsCancellation = true;
_worker.WorkerReportsProgress = true;
_worker.RunWorkerCompleted += new RunWorkerCompletedEventHandler(_worker_RunWorkerCompleted);
_worker.ProgressChanged += new ProgressChangedEventHandler(_worker_ProgressChanged);
_worker.DoWork += new DoWorkEventHandler(delegate(object sender, DoWorkEventArgs e)
{
try
{
#region Work
TraceSource traceSource = new TraceSource("MapBind.IO");
int recordIndex = 1;
MapBindTrace.Source.TraceEvent(TraceEventType.Information, 1, "Worker started");
_worker.ReportProgress(0, "Starting...");
#region Init ICoordinateTransformation
_transform = null;
if (!string.IsNullOrWhiteSpace(targetCoordSystem))
{
//string v_targetCoordSys = "GEOGCS[\"WGS 84\",DATUM[\"WGS_1984\",SPHEROID[\"WGS 84\",6378137,298.257223563,AUTHORITY[\"EPSG\",\"7030\"]],AUTHORITY[\"EPSG\",\"6326\"]],PRIMEM[\"Greenwich\",0,AUTHORITY[\"EPSG\",\"8901\"]],UNIT[\"degree\",0.01745329251994328,AUTHORITY[\"EPSG\",\"9122\"]],AUTHORITY[\"EPSG\",\"4326\"]]";
ICoordinateSystem csSource = ProjNet.Converters.WellKnownText.CoordinateSystemWktReader.Parse(this.CoordinateSystem) as ICoordinateSystem;
ICoordinateSystem csTarget = ProjNet.Converters.WellKnownText.CoordinateSystemWktReader.Parse(targetCoordSystem) as ICoordinateSystem;
_transform = new CoordinateTransformationFactory().CreateFromCoordinateSystems(csSource, csTarget);
}
#endregion Init ICoordinateTransformation
using (SqlConnection db = new SqlConnection(connectionString))
{
db.Open();
SqlTransaction transaction = db.BeginTransaction(IsolationLevel.Serializable);
using (SqlBulkCopy bulk = new SqlBulkCopy(db, SqlBulkCopyOptions.Default, transaction))
{
using (ShapeFileReaderBulk shapeDataReader = new ShapeFileReaderBulk(_shapeFile, _transform, spatialType, SRID, Internal_RaiseError))
{
try
{
bulk.DestinationTableName = SqlServerModel.GenerateFullTableName(tableName, schema);
bulk.BulkCopyTimeout = 0;
bulk.NotifyAfter = 1;
bulk.SqlRowsCopied += (o, args) =>
{
recordIndex++;
if (_worker.CancellationPending)
{
args.Abort = true;
}
else
{
_worker.ReportProgress((int)((args.RowsCopied * 100f) / this.RecordCount), string.Format("Writing {0} records", args.RowsCopied));
}
};
MapBindTrace.Source.TraceEvent(TraceEventType.Information, 1, string.Format("Writing {0} records", 0));
_worker.ReportProgress(0, string.Format("Writing {0} records", 0));
#region Column mappings
List <DbaseFieldDescriptor> fieldsList = new List <DbaseFieldDescriptor>();
int idxSource = 0;
int idxDest = 1;
foreach (DbaseFieldDescriptor desc in shapeDataReader.DbaseHeader.Fields)
{
if (fieldsToImport.Contains(desc.Name))
{
bulk.ColumnMappings.Add(desc.Name, SqlServerModel.CleanSQLName(desc.Name));
fieldsList.Add(desc);
idxDest++;
}
idxSource++;
}
switch (spatialType)
{
case enSpatialType.geometry:
case enSpatialType.geography:
bulk.ColumnMappings.Add("geom", geomcolName);
break;
case enSpatialType.both:
bulk.ColumnMappings.Add("geom_geom", geomcolName + "_geom");
bulk.ColumnMappings.Add("geom_geog", geomcolName + "_geog");
break;
}
#endregion Column mappings
#region create table
List <SqlColumnDescriptor> sqlFields = ShapeFileHelper.TranslateDbfTypesToSql(fieldsList.ToArray());
MapBindTrace.Source.TraceEvent(TraceEventType.Information, 1, "Creating table " + tableName);
string sqlScriptCreateTable = SqlServerModel.GenerateCreateTableScript(tableName, schema, sqlFields, spatialType, recreateTable, geomcolName, IdColName);
DataTable dataTable = SqlServerModel.GenerateDataTable(tableName, sqlFields, spatialType, recreateTable, geomcolName, IdColName);
new SqlCommand(sqlScriptCreateTable, db, transaction).ExecuteNonQuery();
#endregion
bool bulkInError = false;
try
{
bulk.WriteToServer(shapeDataReader);
}
catch (OperationAbortedException)
{
bulkInError = true;
MapBindTrace.Source.TraceEvent(TraceEventType.Error, 1, "SqlBulkImport throw OperationAbortedException");
}
catch (Exception exBulk)
{
MapBindTrace.Source.TraceEvent(TraceEventType.Error, 1, "SqlBulkImport throw Exception" + exBulk.Message);
this.Raise_Error(new ShapeImportExceptionEventArgs(exBulk, true, shapeDataReader.DumpCurrentRecord(), shapeDataReader.Geometry, recordIndex));
bulkInError = true;
}
bulk.Close();
if (_worker.CancellationPending || bulkInError)
{
MapBindTrace.Source.TraceEvent(TraceEventType.Warning, 1, "Rolling back transaction");
transaction.Rollback();
}
else
{
#region Create spatial index
MapBindTrace.Source.TraceEvent(TraceEventType.Information, 1, "Creating spatial index...");
_worker.ReportProgress(100, "Creating index...");
// Create spatial index
Envelope bounds = ShapeFileHelper.ReprojectEnvelope(_transform, this.Bounds);
string sqlScriptCreateIndex = SqlServerModel.GenerateCreateSpatialIndexScript(tableName, schema, geomcolName, SqlServerHelper.GetBoundingBox(bounds), spatialType, enSpatialIndexGridDensity.MEDIUM);
SqlCommand v_createdIndexcmd = new SqlCommand(sqlScriptCreateIndex, db, transaction);
v_createdIndexcmd.CommandTimeout = 3600;
v_createdIndexcmd.ExecuteNonQuery();
#endregion
MapBindTrace.Source.TraceEvent(TraceEventType.Information, 1, "Commit transaction");
transaction.Commit();
}
}
catch (Exception ex)
{
MapBindTrace.Source.TraceEvent(TraceEventType.Error, 1, "Error : " + ex.Message);
MapBindTrace.Source.TraceEvent(TraceEventType.Warning, 1, "Rolling back transaction");
transaction.Rollback();
this.Raise_Error(new ShapeImportExceptionEventArgs(ex, true));
}
finally
{
MapBindTrace.Source.TraceEvent(TraceEventType.Verbose, 1, "SqlBulkCopy.Close()");
bulk.Close();
}
}
}
MapBindTrace.Source.TraceEvent(TraceEventType.Verbose, 1, "db.Close()");
db.Close();
}
#endregion
}
catch (Exception ex)
{
MapBindTrace.Source.TraceEvent(TraceEventType.Error, 1, "Unhandled exception : " + ex.Message);
this.Raise_Error(new ShapeImportExceptionEventArgs(ex, true));
}
});
Trace.CorrelationManager.StartLogicalOperation("ImportShapeFile_Direct Worker");
_worker.RunWorkerAsync();
Trace.CorrelationManager.StartLogicalOperation("ImportShapeFile_Direct Worker");
}