protected Dispose ( bool disposing ) : void | ||
disposing | bool | true when called from Dispose(); /// false when being called from the finalizer. |
Résultat | 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(); }