/// <summary> /// Saves the given feature class to a SpatiaLite database /// </summary> /// <param name="fs"></param> /// <returns></returns> public async Task <bool> TrySaveSpatialClass(DotSpatial.Data.IFeatureSet fs) { SQLiteConnection conn; if (!TryOpen(out conn)) { return(false); } using (conn) { Helper.SpatiaLiteHelper helper = new Helper.SpatiaLiteHelper(); if (helper.CheckIfFeatureSetExists(conn, fs)) { this.ErrorMessage = "Unable to save spatial class: the class already exists in the dataset."; return(false); } bool ok = false; await Task.Run(() => { ok = helper.TrySaveFeatureSet(conn, fs); }); if (!ok) { this.ErrorMessage = "Unable to save spatial class: " + helper.ErrorMessage; return(false); } return(true); } }
/// <summary> /// require three parameters /// </summary> /// <param name="feature">Grid feature set</param> /// <param name="para"> The first is a field name used to sort mf_grid; The second is /// a string array that stores field names used to set layer elevation (from top to bottom) /// </param> public override void Assign(DotSpatial.Data.IFeatureSet feature, params object[] para) { var grid = this.Owner.Grid as MFGrid; var mfprj = this.Owner as Modflow; var dt_grid = feature.DataTable; dt_grid.DefaultView.Sort = para[0].ToString(); string[] ele_fields = para[1] as string[]; if (grid.Elevations == null) { grid.Elevations = new DataCube <float>(grid.LayerCount, 1, grid.ActiveCellCount); } for (int l = 0; l < grid.LayerCount; l++) { foreach (DataRow dr in dt_grid.Rows) { int row = int.Parse(dr["Row"].ToString()) - 1; int col = int.Parse(dr["Column"].ToString()) - 1; int id = grid.Topology.GetID(row, col); int index = grid.Topology.CellID2CellIndex[id]; grid.Elevations[l, index, 0] = float.Parse(dr[ele_fields[l]].ToString()); } } }
/// <summary> /// /// </summary> /// <param name="feature"></param> /// <param name="para">require two parameters: the first is a field name used to sort the feature; the second is a string array that stores field names /// used to set initial head (from top to bottom) name /// </param> public override void Assign(DotSpatial.Data.IFeatureSet feature, params object[] para) { if (para == null) { return; } var dt_grid = feature.DataTable; dt_grid.DefaultView.Sort = para[0].ToString(); string[] strt_fields = para[1] as string[]; var mfgrid = (this.Owner.Grid as MFGrid); //if(mfgrid.IBound == null) // mfgrid.IBound = ILMath.zeros<int>(mfgrid.RowCount, mfgrid.ColumnCount, mfgrid.ActualLayerCount); if (mfgrid.IBound == null) { mfgrid.IBound = new DataCube <float>(mfgrid.ActualLayerCount, mfgrid.RowCount, mfgrid.ColumnCount); } mfgrid.ActiveCellCount = 0; for (int l = 0; l < mfgrid.ActualLayerCount; l++) { foreach (DataRow dr in dt_grid.Rows) { int row = int.Parse(dr["Row"].ToString()); int col = int.Parse(dr["Column"].ToString()); mfgrid.IBound[l, row - 1, col - 1] = float.Parse(dr["Active"].ToString()); if (mfgrid.IBound[l, row - 1, col - 1] != 0) { mfgrid.ActiveCellCount++; } } } if (this.STRT == null) { this.STRT = new DataCube <float>(mfgrid.ActualLayerCount, 1, mfgrid.ActiveCellCount); } for (int l = 0; l < mfgrid.ActualLayerCount; l++) { foreach (DataRow dr in dt_grid.Rows) { int row = int.Parse(dr["Row"].ToString()); int col = int.Parse(dr["Column"].ToString()); int index = mfgrid.Topology.GetSerialIndex(row, col); this.STRT[l, index, 0] = float.Parse(dr[strt_fields[l]].ToString()); } } }
/// <summary> /// Returns true if the specified feature set already exists in the sqlite database. /// </summary> /// <param name="conn"></param> /// <param name="fs"></param> /// <returns></returns> public bool CheckIfFeatureSetExists(SQLiteConnection conn, DotSpatial.Data.IFeatureSet fs) { string sql = string.Format("SELECT name FROM sqlite_master WHERE type='table' AND name='{0}';", fs.Name); var cmd = new SQLiteCommand(sql, conn); int nRows = cmd.ExecuteNonQuery(); if (nRows > 0) { return(true); } else { return(false); } }
/// <summary> /// Save a feature set to a SQLite database. The FeatureSet is saved to a table /// with the same name as FeatureSet.Name. Existing tables will get dropped. /// </summary> /// <param name="connstring">database connection string</param> /// <param name="fs">the feature set to save</param> public bool TrySaveFeatureSet(SQLiteConnection conn, DotSpatial.Data.IFeatureSet fs) { var meta = new SQLiteTableMetadata(fs.Name); if (meta.TableName == null || meta.TableName.Length == 0) { this.ErrorMessage = "Invalid table name provided"; log.Error(this.ErrorMessage); return(false); } int srid = 0; if (fs.Projection == null) { srid = -1; // unknown } else { srid = fs.Projection.AuthorityCode; } using (SQLiteCommand cmd = new SQLiteCommand("DROP TABLE IF EXISTS " + meta.TableName, conn)) { log.Debug("TrySaveFeatureSet: dropped table {0} if it exists", meta.TableName); try { cmd.ExecuteNonQuery(); } catch (Exception ex) { this.ErrorMessage = string.Format("SQL execution issue 1: {0}", ex.Message); log.Error(this.ErrorMessage); return(false); } } string colNames = ""; string vals = ""; List <SQLiteParameter> parms = new List <SQLiteParameter>(); List <string> colNamesInFS = new List <string>(); foreach (DataColumn c in fs.DataTable.Columns) { meta.Columns.Add(new Column(c.ColumnName, c.DataType)); colNames += "[" + c.ColumnName + "], "; vals += "?, "; parms.Add(new SQLiteParameter()); colNamesInFS.Add(c.ColumnName); } var geomMeta = new FeatureSetTableInfo(meta.TableName, fs.FeatureType, srid); colNames += "[" + geomMeta.GeometryColumnName + "]"; vals += "ST_GeomFromWKB(?, " + geomMeta.SRID + ")"; parms.Add(new SQLiteParameter(DbType.Object)); if (!TryCreateFeatureSetTable(conn, meta, geomMeta)) { return(false); } log.Debug("Created feature set table"); var trans = conn.BeginTransaction(); try { SQLiteCommand insCmd = new SQLiteCommand(conn); insCmd.CommandText = string.Format("INSERT INTO [{0}] ({1}) VALUES ({2});", meta.TableName, colNames, vals); insCmd.Parameters.AddRange(parms.ToArray()); for (int i = 0; i < fs.Features.Count; i++) { try { UpdateCommandParameters(fs.Features[i], parms, colNamesInFS, srid); //log.Debug("Starting insert of feature {0}: sql=\"{1}\" parameters=\"{2}\"", i, insCmd.CommandText, GetParametersAsString(insCmd.Parameters)); //log.Debug(string.Join("|", Array.ConvertAll(fs.Features[i].BasicGeometry.Coordinates.ToArray(), x => x.X.ToString() + "," + x.Y.ToString()))); insCmd.ExecuteNonQuery(); } catch (Exception ex) { this.ErrorMessage = string.Format("SQL execution issue 2: {0}", ex.Message); log.Error(this.ErrorMessage); return(false); } } log.Debug("Inserted {0} features", fs.Features.Count); } catch (KeyNotFoundException kex) { log.Error("Unable to save feature set into table \"{0}\": {1}", fs.Name, kex.Message); trans.Rollback(); return(false); } catch (Exception ex) { log.Error("Unable to save feature set into table \"{0}\": {1}", fs.Name, ex.Message); trans.Rollback(); return(false); } trans.Commit(); log.Debug("Saved feature set into the table"); return(true); }