/// <summary>
/// Creates a geometry-enabled (feature set) table.
/// </summary>
/// <param name="metadata">metadata describing the standard attributes</param>
/// <param name="geomMetadata">metadata describing the geometry column</param>
/// <returns></returns>
public bool CreateFeatureSetTable(SQLiteTableMetadata metadata, GeometryColumnInfo geomMetadata)
{
Contract.Requires(metadata != null && geomMetadata != null);
using (var conn = OpenDatabase())
{
CreateFeatureSetTable(conn, metadata, geomMetadata);
conn.Close();
}
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 SaveFeatureSet(IFeatureSet fs)
{
using (SQLiteConnection conn = OpenDatabase())
{
var meta = new SQLiteTableMetadata(fs.Name);
SQLiteCommand cmd = new SQLiteCommand("DROP TABLE IF EXISTS " + meta.TableName, conn);
cmd.ExecuteNonQuery();
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);
}
//TODO: check if EpsgCode is correct == SRID
var geomMeta = new GeometryColumnInfo(meta.TableName, fs.FeatureType, fs.Projection.EpsgCode);
colNames += "[" + geomMeta.GeometryColumnName + "]";
vals += "ST_GeomFromWKB(?, " + geomMeta.SRID + ")";
parms.Add(new SQLiteParameter(DbType.Object));
try
{
CreateFeatureSetTable(conn, meta, geomMeta);
}
catch (Exception ex)
{
conn.Close();
return(false);
}
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++)
{
UpdateCommandParameters(fs.Features[i], parms, colNamesInFS, fs.Projection.EpsgCode);
insCmd.ExecuteNonQuery();
}
}
catch (KeyNotFoundException kex)
{
//TODO: log this
trans.Rollback();
conn.Close();
return(false);
}
catch (Exception ex)
{
//TODO: log this
trans.Rollback();
conn.Close();
return(false);
}
trans.Commit();
conn.Close();
}
return(true);
}
