public static void Main(string[] args)
{
Npgsql.NpgsqlConnection v_con = null;
Npgsql.NpgsqlCommand v_cmd = null;
Npgsql.NpgsqlDataReader v_reader = null;
System.Data.DataTable v_table;
System.Data.DataRow v_row;
Console.WriteLine("Exemplo PostgreSQL usando DataReader");
Console.WriteLine();
try
{
// 1) instanciando Connection
v_con = new Npgsql.NpgsqlConnection(
"Server=127.0.0.1;Port=5432;Database=lugares;User ID=postgres;Password=knightnote"
);
// 2) abrindo Connection
v_con.Open();
// 3) instanciando Command
v_cmd = new Npgsql.NpgsqlCommand("select * from estados", v_con);
// 4) executando DataReader
v_reader = v_cmd.ExecuteReader();
// 5) criando DataTable
v_table = new System.Data.DataTable("RESULTADO");
for (int i = 0; i < v_reader.FieldCount; i++)
v_table.Columns.Add(v_reader.GetName(i), typeof(string));
// 6) alimentando DataTable
while (v_reader.Read())
{
v_row = v_table.NewRow();
for (int i = 0; i < v_reader.FieldCount; i++)
v_row[i] = v_reader[i].ToString();
v_table.Rows.Add(v_row);
}
// 7) usando DataTable (imprimindo na tela)
foreach (System.Data.DataColumn c in v_table.Columns)
Console.Write("{0} ", c.ColumnName);
Console.WriteLine();
foreach (System.Data.DataRow r in v_table.Rows)
{
foreach (System.Data.DataColumn c in v_table.Columns)
Console.Write("{0} ", r[c].ToString());
Console.WriteLine();
}
}
catch (Npgsql.NpgsqlException ex)
{
Console.WriteLine(ex.ToString());
}
finally
{
// 8) liberando Command
if (v_cmd != null)
{
v_cmd.Cancel();
v_cmd.Dispose();
v_cmd = null;
}
// 9) liberando DataReader
if (v_reader != null)
{
v_reader.Close();
v_reader = null;
}
// 10) fechando e liberando Connection
if (v_con != null)
{
v_con.Close();
v_con = null;
}
}
Console.ReadKey();
}
public SharpMap.Data.FeatureDataTable QueryFeatures(IGeometry geom, double distance)
{
//Collection<IGeometry> features = new Collection<IGeometry>();
using (Npgsql.NpgsqlConnection conn = new Npgsql.NpgsqlConnection(_ConnectionString))
{
string strGeom = "GeomFromText('" + geom.AsText() + "')";
if (this.SRID > 0)
{
strGeom = "setSRID(" + strGeom + "," + this.SRID.ToString() + ")";
}
string strSQL = "SELECT * , AsBinary(" + this.GeometryColumn + ") As sharpmap_tempgeometry FROM " + this.Table + " WHERE ";
if (!String.IsNullOrEmpty(_defintionQuery))
{
strSQL += this.DefinitionQuery + " AND ";
}
strSQL += this.GeometryColumn + " && " + "buffer(" + strGeom + "," + distance.ToString(Map.numberFormat_EnUS) + ")";
strSQL += " AND distance(" + this.GeometryColumn + ", " + strGeom + ")<" + distance.ToString(Map.numberFormat_EnUS);
using (Npgsql.NpgsqlDataAdapter adapter = new Npgsql.NpgsqlDataAdapter(strSQL, conn))
{
System.Data.DataSet ds = new System.Data.DataSet();
conn.Open();
adapter.Fill(ds);
conn.Close();
if (ds.Tables.Count > 0)
{
FeatureDataTable fdt = new FeatureDataTable(ds.Tables[0]);
foreach (System.Data.DataColumn col in ds.Tables[0].Columns)
{
if (col.ColumnName != this.GeometryColumn && col.ColumnName != "sharpmap_tempgeometry")
{
fdt.Columns.Add(col.ColumnName, col.DataType, col.Expression);
}
}
foreach (System.Data.DataRow dr in ds.Tables[0].Rows)
{
SharpMap.Data.FeatureDataRow fdr = fdt.NewRow();
foreach (System.Data.DataColumn col in ds.Tables[0].Columns)
{
if (col.ColumnName != this.GeometryColumn && col.ColumnName != "sharpmap_tempgeometry")
{
fdr[col.ColumnName] = dr[col];
}
}
fdr.Geometry = SharpMap.Converters.WellKnownBinary.GeometryFromWKB.Parse((byte[])dr["sharpmap_tempgeometry"]);
fdt.AddRow(fdr);
}
return(fdt);
}
else
{
return(null);
}
}
}
}
public void CreateRecipe(Recipe recipe)
{
_conn.Open();
// Insert some data
using (var cmd = new Npgsql.NpgsqlCommand())
{
cmd.Connection = _conn;
cmd.CommandText = "INSERT INTO recipes (name,ingredients,instructions) VALUES (@name, @ingr, @instr)";
cmd.Parameters.AddWithValue("name", recipe.GetName());
cmd.Parameters.AddWithValue("ingr", recipe.GetIngredients());
cmd.Parameters.AddWithValue("instr", recipe.GetInstructions());
cmd.ExecuteNonQuery();
}
_conn.Close();
}
/// <summary>
/// Runs a stored function and returns value by using a converter
/// </summary>
/// <param name="name">Stored function name</param>
/// <param name="param">List of parameter to use. Set null for no parameters.</param>
/// <param name="connectionString">Connection sting to use</param>
/// <param name="transformer">Transformer function</param>
/// <param name="cancellationToken">Cancellation Token</param>
/// <returns>Object</returns>
public static async Task <object> HelperAsync(
string name,
IEnumerable <Npgsql.NpgsqlParameter> param,
string connectionString,
Func <Npgsql.NpgsqlCommand, CancellationToken, Task <object> > transformer,
CancellationToken cancellationToken)
{
object result = null;
if (Generics.StringExtensions.IsEmpty(name) ||
Generics.StringExtensions.IsEmpty(connectionString) ||
cancellationToken.IsCancellationRequested)
{
return(result);
}
using (Npgsql.NpgsqlConnection connection = new Npgsql.NpgsqlConnection(connectionString))
{
using (Npgsql.NpgsqlCommand command = new Npgsql.NpgsqlCommand(name, connection))
{
command.CommandType = System.Data.CommandType.StoredProcedure;
if (param != null)
{
foreach (var item in param)
{
command.Parameters.Add(item);
}
}
else
{
command.Parameters.Clear();
}
try
{
if (cancellationToken.IsCancellationRequested)
{
throw new OperationCanceledException(cancellationToken);
}
await connection.OpenAsync(cancellationToken);
result = await transformer(command, cancellationToken);
}
catch (AggregateException e)
{
ExceptionDispatchInfo.Capture(e.InnerException).Throw();
}
finally
{
connection.Close();
}
}
}
return(result);
}
public void Get()
{
var connection = new Npgsql.NpgsqlConnection(_appSettings.DbConnection);
var cmd = new Npgsql.NpgsqlCommand("select * from tokens", connection);
connection.Open();
var reader = cmd.ExecuteReader();
connection.Close();
}
public static void Main(string[] args)
{
Npgsql.NpgsqlConnection v_con = null;
Npgsql.NpgsqlDataAdapter v_adapter = null;
System.Data.DataTable v_table;
Console.WriteLine("Exemplo PostgreSQL usando DataAdapter");
Console.WriteLine();
try
{
// 1) instanciando Connection
v_con = new Npgsql.NpgsqlConnection(
"Server=127.0.0.1;Port=5432;Database=lugares;User ID=postgres;Password=knightnote"
);
// 2) abrindo Connection
v_con.Open();
// 3) instanciando DataAdapter
v_adapter = new Npgsql.NpgsqlDataAdapter("select * from estados", v_con);
// 4) instanciando DataTable
v_table = new System.Data.DataTable("RESULTADO");
// 5) alimentando DataTable
v_adapter.Fill(v_table);
// 6) usando DataTable (imprimindo na tela)
foreach (System.Data.DataColumn c in v_table.Columns)
Console.Write("{0} ", c.ColumnName);
Console.WriteLine();
foreach (System.Data.DataRow r in v_table.Rows)
{
foreach (System.Data.DataColumn c in v_table.Columns)
Console.Write("{0} ", r[c].ToString());
Console.WriteLine();
}
}
catch (Npgsql.NpgsqlException ex)
{
Console.WriteLine(ex.ToString());
}
finally
{
// 7) fechando e liberando Connection
if (v_con != null)
{
v_con.Close();
v_con = null;
}
}
Console.ReadKey();
}
/// <summary>
/// Returns the features that intersects with 'geom'
/// </summary>
/// <param name="geom"></param>
/// <param name="ds">FeatureDataSet to fill data into</param>
public void ExecuteIntersectionQuery(SharpMap.Geometries.Geometry geom, FeatureDataSet ds)
{
List <Geometries.Geometry> features = new List <SharpMap.Geometries.Geometry>();
using (Npgsql.NpgsqlConnection conn = new Npgsql.NpgsqlConnection(_ConnectionString))
{
string strGeom = "st_GeomFromText('" + geom.AsText() + "')";
if (this.SRID > 0)
{
strGeom = "st_setSRID(" + strGeom + "," + this.SRID.ToString() + ")";
}
string strSQL = "SELECT * , st_AsBinary(" + this.GeometryColumn + ") As sharpmap_tempgeometry FROM " + this.Table + " WHERE ";
if (_defintionQuery != null && _defintionQuery != "")
{
strSQL += this.DefinitionQuery + " AND ";
}
strSQL += this.GeometryColumn + " && " + strGeom + " AND st_distance(" + this.GeometryColumn + ", " + strGeom + ")<0";
using (Npgsql.NpgsqlDataAdapter adapter = new Npgsql.NpgsqlDataAdapter(strSQL, conn))
{
conn.Open();
adapter.Fill(ds);
conn.Close();
if (ds.Tables.Count > 0)
{
FeatureDataTable fdt = new FeatureDataTable(ds.Tables[0]);
foreach (System.Data.DataColumn col in ds.Tables[0].Columns)
{
if (col.ColumnName != this.GeometryColumn && col.ColumnName != "sharpmap_tempgeometry")
{
fdt.Columns.Add(col.ColumnName, col.DataType, col.Expression);
}
}
foreach (System.Data.DataRow dr in ds.Tables[0].Rows)
{
SharpMap.Data.FeatureDataRow fdr = fdt.NewRow();
foreach (System.Data.DataColumn col in ds.Tables[0].Columns)
{
if (col.ColumnName != this.GeometryColumn && col.ColumnName != "sharpmap_tempgeometry")
{
fdr[col.ColumnName] = dr[col];
}
}
fdr.Geometry = SharpMap.Converters.WellKnownBinary.GeometryFromWKB.Parse((byte[])dr["sharpmap_tempgeometry"]);
fdt.AddRow(fdr);
}
ds.Tables.Add(fdt);
}
}
}
}
/// <summary>
/// Runs a query and returns void
/// </summary>
/// <param name="name">Stored function name</param>
/// <param name="param">List of parameter to use. Set null for no parameters.</param>
/// <param name="connectionString">Connection sting to use</param>
/// <param name="cancellationToken">Cancellation Token</param>
/// <returns>boolean</returns>
public static async Task <bool> VoidAsync(string name, IEnumerable <Npgsql.NpgsqlParameter> param, string connectionString, CancellationToken cancellationToken = default(CancellationToken))
{
if (Generics.StringExtensions.IsEmpty(name) ||
Generics.StringExtensions.IsEmpty(connectionString) ||
cancellationToken.IsCancellationRequested)
{
return(false);
}
using (Npgsql.NpgsqlConnection connection = new Npgsql.NpgsqlConnection(connectionString))
{
using (Npgsql.NpgsqlCommand command = new Npgsql.NpgsqlCommand(name, connection))
{
command.CommandType = System.Data.CommandType.StoredProcedure;
if (param != null)
{
foreach (var item in param)
{
command.Parameters.Add(item);
}
}
else
{
command.Parameters.Clear();
}
try
{
if (cancellationToken.IsCancellationRequested)
{
throw new OperationCanceledException(cancellationToken);
}
await connection.OpenAsync(cancellationToken);
var transaction = connection.BeginTransaction();
command.Transaction = transaction;
var t = await command.ExecuteNonQueryAsync(cancellationToken);
transaction.Commit();
}
catch (AggregateException e)
{
ExceptionDispatchInfo.Capture(e.InnerException).Throw();
}
finally
{
connection.Close();
}
}
}
return(true);
}
public static void Main(string[] args)
{
var cx = new Npgsql.NpgsqlConnection(
"Server=192.168.12.28; Port=5432; User Id=; Password=; Database=; Timeout=10"
);
Console.WriteLine(cx.State);
cx.Open();
Console.WriteLine(cx.State);
cx.Close();
Console.WriteLine(cx.State);
}
/// <summary>
/// Runs a query and returns value by using a converter
/// </summary>
/// <param name="query">Query to run</param>
/// <param name="connectionString">Connection to use</param>
/// <param name="transformer">Cancellation Token</param>
/// <param name="cancellationToken"></param>
/// <returns>Object</returns>
public static async Task <object> HelperAsync(
string query,
string connectionString,
Func <Npgsql.NpgsqlCommand, CancellationToken, Task <object> > transformer,
CancellationToken cancellationToken)
{
object result = null;
if (Generics.StringExtensions.IsEmpty(query) ||
Generics.StringExtensions.IsEmpty(connectionString) ||
cancellationToken.IsCancellationRequested)
{
return(result);
}
using (Npgsql.NpgsqlConnection connection = new Npgsql.NpgsqlConnection(connectionString))
{
using (Npgsql.NpgsqlCommand command = new Npgsql.NpgsqlCommand(query, connection))
{
command.CommandType = System.Data.CommandType.Text;
try
{
if (cancellationToken.IsCancellationRequested)
{
throw new OperationCanceledException(cancellationToken);
}
await connection.OpenAsync(cancellationToken);
var transaction = connection.BeginTransaction();
command.Transaction = transaction;
result = await transformer(command, cancellationToken);
transaction.Commit();
}
catch (AggregateException e)
{
ExceptionDispatchInfo.Capture(e.InnerException).Throw();
}
finally
{
connection.Close();
}
}
}
return(result);
}
} // End Sub CreateUser
public static void DropCreateDb(string dbName)
{
string sql = @"
CREATE DATABASE " + dbName + @"
WITH OWNER = postgres
ENCODING = 'UTF8'
TABLESPACE = pg_default
LC_COLLATE = 'C'
LC_CTYPE = 'C'
CONNECTION LIMIT = -1;
";
// sql = "CREATE ROLE \"" + System.Environment.UserName + "\" WITH PASSWORD 'TopSecret' LOGIN SUPERUSER CREATEDB CREATEROLE REPLICATION VALID UNTIL 'infinity';";
// System.Console.WriteLine(sql);
using (Npgsql.NpgsqlConnection con = new Npgsql.NpgsqlConnection(GetConnectionString()))
{
using (Npgsql.NpgsqlCommand cmd = con.CreateCommand())
{
if (con.State != System.Data.ConnectionState.Open)
{
con.Open();
}
cmd.CommandText = "SELECT COUNT(*) FROM pg_database WHERE datname = '" + dbName.Replace("'", "''") + "'";
long countOfExistingDbsWithTHisName = (long)cmd.ExecuteScalar();
if (countOfExistingDbsWithTHisName > 0)
{
cmd.CommandText = @"SELECT pg_terminate_backend(pg_stat_activity.pid)
FROM pg_stat_activity
WHERE pg_stat_activity.datname = '" + dbName.Replace("'", "''") + @"'
AND pid <> pg_backend_pid();";
cmd.ExecuteNonQuery();
cmd.CommandText = "DROP DATABASE " + dbName + ";";
cmd.ExecuteNonQuery();
} // End if (dbCount > 0)
cmd.CommandText = sql;
cmd.ExecuteNonQuery();
if (con.State != System.Data.ConnectionState.Closed)
{
con.Close();
}
} // End Using cmd
} // End using con
} // End Sub DropCreateDb
/// <summary>
/// Runs a stored function and returns value by using a converter
/// </summary>
/// <param name="name">Stored function name</param>
/// <param name="param">List of parameter to use. Set null for no parameters.</param>
/// <param name="connectionString">Connection sting to use</param>
/// <param name="transformer">Transformer function</param>
/// <returns>Object</returns>
public static object Helper(
string name,
IEnumerable <Npgsql.NpgsqlParameter> param,
string connectionString,
Func <Npgsql.NpgsqlCommand, object> transformer)
{
object result = null;
if (Generics.StringExtensions.IsEmpty(name) || Generics.StringExtensions.IsEmpty(connectionString))
{
return(result);
}
using (Npgsql.NpgsqlConnection connection = new Npgsql.NpgsqlConnection(connectionString))
{
using (Npgsql.NpgsqlCommand command = new Npgsql.NpgsqlCommand(name, connection))
{
command.CommandType = System.Data.CommandType.StoredProcedure;
if (param != null)
{
foreach (var item in param)
{
command.Parameters.Add(item);
}
}
else
{
command.Parameters.Clear();
}
try
{
connection.Open();
result = transformer(command);
}
catch (AggregateException e)
{
ExceptionDispatchInfo.Capture(e.InnerException).Throw();
}
finally
{
connection.Close();
}
}
}
return(result);
}
public void TestMethod1()
{
using (Npgsql.NpgsqlConnection con = new Npgsql.NpgsqlConnection(ApplicationEnv.Env.ConnectionString))
{
con.Open();
using (Npgsql.NpgsqlCommand command = con.CreateCommand())
{
command.CommandText = "DELETE FROM tran_event_overview WHERE event_id='test1'";
command.ExecuteNonQuery();
}
con.Close();
}
DatabaseManager.Executor.InsertEventOverview("test1", "test1");
}
/// <summary>
/// Returns geometries within the specified bounding box
/// </summary>
/// <param name="bbox"></param>
/// <returns></returns>
public Collection <IGeometry> GetGeometriesInView(IEnvelope bbox)
{
Collection <IGeometry> features = new Collection <IGeometry>();
using (Npgsql.NpgsqlConnection conn = new Npgsql.NpgsqlConnection(_ConnectionString))
{
string strBbox = "box2d('BOX3D(" +
bbox.MinX.ToString(SharpMap.Map.numberFormat_EnUS) + " " +
bbox.MinY.ToString(SharpMap.Map.numberFormat_EnUS) + "," +
bbox.MaxX.ToString(SharpMap.Map.numberFormat_EnUS) + " " +
bbox.MaxY.ToString(SharpMap.Map.numberFormat_EnUS) + ")'::box3d)";
if (this.SRID > 0)
{
strBbox = "setSRID(" + strBbox + "," + this.SRID.ToString(Map.numberFormat_EnUS) + ")";
}
string strSQL = "SELECT AsBinary(" + this.GeometryColumn + ") AS Geom ";
strSQL += "FROM " + this.Table + " WHERE ";
if (!String.IsNullOrEmpty(_defintionQuery))
{
strSQL += this.DefinitionQuery + " AND ";
}
strSQL += this.GeometryColumn + " && " + strBbox;
using (Npgsql.NpgsqlCommand command = new Npgsql.NpgsqlCommand(strSQL, conn))
{
conn.Open();
using (Npgsql.NpgsqlDataReader dr = command.ExecuteReader())
{
while (dr.Read())
{
if (dr[0] != DBNull.Value)
{
IGeometry geom = SharpMap.Converters.WellKnownBinary.GeometryFromWKB.Parse((byte[])dr[0]);
if (geom != null)
{
features.Add(geom);
}
}
}
}
conn.Close();
}
}
return(features);
}
/// <summary>
/// add dataProtectionKey
/// </summary>
/// <param name="dataProtectionKey">Data Protection Key</param>
/// <returns></returns>
public bool Add(DataProtectionKey dataProtectionKey)
{
using (var con = new Npgsql.NpgsqlConnection(_connectionString))
{
var sql = @"INSERT INTO public.""DataProtectionKeys""(""FriendlyName"", ""XmlData"") VALUES(@FriendlyName, @XmlData);";
using (var cmd = new Npgsql.NpgsqlCommand(sql, con))
{
cmd.Parameters.Add(new Npgsql.NpgsqlParameter("@FriendlyName", dataProtectionKey.FriendlyName));
cmd.Parameters.Add(new Npgsql.NpgsqlParameter("@XmlData", dataProtectionKey.XmlData));
con.Open();
var result = cmd.ExecuteNonQuery() > 0;
con.Close();
return(result);
}
}
}
public bool Update(DataProtectionKey dataProtectionKey)
{
using (var con = new Npgsql.NpgsqlConnection(_connectionString))
{
var sql = @"update public.""DataProtectionKeys"" set ""XmlData""=@XmlData where ""FriendlyName""=@FriendlyName;";
using (var cmd = new Npgsql.NpgsqlCommand(sql, con))
{
cmd.Parameters.Add(new Npgsql.NpgsqlParameter("@FriendlyName", dataProtectionKey.FriendlyName));
cmd.Parameters.Add(new Npgsql.NpgsqlParameter("@XmlData", dataProtectionKey.XmlData));
con.Open();
var result = cmd.ExecuteNonQuery() > 0;
con.Close();
return(result);
}
}
}
/// <summary>
/// Returns a datarow based on a RowID
/// </summary>
/// <param name="RowID"></param>
/// <returns>datarow</returns>
public SharpMap.Data.FeatureDataRow GetFeature(uint RowID)
{
using (Npgsql.NpgsqlConnection conn = new Npgsql.NpgsqlConnection(_ConnectionString))
{
string strSQL = "select * , AsBinary(" + this.GeometryColumn + ") As sharpmap_tempgeometry from " + this.Table + " WHERE " + this.ObjectIdColumn + "='" + RowID.ToString() + "'";
using (Npgsql.NpgsqlDataAdapter adapter = new Npgsql.NpgsqlDataAdapter(strSQL, conn))
{
FeatureDataSet ds = new FeatureDataSet();
conn.Open();
adapter.Fill(ds);
conn.Close();
if (ds.Tables.Count > 0)
{
FeatureDataTable fdt = new FeatureDataTable(ds.Tables[0]);
foreach (System.Data.DataColumn col in ds.Tables[0].Columns)
{
if (col.ColumnName != this.GeometryColumn && col.ColumnName != "sharpmap_tempgeometry")
{
fdt.Columns.Add(col.ColumnName, col.DataType, col.Expression);
}
}
if (ds.Tables[0].Rows.Count > 0)
{
System.Data.DataRow dr = ds.Tables[0].Rows[0];
SharpMap.Data.FeatureDataRow fdr = fdt.NewRow();
foreach (System.Data.DataColumn col in ds.Tables[0].Columns)
{
if (col.ColumnName != this.GeometryColumn && col.ColumnName != "sharpmap_tempgeometry")
{
fdr[col.ColumnName] = dr[col];
}
}
fdr.Geometry = SharpMap.Converters.WellKnownBinary.GeometryFromWKB.Parse((byte[])dr["sharpmap_tempgeometry"]);
return(fdr);
}
else
{
return(null);
}
}
else
{
return(null);
}
}
}
}
public string insert(string query)
{
Npgsql.NpgsqlConnection connection = null;
try
{
connection = getConnection();
Npgsql.NpgsqlCommand command = new Npgsql.NpgsqlCommand(query, connection);
return((string)command.ExecuteScalar());
}
finally
{
if (connection != null)
{
connection.Close();
}
}
}
/// <summary>
/// Queries the PostGIS database to get the name of the Geometry Column. This is used if the columnname isn't specified in the constructor
/// </summary>
/// <remarks></remarks>
/// <returns>Name of column containing geometry</returns>
private string GetGeometryColumn()
{
string strSQL = "select f_geometry_column from geometry_columns WHERE f_table_name='" + this.Table + "'";
using (Npgsql.NpgsqlConnection conn = new Npgsql.NpgsqlConnection(_ConnectionString))
using (Npgsql.NpgsqlCommand command = new Npgsql.NpgsqlCommand(strSQL, conn))
{
conn.Open();
object columnname = command.ExecuteScalar();
conn.Close();
if (columnname == System.DBNull.Value)
{
throw new ApplicationException("Table '" + this.Table + "' does not contain a geometry column");
}
return((string)columnname);
}
}
private void button3_Click(object sender, EventArgs e)
{
string _conStr = ConfigurationManager.AppSettings["DB_SK"];
Npgsql.NpgsqlConnection connection = new Npgsql.NpgsqlConnection(_conStr);
connection.Open();
Npgsql.NpgsqlTransaction et = connection.BeginTransaction();
Npgsql.NpgsqlCommand cmd = new Npgsql.NpgsqlCommand("nolstedt01(:p_personnr,:p_contactchannel_id)", connection, et);
cmd.CommandType = CommandType.StoredProcedure;
cmd.Parameters.AddWithValue("p_personnr", "196402230319");
cmd.Parameters.AddWithValue("p_contactchannel_id", "48AF6816C15B49A9956079BD6D8DE561");
cmd.Prepare();
cmd.ExecuteNonQuery();
et.Save("apa");
et.Commit();
connection.Close();
}
public void update(string query)
{
Npgsql.NpgsqlConnection connection = null;
try
{
connection = getConnection();
Npgsql.NpgsqlCommand command = new Npgsql.NpgsqlCommand(query, connection);
command.ExecuteScalar();
}
finally
{
if (connection != null)
{
connection.Close();
}
}
}
/// <summary>
/// Runs a query and returns void
/// </summary>
/// <param name="name">Stored function name</param>
/// <param name="param">List of parameter to use. Set null for no parameters.</param>
/// <param name="connectionString">Connection sting to use</param>
/// <returns>boolean</returns>
public static bool Void(string name, IEnumerable <Npgsql.NpgsqlParameter> param, string connectionString)
{
if (Generics.StringExtensions.IsEmpty(name) || Generics.StringExtensions.IsEmpty(connectionString))
{
return(false);
}
using (Npgsql.NpgsqlConnection connection = new Npgsql.NpgsqlConnection(connectionString))
{
using (Npgsql.NpgsqlCommand command = new Npgsql.NpgsqlCommand(name, connection))
{
command.CommandType = System.Data.CommandType.StoredProcedure;
if (param != null)
{
foreach (var item in param)
{
command.Parameters.Add(item);
}
}
else
{
command.Parameters.Clear();
}
try
{
connection.Open();
var transaction = connection.BeginTransaction();
command.Transaction = transaction;
command.ExecuteNonQuery();
transaction.Commit();
}
catch (AggregateException e)
{
ExceptionDispatchInfo.Capture(e.InnerException).Throw();
}
finally
{
connection.Close();
}
}
}
return(true);
}
public bool UpdateObject(IDBObject dbObject)
{
Npgsql.NpgsqlConnection conn = null;
Npgsql.NpgsqlTransaction trans = null;
Npgsql.NpgsqlConnection readerConn = null;
try
{
if (dbObject != null)
{
conn = (Npgsql.NpgsqlConnection)NpgsqlConnectionImpl.GetInstance().GetNewConnection();
readerConn = (Npgsql.NpgsqlConnection)NpgsqlConnectionImpl.GetInstance().GetNewConnection();
object result = this.GetDataReader(dbObject.GetObjectByIdQuery(), readerConn);
if (result != null)
{
string strSql = dbObject.GetUpdateStatement();
Npgsql.NpgsqlCommand command = new Npgsql.NpgsqlCommand(strSql);
command.Connection = conn;
trans = conn.BeginTransaction();
command.ExecuteNonQuery();
trans.Commit();
return(true);
}
}
}
catch (Exception ex)
{
if (trans != null)
{
trans.Rollback();
}
throw ex;
}
finally
{
if (conn != null)
{
conn.Close();
}
if (readerConn != null)
{
readerConn.Close();
}
}
return(false);
}
/// <summary>
/// Returns geometry Object IDs whose bounding box intersects 'bbox'
/// </summary>
/// <param name="bbox"></param>
/// <returns></returns>
public Collection <uint> GetObjectIDsInView(IEnvelope bbox)
{
Collection <uint> objectlist = new Collection <uint>();
using (Npgsql.NpgsqlConnection conn = new Npgsql.NpgsqlConnection(_ConnectionString))
{
string strBbox = "box2d('BOX3D(" +
bbox.MinX.ToString(SharpMap.Map.numberFormat_EnUS) + " " +
bbox.MinY.ToString(SharpMap.Map.numberFormat_EnUS) + "," +
bbox.MaxX.ToString(SharpMap.Map.numberFormat_EnUS) + " " +
bbox.MaxY.ToString(SharpMap.Map.numberFormat_EnUS) + ")'::box3d)";
if (this.SRID > 0)
{
strBbox = "setSRID(" + strBbox + "," + this.SRID.ToString(Map.numberFormat_EnUS) + ")";
}
string strSQL = "SELECT " + this.ObjectIdColumn + " ";
strSQL += "FROM " + this.Table + " WHERE ";
if (!String.IsNullOrEmpty(_defintionQuery))
{
strSQL += this.DefinitionQuery + " AND ";
}
strSQL += this.GeometryColumn + " && " + strBbox;
using (Npgsql.NpgsqlCommand command = new Npgsql.NpgsqlCommand(strSQL, conn))
{
conn.Open();
using (Npgsql.NpgsqlDataReader dr = command.ExecuteReader())
{
while (dr.Read())
{
if (dr[0] != DBNull.Value)
{
uint ID = (uint)(int)dr[0];
objectlist.Add(ID);
}
}
}
conn.Close();
}
}
return(objectlist);
}
private void textBox1_TextChanged(object sender, EventArgs e)
{
string[] mustHaves = { "Server", "MinPoolSize", "MaxPoolSize", "Port", "Database", "Userid" };
try
{
if (textBox1.Text.Length < 90)
{
return;
}
foreach (string musthave in mustHaves)
{
bool checkPoint = textBox1.Text.Contains(musthave);
if (!checkPoint)
{
return;
}
}
Npgsql.NpgsqlConnection conn = new Npgsql.NpgsqlConnection(textBox1.Text);
conn.Open();
_cs = textBox1.Text;
conn.Close();
textBox1.Text = default;
textBox1.Enabled = false;
CreatTablesButt.Enabled = true;
FolderSelectButton.Enabled = true;
string[] specialValues = { "192.168.2.145", "192.168.1.42", "avalon.geo-volodarsk.com" };
foreach (string specialV in specialValues)
{
if (_cs.Contains(specialV))
{
string newName = System
.Text.Encoding
.UTF8.GetString(Convert
.FromBase64String("0KHQvtC30LTQsNGC0Ywg0YHRgNCw0L3Ri9C1INGC0LDQsdC70LjRhtGL"));
CreatTablesButt.Text = newName;
}
}
}
catch
{
}
}
/// <summary>
/// create DataProtectionKeys
/// </summary>
void CreateTable()
{
using (var con = new Npgsql.NpgsqlConnection(_connectionString))
{
var sql = @"CREATE TABLE if not exists public.""DataProtectionKeys""
(
""FriendlyName"" character varying(256) COLLATE pg_catalog.""default"" NOT NULL,
""XmlData"" text COLLATE pg_catalog.""default"",
CONSTRAINT ""DataProtectionKeys_pkey"" PRIMARY KEY(""FriendlyName"")
)";
using (var cmd = new Npgsql.NpgsqlCommand(sql, con))
{
con.Open();
cmd.ExecuteNonQuery();
con.Close();
}
}
}
public string insertObject <T>(T obj, string tableName)
{
Npgsql.NpgsqlConnection connection = null;
try
{
connection = getConnection();
string query = convertObjectFieldsInQuery(obj, tableName);
Npgsql.NpgsqlCommand command = new Npgsql.NpgsqlCommand(query, connection);
return((string)command.ExecuteScalar());
}
finally
{
if (connection != null)
{
connection.Close();
}
}
}
public int Insert(TickerBase entity)
{
var conn = new Npgsql.NpgsqlConnection(connectionstring);
try
{
conn.Open();
var cmd = entitySql.GetSqlCommandForInsert(entity);
cmd.Connection = conn;
cmd.ExecuteNonQuery();
}
catch (Exception msg)
{
// something went wrong, and you wanna know why
Console.WriteLine(msg.ToString());
}
conn.Close();
return(1);
}
} // End Function GetDrawing
public System.Drawing.Image GetLargeDrawing(int idOfOID)
{
System.Drawing.Image img;
using (Npgsql.NpgsqlConnection connection = new Npgsql.NpgsqlConnection(GetConnectionString()))
{
lock (connection)
{
if (connection.State != System.Data.ConnectionState.Open)
{
connection.Open();
}
using (Npgsql.NpgsqlTransaction trans = connection.BeginTransaction())
{
NpgsqlTypes.LargeObjectManager lbm = new NpgsqlTypes.LargeObjectManager(connection);
NpgsqlTypes.LargeObject lo = lbm.Open(takeOID(idOfOID), NpgsqlTypes.LargeObjectManager.READWRITE); //take picture oid from metod takeOID
byte[] buffer = new byte[32768];
using (System.IO.MemoryStream ms = new System.IO.MemoryStream())
{
int read;
while ((read = lo.Read(buffer, 0, buffer.Length)) > 0)
{
ms.Write(buffer, 0, read);
} // Whend
img = System.Drawing.Image.FromStream(ms);
} // End Using ms
lo.Close();
trans.Commit();
if (connection.State != System.Data.ConnectionState.Closed)
{
connection.Close();
}
} // End Using trans
} // End lock connection
} // End Using connection
return(img);
} // End Function GetLargeDrawing
/// <summary>
/// Returns usable and unusable IP addresses for call.route messages
/// </summary>
/// <returns></returns>
private async Task <List <Customer> > GetIPs()
{
var list = new List <Customer>();
using (Npgsql.NpgsqlConnection con = new Npgsql.NpgsqlConnection(this._connectionString))
{
using (Npgsql.NpgsqlCommand com = new Npgsql.NpgsqlCommand(@"SELECT HOST(address) AS ip, prefix FROM domain.customer_ip
INNER JOIN customer ON customer.id = customer_ip.customer_id
WHERE customer.enabled = TRUE AND customer_ip.enabled = TRUE
AND customer_id IN (SELECT DISTINCT customer_id FROM customer_price WHERE NOW()
BETWEEN valid_from AND valid_to);", con))
{
try
{
com.CommandType = System.Data.CommandType.Text;
await con.OpenAsync();
using (var reader = await com.ExecuteReaderAsync(System.Data.CommandBehavior.SingleResult))
{
while (await reader.ReadAsync())
{
list.Add(new Customer()
{
IP = reader.GetString(0),
Prefix = reader.IsDBNull(1) ? string.Empty : reader.GetString(1)
});
}
}
}
catch (Exception ex)
{
Debug.WriteLine(ex);
}
finally
{
con.Close();
}
}
}
return(list);
}
public void create_server_with_existing_instance_id_and_table_test()
{
Guid instanceId = Guid.NewGuid();
using (var server = new MysticMind.PostgresEmbed.PgServer(
"9.5.5.1",
PG_USER,
addLocalUserAccessPermission: ADD_LOCAL_USER_ACCESS_PERMISSION,
instanceId: instanceId))
{
server.Start();
// assert if instance id drectory exists
Assert.True(Directory.Exists(server.InstanceDir));
// Note: set pooling to false to prevent connecting issues
// https://github.com/npgsql/npgsql/issues/939
string connStr = string.Format(CONN_STR, server.PgPort, PG_USER);
var conn = new Npgsql.NpgsqlConnection(connStr);
var cmd =
new Npgsql.NpgsqlCommand(
"CREATE TABLE table1(ID CHAR(256) CONSTRAINT id PRIMARY KEY, Title CHAR)",
conn);
conn.Open();
cmd.ExecuteNonQuery();
conn.Close();
}
using (var server = new MysticMind.PostgresEmbed.PgServer(
"9.5.5.1",
PG_USER,
addLocalUserAccessPermission: ADD_LOCAL_USER_ACCESS_PERMISSION,
instanceId: instanceId,
clearInstanceDirOnStop: true))
{
server.Start();
// assert if instance id drectory exists
Assert.True(Directory.Exists(server.InstanceDir));
}
}
public int CreateTable(string ticher)
{
var conn = new Npgsql.NpgsqlConnection(connectionstring);
try
{
conn.Open();
var cmd = entitySql.GetSqlCommandForCreateDB(ticher);
cmd.Connection = conn;
cmd.ExecuteNonQuery();
}
catch (Exception msg)
{
// something went wrong, and you wanna know why
Console.WriteLine(msg.ToString());
}
conn.Close();
return(1);
}
public List <TickerBase> SelectALL(string ticher)
{
var conn = new Npgsql.NpgsqlConnection(connectionstring);
try
{
conn.Open();
var cmd = entitySql.GetSqlCommandForGetAll(ticher);
cmd.Connection = conn;
var reader = cmd.ExecuteReader();
return(entitySql.PopulateBusinessObjectFromReader(reader));
}
catch (Exception msg)
{
// something went wrong, and you wanna know why
Console.WriteLine(msg.ToString());
}
conn.Close();
return(new List <TickerBase>());
}
protected override System.Data.IDbCommand getCommand(string storedProcedure)
{
Npgsql.NpgsqlCommand mCommand;
if(CommandsCollection.Contains(storedProcedure))
{
mCommand = (Npgsql.NpgsqlCommand) CommandsCollection[storedProcedure];
}
else
{
Npgsql.NpgsqlConnection Conn = new Npgsql.NpgsqlConnection(this.ConnectionString);
Conn.Open();
mCommand = new Npgsql.NpgsqlCommand(storedProcedure,Conn);
mCommand.CommandType = System.Data.CommandType.StoredProcedure;
Npgsql.NpgsqlCommandBuilder.DeriveParameters(mCommand);
Conn.Close();
Conn.Dispose();
CommandsCollection.Add(storedProcedure, mCommand);
}
mCommand.Connection = (Npgsql.NpgsqlConnection) this.Connection;
return (System.Data.IDbCommand) mCommand;
}
void connection(String a)
{
DataSet ds = new DataSet();
DataTable dt = new DataTable();
using (Npgsql.NpgsqlConnection connection = new Npgsql.NpgsqlConnection(ConfigurationSettings.AppSettings["ConnectionString"]))
{
connection.Open();
String chart_select = "SELECT title FROM movie WHERE id = " + a;
NpgsqlDataAdapter da = new NpgsqlDataAdapter(chart_select, connection);
connection.Close();
IMDb imdb = new IMDb("The Godfather", true);
String tittl = imdb.Id;
Label1.Text = Label1.Text + imdb.Title;
}
}
/// <summary>
/// Returns geometries within the specified bounding box
/// </summary>
/// <param name="bbox"></param>
/// <returns></returns>
public System.Collections.Generic.List<SharpMap.Geometries.Geometry> GetGeometriesInView(SharpMap.Geometries.BoundingBox bbox)
{
System.Collections.Generic.List<SharpMap.Geometries.Geometry> features = new Collection<IGeometry>();
using (Npgsql.NpgsqlConnection conn = new Npgsql.NpgsqlConnection(_ConnectionString))
{
string strBbox = "box2d('BOX3D(" +
bbox.MinX.ToString(SharpMap.Map.numberFormat_EnUS) + " " +
bbox.MinY.ToString(SharpMap.Map.numberFormat_EnUS) + "," +
bbox.MaxX.ToString(SharpMap.Map.numberFormat_EnUS) + " " +
bbox.MaxY.ToString(SharpMap.Map.numberFormat_EnUS) + ")'::box3d)";
if (this.SRID > 0)
strBbox = "setSRID(" + strBbox + "," + this.SRID.ToString(Map.numberFormat_EnUS) + ")";
string strSQL = "SELECT AsBinary(" + this.GeometryColumn + ") AS Geom ";
strSQL += "FROM " + this.Table + " WHERE ";
if (!String.IsNullOrEmpty(_defintionQuery))
strSQL += this.DefinitionQuery + " AND ";
strSQL += this.GeometryColumn + " && " + strBbox;
using (Npgsql.NpgsqlCommand command = new Npgsql.NpgsqlCommand(strSQL, conn))
{
conn.Open();
using (Npgsql.NpgsqlDataReader dr = command.ExecuteReader())
{
while (dr.Read())
{
if (dr[0] != DBNull.Value)
{
IGeometry geom = SharpMap.Converters.WellKnownBinary.GeometryFromWKB.Parse((byte[])dr[0]);
if(geom!=null)
features.Add(geom);
}
}
}
conn.Close();
}
}
return features;
}
/// <summary>
/// Returns the geometry corresponding to the Object ID
/// </summary>
/// <param name="oid">Object ID</param>
/// <returns>geometry</returns>
public SharpMap.Geometries.Geometry GetGeometryByID(uint oid)
{
IGeometry geom = null;
using (Npgsql.NpgsqlConnection conn = new Npgsql.NpgsqlConnection(_ConnectionString))
{
string strSQL = "SELECT AsBinary(" + this.GeometryColumn + ") AS Geom FROM " + this.Table + " WHERE " + this.ObjectIdColumn + "='" + oid.ToString() + "'";
conn.Open();
using (Npgsql.NpgsqlCommand command = new Npgsql.NpgsqlCommand(strSQL, conn))
{
using (Npgsql.NpgsqlDataReader dr = command.ExecuteReader())
{
while (dr.Read())
{
if (dr[0] != DBNull.Value)
geom = SharpMap.Converters.WellKnownBinary.GeometryFromWKB.Parse((byte[])dr[0]);
}
}
}
conn.Close();
}
return geom;
}
/// <summary>
/// Boundingbox of dataset
/// </summary>
/// <returns>boundingbox</returns>
public SharpMap.Geometries.BoundingBox GetExtents()
{
using (Npgsql.NpgsqlConnection conn = new Npgsql.NpgsqlConnection(_ConnectionString))
{
string strSQL = "SELECT EXTENT(" + this.GeometryColumn + ") FROM " + this.Table;
if (!String.IsNullOrEmpty(_defintionQuery))
strSQL += " WHERE " + this.DefinitionQuery;
using (Npgsql.NpgsqlCommand command = new Npgsql.NpgsqlCommand(strSQL, conn))
{
conn.Open();
object result = command.ExecuteScalar();
conn.Close();
if (result == System.DBNull.Value)
return null;
string strBox = (string)result;
if (strBox.StartsWith("BOX("))
{
string[] vals = strBox.Substring(4, strBox.IndexOf(")")-4).Split(new char[2] { ',', ' ' });
return SharpMap.Converters.Geometries.GeometryFactory.CreateEnvelope(
double.Parse(vals[0], SharpMap.Map.numberFormat_EnUS),
double.Parse(vals[2], SharpMap.Map.numberFormat_EnUS),
double.Parse(vals[1], SharpMap.Map.numberFormat_EnUS),
double.Parse(vals[3], SharpMap.Map.numberFormat_EnUS));
}
else
return null;
}
}
}
/// <summary>
/// Returns the features that intersects with 'geom'
/// </summary>
/// <param name="geom"></param>
/// <param name="ds">FeatureDataSet to fill data into</param>
public void ExecuteIntersectionQuery(SharpMap.Geometries.Geometry geom, FeatureDataSet ds)
{
List<IGeometry> features = new List<IGeometry>();
using (Npgsql.NpgsqlConnection conn = new Npgsql.NpgsqlConnection(_ConnectionString))
{
string strGeom = "GeomFromText('" + geom.AsText() + "')";
if (this.SRID > 0)
strGeom = "setSRID(" + strGeom + "," + this.SRID.ToString() + ")";
string strSQL = "SELECT * , AsBinary(" + this.GeometryColumn + ") As sharpmap_tempgeometry FROM " + this.Table + " WHERE ";
if (!String.IsNullOrEmpty(_defintionQuery))
strSQL += this.DefinitionQuery + " AND ";
strSQL += this.GeometryColumn + " && " + strGeom + " AND distance(" + this.GeometryColumn + ", " + strGeom + ")<0";
using (Npgsql.NpgsqlDataAdapter adapter = new Npgsql.NpgsqlDataAdapter(strSQL, conn))
{
conn.Open();
adapter.Fill(ds);
conn.Close();
if (ds.Tables.Count > 0)
{
FeatureDataTable fdt = new FeatureDataTable(ds.Tables[0]);
foreach (System.Data.DataColumn col in ds.Tables[0].Columns)
if (col.ColumnName != this.GeometryColumn && col.ColumnName != "sharpmap_tempgeometry")
fdt.Columns.Add(col.ColumnName, col.DataType, col.Expression);
foreach (System.Data.DataRow dr in ds.Tables[0].Rows)
{
SharpMap.Data.FeatureDataRow fdr = fdt.NewRow();
foreach (System.Data.DataColumn col in ds.Tables[0].Columns)
if (col.ColumnName != this.GeometryColumn && col.ColumnName != "sharpmap_tempgeometry")
fdr[col.ColumnName] = dr[col];
fdr.Geometry = SharpMap.Converters.WellKnownBinary.GeometryFromWKB.Parse((byte[])dr["sharpmap_tempgeometry"]);
fdt.AddRow(fdr);
}
ds.Tables.Add(fdt);
}
}
}
}
/// <summary>
/// Returns the number of features in the dataset
/// </summary>
/// <returns>number of features</returns>
public int GetFeatureCount()
{
int count = 0;
using (Npgsql.NpgsqlConnection conn = new Npgsql.NpgsqlConnection(_ConnectionString))
{
string strSQL = "SELECT COUNT(*) FROM " + this.Table;
if (!String.IsNullOrEmpty(_defintionQuery))
strSQL += " WHERE " + this.DefinitionQuery;
using (Npgsql.NpgsqlCommand command = new Npgsql.NpgsqlCommand(strSQL, conn))
{
conn.Open();
count = (int)command.ExecuteScalar();
conn.Close();
}
}
return count;
}
/// <summary>
/// Queries the PostGIS database to get the name of the Geometry Column. This is used if the columnname isn't specified in the constructor
/// </summary>
/// <remarks></remarks>
/// <returns>Name of column containing geometry</returns>
private string GetGeometryColumn()
{
string strSQL = "select f_geometry_column from geometry_columns WHERE f_table_name='" + this.Table + "'";
using (Npgsql.NpgsqlConnection conn = new Npgsql.NpgsqlConnection(_ConnectionString))
using (Npgsql.NpgsqlCommand command = new Npgsql.NpgsqlCommand(strSQL, conn))
{
conn.Open();
object columnname = command.ExecuteScalar();
conn.Close();
if (columnname == System.DBNull.Value)
throw new ApplicationException("Table '" + this.Table + "' does not contain a geometry column");
return (string)columnname;
}
}
/// <summary>
/// Returns geometry Object IDs whose bounding box intersects 'bbox'
/// </summary>
/// <param name="bbox"></param>
/// <returns></returns>
public System.Collections.Generic.List<uint> GetObjectIDsInView(SharpMap.Geometries.BoundingBox bbox)
{
Collection<uint> objectlist = new Collection<uint>();
using (Npgsql.NpgsqlConnection conn = new Npgsql.NpgsqlConnection(_ConnectionString))
{
string strBbox = "box2d('BOX3D(" +
bbox.MinX.ToString(SharpMap.Map.numberFormat_EnUS) + " " +
bbox.MinY.ToString(SharpMap.Map.numberFormat_EnUS) + "," +
bbox.MaxX.ToString(SharpMap.Map.numberFormat_EnUS) + " " +
bbox.MaxY.ToString(SharpMap.Map.numberFormat_EnUS) + ")'::box3d)";
if (this.SRID > 0)
strBbox = "setSRID(" + strBbox + "," + this.SRID.ToString(Map.numberFormat_EnUS) + ")";
string strSQL = "SELECT " + this.ObjectIdColumn + " ";
strSQL += "FROM " + this.Table + " WHERE ";
if (!String.IsNullOrEmpty(_defintionQuery))
strSQL += this.DefinitionQuery + " AND ";
strSQL += this.GeometryColumn + " && " + strBbox;
using (Npgsql.NpgsqlCommand command = new Npgsql.NpgsqlCommand(strSQL, conn))
{
conn.Open();
using (Npgsql.NpgsqlDataReader dr = command.ExecuteReader())
{
while (dr.Read())
{
if (dr[0] != DBNull.Value)
{
uint ID = (uint)(int)dr[0];
objectlist.Add(ID);
}
}
}
conn.Close();
}
}
return objectlist;
}
/// <summary>
/// Returns a datarow based on a RowID
/// </summary>
/// <param name="RowID"></param>
/// <returns>datarow</returns>
public SharpMap.Data.FeatureDataRow GetFeature(uint RowID)
{
using (Npgsql.NpgsqlConnection conn = new Npgsql.NpgsqlConnection(_ConnectionString))
{
string strSQL = "select * , AsBinary(" + this.GeometryColumn + ") As sharpmap_tempgeometry from " + this.Table + " WHERE " + this.ObjectIdColumn + "='" + RowID.ToString() + "'";
using (Npgsql.NpgsqlDataAdapter adapter = new Npgsql.NpgsqlDataAdapter(strSQL, conn))
{
FeatureDataSet ds = new FeatureDataSet();
conn.Open();
adapter.Fill(ds);
conn.Close();
if (ds.Tables.Count > 0)
{
FeatureDataTable fdt = new FeatureDataTable(ds.Tables[0]);
foreach (System.Data.DataColumn col in ds.Tables[0].Columns)
if (col.ColumnName != this.GeometryColumn && col.ColumnName != "sharpmap_tempgeometry")
fdt.Columns.Add(col.ColumnName, col.DataType, col.Expression);
if(ds.Tables[0].Rows.Count>0)
{
System.Data.DataRow dr = ds.Tables[0].Rows[0];
SharpMap.Data.FeatureDataRow fdr = fdt.NewRow();
foreach (System.Data.DataColumn col in ds.Tables[0].Columns)
if (col.ColumnName != this.GeometryColumn && col.ColumnName != "sharpmap_tempgeometry")
fdr[col.ColumnName] = dr[col];
fdr.Geometry = SharpMap.Converters.WellKnownBinary.GeometryFromWKB.Parse((byte[])dr["sharpmap_tempgeometry"]);
return fdr;
}
else
return null;
}
else
return null;
}
}
}
/// <summary>
/// Returns all features with the view box
/// </summary>
/// <param name="bbox">view box</param>
/// <param name="ds">FeatureDataSet to fill data into</param>
public void ExecuteIntersectionQuery(SharpMap.Geometries.BoundingBox bbox, SharpMap.Data.FeatureDataSet ds)
{
List<IGeometry> features = new List<IGeometry>();
using (Npgsql.NpgsqlConnection conn = new Npgsql.NpgsqlConnection(_ConnectionString))
{
string strBbox = "box2d('BOX3D(" +
bbox.MinX.ToString(SharpMap.Map.numberFormat_EnUS) + " " +
bbox.MinY.ToString(SharpMap.Map.numberFormat_EnUS) + "," +
bbox.MaxX.ToString(SharpMap.Map.numberFormat_EnUS) + " " +
bbox.MaxY.ToString(SharpMap.Map.numberFormat_EnUS) + ")'::box3d)";
if (this.SRID > 0)
strBbox = "setSRID(" + strBbox + "," + this.SRID.ToString(Map.numberFormat_EnUS) + ")";
string strSQL = "SELECT *, AsBinary(" + this.GeometryColumn + ") AS sharpmap_tempgeometry ";
strSQL += "FROM " + this.Table + " WHERE ";
if (!String.IsNullOrEmpty(_defintionQuery))
strSQL += this.DefinitionQuery + " AND ";
strSQL += this.GeometryColumn + " && " + strBbox;
using (Npgsql.NpgsqlDataAdapter adapter = new Npgsql.NpgsqlDataAdapter(strSQL, conn))
{
conn.Open();
System.Data.DataSet ds2 = new System.Data.DataSet();
adapter.Fill(ds2);
conn.Close();
if (ds2.Tables.Count > 0)
{
FeatureDataTable fdt = new FeatureDataTable(ds2.Tables[0]);
foreach (System.Data.DataColumn col in ds2.Tables[0].Columns)
if (col.ColumnName != this.GeometryColumn && col.ColumnName != "sharpmap_tempgeometry")
fdt.Columns.Add(col.ColumnName,col.DataType,col.Expression);
foreach (System.Data.DataRow dr in ds2.Tables[0].Rows)
{
SharpMap.Data.FeatureDataRow fdr = fdt.NewRow();
foreach(System.Data.DataColumn col in ds2.Tables[0].Columns)
if (col.ColumnName != this.GeometryColumn && col.ColumnName != "sharpmap_tempgeometry")
fdr[col.ColumnName] = dr[col];
fdr.Geometry = SharpMap.Converters.WellKnownBinary.GeometryFromWKB.Parse((byte[])dr["sharpmap_tempgeometry"]);
fdt.AddRow(fdr);
}
ds.Tables.Add(fdt);
}
}
}
}