| protected Dispose ( bool disposing ) : void | ||
| disposing | bool | true when called from Dispose(); /// false when being called from the finalizer. |
| return | void | |
public static bool Connection_Test(ENTITE.Serveur bean)
{
if (bean.Control_())
{
NpgsqlConnection con = new NpgsqlConnection();
try
{
string constr = "PORT=" + bean.getPort + ";TIMEOUT=15;POOLING=True;MINPOOLSIZE=1;MAXPOOLSIZE=20;COMMANDTIMEOUT=20;COMPATIBLE= 2.0.14.3;DATABASE=" + bean.getDatabase + ";HOST=" + bean.getAdresse + ";PASSWORD="******";USER ID=" + bean.getUser + "";
con = new NpgsqlConnection(constr);
con.Open();
return true;
}
catch (NpgsqlException ex)
{
Messages.Exception(ex);
return false;
}
finally
{
con.Close();
con.Dispose();
}
}
return false;
}
public void Empty()
{
var connection = new NpgsqlConnection(ConnectionString);
try
{
connection.Open();
foreach (var tablename in StoreInfo.Tables)
{
using (var command = connection.CreateCommand())
{
command.CommandType = CommandType.Text;
command.CommandText = $@"delete from {tablename};";
command.ExecuteNonQuery();
}
}
}
catch (NpgsqlException exception)
{
throw new Exception($"Could not delete all from table {StoreInfo.Name}; see the inner exception for more information.", exception);
}
finally
{
connection.Dispose();
}
}
public static DBclasses.AsksCategory getAsksCategoriesById(int id)
{
DBclasses.AsksCategory result = new DBclasses.AsksCategory();
Npgsql.NpgsqlConnection connection = new Npgsql.NpgsqlConnection(PetFinder.Code.Constants.NpgsqlConnect);
string queue = "select * from askscategories where \"id\"=" + id.ToString();
Npgsql.NpgsqlCommand command = new Npgsql.NpgsqlCommand(queue, connection);
connection.Open();
Npgsql.NpgsqlDataReader reader = command.ExecuteReader();
while (reader.Read())
{
try
{
result.id = id;
result.name = reader["name"].ToString();
result.photo = reader["photo"].ToString();
result.description = reader["description"].ToString();
}
catch (Exception ex)
{
connection.Close();
connection.Dispose();
return(null);
}
}
return(result);
}
public void Destroy()
{
var connection = new NpgsqlConnection(_connectionString);
try
{
connection.Open();
foreach (var tablename in StoreInfo.Tables)
{
using (var command = connection.CreateCommand())
{
command.CommandType = CommandType.Text;
command.CommandText = String.Format(@"drop table {0};", tablename);
command.ExecuteNonQuery();
}
}
}
catch (NpgsqlException exception)
{
throw new Exception(String.Format("Could not drop table {0}; see the inner exception for more information.", _storeInfo.Name), exception);
}
finally
{
connection.Dispose();
}
}
public static DBclasses.KindOfAnimal getKindOfAnimalsById(int id)
{
DBclasses.KindOfAnimal result = new DBclasses.KindOfAnimal();
Npgsql.NpgsqlConnection connection = new Npgsql.NpgsqlConnection(PetFinder.Code.Constants.NpgsqlConnect);
string queue = "select * from kindofanimals where \"id\"=" + id.ToString();
Npgsql.NpgsqlCommand command = new Npgsql.NpgsqlCommand(queue, connection);
connection.Open();
Npgsql.NpgsqlDataReader reader = command.ExecuteReader();
while (reader.Read())
{
try
{
result.id = id;
result.name = reader["name"].ToString();
result.photo = reader["photo"].ToString();
result.infoURL = reader["infoURL"].ToString();
}
catch (Exception ex)
{
connection.Close();
connection.Dispose();
return(null);
}
}
return(result);
}
public static List <DBclasses.Pet> getAllPets()
{
List <DBclasses.Pet> result = new List <DBclasses.Pet>();
Npgsql.NpgsqlConnection connection = new Npgsql.NpgsqlConnection(PetFinder.Code.Constants.NpgsqlConnect);
string queue = "select * from pets order by id";
Npgsql.NpgsqlCommand command = new Npgsql.NpgsqlCommand(queue, connection);
connection.Open();
Npgsql.NpgsqlDataReader reader = command.ExecuteReader();
while (reader.Read())
{
try
{
DBclasses.Pet row = new DBclasses.Pet();
row.id = (long)reader["id"];
row.kindOfAnimals = (int)reader["kindOfAnimals"];
row.addedByUser = (long)reader["addedByUser"];
row.addedDateTime = reader["addedDateTime"].ToString();
row.lastUpdatedByUser = (long)reader["lastUpdatedByUser"];
row.lastUpdatedDateTime = reader["lastUpdatedDateTime"].ToString();
row.name = reader["name"].ToString();
row.photos = reader["photos"] as string[];
row.code = reader["code"].ToString();
row.sterilised = (bool)reader["sterilised"];
row.pasported = (bool)reader["pasported"];
row.isLosted = (bool)reader["isLosted"];
row.birthDate = reader["birthDate"].ToString();
row.roughlyBirth = (bool)reader["roughlyBirth"];
row.roughlyDay = (bool)reader["roughlyDay"];
row.roughlyMonth = (bool)reader["roughlyMonth"];
row.organizationID = (int)reader["organizationID"];
row.address = reader["address"].ToString();
row.isDisabled = (bool)reader["isDisabled"];
row.disableDescription = reader["disableDescription"].ToString();
row.price = (long)reader["price"];
row.callTimeFrom = reader["_callTimeFrom"].ToString();
row.callTimeTo = reader["_callTimeTo"].ToString();
row.region = (int)reader["region"];
row.phones = reader["phones"] as string[];
row.vaccinated = (bool)reader["vaccinated"];
row.sex = (bool)reader["sex"];
result.Add(row);
}
catch (Exception ex)
{
connection.Close();
connection.Dispose();
return(null);
}
}
return(result);
}
/// <summary>
/// Disposes the data provider.
/// </summary>
/// <param name="disposing">The disposing state.</param>
protected virtual void Dispose(bool disposing)
{
if (disposing)
{
_connection.Close();
_connection.Dispose();
}
_isDisposed = true;
}
private static void ExecuteScript(string filepath)
{
NpgsqlConnection conn = new NpgsqlConnection(ConfigurationManager.ConnectionStrings["Postgres"].ToString());
FileInfo file = new FileInfo(filepath);
string script = file.OpenText().ReadToEnd();
NpgsqlCommand cmd = new NpgsqlCommand(script, conn);
conn.Open();
cmd.ExecuteNonQuery();
conn.Close();
conn.Dispose();
}
public static DBclasses.Organization getOrganizationById(int id)
{
DBclasses.Organization result = new DBclasses.Organization();
Npgsql.NpgsqlConnection connection = new Npgsql.NpgsqlConnection(PetFinder.Code.Constants.NpgsqlConnect);
string queue = "select * from organizations where \"id\"=" + id.ToString();
Npgsql.NpgsqlCommand command = new Npgsql.NpgsqlCommand(queue, connection);
connection.Open();
Npgsql.NpgsqlDataReader reader = command.ExecuteReader();
while (reader.Read())
{
try
{
result.id = id;
result.isDeleted = (bool)reader["isDeleted"];
result.name = reader["name"].ToString();
result.contactPerson = reader["contactPerson"].ToString();
result.emails = reader["emails"] as string[];
result.phones = reader["phones"] as string[];
/* TODO */
result.callTimeFrom = reader["callTimeFrom"].ToString();;
result.callTimeTo = reader["callTimeTo"].ToString();;
result.addresses = reader["addresses"] as string[];
result.photo = reader["photo"].ToString();
result.aboutOrg = reader["aboutOrg"].ToString();
result.needHelp = reader["needHelp"].ToString();
result.donation = reader["donation"].ToString();
result.otherOrg = reader["otherOrg"].ToString();
result.linkToVK = reader["linkToVK"].ToString();
result.linkToFB = reader["linkToFB"].ToString();
result.linkToYT = reader["linkToYT"].ToString();
result.linkToTG = reader["linkToTG"].ToString();
result.linkToIG = reader["linkToIG"].ToString();
result.region = (int)reader["region"];
}
catch (Exception ex)
{
connection.Close();
connection.Dispose();
result.name = ex.StackTrace + ex.Message;
return(result);
}
}
return(result);
}
/// <summary>
/// 关闭数据库
/// </summary>
public void Close()
{
if (this.IsOpen)
{
dbc.Close();
}
if (dbc != null)
{
dbc.Dispose();
dbc = null;
}
//throw new NotImplementedException();
}
public PgMalDataLoader(string connectionString)
{
m_conn = new NpgsqlConnection(connectionString);
try
{
Logging.Log.Debug("Connecting to PostgreSQL.");
m_conn.Open();
Logging.Log.Debug("Connected to PostgreSQL.");
}
catch
{
m_conn.Dispose();
throw;
}
}
public void ConnectWithPool()
{
NpgsqlConnectionStringBuilder csb = new NpgsqlConnectionStringBuilder(Conn.ConnectionString);
csb.Pooling = true;
String conStr = csb.ConnectionString;
using (var metrics = TestMetrics.Start(TestRunTime, true))
{
while (!metrics.TimesUp)
{
var con = new NpgsqlConnection(conStr);
con.Open();
con.Dispose();
metrics.IncrementIterations();
}
}
}
/// <summary>
/// Check that a connection can be established to Postgresql and return the server version
/// </summary>
/// <param name="connectionString">An Npgsql connection string</param>
/// <returns>A <see cref="HealthResponse"/> object that contains the return status of this health check</returns>
public static HealthResponse CheckHealth(string connectionString)
{
try
{
NpgsqlConnection conn = new NpgsqlConnection(connectionString);
conn.Open();
string host = conn.Host;
string version = conn.PostgreSqlVersion.ToString();
int port = conn.Port;
conn.Close();
conn.Dispose();
return HealthResponse.Healthy(new { host = host, port = port , version = version});
}
catch (Exception e)
{
return HealthResponse.Unhealthy(e);
}
}
public static void Deconnection(NpgsqlConnection con)
{
if (con != null)
{
try
{
con.Close();
con.Dispose();
}
catch (NpgsqlException ex)
{
Messages.Exception(ex);
}
finally
{
con = null;
}
}
}
public void GlobalRegistration()
{
NpgsqlConnection.RegisterEnumGlobally<Mood>();
var myconn = new NpgsqlConnection(ConnectionString);
myconn.Open();
const Mood expected = Mood.Ok;
var cmd = new NpgsqlCommand("SELECT @p::MOOD", myconn);
var p = new NpgsqlParameter { ParameterName = "p", Value = expected };
cmd.Parameters.Add(p);
var reader = cmd.ExecuteReader();
reader.Read();
Assert.That(reader.GetFieldType(0), Is.EqualTo(typeof(Mood)));
Assert.That(reader.GetFieldValue<Mood>(0), Is.EqualTo(expected));
Assert.That(reader.GetValue(0), Is.EqualTo(expected));
reader.Close();
cmd.Dispose();
myconn.Dispose();
}
public static DBclasses.User getUserById(long id)
{
DBclasses.User result = new DBclasses.User();
Npgsql.NpgsqlConnection connection = new Npgsql.NpgsqlConnection(PetFinder.Code.Constants.NpgsqlConnect);
string queue = "select * from users where \"id\"=" + id.ToString();
Npgsql.NpgsqlCommand command = new Npgsql.NpgsqlCommand(queue, connection);
connection.Open();
Npgsql.NpgsqlDataReader reader = command.ExecuteReader();
while (reader.Read())
{
try
{
result.id = id;
result.isDeleted = (bool)reader["isDeleted"];
result.rank = (int)reader["rank"];
result.organizationID = (int)reader["organizationID"];
result.name = reader["name"].ToString();
result.email = reader["email"].ToString();
result.phone1 = reader["phone1"].ToString();
result.phone2 = reader["phone2"].ToString();
result.getAsksInfo = reader["getAsksInfo"] as int[];
result.photo = reader["photo"].ToString();
/* TODO */
result.callTimeFrom = reader["callTimeFrom"].ToString();;
result.callTimeTo = reader["callTimeTo"].ToString();;
result.region = (int)reader["region"];
}
catch (Exception ex)
{
connection.Close();
connection.Dispose();
result.name = ex.StackTrace + ex.Message;
return(result);
}
}
return(result);
}
public static DBclasses.ArchivePet getArchivePetById(int id)
{
DBclasses.ArchivePet result = new DBclasses.ArchivePet();
Npgsql.NpgsqlConnection connection = new Npgsql.NpgsqlConnection(PetFinder.Code.Constants.NpgsqlConnect);
string queue = "select * from archivepets where \"id\"=" + id.ToString();
Npgsql.NpgsqlCommand command = new Npgsql.NpgsqlCommand(queue, connection);
connection.Open();
Npgsql.NpgsqlDataReader reader = command.ExecuteReader();
while (reader.Read())
{
try
{
result.id = id;
result.kindOfAnimals = (int)reader["kindOfAnimals"];
result.addedByUser = (long)reader["addedByUser"];
result.addedDateTime = reader["addedDateTime"].ToString();
result.organizationID = (int)reader["organizationID"];
result.name = reader["name"].ToString();
result.photos = reader["photos"] as string[];
result.homeFound = (bool)reader["homeFound"];
result.newOwnerName = reader["newOwnerName"].ToString();
result.newOwnerPhone = reader["newOwnerPhone"].ToString();
result.lastContactWithNewOwner = reader["lastContactWithNewOwner"].ToString();
}
catch (Exception ex)
{
connection.Close();
connection.Dispose();
result.name = ex.StackTrace + ex.Message;
return(result);
}
}
return(result);
}
public static DBclasses.Ask getAskById(int id)
{
DBclasses.Ask result = new DBclasses.Ask();
Npgsql.NpgsqlConnection connection = new Npgsql.NpgsqlConnection(PetFinder.Code.Constants.NpgsqlConnect);
string queue = "select * from asks where \"id\"=" + id.ToString();
Npgsql.NpgsqlCommand command = new Npgsql.NpgsqlCommand(queue, connection);
connection.Open();
Npgsql.NpgsqlDataReader reader = command.ExecuteReader();
while (reader.Read())
{
try
{
result.id = id;
result.isDeleted = (bool)reader["isDeleted"];
result.addedByUser = (long)reader["addedByUser"];
result.addedDateTime = reader["addedDateTime"].ToString();
result.lastUpdatedByUser = (long)reader["lastUpdatedByUser"];
result.lastUpdatedDateTime = reader["lastUpdatedDateTime"].ToString();
result.categories = reader["categories"] as int[];
result.name = reader["name"].ToString();
result.desciption = reader["desciption"].ToString();
result.organizationID = (int)reader["organizationID"];
}
catch (Exception ex)
{
connection.Close();
connection.Dispose();
result.name = ex.StackTrace + ex.Message;
return(result);
}
}
return(result);
}
/// <summary>
///
/// </summary>
/// <param name="db"></param>
/// <param name="cn"></param>
/// <param name="com"></param>
private static void ConnectionCreate(string db, out NpgsqlConnection cn, out NpgsqlCommand com)
{
Npgsql_Class npgsql_class = new Npgsql_Class();
String connectionString = npgsql_class.connectionstring(db);
cn = new NpgsqlConnection(connectionString);
if (cn.State.ToString() == "Open")
{
cn.Close();
cn.Dispose();
}
cn.Open();
com = cn.CreateCommand();
}
private void Connect ()
{
try {
string connectionString = String.Format("Server={0};Database={1};User ID={2};Password={3};",
Configuration.DatabaseHost,
Configuration.DatabaseName,
Configuration.DatabaseUser,
Configuration.DatabasePassword
);
if (Configuration.DatabasePort != 0)
connectionString += string.Format ("Port={0};", Configuration.DatabasePort);
dbcon = new NpgsqlConnection (connectionString);
log.DebugFormat ("Connecting to database, connection string: {0}", connectionString);
dbcon.Open ();
object db_now_obj = ExecuteScalar ("SELECT now();");
DateTime db_now;
DateTime machine_now = DateTime.Now;
db_now = (DateTime) db_now_obj;
db_time_difference = db_now - machine_now;
log.DebugFormat ("DB now: {0:yyyy/MM/dd HH:mm:ss.ffff}, current machine's now: {1:yyyy/MM/dd HH:mm:ss.ffff}, adjusted now: {3}, diff: {2:yyyy/MM/dd HH:mm:ss.ffff} ms", db_now, machine_now, db_time_difference.TotalMilliseconds, Now);
} catch (Exception) {
if (dbcon != null) {
dbcon.Dispose ();
dbcon = null;
}
throw;
}
}
PostgresTableMetadata IPostgresStore.GetOrCreateTable(Type type)
{
lock (_lock)
{
if (_tables.ContainsKey(type))
{
return _tables[type];
}
var table = new PostgresTableMetadata(type);
var connection = new NpgsqlConnection(_connectionString);
try
{
connection.Open();
using (var command = connection.CreateCommand())
{
command.CommandType = CommandType.Text;
command.CommandText = String.Format(@"
CREATE TABLE IF NOT EXISTS public.{0}
(
id uuid NOT NULL DEFAULT md5(random()::text || clock_timestamp()::text)::uuid,
body json NOT NULL,
created timestamp without time zone NOT NULL DEFAULT now(),
row_version integer NOT NULL DEFAULT 1,
CONSTRAINT pk_{0} PRIMARY KEY (id)
);", table.Name);
command.ExecuteNonQuery();
}
}
catch (NpgsqlException exception)
{
throw new Exception(String.Format("Could not create table {0}; see the inner exception for more information.", table.Name), exception);
}
finally
{
connection.Dispose();
}
_tables[type] = table;
return table;
}
}
private void Connect ()
{
try {
string connectionString;
connectionString = "Server=" + Configuration.DatabaseHost + ";";
connectionString += "Database=builder;User ID=builder;";
if (!string.IsNullOrEmpty (Configuration.DatabasePort))
connectionString += "Port=" + Configuration.DatabasePort + ";";
dbcon = new NpgsqlConnection (connectionString);
Logger.Log (2, "Database connection string: {0}", connectionString);
dbcon.Open ();
object db_now_obj = ExecuteScalar ("SELECT now();");
DateTime db_now;
DateTime machine_now = DateTime.Now;
const string format = "yyyy/MM/dd HH:mm:ss.ffff";
if (db_now_obj is DateTime) {
db_now = (DateTime) db_now_obj;
} else {
Logger.Log ("now () function return value of type: {0}", db_now_obj == null ? "null" : db_now_obj.GetType ().FullName);
db_now = machine_now;
}
db_time_difference = db_now - machine_now;
Logger.Log (2, "DB now: {0}, current machine's now: {1}, adjusted now: {3}, diff: {2} ms", db_now.ToString (format), machine_now.ToString (format), db_time_difference.TotalMilliseconds, Now.ToString (format));
} catch {
if (dbcon != null) {
dbcon.Dispose ();
dbcon = null;
}
throw;
}
}
public void NpgsqlErrorRepro2()
{
NpgsqlConnection connection = new NpgsqlConnection(TheConnectionString);
connection.Open();
NpgsqlTransaction transaction = connection.BeginTransaction();
LargeObjectManager largeObjectMgr = new LargeObjectManager(connection);
try
{
LargeObject largeObject = largeObjectMgr.Open(-1, LargeObjectManager.READWRITE);
transaction.Commit();
}
catch
{
// ignore the LO failure
try
{
transaction.Dispose();
}
catch
{
// ignore dispose failure
}
try
{
connection.Dispose();
}
catch
{
// ignore dispose failure
}
}
using (connection = new NpgsqlConnection(TheConnectionString))
{
connection.Open();
using (NpgsqlCommand command = connection.CreateCommand())
{
command.CommandText = "SELECT * FROM pg_database";
using (NpgsqlDataReader reader = command.ExecuteReader())
{
Assert.IsTrue(reader.Read());
// *1* this fails if the connection for the pool happens to be the bad one from above
Assert.IsTrue(!String.IsNullOrEmpty((string)reader["datname"]));
}
}
}
}
public void GetConnectionState()
{
// Test created to PR #164
NpgsqlConnection c = new NpgsqlConnection();
c.Dispose();
Assert.AreEqual(ConnectionState.Closed, c.State);
}
private IEnumerable<TransactionEvent> GetTransactionEventsUsingQuery(NpgsqlCommand command)
{
NpgsqlConnection connection = null;
NpgsqlDataReader reader = null;
try
{
connection = new NpgsqlConnection(_connectionString);
connection.Open();
command.Connection = connection;
reader = command.ExecuteReader();
if (reader.HasRows)
{
while (reader.Read())
{
yield return GetTransactionEventFromRecord(reader);
}
}
}
finally
{
if (reader != null)
{
reader.Close();
reader.Dispose();
}
if (command != null)
{
command.Dispose();
}
if (connection != null)
{
connection.Close();
connection.Dispose();
}
}
}
public void TestRandomWgs84Points()
{
var succededProjNet = 0;
var failedProjNet = 0;
var exceptedProjNet = 0;
var succededDotSpatial = 0;
var failedDotSpatial = 0;
var exceptedDotSpatial = 0;
var cnProj4 = new NpgsqlConnection(Properties.Resources.PgConnectionString);
var cnSource = new NpgsqlConnection(Properties.Resources.PgConnectionString);
var cnTarget = new NpgsqlConnection(Properties.Resources.PgConnectionString);
cnProj4.Open();
cnSource.Open();
cnTarget.Open();
using (var cmdProj4 = cnProj4.CreateCommand())
using (var cmdSource = cnSource.CreateCommand())
using (var cmdTarget = cnTarget.CreateCommand())
{
cmdProj4.CommandText =
"SELECT st_x(tp.point), st_y(tp.point) FROM (SELECT st_transform(st_setsrid(st_makepoint(@px, @py), @psrid), @ptsrid) as point) AS tp;";
var p4p = cmdProj4.Parameters;
p4p.AddRange(
new[]
{
new NpgsqlParameter("@px", DbType.Double),
new NpgsqlParameter("@py", DbType.Double),
new NpgsqlParameter("@psrid", DbType.Int32),
new NpgsqlParameter("@ptsrid", DbType.Int32),
});
cmdSource.CommandText = "SELECT \"srid\", \"srtext\", \"proj4text\" FROM \"spatial_ref_sys\";";
cmdTarget.CommandText =
"SELECT \"srid\", \"srtext\", \"proj4text\" FROM \"spatial_ref_sys\" WHERE \"srid\">@psrid;";
cmdTarget.Parameters.Add("@psrid", NpgsqlDbType.Integer);
using (var sourceReader = cmdSource.ExecuteReader())
{
while (sourceReader.Read())
{
var srid = sourceReader.GetInt32(0);
cmdTarget.Parameters[0].Value = cmdProj4.Parameters[2].Value = srid;
ICoordinateSystem projNetSource;
try
{
projNetSource =
Factory.CreateFromWkt(sourceReader.GetString(1));
}
catch
{
projNetSource = null;
}
ProjectionInfo DSProjSource;
try
{
DSProjSource =
ProjectionInfo.FromProj4String(sourceReader.GetString(2));
}
catch
{
DSProjSource = null;
}
using (var targetReader = cmdTarget.ExecuteReader(CommandBehavior.Default))
{
while (targetReader.Read())
{
var targetSrid = targetReader.GetInt32(0);
ICoordinateSystem projNetTarget = null;
if (projNetSource != null)
{
try
{
projNetTarget =
Factory.CreateFromWkt(targetReader.GetString(1));
}
catch
{
projNetTarget = null;
}
}
ProjectionInfo DSProjTarget = null;
if (DSProjSource != null)
{
try
{
DSProjTarget =
ProjectionInfo.FromProj4String(targetReader.GetString(2));
}
catch
{
DSProjTarget = null;
}
}
foreach (double[] randomOrdinate in GetRandomOrdinates())
{
//Get source coordinates
p4p[0].Value = randomOrdinate[0];
p4p[1].Value = randomOrdinate[1];
p4p[2].Value = 4326;
p4p[3].Value = srid;
try
{
using (var proj4Reader = cmdProj4.ExecuteReader(CommandBehavior.SingleRow))
{
proj4Reader.Read();
randomOrdinate[0] = proj4Reader.GetDouble(0);
randomOrdinate[1] = proj4Reader.GetDouble(1);
}
}
catch
{
continue;
}
p4p[0].Value = randomOrdinate[0];
p4p[1].Value = randomOrdinate[1];
p4p[2].Value = srid;
p4p[3].Value = targetSrid;
var result = new double[2];
try
{
using (var proj4Reader = cmdProj4.ExecuteReader(CommandBehavior.SingleRow))
{
proj4Reader.Read();
result[0] = proj4Reader.GetDouble(0);
result[1] = proj4Reader.GetDouble(1);
}
}
catch
{
continue;
}
if (projNetSource != null && projNetTarget != null)
{
try
{
var ts = TransformationFactory.CreateFromCoordinateSystems(
projNetSource,
projNetTarget);
var projNetResult = TestForwardAndBackProjNet(ts.MathTransform,
randomOrdinate,
ref succededProjNet,
ref failedProjNet,
ref exceptedProjNet);
var dx = projNetResult[0] - result[0];
var dy = projNetResult[1] - result[1];
if (Math.Abs(dx) > 1d || Math.Abs(dy) > 1d)
{
failedProjNet++;
Console.WriteLine(
string.Format(
"Failed ProjNet {0} -> {1} for ({2}, {3}). [{4}, {5}]",
srid, targetSrid,
randomOrdinate[0], randomOrdinate[1],
dx, dy));
}
}
catch (Exception)
{
exceptedProjNet++;
}
}
else
exceptedProjNet++;
if (DSProjSource != null && DSProjTarget != null)
{
var dsResult = TestForwardAndBackDotSpatial(DSProjSource, DSProjTarget,
randomOrdinate,
ref succededDotSpatial,
ref failedDotSpatial,
ref exceptedDotSpatial);
var dx = dsResult[0] - result[0];
var dy = dsResult[1] - result[1];
if (Math.Abs(dx) > 1d ||
Math.Abs(dy) > 1d)
{
failedProjNet++;
Console.WriteLine(
string.Format(
"Failed DotSpatial {0} -> {1} for ({2}, {3}). [{4}, {5}]",
srid, targetSrid,
randomOrdinate[0], randomOrdinate[1],
dx, dy));
}
}
}
}
}
}
}
}
cnTarget.Dispose();
cnSource.Dispose();
cnProj4.Dispose();
}
private void Button_Click(object sender, RoutedEventArgs e)
{
this.WgsRows = new List<WgsRow>();
NpgsqlConnection connection = new NpgsqlConnection("HOST=192.168.0.12;PORT=5432;DATABASE=test;USER ID=postgres;PASSWORD=postgres;PRELOADREADER=true;");
connection.Open();
FileStream stream = new FileStream(@"C:\Users\loic.EMASH\Documents\GitHub\GeoPat\Data\APRR\Data\WGS_INF.CSV", FileMode.Open);
StreamReader reader = new StreamReader(stream, System.Text.Encoding.GetEncoding(1252));
reader.ReadLine();
String line = null;
while ((line = reader.ReadLine()) != null)
{
String[] strs = line.Split(";".ToCharArray(), StringSplitOptions.RemoveEmptyEntries);
WgsRow row = new WgsRow();
row.LineIndex = Int32.Parse(strs[0]);
row.LayerName = strs[1];
row.Liaison = strs[2];
row.Sens = strs[3];
row.AbsDeb = int.Parse(strs[4].Replace(",", "."), System.Globalization.NumberStyles.AllowDecimalPoint, System.Globalization.CultureInfo.InvariantCulture);
row.AbsFin = int.Parse(strs[5].Replace(",", "."), System.Globalization.NumberStyles.AllowDecimalPoint, System.Globalization.CultureInfo.InvariantCulture);
row.X1 = double.Parse(strs[6].Replace(",", "."), System.Globalization.NumberStyles.AllowDecimalPoint, System.Globalization.CultureInfo.InvariantCulture);
row.Y1 = double.Parse(strs[7].Replace(",", "."), System.Globalization.NumberStyles.AllowDecimalPoint, System.Globalization.CultureInfo.InvariantCulture);
row.X2 = double.Parse(strs[8].Replace(",", "."), System.Globalization.NumberStyles.AllowDecimalPoint, System.Globalization.CultureInfo.InvariantCulture);
row.Y2 = double.Parse(strs[9].Replace(",", "."), System.Globalization.NumberStyles.AllowDecimalPoint, System.Globalization.CultureInfo.InvariantCulture);
this.WgsRows.Add(row);
}
reader.Close();
reader.Dispose();
stream.Close();
stream.Dispose();
Console.WriteLine(this.WgsRows.Count + " lignes chargées");
List<String> liaisons = (from r in WgsRows where r.LayerName.Equals ("SIG_REF_DETAIL") select r.Liaison).Distinct().ToList();
foreach (String liaison in liaisons)
{
List<String> senss = (from r in WgsRows where r.Liaison.Equals(liaison) && r.LayerName.Equals("SIG_REF_DETAIL") select r.Sens).Distinct().ToList();
foreach (String sens in senss)
{
List<int> lineIndexes = (from r in WgsRows where r.Liaison.Equals(liaison) && r.Sens.Equals(sens) && r.LayerName.Equals("SIG_REF_DETAIL") select r.LineIndex).Distinct().ToList();
if (lineIndexes.Count == 1)
{
List<String> strCoords = new List<string>();
//LINESTRING(3.45 4.23,10 50,20 25)
List<WgsRow> rowLines = (from r in WgsRows where r.Liaison.Equals(liaison) && r.Sens.Equals(sens) && r.LineIndex == lineIndexes[0] && r.LayerName.Equals("SIG_REF_DETAIL") orderby r.LineIndex, r.AbsDeb select r).Distinct().ToList();
foreach (WgsRow rowLine in rowLines)
{
strCoords.Add(rowLine.X1.ToString().Replace(",", ".") + " " + rowLine.Y1.ToString().Replace(",", "."));
strCoords.Add(rowLine.X2.ToString().Replace(",", ".") + " " + rowLine.Y2.ToString().Replace(",", "."));
}
String wktString = "LINESTRING(" + string.Join(",", strCoords) + ")";
String sqlIdChaussee = "select inf_chaussee__id from inf.inf_chaussee,inf.inf_liaison where inf_chaussee.inf_chaussee__code='" + sens + "' and inf_liaison.inf_liaison__code='" + liaison + "' and inf_chaussee.inf_liaison__id=inf_liaison.inf_liaison__id";
NpgsqlCommand command = new NpgsqlCommand(sqlIdChaussee, connection);
NpgsqlDataReader readerData = command.ExecuteReader();
Nullable<Int32> idChaussee = null;
if (readerData.Read())
{idChaussee = Int32.Parse(readerData.GetValue(0).ToString());}
readerData.Close();
readerData.Dispose();
if (idChaussee.HasValue)
{
String sqlUpdateGem = "update inf.inf_chaussee set inf_chaussee__geom='" + wktString + "' where inf_chaussee__id=" + idChaussee.Value;
command = new NpgsqlCommand(sqlUpdateGem, connection);
command.ExecuteNonQuery();
}
}
else
{
List<String> lineCoords = new List<string>();
foreach (int lineIndex in lineIndexes)
{
List<String> strCoords = new List<string>();
List<WgsRow> rowLines = (from r in WgsRows where r.Liaison.Equals(liaison) && r.Sens.Equals(sens) && r.LineIndex == lineIndex && r.LayerName.Equals("SIG_REF_DETAIL") orderby r.LineIndex, r.AbsDeb select r).Distinct().ToList();
foreach (WgsRow rowLine in rowLines)
{
strCoords.Add(rowLine.X1.ToString().Replace(",", ".") + " " + rowLine.Y1.ToString().Replace(",", "."));
strCoords.Add(rowLine.X2.ToString().Replace(",", ".") + " " + rowLine.Y2.ToString().Replace(",", "."));
}
lineCoords.Add("("+String.Join(",", strCoords)+")");
}
String wktString = "MULTILINESTRING(" + string.Join(",", lineCoords) + ")";
String sqlIdChaussee = "select inf_chaussee__id from inf.inf_chaussee,inf.inf_liaison where inf_chaussee.inf_chaussee__code='" + sens + "' and inf_liaison.inf_liaison__code='" + liaison + "' and inf_chaussee.inf_liaison__id=inf_liaison.inf_liaison__id";
NpgsqlCommand command = new NpgsqlCommand(sqlIdChaussee, connection);
NpgsqlDataReader readerData = command.ExecuteReader();
Nullable<Int32> idChaussee = null;
if (readerData.Read())
{ idChaussee = Int32.Parse(readerData.GetValue(0).ToString()); }
readerData.Close();
readerData.Dispose();
if (idChaussee.HasValue)
{
String sqlUpdateGem = "update inf.inf_chaussee set inf_chaussee__geom='" + wktString + "' where inf_chaussee__id=" + idChaussee.Value;
command = new NpgsqlCommand(sqlUpdateGem, connection);
command.ExecuteNonQuery();
}
}
}
}
connection.Close();
connection.Dispose();
}
