/// <summary>
/// Creates a feature set table using the given SQLite database connection. Catches exceptions and returns creation status.
/// </summary>
/// <param name="conn"></param>
/// <param name="metadata"></param>
/// <param name="geomMetadata"></param>
/// <returns></returns>
private bool TryCreateFeatureSetTable(SQLiteConnection conn, SQLiteTableMetadata metadata, FeatureSetTableInfo geomMetadata)
{
try
{
string sql = metadata.GetCreateSqlCode();
log.Debug("TryCreateFeatureSetTable: sql=\"{0}\"", sql);
var transaction = conn.BeginTransaction();
var cmd = new SQLiteCommand(sql, conn);
int nRows = cmd.ExecuteNonQuery();
log.Debug("TryCreateFeatureSetTable: nRows={0}", nRows);
sql = String.Format("SELECT AddGeometryColumn('{0}', '{1}', {2}, '{3}', '{4}', 1);", // last parameter means that only NOT NULL geometries will be accepted
metadata.TableName, geomMetadata.GeometryColumnName, geomMetadata.SRID,
geomMetadata.GetGeometryType(), geomMetadata.CoordDimension.ToString().ToUpper());
log.Debug("TryCreateFeatureSetTable: sql=\"{0}\"", sql);
cmd = new SQLiteCommand(sql, conn);
nRows = cmd.ExecuteNonQuery();
log.Debug("TryCreateFeatureSetTable: nRows={0}", nRows);
transaction.Commit();
}
catch (Exception ex)
{
this.ErrorMessage = string.Format("Unable to create feature set table \"{0}\": {1}", metadata.TableName, ex.Message);
log.Error(this.ErrorMessage);
return(false);
}
return(true);
}
/// <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);
}