How can I retrieve data from my PostgreSQL database? WPF/C#

Question

For reference, I am new to C#/WPF/PostgreSQL and I am trying to create a project for practice, however I've hit a bit of a roadblock. I found this earlier and tried following along with the answers (I understand it isn't 1 to 1) with my own code: Retrieving data from database in WPF Desktop application but it didn't work in my case.

I am creating a simple recipe app where a user can create a recipe (e.g., put in the title, steps, things they need, etc.) and on the home screen, they can see a link to the recipe that was saved, which would take them to the Recipe Screen to be displayed if clicked. I am using PostgreSQL for my database and I do see the correct information on there after the user would submit all of the necessary info, I just need to retrieve it and put it in a data grid possibly? Unless there is a better way other than a data grid.

Regardless, I plan to have it shown as a list of just the title of the recipe, where a user can click on it and it would load up the page, but that's something I can tackle another time if that is outside of the scope in regards to my question.

Here is a visual idea of what I'm trying to accomplish:

Here is my code for the submit button found in the Create Screen if it helps, however I have no idea what to do in terms of actually retrieving that data and then displaying it on my Home Screen.

        private static NpgsqlConnection GetConnection()
        {
            return new NpgsqlConnection(@"Server=localhost;Port=5432;User Id=postgres;Password=123;Database=RecipeProj;");
        }

        private void SubmitButton_Click(object sender, RoutedEventArgs e)
        {
            Recipe recipe = new Recipe();
            recipe.Title = TitleBox.Text;
            recipe.Step1 = StepBox1.Text;
            recipe.Step2 = StepBox2.Text;
            recipe.Step3 = StepBox3.Text;
            recipe.Step4 = StepBox4.Text;
            recipe.Step5 = StepBox5.Text;
            recipe.Step6 = StepBox6.Text;
            recipe.Ingredients = IngredientBox.Text;
            recipe.Tools = ToolBox.Text;
            recipe.Notes = NoteBox.Text;

            void InsertRecord()
            {
                using (NpgsqlConnection con = GetConnection())
                {
                    string query = @"insert into public.Recipes(Title, Ingredients, Tools, Notes, StepOne, StepTwo, StepThree, StepFour, StepFive, StepSix)
                                    values(@Title, @Ingredients, @Tools, @Notes, @StepOne, @StepTwo, @StepThree, @StepFour, @StepFive, @StepSix)";
                    NpgsqlCommand cmd = new NpgsqlCommand(query, con);
                    cmd.Parameters.AddWithValue("@Title", recipe.Title);
                    cmd.Parameters.AddWithValue("@Ingredients", recipe.Ingredients);
                    cmd.Parameters.AddWithValue("@Tools", recipe.Tools);
                    cmd.Parameters.AddWithValue("@Notes", recipe.Notes);
                    cmd.Parameters.AddWithValue("@StepOne", recipe.Step1);
                    cmd.Parameters.AddWithValue("@StepTwo", recipe.Step2);
                    cmd.Parameters.AddWithValue("@StepThree", recipe.Step3);
                    cmd.Parameters.AddWithValue("@StepFour", recipe.Step4);
                    cmd.Parameters.AddWithValue("@StepFive", recipe.Step5);
                    cmd.Parameters.AddWithValue("@StepSix", recipe.Step6);

                    con.Open();
                    int n = cmd.ExecuteNonQuery();
                    if (n == 1)
                    {
                        MessageBox.Show("Record Inserted");
                        TitleBox.Text = IngredientBox.Text = ToolBox.Text = NoteBox.Text = StepBox1.Text = StepBox2.Text = StepBox3.Text = StepBox4.Text = StepBox5.Text = StepBox6.Text = null;
                    }
                    con.Close();
                }
            }

            InsertRecord();
        }

avalerio · Accepted Answer · 2022-04-02 00:05:30Z

1

string query = @"select * from Recipes";
NpgsqlCommand cmd = new NpgsqlCommand(query, con);
con.Open();
var reader = cmd.ExecuteReader();

var recipes = new List<Recipe>();

while(reader.Read()){
      //Recipe is just a POCO that represents an entire
      //row inside your Recipes table.
      var recipe = new Recipe(){
          Title = reader.GetString(reader.GetOrdinal("Title")),
          //So on and so forth.
          //...
      };
      recipes.Add(recipe);
}

con.Close();

You can use this same exact query to fill in a List of titles and a DataGrid that shows all the contents of a recipe.

answered Apr 2, 2022 at 0:05

avalerio

2,3601 gold badge13 silver badges13 bronze badges

Sign up to request clarification or add additional context in comments.

2 Comments

TM2020 Over a year ago

Sweet! I finally got it to show up and not be invisible thanks to this so I appreciate it greatly (that's as far as I got while working on this a little while ago, nothing was showing no matter what I did in terms of the bindings). I did have one extra question if you don't mind, being that I binded it with the DataGrid, would this work for the other controls as well? Such as ListView for instance. Just wondering because I also want to style it while making them clickable so I'm just curious about the options I'd have.

avalerio Over a year ago

This code snippet would produce a List so it should bind to any Element that requires an ItemSource. So as I stated before you can use this for a List of Titles. You would only need to bind the Title property of Recipe, and not the whole thing.

Collectives™ on Stack Overflow

How can I retrieve data from my PostgreSQL database? WPF/C#

1 Answer 1

2 Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

2 Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related