/// <summary> /// Reads all features and converts to WKT in the geodatabase table /// </summary> /// <param name="workspace">The ESRI workspace.</param> /// <param name="geodatabaseTableName">Name of the geodatabase table.</param> public void ReadAll(IWorkspace workspace, string geodatabaseTableName) { string sql = string.Empty; DateTime start = DateTime.Now; this.OnUpdateStatusMessage("Read All Data Start: " + start.ToString()); try { IFeatureWorkspace wrkspc = (IFeatureWorkspace)workspace; IFeatureClass fc = wrkspc.OpenFeatureClass(geodatabaseTableName); IDataset ds = (IDataset)fc; IGeoDataset geoDS = (IGeoDataset)ds; ISpatialReference spatialReference = geoDS.SpatialReference; StringBuilder columnHeaders = new StringBuilder("Geometry"); // prepare a list of field IDs List <long> fieldIDs = new List <long>(); if (this.ColumnNames != null) { string[] colNames = this.ColumnNames.Split(this.Delimiter); foreach (string columnName in colNames) { try { int fieldIndex = fc.Fields.FindField(columnName); if (fieldIndex > -1) { columnHeaders.Append(','); columnHeaders.Append(columnName); fieldIDs.Add(fieldIndex); } else { throw new InvalidFieldException("Field Name Does not exist: " + columnName); } } catch (Exception ex) { Trace.WriteLine(ex.StackTrace); throw; } } } int spatialreferenceWKID = spatialReference.FactoryCode; IFeatureCursor cur = null; int featureTotalCount = 0; bool recycle = true; if (this.WhereClause != null) { IQueryFilter filter = new QueryFilterClass(); filter.WhereClause = this.WhereClause; featureTotalCount = fc.FeatureCount(filter); cur = fc.Search(filter, recycle); } else { featureTotalCount = fc.FeatureCount(null); cur = fc.Search(null, recycle); } string featureTotalCountStr = featureTotalCount.ToString(); IFeature feature = null; List <string> transactionList = new List <string>(); long featureCount = 0; // TODO: make this an option this.OnNewDataLine(columnHeaders.ToString()); // write the column headers out while ((feature = cur.NextFeature()) != null) { featureCount++; StringBuilder dataLine = new StringBuilder(); string wkt = string.Empty; this.OnUpdateStatusMessage("Reading " + featureCount.ToString() + " of " + featureTotalCountStr + "..."); if (!feature.Shape.IsEmpty) { IGeometry geom = feature.Shape; int factoryCode = geom.SpatialReference.FactoryCode; try { byte[] geombytes = Util.S2SHelper.ConvertGeometryToWKB(geom); SharpMap.Geometries.IGeometry sharpGeom = SharpMap.Converters.WellKnownBinary.GeometryFromWKB.Parse(geombytes); wkt = SharpMap.Converters.WellKnownText.GeometryToWKT.Write(sharpGeom); } catch (Exception ex) { Trace.WriteLine(ex.StackTrace); Console.WriteLine("Exception while converting geometry: " + ex.Message); throw; } } dataLine.Append("\""); dataLine.Append(wkt); dataLine.Append("\""); foreach (long fieldIndex in fieldIDs) { if (!feature.get_Value((int)fieldIndex).Equals(System.DBNull.Value)) { string fieldValue = feature.get_Value((int)fieldIndex).ToString(); dataLine.Append(','); dataLine.Append("\""); dataLine.Append(fieldValue); dataLine.Append("\""); } } this.OnNewDataLine(dataLine.ToString()); if (this.Limit > 0 && featureCount.Equals(this.Limit)) { break; } } DateTime stop = DateTime.Now; TimeSpan timeSpan = stop.Subtract(start); this.OnUpdateStatusMessage("Read All Data Complete in: " + timeSpan.ToString()); return; } catch (Exception ex) { Trace.WriteLine(ex.StackTrace); throw; } }
/// <summary> /// Executes the Op /// </summary> void ISDEOperation.Execute() { int totalfeaturecount; bool newtable = false; try { System.Diagnostics.Stopwatch stopwatch = new System.Diagnostics.Stopwatch(); stopwatch.Start(); this.Workspace = Util.S2SHelper.Workspace(ref this.operationArguments); using (SQLiteConnection connection = new SQLiteConnection(this.SQLiteDBConnectionString)) { connection.Open(); this.PrintMessage("Spatialite Database Opened."); IFeatureCursor cursor; IFeatureClass fc; int spatialreferenceID; List <FieldMap> fieldmappings = null; string whereclause = this.operationArguments["w"] ?? string.Empty; this.OpenFeatureCursor(this.Workspace, whereclause, out fc, out cursor, out totalfeaturecount, out spatialreferenceID); if (cursor != null) { this.PrintInformation(string.Format(Constants.InitOpFeatureClassOpenedMessage, totalfeaturecount, ((IDataset)fc).Name)); string tablename = Utility.ParseObjectClassName(((IDataset)fc).Name); string geometryfieldname = Constants.DefaultGeometryFieldName; string geometrytype = Util.S2SHelper.GetGeometryType(ref fc); string spatialiteTableName = this.operationArguments["T"] ?? tablename; this.PrintMessage("SDE Table Name = " + tablename); this.PrintMessage("Spatialite Table Name = " + spatialiteTableName); this.PrintMessage("Spatialite Geometry Field Name = " + geometryfieldname); this.PrintMessage("Spatialite Geometry Type = " + geometrytype); this.PrintMessage("Featureclass Spatial Reference Factory Code=" + Util.S2SHelper.GetFactoryCode(fc).ToString()); // load the spatial extension using (SQLiteCommand extloadCommand = connection.CreateCommand()) { // load the spatial extension: extloadCommand.CommandText = @"SELECT load_extension('libspatialite-2.dll');"; int recordsAffected = extloadCommand.ExecuteNonQuery(); if (!this.TableExist(connection, spatialiteTableName)) { this.PrintMessage("Spatialite Table Name = " + spatialiteTableName + " created."); string tname = tablename; if (!string.IsNullOrEmpty(this.operationArguments["D"])) { tname = this.operationArguments["D"] + "." + tname; } extloadCommand.CommandText = Util.S2SHelper.GenerateCreateTableSQL(this.Workspace, tname, spatialiteTableName, out fieldmappings, geometryfieldname); extloadCommand.ExecuteNonQuery(); newtable = true; } else { this.PrintMessage("Spatialite Table Name = " + spatialiteTableName + " already exists."); throw new TableAlreadyExistsException("Table already exists.", tablename); } } using (SQLiteTransaction trans = connection.BeginTransaction()) { using (SQLiteCommand insertCommand = connection.CreateCommand()) { this.PrintMessage("Obtaining Parameterized Insert SQL Statement"); insertCommand.CommandText = this.CreateParameterizedInsertSQL( fieldmappings, spatialiteTableName, insertCommand); this.PrintMessage("Insert SQL Statement: " + insertCommand.CommandText); IFeature feature = null; int featureCount = 0; // add the parameters: for (int f = 0; f < fieldmappings.Count; f++) { insertCommand.Parameters.Add(new SQLiteParameter(fieldmappings[f].DBFieldType)); } // add the wkt parameter insertCommand.Parameters.Add(new SQLiteParameter(DbType.String)); this.PrintInformation("Start featureclass read."); while ((feature = cursor.NextFeature()) != null) { featureCount++; this.PrintMessage("Reading feature OID=" + feature.OID.ToString() + " (" + featureCount.ToString() + " of " + totalfeaturecount.ToString() + ")."); for (int f = 0; f < fieldmappings.Count; f++) { int fieldindex = feature.Fields.FindField(fieldmappings[f].FeatureFieldname); IField field = feature.Fields.get_Field(fieldindex); if (feature.get_Value(fieldindex) != System.DBNull.Value && feature.get_Value(fieldindex) != null) { if (insertCommand.Parameters[f].DbType.Equals(DbType.String)) { insertCommand.Parameters[f].Value = feature.get_Value(fieldindex).ToString(); } else if (insertCommand.Parameters[f].DbType.Equals(DbType.Double)) { insertCommand.Parameters[f].Value = Convert.ToDouble(feature.get_Value(fieldindex)); } else if (insertCommand.Parameters[f].DbType.Equals(DbType.Single)) { insertCommand.Parameters[f].Value = Convert.ToSingle(feature.get_Value(fieldindex)); } else if (insertCommand.Parameters[f].DbType.Equals(DbType.Int32)) { insertCommand.Parameters[f].Value = Convert.ToInt32(feature.get_Value(fieldindex)); } else if (insertCommand.Parameters[f].DbType.Equals(DbType.DateTime)) { insertCommand.Parameters[f].Value = feature.get_Value(fieldindex).ToString(); } else { throw new Exception("DBType Not Implemented"); } } else { insertCommand.Parameters[f].Value = System.DBNull.Value; } } // need to the WKT parameter: string wkt = string.Empty; if (!feature.Shape.IsEmpty) { IGeometry geom = feature.Shape; try { this.PrintMessage("Converting geometry to WKT..."); byte[] geombytes = Util.S2SHelper.ConvertGeometryToWKB(geom); SharpMap.Geometries.IGeometry sharpGeom = SharpMap.Converters.WellKnownBinary.GeometryFromWKB.Parse(geombytes); wkt = SharpMap.Converters.WellKnownText.GeometryToWKT.Write(sharpGeom); this.PrintMessage("...conversion to WKT complete."); } catch (Exception ex) { System.Diagnostics.Trace.WriteLine(ex.StackTrace); Console.WriteLine("Exception while converting geometry: " + ex.Message + " for objectid = " + feature.OID.ToString() + "."); } } insertCommand.Parameters[insertCommand.Parameters.Count - 1].Value = wkt; try { insertCommand.ExecuteNonQuery(); } catch (SQLiteException ex) { System.Diagnostics.Trace.WriteLine(ex.StackTrace); throw; } } } this.PrintInformation("Committing insert transactions..."); trans.Commit(); this.PrintInformation("...commit Complete."); } if (newtable) { // create the geometry field: using (SQLiteCommand updateCommand = connection.CreateCommand()) { this.PrintInformation("Adding geometry column..."); string cmdtext = string.Format(Constants.SQLCreateGeometryField, spatialiteTableName, geometryfieldname, this.SpatialReferenceID.ToString(), geometrytype); this.PrintMessage("SQL: " + cmdtext); updateCommand.CommandText = cmdtext; updateCommand.CommandType = CommandType.Text; updateCommand.ExecuteNonQuery(); this.PrintInformation("...geometry column created."); } } // update the geometry field: using (SQLiteCommand updateCommand = connection.CreateCommand()) { this.PrintInformation("Updating geometry from WKT..."); updateCommand.CommandText = string.Format(Constants.SQLUpdateGeomFromWKT, spatialiteTableName, geometryfieldname, "WKT", this.SpatialReferenceID.ToString(), geometrytype); this.PrintMessage("SQL: " + updateCommand.CommandText); updateCommand.ExecuteNonQuery(); this.PrintInformation("...geometry update from WKT complete."); } } connection.Close(); } stopwatch.Stop(); this.PrintInformation(totalfeaturecount.ToString() + " feature(s) exported in " + ((double)stopwatch.ElapsedMilliseconds / 1000).ToString("0.0000") + " seconds."); } catch (Exception ex) { System.Diagnostics.Trace.WriteLine(ex.StackTrace); throw; } }