예제 #1
1
        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();
        }
예제 #2
0
 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();
 }
예제 #3
0
        public IList <DdtEkg> GetByParentId(string parentId)
        {
            IList <DdtEkg> list = new List <DdtEkg>();

            if (parentId == null)
            {
                return(list);
            }
            using (dynamic connection = connectionFactory.GetConnection())
            {
                String sql = String.Format(CultureInfo.CurrentCulture, "SELECT dsid_hospitality_session, ek.r_object_id, dsdt_analysis_date, " +
                                           "r_modify_date, ek.dss_parent_type, r_creation_date, rel.dsid_parent, dsb_admission_analysis, dss_ekg, " +
                                           "dsid_doctor, dsid_patient FROM ddt_ekg ek, ddt_relation rel WHERE rel.dsid_parent = '{0}' AND rel.dsid_child=ek.r_object_id", parentId);

                Logger.Debug(CultureInfo.CurrentCulture, "SQL: {0}", sql);

                Npgsql.NpgsqlCommand command = new Npgsql.NpgsqlCommand(sql, connection);
                using (DbDataReader reader = command.ExecuteReader())
                {
                    while (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);
                        list.Add(obj);
                    }
                }
            }
            return(list);
        }
예제 #4
0
        public DdtIssuedMedicineList GetLastMedList(string id)
        {
            using (dynamic connection = connectionFactory.GetConnection())
            {
                String sql = String.Format("SELECT r_object_id, dss_has_kag, dsid_parent_id, dsid_pharmacologist, dss_diagnosis, r_creation_date, " +
                                           "dsid_director, dsid_doctor, dsid_patient, dsid_hospitality_session, dsid_nurse, r_modify_date, dss_parent_type, dss_template_name, " +
                                           "dsdt_issuing_date, dsb_skip_print FROM ddt_issued_medicine_list WHERE dsid_hospitality_session='{0}' ORDER BY r_modify_date DESC", id);

                Logger.Debug(CultureInfo.CurrentCulture, "SQL: {0}", sql);

                Npgsql.NpgsqlCommand command = new Npgsql.NpgsqlCommand(sql, connection);
                using (DbDataReader reader = command.ExecuteReader())
                {
                    if (reader.Read())
                    {
                        DdtIssuedMedicineList obj = new DdtIssuedMedicineList();
                        obj.ObjectId           = reader.IsDBNull(0) ? null : reader.GetString(0);
                        obj.HasKag             = reader.IsDBNull(1) ? null : reader.GetString(1);
                        obj.ParentId           = reader.IsDBNull(2) ? null : reader.GetString(2);
                        obj.Pharmacologist     = reader.IsDBNull(3) ? null : reader.GetString(3);
                        obj.Diagnosis          = reader.IsDBNull(4) ? null : reader.GetString(4);
                        obj.CreationDate       = reader.IsDBNull(5) ? DateTime.MinValue : reader.GetDateTime(5);
                        obj.Director           = reader.IsDBNull(6) ? null : reader.GetString(6);
                        obj.Doctor             = reader.IsDBNull(7) ? null : reader.GetString(7);
                        obj.Patient            = reader.IsDBNull(8) ? null : reader.GetString(8);
                        obj.HospitalitySession = reader.IsDBNull(9) ? null : reader.GetString(9);
                        obj.Nurse        = reader.IsDBNull(10) ? null : reader.GetString(10);
                        obj.ModifyDate   = reader.IsDBNull(11) ? DateTime.MinValue : reader.GetDateTime(11);
                        obj.ParentType   = reader.IsDBNull(12) ? null : reader.GetString(12);
                        obj.TemplateName = reader.IsDBNull(13) ? null : reader.GetString(13);
                        obj.IssuingDate  = reader.IsDBNull(14) ? DateTime.MinValue : reader.GetDateTime(14);
                        obj.SkipPrint    = reader.GetBoolean(15);

                        return(obj);
                    }
                }
            }
            return(null);
        }
예제 #5
0
        public Recipe GetRecipe(int id)
        {
            _conn.Open();

            var data = new object[] { };

            // Retrieve all rows
            using (var cmd = new Npgsql.NpgsqlCommand("SELECT id, user_id, name, ingredients, instructions FROM recipes WHERE id = @id LIMIT 1", _conn))
            {
                cmd.Parameters.AddWithValue("id", id);
                var reader = cmd.ExecuteReader();
                while (reader.Read())
                {
                    reader.GetValues(data);
                }
                Console.WriteLine(reader.GetString(0));
            }

            _conn.Close();

            return(new Recipe(int.Parse(data[0].ToString()), data[1].ToString(), data[2].ToString(), data[3].ToString()));
        }
        public IList <DdtOncologicMarkers> GetAll()
        {
            IList <DdtOncologicMarkers> list = new List <DdtOncologicMarkers>();

            using (dynamic connection = connectionFactory.GetConnection())
            {
                String sql = "SELECT r_object_id, dsdt_analysis_date, r_creation_date, dss_cea, dsid_parent, dss_psa_common, dss_psa_free, dsid_doctor, dsid_patient, dsid_hospitality_session, dss_hgch, r_modify_date, dss_parent_type, dss_ca_125, dss_ca_199, dss_ca_153, dss_afr FROM ddt_oncologic_markers";

                Logger.Debug(CultureInfo.CurrentCulture, "SQL: {0}", sql);

                Npgsql.NpgsqlCommand command = new Npgsql.NpgsqlCommand(sql, connection);
                using (DbDataReader reader = command.ExecuteReader())
                {
                    while (reader.Read())
                    {
                        DdtOncologicMarkers obj = new DdtOncologicMarkers();
                        obj.ObjectId           = reader.IsDBNull(0) ? null : reader.GetString(0);
                        obj.AnalysisDate       = reader.IsDBNull(1) ? DateTime.MinValue : reader.GetDateTime(1);
                        obj.CreationDate       = reader.IsDBNull(2) ? DateTime.MinValue : reader.GetDateTime(2);
                        obj.Cea                = reader.IsDBNull(3) ? null : reader.GetString(3);
                        obj.Parent             = reader.IsDBNull(4) ? null : reader.GetString(4);
                        obj.PsaCommon          = reader.IsDBNull(5) ? null : reader.GetString(5);
                        obj.PsaFree            = reader.IsDBNull(6) ? null : reader.GetString(6);
                        obj.Doctor             = reader.IsDBNull(7) ? null : reader.GetString(7);
                        obj.Patient            = reader.IsDBNull(8) ? null : reader.GetString(8);
                        obj.HospitalitySession = reader.IsDBNull(9) ? null : reader.GetString(9);
                        obj.Hgch               = reader.IsDBNull(10) ? null : reader.GetString(10);
                        obj.ModifyDate         = reader.IsDBNull(11) ? DateTime.MinValue : reader.GetDateTime(11);
                        obj.ParentType         = reader.IsDBNull(12) ? null : reader.GetString(12);
                        obj.Ca125              = reader.IsDBNull(13) ? null : reader.GetString(13);
                        obj.Ca199              = reader.IsDBNull(14) ? null : reader.GetString(14);
                        obj.Ca153              = reader.IsDBNull(15) ? null : reader.GetString(15);
                        obj.Afr                = reader.IsDBNull(16) ? null : reader.GetString(16);
                        list.Add(obj);
                    }
                }
            }
            return(list);
        }
예제 #7
0
        /// <summary>
        /// Get's all users from database.
        /// </summary>
        /// <returns></returns>
        public IEnumerable <User> GetAllUsers()
        {
            //This is a fairly generic db query with no parameters and pulling all values from reader and tossing data into User
            IList <User> allUsers = new List <User>();

            using (Npgsql.NpgsqlConnection conn = new Npgsql.NpgsqlConnection(Infrastructure.ConfigReader.ConnectionString.ToString()))
            {
                conn.Open();
                using (Npgsql.NpgsqlCommand command = new Npgsql.NpgsqlCommand(SelectAllQuery, conn))
                {
                    using (Npgsql.NpgsqlDataReader dr = command.ExecuteReader())
                    {
                        while (dr.Read())
                        {
                            User newUser = populateUserFromDB(dr);
                            allUsers.Add(newUser);
                        }
                    }
                }
            }
            return(allUsers);
        }
        public IEnumerable <SubPracticeHeadCountDto> GetSubPracticeWiseHeadCountFromPostgres()
        {
            List <SubPracticeHeadCountDto> records = new List <SubPracticeHeadCountDto>();

            Npgsql.NpgsqlConnection con = null;
            try
            {
                con = new Npgsql.NpgsqlConnection(PostgresSqlQueries.CONNECTION_STRING);
                con.Open();
                string qry = PostgresSqlQueries.GET_SUB_PRACTICE_WISE_HEAD_COUNT;
                qry = qry.Replace("__CURRENT_DATE__", $"{DateTime.Today.Year}-{DateTime.Today.Month}-{DateTime.Today.Day}");
                Npgsql.NpgsqlCommand    cmd = new Npgsql.NpgsqlCommand(qry, con);
                Npgsql.NpgsqlDataReader res = cmd.ExecuteReader();
                int?nullInt = null;
                if (res.HasRows)
                {
                    while (res.Read())
                    {
                        records.Add(new SubPracticeHeadCountDto
                        {
                            HeadCount     = res.IsDBNull(4) == false ? (int)res.GetInt64(4) : nullInt,
                            Practice      = res.IsDBNull(1) == false ? res.GetString(1) : "",
                            PracticeID    = res.IsDBNull(0) == false ? (int)res.GetInt64(0) : nullInt,
                            SubPractice   = res.IsDBNull(3) == false ? res.GetString(3) : "",
                            SubPracticeID = res.IsDBNull(2) == false ? (int)res.GetInt64(2) : nullInt
                        });
                    }
                }
            }
            catch (Exception) { }
            finally
            {
                con.Close();
                con.Dispose();
            }

            return(records);
        }
예제 #9
0
        public void Select(string sql, string key, string value, OnKeyValue handler)
        {
            Logger.Debug(CultureInfo.CurrentCulture, "SQL: {0}", sql);
            using (dynamic connection = connectionFactory.GetConnection())
            {
                Npgsql.NpgsqlCommand command = new Npgsql.NpgsqlCommand(sql, connection);
                using (DbDataReader reader = command.ExecuteReader())
                {
                    System.Collections.ObjectModel.ReadOnlyCollection <DbColumn> columns = reader.GetColumnSchema();
                    IEnumerator <DbColumn> colsNumerator = columns.GetEnumerator();

                    while (reader.Read())
                    {
                        string attrKeyValue = null;
                        string attrValValue = null;
                        while (colsNumerator.MoveNext())
                        {
                            if (key.Equals(colsNumerator.Current.ColumnName, StringComparison.Ordinal))
                            {
                                int indx = columns.IndexOf(colsNumerator.Current);
                                attrKeyValue = getWrappedValue(reader.GetValue(indx), reader.GetFieldType(indx));
                            }
                            else if (value.Equals(colsNumerator.Current.ColumnName, StringComparison.Ordinal))
                            {
                                int indx = columns.IndexOf(colsNumerator.Current);
                                attrValValue = getWrappedValue(reader.GetValue(indx), reader.GetFieldType(indx));
                            }
                        }
                        colsNumerator.Reset();

                        if (attrKeyValue != null)
                        {
                            handler(attrKeyValue, attrValValue);
                        }
                    }
                }
            }
        }
예제 #10
0
        public IList <DdtCoagulogram> getByParentId(string parentId)
        {
            IList <DdtCoagulogram> list = new List <DdtCoagulogram>();

            if (parentId == null)
            {
                return(list);
            }

            using (dynamic connection = connectionFactory.GetConnection())
            {
                String sql = String.Format("SELECT dss_ddimer, dsid_hospitality_session, cc.r_object_id, dsdt_analysis_date, r_modify_date, dss_mcho, r_creation_date, dss_achtv, " +
                                           "dsid_doctor, dsid_patient FROM ddt_coagulogram cc, ddt_relation rel WHERE rel.dsid_parent = '{0}' AND rel.dsid_child=cc.r_object_id", parentId);

                Logger.Debug(CultureInfo.CurrentCulture, "SQL: {0}", sql);

                Npgsql.NpgsqlCommand command = new Npgsql.NpgsqlCommand(sql, connection);
                using (DbDataReader reader = command.ExecuteReader())
                {
                    while (reader.Read())
                    {
                        DdtCoagulogram obj = new DdtCoagulogram();
                        obj.Ddimer             = reader.IsDBNull(0) ? null : reader.GetString(0);
                        obj.HospitalitySession = reader.IsDBNull(1) ? null : reader.GetString(1);
                        obj.ObjectId           = reader.IsDBNull(2) ? null : reader.GetString(2);
                        obj.AnalysisDate       = reader.IsDBNull(3) ? DateTime.MinValue : reader.GetDateTime(3);
                        obj.ModifyDate         = reader.IsDBNull(4) ? DateTime.MinValue : reader.GetDateTime(4);
                        obj.Mcho         = reader.IsDBNull(5) ? null : reader.GetString(5);
                        obj.CreationDate = reader.IsDBNull(6) ? DateTime.MinValue : reader.GetDateTime(6);
                        obj.Achtv        = reader.IsDBNull(7) ? null : reader.GetString(7);
                        obj.Doctor       = reader.IsDBNull(8) ? null : reader.GetString(8);
                        obj.Patient      = reader.IsDBNull(9) ? null : reader.GetString(9);
                        list.Add(obj);
                    }
                }
            }
            return(list);
        }
예제 #11
0
        public IList <DdtHospital> GetAll()
        {
            IList <DdtHospital> list = new List <DdtHospital>();

            using (dynamic connection = connectionFactory.GetConnection())
            {
                String sql = "SELECT r_object_id, dss_diagnosis, dsid_duty_doctor, r_creation_date, dsi_release_type, dsdt_admission_date, dsid_patient, dsid_curing_doctor, dsb_active, r_modify_date, dsb_reject_cure, dss_room_cell, dsb_death, dsid_dir_cardio_reanim_doctor, dsid_substitution_doctor, dsid_anesthetist_doctor FROM ddt_hospital";

                Logger.Debug(CultureInfo.CurrentCulture, "SQL: {0}", sql);

                Npgsql.NpgsqlCommand command = new Npgsql.NpgsqlCommand(sql, connection);
                using (DbDataReader reader = command.ExecuteReader())
                {
                    while (reader.Read())
                    {
                        DdtHospital obj = new DdtHospital();
                        obj.ObjectId              = reader.IsDBNull(0) ? null : reader.GetString(0);
                        obj.Diagnosis             = reader.IsDBNull(1) ? null : reader.GetString(1);
                        obj.DutyDoctor            = reader.IsDBNull(2) ? null : reader.GetString(2);
                        obj.CreationDate          = reader.IsDBNull(3) ? DateTime.MinValue : reader.GetDateTime(3);
                        obj.ReleaseType           = reader.IsDBNull(4) ? -1 : reader.GetInt16(4);
                        obj.AdmissionDate         = reader.IsDBNull(5) ? DateTime.MinValue : reader.GetDateTime(5);
                        obj.Patient               = reader.IsDBNull(6) ? null : reader.GetString(6);
                        obj.CuringDoctor          = reader.IsDBNull(7) ? null : reader.GetString(7);
                        obj.Active                = reader.GetBoolean(8);
                        obj.ModifyDate            = reader.IsDBNull(9) ? DateTime.MinValue : reader.GetDateTime(9);
                        obj.RejectCure            = reader.GetBoolean(10);
                        obj.RoomCell              = reader.IsDBNull(11) ? null : reader.GetString(11);
                        obj.Death                 = reader.GetBoolean(12);
                        obj.DirCardioReanimDoctor = reader.IsDBNull(13) ? null : reader.GetString(13);
                        obj.SubstitutionDoctor    = reader.IsDBNull(14) ? null : reader.GetString(14);
                        obj.AnesthetistDoctor     = reader.IsDBNull(15) ? null : reader.GetString(15);
                        list.Add(obj);
                    }
                }
            }
            return(list);
        }
예제 #12
0
 /// <summary>
 /// get XmlData list
 /// </summary>
 /// <returns></returns>
 public IList <XElement> GetXElements()
 {
     CreateTable();
     using (var con = new Npgsql.NpgsqlConnection(_connectionString))
     {
         var sql = @"select ""XmlData"" from public.""DataProtectionKeys"" ";
         using (var cmd = new Npgsql.NpgsqlCommand(sql, con))
         {
             var elements = new List <XElement>();
             con.Open();
             using (var reader = cmd.ExecuteReader())
             {
                 while (reader.Read())
                 {
                     elements.Add(XElement.Parse(reader.GetString(0)));
                 }
                 reader.Close();
             }
             con.Close();
             return(elements);
         }
     }
 }
예제 #13
0
 public IList <AppointmentSheet> AssignAppointments(int appointmentid)
 {
     assignableAppointments.Clear();
     using (Npgsql.NpgsqlConnection conn = new Npgsql.NpgsqlConnection(Infrastructure.ConfigReader.ConnectionString.ToString()))
     {
         conn.Open();
         using (Npgsql.NpgsqlCommand command = new Npgsql.NpgsqlCommand(GetAppointmentQuery, conn))
         {
             command.Parameters.Add(new Npgsql.NpgsqlParameter(":appointmentsheetid", NpgsqlTypes.NpgsqlDbType.Integer));
             command.Prepare();
             command.Parameters[0].Value = appointmentid;
             using (Npgsql.NpgsqlDataReader dr = command.ExecuteReader())
             {
                 while (dr.Read())
                 {
                     AppointmentSheet newAppointment = populateAppointmentFromDB(dr);
                     assignableAppointments.Add(newAppointment);
                 }
             }
         }
     }
     return(assignableAppointments);
 }
예제 #14
0
 public IList <Lead> Assignleads(int leadid)
 {
     assignableLeads.Clear();
     using (Npgsql.NpgsqlConnection conn = new Npgsql.NpgsqlConnection(Infrastructure.ConfigReader.ConnectionString.ToString()))
     {
         conn.Open();
         using (Npgsql.NpgsqlCommand command = new Npgsql.NpgsqlCommand(GetLeadsQuery, conn))
         {
             command.Parameters.Add(new Npgsql.NpgsqlParameter(":leadid", NpgsqlTypes.NpgsqlDbType.Integer));
             command.Prepare();
             command.Parameters[0].Value = leadid;
             using (Npgsql.NpgsqlDataReader dr = command.ExecuteReader())
             {
                 while (dr.Read())
                 {
                     Lead newLead = populateLeadFromDB(dr);
                     assignableLeads.Add(newLead);
                 }
             }
         }
     }
     return(assignableLeads);
 }
예제 #15
0
        public DdvAllDiagnosis GetById(string id)
        {
            using (dynamic connection = connectionFactory.GetConnection())
            {
                String sql = String.Format("SELECT dsid_hospitality_session, r_object_id, object_type, dss_diagnosis FROM ddv_all_diagnosis 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())
                    {
                        DdvAllDiagnosis obj = new DdvAllDiagnosis();
                        obj.HospitalitySession = reader.IsDBNull(0) ? null : reader.GetString(0);
                        obj.ObjectId           = reader.IsDBNull(1) ? null : reader.GetString(1);
                        obj.Type      = reader.IsDBNull(2) ? null : reader.GetString(2);
                        obj.Diagnosis = reader.IsDBNull(3) ? null : reader.GetString(3);
                        return(obj);
                    }
                }
            }
            return(null);
        }
예제 #16
0
        public T queryObject <T>(string query)
        {
            Npgsql.NpgsqlConnection connection = null;
            try
            {
                connection = getConnection();
                Npgsql.NpgsqlCommand    command = new Npgsql.NpgsqlCommand(query, connection);
                Npgsql.NpgsqlDataReader reader  = command.ExecuteReader();

                if (reader.Read())
                {
                    return(fillObject <T>(reader));
                }
            }
            finally
            {
                if (connection != null)
                {
                    connection.Close();
                }
            }
            return(default(T));
        }
예제 #17
0
 public IEnumerable <Ticket> GetTicketsByAccountID(int accountid)
 {
     realTickets.Clear();
     using (Npgsql.NpgsqlConnection conn = new Npgsql.NpgsqlConnection(Infrastructure.ConfigReader.ConnectionString.ToString()))
     {
         conn.Open();
         using (Npgsql.NpgsqlCommand command = new Npgsql.NpgsqlCommand(TicketAccountSelectQuery, conn))
         {
             command.Parameters.Add(new Npgsql.NpgsqlParameter("accountid", NpgsqlTypes.NpgsqlDbType.Integer));
             command.Prepare();
             command.Parameters[0].Value = accountid;
             using (Npgsql.NpgsqlDataReader dr = command.ExecuteReader())
             {
                 while (dr.Read())
                 {
                     Ticket newTicket = populateTicketsFromDB(dr);
                     realTickets.Add(newTicket);
                 }
             }
         }
     }
     return(realTickets.AsQueryable());
 }
예제 #18
0
        //  private static int counter = 1;

        public PhoneUser GetPhoneUser(int userid)
        {
            PhoneUser newPhoneuser = new PhoneUser();

            using (Npgsql.NpgsqlConnection conn = new Npgsql.NpgsqlConnection(Infrastructure.ConfigReader.ConnectionString.ToString()))
            {
                conn.Open();
                using (Npgsql.NpgsqlCommand command = new Npgsql.NpgsqlCommand(UserSelectQuery, 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())
                        {
                            newPhoneuser = populatePhoneUserFromDB(dr);
                        }
                    }
                }
            }
            return(newPhoneuser);
        }
예제 #19
0
 public IEnumerable <TicketHistory> GetTicketHistoryByTicketID(string ticketid)
 {
     realTicketHistory.Clear();
     using (Npgsql.NpgsqlConnection conn = new Npgsql.NpgsqlConnection(Infrastructure.ConfigReader.ConnectionString.ToString()))
     {
         conn.Open();
         using (Npgsql.NpgsqlCommand command = new Npgsql.NpgsqlCommand(TicketHistoryTicketSelectQuery, conn))
         {
             command.Parameters.Add(new Npgsql.NpgsqlParameter("ticketid", NpgsqlTypes.NpgsqlDbType.Text));
             command.Prepare();
             command.Parameters[0].Value = ticketid;
             using (Npgsql.NpgsqlDataReader dr = command.ExecuteReader())
             {
                 while (dr.Read())
                 {
                     TicketHistory newTicketHistory = populateTicketHistoryFromDB(dr);
                     realTicketHistory.Add(newTicketHistory);
                 }
             }
         }
     }
     return(realTicketHistory.AsQueryable());
 }
예제 #20
0
        public IList <DdtHormones> GetByParentId(string parentId)
        {
            IList <DdtHormones> list = new List <DdtHormones>();

            if (parentId == null)
            {
                return(list);
            }
            using (dynamic connection = connectionFactory.GetConnection())
            {
                String sql = "SELECT dsid_hospitality_session, ho.r_object_id, dss_ttg, dsdt_analysis_date, dss_t3, r_modify_date, dss_t4, r_creation_date, " +
                             "dsid_doctor, dsid_patient FROM ddt_hormones ho, ddt_relation rel WHERE rel.dsid_parent = '{0}' AND rel.dsid_child=ho.r_object_id";

                Logger.Debug(CultureInfo.CurrentCulture, "SQL: {0}", sql);

                Npgsql.NpgsqlCommand command = new Npgsql.NpgsqlCommand(sql, connection);
                using (DbDataReader reader = command.ExecuteReader())
                {
                    while (reader.Read())
                    {
                        DdtHormones obj = new DdtHormones();
                        obj.HospitalitySession = reader.IsDBNull(0) ? null : reader.GetString(0);
                        obj.ObjectId           = reader.IsDBNull(1) ? null : reader.GetString(1);
                        obj.Ttg          = reader.IsDBNull(2) ? null : reader.GetString(2);
                        obj.AnalysisDate = reader.IsDBNull(3) ? DateTime.MinValue : reader.GetDateTime(3);
                        obj.T3           = reader.IsDBNull(4) ? null : reader.GetString(4);
                        obj.ModifyDate   = reader.IsDBNull(5) ? DateTime.MinValue : reader.GetDateTime(5);
                        obj.T4           = reader.IsDBNull(6) ? null : reader.GetString(6);
                        obj.CreationDate = reader.IsDBNull(7) ? DateTime.MinValue : reader.GetDateTime(7);
                        obj.Doctor       = reader.IsDBNull(8) ? null : reader.GetString(8);
                        obj.Patient      = reader.IsDBNull(9) ? null : reader.GetString(9);
                        list.Add(obj);
                    }
                }
            }
            return(list);
        }
예제 #21
0
 public string recoverPassword(string userRequesting)
 {
     using (var connection = GetConnection())
     {
         connection.Open();
         var sql = "select * from users where username =@user or email=@mail";
         using (var cmd = new Npgsql.NpgsqlCommand())
         {
             cmd.Connection  = connection;
             cmd.CommandText = sql;
             cmd.Parameters.AddWithValue("@user", userRequesting);
             cmd.Parameters.AddWithValue("@mail", userRequesting);
             cmd.CommandType = CommandType.Text;
             Npgsql.NpgsqlDataReader reader = cmd.ExecuteReader();
             if (reader.Read() == true)
             {
                 string username    = reader.GetString(1);
                 string password    = reader.GetString(2);
                 string email       = reader.GetString(3);
                 var    mailService = new EmailService.SystemSupportMail();
                 mailService.sendMail(
                     subject: "System: Password Recovery request",
                     body: "Hi " + username + "\n you requested to recover your password.\n" +
                     "your current password is " + password,
                     recipientMail: new List <string> {
                     email
                 });
                 return("Hi " + username + "\n you requested to recover your password.\n" +
                        "your current password is " + password);
             }
             else
             {
                 return("Sorry you dont have any accout with us");
             }
         }
     }
 }
예제 #22
0
        public IList <DdtUzi> GetAll()
        {
            IList <DdtUzi> list = new List <DdtUzi>();

            using (dynamic connection = connectionFactory.GetConnection())
            {
                String sql = "SELECT r_object_id, dsdt_analysis_date, dss_eho_kg, r_creation_date, dss_pleurs_uzi, dsid_parent, dsid_doctor, dsid_patient, " +
                             "dsid_hospitality_session, dss_uzd_bca, r_modify_date, dss_parent_type, dss_cds, dss_uzi_obp FROM ddt_uzi";
                Logger.Debug(CultureInfo.CurrentCulture, "SQL: {0}", sql);

                Npgsql.NpgsqlCommand command = new Npgsql.NpgsqlCommand(sql, connection);
                using (DbDataReader reader = command.ExecuteReader())
                {
                    while (reader.Read())
                    {
                        DdtUzi obj = new DdtUzi();
                        obj.ObjectId           = reader.IsDBNull(0) ? null : reader.GetString(0);
                        obj.AnalysisDate       = reader.IsDBNull(1) ? DateTime.MinValue : reader.GetDateTime(1);
                        obj.EhoKg              = reader.IsDBNull(2) ? null : reader.GetString(2);
                        obj.CreationDate       = reader.IsDBNull(3) ? DateTime.MinValue : reader.GetDateTime(3);
                        obj.PleursUzi          = reader.IsDBNull(4) ? null : reader.GetString(4);
                        obj.Parent             = 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.UzdBca             = reader.IsDBNull(9) ? null : reader.GetString(9);
                        obj.ModifyDate         = reader.IsDBNull(10) ? DateTime.MinValue : reader.GetDateTime(10);
                        obj.ParentType         = reader.IsDBNull(11) ? null : reader.GetString(11);
                        obj.Cds    = reader.IsDBNull(12) ? null : reader.GetString(12);
                        obj.UziObp = reader.IsDBNull(13) ? null : reader.GetString(13);
                        list.Add(obj);
                    }
                }
            }
            return(list);
        }
예제 #23
0
        public List <DistanciaViagemDTO> GetDistanciaViagem(int radarInicio, int radarFinal)
        {
            List <DistanciaViagemDTO> listaRetorno = new List <DistanciaViagemDTO>();

            using (Npgsql.NpgsqlConnection conn = new Npgsql.NpgsqlConnection(connString))
            {
                Npgsql.NpgsqlCommand comm = conn.CreateCommand();
                comm.CommandTimeout = 420;

                comm.CommandType = CommandType.Text;
                comm.CommandText =
                    "select br0.codigo as RadarInicio, " +
                    "br1.codigo as RadarFinal, " +
                    "ST_Distance(ST_Transform(concat('SRID=4326;POINT(', cast(br0.lat as varchar(20)), ' ', cast(br0.lon as varchar(20)), ')')::geometry, 3857), " +
                    "ST_Transform(concat('SRID=4326;POINT(', cast(br1.lat as varchar(20)), ' ', cast(br1.lon as varchar(20)), ')')::geometry, 3857)) * cosd(42.3521) as distancia " +
                    "from base_radares_lat_lon br0 inner join base_radares_lat_lon br1 " +
                    "on br1.codigo = " + radarFinal + " where br0.codigo = " + radarInicio + "; ";

                conn.Open();

                Npgsql.NpgsqlDataReader dr = comm.ExecuteReader();

                while (dr.Read())
                {
                    DistanciaViagemDTO ett = new DistanciaViagemDTO();

                    ett.codigoRadarInicio = Convert.ToInt32(dr["RadarInicio"]);
                    ett.codigoRadarFinal  = Convert.ToInt32(dr["RadarFinal"]);
                    ett.distancia         = Convert.ToDecimal(dr["distancia"]);

                    listaRetorno.Add(ett);
                }
            }

            return(listaRetorno);
        }
예제 #24
0
        private IList <DdtRelation> Select(string query)
        {
            IList <DdtRelation> list = new List <DdtRelation>();

            using (dynamic connection = connectionFactory.GetConnection())
            {
                Logger.Debug(CultureInfo.CurrentCulture, "SQL: {0}", query);

                Npgsql.NpgsqlCommand command = new Npgsql.NpgsqlCommand(query, connection);
                using (DbDataReader reader = command.ExecuteReader())
                {
                    while (reader.Read())
                    {
                        DdtRelation obj = new DdtRelation();
                        obj.ObjectId  = reader.IsDBNull(0) ? null : reader.GetString(0);
                        obj.Parent    = reader.IsDBNull(1) ? null : reader.GetString(1);
                        obj.Child     = reader.IsDBNull(2) ? null : reader.GetString(2);
                        obj.ChildType = reader.IsDBNull(3) ? null : reader.GetString(3);
                        list.Add(obj);
                    }
                }
            }
            return(list);
        }
예제 #25
0
        public DmGroupUsers GetById(string id)
        {
            using (dynamic connection = connectionFactory.GetConnection())
            {
                String sql = String.Format("SELECT r_object_id, r_modify_date, r_creation_date, dsid_doctor_id, dss_group_name FROM dm_group_users 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())
                    {
                        DmGroupUsers obj = new DmGroupUsers();
                        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.DoctorId     = reader.IsDBNull(3) ? null : reader.GetString(3);
                        obj.GroupName    = reader.IsDBNull(4) ? null : reader.GetString(4);
                        return(obj);
                    }
                }
            }
            return(null);
        }
예제 #26
0
        public IQueryable <Domain.Lead> LeadByStatus(string status, int userId)
        {
            fakeLeads.Clear();
            using (Npgsql.NpgsqlConnection conn = new Npgsql.NpgsqlConnection(Infrastructure.ConfigReader.ConnectionString.ToString()))
            {
                conn.Open();
                using (Npgsql.NpgsqlCommand command = new Npgsql.NpgsqlCommand(LeadByStatusSelectQuery, conn))
                {
                    command.Parameters.AddWithValue("status", status);
                    command.Parameters.AddWithValue("userId", userId);
                    command.Prepare();

                    using (Npgsql.NpgsqlDataReader dr = command.ExecuteReader())
                    {
                        while (dr.Read())
                        {
                            Lead newLead = populateLeadFromDB(dr);
                            fakeLeads.Add(newLead);
                        }
                    }
                }
            }
            return(fakeLeads.AsQueryable());
        }
예제 #27
0
        public IList <DdtSerology> GetAll()
        {
            IList <DdtSerology> list = new List <DdtSerology>();

            using (dynamic connection = connectionFactory.GetConnection())
            {
                String sql = "SELECT r_object_id, dsdt_analysis_date, dss_blood_type, r_creation_date, dss_kell_ag, dss_phenotype, dss_rw, dsid_doctor, dsid_patient, dss_hbs_ag, dss_anti_hcv, dsid_hospitality_session, r_modify_date, dss_hiv, dss_rhesus_factor FROM ddt_serology";
                Logger.Debug(CultureInfo.CurrentCulture, "SQL: {0}", sql);

                Npgsql.NpgsqlCommand command = new Npgsql.NpgsqlCommand(sql, connection);
                using (DbDataReader reader = command.ExecuteReader())
                {
                    while (reader.Read())
                    {
                        DdtSerology obj = new DdtSerology();
                        obj.ObjectId           = reader.IsDBNull(0) ? null : reader.GetString(0);
                        obj.AnalysisDate       = reader.IsDBNull(1) ? DateTime.MinValue : reader.GetDateTime(1);
                        obj.BloodType          = reader.IsDBNull(2) ? null : reader.GetString(2);
                        obj.CreationDate       = reader.IsDBNull(3) ? DateTime.MinValue : reader.GetDateTime(3);
                        obj.KellAg             = reader.IsDBNull(4) ? null : reader.GetString(4);
                        obj.Phenotype          = reader.IsDBNull(5) ? null : reader.GetString(5);
                        obj.Rw                 = reader.IsDBNull(6) ? null : reader.GetString(6);
                        obj.Doctor             = reader.IsDBNull(7) ? null : reader.GetString(7);
                        obj.Patient            = reader.IsDBNull(8) ? null : reader.GetString(8);
                        obj.HbsAg              = reader.IsDBNull(9) ? null : reader.GetString(9);
                        obj.AntiHcv            = reader.IsDBNull(10) ? null : reader.GetString(10);
                        obj.HospitalitySession = reader.IsDBNull(11) ? null : reader.GetString(11);
                        obj.ModifyDate         = reader.IsDBNull(12) ? DateTime.MinValue : reader.GetDateTime(12);
                        obj.Hiv                = reader.IsDBNull(13) ? null : reader.GetString(13);
                        obj.RhesusFactor       = reader.IsDBNull(14) ? null : reader.GetString(14);
                        list.Add(obj);
                    }
                }
            }
            return(list);
        }
예제 #28
0
        public IList <DdtVariousSpecConcluson> GetListByParentId(string parentId)
        {
            IList <DdtVariousSpecConcluson> list = new List <DdtVariousSpecConcluson>();

            using (dynamic connection = connectionFactory.GetConnection())
            {
                String sql = String.Format("SELECT dss_additional_info0, r_object_id, dsb_visible, dss_specialist_type, dss_additional_info2, dss_additional_info1, r_creation_date, dsid_parent, dsdt_admission_date, dss_additional_info4, dss_additional_info3, dss_specialist_conclusion, r_modify_date, dss_parent_type, dsb_additional_bool FROM ddt_various_spec_concluson WHERE dsid_parent = '{0}' AND dsb_additional_bool = false order by dsdt_admission_date", parentId);
                Logger.Debug(CultureInfo.CurrentCulture, "SQL: {0}", sql);

                Npgsql.NpgsqlCommand command = new Npgsql.NpgsqlCommand(sql, connection);
                using (DbDataReader reader = command.ExecuteReader())
                {
                    while (reader.Read())
                    {
                        DdtVariousSpecConcluson obj = new DdtVariousSpecConcluson();
                        obj.AdditionalInfo0      = reader.IsDBNull(0) ? null : reader.GetString(0);
                        obj.ObjectId             = reader.IsDBNull(1) ? null : reader.GetString(1);
                        obj.Visible              = reader.GetBoolean(2);
                        obj.SpecialistType       = reader.IsDBNull(3) ? null : reader.GetString(3);
                        obj.AdditionalInfo2      = reader.IsDBNull(4) ? null : reader.GetString(4);
                        obj.AdditionalInfo1      = reader.IsDBNull(5) ? null : reader.GetString(5);
                        obj.CreationDate         = reader.IsDBNull(6) ? DateTime.MinValue : reader.GetDateTime(6);
                        obj.Parent               = reader.IsDBNull(7) ? null : reader.GetString(7);
                        obj.AdmissionDate        = reader.IsDBNull(8) ? DateTime.MinValue : reader.GetDateTime(8);
                        obj.AdditionalInfo4      = reader.IsDBNull(9) ? null : reader.GetString(9);
                        obj.AdditionalInfo3      = reader.IsDBNull(10) ? null : reader.GetString(10);
                        obj.SpecialistConclusion = reader.IsDBNull(11) ? null : reader.GetString(11);
                        obj.ModifyDate           = reader.IsDBNull(12) ? DateTime.MinValue : reader.GetDateTime(12);
                        obj.ParentType           = reader.IsDBNull(13) ? null : reader.GetString(13);
                        obj.AdditionalBool       = reader.GetBoolean(14);
                        list.Add(obj);
                    }
                }
            }
            return(list);
        }
예제 #29
0
        public IEnumerable <Equipment> GetEquipmentByType(string equipmenttype)
        {
            IList <Domain.Equipment> equiptype = new List <Domain.Equipment>();

            using (Npgsql.NpgsqlConnection conn = new Npgsql.NpgsqlConnection(Infrastructure.ConfigReader.ConnectionString.ToString()))
            {
                conn.Open();
                using (Npgsql.NpgsqlCommand command = new Npgsql.NpgsqlCommand(EquipmentTypeQuery, conn))
                {
                    command.Parameters.Add(new Npgsql.NpgsqlParameter("equiptype", NpgsqlTypes.NpgsqlDbType.Text));
                    command.Prepare();
                    command.Parameters[0].Value = equipmenttype;
                    using (Npgsql.NpgsqlDataReader dr = command.ExecuteReader())
                    {
                        while (dr.Read())
                        {
                            Equipment newequip = populateEquipmentFromDB(dr);
                            equiptype.Add(newequip);
                        }
                    }
                }
            }
            return(equiptype);
        }
예제 #30
0
        void ShowDate()
        {
            try
            {
                var sql = @"select(c.model || ' ' || b.brand) as c_col, count(p.id) as avto
                            from car c, brands b, purchases p
                                where b.id = c.brand_id and c.id = p.car_id
                                    group by c_col;";
                Connection.con = new Npgsql.NpgsqlConnection(Connection.connection());
                Connection.con.Open();
                Npgsql.NpgsqlCommand    cmd    = new Npgsql.NpgsqlCommand(sql, Connection.con);
                DataTable               dt     = new DataTable();
                Npgsql.NpgsqlDataReader reader = cmd.ExecuteReader();
                dt.Load(reader);
                dataGridView1.DataSource = dt;

                Main.LocalizeHeaders(dataGridView1);
                Connection.con.Close();
            }
            catch
            {
                Connection.con.Close();
            }
        }
예제 #31
0
        public DmGroup GetGroupByName(string groupName)
        {
            using (dynamic connection = connectionFactory.GetConnection())
            {
                String sql = String.Format("SELECT r_object_id, dss_description, r_modify_date, r_creation_date, dss_name FROM dm_group WHERE dss_name = '{0}'", groupName);
                Logger.Debug(CultureInfo.CurrentCulture, "SQL: {0}", sql);

                Npgsql.NpgsqlCommand command = new Npgsql.NpgsqlCommand(sql, connection);
                using (DbDataReader reader = command.ExecuteReader())
                {
                    if (reader.Read())
                    {
                        DmGroup obj = new DmGroup();
                        obj.ObjectId     = reader.IsDBNull(0) ? null : reader.GetString(0);
                        obj.Description  = reader.IsDBNull(1) ? null : reader.GetString(1);
                        obj.ModifyDate   = reader.IsDBNull(2) ? DateTime.MinValue : reader.GetDateTime(2);
                        obj.CreationDate = reader.IsDBNull(3) ? DateTime.MinValue : reader.GetDateTime(3);
                        obj.Name         = reader.IsDBNull(4) ? null : reader.GetString(4);
                        return(obj);
                    }
                }
            }
            return(null);
        }
예제 #32
0
        public static DataTable Npgsql_Data(String sql)
        {
            //參考資料
            //https://www.npgsql.org/doc/index.html
            String connstr = ConfigurationManager.ConnectionStrings["ConnectionString"].ConnectionString;

            DataTable dt = new DataTable();

            using (Npgsql.NpgsqlConnection conn = new Npgsql.NpgsqlConnection(connstr))
            {
                conn.Open();
                using (var cmd = new Npgsql.NpgsqlCommand())
                {
                    cmd.Connection  = conn;
                    cmd.CommandText = sql;
                    //執行不Query的方式
                    //cmd.ExecuteNonQuery();
                    Npgsql.NpgsqlDataReader dr = cmd.ExecuteReader();
                    dt.Load(dr);
                }
            }

            return(dt);
        }
예제 #33
0
 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;
         }
     }
 }
예제 #34
0
 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;
         }
     }
 }
예제 #35
0
		/// <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;
		}
예제 #36
0
        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;
        }
예제 #37
0
 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;
         }
     }
 }
예제 #38
0
        public override List<List<string>> Select(string sql, List<Database.Bind> bind_var = null)
        {
            if (!this.IsConnected)
                throw new WmibException("The database is not connected");

            Npgsql.NpgsqlCommand command = new Npgsql.NpgsqlCommand(sql, this.connection);
            if (bind_var != null)
                BindVars(sql, bind_var, command);
            SystemHooks.OnSQL(LocalName, sql);
            Npgsql.NpgsqlDataReader dr = command.ExecuteReader();
            List<List<string>> results = new List<List<string>>();
            while (dr.Read())
            {
                List<string> line = new List<string>();
                results.Add(line);
                for (int i = 0; i < dr.FieldCount; i++)
                {
                    line.Add(dr[i].ToString());
                }
            }
            return results;
        }
예제 #39
0
        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;
        }
예제 #40
0
		/// <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;
		}
예제 #41
0
        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;
        }
예제 #42
0
		/// <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;
		}