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

Time:04-02

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: enter image description here

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();
        }

CodePudding user response:

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.

  • Related