Joining Tables

Databases Have Many Tables

Databases rarely have just one table. In this section you will learn how to add multiple tables and how to make that interactive with the user.

With Server Explorer open, expand an existing database and right click on the Tables directory and click on Add New Table. This will open a new instance of the table editor.

Adding a new table to the database

When making a table, don't forget to give a name to your new table on the same line as the CREATE TABLE statement and to click Update when you're done to commit this new table to your database. Refresh Server Explorer to view your changes.


Adding Foreign Keys

Now that we've added a new table, let's make it relational to our exsiting database. In my example, my students table will need a new column that contains an id number from the Programs table. This will be foreign key to the id column in the other table. To add foreign keys, open the Table Definition editor. You could manually add foreign keys in the SQL like so:

        CONSTRAINT [FK_give_a_name] FOREIGN (column_name) REFERENCES other_table(other_table_column)
        

Or right click on Foreign Keys and click on Add New Foreign Key. From here, Visual Studio will write a barebones SQL line that will add a foreign key constraint for you to define. Click anywhere else on the window for the SQL statment to appear for you to define. Don't forget to click Update when you're done!

Adding a foreign key to a table

Using Multiple Tables

Now that our database has relational tables, let's show that relation on the screen. Show this output on another page. To do so, go to Project and click Add New Item.... From the options under the the Web tag select Web Form option and name your page in the field below. On this new page, provide a GridView in the HTML for the database to show itself. In the C# for this page you will also have to redeclare your connection string. Doing do also means you need to check if

            using System.Data.SqlClient;
            using System.Configuration;
        

is present. So far your new page C# code should look like this:

Your page thus far

Showing data from two databases is not that different than showing data from one database. The key detail is in your query statement which determines what is shown in the resulting GridView. Showing data from two tables requires a JOIN statement like so:

C# function to show two tables

And the result:

Results of joining tables

Notice that there is a link back to the homepage. The code to navigate to another page in ASP.NET is like this:

        <asp:HyperLink NavigateUrl="~/Homepage.aspx" runat="server">Go to Homepage</asp:HyperLink>
        

The NavigateUrl will auto correct as you type and search for existing pages in the current project.