public void ManageDatabaseServiceInputViewModelCloneRegion_ReturnsNull() { //------------Setup for test-------------------------- var mod = new SqlServerModel(); var act = new DsfSqlServerDatabaseActivity(); var sqlServer = new SqlServerDatabaseDesignerViewModel(ModelItemUtils.CreateModelItem(act), mod, new SynchronousAsyncWorker(), new ViewPropertyBuilder()); var inputview = new ManageDatabaseServiceInputViewModel(sqlServer, mod); inputview.Model = new DatabaseService(); //------------Execute Test--------------------------- var clone = inputview.CloneRegion(); //------------Assert Results------------------------- Assert.AreEqual(inputview, clone); }
public void ManageDatabaseServiceInputViewModel_RestoreRegion_DoesNothing() { //------------Setup for test-------------------------- var mod = new SqlServerModel(); var act = new DsfSqlServerDatabaseActivity(); var sqlServer = new SqlServerDatabaseDesignerViewModel(ModelItemUtils.CreateModelItem(act), mod, new SynchronousAsyncWorker(), new ViewPropertyBuilder()); var inputview = new ManageDatabaseServiceInputViewModel(sqlServer, mod); inputview.Model = new DatabaseService(); //------------Execute Test--------------------------- inputview.RestoreRegion(null); //------------Assert Results------------------------- Assert.IsTrue(true, "Error RestoreRegion should do nothing"); }
public void ManageDatabaseServiceInputViewModel_PropertyChangedHandler() { //------------Setup for test-------------------------- var mod = new SqlServerModel(); var act = new DsfSqlServerDatabaseActivity(); var called = false; var sqlServer = new SqlServerDatabaseDesignerViewModel(ModelItemUtils.CreateModelItem(act), mod, new SynchronousAsyncWorker(), new ViewPropertyBuilder()); var inputview = new ManageDatabaseServiceInputViewModel(sqlServer, mod); inputview.PropertyChanged += (sender, args) => called = true; inputview.Model = new DatabaseService(); //------------Execute Test--------------------------- inputview.TryExecuteTest(); //------------Assert Results------------------------- Assert.IsTrue(called); }
public static void LoadCache(string table, string connectionString) { lock (_syncLock) { try { if (!_loadedTables.ContainsKey(table)) { _loadedTables[table] = false; string strQuery = SqlServerModel.GenerateGetAllGeomAndIdScript(table); using (SqlConnection con = new SqlConnection(connectionString)) { con.Open(); using (var cmd = new SqlCommand(strQuery, con)) { cmd.CommandType = System.Data.CommandType.Text; DateTime start = DateTime.Now; using (var rdr = cmd.ExecuteReader()) { while (rdr.Read()) { int id = (int)rdr[0]; SqlGeometry geom = (SqlGeometry)rdr[1]; //SqlGeography geog = (SqlGeography)rdr[2]; //AddGeomToCache(table, id, geom, geog); AddGeomToCache(table, id, geom); } Trace.WriteLine("cache loaded in: " + (DateTime.Now - start).TotalMilliseconds + " ms"); } } } _loadedTables[table] = true; } } catch (Exception) { throw; } } }
public void ManageDatabaseServiceInputViewModelOkAction_Exception() { //------------Setup for test-------------------------- var mod = new SqlServerModel(); mod.HasRecError = true; var act = new DsfSqlServerDatabaseActivity(); var sqlServer = new SqlServerDatabaseDesignerViewModel(ModelItemUtils.CreateModelItem(act), mod, new SynchronousAsyncWorker(), new ViewPropertyBuilder()); var inputview = new ManageDatabaseServiceInputViewModel(sqlServer, mod); sqlServer.OutputsRegion.Outputs = null; //------------Execute Test--------------------------- inputview.ExecuteOk(); //------------Assert Results------------------------- Assert.IsTrue(inputview.Errors.Count == 1); }
public GeoJsonResult GetGeoJsonData(BingTileQuery query) { int tileX; int tileY; int zoom; int nwX; int nwY; double nwLat; double nwLon; double seLat; double seLon; BingMapsTileSystem.QuadKeyToTileXY(query.quadKey, out tileX, out tileY, out zoom); BingMapsTileSystem.TileXYToPixelXY(tileX, tileY, out nwX, out nwY); BingMapsTileSystem.PixelXYToLatLong(nwX, nwY, zoom, out nwLat, out nwLon); BingMapsTileSystem.PixelXYToLatLong(nwX + 256, nwY + 256, zoom, out seLat, out seLon); double res = BingMapsTileSystem.GroundResolution(seLat + (nwLat - seLat) / 2d, zoom); int numDigits = BingMapsTileSystem.UsefulDigits(res); object bbox = null; if (useGeography) { bbox = SqlServerModel.GeograhyFromBoundingBoxNwSe(nwLat, nwLon, seLat, seLon, 4326); } else { bbox = SqlServerModel.GeometryFromBoundingBoxNwSe(nwLat, nwLon, seLat, seLon, 4326); } return(GetGeoJsonData( new BoundingBoxQuery() { useGeography = useGeography, Box = bbox, Resolution = res, NumDigits = numDigits, Tables = query.Tables } )); }
public void ManageDatabaseServiceInputViewModel_Ctor() { var mod = new SqlServerModel(); var act = new DsfSqlServerDatabaseActivity { SourceId = mod.Sources[0].Id, Outputs = new List <IServiceOutputMapping> { new ServiceOutputMapping("a", "b", "c"), new ServiceOutputMapping("d", "e", "f") }, ServiceName = "dsfBob" }; var sqlServer = new SqlServerDatabaseDesignerViewModel(ModelItemUtils.CreateModelItem(act), mod, new SynchronousAsyncWorker(), new ViewPropertyBuilder()); //------------Assert Results------------------------- var vm = new ManageDatabaseServiceInputViewModel(sqlServer, mod); Assert.IsNotNull(vm.CloseCommand); Assert.IsNotNull(vm.CloseCommand); }
public void ManageDatabaseServiceInputViewModel_TestActionSetSourceAndTestClickClose() { //------------Setup for test-------------------------- var mod = new SqlServerModel(); var act = new DsfSqlServerDatabaseActivity(); var sqlServer = new SqlServerDatabaseDesignerViewModel(ModelItemUtils.CreateModelItem(act), mod, new SynchronousAsyncWorker(), new ViewPropertyBuilder()); var inputview = new ManageDatabaseServiceInputViewModel(sqlServer, mod); inputview.Model = new DatabaseService(); inputview.ExecuteClose(); //------------Execute Ok--------------------------- Assert.IsNull(inputview.OutputArea.Outputs); Assert.IsTrue(sqlServer.SourceRegion.IsEnabled); Assert.IsFalse(sqlServer.OutputsRegion.IsEnabled); Assert.IsTrue(sqlServer.InputArea.IsEnabled); Assert.IsTrue(sqlServer.ErrorRegion.IsEnabled); Assert.IsFalse(sqlServer.ManageServiceInputViewModel.InputArea.IsEnabled); //------------Assert Results------------------------- }
public void ManageDatabaseServiceInputViewModel_TestActionSetSourceAndTestClickOk() { //------------Setup for test-------------------------- var mod = new SqlServerModel(); var act = new DsfSqlServerDatabaseActivity(); var sqlServer = new SqlServerDatabaseDesignerViewModel(ModelItemUtils.CreateModelItem(act), mod, new SynchronousAsyncWorker(), new ViewPropertyBuilder()); var inputview = new ManageDatabaseServiceInputViewModel(sqlServer, mod); inputview.Model = new DatabaseService() { Source = new DbSourceDefinition(), Action = new DbAction() { Inputs = new List <IServiceInput>(), Name = "bob" }, }; inputview.TryExecuteTest(); //------------Execute Test--------------------------- Assert.IsTrue(inputview.TestPassed); Assert.IsFalse(inputview.TestFailed); Assert.AreEqual(string.Empty, inputview.TestMessage); Assert.IsFalse(inputview.ShowTestMessage); inputview.ExecuteOk(); //------------Execute Ok--------------------------- Assert.IsTrue(sqlServer.SourceRegion.IsEnabled); Assert.IsTrue(sqlServer.InputArea.IsEnabled); Assert.IsTrue(sqlServer.OutputsRegion.IsEnabled); Assert.IsTrue(sqlServer.ErrorRegion.IsEnabled); Assert.IsFalse(sqlServer.ManageServiceInputViewModel.InputArea.IsEnabled); Assert.IsFalse(inputview.TestPassed); Assert.IsFalse(inputview.TestFailed); Assert.AreEqual(string.Empty, inputview.TestMessage); Assert.IsFalse(inputview.ShowTestMessage); //------------Assert Results------------------------- }
private void ProcessGeometry <Toutput, Tpoint>(GeometryWriterBase <Toutput, Tpoint> writer, double reduceTolerance, double pixelRadiansAreaXY, ref int numPixels, ref SqlGeometry geom, double geomArea) { bool geomIsPixel = geomArea > 0 && geomArea <= pixelRadiansAreaXY; if (!geomIsPixel && _geometryReduce) { geom = geom.Reduce(reduceTolerance); } if (_geometryRemoveArtefacts) { geom = SqlServerModel.DoRemoveArtefacts(geom); } if (geomIsPixel) { numPixels++; writer.WritePoint(geom.STPointN(1)); } else { writer.WriteGeometry(geom); } }
static void Main(string[] args) { var myEntity = new MyEntity { Name = "Test Name" }; using (var model = new SqliteModel()) { model.MyEntities.Add(myEntity); model.SaveChanges(); myEntity = model.MyEntities.First(); Console.WriteLine($"Sqlite Data: myEntity[Id:{myEntity.Id},Name:{myEntity.Name}]"); } using (var model = new SqlServerModel()) { model.MyEntities.Add(myEntity); model.SaveChanges(); myEntity = model.MyEntities.First(); Console.WriteLine($"SqlServer Data: myEntity[Id:{myEntity.Id},Name:{myEntity.Name}]"); } Console.WriteLine("Test Completed!"); Console.ReadKey(); }
public void ManageDatabaseServiceInputViewModel_TestActionSetSourceAndReturnNoDataMessage() { //------------Setup for test-------------------------- var mod = new SqlServerModel(); mod.ReturnsNoColumns = true; var act = new DsfSqlServerDatabaseActivity(); var sqlServer = new SqlServerDatabaseDesignerViewModel(ModelItemUtils.CreateModelItem(act), mod, new SynchronousAsyncWorker(), new ViewPropertyBuilder()); var inputview = new ManageDatabaseServiceInputViewModel(sqlServer, mod); inputview.Model = new DatabaseService() { Source = new DbSourceDefinition(), Action = new DbAction() { Inputs = new List <IServiceInput>(), Name = "" }, }; inputview.TryExecuteTest(); Assert.IsTrue(inputview.TestPassed); Assert.IsFalse(inputview.TestFailed); Assert.AreEqual("No data returned. ", inputview.TestMessage); Assert.IsTrue(inputview.ShowTestMessage); }
public IActionResult Index(SqlServerModel model) { var cu = ControllerUtils.From(this); cu.PersistInput("ConnectionString", model, SqlServerModel.Default.ConnectionString); cu.PersistInput("Sql", model, SqlServerModel.Default.Sql); if (!model.SkipSql) { try { var sql = model.Sql.Trim(); if (Regex.IsMatch(sql.ToUpper(), "DROP\\s+TABLE\\s+")) { throw new NotSupportedException("Cannot SQL contains drop table command."); } if (Regex.IsMatch(sql.ToUpper(), "DELETE\\s+FROM\\s+")) { throw new NotSupportedException("Cannot SQL contains delete command."); } if (Regex.IsMatch(sql.ToUpper(), "UPDATE\\s+.+SET\\s.+")) { throw new NotSupportedException("Cannot SQL contains update command."); } if (Regex.IsMatch(sql.ToUpper(), "INSERT\\s+INTO\\s+")) { throw new NotSupportedException("Cannot SQL contains insert command."); } if (sql.StartsWith("SELECT ", StringComparison.CurrentCultureIgnoreCase)) { // remove input TOP(*) word var mstr = Regex.Match(sql, "\\s+[Tt][Oo][Pp]\\s*\\(\\d+\\)\\s+"); if (mstr.Success) { sql = sql.Replace(mstr.Value, " "); } sql = "SELECT TOP(20) " + StrUtil.MidSkip(sql, "SELECT\\s+"); } model.LatestSql = sql; using var con = new SqlConnection(model.ConnectionString); con.Open(); using var sqlcommand = new SqlCommand(sql, con); using var row = sqlcommand.ExecuteReader(); var cs = row.GetColumnSchema(); model.Table = new List <List <string> >(); model.Table.Add(cs.Select(a => a.ColumnName).ToList()); model.Table.Add(cs.Select(a => $"<{a.DataTypeName}>").ToList()); while (row.Read()) { var r = new List <string>(); for (var col = 0; col < row.FieldCount; col++) { r.Add(Tono.DbUtil.ToString(row[col], "(null)")); } model.Table.Add(r); if (model.Table.Count > 22) { break; } } } catch (Exception ex) { model.ErrorMessage = ex.Message; } } if (model.Table == null) { model.Table = new List <List <string> >(); } while (model.Table.Count < 2) { model.Table.Add(new List <string>()); } model.SkipSql = false; return(View(model)); }
private Bitmap GetBenchImageGeneric(BoundingBoxQuery query, Metrics metrics, string tableName, bool useCache) { Bitmap bmp = new Bitmap(query.Width, query.Height); try { string strQuery = null; if (useCache) { strQuery = SqlServerModel.GenerateGetGeomIdInBBoxScript(tableName, query.BBox); } else { strQuery = SqlServerModel.GenerateGetGeomInBBoxScript(tableName, query.BBox); } SqlGeometry bboxGeom = SqlServerModel.GeometryFromBoundingBox(query.BBox); //start = DateTime.Now; //IList<int> resultsWithIndex = _spatialIndex.Query(new Envelope(query.BBox.minX, query.BBox.maxX, query.BBox.minY, query.BBox.maxY)); //foreach (int id in resultsWithIndex) //{ // SqlGeometry geom = GetGeomFromCache(tableName, id); //} //metrics.TaskTimesMilliseconds.Add(string.Format("quadTree {0} items", resultsWithIndex.Count), (int)(DateTime.Now - start).TotalMilliseconds); metrics.Start("STR"); IList <int> resultsWithIndexSTR = GeometryCache.Query(tableName, new Envelope(query.BBox.minX, query.BBox.maxX, query.BBox.minY, query.BBox.maxY)); foreach (int id in resultsWithIndexSTR) { SqlGeometry geom = GeometryCache.GetGeometry(tableName, id); } metrics.Stop("STR"); Trace.WriteLine(string.Format("STR {0} items", resultsWithIndexSTR.Count)); //List<SqlGeometry> vlist = ImageryDataService._geomCacheByTableThenId[tableName].Values.Where(g => g.STIntersects(bboxGeom).Value).ToList(); metrics.Start("DB"); int nbRecords = 0; using (var conn = GetOpenConnection()) { using (var cmd = new SqlCommand(strQuery, conn)) { cmd.CommandType = System.Data.CommandType.Text; using (var rdr = cmd.ExecuteReader(System.Data.CommandBehavior.SequentialAccess)) { while (rdr.Read()) { int id = rdr.GetSqlInt32(0).Value; SqlGeometry geom = GeometryCache.GetGeometry(tableName, id); nbRecords++; } rdr.Close(); } } } metrics.Stop("DB"); Trace.WriteLine(string.Format("DB {0} items", nbRecords)); } catch (Exception) { throw; } return(bmp); }
private Bitmap LoadTileFromDisc(int tileX, int tileY, int zoom, string tableName) { RasterFileSystem rasterFS = new RasterFileSystem(SqlServerModel.GetSqlDatabaseName(this.GetConnectionString()), tableName); return(rasterFS.GetTileBitmap(zoom, tileX, tileY)); }
public Toutput GetObjectGeneric_FromCacheBBox <Toutput, Tpoint>(BoundingBoxQuery query, Metrics metrics, string tableName, GeometryWriterBase <Toutput, Tpoint> writer) { Toutput objOut = default(Toutput); metrics.Start("Init"); try { double reduceTolerance = Math.Min(query.BBox.Width / query.Width, query.BBox.Height / query.Height); // degrees per pixel / 2 double reduceToleranceMeters = reduceTolerance * 6378137; //double pixelArea = Math.Pow(BingMapsTileSystem.GroundResolution(query.BBox.maxY, query.ZoomLevel), 2); // mapResolution * mapResolution double pixelRadiansAreaXY = ((query.BBox.maxX - query.BBox.minX) / query.Width) * ((query.BBox.maxY - query.BBox.minY) / query.Height); int numPixels = 0; #region Get data from cache or open DB if (UseInMemoryCache) { #region From Cache #region Preload cache metrics.Start("Cache"); if (!GeometryCache.IsCacheLoaded(query.Tables()[0])) { while (!GeometryCache.IsCacheLoaded(query.Tables()[0])) { GeometryCache.LoadCache(query.Tables()[0], this.GetConnectionString()); Trace.WriteLine(string.Format("Thread {0} waiting for cache...", System.Threading.Thread.CurrentThread.ManagedThreadId)); } } metrics.Stop("Cache"); #endregion SqlGeometry bboxGeom = SqlServerModel.GeometryFromBoundingBox(query.BBox); IList <int> resultsWithIndexSTR = GeometryCache.Query(tableName, new Envelope(query.BBox.minX, query.BBox.maxX, query.BBox.minY, query.BBox.maxY)); if (resultsWithIndexSTR.Count > 0) { foreach (int id in resultsWithIndexSTR) { SqlGeometry geom = GeometryCache.GetGeometry(tableName, id); double geomArea = GeometryCache.GetGeometryArea(tableName, id); bool geomIsPixel = geomArea > 0 && geomArea <= pixelRadiansAreaXY; metrics.Start("Process"); ProcessGeometry <Toutput, Tpoint>(writer, reduceTolerance, pixelRadiansAreaXY, ref numPixels, ref geom, geomArea); metrics.Stop("Process"); } metrics.Start("GetOutput"); objOut = writer.GetOutput(); metrics.Stop("GetOutput"); } #endregion } else { #region Get data from SQL DB using (SqlConnection conn = GetOpenConnection()) { string strQuery = SqlServerModel.GenerateGetGeomInBBoxScript(tableName, query.BBox); using (var cmd = new SqlCommand(strQuery, conn)) { cmd.CommandType = System.Data.CommandType.Text; using (var rdr = cmd.ExecuteReader(System.Data.CommandBehavior.SequentialAccess)) { if (rdr.HasRows) { while (rdr.Read()) { metrics.Start("Area"); SqlGeometry geom = SqlGeometry.STGeomFromWKB(rdr.GetSqlBytes(0), 4326); double geomArea = geom.STArea().Value; // optimize this by having area field in DB metrics.Stop("Area"); metrics.Start("Process"); ProcessGeometry <Toutput, Tpoint>(writer, reduceTolerance, pixelRadiansAreaXY, ref numPixels, ref geom, geomArea); metrics.Stop("Process"); } metrics.Start("GetOutput"); objOut = writer.GetOutput(); metrics.Stop("GetOutput"); } rdr.Close(); } } } #endregion } #endregion } catch (Exception) { throw; } return(objOut); }
private Models.GeoJson.FeatureCollection GetGeoJsonGeneric(SqlGeometry box, double resolution, int numDigits, string tableName) { if (resolution == 0) { resolution = 1500d; } if (numDigits == 0) { numDigits = 5; } var features = new List <Models.GeoJson.Feature>(); try { using (var conn = GetOpenConnection()) { using (var cmd = new SqlCommand("SL_" + tableName + "_InBounds_geom", conn)) { cmd.CommandType = System.Data.CommandType.StoredProcedure; cmd.Parameters.Add(new SqlParameter("@bbox", System.Data.SqlDbType.Udt) { UdtTypeName = "geometry", Value = box }); if (_geometryReduce) { cmd.Parameters.AddWithValue("@res", (float)resolution / 2); } else { cmd.Parameters.AddWithValue("@res", DBNull.Value); } using (var rdr = cmd.ExecuteReader()) { while (rdr.Read()) { // get fields object outGeometry = null; Dictionary <string, string> properties = null; for (int i = 0; i < rdr.FieldCount; i++) { var fieldName = rdr.GetName(i); switch (fieldName) { case "geom": try { // get geography and translate to GeoJson // Reserved field names for geom are : geom, geomClip, geomWKB if (!rdr.IsDBNull(i)) { var geometry = (SqlGeometry)rdr["geom"]; if (this.GeometryClip) { geometry = geometry.STIntersection(box); } if (this.GeometryRemoveArtefacts) { geometry = SqlServerModel.DoRemoveArtifacts(geometry); } if (geometry != null && !geometry.STIsEmpty()) { outGeometry = geometry.ToGeoJson(numDigits); } } } catch (Exception) { throw; } break; default: //if (!rdr.IsDBNull(i)) //{ // if (properties == null) // properties = new Dictionary<string, string>(); // properties.Add(fieldName, rdr[i].ToString()); //} break; } } if (outGeometry != null) { var feature = new Models.GeoJson.Feature() { geometry = outGeometry, properties = properties }; features.Add(feature); } } } } } } catch (Exception) { throw; } var fc = new Models.GeoJson.FeatureCollection() { features = features.ToArray() }; return(fc); }
public void ImportShapeFile(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 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 (ShapefileDataReader shapeDataReader = new ShapefileDataReader(_shapeFile, GeometryFactory.Default)) { using (SqlConnection db = new SqlConnection(connectionString)) { MapBindTrace.Source.TraceEvent(TraceEventType.Information, 1, "Opening SQL connection"); db.Open(); SqlTransaction transaction = db.BeginTransaction(IsolationLevel.Serializable); try { #region Create destination table DbaseFieldDescriptor[] fields = (from field in shapeDataReader.DbaseHeader.Fields where fieldsToImport.Contains(field.Name) select field).ToArray(); List <SqlColumnDescriptor> sqlFields = ShapeFileHelper.TranslateDbfTypesToSql(fields); MapBindTrace.Source.TraceEvent(TraceEventType.Information, 1, "Create SQL 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 #region Read shape file int numRecord = 0; while (shapeDataReader.Read()) { numRecord++; try { #region Shape feature import if (_worker.CancellationPending) { break; } IGeometry geom = shapeDataReader.Geometry; IGeometry geomOut = null; // BUGGY GeometryTransform.TransformGeometry(GeometryFactory.Default, geom, trans.MathTransform); if (_transform == null) { geomOut = geom; } else { geomOut = ShapeFileHelper.ReprojectGeometry(geom, _transform); } #region Prepare insert // Set geom SRID geomOut.SRID = SRID; List <object> SqlNativeGeomList = new List <object>(); List <object> properties = new List <object>(); switch (spatialType) { #region geography case enSpatialType.geography: try { SqlNativeGeomList.Add(SqlServerHelper.ConvertToSqlType(geomOut, SRID, true, numRecord)); } catch (Exception exGeomConvert) { MapBindTrace.Source.TraceData(TraceEventType.Error, 1, "Error Converting geography : ", exGeomConvert); var args = new ShapeImportExceptionEventArgs(exGeomConvert, false, shapeDataReader.DumpCurrentRecord(), shapeDataReader.Geometry, numRecord); if (this.Raise_Error(args)) { if (args.Ignore) { SqlNativeGeomList = new List <object>(); } else { break; } } else { break; } } break; #endregion #region geometry case enSpatialType.geometry: try { SqlNativeGeomList.Add(SqlServerHelper.ConvertToSqlType(geomOut, SRID, false, numRecord)); } catch (Exception exGeomConvert) { MapBindTrace.Source.TraceData(TraceEventType.Error, 1, "Error Converting geometry : ", exGeomConvert); var args = new ShapeImportExceptionEventArgs(exGeomConvert, false, shapeDataReader.DumpCurrentRecord(), shapeDataReader.Geometry, numRecord); if (this.Raise_Error(args)) { if (args.Ignore) { SqlNativeGeomList = new List <object>(); } else { break; } } else { break; } } break; #endregion #region both case enSpatialType.both: bool geomConverted = false; try { SqlNativeGeomList.Add(SqlServerHelper.ConvertToSqlType(geomOut, SRID, false, numRecord)); geomConverted = true; SqlNativeGeomList.Add(SqlServerHelper.ConvertToSqlType(geomOut, SRID, true, numRecord)); } catch (Exception exGeomConvert) { MapBindTrace.Source.TraceData(TraceEventType.Error, 1, "Error Converting geometry or geography : ", exGeomConvert); var args = new ShapeImportExceptionEventArgs(exGeomConvert, false, shapeDataReader.DumpCurrentRecord(), shapeDataReader.Geometry, numRecord); if (this.Raise_Error(args)) { if (args.Ignore) { if (geomConverted) { SqlNativeGeomList.Add(null); } else { SqlNativeGeomList.AddRange(new object[] { null, null }); } } else { break; } } else { break; } } break; #endregion } // Get Attributes for (int i = 0; i < fields.Length; i++) { properties.Add(shapeDataReader[fields[i].Name]); } // Fill in-memory datatable DataRow row = SqlServerModel.GetNewDataTableRow(dataTable, tableName, SqlNativeGeomList, properties); dataTable.Rows.Add(row); #endregion //if (numRecord % 10 == 0) _worker.ReportProgress((int)((numRecord * 100f) / this.RecordCount), string.Format("Reading {0} records", numRecord)); #endregion } catch (Exception exGeom) { MapBindTrace.Source.TraceData(TraceEventType.Error, 1, "Error Converting geometry : ", exGeom); this.Raise_Error(new ShapeImportExceptionEventArgs(exGeom, true, shapeDataReader.DumpCurrentRecord(), shapeDataReader.Geometry, numRecord)); } } #endregion Read shape file #region Bulk insert if (!_worker.CancellationPending) { using (SqlBulkCopy bulk = new SqlBulkCopy(db, SqlBulkCopyOptions.Default, transaction)) { try { bulk.DestinationTableName = SqlServerModel.GenerateFullTableName(tableName, schema); bulk.BulkCopyTimeout = 3600; // 1 hour timeout bulk.NotifyAfter = 10; bulk.SqlRowsCopied += (o, args) => { if (_worker.CancellationPending) { args.Abort = true; } else { _worker.ReportProgress((int)((args.RowsCopied * 100f) / this.RecordCount), string.Format("Writing {0} records", args.RowsCopied)); } }; _worker.ReportProgress(0, string.Format("Writing {0} records", 0)); bulk.WriteToServer(dataTable); bulk.Close(); } catch (OperationAbortedException ex) { MapBindTrace.Source.TraceData(TraceEventType.Error, 1, "Error inserting: ", ex); bulk.Close(); } } } #endregion if (_worker.CancellationPending) { MapBindTrace.Source.TraceEvent(TraceEventType.Warning, 1, "Rolling back transaction"); transaction.Rollback(); } else { #region Create spatial index MapBindTrace.Source.TraceEvent(TraceEventType.Information, 1, "Create spatial index"); _worker.ReportProgress(100, "Creating index..."); // Create spatial index string sqlScriptCreateIndex = SqlServerModel.GenerateCreateSpatialIndexScript(tableName, schema, geomcolName, SqlServerHelper.GetBoundingBox(this.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.TraceData(TraceEventType.Error, 2, "Error: ", ex); MapBindTrace.Source.TraceEvent(TraceEventType.Warning, 2, "Rolling back transaction"); transaction.Rollback(); if (!this.Raise_Error(new ShapeImportExceptionEventArgs(ex, true))) { throw; } } MapBindTrace.Source.TraceEvent(TraceEventType.Verbose, 2, "closing DB"); db.Close(); } } #endregion } catch (Exception ex) { MapBindTrace.Source.TraceData(TraceEventType.Error, 3, "Error: ", ex); this.Raise_Error(new ShapeImportExceptionEventArgs(ex, true)); } }); Trace.CorrelationManager.StartLogicalOperation("ImportShapeFile Worker"); _worker.RunWorkerAsync(); Trace.CorrelationManager.StopLogicalOperation(); }
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"); }
public SqlServerViewModel(SqlServerModel sqlServerModel) : base(sqlServerModel) { sqlServer = sqlServerModel; }