C# Using Multiple Database Results with Listboxes that (when item is selected) Display Info in Textboxes

I am working on a Cook Book using a database. I have been working with tutorials to build my project since I'm still just a novice.

I hav开发者_运维知识库e asked before how to search a database using a button, and the help was so quick, I'll have to ask another question.

Here's the problem:

My form allows a user to find recipes that have a certain ingredient. It allows them to type an ingredient in the textbox, and the button shows all the results (the recipes' names) in a listbox. That part has been successfully coded, thanks to the help here. Once the list box is populated, though, I'd like the user to be able to select a recipe from the list box, and the textboxes next to the listbox fill with the particular recipe's data information (such as ingredients, directions, and additional comments).

What recipes populate the listbox are up to the user, so I can't really code this without some serious logic, right?

Here is the code for my entire form:

using System;
using System.Collections.Generic;
using System.ComponentModel;
using System.Data;
using System.Drawing;
using System.Linq;
using System.Text;
using System.Windows.Forms;
using System.Data.Sql;
using System.Data.SqlClient;
using System.Collections;


namespace Cookbook
{
    public partial class BrowseIngredients : Form
    {
        public BrowseIngredients()
        {
            InitializeComponent();
        }

        SqlConnection con;
        SqlDataAdapter dataAdapt;
        DataSet dataRecipe;
        int MaxRows = 0;
        int inc = 0;


        private void exitToolStripMenuItem_Click(object sender, EventArgs e)
        {
            if (MessageBox.Show("Exit Cook Book?", "Exit?", MessageBoxButtons.OKCancel) == DialogResult.OK)
            {
                Application.Exit();
            }
        }

        private void btnBack_Click(object sender, EventArgs e)
        {
            BrowseRecipes goBack = new BrowseRecipes();

            Close();
            goBack.Show();
        }

        private void howToSearchToolStripMenuItem_Click(object sender, EventArgs e)
        {
            MessageBox.Show("To look for recipes with ingredients you have, simply type in the first ingredient you have to cook. \r\n To narrow your search, add another ingredient you'd like to search for in the recipe results.", "Search by Ingredients");
        }


        private void BrowseIngredients_Load(object sender, EventArgs e)
        {

            con = new SqlConnection();
            con.ConnectionString = "Data Source=.\\SQLEXPRESS;AttachDbFilename=C:\\Documents and Settings\\Cady Wong\\My Documents\\Visual Studio 2010\\Projects\\Cookbook\\Cookbook\\Recipes.mdf;Integrated Security=True;User Instance=True";
            dataRecipe = new DataSet();

            con.Open();

           string sql = "SELECT* From CookBookRecipes";
           dataAdapt = new SqlDataAdapter(sql, con);
           dataAdapt.Fill(dataRecipe, "CookBookRecipes");
           NavigateRecords();
           MaxRows = dataRecipe.Tables["CookBookRecipes"].Rows.Count;

            con.Close();

        }

        private void NavigateRecords()
        {
            DataRow dRow = dataRecipe.Tables["CookBookRecipes"].Rows[inc];

        }


//This is the search and populate listbox code //


        private void btnSearch_Click(object sender, EventArgs e)
        {


            if (tbSearch.TextLength >= 1)
           {
                //MessageBox.Show("This will work when you put in a word!");

               listBox1.Items.Clear();

                string searchOut = tbSearch.Text;
                int result = 0;

                DataRow[] returnRows;

                returnRows = dataRecipe.Tables["CookBookRecipes"].Select("Recipe_Ingredients LIKE '*" + searchOut + "*'");


                result = returnRows.Length;

//This allows mutiple results to be seen one line after another //

                if (result > 0)
                {
                    string temp ="";
                    DataRow rowBack;
                    for (int index = 0; index < returnRows.Count(); index++ )
                    {
                        rowBack = returnRows[index];
                        listBox1.Items.Add(rowBack[0].ToString());
                        temp += rowBack[0].ToString();
                        temp += "\n";
                    }

                }
                else
                {
                    MessageBox.Show("No record");
                }

           }

           else
            {
               MessageBox.Show("Please enter an ingredient to search for!", "Search");
           }
        }

    }
}

If you need more information, just let me know!

Thank you in advance!

Ok, I haven't really provided the answer- more bane you with a few tips that hopefully will push you forward, not just on this project, but as a programmer in general.

On a slight side note before I continue, the tags "multiple" and "logic" are a perculiar choice for your question

Regarding BrowseIngredients_Load:

Here you use a SqlConnection which implements the IDisposable interface. The best practice (and it's very highly recommended) is to always use the Using Block Pattern when working with objects that implement IDispoable (at least where possible).

using (con = New SqlConnection(connectionString)){
    ' use the connection within these bracers
}
' because you used this pattern, the object is disposed correctly, memory deallocated, etc

The next bit of advice would be to take a look at Linq to SQL (L2S for short). L2S is an Object-Relational Mapper. Sounds complicated but basically means it is responsible for mapping your database objects (Tables, Views, etc) to your Domain Model (Classes, Strucutres, etc). Probably again sounds a bit complicated, but trust me, check out these dead simple tutorials and you'll love it:

CodeProject Article including Source Code

Part 1 of Scott Gu's Blog/Tutorials

Good ol' MSDN Introduction to L2S

NB: There are many ORMs available, Microsoft itself also provides Entity Framework, but L2S should be easy to learn and arm you with enough knowledge to make educated choices towards ORM alternatives in the future

If you go with L2S (which once you've spent a couple hours with, you'll find you'll use over writing out T-SQL in your code every time) it solves a couple other issues, such as introducing SQL Injection which Linq would handle for you.

It would also solve your overall problem, or almost. With Linq to SQL, displaying the Recipes data would be simple because they would just be the properties of a class.

txtIngredients.Text = myAutoGeneratedLinqRecipe.Ingredients;

I hope that helps get you on your way, I'm sure somebody else will give you the code to complete your exercise without introducing new code patterns and technologies ^^

EDIT: A quick exmaple of the using block so you get the idea:

Private void BrowseIngredients_Load(object sender, EventArgs e)
{
    string connStr = "Data Source=.....etc";
    using (con = New SqlConnection(connStr))
    {
        con.Open
        ' get your data here - if you were losing Linq, you wouldnt have to worry
        ' about connections or Sql at all ;-)
        con.Close
    } ' this ensures that the connection is disposed correctly, even if for example
      ' you throw an exception inside the block which isn't caught
}