Using Linq To SQL to fill a GridView with a Generic List

I’ve ventured into Linq To SQL.  I’m late to the show, but as I’ve been learning it I’m discovering that the tutorials online leave a lot to be desired.  I’ve had to pull things together to fit my own unique needs and maybe this will help you, too.

Since I build dynamic data driven websites the first hurdle was getting the data from SQL into an enumerable list; a Generic List, or IEnumerable.

Tools

I’ll be using SQL Server Management Studio 2012, Visual Studio Ultimate 2013, and, of course, Linq To SQL.  Even if you don’t have these versions I believe the tutorial will still help.  I don’t think I’m pushing any limits here with what I’m doing.  My language of choice is VB.Net, but you can convert the code to C# here.

Build the Database

Obviously, you need a database to pull data from.  Make sure you’ve got a couple tables ready to go.  You’ll also want at least one  basic stored procedure that SELECTS the data from the table.

CREATE PROC MyTable_GetAll
as
Select * from MyTable;

Set up a website

For the purposes of this demo I set up a website using Visual Studio’s File->New Website command and selected “ASP.Net Web Forms Site” from the list.  I then added two new folders under App_Code; Controllers and Data.

Create the DBML

Linq to SQL uses DBML files to create connections between the code and the database.  It becomes the data layer.  By adding a DBML file all the code is generated for you as you add tables and stored procedures.  You could also manually build this file, but for our purposes, we’re going to let Visual Studio do the hard work.

1. Right click on the Data folder and select “Add New Item”
2. Select “Linq To SQL Classes” from the list.
3. Name your file the same as your table.  It’s easier to keep things in order this way.  If you’re dataset will not match a simple table then name the DBML file to whatever name matches the dataset.  I was building a link manager application and my main table is called “cmLinkCatalog”.  The “cm” was to keep table names in order in the database, but I named the DBML file “LinkCatalog.dbml”

If you don’t already have a data connection set up follow these steps.

1. Open the Server Explorer in Visual Studio
2. Right Click on “Data Connections” and select “Add Connection”
3. Fill out the dialogue to connect to your SQL database.

Continuing with the DBML

1. After adding your DBML file you should see the new file opened in design view in Visual Studio. It should look like two windows separated by a vertical scroll bar.
2. Open the Server Explorer in Visual Studio and expand the data connection for your database so that you can see the tables and stored procedures.
3. Drag the table from the Server Explorer to the left hand window in the DBML. You should see your table displayed with the fields listed as “Properties”
4. Drag the “Select” stored procedure from the Server Explorer to the right hand window in the DBML.  You should see it listed as a “Method”
5. Save the file

If you’ve worked with Generic Lists at all, especially in the DotNetNuke environment, open the designer.vb file for your DBML and the code should look familiar.  The way we’ve built this means that code is autogenerated.  If you manually modify this file and then add another method through the interface you’ll wipe out your custom code.

Create a web page

We’ll add a basic grid view to display the data.

1. Add a web page to your site and open the .aspx, or .ascx, and add a grid view.
2. Open the code behind.  I’m not going to use the LinqDataSource object because I don’t like any of the datasource objects. Create a new function called “LoadGrid” that looks something like this.  I was building a Link Catalog, so everywhere you see “LinkCatalog” you can swap in the name for your DBML.

Function LoadGrid()
  'Create a controller
  Dim lc As LinkCatalogDataContext = New LinkCatalogDataContext()

  'Create an empty list defined by the result set
  Dim links As IEnumerable(Of Data.cmLinkCatalog_GetAllResult)
  
  'Get the data
  'd is arbitrary.  Use any variable name you like.  It's the placeholder for the result set.
  links = (From d In lc.cmLinkCatalog_GetAll
                Select d).AsEnumerable()
  
  'Bind the data to the grid  
  MyGrid.DataSource = links
  MyGrid.DataBind()
End Function

It’s very simple when you see it this way, but there are some parts that tripped me up. In the Generic List world everything is already a list and I assumed Linq To SQL created a list.  It doesn’t.  The result set from Linq is an IQueryable object which doesn’t play well with lists.

Now that you have it as an Enumerable and Generic List you can filter, page, and sort.  Those posts are coming soon.