public IList <Lead> GetWarmLeads(int userId) { warmLeads.Clear(); using (Npgsql.NpgsqlConnection conn = new Npgsql.NpgsqlConnection(Infrastructure.ConfigReader.ConnectionString.ToString())) { conn.Open(); using (Npgsql.NpgsqlCommand command = new Npgsql.NpgsqlCommand(GetWarmLeadsQuery, conn)) { if (userId == -1) { command.Parameters.AddWithValue("userId", null); } else { command.Parameters.AddWithValue("userId", userId); } command.Prepare(); using (Npgsql.NpgsqlDataReader dr = command.ExecuteReader()) { while (dr.Read()) { Lead newLead = populateLeadFromDB(dr); warmLeads.Add(newLead); } } } } return(warmLeads); }
public Account GetAccountByAccountId(int accountid) { //Please see section on using prepared statements in npgsql user manual for explanation on params and query structure Domain.Account Account = new Domain.Account(); using (Npgsql.NpgsqlConnection conn = new Npgsql.NpgsqlConnection(Infrastructure.ConfigReader.ConnectionString.ToString())) { conn.Open(); using (Npgsql.NpgsqlCommand command = new Npgsql.NpgsqlCommand(AccountByAccountIDQuery, 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()) { Account = populateAccountFromDB(dr); } } } } return(Account); }
public List <T> queryObjectsCollection <T>(String query) { List <T> result = new List <T>(); Npgsql.NpgsqlConnection connection = null; try { connection = getConnection(); Npgsql.NpgsqlCommand command = new Npgsql.NpgsqlCommand(query, connection); Npgsql.NpgsqlDataReader reader = command.ExecuteReader(); while (reader.Read()) { T bean = fillObject <T>(reader); result.Add(bean); } } finally { if (connection != null) { connection.Close(); } } return(result); }
public IEnumerable <UserZone> GetAllZonesByUser(int userid) { IList <UserZone> zones = new List <UserZone>(); using (Npgsql.NpgsqlConnection conn = new Npgsql.NpgsqlConnection(Infrastructure.ConfigReader.ConnectionString.ToString())) { conn.Open(); using (Npgsql.NpgsqlCommand command = new Npgsql.NpgsqlCommand(GetZoneByUserQuery, 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()) { var temp = new UserZone() { UserID = Helper.ConvertFromDBVal <int>(dr[0]), ZoneId = Helper.ConvertFromDBVal <int>(dr[1]) }; zones.Add(temp); } } } } return(zones); }
public Domain.Lead LeadByLeadID(int leadid) { Lead newLead = new Lead(); using (Npgsql.NpgsqlConnection conn = new Npgsql.NpgsqlConnection(Infrastructure.ConfigReader.ConnectionString.ToString())) { conn.Open(); using (Npgsql.NpgsqlCommand command = new Npgsql.NpgsqlCommand(LeadByLeadIDSelectQuery, conn)) { command.Parameters.Add(new Npgsql.NpgsqlParameter("leadid", NpgsqlTypes.NpgsqlDbType.Integer)); command.Prepare(); command.Parameters[0].Value = leadid; using (Npgsql.NpgsqlDataReader dr = command.ExecuteReader()) { if (dr.Read()) { newLead = populateLeadFromDB(dr); } //IF there is more than one row coming back for an id, than we should toss an exception if (dr.Read()) { throw new InvalidOperationException("More than one user came back when Querying by UserId"); } } } } return(newLead); }
/// <summary> /// Init /// </summary> /// <param name="reader">Reader</param> public ConstraintModel(Npgsql.NpgsqlDataReader reader) { this.Name = reader.GetString(2); this.Schema = reader.GetString(1); this.Table = reader.GetString(5); this.Type = reader.GetString(6); }
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); }
/// <summary> /// Returns a single user by using a User Id to locate it /// </summary> /// <param name="id"></param> /// <returns></returns> public User GetUserById(int id) { //Please see section on using prepared statements in npgsql user manual for explanation on params and query structure User newUser = null; using (Npgsql.NpgsqlConnection conn = new Npgsql.NpgsqlConnection(Infrastructure.ConfigReader.ConnectionString.ToString())) { conn.Open(); using (Npgsql.NpgsqlCommand command = new Npgsql.NpgsqlCommand(SelectByUserIdQuery, conn)) { command.Parameters.Add(new Npgsql.NpgsqlParameter("user_id", NpgsqlTypes.NpgsqlDbType.Integer)); command.Prepare(); command.Parameters[0].Value = id; using (Npgsql.NpgsqlDataReader dr = command.ExecuteReader()) { if (dr.Read()) { newUser = populateUserFromDB(dr); } //IF there is more than one row coming back for an id, than we should toss an exception if (dr.Read()) { throw new InvalidOperationException("More than one user came back when Querying by UserId"); } } } } return(newUser); }
public User GetUserByUsername(string username) { //Please see section on using prepared statements in npgsql user manual for explanation on params and query structure User newUser = null; using (Npgsql.NpgsqlConnection conn = new Npgsql.NpgsqlConnection(Infrastructure.ConfigReader.ConnectionString.ToString())) { conn.Open(); using (Npgsql.NpgsqlCommand command = new Npgsql.NpgsqlCommand(SelectByUsernameQuery, conn)) { command.Parameters.Add(new Npgsql.NpgsqlParameter("username", NpgsqlTypes.NpgsqlDbType.Text)); command.Prepare(); command.Parameters[0].Value = username; using (Npgsql.NpgsqlDataReader dr = command.ExecuteReader()) { while (dr.Read()) { newUser = populateUserFromDB(dr); } } } } return(newUser); }
public IEnumerable <CalendarEvent> GetEventsByUserId(int userid) { IList <CalendarEvent> userEvents = new List <CalendarEvent>(); using (Npgsql.NpgsqlConnection conn = new Npgsql.NpgsqlConnection(Infrastructure.ConfigReader.ConnectionString.ToString())) { conn.Open(); using (Npgsql.NpgsqlCommand command = new Npgsql.NpgsqlCommand(EventByUserIdQuery, conn)) { command.Parameters.Add(new Npgsql.NpgsqlParameter("userid", NpgsqlTypes.NpgsqlDbType.Integer)); command.Prepare(); command.Parameters[0].Value = userid; using (Npgsql.NpgsqlDataReader dr = command.ExecuteReader()) { while (dr.Read()) { CalendarEvent newEvent = populateEventsFromDB(dr); userEvents.Add(newEvent); } } } } return(userEvents); }
private static CalendarEvent populateEventsFromDB(Npgsql.NpgsqlDataReader dr) { CalendarEvent newEvent = new CalendarEvent(); newEvent.id = Helper.ConvertFromDBVal <int>(dr[0]); newEvent.title = dr[1].ToString(); newEvent.type = dr[2].ToString(); newEvent.description = dr[3].ToString(); newEvent.appointment = Helper.ConvertFromDBVal <Boolean>(dr[4]); newEvent.personal = Helper.ConvertFromDBVal <Boolean>(dr[5]); newEvent.city = dr[6].ToString(); newEvent.state = dr[7].ToString(); newEvent.zipcode = Helper.ConvertFromDBVal <int>(dr[8]); newEvent.zone = Helper.ConvertFromDBVal <int>(dr[9]); newEvent.map = dr[10].ToString(); newEvent.creator = Helper.ConvertFromDBVal <int>(dr[11]); newEvent.assigned = Helper.ConvertFromDBVal <int>(dr[12]); newEvent.start = dr[13].ToString(); newEvent.end = dr[14].ToString(); newEvent.street = dr[15].ToString(); newEvent.Parent_User_Id = Helper.ConvertFromDBVal <int>(dr[16]); newEvent.Parent_Appointment_Id = Helper.ConvertFromDBVal <int>(dr[17]); newEvent.Appointment_Reference = (dr[18]).ToString(); return(newEvent); }
public bool Login(string user, string password) { using (var connection = GetConnection()) { Console.Write(user, password); var sql = "SELECT * FROM users where username="******"'" + user + "' and password= '******'"; connection.Open(); using var cmd = new Npgsql.NpgsqlCommand(sql, connection); Npgsql.NpgsqlDataReader dr = cmd.ExecuteReader(); if (dr.HasRows) { while (dr.Read()) { UserLoginCache.idUser = dr.GetInt32(0); UserLoginCache.Username = dr.GetString(1); UserLoginCache.Email = dr.GetString(3); UserLoginCache.F_name = dr.GetString(4); UserLoginCache.L_name = dr.GetString(5); UserLoginCache.Role = dr.GetString(6); } return(true); } else { return(false); } } }
private static Card populateCardFromDB(Npgsql.NpgsqlDataReader dr) { Card newUser = new Card(); newUser.CardId = Helper.ConvertFromDBVal <int>(dr[0]); newUser.CardType = dr[1].ToString(); newUser.Comment = dr[2].ToString(); newUser.CreatedOn = Helper.ConvertFromDBVal <DateTime>(dr[3]); newUser.LastUpdated = Helper.ConvertFromDBVal <DateTime>(dr[4]); newUser.LeftVM = Helper.ConvertFromDBVal <Boolean>(dr[5]); newUser.NumberCalled = Helper.ConvertFromDBVal <int>(dr[6]); newUser.TalkedToDM = Helper.ConvertFromDBVal <Boolean>(dr[7]); newUser.TalkedToOfficeManager = Helper.ConvertFromDBVal <Boolean>(dr[8]); newUser.TalkedToOther = Helper.ConvertFromDBVal <Boolean>(dr[9]); newUser.TalkedToPerson = Helper.ConvertFromDBVal <Boolean>(dr[10]); newUser.CreatorId = Helper.ConvertFromDBVal <int>(dr[11]); newUser.ParentLeadId = Helper.ConvertFromDBVal <int>(dr[12]); newUser.AssignedAAId = Helper.ConvertFromDBVal <int>(dr[13]); newUser.AppointmentSheetId = Helper.ConvertFromDBVal <int>(dr[14]); newUser.Reassigned = Helper.ConvertFromDBVal <Boolean>(dr[15]); newUser.CreatorName = dr[16].ToString(); newUser.CallBackDate = Helper.ConvertFromDBVal <DateTime>(dr[17]); newUser.NoInterestChk = Helper.ConvertFromDBVal <Boolean>(dr[18]); newUser.NoInterestRea = dr[19].ToString(); newUser.AcquiredDMName = Helper.ConvertFromDBVal <Boolean>(dr[20]); return(newUser); }
public IList <AppointmentSheet> AppointmentQueue(int said, Boolean reschedule) { assignableAppointments.Clear(); using (Npgsql.NpgsqlConnection conn = new Npgsql.NpgsqlConnection(Infrastructure.ConfigReader.ConnectionString.ToString())) { conn.Open(); using (Npgsql.NpgsqlCommand command = new Npgsql.NpgsqlCommand(GetAppointmentQueueQuery, conn)) { command.Parameters.Add(new Npgsql.NpgsqlParameter(":salesagent", NpgsqlTypes.NpgsqlDbType.Integer)); command.Parameters.Add(new Npgsql.NpgsqlParameter(":reschedule", NpgsqlTypes.NpgsqlDbType.Boolean)); command.Prepare(); command.Parameters[0].Value = said; command.Parameters[1].Value = reschedule; using (Npgsql.NpgsqlDataReader dr = command.ExecuteReader()) { while (dr.Read()) { AppointmentSheet newAppointment = populateAppointmentFromDB(dr); assignableAppointments.Add(newAppointment); } } } } return(assignableAppointments); }
public LogModel(Npgsql.NpgsqlDataReader reader) { this.Created = reader.GetDateTime(3); this.Message = reader.GetString(2); this.Path = reader.GetString(0); this.Section = reader.GetString(1); }
private static User populateUserFromDB(Npgsql.NpgsqlDataReader dr) { // We are using ToString() here instead of (string)dr[] to fight DBNulls User newUser = new User(); newUser.UserId = Helper.ConvertFromDBVal <int>(dr[0]); newUser.UserName = dr[1].ToString(); newUser.Password = dr[2].ToString(); newUser.FirstName = dr[3].ToString(); newUser.MiddleName = dr[4].ToString(); newUser.LastName = dr[5].ToString(); newUser.Address1 = dr[6].ToString(); newUser.Address2 = dr[7].ToString(); newUser.City = dr[8].ToString(); newUser.State = dr[9].ToString(); newUser.ZipCode = dr[10].ToString(); newUser.AssignedRoleId = Helper.ConvertFromDBVal <int>(dr[11]); newUser.OfficeNumber = Helper.ConvertFromDBVal <int>(dr[12]); newUser.SalesRepNumber = Helper.ConvertFromDBVal <int>(dr[13]); newUser.CalendarColor = dr[14].ToString(); newUser.PhoneNumberOne = dr[15].ToString(); newUser.PhoneNumberTwo = dr[16].ToString(); newUser.FaxNumber = dr[17].ToString(); newUser.HourlyRate = Helper.ConvertFromDBVal <Single>(dr[18]); newUser.EmailOne = dr[19].ToString(); newUser.EmailTwo = dr[20].ToString(); newUser.IsActive = Helper.ConvertFromDBVal <Boolean>(dr[21]); newUser.TeamNumber = Helper.ConvertFromDBVal <int>(dr[22]); return(newUser); }
void create_graph() { try { int id = 1; 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); Npgsql.NpgsqlDataReader reader = cmd.ExecuteReader(); while (reader.Read()) { chart1.Series.Add(reader["c_col"].ToString()); chart1.Series[reader["c_col"].ToString()].Points.AddY(reader["avto"]); chart1.Series[reader["c_col"].ToString()].ChartType = SeriesChartType.Column; chart1.Series[reader["c_col"].ToString()].IsValueShownAsLabel = true; chart1.Series[reader["c_col"].ToString()].LegendText = id + ". " + reader["c_col"].ToString(); chart1.ChartAreas["ChartArea1"].AxisX.MajorGrid.Enabled = false; chart1.ChartAreas["ChartArea1"].AxisY.MajorGrid.Enabled = false; id++; } Connection.con.Close(); } catch { Connection.con.Close(); } }
public IEnumerable <User> GetAllUsersByTeam(int TeamNumber) { //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(SelectAllByTeamNumberQuery, conn)) { command.Parameters.Add(new Npgsql.NpgsqlParameter("team", NpgsqlTypes.NpgsqlDbType.Integer)); command.Prepare(); command.Parameters[0].Value = TeamNumber; using (Npgsql.NpgsqlDataReader dr = command.ExecuteReader()) { while (dr.Read()) { User newUser = populateUserFromDB(dr); allUsers.Add(newUser); } } } } return(allUsers); }
public Report CheckExistingRecord(int aaid) { using (Npgsql.NpgsqlConnection conn = new Npgsql.NpgsqlConnection(Infrastructure.ConfigReader.ConnectionString.ToString())) { conn.Open(); using (Npgsql.NpgsqlCommand command = new Npgsql.NpgsqlCommand(ReportCheckExistingRecordQuery, conn)) { command.Parameters.Add(new Npgsql.NpgsqlParameter("month", NpgsqlTypes.NpgsqlDbType.Text)); command.Parameters.Add(new Npgsql.NpgsqlParameter("year", NpgsqlTypes.NpgsqlDbType.Integer)); command.Parameters.Add(new Npgsql.NpgsqlParameter("aauserid", NpgsqlTypes.NpgsqlDbType.Integer)); command.Prepare(); command.Parameters[0].Value = Helper.ConverttoStringDate(DateTime.Now); command.Parameters[1].Value = DateTime.Now.Year; command.Parameters[2].Value = aaid; using (Npgsql.NpgsqlDataReader dr = command.ExecuteReader()) { while (dr.Read()) { newReport = populateRecordsFromDB(dr);; } } } } return(newReport); }
/// <summary> /// Returns the role with it's permissions list populated /// </summary> /// <param name="roleId"></param> /// <returns></returns> public Domain.Role GetRoleByRoleId(int roleId) { Role role = new Role() { RoleId = roleId }; using (Npgsql.NpgsqlConnection conn = new Npgsql.NpgsqlConnection()) { conn.Open(); using (Npgsql.NpgsqlCommand command = new Npgsql.NpgsqlCommand(GetRoleByIdQuery, conn)) { command.Parameters.Add(new Npgsql.NpgsqlParameter("role_id", NpgsqlTypes.NpgsqlDbType.Integer)); command.Prepare(); command.Parameters[0].Value = roleId; using (Npgsql.NpgsqlDataReader dr = command.ExecuteReader()) { while (dr.Read()) { if (role.Name.Equals("")) { role.Name = dr[0].ToString(); } role.Permissions.Add(new Permission() { Name = dr[1].ToString(), Action = dr[2].ToString(), PermissionId = Helper.ConvertFromDBVal <int>(dr[3]) }); } } } } return(role); }
public void Initialize(string connectstr) { acc = connectstr; InitializeComponent(); using (Npgsql.NpgsqlCommand cmm = new Npgsql.NpgsqlCommand("", new Npgsql.NpgsqlConnection(acc))) { try { cmm.CommandText = "SELECT to_number(substring(substring(schema_name from '....$') from 1 for 2 )) thang,to_number('20'||(substring(substring(schema_name from '....$') from '..$'))) nam ,substring(substring(schema_name from '....$') from 1 for 2 )||'-20'||(substring(substring(schema_name from '....$') from '..$')) thangnam from information_schema.schemata where schema_name like 'medibv0%' or schema_name like 'medibv1%' order by thang,nam desc"; cmm.Connection.Open(); Npgsql.NpgsqlDataReader dtrd = cmm.ExecuteReader(); while (dtrd.Read()) { THANGNAMRow nr = THANGNAM.NewTHANGNAMRow(); for (int i = 0; i < dtrd.FieldCount; i++) { if (THANGNAM.Columns.Contains(dtrd.GetName(i))) { nr[dtrd.GetName(i)] = dtrd[i]; } } THANGNAM.Rows.Add(nr); } } finally { cmm.Connection.Close(); } } }
/// <summary> /// Returns a list of all roles in db. Permissions lists will NOT be populated. /// Can be updated to do so if necessary /// </summary> /// <returns></returns> public IEnumerable <Domain.Role> GetAllRoles() { IList <Domain.Role> roles = new List <Domain.Role>(); using (Npgsql.NpgsqlConnection conn = new Npgsql.NpgsqlConnection()) { conn.Open(); using (Npgsql.NpgsqlCommand command = new Npgsql.NpgsqlCommand(GetAllRolesQuery, conn)) { using (Npgsql.NpgsqlDataReader dr = command.ExecuteReader()) { while (dr.Read()) { var temp = new Role() { RoleId = Helper.ConvertFromDBVal <int>(dr[1]), Name = dr[0].ToString() }; roles.Add(temp); } } } } return(roles); }
/// <summary> /// Returns the geometry corresponding to the Object ID /// </summary> /// <param name="oid">Object ID</param> /// <returns>geometry</returns> public IGeometry GetGeometryByID(uint oid) { IGeometry geom = null; using (Npgsql.NpgsqlConnection conn = new Npgsql.NpgsqlConnection(_ConnectionString)) { string strSQL = "SELECT AsBinary(" + this.GeometryColumn + ") AS Geom FROM " + this.Table + " WHERE " + this.ObjectIdColumn + "='" + oid.ToString() + "'"; conn.Open(); using (Npgsql.NpgsqlCommand command = new Npgsql.NpgsqlCommand(strSQL, conn)) { using (Npgsql.NpgsqlDataReader dr = command.ExecuteReader()) { while (dr.Read()) { if (dr[0] != DBNull.Value) { geom = SharpMap.Converters.WellKnownBinary.GeometryFromWKB.Parse((byte[])dr[0]); } } } } conn.Close(); } return(geom); }
public IEnumerable <ManagerWiseAllocationDto> GetManagerWiseAllocationSummaryFromPostgres() { List <ManagerWiseAllocationDto> records = new List <ManagerWiseAllocationDto>(); Npgsql.NpgsqlConnection con = null; try { con = new Npgsql.NpgsqlConnection(PostgresSqlQueries.CONNECTION_STRING); con.Open(); string qry = PostgresSqlQueries.GET_MANAGER_WISE_PROJECTS_SUMMARY.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(); if (res.HasRows) { while (res.Read()) { records.Add(new ManagerWiseAllocationDto { ManagerName = res.IsDBNull(1) == false ? res.GetString(1) : "", ProjectCount = res.IsDBNull(2) == false ? (int)res.GetInt64(2) : 0, ProjectManagerID = res.IsDBNull(0) == false ? res.GetInt32(0) : -1 }); } } } catch (Exception) { } finally { con.Close(); con.Dispose(); } return(records); }
public IEnumerable <Account> GetAccountsByLeadId(int leadId) { //Please see section on using prepared statements in npgsql user manual for explanation on params and query structure IList <Domain.Account> leadAccounts = new List <Domain.Account>(); using (Npgsql.NpgsqlConnection conn = new Npgsql.NpgsqlConnection(Infrastructure.ConfigReader.ConnectionString.ToString())) { conn.Open(); using (Npgsql.NpgsqlCommand command = new Npgsql.NpgsqlCommand(AccountSelectonLeadIDQuery, 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()) { Account newAccount = populateAccountFromDB(dr); leadAccounts.Add(newAccount); } } } } return(leadAccounts); }
public IEnumerable <EmployeeLoginDto> GetUserAndRoleMapEntries() { List <EmployeeLoginDto> records = new List <EmployeeLoginDto>(); Npgsql.NpgsqlConnection con = null; try { con = new Npgsql.NpgsqlConnection(PostgresSqlQueries.SECURITY_DB_CONNECTION_STRING); con.Open(); Npgsql.NpgsqlCommand cmd = new Npgsql.NpgsqlCommand(PostgresSqlQueries.GET_USER_LOGIN_ROLE_ENTRIES, con); Npgsql.NpgsqlDataReader res = cmd.ExecuteReader(); if (res.HasRows) { while (res.Read()) { records.Add(new EmployeeLoginDto { Email = res.IsDBNull(1) == false ? res.GetString(1) : "", RoleName = res.IsDBNull(2) == false ? res.GetString(2) : "", UserID = res.IsDBNull(0) == false ? res.GetString(0) : "" }); } } } catch (Exception) { } finally { con.Close(); con.Dispose(); } return(records); }
public IEnumerable <ZipCodes> GetZipcodesByZone(int zone_id) { //This is a fairly generic db query with no parameters and pulling all values from reader and tossing data into User IList <ZipCodes> allZips = new List <ZipCodes>(); using (Npgsql.NpgsqlConnection conn = new Npgsql.NpgsqlConnection(Infrastructure.ConfigReader.ConnectionString.ToString())) { conn.Open(); using (Npgsql.NpgsqlCommand command = new Npgsql.NpgsqlCommand(ZoneSelectonZipCodeQuery, conn)) { command.Parameters.Add(new Npgsql.NpgsqlParameter(":zone_id", NpgsqlTypes.NpgsqlDbType.Integer)); command.Prepare(); command.Parameters[0].Value = zone_id; using (Npgsql.NpgsqlDataReader dr = command.ExecuteReader()) { while (dr.Read()) { ZipCodes newZipcode = populateZipcodesFromDB(dr); allZips.Add(newZipcode); } } } } return(allZips); }
public IEnumerable <PodWiseCountDto> GetPodWiseAllocationCount() { List <PodWiseCountDto> records = new List <PodWiseCountDto>(); Npgsql.NpgsqlConnection con = null; try { con = new Npgsql.NpgsqlConnection(PostgresSqlQueries.CONNECTION_STRING); con.Open(); string query = PostgresSqlQueries.POD_WISE_EMPLOYEE_COUNT.Replace("__CURRENT_DATE__", $"{ DateTime.Today.Year}-{ DateTime.Today.Month}-{ DateTime.Today.Day}"); Npgsql.NpgsqlCommand cmd = new Npgsql.NpgsqlCommand(query, con); Npgsql.NpgsqlDataReader res = cmd.ExecuteReader(); if (res.HasRows) { while (res.Read()) { records.Add(new PodWiseCountDto { PracticeName = res.IsDBNull(0) == false ? res.GetString(0) : "", SubCategoryName = res.IsDBNull(1) == false ? res.GetString(1) : "", Count = res.IsDBNull(2) == false ? (int)res.GetInt64(2) : 0, }); } } } catch (Exception exp) { } finally { con.Close(); con.Dispose(); } return(records); }
public string NewID() { string i = ""; string sQuery = "select '" + clsGlobal.pstrservercode + "'||nextval('tbm_booking_nextid') as id;"; Npgsql.NpgsqlCommand cmd = new Npgsql.NpgsqlCommand(sQuery, Koneksi); cmd.CommandText = sQuery; try { Npgsql.NpgsqlDataReader rdr = cmd.ExecuteReader(); if (rdr.Read()) { if (!rdr.IsDBNull(rdr.GetOrdinal("id"))) { i = rdr.GetValue(0).ToString(); } else { i = ""; }; } rdr.Close(); } catch (Npgsql.NpgsqlException Ex) { System.Windows.Forms.MessageBox.Show(Ex.Message, "An error occurred while processing!!!"); return(""); } return(i); }
public IList <Lead> GetLeadByCardType(string cardtype) { IList <Domain.Lead> getLeads = new List <Domain.Lead>(); getLeads.Clear(); using (Npgsql.NpgsqlConnection conn = new Npgsql.NpgsqlConnection(Infrastructure.ConfigReader.ConnectionString.ToString())) { conn.Open(); using (Npgsql.NpgsqlCommand command = new Npgsql.NpgsqlCommand(GetLeadsByCardTypeQuery, conn)) { command.Parameters.Add(new Npgsql.NpgsqlParameter(":cardtype", NpgsqlTypes.NpgsqlDbType.Text)); command.Prepare(); command.Parameters[0].Value = cardtype; using (Npgsql.NpgsqlDataReader dr = command.ExecuteReader()) { while (dr.Read()) { Lead newLead = populateLeadFromDB(dr); getLeads.Add(newLead); } } } } return(getLeads); }