SQL Database Joins in ASP.NET
See more tutorials in Database. This post has Comments Off on SQL Database Joins in ASP.NET.
This tutorial will teach you how to Use an SQL Join command.
In This Tutorial we will learn about the SQL Join Query and what it is used for. The Join query is an advanced SQL technique that is used to join two SQL tables with a common Column. They are linked together by that column which is known as a Foreign Key and the link gives you access to all of the columns in both tables where the Foreign Keys match.
Step 1. Creating the Tables
1. In your project create two Tables, one called “Message” and one Called “Employee”.
2. Feel free to fill these with Data as you wish just make sure that they both have a column named “EmployeeID” as this will be our foreign key.
3. My example has a Message from an anonymous user with EmployeeID of 1, and in the Employee Table it shows that EmployeeID of 1 belongs to John Doe.
4. We will use SQL to lookup the EmployeeID of a message and Join it to the Employee table to get that persons name. So you can see how this Join command can come in handy by linking tables that aren’t directly related, but have something in common.
Step 2. Creating the Page and the SQL Query
1. The Code below is an example of how to use the Join Command to link two SQL tables together to get information from both.
2. The Query looks like This.
SELECT Message.EmployeeID, Message.Message, Employee.EmployeeName FROM Message
INNER JOIN Employee
ON Message.EmployeeID = Employee.EmployeeID
3. You will notice that this command grabs items using a different format(the . operator) This allows us to specify which Table we are grabbing from. When we specify this it is expecting a Join Command to be present. Now we just need to list this on the screen
<!DOCTYPE html PUBLIC "-//W3C//DTD XHTML 1.0 Transitional//EN"
<form id="form1" runat="server">
<asp:SqlDataSource ID="CommentSqlDataSource" runat="server"
ConnectionString="<%$ ConnectionStrings:ConnectionString %>"
SelectCommand="SELECT Message.EmployeeID, Message.Message,
Employee.EmployeeName FROM Message
INNER JOIN Employee
ON Message.EmployeeID = Employee.EmployeeID">
<asp:GridView ID="GridView1" runat="server"
<asp:BoundField DataField="EmployeeID" HeaderText="EmployeeID"
SortExpression="EmployeeID" /> <asp:BoundField
<asp:BoundField DataField="Message" HeaderText="Message"
In Conclusion, we have used the SQL Join Command to link two tables together based on a single key. This is all done in the front end so there is no need for C# Code in this example. This is one way to show how truly advanced and versatile SQL can be.