Search - Articles
DevASP.NET for ASP.NET, VB.NET, XML and C# (C-Sharp) Developers Tuesday, March 03, 2009
Dev Articles
Search Directory
ASP.NET
VB.Net
C-Sharp
SQL Server
 

LINQ to SQL in ASP.NET

Author: Rudy Pinson
Download Source Code : 1293_LINQToSQL.zip

LINQ to SQL is one of several implementations of LINQ. This article will explain how to use “LINQ to SQL” from code behind file in ASP.NET.

 
In a previous article on DevASP.NET, I have explained LinqDataSource with an example. LinqDataSource is a good way to bring data from SQL Server database and bind to a data control like GridView.  We can also enable insert, update and delete using LinqDataSource.
 
Some people say SQL queries are no longer in use because of the LINQ to SQL in ASP.NET. You can write LINQ queries instead of using LinqDataSource for LINQ to SQL. LINQ to SQL means we don’t need to write SQL queries and ADO.NET code to get data from database and to insert, update and delete data. LINQ can be used with any data source but LINQ to SQL make it very easy for developers to query data from SQL databases. Before LINQ to SQL, you have to write data access code in two different languages at front end and back end and you need to have good knowledge of both. LINQ to SQL provides you the way to query data in your code files at front end.  LINQ also provides type safety and compile time checking of query expressions. LINQ to SQL completely supports stored procedures, views and transactions.

Below example will show how you can write LINQ queries for LINQ to SQL. Complete source code is available to download at the top.

 

  1. Create a new Web Site in Visual Studio 2010 either in C# or VB.NET
  2. Add a Web Form to Web Site
  3. Now make connection to the server by using server explorer. I am using NORTHWIND Sample Database for this example.
  4. Add LINQ to SQL Classes by using Add New Item. It will be added in App_Code folder.
  5. Drag a Table from Server Explorer table list of your database and drop it in DataClasses.dbml. I am using “Products” table from NORTHWIND sample database.
  6. Now open Default.aspx page and drag and drop a GridView in the page.

     
    <asp:GridView ID="GridView1" runat="server">
     
    </asp:GridView>
     
  7. Open code behind file and write code below in Page Load method. Make sure “System.Linq” namespace exists at the top of your code behind file.

    1. To get data from database

      C#
       
      DataClassesDataContext database = new DataClassesDataContext();
       
      var products = from pro in database.Products
                     where pro.UnitPrice <= 10
                     orderby pro.UnitPrice
                     select pro;
       
      GridView1.DataSource = products;
      GridView1.DataBind();
       
      VB.NET
       
      Dim database As New DataClassesDataContext()
       
      Dim products = From pro In database.Products
              Where pro.UnitPrice <= 10
                     Order By pro.UnitPrice
                     Select pro
       
      GridView1.DataSource = products
      GridView1.DataBind()

       
    2. To insert data into database

      C#
       
      DataClassesDataContext db1 = new DataClassesDataContext();
       
      Product product1 = new Product();
      product1.ProductName = "Any Product";
      product1.UnitPrice = 10;
      db1.Products.InsertOnSubmit(product1);
      db1.SubmitChanges();
       
      VB.NET
       
      Dim db1 As New DataClassesDataContext()
       
      Dim product1 As New Product()
      product1.ProductName = "Any Product"
      product1.UnitPrice = 10
       
      db1.Products.InsertOnSubmit(product1)
      db1.SubmitChanges()
       
    3. To delete data from database

      C#
       
      DataClassesDataContext db2 = new DataClassesDataContext();
       
      var product2 = from pro2 in db2.Products
                     where pro2.ProductName.Contains("Any")
                     select pro2;
      db2.Products.DeleteAllOnSubmit(product2);
      db2.SubmitChanges();
       
      VB.NET
       
      Dim db2 As New DataClassesDataContext()
       
      Dim product2 = From pro2 In db2.Products
                     Where pro2.ProductName.Contains("Any")
                     Select pro2
       
      db2.Products.DeleteAllOnSubmit(product2)
      db2.SubmitChanges()
       
  8. View the Web Site in browser and see the result.

 

   
Add Article Comment:
Name :
Email Address :
   
Comments :
 
   
<< How to Bind TreeView Control to SiteMap and XML files in ASP.NET

Disclaimer - Privacy
© 2002-2017 DevASP.net