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 IEnumerable <CalendarEvent> GetEventsByUserId(int userid) { IList <CalendarEvent> userEvents = new List <CalendarEvent>(); using (Npgsql.NpgsqlConnection conn = new Npgsql.NpgsqlConnection(Infrastructure.ConfigReader.ConnectionString.ToString())) { conn.Open(); using (Npgsql.NpgsqlCommand command = new Npgsql.NpgsqlCommand(EventByUserIdQuery, conn)) { command.Parameters.Add(new Npgsql.NpgsqlParameter("userid", NpgsqlTypes.NpgsqlDbType.Integer)); command.Prepare(); command.Parameters[0].Value = userid; using (Npgsql.NpgsqlDataReader dr = command.ExecuteReader()) { while (dr.Read()) { CalendarEvent newEvent = populateEventsFromDB(dr); userEvents.Add(newEvent); } } } } return(userEvents); }
private void CreateMatrixMdb() { using ( var connPoints = new Npgsql.NpgsqlConnection( "server=127.0.0.1;port=5432;database=ivv-projekte;uid=postgres;password=1.Kennwort")) { connPoints.Open(); using ( var pointsReader = new Npgsql.NpgsqlCommand( "WITH t as (SELECT zellnr, st_pointonsurface(wkb_geometry) as g FROM \"DFN2950\".\"fuerimport\") " + "SELECT zellnr, st_X(g), st_Y(g) FROM t ORDER BY zellnr;", connPoints).ExecuteReader()) { using ( var connMatrix = new Npgsql.NpgsqlConnection( "server=127.0.0.1;port=5432;database=ivv-projekte;uid=postgres;password=1.Kennwort")) { connMatrix.Open(); using ( var matrixReader = new Npgsql.NpgsqlCommand( "SELECT origin, destin, val FROM \"DFN2950\".\"iv-kfz\" " + "WHERE val > 10 " + "ORDER BY origin, destin;", connMatrix).ExecuteReader()) _matrix = new ODMatrix(pointsReader, matrixReader); } } } }
public async Task create_server_async_and_table_test() { using (var server = new MysticMind.PostgresEmbed.PgServer( "9.5.5.1", PG_USER, addLocalUserAccessPermission: ADD_LOCAL_USER_ACCESS_PERMISSION, clearInstanceDirOnStop: true)) { await server.StartAsync(); // 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); await conn.OpenAsync(); await cmd.ExecuteNonQueryAsync(); conn.Close(); } }
protected static void RunScript(string script, IDictionary <string, Npgsql.NpgsqlParameter> parameters, Npgsql.NpgsqlConnection conn, Npgsql.NpgsqlTransaction trans) { if (parameters == null) { parameters = new Dictionary <string, Npgsql.NpgsqlParameter>(); } foreach (var commandText in script.Split(';')) { var sql = commandText + ";"; _logger.Debug(d => d("Script command: {0}", sql)); using (var cmd = new Npgsql.NpgsqlCommand(sql, conn, trans)) { foreach (var parameter in parameters.Keys) { if (sql.Contains(parameter)) { var value = parameters[parameter]; _logger.Debug(d => d("The command contains the parameter '{0}', setting value to '{1}'...", parameter, value.Value)); cmd.Parameters.Add(value); } } cmd.ExecuteNonQuery(); } } _logger.Debug(d => d("Script complete.")); }
protected static bool IsSuperUser(Npgsql.NpgsqlConnection conn, Npgsql.NpgsqlTransaction trans) { using (var cmd = new Npgsql.NpgsqlCommand(IS_SUPERUSER, conn, trans)) { return(HasRows(cmd)); } }
public bool Login(string user, string password) { using (var connection = GetConnection()) { Console.Write(user, password); var sql = "SELECT * FROM users where username="******"'" + user + "' and password= '******'"; connection.Open(); using var cmd = new Npgsql.NpgsqlCommand(sql, connection); Npgsql.NpgsqlDataReader dr = cmd.ExecuteReader(); if (dr.HasRows) { while (dr.Read()) { UserLoginCache.idUser = dr.GetInt32(0); UserLoginCache.Username = dr.GetString(1); UserLoginCache.Email = dr.GetString(3); UserLoginCache.F_name = dr.GetString(4); UserLoginCache.L_name = dr.GetString(5); UserLoginCache.Role = dr.GetString(6); } return(true); } else { return(false); } } }
public IList <DdvDoctor> GetByGroupName(string groupName) { IList <DdvDoctor> list = new List <DdvDoctor>(); using (dynamic connection = connectionFactory.GetConnection()) { String sql = String.Format("SELECT d.r_object_id, d.dss_full_name, d.dss_middle_name, d.dss_first_name, d.r_modify_date," + " d.dss_short_name, d.r_creation_date, d.dss_last_name FROM ddv_doctor d, dm_group_users g WHERE g.dss_group_name = '{0}'" + " AND d.r_object_id = g.dsid_doctor_id", groupName); Logger.Debug(CultureInfo.CurrentCulture, "SQL: {0}", sql); Npgsql.NpgsqlCommand command = new Npgsql.NpgsqlCommand(sql, connection); using (DbDataReader reader = command.ExecuteReader()) { while (reader.Read()) { DdvDoctor obj = new DdvDoctor(); obj.ObjectId = reader.IsDBNull(0) ? null : reader.GetString(0); obj.FullName = reader.IsDBNull(1) ? null : reader.GetString(1); obj.MiddleName = reader.IsDBNull(2) ? null : reader.GetString(2); obj.FirstName = reader.IsDBNull(3) ? null : reader.GetString(3); obj.ModifyDate = reader.IsDBNull(4) ? DateTime.MinValue : reader.GetDateTime(4); obj.ShortName = reader.IsDBNull(5) ? null : reader.GetString(5); obj.CreationDate = reader.IsDBNull(6) ? DateTime.MinValue : reader.GetDateTime(6); obj.LastName = reader.IsDBNull(7) ? null : reader.GetString(7); list.Add(obj); } } } return(list); }
public IList <DdvDoctor> GetByConsiliumGroupId(string consiliumGroupId) { IList <DdvDoctor> list = new List <DdvDoctor>(); using (dynamic connection = connectionFactory.GetConnection()) { String sql = String.Format("SELECT d.r_object_id, dss_full_name, dss_middle_name, dss_first_name, d.r_modify_date, dss_short_name, d.r_creation_date, dss_last_name, gm.dss_name " + "FROM ddt_consilium_group cg, ddt_consilium_group_member gm, ddv_doctor d " + "WHERE cg.r_object_id = gm.dsid_group and gm.dsid_doctor = d.r_object_id and cg.r_object_id = '{0}'", consiliumGroupId); Logger.Debug(CultureInfo.CurrentCulture, "SQL: {0}", sql); Npgsql.NpgsqlCommand command = new Npgsql.NpgsqlCommand(sql, connection); using (DbDataReader reader = command.ExecuteReader()) { while (reader.Read()) { DdvDoctor obj = new DdvDoctor(); obj.ObjectId = reader.IsDBNull(0) ? null : reader.GetString(0); obj.FullName = reader.IsDBNull(1) ? null : reader.GetString(1); obj.MiddleName = reader.IsDBNull(2) ? null : reader.GetString(2); obj.FirstName = reader.IsDBNull(3) ? null : reader.GetString(3); obj.ModifyDate = reader.IsDBNull(4) ? DateTime.MinValue : reader.GetDateTime(4); obj.ShortName = (reader.IsDBNull(8) ? null : reader.GetString(8)) + " " + (reader.IsDBNull(5) ? null : reader.GetString(5)); obj.CreationDate = reader.IsDBNull(6) ? DateTime.MinValue : reader.GetDateTime(6); obj.LastName = reader.IsDBNull(7) ? null : reader.GetString(7); list.Add(obj); } } } return(list); }
public DdtEkg GetByHospitalSessionAndAdmission(string hospitalSession) { using (dynamic connection = connectionFactory.GetConnection()) { String sql = String.Format(CultureInfo.CurrentCulture, "SELECT dsid_hospitality_session, r_object_id, dsdt_analysis_date, r_modify_date, dss_parent_type, r_creation_date, dsid_parent, dsb_admission_analysis, dss_ekg, dsid_doctor, dsid_patient " + "FROM ddt_ekg WHERE dsid_hospitality_session = '{0}'", hospitalSession); Logger.Debug(CultureInfo.CurrentCulture, "SQL: {0}", sql); Npgsql.NpgsqlCommand command = new Npgsql.NpgsqlCommand(sql, connection); using (DbDataReader reader = command.ExecuteReader()) { if (reader.Read()) { DdtEkg obj = new DdtEkg(); obj.HospitalitySession = reader.IsDBNull(0) ? null : reader.GetString(0); obj.ObjectId = reader.IsDBNull(1) ? null : reader.GetString(1); obj.AnalysisDate = reader.IsDBNull(2) ? DateTime.MinValue : reader.GetDateTime(2); obj.ModifyDate = reader.IsDBNull(3) ? DateTime.MinValue : reader.GetDateTime(3); obj.ParentType = reader.IsDBNull(4) ? null : reader.GetString(4); obj.CreationDate = reader.IsDBNull(5) ? DateTime.MinValue : reader.GetDateTime(5); obj.Parent = reader.IsDBNull(6) ? null : reader.GetString(6); obj.AdmissionAnalysis = reader.GetBoolean(7); obj.Ekg = reader.IsDBNull(8) ? null : reader.GetString(8); obj.Doctor = reader.IsDBNull(9) ? null : reader.GetString(9); obj.Patient = reader.IsDBNull(10) ? null : reader.GetString(10); return(obj); } } } return(null); }
public DdtConsiliumGroupMember GetById(string id) { using (dynamic connection = connectionFactory.GetConnection()) { String sql = String.Format("SELECT r_object_id, r_modify_date, r_creation_date, dss_name, dsid_doctor, dsid_group FROM ddt_consilium_group_member WHERE r_object_id = '{0}'", id); Logger.Debug(CultureInfo.CurrentCulture, "SQL: {0}", sql); Npgsql.NpgsqlCommand command = new Npgsql.NpgsqlCommand(sql, connection); using (DbDataReader reader = command.ExecuteReader()) { if (reader.Read()) { DdtConsiliumGroupMember obj = new DdtConsiliumGroupMember(); obj.ObjectId = reader.IsDBNull(0) ? null : reader.GetString(0); obj.ModifyDate = reader.IsDBNull(1) ? DateTime.MinValue : reader.GetDateTime(1); obj.CreationDate = reader.IsDBNull(2) ? DateTime.MinValue : reader.GetDateTime(2); obj.Name = reader.IsDBNull(3) ? null : reader.GetString(3); obj.Doctor = reader.IsDBNull(4) ? null : reader.GetString(4); obj.Group = reader.IsDBNull(5) ? null : reader.GetString(5); return(obj); } } } return(null); }
/// <summary> /// Get the ID of the next batch to be processed. /// </summary> /// <returns>The next batch ID to process, or null if there are no more events available.</returns> private long?GetNextBatchId() { long?batchID; using (var cnt = new Npgsql.NpgsqlConnection(_pgOptions.ConnectionString)) { cnt.Open(); using (var cmd = new Npgsql.NpgsqlCommand()) { cmd.Connection = cnt; cmd.Parameters.AddWithValue("@queueName", _queueName); cmd.Parameters.AddWithValue("@consumeName", _consumerName); cmd.CommandText = "select * from pgq.next_batch(@queueName,@consumeName);"; if (long.TryParse(cmd.ExecuteScalar().ToString(), out long result)) { batchID = result; } else { batchID = null; } } } return(batchID); }
public IList <DdtDoctor> GetAll() { IList <DdtDoctor> list = new List <DdtDoctor>(); using (dynamic connection = connectionFactory.GetConnection()) { String sql = "SELECT r_object_id, dss_middle_name, dss_first_name, r_modify_date, r_creation_date, dss_last_name FROM ddt_doctor"; Logger.Debug(CultureInfo.CurrentCulture, "SQL: {0}", sql); Npgsql.NpgsqlCommand command = new Npgsql.NpgsqlCommand(sql, connection); using (DbDataReader reader = command.ExecuteReader()) { while (reader.Read()) { DdtDoctor obj = new DdtDoctor(); obj.ObjectId = reader.IsDBNull(0) ? null : reader.GetString(0); obj.MiddleName = reader.IsDBNull(1) ? null : reader.GetString(1); obj.FirstName = reader.IsDBNull(2) ? null : reader.GetString(2); obj.ModifyDate = reader.IsDBNull(3) ? DateTime.MinValue : reader.GetDateTime(3); obj.CreationDate = reader.IsDBNull(4) ? DateTime.MinValue : reader.GetDateTime(4); obj.LastName = reader.IsDBNull(5) ? null : reader.GetString(5); list.Add(obj); } } } return(list); }
public IList <DdtValues> GetListByNameLike(string name) { IList <DdtValues> list = new List <DdtValues>(); using (dynamic connection = connectionFactory.GetConnection()) { String sql = String.Format("SELECT r_object_id, r_modify_date, r_creation_date, dss_name, dss_value FROM ddt_values WHERE dss_name LIKE '{0}%'", name); Logger.Debug(CultureInfo.CurrentCulture, "SQL: {0}", sql); Npgsql.NpgsqlCommand command = new Npgsql.NpgsqlCommand(sql, connection); using (DbDataReader reader = command.ExecuteReader()) { while (reader.Read()) { DdtValues obj = new DdtValues(); obj.ObjectId = reader.IsDBNull(0) ? null : reader.GetString(0); obj.ModifyDate = reader.IsDBNull(1) ? DateTime.MinValue : reader.GetDateTime(1); obj.CreationDate = reader.IsDBNull(2) ? DateTime.MinValue : reader.GetDateTime(2); obj.Name = reader.IsDBNull(3) ? null : reader.GetString(3); obj.Value = reader.IsDBNull(4) ? null : reader.GetString(4); list.Add(obj); } } } return(list); }
/// <summary> /// get DataProtectionKey by FriendlyName /// </summary> /// <param name="friendlyName">Friendly Name</param> /// <returns></returns> public DataProtectionKey GetElement(string friendlyName) { using (var con = new Npgsql.NpgsqlConnection(_connectionString)) { var sql = @"select ""FriendlyName"",""XmlData"" from public.""DataProtectionKeys"" where ""FriendlyName""=@FriendlyName;"; using (var cmd = new Npgsql.NpgsqlCommand(sql, con)) { DataProtectionKey dataProtectionKey = null; cmd.Parameters.Add(new Npgsql.NpgsqlParameter("@FriendlyName", friendlyName)); con.Open(); using (var reader = cmd.ExecuteReader()) { while (reader.Read()) { dataProtectionKey = new DataProtectionKey(); dataProtectionKey.FriendlyName = reader.GetString(0); dataProtectionKey.XmlData = reader.GetString(1); } reader.Close(); } con.Close(); return(dataProtectionKey); } } }
public string NewID() { string i = ""; string sQuery = "select '" + clsGlobal.pstrservercode + "'||nextval('tbm_carbrandid_nextid') as id;"; Npgsql.NpgsqlCommand cmd = new Npgsql.NpgsqlCommand(sQuery, Koneksi); cmd.CommandText = sQuery; try { Npgsql.NpgsqlDataReader rdr = cmd.ExecuteReader(); if (rdr.Read()) { if (!rdr.IsDBNull(rdr.GetOrdinal("id"))) { i = rdr.GetValue(0).ToString(); } else { i = ""; }; } rdr.Close(); } catch (Npgsql.NpgsqlException Ex) { System.Windows.Forms.MessageBox.Show(Ex.Message, "An error occurred while processing!!!"); return(""); } return(i); }
public void create_server_with_user_defined_instance_id_and_table_test() { using (var server = new MysticMind.PostgresEmbed.PgServer( "9.5.5.1", PG_USER, addLocalUserAccessPermission: ADD_LOCAL_USER_ACCESS_PERMISSION, instanceId: Guid.NewGuid(), clearInstanceDirOnStop: true)) { 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(); } }
public bool Update() { string sQuery = "UPDATE tbm_carbrand SET " + "brandname = @bname,dlt=@dlt,opedit=@opedit,pcedit=@pcedit,luedit=now()" + "WHERE carbrandid = @bid"; Npgsql.NpgsqlCommand cmd = new Npgsql.NpgsqlCommand(sQuery, Koneksi); if (m_brandid != null) { cmd.Parameters.Add("@bid", NpgsqlTypes.NpgsqlDbType.Varchar).Value = brandid; } else { cmd.Parameters.Add("@bid", NpgsqlTypes.NpgsqlDbType.Varchar).Value = DBNull.Value; } if (m_brandname != null) { cmd.Parameters.Add("@bname", NpgsqlTypes.NpgsqlDbType.Varchar).Value = brandname; } else { cmd.Parameters.Add("@bname", NpgsqlTypes.NpgsqlDbType.Varchar).Value = DBNull.Value; } if (opedit != null) { cmd.Parameters.Add("@opedit", NpgsqlTypes.NpgsqlDbType.Varchar).Value = opedit; } else { cmd.Parameters.Add("@opedit", NpgsqlTypes.NpgsqlDbType.Varchar).Value = DBNull.Value; } if (pcedit != null) { cmd.Parameters.Add("@pcedit", NpgsqlTypes.NpgsqlDbType.Varchar).Value = pcedit; } else { cmd.Parameters.Add("@pcedit", NpgsqlTypes.NpgsqlDbType.Varchar).Value = DBNull.Value; } if (luedit != null && luedit != DateTime.MinValue) { cmd.Parameters.Add("@luedit", NpgsqlTypes.NpgsqlDbType.Timestamp).Value = luedit; } else { cmd.Parameters.Add("@luedit", NpgsqlTypes.NpgsqlDbType.Timestamp).Value = DBNull.Value; } cmd.Parameters.Add("@dlt", NpgsqlTypes.NpgsqlDbType.Boolean).Value = dlt; cmd.CommandText = sQuery; try { cmd.ExecuteNonQuery(); return(true); } catch (Npgsql.NpgsqlException Ex) { System.Windows.Forms.MessageBox.Show(Ex.Message, "An error occurred while processing!!!"); return(false); } }
public DdtKag GetById(string id) { using (dynamic connection = connectionFactory.GetConnection()) { String sql = String.Format("SELECT r_object_id, dsdt_analysis_date, dsdt_end_time, r_creation_date, dsid_parent, dss_kag_manipulation, dsid_doctor, dsid_patient, dsid_hospitality_session, dsdt_start_time, r_modify_date, dss_parent_type, dss_results, dss_kag_action FROM ddt_kag WHERE r_object_id = '{0}'", id); Logger.Debug(CultureInfo.CurrentCulture, "SQL: {0}", sql); Npgsql.NpgsqlCommand command = new Npgsql.NpgsqlCommand(sql, connection); using (DbDataReader reader = command.ExecuteReader()) { if (reader.Read()) { DdtKag obj = new DdtKag(); obj.ObjectId = reader.IsDBNull(0) ? null : reader.GetString(0); obj.AnalysisDate = reader.IsDBNull(1) ? DateTime.MinValue : reader.GetDateTime(1); obj.EndTime = reader.IsDBNull(2) ? DateTime.MinValue : reader.GetDateTime(2); obj.CreationDate = reader.IsDBNull(3) ? DateTime.MinValue : reader.GetDateTime(3); obj.Parent = reader.IsDBNull(4) ? null : reader.GetString(4); obj.KagManipulation = reader.IsDBNull(5) ? null : reader.GetString(5); obj.Doctor = reader.IsDBNull(6) ? null : reader.GetString(6); obj.Patient = reader.IsDBNull(7) ? null : reader.GetString(7); obj.HospitalitySession = reader.IsDBNull(8) ? null : reader.GetString(8); obj.StartTime = reader.IsDBNull(9) ? DateTime.MinValue : reader.GetDateTime(9); obj.ModifyDate = reader.IsDBNull(10) ? DateTime.MinValue : reader.GetDateTime(10); obj.ParentType = reader.IsDBNull(11) ? null : reader.GetString(11); obj.Results = reader.IsDBNull(12) ? null : reader.GetString(12); obj.KagAction = reader.IsDBNull(13) ? null : reader.GetString(13); return(obj); } } } return(null); }
public IList <DdtKag> GetByQuery(string sql) { IList <DdtKag> list = new List <DdtKag>(); using (dynamic connection = connectionFactory.GetConnection()) { Logger.Debug(CultureInfo.CurrentCulture, "SQL: {0}", sql); Npgsql.NpgsqlCommand command = new Npgsql.NpgsqlCommand(sql, connection); using (DbDataReader reader = command.ExecuteReader()) { if (reader.Read()) { DdtKag obj = new DdtKag(); obj.ObjectId = reader.IsDBNull(0) ? null : reader.GetString(0); obj.AnalysisDate = reader.IsDBNull(1) ? DateTime.MinValue : reader.GetDateTime(1); obj.EndTime = reader.IsDBNull(2) ? DateTime.MinValue : reader.GetDateTime(2); obj.CreationDate = reader.IsDBNull(3) ? DateTime.MinValue : reader.GetDateTime(3); obj.Parent = reader.IsDBNull(4) ? null : reader.GetString(4); obj.KagManipulation = reader.IsDBNull(5) ? null : reader.GetString(5); obj.Doctor = reader.IsDBNull(6) ? null : reader.GetString(6); obj.Patient = reader.IsDBNull(7) ? null : reader.GetString(7); obj.HospitalitySession = reader.IsDBNull(8) ? null : reader.GetString(8); obj.StartTime = reader.IsDBNull(9) ? DateTime.MinValue : reader.GetDateTime(9); obj.ModifyDate = reader.IsDBNull(10) ? DateTime.MinValue : reader.GetDateTime(10); obj.ParentType = reader.IsDBNull(11) ? null : reader.GetString(11); obj.Results = reader.IsDBNull(12) ? null : reader.GetString(12); obj.KagAction = reader.IsDBNull(13) ? null : reader.GetString(13); list.Add(obj); } } } return(list); }
public DdtDoctor GetById(string id) { using (dynamic connection = connectionFactory.GetConnection()) { String sql = String.Format("SELECT r_object_id, dss_middle_name, dss_first_name, r_modify_date, r_creation_date, dss_last_name FROM ddt_doctor WHERE r_object_id = '{0}'", id); Logger.Debug(CultureInfo.CurrentCulture, "SQL: {0}", sql); Npgsql.NpgsqlCommand command = new Npgsql.NpgsqlCommand(sql, connection); using (DbDataReader reader = command.ExecuteReader()) { if (reader.Read()) { DdtDoctor obj = new DdtDoctor(); obj.ObjectId = reader.IsDBNull(0) ? null : reader.GetString(0); obj.MiddleName = reader.IsDBNull(1) ? null : reader.GetString(1); obj.FirstName = reader.IsDBNull(2) ? null : reader.GetString(2); obj.ModifyDate = reader.IsDBNull(3) ? DateTime.MinValue : reader.GetDateTime(3); obj.CreationDate = reader.IsDBNull(4) ? DateTime.MinValue : reader.GetDateTime(4); obj.LastName = reader.IsDBNull(5) ? null : reader.GetString(5); return(obj); } } } return(null); }
static void Main(string[] args) { const string PG_USER = "******"; const string CONN_STR = "Server=localhost;Port={0};User Id={1};Password=test;Database=postgres;Pooling=false"; using (var server = new MysticMind.PostgresEmbed.PgServer( "9.5.5.1", PG_USER, addLocalUserAccessPermission: true, clearInstanceDirOnStop: true)) { server.Start(); // 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(); } }
/// <summary> /// Returns the geometry corresponding to the Object ID /// </summary> /// <param name="oid">Object ID</param> /// <returns>geometry</returns> public IGeometry 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); }
} // End Function GetConnectionString public static void CreateUser(string userName, string password) { string role = "CREATE ROLE \"" + userName.Replace("\"", "\"\"") + "\" " + "WITH PASSWORD '" + password.Replace("'", "''") + "' " + "LOGIN SUPERUSER CREATEDB CREATEROLE REPLICATION VALID UNTIL 'infinity'; "; using (Npgsql.NpgsqlConnection con = new Npgsql.NpgsqlConnection(GetConnectionString())) { using (Npgsql.NpgsqlCommand cmd = con.CreateCommand()) { if (con.State != System.Data.ConnectionState.Open) { con.Open(); } // https://stackoverflow.com/questions/8092086/create-postgresql-role-user-if-it-doesnt-exist // https://stackoverflow.com/questions/8546759/how-to-check-if-a-postgres-user-exists cmd.CommandText = "SELECT COUNT(*) FROM pg_roles WHERE rolname = '" + userName.Replace("'", "''") + "'; "; // cmd.CommandText = "SELECT COUNT(*) FROM pg_catalog.pg_user WHERE usename = '" + userName.Replace("'", "''") + "'"; long countOfExistingUsersWithThisName = (long)cmd.ExecuteScalar(); if (countOfExistingUsersWithThisName == 0) { cmd.CommandText = role; cmd.ExecuteNonQuery(); } // End if (dbCount > 0) if (con.State != System.Data.ConnectionState.Closed) { con.Close(); } } // End Using cmd } // End using con } // End Sub CreateUser
public void create_server_without_using_block() { var server = new MysticMind.PostgresEmbed.PgServer( "9.5.5.1", PG_USER, addLocalUserAccessPermission: ADD_LOCAL_USER_ACCESS_PERMISSION); try { server.Start(); 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(); } finally { server.Stop(); } }
/// <summary> /// The result of the registration. 1 means success, 0 means already registered. /// </summary> /// <returns></returns> private bool Register() { try { int registerID; using (var cnt = new Npgsql.NpgsqlConnection(_pgOptions.ConnectionString)) { cnt.Open(); using (var cmd = new Npgsql.NpgsqlCommand()) { cmd.Connection = cnt; cmd.Parameters.AddWithValue("@queueName", _queueName); cmd.Parameters.AddWithValue("@consumeName", _consumerName); cmd.CommandText = "select * from pgq.register_consumer(@queueName,@consumeName);"; registerID = (int)cmd.ExecuteScalar(); } } return(REGISTER_SUCCESS == registerID); } catch (Exception e) { _logger.LogError($"[PgMQProvider.Register.Exception]: {e.ToString()}"); return(false); } }
public IList <DdtConsiliumGroupMember> GetDefault() { IList <DdtConsiliumGroupMember> list = new List <DdtConsiliumGroupMember>(); using (dynamic connection = connectionFactory.GetConnection()) { String sql = "SELECT r_object_id, r_modify_date, r_creation_date, dss_name, dsid_doctor, dsid_group FROM ddt_consilium_group_member WHERE dsb_default = TRUE"; Logger.Debug(CultureInfo.CurrentCulture, "SQL: {0}", sql); Npgsql.NpgsqlCommand command = new Npgsql.NpgsqlCommand(sql, connection); using (DbDataReader reader = command.ExecuteReader()) { while (reader.Read()) { DdtConsiliumGroupMember obj = new DdtConsiliumGroupMember(); obj.ObjectId = reader.IsDBNull(0) ? null : reader.GetString(0); obj.ModifyDate = reader.IsDBNull(1) ? DateTime.MinValue : reader.GetDateTime(1); obj.CreationDate = reader.IsDBNull(2) ? DateTime.MinValue : reader.GetDateTime(2); obj.Name = reader.IsDBNull(3) ? null : reader.GetString(3); obj.Doctor = reader.IsDBNull(4) ? null : reader.GetString(4); obj.Group = reader.IsDBNull(5) ? null : reader.GetString(5); list.Add(obj); } } } return(list); }
public IList <DdtCure> GetListByMedicineListId(string id) { IList <DdtCure> list = new List <DdtCure>(); using (dynamic connection = connectionFactory.GetConnection()) { String sql = String.Format("SELECT c.r_object_id, c.r_modify_date, c.r_creation_date, c.dss_name, c.dsid_cure_type FROM ddt_cure c, ddt_issued_medicine m WHERE m.dsid_cure = c.r_object_id AND m.dsid_med_list = '{0}'", id); Logger.Debug(CultureInfo.CurrentCulture, "SQL: {0}", sql); Npgsql.NpgsqlCommand command = new Npgsql.NpgsqlCommand(sql, connection); using (DbDataReader reader = command.ExecuteReader()) { while (reader.Read()) { DdtCure obj = new DdtCure(); obj.ObjectId = reader.IsDBNull(0) ? null : reader.GetString(0); obj.ModifyDate = reader.IsDBNull(1) ? DateTime.MinValue : reader.GetDateTime(1); obj.CreationDate = reader.IsDBNull(2) ? DateTime.MinValue : reader.GetDateTime(2); obj.Name = reader.IsDBNull(3) ? null : reader.GetString(3); obj.CureType = reader.IsDBNull(4) ? null : reader.GetString(4); list.Add(obj); } } } return(list); }
public IList <DdtCure> GetAll() { IList <DdtCure> list = new List <DdtCure>(); using (dynamic connection = connectionFactory.GetConnection()) { String sql = "SELECT r_object_id, r_modify_date, r_creation_date, dss_name, dsid_cure_type FROM ddt_cure"; Logger.Debug(CultureInfo.CurrentCulture, "SQL: {0}", sql); Npgsql.NpgsqlCommand command = new Npgsql.NpgsqlCommand(sql, connection); using (DbDataReader reader = command.ExecuteReader()) { while (reader.Read()) { DdtCure obj = new DdtCure(); obj.ObjectId = reader.IsDBNull(0) ? null : reader.GetString(0); obj.ModifyDate = reader.IsDBNull(1) ? DateTime.MinValue : reader.GetDateTime(1); obj.CreationDate = reader.IsDBNull(2) ? DateTime.MinValue : reader.GetDateTime(2); obj.Name = reader.IsDBNull(3) ? null : reader.GetString(3); obj.CureType = reader.IsDBNull(4) ? null : reader.GetString(4); list.Add(obj); } } } return(list); }
public List <DdtCure> GetByQuery(string sql) { List <DdtCure> list = new List <DdtCure>(); using (dynamic connection = connectionFactory.GetConnection()) { Logger.Debug(CultureInfo.CurrentCulture, "SQL: {0}", sql); Npgsql.NpgsqlCommand command = new Npgsql.NpgsqlCommand(sql, connection); using (DbDataReader reader = command.ExecuteReader()) { while (reader.Read()) { DdtCure obj = new DdtCure(); obj.ObjectId = reader.IsDBNull(0) ? null : reader.GetString(0); obj.ModifyDate = reader.IsDBNull(1) ? DateTime.MinValue : reader.GetDateTime(1); obj.CreationDate = reader.IsDBNull(2) ? DateTime.MinValue : reader.GetDateTime(2); obj.Name = reader.IsDBNull(3) ? null : reader.GetString(3); obj.CureType = reader.IsDBNull(4) ? null : reader.GetString(4); list.Add(obj); } } } return(list); }
public IList <DdtCure> GetListByTemplate(string templateName) { IList <DdtCure> list = new List <DdtCure>(); using (dynamic connection = connectionFactory.GetConnection()) { String sql = String.Format("Select cure.* from ddt_values vv, ddt_cure cure where vv.dss_name like '{0}%' AND vv.dss_value = cure.dss_name", templateName); Logger.Debug(CultureInfo.CurrentCulture, "SQL: {0}", sql); Npgsql.NpgsqlCommand command = new Npgsql.NpgsqlCommand(sql, connection); using (DbDataReader reader = command.ExecuteReader()) { while (reader.Read()) { DdtCure obj = new DdtCure(); obj.ObjectId = reader.IsDBNull(0) ? null : reader.GetString(0); obj.ModifyDate = reader.IsDBNull(1) ? DateTime.MinValue : reader.GetDateTime(1); obj.CreationDate = reader.IsDBNull(2) ? DateTime.MinValue : reader.GetDateTime(2); obj.Name = reader.IsDBNull(3) ? null : reader.GetString(3); obj.CureType = reader.IsDBNull(4) ? null : reader.GetString(4); list.Add(obj); } } } return(list); }
private void RefreshButtonClick(object sender, RoutedEventArgs e) { var pgQuery = new Npgsql.NpgsqlCommand("SELECT * FROM info_log"); pgQuery.Connection = _pgConnection; var reader = pgQuery.ExecuteReader(); ServerLogGrid.ItemsSource = reader; ServerLogGrid.Items.Refresh(); }
public static void Analyze() { string sql = "ANALYZE;"; using (Npgsql.NpgsqlCommand command = new Npgsql.NpgsqlCommand(sql)) { command.CommandTimeout = 3600; MixERP.Net.DBFactory.DBOperations.ExecuteNonQuery(command); } }
public static void VacuumFull() { string sql = "VACUUM FULL;"; using (Npgsql.NpgsqlCommand command = new Npgsql.NpgsqlCommand(sql)) { command.CommandTimeout = 3600; MixERP.Net.DBFactory.DBOperations.ExecuteNonQuery(command); } }
private void button1_Click(object sender, EventArgs e) { if (this.richTextBox1.Text.Trim().Length > 2) { if (InsInBase == true) { Npgsql.NpgsqlCommand nci = new Npgsql.NpgsqlCommand("update error_log set user_descr = '" + this.richTextBox1.Text.Trim() + "' where err_id = '"+errorLogId+"' ", DBExchange.Inst.connectDb); nci.ExecuteNonQuery(); } else { if (!(System.IO.Directory.Exists(Application.StartupPath + "\\Errors\\"))) { System.IO.Directory.CreateDirectory(Application.StartupPath + "\\Errors\\"); } FileStream fs = new FileStream(Application.StartupPath + "\\Errors\\errlog.txt", FileMode.OpenOrCreate, FileAccess.ReadWrite); StreamWriter s = new StreamWriter(fs); s.Close(); fs.Close(); FileStream fs1 = new FileStream(Application.StartupPath + "\\Errors\\errlog.txt", FileMode.Append, FileAccess.Write); StreamWriter s1 = new StreamWriter(fs1); s1.Write("Date/Time: " + DateTime.Now.ToString()); s1.Write("\nКомментарий: " + this.richTextBox1.Text.Trim()); s1.Write("\n=========================================================================================== \n\n"); s1.Close(); fs1.Close(); } } FormRegisterError.ActiveForm.Close(); }
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(); }
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; }
/// <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 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> /// 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 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> /// 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; }
protected static void RunScript(string script, IDictionary<string, Npgsql.NpgsqlParameter> parameters, Npgsql.NpgsqlConnection conn, Npgsql.NpgsqlTransaction trans) { if (parameters == null) parameters = new Dictionary<string, Npgsql.NpgsqlParameter>(); foreach (var commandText in script.Split(';')) { var sql = commandText + ";"; Log.Debug(d => d("Script command: {0}", sql)); using (var cmd = new Npgsql.NpgsqlCommand(sql, conn, trans)) { foreach (var parameter in parameters.Keys) { if (sql.Contains(parameter)) { var value = parameters[parameter]; Log.Debug(d => d("The command contains the parameter '{0}', setting value to '{1}'...", parameter, value.Value)); cmd.Parameters.Add(value); } } cmd.ExecuteNonQuery(); } } Log.Debug(d => d("Script complete.")); }
public static List<Common.Models.Tasks.Task> GetTodoListForAll( DateTime? start = null, DateTime? stop = null, IDbConnection conn = null, bool closeConnection = true) { string sql; List<Npgsql.NpgsqlParameter> parms = new List<Npgsql.NpgsqlParameter>(); List<Common.Models.Tasks.Task> list = new List<Common.Models.Tasks.Task>(); sql = "SELECT * FROM \"task\" WHERE \"active\"=true AND \"utc_disabled\" is null "; if (start.HasValue) { parms.Add(new Npgsql.NpgsqlParameter("Start", DbType.DateTime) { Value = start.Value }); if (stop.HasValue) { sql += "AND \"due_date\" BETWEEN @Start AND @Stop "; parms.Add(new Npgsql.NpgsqlParameter("Stop", DbType.DateTime) { Value = stop.Value }); } else { sql += "AND \"due_date\">=@Start "; } } sql += "ORDER BY \"due_date\" ASC"; conn = DataHelper.OpenIfNeeded(conn); using (Npgsql.NpgsqlCommand cmd = new Npgsql.NpgsqlCommand(sql, (Npgsql.NpgsqlConnection)conn)) { parms.ForEach(x => cmd.Parameters.Add(x)); using (Npgsql.NpgsqlDataReader reader = cmd.ExecuteReader()) { while (reader.Read()) { DBOs.Tasks.Task dbo = new DBOs.Tasks.Task(); dbo.Id = Database.GetDbColumnValue<long>("id", reader); dbo.Title = Database.GetDbColumnValue<string>("title", reader); dbo.Description = Database.GetDbColumnValue<string>("description", reader); dbo.ProjectedStart = Database.GetDbColumnValue<DateTime?>("projected_start", reader); dbo.DueDate = Database.GetDbColumnValue<DateTime?>("due_date", reader); dbo.ProjectedEnd = Database.GetDbColumnValue<DateTime?>("projected_end", reader); dbo.ActualEnd = Database.GetDbColumnValue<DateTime?>("actual_end", reader); dbo.ParentId = Database.GetDbColumnValue<long?>("parent_id", reader); dbo.IsGroupingTask = Database.GetDbColumnValue<bool>("is_grouping_task", reader); dbo.SequentialPredecessorId = Database.GetDbColumnValue<long?>("sequential_predecessor_id", reader); list.Add(Mapper.Map<Common.Models.Tasks.Task>(dbo)); } } } DataHelper.Close(conn, closeConnection); return list; }
public void SetupDBProviderFromConfig() { SetConnectionStringFromConfig(); connection = new Npgsql.NpgsqlConnection(connstring); comm = new Npgsql.NpgsqlCommand("", connection); }
public void SetupDBProvider(string server, string port, string user, string password, string database) { SetConnectionString(server, port, user, password, database); connection = new Npgsql.NpgsqlConnection(connstring); comm = new Npgsql.NpgsqlCommand("", connection); }
public void Open(bool transaction) { connection = new Npgsql.NpgsqlConnection(connstring); connection.Open(); comm = new Npgsql.NpgsqlCommand("", connection); if (transaction) { comm.Transaction = connection.BeginTransaction(System.Data.IsolationLevel.ReadCommitted); } }
protected static bool TableExists(string tableName, Npgsql.NpgsqlConnection conn, Npgsql.NpgsqlTransaction trans) { using (var cmd = new Npgsql.NpgsqlCommand(TABLE_EXISTS, conn, trans)) { cmd.Parameters.Add("@tableName", NpgsqlTypes.NpgsqlDbType.Varchar, 255).Value = tableName; using (var r = cmd.ExecuteReader()) { return r.HasRows; } } }
protected static void SetDBVersion(Npgsql.NpgsqlConnection conn, Npgsql.NpgsqlTransaction trans, string versionNumber) { using (var cmd = new Npgsql.NpgsqlCommand("update versions set version = @version where name = 'application';", conn, trans)) { cmd.CommandType = System.Data.CommandType.Text; cmd.Parameters.Add("@version", NpgsqlTypes.NpgsqlDbType.Varchar, 15).Value = versionNumber; cmd.ExecuteNonQuery(); } }
protected static void RunStatement(string statement, IDictionary<string, Npgsql.NpgsqlParameter> parameters, Npgsql.NpgsqlConnection conn, Npgsql.NpgsqlTransaction trans) { if (parameters == null) parameters = new Dictionary<string, Npgsql.NpgsqlParameter>(); using (var cmd = new Npgsql.NpgsqlCommand(statement, conn, trans)) { foreach (var parameter in parameters.Keys) { if (statement.Contains(parameter)) { var value = parameters[parameter]; Log.Debug(d => d("The command contains the parameter '{0}', setting value to '{1}'...", parameter, value.Value)); cmd.Parameters.Add(value); } } cmd.ExecuteNonQuery(); } }
/// <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; } } }
protected static string GetDBVersion(Npgsql.NpgsqlConnection conn, Npgsql.NpgsqlTransaction trans) { using (var cmd = new Npgsql.NpgsqlCommand("select version from versions where name='application' limit 1;", conn, trans)) { cmd.CommandType = System.Data.CommandType.Text; return (string)cmd.ExecuteScalar(); } }
public override int Delete(string table, string query) { int result = 0; string sql = "DELETE FROM " + table; if (!String.IsNullOrEmpty(query)) sql += " " + query; SystemHooks.OnSQL(LocalName, sql); lock (DatabaseLock) { try { Npgsql.NpgsqlCommand SqlCommand = new Npgsql.NpgsqlCommand(sql, this.connection); result = SqlCommand.ExecuteNonQuery(); } catch (Npgsql.NpgsqlException me) { ErrorBuffer = me.Message; HandleError(sql); } } return result; }
public static List<Common.Models.Tasks.Task> ListChildren( long? parentId, List<Tuple<string, string>> filter = null, IDbConnection conn = null, bool closeConnection = true) { List<Common.Models.Tasks.Task> list = new List<Common.Models.Tasks.Task>(); IEnumerable<DBOs.Tasks.Task> ie = null; //filter = new List<Tuple<string, string>>(); //filter.Add(new Tuple<string, string>("status", "pending")); if (filter != null) { string filterStr = null; List<string> cats = new List<string>(); List<string> tags = new List<string>(); List<Npgsql.NpgsqlParameter> parms = new List<Npgsql.NpgsqlParameter>(); filter.ForEach(x => { if (!string.IsNullOrWhiteSpace(x.Item1)) cats.Add(x.Item1.ToLower()); if (!string.IsNullOrWhiteSpace(x.Item2)) tags.Add(x.Item2.ToLower()); }); filterStr = "SELECT * FROM \"task\" WHERE \"id\" IN (SELECT \"task_id\" FROM \"task_tag\" WHERE \"tag_category_id\" " + "IN (SELECT \"id\" FROM \"tag_category\" WHERE LOWER(\"name\") IN ("; cats.ForEach(x => { string parmName = parms.Count.ToString(); parms.Add(new Npgsql.NpgsqlParameter(parmName, NpgsqlTypes.NpgsqlDbType.Text) { Value = x }); filterStr += ":" + parmName + ","; }); filterStr = filterStr.TrimEnd(','); filterStr += ")) AND LOWER(\"tag\") IN ("; tags.ForEach(x => { string parmName = parms.Count.ToString(); parms.Add(new Npgsql.NpgsqlParameter(parmName, NpgsqlTypes.NpgsqlDbType.Text) { Value = x }); filterStr += ":" + parmName + ","; }); filterStr = filterStr.TrimEnd(','); filterStr += ")) AND \"parent_id\""; if (parentId.HasValue && parentId.Value > 0) { filterStr += "=:parentid "; parms.Add(new Npgsql.NpgsqlParameter("parentid", DbType.Int64) { Value = parentId.Value.ToString() }); } else filterStr += " is null "; filterStr += "AND \"utc_disabled\" is null"; conn = DataHelper.OpenIfNeeded(conn); using (Npgsql.NpgsqlCommand cmd = new Npgsql.NpgsqlCommand(filterStr, (Npgsql.NpgsqlConnection)conn)) { parms.ForEach(x => cmd.Parameters.Add(x)); using (Npgsql.NpgsqlDataReader reader = cmd.ExecuteReader()) { while (reader.Read()) { DBOs.Tasks.Task dbo = new DBOs.Tasks.Task(); dbo.Id = Database.GetDbColumnValue<long>("id", reader); dbo.Title = Database.GetDbColumnValue<string>("title", reader); dbo.Description = Database.GetDbColumnValue<string>("description", reader); dbo.ProjectedStart = Database.GetDbColumnValue<DateTime?>("projected_start", reader); dbo.DueDate = Database.GetDbColumnValue<DateTime?>("due_date", reader); dbo.ProjectedEnd = Database.GetDbColumnValue<DateTime?>("projected_end", reader); dbo.ActualEnd = Database.GetDbColumnValue<DateTime?>("actual_end", reader); dbo.ParentId = Database.GetDbColumnValue<long?>("parent_id", reader); dbo.IsGroupingTask = Database.GetDbColumnValue<bool>("is_grouping_task", reader); dbo.SequentialPredecessorId = Database.GetDbColumnValue<long?>("sequential_predecessor_id", reader); list.Add(Mapper.Map<Common.Models.Tasks.Task>(dbo)); } } } } else { conn = DataHelper.OpenIfNeeded(conn); if (parentId.HasValue) ie = conn.Query<DBOs.Tasks.Task>( "SELECT * FROM \"task\" WHERE \"parent_id\"=@ParentId AND \"utc_disabled\" is null", new { ParentId = parentId.Value }); else ie = conn.Query<DBOs.Tasks.Task>( "SELECT * FROM \"task\" WHERE \"parent_id\" is null AND \"utc_disabled\" is null"); foreach (DBOs.Tasks.Task dbo in ie) list.Add(Mapper.Map<Common.Models.Tasks.Task>(dbo)); } DataHelper.Close(conn, closeConnection); return list; }
protected static bool IsSuperUser(Npgsql.NpgsqlConnection conn, Npgsql.NpgsqlTransaction trans) { using (var cmd = new Npgsql.NpgsqlCommand(IS_SUPERUSER, conn, trans)) { using (var r = cmd.ExecuteReader()) { return r.HasRows; } } }
protected static bool FunctionExists(string functionName, Npgsql.NpgsqlConnection conn, Npgsql.NpgsqlTransaction trans) { using (var cmd = new Npgsql.NpgsqlCommand(FUNCTION_EXISTS, conn, trans)) { cmd.Parameters.Add("@procname", NpgsqlTypes.NpgsqlDbType.Varchar, 255).Value = functionName; using (var r = cmd.ExecuteReader()) { return r.HasRows; } } }
public static List<Common.Models.Tasks.Task> GetTodoListForAll(List<Common.Models.Settings.TagFilter> tagFilter, DateTime? start = null, DateTime? stop = null) { string sql; List<string> cats = new List<string>(); List<string> tags = new List<string>(); List<Npgsql.NpgsqlParameter> parms = new List<Npgsql.NpgsqlParameter>(); List<Common.Models.Tasks.Task> list = new List<Common.Models.Tasks.Task>(); tagFilter.ForEach(x => { if (!string.IsNullOrWhiteSpace(x.Category)) cats.Add(x.Category.ToLower()); if (!string.IsNullOrWhiteSpace(x.Tag)) tags.Add(x.Tag.ToLower()); }); sql = "SELECT * FROM \"task\" WHERE \"active\"=true AND " + "\"id\" IN (SELECT \"task_id\" FROM \"task_tag\" WHERE \"tag_category_id\" " + "IN (SELECT \"id\" FROM \"tag_category\" WHERE LOWER(\"name\") IN ("; cats.ForEach(x => { string parmName = parms.Count.ToString(); parms.Add(new Npgsql.NpgsqlParameter(parmName, NpgsqlTypes.NpgsqlDbType.Text) { Value = x }); sql += ":" + parmName + ","; }); sql = sql.TrimEnd(','); sql += ")) AND LOWER(\"tag\") IN ("; tags.ForEach(x => { string parmName = parms.Count.ToString(); parms.Add(new Npgsql.NpgsqlParameter(parmName, NpgsqlTypes.NpgsqlDbType.Text) { Value = x }); sql += ":" + parmName + ","; }); sql = sql.TrimEnd(','); sql += ")) AND \"utc_disabled\" is null "; if (start.HasValue) { parms.Add(new Npgsql.NpgsqlParameter("Start", DbType.DateTime) { Value = start.Value }); if (stop.HasValue) { sql += "AND \"due_date\" BETWEEN @Start AND @Stop "; parms.Add(new Npgsql.NpgsqlParameter("Stop", DbType.DateTime) { Value = stop.Value }); } else { sql += "AND \"due_date\">=@Start "; } } sql += "ORDER BY \"due_date\" ASC"; using (Npgsql.NpgsqlConnection conn = (Npgsql.NpgsqlConnection)Database.Instance.GetConnection()) { conn.Open(); using (Npgsql.NpgsqlCommand cmd = new Npgsql.NpgsqlCommand(sql, conn)) { parms.ForEach(x => cmd.Parameters.Add(x)); using (Npgsql.NpgsqlDataReader reader = cmd.ExecuteReader()) { while (reader.Read()) { DBOs.Tasks.Task dbo = new DBOs.Tasks.Task(); dbo.Id = Database.GetDbColumnValue<long>("id", reader); dbo.Title = Database.GetDbColumnValue<string>("title", reader); dbo.Description = Database.GetDbColumnValue<string>("description", reader); dbo.ProjectedStart = Database.GetDbColumnValue<DateTime?>("projected_start", reader); dbo.DueDate = Database.GetDbColumnValue<DateTime?>("due_date", reader); dbo.ProjectedEnd = Database.GetDbColumnValue<DateTime?>("projected_end", reader); dbo.ActualEnd = Database.GetDbColumnValue<DateTime?>("actual_end", reader); dbo.ParentId = Database.GetDbColumnValue<long?>("parent_id", reader); dbo.IsGroupingTask = Database.GetDbColumnValue<bool>("is_grouping_task", reader); dbo.SequentialPredecessorId = Database.GetDbColumnValue<long?>("sequential_predecessor_id", reader); list.Add(Mapper.Map<Common.Models.Tasks.Task>(dbo)); } } } } return list; }
public void writeLog(string _thisFunc, string _thisException, string _thisLog) { //if (!(System.IO.Directory.Exists(Application.StartupPath + "\\Errors\\"))) //{ // System.IO.Directory.CreateDirectory(Application.StartupPath + "\\Errors\\"); // System.IO.File.Create(Application.StartupPath + "\\Errors\\errlog.txt"); //} if (!(System.IO.Directory.Exists(di.FullName + "\\Medex\\Errors\\"))) { System.IO.Directory.CreateDirectory(di.FullName + "\\Medex\\Errors\\"); if (!(System.IO.File.Exists(di.FullName + "\\Medex\\Errors\\errlog_"+DateTime.Now.ToShortDateString()+".txt"))) { System.IO.File.Create(di.FullName + "\\Medex\\Errors\\errlog_"+DateTime.Now.ToShortDateString()+".txt"); } } if (_thisException == "There is already an open DataReader associated with this Command which must be closed first.") { DBExchange.Inst.connectDb.Close(); DBExchange.Inst.connectDb.Dispose(); DBExchange.Inst.connectDb.Open(); } else { try { string s3 = ""; s3+= "Program version: " + DBExchange.Inst.versionNumber.ToString(); s3+="\nFunction: " + _thisFunc; s3+="\nMessage: " + _thisException; s3+="\nStackTrace: " + _thisLog; s3+= "\n=========================================================================================== \n\n"; Npgsql.NpgsqlCommand nci = new Npgsql.NpgsqlCommand("insert into error_log (descr, user_id) values ('"+s3+"','"+DBExchange.Inst.dbUsrId+"') returning err_id ", DBExchange.Inst.connectDb); int error_id = (int)nci.ExecuteScalar(); System.Windows.Forms.MessageBox.Show("ВНИМАНИЕ ВОЗНИКЛА НЕПРЕДВИДЕННАЯ ОШИБКА \nОшибка зарегистрирована в базе данных \nВы можете сообщить о своих действиях на сайте http://medx.spb.ru/issues \nили описать свои деействия в следующей форме" ); Templates.FormRegisterError fre = new Templates.FormRegisterError(true,error_id); fre.ShowDialog(); } catch { FileStream fs = new FileStream(di.FullName + "\\Medex\\Errors\\errlog_" + DateTime.Now.ToShortDateString() + ".txt", FileMode.OpenOrCreate, FileAccess.ReadWrite); StreamWriter s = new StreamWriter(fs); s.Close(); fs.Close(); FileStream fs1 = new FileStream(di.FullName + "\\Medex\\Errors\\errlog_" + DateTime.Now.ToShortDateString() + ".txt", FileMode.Append, FileAccess.Write); StreamWriter s1 = new StreamWriter(fs1); s1.Write("Date/Time: " + DateTime.Now.ToString()); s1.Write("\nProgram version: " + DBExchange.Inst.versionNumber.ToString()); s1.Write("\nFunction: " + _thisFunc); s1.Write("\nMessage: " + _thisException); s1.Write("\nStackTrace: " + _thisLog); s1.Write("\n=========================================================================================== \n\n"); s1.Close(); fs1.Close(); Templates.FormRegisterError fre = new Templates.FormRegisterError(false,0); System.Windows.Forms.MessageBox.Show(_thisFunc + " \n" + _thisException + " \n" + _thisLog); fre.ShowDialog(); } } }
public override void Commit() { SystemHooks.OnSQL(LocalName, "commit;"); Npgsql.NpgsqlCommand c = new Npgsql.NpgsqlCommand("commit;", this.connection); c.ExecuteNonQuery(); }