Example for All Types of SQL JOIN (Inner Join, Cross Join, Outer Join, Self Join)
Definition and very simple examples for each JOINS in SQL
Here we are demonstrating with examples all types of JOINS in SQL Server. First of all we created sample tables and as per the table data we are explaining each SQL JOINS with most understandable examples.
Sample tables
In the following tables, Department.DepartmentID is the primary key, while Employee.DepartmentID is a foreign key.
|
|
Inner join
An inner join requires each record in the two joined tables to have a matching record. An inner join essentially combines the records from two tables (A and B) based on a given join-predicate. The result of the join can be defined as the outcome of first taking the Cartesian product (or cross-join) of all records in the tables (combining every record in table A with every record in table B) – then return all records which satisfy the join predicate.
SQL specifies two different syntactical ways to express joins. The first, called “explicit join notation”, uses the keyword JOIN, whereas the second uses the “implicit join notation”. The implicit join notation lists the tables for joining in the FROM clause of a SELECT statement, using commas to separate them. Thus, it specifies a cross-join, and the WHERE clause may apply additional filter-predicates. Those filter-predicates function comparably to join-predicates in the explicit notation.
One can further classify inner joins as equi-joins, as natural joins, or as cross-joins (see below).
Example of an explicit inner join:
SELECT * FROM employee INNER JOIN department ON employee.DepartmentID = department.DepartmentID Is equivalent to: SELECT * FROM employee, department WHERE employee.DepartmentID = department.DepartmentID
Explicit Inner join result:
Employee. LastName |
Employee. DepartmentID |
Department. DepartmentName |
Department. DepartmentID |
Smith |
34 |
Clerical |
34 |
Jones |
33 |
Engineering |
33 |
Robinson |
34 |
Clerical |
34 |
Steinberg |
33 |
Engineering |
33 |
Rafferty |
31 |
Sales |
31 |
Types of inner joins
Equi-join
An equi-join, also known as an equijoin, is a specific type of comparator-based join, or theta join, that uses only equality comparisons in the join-predicate. Using other comparison operators (such as <) disqualifies a join as an equi-join.
SELECT Employee.lastName, Employee.DepartmentID, Department.DepartmentName FROM Employee INNER JOIN Department ON Employee.DepartmentID = Department.DepartmentID; SQL provides optional syntactic sugar for expressing equi-joins, by way of the USING construct (Feature ID F402): SELECT Employee.lastName, DepartmentID, Department.DepartmentName FROM Employee INNER JOIN Department USING(DepartmentID);
The USING clause is supported by MySQL, Oracle and PostgreSQL.
Natural join
A natural join offers a further specialization of equi-joins. The join predicate arises implicitly by comparing all columns in both tables that have the same column-name in the joined tables. The resulting joined table contains only one column for each pair of equally-named columns.
The above sample query for inner joins can be expressed as a natural join in the following way:
SELECT * FROM employee NATURAL JOIN department
The result appears slightly different, however, because only one DepartmentID column occurs in the joined table.
DepartmentID |
Employee.LastName |
Department.DepartmentName |
34 |
Smith |
Clerical |
33 |
Jones |
Engineering |
34 |
Robinson |
Clerical |
33 |
Steinberg |
Engineering |
31 |
Rafferty |
Sales |
Cross join
A cross join, cartesian join or product provides the foundation upon which all types of inner joins operate. A cross join returns the cartesian product of the sets of records from the two joined tables. Thus, it equates to an inner join where the join-condition always evaluates to True or join-condition is absent in statement.
If A and B are two sets, then the cross join is written as A × B.
The SQL code for a cross join lists the tables for joining (FROM), but does not include any filtering join-predicate.
Example of an explicit cross join:
SELECT * FROM employee CROSS JOIN department
Example of an implicit cross join:
SELECT *
FROM employee, department;
Employee. LastName |
Employee. DepartmentID |
Department. DepartmentName |
Department. DepartmentID |
Rafferty |
31 |
Sales |
31 |
Jones |
33 |
Sales |
31 |
Steinberg |
33 |
Sales |
31 |
Smith |
34 |
Sales |
31 |
Robinson |
34 |
Sales |
31 |
Jasper |
NULL |
Sales |
31 |
Rafferty |
31 |
Engineering |
33 |
Jones |
33 |
Engineering |
33 |
Steinberg |
33 |
Engineering |
33 |
Smith |
34 |
Engineering |
33 |
Robinson |
34 |
Engineering |
33 |
Jasper |
NULL |
Engineering |
33 |
Rafferty |
31 |
Clerical |
34 |
Jones |
33 |
Clerical |
34 |
Steinberg |
33 |
Clerical |
34 |
Smith |
34 |
Clerical |
34 |
Robinson |
34 |
Clerical |
34 |
Jasper |
NULL |
Clerical |
34 |
Rafferty |
31 |
Marketing |
35 |
Jones |
33 |
Marketing |
35 |
Steinberg |
33 |
Marketing |
35 |
Smith |
34 |
Marketing |
35 |
Robinson |
34 |
Marketing |
35 |
Jasper |
NULL |
Marketing |
35 |
Outer joins
An outer join does not require each record in the two joined tables to have a matching record. The joined table retains each record—even if no other matching record exists. Outer joins subdivide further into left outer joins, right outer joins, and full outer joins, depending on which table(s) one retains the rows from (left, right, or both).
Left outer join
The result of a left outer join (or simply left join) for tables A and B always contains all records of the “left” table (A), even if the join-condition does not find any matching record in the “right” table (B). This means that if the ON clause matches 0 (zero) records in B, the join will still return a row in the result—but with NULL in each column from B. This means that a left outer join returns all the values from the left table, plus matched values from the right table (or NULL in case of no matching join predicate).
SELECT * FROM employee LEFT OUTER JOIN department ON employee.DepartmentID = department.DepartmentID
Employee. LastName |
Employee. DepartmentID |
Department. DepartmentName |
Department. DepartmentID |
Jones |
33 |
Engineering |
33 |
Rafferty |
31 |
Sales |
31 |
Robinson |
34 |
Clerical |
34 |
Smith |
34 |
Clerical |
34 |
Jasper |
NULL |
NULL |
NULL |
Steinberg |
33 |
Engineering |
33 |
Right outer joins
A right outer join (or right join) closely resembles a left outer join, except with the tables reversed. Every row from the “right” table (B) will appear in the joined table at least once. If no matching row from the “left” table (A) exists, NULL will appear in columns from A for those records that have no match in A.
A right outer join returns all the values from the right table and matched values from the left table (NULL in case of no matching join predicate). SELECT * FROM employee RIGHT OUTER JOIN department ON employee.DepartmentID = department.DepartmentID
Employee. LastName |
Employee. DepartmentID |
Department. DepartmentName |
Department. DepartmentID |
Smith |
34 |
Clerical |
34 |
Jones |
33 |
Engineering |
33 |
Robinson |
34 |
Clerical |
34 |
Steinberg |
33 |
Engineering |
33 |
Rafferty |
31 |
Sales |
31 |
NULL |
NULL |
Marketing |
35 |
In practice, explicit right outer joins are rarely used, since they can always be replaced with left outer joins and provide no additional functionality.
Full outer join
A full outer join combines the results of both left and right outer joins. The joined table will contain all records from both tables, and fill in NULLs for missing matches on either side.
For example, this allows us to see each employee who is in a department and each department that has an employee, but also see each employee who is not part of a department and each department which doesn’t have an employee.
Example full outer join:
SELECT * FROM employee FULL OUTER JOIN department ON employee.DepartmentID = department.DepartmentID
Employee. LastName |
Employee. DepartmentID |
Department. DepartmentName |
Department. DepartmentID |
Smith |
34 |
Clerical |
34 |
Jones |
33 |
Engineering |
33 |
Robinson |
34 |
Clerical |
34 |
Jasper |
NULL |
NULL |
NULL |
Steinberg |
33 |
Engineering |
33 |
Rafferty |
31 |
Sales |
31 |
NULL |
NULL |
Marketing |
35 |
Self-join
A self-join is joining a table to itself.
Example
A query to find all pairings of two employees in the same country is desired. If you had two separate tables for employees and a query which requested employees in the first table having the same country as employees in the second table, you could use a normal join operation to find the answer table. However, all the employee information is contained within a single large table.
Considering a modified Employee table such as the following:
Employee Table |
|||
EmployeeID |
LastName |
Country |
DepartmentID |
123 |
Rafferty |
Australia |
31 |
124 |
Jones |
Australia |
33 |
145 |
Steinberg |
Australia |
33 |
201 |
Robinson |
United States |
34 |
305 |
Smith |
United Kingdom |
34 |
306 |
Jasper |
United Kingdom |
NULL |
An example solution query could be as follows:
SELECT F.EmployeeID, F.LastName, S.EmployeeID, S.LastName, F.Country FROM Employee F, Employee S WHERE F.Country = S.Country AND F.EmployeeID < S.EmployeeID ORDER BY F.EmployeeID, S.EmployeeID;
Which results in the following table being generated.
Employee Table after Self-join by Country |
||||
EmployeeID |
LastName |
EmployeeID |
LastName |
Country |
123 |
Rafferty |
124 |
Jones |
Australia |
123 |
Rafferty |
145 |
Steinberg |
Australia |
124 |
Jones |
145 |
Steinberg |
Australia |
305 |
Smith |
306 |
Jasper |
United Kingdom |
How to Create a Data Table Dynamically with sample data and Bind to Grid/Create datatable with sample data.
How to create a datatable with sample data
We are going to demonstrate how to create a sample datatable dynamically in ASP.Net. Here we are creating a ASPx page with a grid view. In the page load of the ASPx page will call a bind method which creating a datatable dynamically and bind the datatable with datagrid. In BindGridviewData function create data for an employee and binding employee data to the employee grid.
ASPxPage
<%@ Page Language="C#" AutoEventWireup="true" CodeBehind="DynamicCreateDataTable.aspx.cs" Inherits="ExperimentLab.DynamicCreateDataTable" %> <!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"> <title></title> </head> <body> <form id="form1" runat="server"> <div> <asp:GridView ID="gdEmployee" runat="server"> </asp:GridView> </div> </form> </body> </html>
Code Behind
protected void Page_Load(object sender, EventArgs e) { BindGridviewData(); } //Creating datatable dynamically and bind data to a grid protected void BindGridviewData() { DataTable dt = new DataTable(); //Create datatable Columns dynamically dt.Columns.Add("EmployeeID", typeof(Int32)); dt.Columns.Add("EmployeeName", typeof(string)); dt.Columns.Add("Department", typeof(string)); dt.Columns.Add("City", typeof(string)); //Create data table row dynamically add DataRow dtrow = dt.NewRow(); // Create New Row //Assign data to datarow dynamically dtrow["EmployeeID"] = 1; //Bind Data to Columns dtrow["EmployeeName"] = "Ramraj"; dtrow["Department"] = "Admin"; dtrow["City"] = "Calicut"; dt.Rows.Add(dtrow); dtrow = dt.NewRow(); // Create New Row //Assign data to datarow dynamically dtrow["EmployeeID"] = 2; //Bind Data to Columns dtrow["EmployeeName"] = "Malhothra"; dtrow["Department"] = "HR"; dtrow["City"] = "Mumbai"; dt.Rows.Add(dtrow); dtrow = dt.NewRow(); // Create New Row //Assign data to datarow dynamically dtrow["EmployeeID"] = 3; //Bind Data to Columns dtrow["EmployeeName"] = "Sinan Hafis"; dtrow["Department"] = "Admin"; dtrow["City"] = "Delhi"; dt.Rows.Add(dtrow); dtrow = dt.NewRow(); // Create New Row //Assign data to datarow dynamically dtrow["EmployeeID"] = 4; //Bind Data to Columns dtrow["EmployeeName"] = "Wazim Jafar"; dtrow["Department"] = "Finance"; dtrow["City"] = "Goa"; dt.Rows.Add(dtrow); gdEmployee.DataSource = dt; gdEmployee.DataBind(); } RESULT

Dynamic datatable in ASP.Net
How to delete large amount of rows from table in SQL
How to delete huge amount of data from a table in SQL
In some scenario we have to delete large amount of rows from sql table and it will going to timeout if the table has very large amount of rows (Some tables in the database has more than crore rows). In this scenario we need to delete some small amount of records and from the table and continue the process until all records in the table deleted.
Query for recursive deletion from the table in SQL
Below codes delete 50000 rows recursively untill all records int the table deleted. So it never meets the timeout exception and will complete execution faster than normal query
WHILE exists ( SELECT * FROM myTable WHERE name like ‘%ab%’ ) DELETE TOP (50000) scanned_cont_dtls WHERE name like ‘%ab%’;
How to Create and use Table-Valued Parameter in C# and T-SQL/ How to pass table to stored procedures in SQL
What is Table – Valued Parameter in SQL Server 2008?
Table-valued parameters provide an easy way to marshal multiple rows of data from a client application to SQL Server without requiring multiple round trips or special server-side logic for processing the data. You can use table-valued parameters to encapsulate rows of data in a client application and send the data to the server in a single parameterized command. The incoming data rows are stored in a table variable that can then be operated on by using Transact-SQL.
What we are going to do with Table – Valued Parameter?
We are going to demonstrate a very simple example for using Table – Valued parameter. In this sample project we will insert bulk amount of data into the table by passing a bulk data using datatable in C# to SQL stored procedure.
Create a Table for insert data using Table – Valued Parameter
Here we are having a table named Officer and having three fields ID,Name and Salary. We are going to fill the table with bulk data.
CREATE TABLE Officer( ID INT PRIMARY KEY IDENTITY(1,1), NAME VARCHAR(50), SALARY DECIMAL(18, 0))
Stored Procedure for insert data by accepting Table Valued Parameter
Now we are going to create a Stored Procedure that accepting a table type as parameter and insert values in this type into the table.
CREATE PROCEDURE InsertOfficerDetails ( @OfficerData OfficerDetails readonly ) AS INSERT INTO Officer (Name, Salary) SELECT Name, Salary FROM @OfficerData;
C# code to call Stored Procedure to insert data in to the table using Table – Valued Parameter
We are creating a simple ASPX page with a single button. When we click this button we are calling above stored procedure by creating and passing some amount of sample data to the stored procedure as Table – Valued Parameter.
ASPX Page
<%@ Page Language="C#" AutoEventWireup="true" CodeBehind="CallSP.aspx.cs" Inherits="ExperimentLab.CallSP" %> <!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 id="Head1" runat="server"> <title></title> </head> <body> <form id="form1" runat="server"> <div> <asp:Button ID="btnCallSP" runat="server" Text="Call SP" OnClick="btnCallSP_Click" /> </div> </form> </body> </html>
Code Behind
protected void btnCallSP_Click(object sender, EventArgs e) { try { DataTable dt = new DataTable(); DataColumn dtCol = new DataColumn(); dtCol.ColumnName = "ID"; dt.Columns.Add(dtCol); dtCol = new DataColumn(); dtCol.ColumnName = "Name"; dt.Columns.Add(dtCol); dtCol = new DataColumn(); dtCol.ColumnName = "Salary"; dt.Columns.Add(dtCol); for (int i = 0; i < 10; i++) { DataRow dr = dt.NewRow(); dr["Name"] = "Name " + i; dr["Salary"] = 1000 + i; dr["ID"] = i; dt.Rows.Add(dr); } string connStr = ConfigurationManager. AppSettings["LocalSqlServer"].ToString(); SqlConnection con = new SqlConnection(connStr); using (var conn = new SqlConnection(connStr)) using (var cmd = conn.CreateCommand()) { cmd.Connection = con; con.Open(); cmd.CommandType = CommandType.StoredProcedure; cmd.CommandText = "dbo.InsertOfficerDetails"; SqlParameter param = cmd.Parameters.AddWithValue("@OfficerData", dt); cmd.ExecuteNonQuery(); } } catch (Exception a) { Response.Write(a.Message); } }
Web.Config
<appSettings> <add key="LocalSqlServer" value="Database=testDB;Server=Servername\SQLEXPRESS;User Id=userid;Password=password"/> </appSettings>
Hence we discussed about how to create and use Table valued parameters, how to create a store procedure with table type as parameter, how to pass table to stored procedure in C#/Asp.Net, how to insert multiple rows of data to a table with table valued parameter in SQL, how to insert bulk data to SQL table using Table Valued Parameter in SQL Server 2008 etc..
How to UNPIVOT table in SQL Server / UNPIVOT table example in SQL Server
What is PIVOT and UNPIVOT operator in SQL Server
We can use the PIVOT and UNPIVOT relational operators to change a table-valued expression into another table. PIVOT rotates a table-valued expression by turning the unique values from one column in the expression into multiple columns in the output, and performs aggregations where they are required on any remaining column values that are wanted in the final output.
Please go through our previous post for PIVOT sample in SQL
Before UNPIVOT

Before UNPIVOT table
After UNPIVOT

UNPIVOT sample in SQL Server
A simple UNPIVOT sample in SQL Server
Here we are going to demonstrate a very simple UNPIVOT sample without any complexity. We are having a table named EmploymentHistory and its containing employee name with previous company name that particular employee worked.
Table Structure For showing simple UNPIVOT sample
CREATE TABLE EmploymentHistory (Id INT, Employee VARCHAR(500), Company1 VARCHAR(500), Company2 VARCHAR(500), Company3 VARCHAR(500) ) GO -- Load Sample data INSERT INTO EmploymentHistory SELECT 1, 'John', 'Tata Motors', 'Ashok Leyland', 'Suzuki' UNION ALL SELECT 2, 'Kate', 'Airtel', 'Vodafone', 'Tata Docomo' UNION ALL SELECT 3, 'Sam', 'Hercules', 'Hero', 'Atlas' GO
In order to UNPIVOT above table we can use below mentioned script. The result should be as Employee with each company as a single row.
SELECT Id, ROW_NUMBER()OVER(Order By ID) as NewID, Employee, Company FROM ( SELECT Id, Employee, Company1, Company2, Company3 FROM EmploymentHistory ) Main UNPIVOT ( Company FOR Companies IN (Company1, Company2, Company3) ) Sub
UNPIVOT Table with multiple columns
Now we are going to UNPIVOT table with multiple columns. Suppose we are having a table with Employee name, list of company names that particular employee worked and corresponding cities also. The table structure like below.
CREATE TABLE EmploymentHistoryWithCity (Id INT, Employee VARCHAR(500), Company1 VARCHAR(500), Company2 VARCHAR(500), Company3 VARCHAR(500), City1 VARCHAR(500), City2 VARCHAR(500), City3 VARCHAR(500) ) GO -- Load Sample data INSERT INTO EmploymentHistoryWithCity SELECT 1, 'John', 'Tata Motors', 'Ashok Leyland', 'Suzuki', 'Mumbai', 'Kolkata', 'Delhi' UNION ALL SELECT 2, 'Kate', 'Airtel', 'Vodafone', 'Tata Docomo', 'Chennai', 'Kolkata', 'Banglore' UNION ALL SELECT 3, 'Sam', 'Hercules', 'Hero', 'Atlas', 'Delhi', 'Mumbai', 'Banglore' GO

Before UNPIVOT multiple columns table
In order to UNPIVOT above table we can use below mentioned script. The result should be as Employee name with Company1 and City1 as a first row, Employee Name with Company2 and City2 as second row and so on SELECT Id, ROW_NUMBER()OVER(Order By ID) as NewID, Employee, Company, City FROM ( SELECT Id, Employee, Company1, Company2, Company3, City1, City2, City3 FROM EmploymentHistoryWithCity ) Main UNPIVOT ( Company FOR companies IN (Company1, Company2, Company3) ) Sup UNPIVOT ( City For Cities IN (City1, City2, City3 ) ) Ct WHERE RIGHT(companies,1) = RIGHT(Cities,1)

After UNPIVOT multiple columns table
How to PIVOT table in SQL Server / PIVOT table example in SQL Server
What is PIVOT and UNPIVOT operator in SQL Server
We can use the PIVOT and UNPIVOT relational operators to change a table-valued expression into another table. PIVOT rotates a table-valued expression by turning the unique values from one column in the expression into multiple columns in the output, and performs aggregations where they are required on any remaining column values that are wanted in the final output.
Please go through our post for simple UNPIVOT example
Before PIVOT

Before PIVOT table
After PIVOT

After PIVOT table
A simple PIVOT sample in SQL Server
Here we are going to demonstrate a very simple PIVOT sample without any complexity. We are having a table named Accounts and it containing customer name and their deposit with denominations.
Table Structure For showing simple PIVOT sample
CREATE TABLE Accounts(Customer VARCHAR(25), Denomination VARCHAR(20), QTY INT) GO -- Inserting Data into Table INSERT INTO Accounts(Customer, Denomination, QTY) VALUES('John','10 $',2) INSERT INTO Accounts(Customer, Denomination, QTY) VALUES('John','50 $',6) INSERT INTO Accounts(Customer, Denomination, QTY) VALUES('John','100 $',1) INSERT INTO Accounts(Customer, Denomination, QTY) VALUES('Ram','10 $',4) INSERT INTO Accounts(Customer, Denomination, QTY) VALUES('Ram','50 $',3) INSERT INTO Accounts(Customer, Denomination, QTY) VALUES('Ram','100 $',11) INSERT INTO Accounts(Customer, Denomination, QTY) VALUES('KATE','10 $',20) INSERT INTO Accounts(Customer, Denomination, QTY) VALUES('KATE','50 $',12) INSERT INTO Accounts(Customer, Denomination, QTY) VALUES('KATE','100 $',2) INSERT INTO Accounts(Customer, Denomination, QTY) VALUES('Eby','10 $',0) INSERT INTO Accounts(Customer, Denomination, QTY) VALUES('Eby','50 $',5) INSERT INTO Accounts(Customer, Denomination, QTY) VALUES('Eby','100 $',5)
In order to PIVOT above mentioned table we can use below script. The result should be as Customer name with all denomination will be coming as columns with qty as values for each column.
SELECT * FROM Accounts PIVOT (SUM(QTY) For Denomination IN ([10 $],[50 $],[100 $])) AS Total
Dynamic Query to PIVOT table for dynamic columns
In the above example we are using a simple structured table and then PIVOT with denomination values. This can be achieved only when we are having denomination values as static. Suppose this denomination values are dynamic (Each country having different denomination like $,EUR, IND etc..), we need to create a dynamic query to PIVOT above table.
Suppose we are having different table for getting Denomination values and we are going to take Denomination values from this table at run time as dynamic.
CREATE TABLE Denomination(Value VARCHAR(25)) GO INSERT INTO Denomination(Value) VALUES('10 $') INSERT INTO Denomination(Value) VALUES('50 $') INSERT INTO Denomination(Value) VALUES('100 $')
First of all, we need to get dynamic columns names from the above table. After that we can create a dynamic query with these columns.
Declare @ColumnNames VARCHAR(100); SELECT @ColumnNames = COALESCE(@ColumnNames+ ',','') + '['+ Cast(Value AS VARCHAR(50)) +']' FROM Denomination cust PRINT @ColumnNames DECLARE @DynamicQuery Varchar(MAX); SET @DynamicQuery = ' SELECT * FROM Accounts PIVOT (SUM(QTY) For Denomination IN (' + @ColumnNames + ')) AS Total' EXEC (@DynamicQuery);
How to Calculate Age in Sql Server OR Find age using SQL query
Calculate age of employee using SQL Server
To calculate an age from the SQL table is little bit tricky. We are demonstrating how to calculate the age from the date of birth fields in the SQL table. While fill the application form it’s better to fill the Date Of Birth field instead of age field. Because age field can calculate from the Date Of Birth field easily and accurately.
Find the age from the Date Of Birth using SQL Server
Here we are having a table called Employee and having coloumns ID,Name and DOB. From this table we need to find the age of each employee using SQL script.
Please see the table structure.

Calculate age from SQL Server
We can use Date Diff function in SQL Server to get the age of each employee. But if we consider date field only it will get wrong answer because it will consider the current year even date of birth not came this year. The below script and result is example.
For avoiding above mentioned issue, we can use below script to find out the age from the Date Of Birth field using SQL Server.
SELECT DOB AS DateOfBirth, GETDATE() AS CurrentDate, DATEDIFF(YEAR,DOB,GETDATE()) - (CASE WHEN DATEADD(YY,DATEDIFF(YEAR,DOB,GETDATE()),DOB) > GETDATE() THEN 1 ELSE 0 END) AS Age FROM Employee

Calculate age using SQL Server
How to create asynchronous file up loader using ajax in ASP.Net
Ajax files uploader in ASP.Net/C# to upload files to Server as asynchronous
In most of the web applications, there might be have an option to upload files to server. If we are using ASP.Net file uplaoder option to upload files, will have postback after every server request. It may be very disturbance to the user. In order to avoid postback while uploading files into server in ASP.Net/C#, we can implement AJAX mechanism.
Include AjaxControlToolkit reference to the Project
First of all we need to refer ajax tool kit to our project. In order to do this, right click the project and add reference and select the folder that included the ajaxtoolkit file. Once we added, in the aspx page below line will be displayed
<%@ Register Namespace="AjaxControlToolkit" Assembly="AjaxControlToolkit" tagPrefix="ajax" %>
After that we can incuded ajax uploader into the ASPX page like this.
<ajax:AsyncFileUpload ID="fileUpload1" OnClientUploadComplete="uploadComplete"OnClientUploadError="uploadError" CompleteBackColor="White" Width="350px" runat="server" UploaderStyle="Modern"UploadingBackColor="#CCFFFF" ThrobberID="imgLoad" OnUploadedComplete="fileUploadComplete" />
There are some special kind if events are available in the control like onUploadComplete, OnClineUploadError ..
OnClientUploadError – This property is used to execute the client side JavaScript function if file uploading failed.
OnClientUploadStarted – This property is used to execute the client side JavaScript function whenver file uploading start.
OnClientUploadComplete – This property is used to execute the client side JavaScript function after file successfully uploaded.
CompleteBackColor – This property is used to set fileupload control background after file upload complete its default value ‘Lime’.
ErrorBackColor – This property is used to set fileupload control background if file upload failed its default value ‘Red’.
UploadingBackColor – This property is the id of the control which is seen during upload file.
UploaderStyle – This property is used to set fileupload control appearance style either Modern orTraditional. By default its value “Traditional”.
ThrobberID – ID of control that is shown while the file is uploading.
ASPX PAGE
<%@ Register Assembly="AjaxControlToolkit" Namespace="AjaxControlToolkit" TagPrefix="ajax" %> <html xmlns="http://www.w3.org/1999/xhtml"> <head id="Head1" runat="server"> <title>Untitled Page</title> <script type="text/javascript"> // This function will execute after file uploaded successfully function uploadComplete() { document.getElementById('<%=lblMsg.ClientID %>').innerHTML = "File Uploaded Successfully"; } // This function will execute if file upload fails function uploadError() { document.getElementById('<%=lblMsg.ClientID %>').innerHTML = "File upload Failed."; } </script> </head> <body> <form id="form1" runat="server"> <ajax:ToolkitScriptManager ID="scriptManager1" runat="server"/> <div> <ajax:AsyncFileUpload ID="fileUpload1" OnClientUploadComplete="uploadComplete"OnClientUploadError="uploadError" CompleteBackColor="White" Width="350px" runat="server" UploaderStyle="Modern"UploadingBackColor="#CCFFFF" ThrobberID="imgLoad" OnUploadedComplete="fileUploadComplete" /><br /> <asp:Image ID="imgLoad" runat="server" ImageUrl="loading.gif" /> <br /> <asp:Label ID="lblMsg" runat="server" Text=""></asp:Label> </div> </form> </body> </html>
CODE BEHIND
using System; using System.Web.UI; using AjaxControlToolkit; protected void fileUploadComplete(object sender, AsyncFileUploadEventArgs e) { string filename = System.IO.Path.GetFileName(fileUpload1.FileName); fileUpload1.SaveAs(Server.MapPath("Files/") + filename); }
How to crop image using ASP.Net/C# OR Cropping image in C# before upload
DOWNLOAD SOURCE CODE FOR CROP IMAGE IN ASP.NET
Crop images before upload to the server in C#/ASP.Net
In our previous post we demonstrate how we can re size image using C# or Create thumbnail image using ASP.Net. Here we are demonstrating how we can crop images using ASP.Net application using Jquery and C#. In some applications we need to upload images and we need only some portion of the images to get clear picture on the photo. In this case we need to give an option to users to crop image before they are uploading the image.
Include Jquery file/CSS files to the application.
First of all we need to include following jquery/CSS files to the application.
1. jquery.min.js – You can download from here
2. jquery.Jcrop.js – You can download from here
3. jquery.Jcrop.css – You can download from here
Simple steps to crop image using ASP.Net,C#,Jquery
In this application we are having a browse option for selecting an image. Once we selected a image it will display int the screen.

Select an image to crop using ASP.Net C# Jquery
In the next step we will have the option to select an area to crop the image. Once we selected an area we can click crop button on the screen.

Select image area to crop
Then the selected area will be cropped and displayed in the screen.

Cropped area of the image
ASPX Page for crop images using Jquery
<%@ Page Language="C#" AutoEventWireup="true" CodeBehind="CropImage.aspx.cs" Inherits="ExperimentLab.CropImage" %> <!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 id="Head1" runat="server"> <title>Crop Image</title> <link href="Styles/jquery.Jcrop.css" rel="stylesheet" type="text/css" /> <script type="text/javascript" src="http://ajax.googleapis.com/ajax/libs/jquery/1.3/jquery.min.js"></script> <script type="text/javascript" src="Scripts/jquery.Jcrop.js"></script> <script type="text/javascript"> jQuery(document).ready(function () { jQuery('#imgCrop').Jcrop({ onSelect: storeCoords }); }); function storeCoords(c) { jQuery('#X').val(c.x); jQuery('#Y').val(c.y); jQuery('#W').val(c.w); jQuery('#H').val(c.h); }; </script> </head> <body> <form id="form1" runat="server"> <div> <asp:Panel ID="pnlUpload" runat="server"> <asp:FileUpload ID="Upload" runat="server" /> <br /> <asp:Button ID="btnUpload" runat="server" OnClick="btnUpload_Click" Text="Upload" /> <asp:Label ID="lblError" runat="server" Visible="false" /> </asp:Panel> <asp:Panel ID="pnlCrop" runat="server" Visible="false"> <asp:Image ID="imgCrop" runat="server" /> <br /> <asp:HiddenField ID="X" runat="server" /> <asp:HiddenField ID="Y" runat="server" /> <asp:HiddenField ID="W" runat="server" /> <asp:HiddenField ID="H" runat="server" /> <asp:Button ID="btnCrop" runat="server" Text="Crop" OnClick="btnCrop_Click" /> </asp:Panel> <asp:Panel ID="pnlCropped" runat="server" Visible="false"> <asp:Image ID="imgCropped" runat="server" /> </asp:Panel> </div> </form> </body> </html>
Code Behind of the ASPX page for Crop image using ASP.Net/C#
using System; using System.Collections.Generic; using System.Linq; using System.Web; using System.Web.UI; using System.Web.UI.WebControls; using System.IO; using SD = System.Drawing; using System.Drawing.Drawing2D; namespace ExperimentLab { public partial class CropImage : System.Web.UI.Page { protected void Page_Load(object sender, EventArgs e) { } String path = HttpContext.Current.Request.PhysicalApplicationPath + "images\\"; protected void btnUpload_Click(object sender, EventArgs e) { Boolean FileOK = false; Boolean FileSaved = false; if (Upload.HasFile) { Session["WorkingImage"] = Upload.FileName; String FileExtension = Path.GetExtension(Session["WorkingImage"].ToString()).ToLower(); String[] allowedExtensions = { ".png", ".jpeg", ".jpg", ".gif" }; for (int i = 0; i < allowedExtensions.Length; i++) { if (FileExtension == allowedExtensions[i]) { FileOK = true; } } } if (FileOK) { try { Upload.PostedFile.SaveAs(path + Session["WorkingImage"]); FileSaved = true; } catch (Exception ex) { lblError.Text = "File could not be uploaded." + ex.Message.ToString(); lblError.Visible = true; FileSaved = false; } } else { lblError.Text = "Cannot accept files of this type."; lblError.Visible = true; } if (FileSaved) { pnlUpload.Visible = false; pnlCrop.Visible = true; imgCrop.ImageUrl = "images/" + Session["WorkingImage"].ToString(); } } protected void btnCrop_Click(object sender, EventArgs e) { string ImageName = Session["WorkingImage"].ToString(); int w = Convert.ToInt32(W.Value); int h = Convert.ToInt32(H.Value); int x = Convert.ToInt32(X.Value); int y = Convert.ToInt32(Y.Value); byte[] CropImage = Crop(path + ImageName, w, h, x, y); using (MemoryStream ms = new MemoryStream(CropImage, 0, CropImage.Length)) { ms.Write(CropImage, 0, CropImage.Length); using (SD.Image CroppedImage = SD.Image.FromStream(ms, true)) { string SaveTo = path + "crop" + ImageName; CroppedImage.Save(SaveTo, CroppedImage.RawFormat); pnlCrop.Visible = false; pnlCropped.Visible = true; imgCropped.ImageUrl = "images/crop" + ImageName; } } } static byte[] Crop(string Img, int Width, int Height, int X, int Y) { try { using (SD.Image OriginalImage = SD.Image.FromFile(Img)) { using (SD.Bitmap bmp = new SD.Bitmap(Width, Height)) { bmp.SetResolution(OriginalImage.HorizontalResolution, OriginalImage.VerticalResolution); using (SD.Graphics Graphic = SD.Graphics.FromImage(bmp)) { Graphic.SmoothingMode = SmoothingMode.AntiAlias; Graphic.InterpolationMode = InterpolationMode.HighQualityBicubic; Graphic.PixelOffsetMode = PixelOffsetMode.HighQuality; Graphic.DrawImage(OriginalImage, new SD.Rectangle(0, 0, Width, Height), X, Y, Width, Height, SD.GraphicsUnit.Pixel); MemoryStream ms = new MemoryStream(); bmp.Save(ms, OriginalImage.RawFormat); return ms.GetBuffer(); } } } } catch (Exception Ex) { throw (Ex); } } } } DOWNLOAD SOURCE CODE FOR CROP IMAGE IN ASP.NET
Recent Comments