I am trying to create a simple application which connects with a PostgreSQL database to get businesses based on the state and city. Before I go any further I would like to apologize for any vagueness, poor wording, or simple ignorance as this is my first time using C#.
There is a table stored in PostgreSQL called business which the application queries. I am trying to implement these SQL statements.
SELECT DISTINCT state
FROM business
ORDER BY state;
SELECT DISTINCT city
FROM business
WHERE state= "selected state"
ORDER BY city;
SELECT name
FROM business
WHERE city= "selected city" AND state= "selected state";
Overall the application will look like
.
I was able to implement the select statements to get the states and the cities but am having trouble linking the two together for the order by and where clauses. The main trouble I'm having is getting the state so that it only shows cities that are in the state. Same with the business selection, I am having trouble selecting both the city and state such that only businesses in both the city and state are shown.
Here is the code so far:
using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Threading.Tasks;
using System.Windows;
using System.Windows.Controls;
using System.Windows.Data;
using System.Windows.Documents;
using System.Windows.Input;
using System.Windows.Media;
using System.Windows.Media.Imaging;
using System.Windows.Navigation;
using System.Windows.Shapes;
using Npgsql;
namespace Milestone1
{
/// <summary>
/// Interaction logic for MainWindow.xaml
/// </summary>
public partial class MainWindow : Window
{
public class Business
{
public string name { get; set; }
public string state { get; set; }
public string city { get; set; }
}
public MainWindow()
{
InitializeComponent();
addStates();
addCities();
addColumns2Grid();
}
private string buildConnString()
{
return "Host=localhost; username=postgres; Database=Milestone1DB";
}
public void addStates()
{
using (var conn = new NpgsqlConnection(buildConnString()))
{
conn.Open();
using (var cmd = new NpgsqlCommand())
{
cmd.Connection = conn;
cmd.CommandText = "SELECT distinct state FROM business ORDER BY state;";
using (var reader = cmd.ExecuteReader())
{
while (reader.Read())
{
while (reader.Read()) {
statelist.Items.Add(reader.GetString(0));
}
}
}
}
conn.Close();
}
}
public void addCities()
{
using (var conn = new NpgsqlConnection(buildConnString()))
{
conn.Open();
using (var cmd = new NpgsqlCommand())
{
Business b = new Business();
cmd.Connection = conn;
cmd.CommandText = "SELECT distinct city FROM business WHERE state = '" + b.state.ToString() + "';"; ##Problem is with this line
using (var reader = cmd.ExecuteReader())
{
while (reader.Read())
{
while (reader.Read())
{
citylist.Items.Add(reader.GetString(0));
statelist.Items.Add(reader.GetString(1));
}
}
}
}
conn.Close();
}
}
public void addColumns2Grid()
{
DataGridTextColumn col1 = new DataGridTextColumn();
col1.Header = "Business Name";
col1.Binding = new Binding("name");
col1.Width = 255;
businessGrid.Columns.Add(col1);
DataGridTextColumn col2 = new DataGridTextColumn();
col2.Header = "State";
col2.Binding = new Binding("state");
businessGrid.Columns.Add(col2);
DataGridTextColumn col3 = new DataGridTextColumn();
col3.Header = "City";
col3.Binding = new Binding("City");
businessGrid.Columns.Add(col3);
}
private void ComboBox_SelectionChanged(object sender, SelectionChangedEventArgs e)
{
}
private void statelist_SelectionChanged(object sender, SelectionChangedEventArgs e)
{
businessGrid.Items.Clear();
if (statelist.SelectedIndex > -1)
{
using (var conn = new NpgsqlConnection(buildConnString()))
{
conn.Open();
using (var cmd = new NpgsqlCommand())
{
cmd.Connection = conn;
cmd.CommandText = "SELECT name,state FROM business WHERE state = '" + statelist.SelectedItem.ToString()+ "';";
using (var reader = cmd.ExecuteReader())
{
while (reader.Read())
{
while (reader.Read())
{
businessGrid.Items.Add(new Business() { name = reader.GetString(0), state = reader.GetString(1) });
}
}
}
}
conn.Close();
}
}
}
private void citylist_SelectionChanged(object sender, SelectionChangedEventArgs e)
{
businessGrid.Items.Clear();
if (citylist.SelectedIndex > -1)
{
using (var conn = new NpgsqlConnection(buildConnString()))
{
conn.Open();
using (var cmd = new NpgsqlCommand())
{
Business b = new Business();
cmd.Connection = conn;
cmd.CommandText = "SELECT name,state,city FROM business WHERE state = '" + b.state.ToString() + " ORDER BY " + citylist.SelectedItem.ToString() + ";"; ##Problem is with this line
using (var reader = cmd.ExecuteReader())
{
while (reader.Read())
{
while (reader.Read())
{
businessGrid.Items.Add(new Business() { name = reader.GetString(0), state = reader.GetString(1), city = reader.GetString(2) });
}
}
}
}
conn.Close();
}
}
}
}
}
Again I apologize for my lack of knowledge on the subject. Any advice is appreciated.
Thank you for reading.
