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