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