Bind DataGridView to multiple tables

I've got the following three tables in my database which I want to display to the user in a single DataGridView:

RecipeNames     RecipeValue         Ingredient
-----------     -------------       ----------
PK  Name        FK RecipeName      
                FK IngredientName   PK Name
                   Amount

The user should see one row per RecipeName, the recipe name being the first column and the ingredient names the following columns. Each cell will contain the amount of a recipeValue and every Recipe has every Ingredient. Also the user must be able to edit any cell add a new recipe by adding a new row. Should look like this:

Name     |  Ingredient 1 | ... | Ingr开发者_如何学Cedient n |
-----------------------------------------------
Recipe1  |   100         | ... | 1000         |
Recipe2  |   200         | ... | 2000         |

At the moment I'm thinking about generating and filling the grid manually like this:

• add columns for each row in the ingredient table
• add rows for each Name in RecipeNames
• fill Cells according to RecipeValue table

Then the user can add / modify stuff in the table. When he hits a "Save" button I iterate over the datagrid again and do the appropriate update/insert operations for each row.

Can this be done automatically? How would you bind the tables to the Datagrid? (I know how databinding works for a single table/view, but am at a loss how to do it in this case)

Also any comment on the design is welcome.

edit to clarify: the tables are simplified, there are more fields to them, among them an ID number as primary key and the queries will be limited by other parameters so that only a few ingredients will be listed. I primarily want to know ways to present such a table design in a datagrid or similiar UI which keeps the development effort minimal.

Could you please not work with strings as primary or foreign keys? That is really bad practice and if you're working with MSSQL (which I guess) you're turning down the server's speed.

tblRecipeNames  AsRecipeValue       tblIngredients    tblAmountTypes
-----------     -------------       ----------        ----------
PK ID           PK ID               PK ID             PK ID
   Name         FK RecName_ID          Name              Name
                FK Ingredient_ID
                FK AmountType
                   Amount

I renamed your RecipeValue because this table is an "association table" so that's where the "As" comes from. I also added IDs to every table because that's what you should be working with.

The new table AmountTypes is because of the different types:

• teaspoon
• tablespoon
• large
• medium
• small
• cup
• zest

and so on.

After that and now coming back to your question, first of all you generate stored procedures for your getters and setters. You want to seperate your SQL-Server as the "Hard-Drive" you're getting information from since it's the fastest way to handle data.

For Example:

SELECT * FROM tblRecipeNames 
        INNER JOIN AsRecipeValue ON tblRecipeNames.ID = AsRecipeValue.RecName_ID
        INNER JOIN tblIngredients ON tblIngredients.ID = AsRecipeValue.Ingredient_ID

That's the first step. If you're there you can add a SqlDatasource to your GridView and in the site you add your stored procedures to the control like this:

<asp:SqlDataSource ID="sqlSrc" runat="server" InsertCommand="spInsert" DeleteCommand="spDelete" UpdateCommand="spUpdate" />

sp* stands for your own stored procedure.