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