/// <summary>
/// Imports the provided shapefile into a sqlite database using the VirtualShape extension
/// </summary>
/// <param name="conn"></param>
/// <param name="client"></param>
/// <param name="filename"></param>
/// <param name="tableName"></param>
/// <returns></returns>
public static bool ImportShapefile(DbConnection conn, IDataClient client, string filename, string tableName, int srid)
{
try
{
if (!File.Exists(filename))
{
_log.ErrorFormat("ImportShapefile Failed!: File does not exist {0}", filename);
return(false);
}
if (DataClient.HasTable(conn, client, tableName))
{
client.GetCommand(string.Format("DROP TABLE \"{0}\"", tableName)).ExecuteNonQuery();
}
//trim off the '.shp' from the end
filename = Path.Combine(Path.GetDirectoryName(filename), Path.GetFileNameWithoutExtension(filename));
string sql = string.Format("CREATE VIRTUAL TABLE " + tableName + " USING VirtualShape('{0}', CP1252, {1});", filename, srid);
client.GetCommand(sql, conn).ExecuteNonQuery();
_log.DebugFormat("Imported Shapefile {0} into table {1}",
Path.GetFileNameWithoutExtension(filename),
tableName);
return(true);
}
catch (Exception ex)
{
_log.Error("ImportShapefile failed: Error while loading shapefile ", ex);
}
return(false);
}
/// <summary>
/// Uses a select statement and the ADO.NET CommandBuilder
/// to generate Insert,Update, and Delete statements, and load them onto an adapter
/// </summary>
/// <param name="conn"></param>
/// <param name="client"></param>
/// <param name="sql"></param>
/// <returns></returns>
public static DbDataAdapter GetMagicAdapter(DbConnection conn, IDataClient client, string sql)
{
var cmd = client.GetCommand(sql, conn);
var dba = client.GetDataAdapter(cmd);
var builder = client.GetCommandBuilder(dba);
dba.InsertCommand = builder.GetInsertCommand(true);
dba.DeleteCommand = builder.GetDeleteCommand(true);
dba.UpdateCommand = builder.GetUpdateCommand(true);
if (dba.InsertCommand != null)
{
dba.InsertCommand.CommandTimeout = client.QueryTimeout;
}
if (dba.DeleteCommand != null)
{
dba.DeleteCommand.CommandTimeout = client.QueryTimeout;
}
if (dba.UpdateCommand != null)
{
dba.UpdateCommand.CommandTimeout = client.QueryTimeout;
}
return(dba);
}
protected DataTable SetupTable(DbConnection conn, IDataClient client, string tablename)
{
tempTableName = tablename;
client.GetCommand(string.Format(CreateTableSQL, tablename), conn).ExecuteNonQuery();
string selectAllSQL = string.Format("select * from \"{0}\"", tablename);
return(DataClient.GetMagicTable(conn, client, selectAllSQL));
}
public static int RowCount(DbConnection conn, IDataClient client, string tablename)
{
try
{
string sql = string.Format("select count(1) from {0}", tablename);
var cmd = client.GetCommand(sql, conn);
return(Utilities.GetAs <int>(cmd.ExecuteScalar(), -1));
}
catch (Exception ex)
{
_log.Error("Error while looking for table", ex);
}
return(-1);
}
/// <summary>
/// Returns true if a particular table has a particular column
/// </summary>
/// <param name="columnname"></param>
/// <param name="tablename"></param>
/// <param name="conn"></param>
/// <param name="client"></param>
/// <returns></returns>
public static bool HasColumn(string columnname, string tablename, DbConnection conn, IDataClient client)
{
string sql = string.Format("select * from {0} WHERE 0 = 1", tablename);
var dt = GetMagicTable(conn, client, sql);
var cmd = client.GetCommand(sql, conn);
var dba = client.GetDataAdapter(cmd);
dba.Fill(dt);
if (dt != null)
{
return(dt.Columns.Contains(columnname));
}
return(false);
}
/// <summary>
/// 'Magically' populates a DataTable object with the contents of your sql query
/// </summary>
/// <param name="conn"></param>
/// <param name="client"></param>
/// <param name="sql"></param>
/// <returns></returns>
public static DataTable GetMagicTable(DbConnection conn, IDataClient client, string sql)
{
DataTable dt = new DataTable();
try
{
var cmd = client.GetCommand(sql, conn);
var dba = client.GetDataAdapter(cmd);
dba.Fill(dt);
return(dt);
}
catch (Exception ex)
{
_log.Error("error during select", ex);
}
return(null);
}
/// <summary>
/// Uses a select statement and the ADO.NET CommandBuilder
/// to generate Insert,Update, and Delete statements, and load them onto an adapter
/// </summary>
/// <param name="conn"></param>
/// <param name="client"></param>
/// <param name="sql"></param>
/// <returns></returns>
public static DbDataAdapter GetMagicAdapter(DbConnection conn, IDataClient client, string sql)
{
var cmd = client.GetCommand(sql, conn);
var dba = client.GetDataAdapter(cmd);
var builder = client.GetCommandBuilder(dba);
dba.InsertCommand = builder.GetInsertCommand(true);
dba.DeleteCommand = builder.GetDeleteCommand(true);
dba.UpdateCommand = builder.GetUpdateCommand(true);
if (dba.InsertCommand != null)
dba.InsertCommand.CommandTimeout = client.QueryTimeout;
if (dba.DeleteCommand != null)
dba.DeleteCommand.CommandTimeout = client.QueryTimeout;
if (dba.UpdateCommand != null)
dba.UpdateCommand.CommandTimeout = client.QueryTimeout;
return dba;
}
/// <summary>
/// Imports the provided shapefile into a sqlite database using the VirtualShape extension
/// </summary>
/// <param name="conn"></param>
/// <param name="client"></param>
/// <param name="filename"></param>
/// <param name="tableName"></param>
/// <returns></returns>
public static bool ImportShapefile(DbConnection conn, IDataClient client, string filename, string tableName, int srid)
{
try
{
if (!File.Exists(filename))
{
_log.ErrorFormat("ImportShapefile Failed!: File does not exist {0}", filename);
return false;
}
if (DataClient.HasTable(conn, client, tableName))
{
client.GetCommand(string.Format("DROP TABLE \"{0}\"", tableName)).ExecuteNonQuery();
}
//trim off the '.shp' from the end
filename = Path.Combine(Path.GetDirectoryName(filename), Path.GetFileNameWithoutExtension(filename));
string sql = string.Format("CREATE VIRTUAL TABLE " + tableName + " USING VirtualShape('{0}', CP1252, {1});", filename, srid);
client.GetCommand(sql, conn).ExecuteNonQuery();
_log.DebugFormat("Imported Shapefile {0} into table {1}",
Path.GetFileNameWithoutExtension(filename),
tableName);
return true;
}
catch (Exception ex)
{
_log.Error("ImportShapefile failed: Error while loading shapefile ", ex);
}
return false;
}
public void RemoveTemporaryTable(DbConnection conn, IDataClient client)
{
client.GetCommand(string.Format(DropTableSQL, tempTableName), conn).ExecuteNonQuery();
}
protected DataTable SetupTable(DbConnection conn, IDataClient client, string tablename)
{
tempTableName = tablename;
client.GetCommand(string.Format(CreateTableSQL, tablename), conn).ExecuteNonQuery();
string selectAllSQL = string.Format("select * from \"{0}\"", tablename);
return DataClient.GetMagicTable(conn, client, selectAllSQL);
}
public void RemoveTemporaryTable(DbConnection conn, IDataClient client)
{
client.GetCommand(string.Format(DropTableSQL, tempTableName), conn).ExecuteNonQuery();
}
public static int RowCount(DbConnection conn, IDataClient client, string tablename)
{
try
{
string sql = string.Format("select count(1) from {0}", tablename);
var cmd = client.GetCommand(sql, conn);
return Utilities.GetAs<int>(cmd.ExecuteScalar(), -1);
}
catch (Exception ex)
{
_log.Error("Error while looking for table", ex);
}
return -1;
}
/// <summary>
/// Returns true if a particular table has a particular column
/// </summary>
/// <param name="columnname"></param>
/// <param name="tablename"></param>
/// <param name="conn"></param>
/// <param name="client"></param>
/// <returns></returns>
public static bool HasColumn(string columnname, string tablename, DbConnection conn, IDataClient client)
{
string sql = string.Format("select * from {0} WHERE 0 = 1", tablename);
var dt = GetMagicTable(conn, client, sql);
var cmd = client.GetCommand(sql, conn);
var dba = client.GetDataAdapter(cmd);
dba.Fill(dt);
if (dt != null)
{
return dt.Columns.Contains(columnname);
}
return false;
}
/// <summary>
/// 'Magically' populates a DataTable object with the contents of your sql query
/// </summary>
/// <param name="conn"></param>
/// <param name="client"></param>
/// <param name="sql"></param>
/// <returns></returns>
public static DataTable GetMagicTable(DbConnection conn, IDataClient client, string sql)
{
DataTable dt = new DataTable();
try
{
var cmd = client.GetCommand(sql, conn);
var dba = client.GetDataAdapter(cmd);
dba.Fill(dt);
return dt;
}
catch (Exception ex)
{
_log.Error("error during select", ex);
}
return null;
}
