How To Bind GridView Using LINQ To SQL

Here are the steps:
Step 1:

Firstly, we need to create an empty project by opening Visual Studio by pressing command devenv in the start menu search bar or using Windows Run-Command by pressing (Windows icon + R ) and enterdevenv. It will open the installed version of Visual Studio.

If by pressing this opens the old one, you can change it by changing the registry value for updated version by following this link.

Step 2: Now after opening Visual Studio you can create a new website by going to File, New,then Website or by pressing Shift+Alt+N command in Visual Studio.

Step 3:
Now give a name to the project and also select desired location of website.

Step 4:
Now create a new web form by pressing combination of keys (Ctrl + Shift + A).

Step 5:
Now we first add GridView control to the Web Form.
  1. <asp:GridView ID=“gvitems” runat=“server” AutoGenerateColumns=“false”>
  2.                 <Columns>
  3.                     <asp:BoundField DataField=“ID” HeaderText=“Item ID” ItemStyle-Width=“60”  />
  4.                     <asp:BoundField DataField=“Name” HeaderText=“Item Name” ItemStyle-Width=“150” />
  5.                     <asp:BoundField DataField=“Quantity” HeaderText=“Item Quantity” ItemStyle-Width=“150” />
  6.                 </Columns>
  7.             </asp:GridView>
Note: (Possible Exceptions)
  1. Make sure that GridView control must be placed in <form> tag with runat=’server’ attribute.
  2. Another thing which you need to take care of is that you must use AutoGenerateColumns=”false”.
If you are using custom columns which are binding date then you must set this attribute to false otherwise see the image for reference.
As you can see the columns repeated by default, it is set to true when you set it to false and it come to its original position of number of assigned columns.
Step 6: Now we can add New LINQ to SQL class to the project.
 Note: If you are creating an empty project then after adding dbml to your website it gives you the following prompt:

Microsoft Visual Studio
You are attempting to add a special file type (LINQ to SQL Classes) to an ASP.NET Web site. In general, to use this type of item in your site, you should place it in the ‘App_Code’ folder. Do you want to place the file in the ‘App_Code’ folder?
Yes No Cancel
 If you choose Yes option then new folder with the name of App_Code created automatically by the Visual Studio IDE and add dbml inside it.
 If you choose No, then no special folder created in spite of dbml added at your root directory.
After adding DBML file your view look like the following.
As you can see the new dbml class file you can create table and drag and drop from server explorer to the Left view of dbml class to create data classes and in the right view you can drag and drop stored procedures to create methods.
Step 7:  Now lets create database table to get values by querying LINQ query.
  2. (
  6. )
Step 8:

Connect to the server explorer, connect and drag drop table on the left hand side of the dbml class as elaborated above. Firstly, we open the Server Explorer from View => Server Explorer or by pressing keyboard shortcut combination (Ctrl + W, L).

Right click on Data Connections, New Context Menu opens which shows Add Connection option.

After clicking new connection window opens.

Enter the server name and use your required Authentication to log on the server, after that connect to the database by selecting from drop down menu and select your database and click on Test Connection before clicking on OK button as you are best aware of that the connection is connected mode or not.

Step 9: In this step we can drag and drop table from server explorer to dbml class designer view. After this process designer look like this.
Note: As we drag and drop the table the designer will create the connection string in  configuration file.
Step 10: Import the following name space.
  1. using System.Linq;
Step 11: The final step is to query to get items using LINQ to SQL.
  1. using System;
  2. using System.Collections.Generic;
  3. using System.Linq;
  4. using System.Web;
  5. using System.Web.UI;
  6. using System.Web.UI.WebControls;
  7. using System.Data;
  8. public partial class _Default : System.Web.UI.Page
  9. {
  10.     protected void Page_Load(object sender, EventArgs e)
  11.     {
  12.         if (!Page.IsPostBack)
  13.         {
  14.             gvitems.DataSource = GetItemsRecord();
  15.             gvitems.DataBind();
  16.         }
  17.     }
  18.     public List<ITEM> GetItemsRecord()
  19.     {
  20.         BindGridViewDataContext db = new BindGridViewDataContext();
  21.         var listitemsrecord = (from x in db.ITEMs select x).ToList<ITEM>();
  22.         return listitemsrecord;
  23.     }
  24. }

The Final Output


Export To Excel Data By LINQ to SQL

There are many times we require certain reports to be exported to certain require Files i.e Export to Excel, PDF and CSV but for today we can see how we can export to excel with querying to database through LINQ to SQL query.

So let’s start to build interface buttons which will do the rest of work to query and export to excel.

  1. <asp:Button ID=“btnGetData” runat=“server” Text=“Export to Excel” OnClick=“btnGetData_Click” />
This button has OnClick (btnGetData_Click) Event which will fire when we click on it, before starting we will add the following references to the project. on the upper section where reference are defined and also by adding in the Reference folder of solution.

  1. using System.Web;
  2. using System.Drawing;
  3. using System.Reflection;
  4. using System.Data;
  5. using System.Linq;
  6. using OfficeOpenXml;
  7. using OfficeOpenXml.Style;
  8. using OfficeOpenXml.Table;
Now we will write the backend logic of button.
  1. protected void btnGetData_Click(object sender, EventArgs e)
  2. {
  3. DataTable dt = new DataTable();
  4. List<NewsFeed> NewsFeeds = (from emp in db.NewsFeed select emp).ToList<NewsFeed>();
  5.             if (NewsFeeds.Any())
  6. {
  7. ListtoDataTableConverter converter = new ListtoDataTableConverter();
  8. dt = converter.ToDataTable(NewsFeeds);
  9.                 ExportExcel(dt);
  10. }
  11. }
Now I will explain the line of code as you can see we have a button event which will fire on click.
FIrst of all we create DataTable and initialize it on the start which will hold the converted data.
  1. DataTable dt = new DataTable();
Now we have write the linq to sql query and all the result which will fetch through query is moved to the list.
  1. List<NewsFeed> NewsFeeds = (from emp in db.NewsFeed select emp).ToList<NewsFeed>();
After that we will check as a best practice, is there any data returned by the query by writing line of code like this
  1. if (NewsFeeds.Any())
  2. {
  3. }
If list variable contains any thing we have use .Any() as a best practice to check the content length otherwise you can use .Count() method too to check for any count of returned row to do the rest of processing.
  1. if (NewsFeeds.Count())
  2. {
  3. }
Now we will create new class and initialize it to convert the list structure to datatable to ease our work as per our requirement.

  1. ListtoDataTableConverter converter = new ListtoDataTableConverter();
Complete class is elaborated below.
  1. public class ListtoDataTableConverter
  2.  {
  3.  public DataTable ToDataTable<T>(List<T> items)
  4. {
  5.  DataTable dataTable = new DataTable(typeof(T).Name);
  6. //Get all the properties
  7.  PropertyInfo[] Props = typeof(T).GetProperties(BindingFlags.Public | BindingFlags.Instance);
  8.  // Loop through all the properties
  9.  foreach (PropertyInfo prop in Props)
  10.  {
  11.  //Setting column names as Property names
  12.  dataTable.Columns.Add(prop.Name);
  13.  }
  14.  foreach (T item in items)
  15. {
  16.  var values = new object[Props.Length];
  17.  for (int i = 0; i < Props.Length; i++)
  18.  {
  19.  //inserting property values to datatable rows
  20.  values[i] = Props[i].GetValue(item, null);
  21.  }
  22.  // Finally add value to datatable
  23.  dataTable.Rows.Add(values);
  24.  }
  25.  //put a breakpoint here and check datatable of return values
  26.  return dataTable;
  27.  }
  28. }
I have elaborate every piece of code with comments what is going on while converting list to dataTable. We are using the DataTable method to convert it to the datatable from list inside classListtoDataTableConverter.
  1. dt = converter.ToDataTable(NewsFeeds);
Finally, we will use the Export to Excel function by allocating the content type to convert it to the appropriate format. We will call export method and pass converted dataTable from list.
  1. ExportExcel(dt);
For conversion to excel purpose I am using microsoft built in library called open office XML Extension.

Right click on References folder of project and select Add Reference.

Add below highlighted Extensions in to the project.

Add This Method to File.
  1.  private void ExportExcel(DataTable dt)
  2.    using (ExcelPackage pck = new ExcelPackage())
  3.   {
  4.   // Excel Sheet name
  5.   ExcelWorksheet ws = pck.Workbook.Worksheets.Add(“ExportReport”);
  6.   // pass the cell from where we start dumping data and load the data from datatable
  7.  ws.Cells[“A1”].LoadFromDataTable(dt, true, TableStyles.Medium15);
  8.  //Read the Excel file in a byte array
  9.  Byte[] fileBytes = pck.GetAsByteArray();
  10.  HttpContext.Current.Response.ClearContent();
  11.  // Add the content disposition (file name to be customizable) to be exported.
  12.  HttpContext.Current.Response.AddHeader(“content- disposition”,“attachment;filename=Exported_” + DateTime.Now.ToString(“M_dd_yyyy_H_M_s”) + “.xlsx”);
  13.  // add the required content type
  14.  HttpContext.Current.Response.ContentType = “application/vnd.openxmlformats-officedocument.spreadsheetml.sheet”;
  15.  // write the bytes to the file and end the response
  16.  HttpContext.Current.Response.BinaryWrite(fileBytes);
  17.  HttpContext.Current.Response.End();
  18.  }
Code Elaboration:
  1.  // Excel Sheet name
  2.   ExcelWorksheet ws = pck.Workbook.Worksheets.Add(“ExportReport”);
  3.   // pass the cell from where we start dumping data and load the data from datatable, also you can add styling by using table style and check name from excel file for required design
  4.   ws.Cells[“A1”].LoadFromDataTable(dt, true, TableStyles.Medium15);
  5.   //Read the Excel file in a byte array
  6.   Byte[] fileBytes = pck.GetAsByteArray();
  7.  // Add the content disposition (file name to be customizable) to be exported.
  8.  HttpContext.Current.Response.AddHeader(“content-disposition”“attachment;filename=Exported_” + DateTime.Now.ToString(“M_dd_yyyy_H_M_s”) + “.xlsx”);
  9.  // add the required content type
  10.  HttpContext.Current.Response.ContentType = “application/vnd.openxmlformats-officedocument.spreadsheetml.sheet”;
  11.  // write the bytes to the file and end the response
  12.  HttpContext.Current.Response.BinaryWrite(fileBytes);
  13.  HttpContext.Current.Response.End();
Final Output