public void Delete(string id) { using (dynamic connection = connectionFactory.GetConnection()) { String sql = "delete from ddt_journal WHERE dsid_journal_day = '" + id + "' "; Logger.Debug(CultureInfo.CurrentCulture, "SQL: {0}", sql); Npgsql.NpgsqlCommand command = new Npgsql.NpgsqlCommand(sql, connection); command.ExecuteScalar(); sql = "delete from ddt_journal_day WHERE r_object_id = '" + id + "' "; Logger.Debug(CultureInfo.CurrentCulture, "SQL: {0}", sql); command.ExecuteScalar(); } }
private int GetAccounts(DateTime startdate, DateTime enddate, int userid, int sauserid, string volume) { int accountscount; using (Npgsql.NpgsqlConnection conn = new Npgsql.NpgsqlConnection(Infrastructure.ConfigReader.ConnectionString.ToString())) { conn.Open(); using (Npgsql.NpgsqlCommand command = new Npgsql.NpgsqlCommand(GetAccountsQuery, conn)) { command.Parameters.Add(new Npgsql.NpgsqlParameter("userid", NpgsqlTypes.NpgsqlDbType.Integer)); command.Parameters.Add(new Npgsql.NpgsqlParameter("sauserid", NpgsqlTypes.NpgsqlDbType.Integer)); command.Parameters.Add(new Npgsql.NpgsqlParameter("startdate", NpgsqlTypes.NpgsqlDbType.Timestamp)); command.Parameters.Add(new Npgsql.NpgsqlParameter("enddate", NpgsqlTypes.NpgsqlDbType.Timestamp)); command.Parameters.Add(new Npgsql.NpgsqlParameter("volume", NpgsqlTypes.NpgsqlDbType.Text)); command.Prepare(); command.Parameters[0].Value = userid; command.Parameters[1].Value = sauserid; command.Parameters[2].Value = startdate; command.Parameters[3].Value = enddate; command.Parameters[4].Value = volume; int dr; dr = Convert.ToInt32(command.ExecuteScalar()); accountscount = dr; } } return(accountscount); }
public int GetNonAllocatedResourcesCountFromPostgres(bool forDelivery = true) { int count = 0; Npgsql.NpgsqlConnection con = null; try { con = new Npgsql.NpgsqlConnection(PostgresSqlQueries.CONNECTION_STRING); con.Open(); string qry = PostgresSqlQueries.GET_COUNT_OF_NON_ALLOCATED_EMPLOYEES_FROM_DELIVERY; if (!forDelivery) { qry = PostgresSqlQueries.GET_COUNT_OF_NON_ALLOCATED_EMPLOYEES_FROM_NON_DELIVERY; } qry = qry.Replace("__CURRENT_DATE__", $"{DateTime.Today.Year}-{DateTime.Today.Month}-{DateTime.Today.Day}"); Npgsql.NpgsqlCommand cmd = new Npgsql.NpgsqlCommand(qry, con); object res = cmd.ExecuteScalar(); int.TryParse(res.ToString(), out count); } catch (Exception) { } finally { con.Close(); con.Dispose(); } return(count); }
/// <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); } }
} // 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
/// <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 string Save(DdtConsilium obj) { using (dynamic connection = connectionFactory.GetConnection()) { if (GetById(obj.ObjectId) != null) { string sql = "UPDATE ddt_consilium SET " + "dsid_hospitality_session = @HospitalitySession, " + "dsid_patient = @Patient, " + "dsid_doctor = @Doctor, " + "dsdt_consilium_date = @ConsiliumDate, " + "dss_goal = @Goal, " + "dss_dynamics = @Dynamics, " + "dss_diagnosis = @Diagnosis, " + "dss_decision = @Decision, " + "dss_duty_admin_name = @DutyAdminName " + "WHERE r_object_id = @ObjectId"; Logger.Debug(CultureInfo.CurrentCulture, "SQL: {0}", sql); using (Npgsql.NpgsqlCommand cmd = new Npgsql.NpgsqlCommand(sql, connection)) { cmd.CommandType = CommandType.Text; cmd.Parameters.AddWithValue("@HospitalitySession", obj.HospitalitySession); cmd.Parameters.AddWithValue("@Patient", obj.Patient); cmd.Parameters.AddWithValue("@Doctor", obj.Doctor); cmd.Parameters.AddWithValue("@ConsiliumDate", obj.ConsiliumDate); cmd.Parameters.AddWithValue("@Goal", obj.Goal == null ? "" : obj.Goal); cmd.Parameters.AddWithValue("@Dynamics", obj.Dynamics == null ? "" : obj.Dynamics); cmd.Parameters.AddWithValue("@Diagnosis", obj.Diagnosis == null ? "" : obj.Diagnosis); cmd.Parameters.AddWithValue("@Decision", obj.Decision == null ? "" : obj.Decision); cmd.Parameters.AddWithValue("@DutyAdminName", obj.DutyAdminName == null ? "" : obj.DutyAdminName); cmd.Parameters.AddWithValue("@ObjectId", obj.ObjectId); cmd.ExecuteNonQuery(); } return(obj.ObjectId); } else { string sql = "INSERT INTO ddt_consilium(dsid_hospitality_session,dsid_patient,dsid_doctor,dsdt_consilium_date,dss_goal,dss_dynamics,dss_diagnosis,dss_decision,dss_duty_admin_name) " + "VALUES(@HospitalitySession,@Patient,@Doctor,@ConsiliumDate,@Goal,@Dynamics,@Diagnosis,@Decision,@DutyAdminName) RETURNING r_object_id"; Logger.Debug(CultureInfo.CurrentCulture, "SQL: {0}", sql); using (Npgsql.NpgsqlCommand cmd = new Npgsql.NpgsqlCommand(sql, connection)) { cmd.CommandType = CommandType.Text; cmd.Parameters.AddWithValue("@HospitalitySession", obj.HospitalitySession); cmd.Parameters.AddWithValue("@Patient", obj.Patient); cmd.Parameters.AddWithValue("@Doctor", obj.Doctor); cmd.Parameters.AddWithValue("@ConsiliumDate", obj.ConsiliumDate); cmd.Parameters.AddWithValue("@Goal", obj.Goal == null ? "" : obj.Goal); cmd.Parameters.AddWithValue("@Dynamics", obj.Dynamics == null ? "" : obj.Dynamics); cmd.Parameters.AddWithValue("@Diagnosis", obj.Diagnosis == null ? "" : obj.Diagnosis); cmd.Parameters.AddWithValue("@Decision", obj.Decision == null ? "" : obj.Decision); cmd.Parameters.AddWithValue("@DutyAdminName", obj.DutyAdminName == null ? "" : obj.DutyAdminName); return((string)cmd.ExecuteScalar()); } } } }
public string Save(DdtInspection obj) { using (dynamic connection = connectionFactory.GetConnection()) { if (GetById(obj.ObjectId) != null) { string sql = "UPDATE ddt_inspection SET " + "dsid_hospitality_session = @HospitalitySession, " + "dsid_patient = @Patient, " + "dsid_doctor = @Doctor, " + "dsdt_inspection_date = @InspectionDate, " + "dss_diagnosis = @Diagnosis, " + "dss_complaints = @Complaints, " + "dss_inspection = @Inspection, " + "dss_kateter_placement = @KateterPlacement, " + "dss_inspection_result = @InspectionResult " + "WHERE r_object_id = @ObjectId"; Logger.Debug(CultureInfo.CurrentCulture, "SQL: {0}", sql); using (Npgsql.NpgsqlCommand cmd = new Npgsql.NpgsqlCommand(sql, connection)) { cmd.CommandType = CommandType.Text; cmd.Parameters.AddWithValue("@HospitalitySession", obj.HospitalitySession); cmd.Parameters.AddWithValue("@Patient", obj.Patient); cmd.Parameters.AddWithValue("@Doctor", obj.Doctor); cmd.Parameters.AddWithValue("@InspectionDate", obj.InspectionDate); cmd.Parameters.AddWithValue("@Diagnosis", obj.Diagnosis == null ? "" : obj.Diagnosis); cmd.Parameters.AddWithValue("@Complaints", obj.Complaints == null ? "" : obj.Complaints); cmd.Parameters.AddWithValue("@Inspection", obj.Inspection == null ? "" : obj.Inspection); cmd.Parameters.AddWithValue("@KateterPlacement", obj.KateterPlacement == null ? "" : obj.KateterPlacement); cmd.Parameters.AddWithValue("@InspectionResult", obj.InspectionResult == null ? "" : obj.InspectionResult); cmd.Parameters.AddWithValue("@ObjectId", obj.ObjectId); cmd.ExecuteNonQuery(); } return(obj.ObjectId); } else { string sql = "INSERT INTO ddt_inspection(dsid_hospitality_session,dsid_patient,dsid_doctor,dsdt_inspection_date,dss_diagnosis,dss_complaints,dss_inspection,dss_kateter_placement,dss_inspection_result) " + "VALUES(@HospitalitySession,@Patient,@Doctor,@InspectionDate,@Diagnosis,@Complaints,@Inspection,@KateterPlacement,@InspectionResult) RETURNING r_object_id"; Logger.Debug(CultureInfo.CurrentCulture, "SQL: {0}", sql); using (Npgsql.NpgsqlCommand cmd = new Npgsql.NpgsqlCommand(sql, connection)) { cmd.CommandType = CommandType.Text; cmd.Parameters.AddWithValue("@HospitalitySession", obj.HospitalitySession); cmd.Parameters.AddWithValue("@Patient", obj.Patient); cmd.Parameters.AddWithValue("@Doctor", obj.Doctor); cmd.Parameters.AddWithValue("@InspectionDate", obj.InspectionDate); cmd.Parameters.AddWithValue("@Diagnosis", obj.Diagnosis == null ? "" : obj.Diagnosis); cmd.Parameters.AddWithValue("@Complaints", obj.Complaints == null ? "" : obj.Complaints); cmd.Parameters.AddWithValue("@Inspection", obj.Inspection == null ? "" : obj.Inspection); cmd.Parameters.AddWithValue("@KateterPlacement", obj.KateterPlacement == null ? "" : obj.KateterPlacement); cmd.Parameters.AddWithValue("@InspectionResult", obj.InspectionResult == null ? "" : obj.InspectionResult); return((string)cmd.ExecuteScalar()); } } } }
public string Save(DdtTransfer obj) { using (dynamic connection = connectionFactory.GetConnection()) { if (GetById(obj.ObjectId) != null) { string sql = "UPDATE ddt_transfer SET " + "dsid_hospitality_session = @HospitalitySession, " + "dsid_patient = @Patient, " + "dsid_doctor = @Doctor, " + "dsdt_start_date = @StartDate, " + "dsdt_end_date = @EndDate, " + "dss_destination = @Destination, " + "dss_contacts = @Contacts, " + "dss_transfer_justification = @TransferJustification, " + "dsi_type = @Type " + "WHERE r_object_id = @ObjectId"; Logger.Debug(CultureInfo.CurrentCulture, "SQL: {0}", sql); using (Npgsql.NpgsqlCommand cmd = new Npgsql.NpgsqlCommand(sql, connection)) { cmd.CommandType = CommandType.Text; cmd.Parameters.AddWithValue("@HospitalitySession", obj.HospitalitySession); cmd.Parameters.AddWithValue("@Patient", obj.Patient); cmd.Parameters.AddWithValue("@Doctor", obj.Doctor); cmd.Parameters.AddWithValue("@StartDate", obj.StartDate); cmd.Parameters.AddWithValue("@EndDate", obj.EndDate); cmd.Parameters.AddWithValue("@Destination", obj.Destination == null ? "" : obj.Destination); cmd.Parameters.AddWithValue("@Contacts", obj.Contacts == null ? "" : obj.Contacts); cmd.Parameters.AddWithValue("@TransferJustification", obj.TransferJustification == null ? "" : obj.TransferJustification); cmd.Parameters.AddWithValue("@Type", obj.Type); cmd.Parameters.AddWithValue("@ObjectId", obj.ObjectId); cmd.ExecuteNonQuery(); } return(obj.ObjectId); } else { string sql = "INSERT INTO ddt_transfer(dsid_hospitality_session,dsid_patient,dsid_doctor,dsdt_start_date,dsdt_end_date,dss_destination,dss_contacts,dss_transfer_justification,dsi_type) " + "VALUES(@HospitalitySession,@Patient,@Doctor,@StartDate,@EndDate,@Destination,@Contacts,@TransferJustification,@Type) RETURNING r_object_id"; Logger.Debug(CultureInfo.CurrentCulture, "SQL: {0}", sql); using (Npgsql.NpgsqlCommand cmd = new Npgsql.NpgsqlCommand(sql, connection)) { cmd.CommandType = CommandType.Text; cmd.Parameters.AddWithValue("@HospitalitySession", obj.HospitalitySession); cmd.Parameters.AddWithValue("@Patient", obj.Patient); cmd.Parameters.AddWithValue("@Doctor", obj.Doctor); cmd.Parameters.AddWithValue("@StartDate", obj.StartDate); cmd.Parameters.AddWithValue("@EndDate", obj.EndDate); cmd.Parameters.AddWithValue("@Destination", obj.Destination == null ? "" : obj.Destination); cmd.Parameters.AddWithValue("@Contacts", obj.Contacts == null ? "" : obj.Contacts); cmd.Parameters.AddWithValue("@TransferJustification", obj.TransferJustification == null ? "" : obj.TransferJustification); cmd.Parameters.AddWithValue("@Type", obj.Type); return((string)cmd.ExecuteScalar()); } } } }
public void Execute(string sql) { using (dynamic connection = connectionFactory.GetConnection()) { Npgsql.NpgsqlCommand command = new Npgsql.NpgsqlCommand(sql, connection); command.ExecuteScalar(); } }
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()); } }
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; var exists = (long)cmd.ExecuteScalar(); return(exists > 0); } }
public string Save(DdtHolter obj) { using (dynamic connection = connectionFactory.GetConnection()) { if (GetById(obj.ObjectId) != null) { string sql = "UPDATE ddt_holter SET " + "dsid_hospitality_session = @HospitalitySession, " + "dsid_patient = @Patient, " + "dsid_doctor = @Doctor, " + "dsid_parent = @Parent, " + "dss_parent_type = @ParentType, " + "dsdt_analysis_date = @AnalysisDate, " + "dss_holter = @Holter, " + "dss_monitoring_ad = @MonitoringAd " + "WHERE r_object_id = @ObjectId"; Logger.Debug(CultureInfo.CurrentCulture, "SQL: {0}", sql); using (Npgsql.NpgsqlCommand cmd = new Npgsql.NpgsqlCommand(sql, connection)) { cmd.CommandType = CommandType.Text; cmd.Parameters.AddWithValue("@HospitalitySession", obj.HospitalitySession); cmd.Parameters.AddWithValue("@Patient", obj.Patient); cmd.Parameters.AddWithValue("@Doctor", obj.Doctor); cmd.Parameters.AddWithValue("@Parent", obj.Parent == null ? "0000000000000000" : obj.Parent); cmd.Parameters.AddWithValue("@ParentType", obj.ParentType == null ? "" : obj.ParentType); cmd.Parameters.AddWithValue("@AnalysisDate", obj.AnalysisDate); cmd.Parameters.AddWithValue("@Holter", obj.Holter == null ? "" : obj.Holter); cmd.Parameters.AddWithValue("@MonitoringAd", obj.MonitoringAd == null ? "" : obj.MonitoringAd); cmd.Parameters.AddWithValue("@ObjectId", obj.ObjectId); cmd.ExecuteNonQuery(); } return(obj.ObjectId); } else { string sql = "INSERT INTO ddt_holter(dsid_hospitality_session,dsid_patient,dsid_doctor,dsid_parent,dss_parent_type,dsdt_analysis_date,dss_holter,dss_monitoring_ad) " + "VALUES(@HospitalitySession,@Patient,@Doctor,@Parent,@ParentType,@AnalysisDate,@Holter,@MonitoringAd) RETURNING r_object_id"; Logger.Debug(CultureInfo.CurrentCulture, "SQL: {0}", sql); using (Npgsql.NpgsqlCommand cmd = new Npgsql.NpgsqlCommand(sql, connection)) { cmd.CommandType = CommandType.Text; cmd.Parameters.AddWithValue("@HospitalitySession", obj.HospitalitySession); cmd.Parameters.AddWithValue("@Patient", obj.Patient); cmd.Parameters.AddWithValue("@Doctor", obj.Doctor); cmd.Parameters.AddWithValue("@Parent", obj.Parent == null ? "0000000000000000" : obj.Parent); cmd.Parameters.AddWithValue("@ParentType", obj.ParentType == null ? "" : obj.ParentType); cmd.Parameters.AddWithValue("@AnalysisDate", obj.AnalysisDate); cmd.Parameters.AddWithValue("@Holter", obj.Holter == null ? "" : obj.Holter); cmd.Parameters.AddWithValue("@MonitoringAd", obj.MonitoringAd == null ? "" : obj.MonitoringAd); return((string)cmd.ExecuteScalar()); } } } }
public string Save(DdtHormones obj) { using (dynamic connection = connectionFactory.GetConnection()) { if (GetById(obj.ObjectId) != null) { string sql = "UPDATE ddt_hormones SET " + "dsid_hospitality_session = @HospitalitySession, " + "dsid_patient = @Patient, " + "dsid_doctor = @Doctor, " + "dsdt_analysis_date = @AnalysisDate, " + "dss_t3 = @T3, " + "dss_t4 = @T4, " + "dss_ttg = @Ttg " + "WHERE r_object_id = @ObjectId"; Logger.Debug(CultureInfo.CurrentCulture, "SQL: {0}", sql); using (Npgsql.NpgsqlCommand cmd = new Npgsql.NpgsqlCommand(sql, connection)) { cmd.CommandType = CommandType.Text; cmd.Parameters.AddWithValue("@HospitalitySession", obj.HospitalitySession); cmd.Parameters.AddWithValue("@Patient", obj.Patient); cmd.Parameters.AddWithValue("@Doctor", obj.Doctor); cmd.Parameters.AddWithValue("@AnalysisDate", obj.AnalysisDate); cmd.Parameters.AddWithValue("@T3", obj.T3 == null ? "" : obj.T3); cmd.Parameters.AddWithValue("@T4", obj.T4 == null ? "" : obj.T4); cmd.Parameters.AddWithValue("@Ttg", obj.Ttg == null ? "" : obj.Ttg); cmd.Parameters.AddWithValue("@ObjectId", obj.ObjectId); cmd.ExecuteNonQuery(); } return(obj.ObjectId); } else { string sql = "INSERT INTO ddt_hormones(dsid_hospitality_session,dsid_patient,dsid_doctor,dsdt_analysis_date,dss_t3,dss_t4,dss_ttg) " + "VALUES(@HospitalitySession,@Patient,@Doctor,@AnalysisDate,@T3,@T4,@Ttg) RETURNING r_object_id"; Logger.Debug(CultureInfo.CurrentCulture, "SQL: {0}", sql); using (Npgsql.NpgsqlCommand cmd = new Npgsql.NpgsqlCommand(sql, connection)) { cmd.CommandType = CommandType.Text; cmd.Parameters.AddWithValue("@HospitalitySession", obj.HospitalitySession); cmd.Parameters.AddWithValue("@Patient", obj.Patient); cmd.Parameters.AddWithValue("@Doctor", obj.Doctor); cmd.Parameters.AddWithValue("@AnalysisDate", obj.AnalysisDate); cmd.Parameters.AddWithValue("@T3", obj.T3 == null ? "" : obj.T3); cmd.Parameters.AddWithValue("@T4", obj.T4 == null ? "" : obj.T4); cmd.Parameters.AddWithValue("@Ttg", obj.Ttg == null ? "" : obj.Ttg); return((string)cmd.ExecuteScalar()); } } } }
public string Save(DdtCoagulogram obj) { using (dynamic connection = connectionFactory.GetConnection()) { if (GetById(obj.ObjectId) != null) { string sql = "UPDATE ddt_coagulogram SET " + "dsid_hospitality_session = @HospitalitySession, " + "dsid_patient = @Patient, " + "dsid_doctor = @Doctor, " + "dsdt_analysis_date = @AnalysisDate, " + "dss_achtv = @Achtv, " + "dss_mcho = @Mcho, " + "dss_ddimer = @Ddimer " + "WHERE r_object_id = @ObjectId"; Logger.Debug(CultureInfo.CurrentCulture, "SQL: {0}", sql); using (Npgsql.NpgsqlCommand cmd = new Npgsql.NpgsqlCommand(sql, connection)) { cmd.CommandType = CommandType.Text; cmd.Parameters.AddWithValue("@HospitalitySession", obj.HospitalitySession); cmd.Parameters.AddWithValue("@Patient", obj.Patient); cmd.Parameters.AddWithValue("@Doctor", obj.Doctor); cmd.Parameters.AddWithValue("@AnalysisDate", obj.AnalysisDate); cmd.Parameters.AddWithValue("@Achtv", obj.Achtv == null ? "" : obj.Achtv); cmd.Parameters.AddWithValue("@Mcho", obj.Mcho == null ? "" : obj.Mcho); cmd.Parameters.AddWithValue("@Ddimer", obj.Ddimer == null ? "" : obj.Ddimer); cmd.Parameters.AddWithValue("@ObjectId", obj.ObjectId); cmd.ExecuteNonQuery(); } return(obj.ObjectId); } else { string sql = "INSERT INTO ddt_coagulogram(dsid_hospitality_session,dsid_patient,dsid_doctor,dsdt_analysis_date,dss_achtv,dss_mcho,dss_ddimer) " + "VALUES(@HospitalitySession,@Patient,@Doctor,@AnalysisDate,@Achtv,@Mcho,@Ddimer) RETURNING r_object_id"; Logger.Debug(CultureInfo.CurrentCulture, "SQL: {0}", sql); using (Npgsql.NpgsqlCommand cmd = new Npgsql.NpgsqlCommand(sql, connection)) { cmd.CommandType = CommandType.Text; cmd.Parameters.AddWithValue("@HospitalitySession", obj.HospitalitySession); cmd.Parameters.AddWithValue("@Patient", obj.Patient); cmd.Parameters.AddWithValue("@Doctor", obj.Doctor); cmd.Parameters.AddWithValue("@AnalysisDate", obj.AnalysisDate); cmd.Parameters.AddWithValue("@Achtv", obj.Achtv == null ? "" : obj.Achtv); cmd.Parameters.AddWithValue("@Mcho", obj.Mcho == null ? "" : obj.Mcho); cmd.Parameters.AddWithValue("@Ddimer", obj.Ddimer == null ? "" : obj.Ddimer); return((string)cmd.ExecuteScalar()); } } } }
public void DeleteHistoryById(string operationId) { using (dynamic connection = connectionFactory.GetConnection()) { String sql = String.Format("delete from ddt_history WHERE dsid_operation_id='{0}'", operationId); Logger.Debug(CultureInfo.CurrentCulture, "SQL: {0}", sql); Npgsql.NpgsqlCommand command = new Npgsql.NpgsqlCommand(sql, connection); command.ExecuteScalar(); } }
public void RemoveConnectionByParentAndChildIds(string parentId, string childId) { String query = String.Format("DELETE FROM ddt_relation WHERE dsid_parent='{0}' AND dsid_child='{1}'", parentId, childId); using (dynamic connection = connectionFactory.GetConnection()) { Logger.Debug(CultureInfo.CurrentCulture, "SQL: {0}", query); Npgsql.NpgsqlCommand command = new Npgsql.NpgsqlCommand(query, connection); command.ExecuteScalar(); } }
public string Save(DdtJournalDay obj) { using (dynamic connection = connectionFactory.GetConnection()) { if (GetById(obj.ObjectId) != null) { string sql = "UPDATE ddt_journal_day SET " + "dsid_hospitality_session = @HospitalitySession, " + "dsid_patient = @Patient, " + "dsdt_admission_date = @AdmissionDate, " + "dsid_doctor = @Doctor, " + "dsi_journal_type = @JournalType, " + "dss_diagnosis = @Diagnosis, " + "dss_name = @Name " + "WHERE r_object_id = @ObjectId"; Logger.Debug(CultureInfo.CurrentCulture, "SQL: {0}", sql); using (Npgsql.NpgsqlCommand cmd = new Npgsql.NpgsqlCommand(sql, connection)) { cmd.CommandType = CommandType.Text; cmd.Parameters.AddWithValue("@HospitalitySession", obj.HospitalitySession); cmd.Parameters.AddWithValue("@Patient", obj.Patient); cmd.Parameters.AddWithValue("@AdmissionDate", obj.AdmissionDate); cmd.Parameters.AddWithValue("@Doctor", obj.Doctor); cmd.Parameters.AddWithValue("@Name", obj.Name == null ? "" : obj.Name); cmd.Parameters.AddWithValue("@JournalType", obj.JournalType); cmd.Parameters.AddWithValue("@Diagnosis", obj.Diagnosis); cmd.Parameters.AddWithValue("@ObjectId", obj.ObjectId); cmd.ExecuteNonQuery(); } return(obj.ObjectId); } else { string sql = "INSERT INTO ddt_journal_day(dsid_hospitality_session,dsid_patient,dsdt_admission_date,dsid_doctor,dss_name,dsi_journal_type,dss_diagnosis) " + "VALUES(@HospitalitySession,@Patient,@AdmissionDate,@Doctor,@Name,@JournalType,@Diagnosis) RETURNING r_object_id"; Logger.Debug(CultureInfo.CurrentCulture, "SQL: {0}", sql); using (Npgsql.NpgsqlCommand cmd = new Npgsql.NpgsqlCommand(sql, connection)) { cmd.CommandType = CommandType.Text; cmd.Parameters.AddWithValue("@HospitalitySession", obj.HospitalitySession); cmd.Parameters.AddWithValue("@Patient", obj.Patient); cmd.Parameters.AddWithValue("@AdmissionDate", obj.AdmissionDate); cmd.Parameters.AddWithValue("@Doctor", obj.Doctor); cmd.Parameters.AddWithValue("@Name", obj.Name == null ? "" : obj.Name); cmd.Parameters.AddWithValue("@JournalType", obj.JournalType); cmd.Parameters.AddWithValue("@Diagnosis", obj.Diagnosis == null ? "" : obj.Diagnosis); return((string)cmd.ExecuteScalar()); } } } }
public void RemoveConnectionById(string id) { String query = String.Format("DELETE FROM ddt_relation WHERE r_object_id='{0}' ", id); using (dynamic connection = connectionFactory.GetConnection()) { Logger.Debug(CultureInfo.CurrentCulture, "SQL: {0}", query); Npgsql.NpgsqlCommand command = new Npgsql.NpgsqlCommand(query, connection); command.ExecuteScalar(); } }
} // End Sub CreateUser public static void DropCreateDb(string dbName) { string sql = @" CREATE DATABASE " + dbName + @" WITH OWNER = postgres ENCODING = 'UTF8' TABLESPACE = pg_default LC_COLLATE = 'C' LC_CTYPE = 'C' CONNECTION LIMIT = -1; "; // sql = "CREATE ROLE \"" + System.Environment.UserName + "\" WITH PASSWORD 'TopSecret' LOGIN SUPERUSER CREATEDB CREATEROLE REPLICATION VALID UNTIL 'infinity';"; // System.Console.WriteLine(sql); using (Npgsql.NpgsqlConnection con = new Npgsql.NpgsqlConnection(GetConnectionString())) { using (Npgsql.NpgsqlCommand cmd = con.CreateCommand()) { if (con.State != System.Data.ConnectionState.Open) { con.Open(); } cmd.CommandText = "SELECT COUNT(*) FROM pg_database WHERE datname = '" + dbName.Replace("'", "''") + "'"; long countOfExistingDbsWithTHisName = (long)cmd.ExecuteScalar(); if (countOfExistingDbsWithTHisName > 0) { cmd.CommandText = @"SELECT pg_terminate_backend(pg_stat_activity.pid) FROM pg_stat_activity WHERE pg_stat_activity.datname = '" + dbName.Replace("'", "''") + @"' AND pid <> pg_backend_pid();"; cmd.ExecuteNonQuery(); cmd.CommandText = "DROP DATABASE " + dbName + ";"; cmd.ExecuteNonQuery(); } // End if (dbCount > 0) cmd.CommandText = sql; cmd.ExecuteNonQuery(); if (con.State != System.Data.ConnectionState.Closed) { con.Close(); } } // End Using cmd } // End using con } // End Sub DropCreateDb
/// <summary> /// 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); } }
public void update(string query) { Npgsql.NpgsqlConnection connection = null; try { connection = getConnection(); Npgsql.NpgsqlCommand command = new Npgsql.NpgsqlCommand(query, connection); command.ExecuteScalar(); } finally { if (connection != null) { connection.Close(); } } }
public string insert(string query) { Npgsql.NpgsqlConnection connection = null; try { connection = getConnection(); Npgsql.NpgsqlCommand command = new Npgsql.NpgsqlCommand(query, connection); return((string)command.ExecuteScalar()); } finally { if (connection != null) { connection.Close(); } } }
public string Save(DdtConsiliumGroupMember obj) { using (dynamic connection = connectionFactory.GetConnection()) { if (GetById(obj.ObjectId) != null) { string sql = "UPDATE ddt_consilium_group_member SET " + "dsid_group = @Group, " + "dss_name = @Name, " + "dsid_doctor = @Doctor, " + "dsid_group = @Group " + "WHERE r_object_id = @ObjectId"; Logger.Debug(CultureInfo.CurrentCulture, "SQL: {0}", sql); using (Npgsql.NpgsqlCommand cmd = new Npgsql.NpgsqlCommand(sql, connection)) { cmd.CommandType = CommandType.Text; cmd.Parameters.AddWithValue("@Group", obj.Group); cmd.Parameters.AddWithValue("@Name", obj.Name == null ? "" : obj.Name); cmd.Parameters.AddWithValue("@Doctor", obj.Doctor); cmd.Parameters.AddWithValue("@Group", obj.Group); cmd.Parameters.AddWithValue("@ObjectId", obj.ObjectId); cmd.ExecuteNonQuery(); } return(obj.ObjectId); } else { string sql = "INSERT INTO ddt_consilium_group_member(dsid_group,dss_name,dsid_doctor,dsid_group) " + "VALUES(@Group,@Name,@Doctor,@Group) RETURNING r_object_id"; Logger.Debug(CultureInfo.CurrentCulture, "SQL: {0}", sql); using (Npgsql.NpgsqlCommand cmd = new Npgsql.NpgsqlCommand(sql, connection)) { cmd.CommandType = CommandType.Text; cmd.Parameters.AddWithValue("@Group", obj.Group); cmd.Parameters.AddWithValue("@Name", obj.Name == null ? "" : obj.Name); cmd.Parameters.AddWithValue("@Doctor", obj.Doctor); cmd.Parameters.AddWithValue("@Group", obj.Group); return((string)cmd.ExecuteScalar()); } } } }
/// <summary> /// Finish the given batch. /// </summary> /// <param name="batchId">The batch to finish.</param> /// <returns></returns> private int FinishBatch(long batchId) { _logger.LogDebug($"Finishing batch ID {batchId}"); int batchID; using (var cnt = new Npgsql.NpgsqlConnection(_pgOptions.ConnectionString)) { cnt.Open(); using (var cmd = new Npgsql.NpgsqlCommand()) { cmd.Connection = cnt; cmd.Parameters.AddWithValue("@batchId", batchId); cmd.CommandText = "select * from pgq.finish_batch(@batchId);"; batchID = (int)cmd.ExecuteScalar(); } } return(batchID); }
public string insertObject <T>(T obj, string tableName) { Npgsql.NpgsqlConnection connection = null; try { connection = getConnection(); string query = convertObjectFieldsInQuery(obj, tableName); Npgsql.NpgsqlCommand command = new Npgsql.NpgsqlCommand(query, connection); return((string)command.ExecuteScalar()); } finally { if (connection != null) { connection.Close(); } } }
public string Save(DdtRelation relation) { using (dynamic connection = connectionFactory.GetConnection()) { if (GetById(relation.ObjectId) != null) { string sql = "UPDATE ddt_relation SET " + "dsid_parent = @Parent, " + "dsid_child = @Child, " + "dss_child_type = @ChildType " + "WHERE r_object_id = @ObjectId"; Logger.Debug(CultureInfo.CurrentCulture, "SQL: {0}", sql); using (Npgsql.NpgsqlCommand cmd = new Npgsql.NpgsqlCommand(sql, connection)) { cmd.CommandType = CommandType.Text; cmd.Parameters.AddWithValue("@Parent", relation.Parent); cmd.Parameters.AddWithValue("@Child", relation.Child); cmd.Parameters.AddWithValue("@ChildType", relation.ChildType); cmd.Parameters.AddWithValue("@ObjectId", relation.ObjectId); cmd.ExecuteNonQuery(); } return(relation.ObjectId); } else { string sql = "INSERT INTO ddt_relation(dsid_parent,dsid_child,dss_child_type) " + "VALUES(@Parent,@Child,@ChildType) RETURNING r_object_id"; Logger.Debug(CultureInfo.CurrentCulture, "SQL: {0}", sql); using (Npgsql.NpgsqlCommand cmd = new Npgsql.NpgsqlCommand(sql, connection)) { cmd.CommandType = CommandType.Text; cmd.Parameters.AddWithValue("@Parent", relation.Parent); cmd.Parameters.AddWithValue("@Child", relation.Child); cmd.Parameters.AddWithValue("@ChildType", relation.ChildType); return((string)cmd.ExecuteScalar()); } } } }
/// <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); }
public string Save(DdtIssuedMedicine obj) { using (dynamic connection = connectionFactory.GetConnection()) { if (GetById(obj.ObjectId) != null) { string sql = "UPDATE ddt_issued_medicine SET " + "dsid_med_list = @MedList, " + "dsid_cure = @Cure " + "WHERE r_object_id = @ObjectId"; Logger.Debug(CultureInfo.CurrentCulture, "SQL: {0}", sql); using (Npgsql.NpgsqlCommand cmd = new Npgsql.NpgsqlCommand(sql, connection)) { cmd.CommandType = CommandType.Text; cmd.Parameters.AddWithValue("@MedList", obj.MedList); cmd.Parameters.AddWithValue("@Cure", obj.Cure); cmd.Parameters.AddWithValue("@ObjectId", obj.ObjectId); cmd.ExecuteNonQuery(); } return(obj.ObjectId); } else { string sql = "INSERT INTO ddt_issued_medicine(dsid_med_list,dsid_cure) " + "VALUES(@MedList,@Cure) RETURNING r_object_id"; Logger.Debug(CultureInfo.CurrentCulture, "SQL: {0}", sql); using (Npgsql.NpgsqlCommand cmd = new Npgsql.NpgsqlCommand(sql, connection)) { cmd.CommandType = CommandType.Text; cmd.Parameters.AddWithValue("@MedList", obj.MedList); cmd.Parameters.AddWithValue("@Cure", obj.Cure); obj.ObjectId = (string)cmd.ExecuteScalar(); return(obj.ObjectId); } } } }
public void updateObject <T>(T obj, string tableName, string conditionAttrName, string conditionAttrValue) { Npgsql.NpgsqlConnection connection = null; try { connection = getConnection(); StringBuilder builder = new StringBuilder(); builder.Append(@"UPDATE ").Append(tableName).Append(" SET "); FieldInfo[] fields = typeof(T).GetFields(BindingFlags.NonPublic | BindingFlags.Instance); for (int i = 0; i < fields.Length; i++) { FieldInfo fieldInfo = fields[i]; TableAttribute attrInfo = Attribute.GetCustomAttribute(fieldInfo, typeof(TableAttribute)) as TableAttribute; if (attrInfo != null && attrInfo.CanSetAttr) { object value = fieldInfo.GetValue(obj); builder.Append(attrInfo.AttrName).Append("=").Append(getWrappedValue(value, fieldInfo.FieldType)); if (i < fields.Length - 1) { builder.Append(","); } } } builder.Append(" WHERE ").Append(conditionAttrName).Append("='").Append(conditionAttrValue).Append("'"); Console.WriteLine(builder.ToString()); Npgsql.NpgsqlCommand command = new Npgsql.NpgsqlCommand(builder.ToString(), connection); command.ExecuteScalar(); } finally { if (connection != null) { connection.Close(); } } }
/// <summary> /// Queries the PostGIS database to get the name of the Geometry Column. This is used if the columnname isn't specified in the constructor /// </summary> /// <remarks></remarks> /// <returns>Name of column containing geometry</returns> private string GetGeometryColumn() { string strSQL = "select f_geometry_column from geometry_columns WHERE f_table_name='" + this.Table + "'"; using (Npgsql.NpgsqlConnection conn = new Npgsql.NpgsqlConnection(_ConnectionString)) using (Npgsql.NpgsqlCommand command = new Npgsql.NpgsqlCommand(strSQL, conn)) { conn.Open(); object columnname = command.ExecuteScalar(); conn.Close(); if (columnname == System.DBNull.Value) throw new ApplicationException("Table '" + this.Table + "' does not contain a geometry column"); return (string)columnname; } }
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(); } }
/// <summary> /// Boundingbox of dataset /// </summary> /// <returns>boundingbox</returns> public SharpMap.Geometries.BoundingBox GetExtents() { using (Npgsql.NpgsqlConnection conn = new Npgsql.NpgsqlConnection(_ConnectionString)) { string strSQL = "SELECT EXTENT(" + this.GeometryColumn + ") FROM " + this.Table; if (!String.IsNullOrEmpty(_defintionQuery)) strSQL += " WHERE " + this.DefinitionQuery; using (Npgsql.NpgsqlCommand command = new Npgsql.NpgsqlCommand(strSQL, conn)) { conn.Open(); object result = command.ExecuteScalar(); conn.Close(); if (result == System.DBNull.Value) return null; string strBox = (string)result; if (strBox.StartsWith("BOX(")) { string[] vals = strBox.Substring(4, strBox.IndexOf(")")-4).Split(new char[2] { ',', ' ' }); return SharpMap.Converters.Geometries.GeometryFactory.CreateEnvelope( double.Parse(vals[0], SharpMap.Map.numberFormat_EnUS), double.Parse(vals[2], SharpMap.Map.numberFormat_EnUS), double.Parse(vals[1], SharpMap.Map.numberFormat_EnUS), double.Parse(vals[3], SharpMap.Map.numberFormat_EnUS)); } else return null; } } }
/// <summary> /// Returns the 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; }
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(); } } }