SQL Joins and .Net
Previous:
SQL Transaction and Locking
Sometimes the data from different tables need to be fetched in the applications. The joins in SQL is used to achieve this aim in the applications. There are following types of Joins:
- Inner Join : An Inner Join will take two tables and join them together based on the values in common columns (linking field) from each table. The INNER JOIN returns all rows from both tables where there is a match. If there are rows in Employees that do not have matches in Orders, those rows will not be listed.
- Outer Join: Outer join is used to forcefully retrieve the data from the corresponding tables if the data is missing from the table for the corresponding record in another table. The outer join is further divided into two divisions i.e. either they are left outer join or right outer join.
- Self Join: This is a particular case when one table joins to itself, with one or two aliases to avoid confusion. A self join can be of any type, as long as the joined tables are the same
- Cartesian Join: This will result a Cartesian product of the two tables. Let take an example if there are 20 rows in table1 and 30 rows in table2 then the result would be 20 multiply by 30 that is 60. This will give the Cartesian product of the records that are there in the database.
The following Stored Procedure needs to be created in the database for implementing Joins
1) SELF JOIN
CREATE PROCEDURE sp_selfjoin
AS
SELECT DISTINCT TOP 50 PERCENT dbo.authors.au_lname, dbo.authors.au_fname, dbo.authors.city
FROM dbo.authors INNER JOIN
dbo.authors authors_1 ON dbo.authors.city = authors_1.city AND
dbo.authors.au_fname + ' ' + dbo.authors.au_lname <> authors_1.au_fname +' ' + authors_1.au_lname
ORDER BY dbo.authors.city, dbo.authors.au_lname
2) CARTESIAN JOIN
CREATE PROCEDURE sp_cartesianJoin
AS
select count(*) from jobs j, authors a
GO
3) INNER JOIN
CREATE PROCEDURE sp_InnerJoin
AS
Select TOP 5 c.categoryName, p.productName
from Categories c Inner Join products p
on c.categoryId = p.categoryId
GO
4) RIGHT OUTER JOIN
CREATE PROCEDURE sp_RightOuterJoin
AS
Select TOP 5 c.categoryName, p.productName
from Categories c Right Outer Join products p
on c.categoryId = p.categoryId
GO
5) LEFT OUTER JOIN
CREATE PROCEDURE sp_LeftOuterJoin
AS
Select TOP 5 c.categoryName, p.productName
from Categories c LEft Outer Join products p
on c.categoryId = p.categoryId
Note:- The following example will call the above procedures to show the data using sql joins.
Example: Demonstrate SQL Joins
JoinsWithNET.aspx
<% @ Page Language ="C#" AutoEventWireup ="true" CodeFile ="JoinsWithNET.aspx.cs" Inherits ="JoinsWithNET" %>
<! DOCTYPE html PUBLIC "-//W3C//DTD XHTML 1.0 Transitional//EN" "http://www.w3.org/TR/xhtml1/DTD/xhtml1-transitional.dtd">
<html xmlns ="http://www.w3.org/1999/xhtml" >
<head runat ="server">
</head >
<body >
<form id ="form1" runat ="server">
<div >
<strong ><span style ="text-decoration: underline"> SQL Joins and .Net < br />
<br />
</span>
<asp:Button ID ="txtSelf" runat ="server" OnClick ="txtSelf_Click" Text ="Self Join" />< span style ="text-decoration: underline"> </ span >
<asp:GridView ID ="grdvSelf" runat ="server" AutoGenerateColumns ="False" BackColor ="LightGoldenrodYellow" BorderColor ="Tan" BorderWidth ="1px" CellPadding ="2" Font-Bold ="False" ForeColor ="Black" GridLines ="None">
<FooterStyle BackColor ="Tan" />
<Columns >
< asp : BoundField DataField ="au_lname" HeaderText ="Last Name" />
< asp : BoundField DataField ="au_fname" HeaderText ="First Name" />
< asp : BoundField DataField ="city" HeaderText ="City" />
</Columns>
<SelectedRowStyle BackColor ="DarkSlateBlue" ForeColor ="GhostWhite" />
<PagerStyle BackColor ="PaleGoldenrod" ForeColor ="DarkSlateBlue" HorizontalAlign ="Center" />
<HeaderStyle BackColor ="Tan" Font-Bold ="True" />
<AlternatingRowStyle BackColor ="PaleGoldenrod" />
</asp:GridView>
<br/>
</strong>
<asp:Button ID ="btnCartesian" runat ="server" OnClick ="btnCartesian_Click" Text ="Cartesian Join" />
<asp:Label ID ="lblCartesian" runat ="server"></ asp : Label >
<br />
<br />
<asp:Button ID ="btnInner" runat ="server" OnClick ="btnInner_Click" Text ="Inner Join" />
<asp:GridView ID ="grdvInner" runat ="server" AutoGenerateColumns ="False" BackColor ="LightGoldenrodYellow" BorderColor ="Tan" BorderWidth ="1px" CellPadding ="2" ForeColor ="Black" GridLines ="None">
<FooterStyle BackColor ="Tan" />
<Columns >
<asp:BoundField DataField ="categoryName" HeaderText ="Category Name" />
<asp:BoundField DataField ="productName" HeaderText ="Product Name" />
</Columns>
<SelectedRowStyle BackColor ="DarkSlateBlue" ForeColor ="GhostWhite" />
<PagerStyle BackColor ="PaleGoldenrod" ForeColor ="DarkSlateBlue" HorizontalAlign ="Center" />
<HeaderStyle BackColor ="Tan" Font-Bold ="True" />
<AlternatingRowStyle BackColor ="PaleGoldenrod" />
</asp:GridView >
<br />
<asp:Button ID ="btnRight" runat ="server" OnClick ="btnRight_Click" Text ="Right Outer Join" />
<asp:GridView ID ="grdvRight" runat ="server" AutoGenerateColumns ="False" BackColor ="LightGoldenrodYellow" BorderColor ="Tan" BorderWidth ="1px" CellPadding ="2" ForeColor ="Black" GridLines ="None">
<FooterStyle BackColor ="Tan" />
<Columns >
<asp:BoundField DataField ="categoryName" HeaderText ="Category Name" />
<asp:BoundField DataField ="productName" HeaderText ="Product Name" />
</Columns>
<SelectedRowStyle BackColor ="DarkSlateBlue" ForeColor ="GhostWhite" />
<PagerStyle BackColor ="PaleGoldenrod" ForeColor ="DarkSlateBlue" HorizontalAlign ="Center" />
<HeaderStyle BackColor ="Tan" Font-Bold ="True" />
<AlternatingRowStyle BackColor ="PaleGoldenrod" />
</asp:GridView>
<br/> <asp:Button ID ="btnLeft" runat ="server" OnClick ="btnLeft_Click" Text ="Left Outer Join" />
<asp:GridView ID ="grdvLeft" runat ="server" AutoGenerateColumns ="False" BackColor ="LightGoldenrodYellow" BorderColor ="Tan" BorderWidth ="1px" CellPadding ="2" ForeColor ="Black" GridLines ="None">
<FooterStyle BackColor ="Tan" />
<Columns>
<asp:BoundField DataField ="categoryName" HeaderText ="Category Name" />
<asp:BoundField DataField ="productname" HeaderText ="Product Name" />
</Columns>
<SelectedRowStyle BackColor ="DarkSlateBlue" ForeColor ="GhostWhite" />
<PagerStyle BackColor ="PaleGoldenrod" ForeColor ="DarkSlateBlue" HorizontalAlign ="Center" />
<HeaderStyle BackColor ="Tan" Font-Bold ="True" />
<AlternatingRowStyle BackColor ="PaleGoldenrod" />
</asp:GridView>
</div>
</form>
</body >
</html >
JoinsWithNET.aspx.cs
using System;
using System.Data;
using System.Configuration;
using System.Collections;
using System.Web;
using System.Web.Security;
using System.Web.UI;
using System.Web.UI.WebControls;
using System.Web.UI.WebControls.WebParts;
using System.Web.UI.HtmlControls;
using System.Data.SqlClient;
public partial class JoinsWithNET : System.Web.UI. Page
{
protected void Page_Load( object sender, EventArgs e)
{
}
protected void txtSelf_Click( object sender, EventArgs e)
{ //Self Join
string strConn = "Data Source=localhost;Initial Catalog= pubs; user Id=sa;Password = test" ;
SqlConnection conn = new SqlConnection (strConn);
//Create Data Adapter Instance
SqlDataAdapter ada = new SqlDataAdapter ( "sp_selfjoin" , conn);
//Create Data Table Instance
DataTable dtSelf = new DataTable ();
ada.Fill(dtSelf);
//GridView
grdvSelf.DataSource = dtSelf.DefaultView;
grdvSelf.DataBind();
}
protected void btnInner_Click( object sender, EventArgs e)
{ //Inner Join
string strConn = "Data Source=localhost;Initial Catalog= Northwind; user Id=sa;Password = test" ;
SqlConnection conn = new SqlConnection (strConn);
//Create Data Adapter Instance
SqlDataAdapter ada = new SqlDataAdapter ( "sp_InnerJoin" , conn);
//Create Data Table Instance
DataTable dtInner = new DataTable ();
ada.Fill(dtInner);
//GridView
grdvInner.DataSource = dtInner.DefaultView;
grdvInner.DataBind();
}
protected void btnRight_Click( object sender, EventArgs e)
{ //Right Join
string strConn = "Data Source=localhost;Initial Catalog= Northwind; user Id=sa;Password =test" ;
SqlConnection conn = new SqlConnection (strConn);
//Create Data Adapter Instance
SqlDataAdapter ada = new SqlDataAdapter ( "sp_RightOuterJoin" , conn);
//Create Data Table Instance
DataTable dtRight = new DataTable ();
ada.Fill(dtRight);
//GridView
grdvRight.DataSource = dtRight.DefaultView;
grdvRight.DataBind();
}
protected void btnLeft_Click( object sender, EventArgs e)
{ //Left Join
string strConn = "Data Source=localhost;Initial Catalog= Northwind; user Id=sa;Password =test" ;
SqlConnection conn = new SqlConnection (strConn);
//Create Data Adapter Instance
SqlDataAdapter ada = new SqlDataAdapter ( "sp_LeftOuterJoin" , conn);
//Create Data Table Instance
DataTable dtLeft = new DataTable ();
ada.Fill(dtLeft);
//GridView
grdvLeft.DataSource = dtLeft.DefaultView;
grdvLeft.DataBind();
}
protected void btnCartesian_Click( object sender, EventArgs e)
{ //Cartesian Join
string strConn = "Data Source=localhost;Initial Catalog= pubs; user Id=sa;Password =test" ;
SqlConnection conn = new SqlConnection (strConn);
SqlCommand cmd = new SqlCommand ();
cmd.Connection = conn;
cmd.CommandType = CommandType .StoredProcedure;
cmd.CommandText = "sp_cartesianJoin" ;
conn.Open();
lblCartesian.Text = "Cartesian Product is :: " + cmd.ExecuteScalar().ToString() ;
conn.Close();
}
}
Output
Aspnet Discussion
- - Any idea??
- - How to Encode-Decode URL
- - Change IE settings using
- - Excel problem
- - Example of Server.Transfe
