How to use joins and orderby in Linq to Sql

LINQ to SQL is designed to support ordering by simple primitive types, such as string, int,datetime and so on, for this example I have taken Orders and Customers tables in Northwind Database.

DataClasses1DataContext context = new DataClasses1DataContext();

            var Details = from cus in context.Customers

                          join ord in context.Orders on cus.CustomerID equals ord.CustomerID

                          orderby ord.OrderDate descending

                          select new

                          {

                              cus.Address,

                              cus.City,

                              cus.CompanyName,

                              cus.ContactName,

                              ord.OrderDate,

                              ord.ShipAddress,

                              ord.ShipVia

                          };

Joins in Linq to Sql

Linq to Sql given us excellent feature of joins, the two common joins are the inner join (or just join in LINQ) and the left join. Inner join returns all the rows that are matched in both tables, left join returns all rows from the left table, even if there are no matches in the right table

Sample pieces for inner join and left join

Inner Join

var empDetails = from emp in objEmp.Employees

                         join dept in objEmp.Depts on emp.Deptid equals dept.DeptId

                         select new

                         {

                             emp.EmpName,

                             emp.Designation,

                             emp.Location,

                             dept.DeptName

                         };

Left Join

var empDetails = from emp in objEmp.Employees

                join dept in objEmp.Depts on emp.Deptid equals dept.DeptId into empdet

                         from de in empdet.DefaultIfEmpty()

                         select new

                         {

                             emp.EmpName,

                             emp.Designation,

                             emp.Location,

                             de.DeptName

                         };

Pro LINQ: Language Integrated Query in C# 2008 (Windows.Net) 

Inserting Record and Binding Data to Gridview Using Stored Procedures with Linq to Sql

In this post I explained How to insert a record using linq to Sql and how to get the data and append to grid using StoredProcedures.

For this I have taken two tables named Department and Employee

Please Create tables and stored procedures as follows.

Department Table

CREATE TABLE [dbo].[Dept](

      [DeptId] [int] IDENTITY(1,1) CONSTRAINT [PK_Dept] PRIMARY KEY CLUSTERED ,

      [DeptName] [nvarchar](20) NULL)

Insert into Dept Values('Software')

Insert into Dept Values('HR')

Employee Table

CREATE TABLE [dbo].[Employee](

      [EmpId] [int] CONSTRAINT [PK_Employee] PRIMARY KEY IDENTITY(1,1) ,

      [EmpName] [nvarchar](50) NULL,

      [Designation] [nvarchar](50) NULL,

      [Location] [nvarchar](50) NULL,

      [Deptid] int CONSTRAINT FK_Employee_Dept FOREIGN KEY REFERENCES dbo.[Dept](DeptId))

CREATE PROCEDURE GetDepartmentDetails

      -- Add the parameters for the stored procedure here

     

AS

BEGIN

      -- SET NOCOUNT ON added to prevent extra result sets from

      -- interfering with SELECT statements.

      SET NOCOUNT ON;

    -- Insert statements for procedure here

      Select * from Dept

END

 CREATE PROCEDURE GetEmployeeDetails     

     AS

     BEGIN

      -- SET NOCOUNT ON added to prevent extra result sets from

      -- interfering with SELECT statements.

      SET NOCOUNT ON;

     

      SELECT E.EmpName,E.Designation,E.Location,D.DeptName

    from Employee E

    Inner Join

    Dept D

    on D.DeptId=E.DeptId   

 END

 CREATE PROCEDURE InsertEmployeeDetails

      -- Add the parameters for the stored procedure here

      @EmpName nvarchar(50),

      @Designation nvarchar(50),

      @Location nvarchar(50),

      @DeptId  int

      AS

      BEGIN

      -- SET NOCOUNT ON added to prevent extra result sets from

      -- interfering with SELECT statements.

      SET NOCOUNT ON;

    -- Insert statements for procedure here

      Insert into Employee values(@EmpName,@Designation,@Location,@DeptId)

       Return @@RowCount

     END

Take new project with name LinqtoSql 

 

Click on ok button to create new project

Right click on Solution explorer click on add new item. Here you will find dbml classes add dbml class named as EmployeeDept 

  

Now we need to connect sqlserver through Server Explorer, if you do not find server explorer on left side corner please go to view and select server explorer

right click on dataconnections in  Server explorer click on add connection.

 you will have a option to choose data source type, for our project we have selected Microsoft SqlServer then click on continue.

Here we need select Server name and Database Name once you have selected Server name and Database Name test connection weather succeed.

 

Once finished the  connection with SqlServer you can get all the tables and Stored procedures.

Just drag and drop Tables and Stored Procedures on the dbml Class Surface.visual studio automatically creates properties for table and methods for stored procedures.

Inserting Record

copy and paste flowing html code in asp page.here i have taken three textboxes, one dropdownlist and one button.

<div>

   <table>

   <tr>

   <td colspan="2">

       <asp:Label ID="lblMessage" runat="server" Font-Bold="True" ForeColor="Blue"

         ></asp:Label>

   </td>

   </tr>

   <tr>

   <td>

      EmpName

   </td>

   <td>

       <asp:TextBox ID="txtEmpName" runat="server"></asp:TextBox>

   </td>

   </tr>

   <tr>

   <td>

       Designation</td>

   <td>

       <asp:TextBox ID="txtDesignation" runat="server"></asp:TextBox>

   </td>

   </tr>

   <tr>

   <td>

       Location</td>

   <td>

       <asp:TextBox ID="txtLocation" runat="server"></asp:TextBox>

   </td>

   </tr>

   <tr>

   <td>

       Department</td>

   <td>

       <asp:DropDownList ID="ddlDepartment" runat="server">

       </asp:DropDownList>

   </td>

   </tr>

   <tr>

   <td>

       &nbsp;</td>

   <td>

       <asp:Button ID="btnSubmit" runat="server" Text="Submit"

           onclick="btnSubmit_Click" />

   </td>

   </tr>

   </table>

   </div>

Code Here

 

public partial class _Default : System.Web.UI.Page

    {

        EmployeeDeptDataContext objEmp = new EmployeeDeptDataContext();

        protected void Page_Load(object sender, EventArgs e)

        {

            if (!IsPostBack)

            {

                BindDatatoDropDown();

            }

        }

        void BindDatatoDropDown()

        {

            var v = objEmp.GetDepartmentDetails();

            ddlDepartment.DataTextField = "DeptName";

            ddlDepartment.DataValueField = "DeptId";

            ddlDepartment.DataSource = v;

            ddlDepartment.DataBind();

        }

        protected void btnSubmit_Click(object sender, EventArgs e)

        {

         int iCount=objEmp.InsertEmployeeDetails(txtEmpName.Text, txtDesignation.Text, txtLocation.Text, Convert.ToInt32(ddlDepartment.SelectedValue));

         if (iCount > 0)

         {

             lblMessage.Text = "Record Inserted Successfully";

         }

         else

         {

             lblMessage.Text = "Failed";

         }

        }

    }

after finishing writing the code click on F5 to run your Project

Binding Data to Grid

Please copy and paste the following code in another Asp page named as Grid

<div>

        <asp:GridView ID="grdEmployees" runat="server" AutoGenerateColumns="False"

            CellPadding="4" ForeColor="#333333" GridLines="None">

            <RowStyle BackColor="#EFF3FB" />

            <Columns>

                <asp:BoundField DataField="EmpName" HeaderText="Emp Name" />

                <asp:BoundField DataField="Designation" HeaderText="Designation" />

                <asp:BoundField DataField="Location" HeaderText="Location" />

                <asp:BoundField DataField="DeptName" HeaderText="Dept Name" />

            </Columns>

            <FooterStyle BackColor="#507CD1" Font-Bold="True" ForeColor="White" />

            <PagerStyle BackColor="#2461BF" ForeColor="White" HorizontalAlign="Center" />

            <SelectedRowStyle BackColor="#D1DDF1" Font-Bold="True" ForeColor="#333333" />

            <HeaderStyle BackColor="#507CD1" Font-Bold="True" ForeColor="White" />

            <EditRowStyle BackColor="#2461BF" />

            <AlternatingRowStyle BackColor="White" />

        </asp:GridView>

    </div>

Code Here

public partial class Grid : System.Web.UI.Page

    {

        EmployeeDeptDataContext objEmp = new EmployeeDeptDataContext();

        protected void Page_Load(object sender, EventArgs e)

        {

            if (!IsPostBack)

            {

                BindGrid();

            }

        }

        void BindGrid()

        {

            DataTable dt = new DataTable();

            var v = objEmp.GetEmployeeDetails();

            dt = ObtainDataTableFromIEnumerable(v);

            grdEmployees.DataSource = dt;

           grdEmployees.DataBind();

        }

        private DataTable ObtainDataTableFromIEnumerable(System.Data.Linq.ISingleResult<GetEmployeeDetailsResult> result)

        {

            DataTable dt = new DataTable();

            dt.Columns.Add("EmpName",typeof(string));

            dt.Columns.Add("Designation", typeof(string));

            dt.Columns.Add("Location", typeof(string));

            dt.Columns.Add("DeptName", typeof(string));

            foreach (var v in result)

            {

                DataRow dr = dt.NewRow();

                dr["EmpName"] = v.EmpName;

                dr["Designation"] = v.Designation;

                dr["Location"] = v.Location;

                dr["DeptName"] = v.DeptName;

                dt.Rows.Add(dr);

            }

            return dt;

        }

    }

Once you added above code click on F5.