` CS 5610 Home

CS 5610 Web Development Home

http://www.ccs.neu.edu/teaching/web/


The CS 4550 Web Development site is now at http://cs4550.com/cs4550/

This CS 5610 page updated on


Keep in mind:

      

Introduction

Microsoft has created a wonderful technology called “Language Integrated Query” or LINQ to provide an object-oriented front end to access and update data on a SQL Server or in an XML file. In these notes, we will focus on the SQL situation.

To support the mapping of SQL data, structure, and behavior to an object-oriented set of classes, Microsoft provides a designer tool that is reached by creating a New Item in ~/app_code using the dialog choice:

LINQ to SQL Classes

Once you choose a name for the designer file, Visual Studio will create the file:

name.dbml

This file contains the design decisions. Once this file is saved, Visual Studio creates two additional files:

name.dbml.layout

name.designer.cs

The dbml.layout file captures the visual representation (boxes and arrows) of the designer information.

The designer.cs file contains the auto-generated C# code for the LINQ to SQL mapping. This is the file that must be deployed to the server. The first two files may be deployed but they will only be of interest in Source or Statistics Server.

It turns out that if you use the designer in a naive way then it has some annoying defaults. My thanks to Armin Galliker who pointed out that if the Properties of the designer dbml object are set before beginning a design then the defaults may be overridden to great benefit.

Prior to knowing this, I was manually editing the auto-generated C# code to fix the problems with the defaults. Now I no longer need to engage in such a problematic software engineering practice.

Step Zero for the LINQ to SQL Designer

We insert here a link to a story with snapshots for the steps needed to use the LINQ to SQL Designer in Visual Studio 2010.

We strongly urge you to look at this story before continuing with the prose below.

LINQ to SQL Designer in Visual Studio 2010

The First Step in the LINQ to SQL Designer

It is important that the name given to the dbml file strongly suggest what data the file will manage.

On my site, I have a file pubs.dbml that manages the tables, views, and stored procedures for the pubs database.

For the Northwind database, I have a file Northwind.dbml that mananges the tables, views, and stored procedures in the Northwind database.

In my own rasala database, I have two independent tables so I have two separate dbml files:

  • Browsers.dbml that mananges the tiny Browsers table with user browser preferences.
  • US_State.dbml that mananges the small US_State table with state and population data.

In each case, I use the file name to strongly suggest what data the file will manage. I urge you to use similar care in selecting the file name.

By default, when a dbml file is created, it does not protect the auto-generated C# code with a namespace. This is very unfortunate because if two databases have tables with the same name a name conflict will be created in the auto-generated code. This error will prevent your entire site from compiling.

Thus, it is quite important to supply a namespace when creating a dbml file.

We recommend that the namespace for a dbml file be set up with the following convention:

edu.neu.ccis.username.filename

Here username is your CCIS username and filename is the name used on the dbml file. This convention will prevent conflicts both within your site and across sites if your code is used by another student.

On my site, this convention leads to the following namespaces:

  • edu.neu.ccis.rasala.pubs
  • edu.neu.ccis.rasala.Northwind
  • edu.neu.ccis.rasala.Browsers
  • edu.neu.ccis.rasala.US_State

As you can see, this makes it easy to remember the relationship between a database or table, its dbml file, and its associated namespace.

By default, the LINQ to SQL designer auto-generates a connection string to the database being used in the designer. In an act that is even more evil, the designer inserts this string into ~/web.config with no notification. This interferes with maintaining the parallelism of the various versions of the web.config needed for the remote machine, the CCIS lab, and the IIS Server.

The creation of the auto-generated connection string will happen the moment you transfer the first bit of information from the SQL database to the designer. Therefore, if you are going to stop this action, you must act at the very start of using the designer.

The first step in working with a new dbml is to set the critical properties.

Absolutely do not touch the database or its information before setting the critical properties.

Here is how to set the critical properties.

Open the Properties Window. This may be available as a tab on the right side of the Visual Studio window. If not, use the menu sequence:

View > Properties Window

The next actions are best explained with a snapshot taken from the Properties Window of the pubs.dbml file.

pubs.dbml properties window

You can see that there are 2 namespace settings but I take the simple path and make both settings the same following my naming conventions:

edu.neu.ccis.rasala.pubs

When the Properties Window opens, the Connection field is blank. If it remains blank as database information is added, then a new connection string will be auto-generated and immediatly added both to this field and to ~/web.config. Hence to avoid an auto-generated connection string, this field must be set.

It turns out that the field is actually a dropdown list that will show all of the connection strings installed in ~/web.config. Since I already had prepared a connection string for pubs, namely, pubsCS, I can select this and I am set to go. No auto-generated connection string will be created and ~/web.config will remain unmodified.

Once these 3 property settings are done, you may move on to acquiring the desired database information.

Acquisition of Database Information

The LINQ to SQL designer provides a drag-and-drop interface to permit a user to specify what tables, views, and stored procedures will be imported into the designer. To be precise, actual data is not imported, only table structure, relations, definitions, etc.

To use the drag-and-drop interface, one must set up a connection to the database in Server Explorer. There may be a tab for this on the right side of the Visual Studio window. If not, use:

View > Server Explorer

A snapshot of the Server Explorer with the connection to the pubs database open is shown below:

pubs in Server Explorer

To establish such a connection, you must have the 1433 tunnel active. Right-click on Data Connections and select Add Connection.... Then provide credentials as in the discussion in Accessing SQL. You should save your password if this is happening on your own machine. Then select or enter the database name and click OK.

The database connection will be stored with your web site project in Visual Studio so it will be available in the future as needed.

For the pubs database, I decided to import everything: tables, views, and stored procedures. Another option I might have chosen is to import just all tables. If you want to import finer subsets, you should examine the table structures to make sure you are not missing tables whose presence is needed for other tables. I find it easier to “import all”.

The actual import of tables, views, and stored procedures is trivial.

  • Select all tables and drag them to the left pane of the design surface.
  • Select all views and drag them to the left pane of the design surface.
  • Select all stored procedures and drag them to the right pane of the design surface.

After each drag, allow the designer a few seconds to create the visual feedback of boxes and arrows or procedure calls.

Designer Snapshots

Here is a snapshot of the left pane of the design surface for pubs.dbml:

Tables and views for pubs

Here is a snapshot of the right pane of the design surface for pubs.dbml:

Stored procedures for pubs

The US_State Example

There is a very simple example on the Rasala site that uses one table with data about the 50 US states plus Washington DC and Puerto Rico. This table has 3 columns: Abbr, Name, and Population. The data is old but whatever. Here is a snapshot of the table in Server Explorer.

US_State table in Server Explorer

Here is the link to the simple LINQ based web page that uses this data:

US state data via LINQ to SQL connection

Here is the link to the source of this page:

~/us_state/Default.aspx

Here are the links to the associated files in ~/app_code:

~/app_code/US_State.designer.cs

~/app_code/US_State.designer.extra.cs

We strongly suggest that you play with the state application until you are sure how it works. Then you can fruitfully examine the source files to learn why it works.

We will simply make some remarks to highlight important points.

  • The 4 columns in the state application are all sortable. The only work that needs to be done is in the markup:

    • AllowSorting="True" in the header of the GridView
    • The choice of a SortExpression for each column.

    Note that columns 3 and 4 use the same SortExpression, namely, Population. This is perfectly fine. Note also that there is no C# script. The LINQ to SQL classes together with GridView handle sorting automatically.

  • In the markup for the LinqDataSource, one must explicitly give the full type for ContextTypeName:

    edu.neu.ccis.rasala.US_State.US_StateDataContext

    Unfortunately, using a namespace does not apply to the markup side of ASP.NET.

  • The file US_State.designer.cs is exactly the file that is auto-generated by US_State.dbml. Note that is has the correct namespace and correct connection string rasalaCS. The connection string always refers to the containing database not to any individual table.

  • The file US_State.designer.extra.cs is created by hand to add the properties needed to show the population bars and to tweak one definition. This file makes use of a wonderful C# feature: partial classes. Partial classes allow one to add to the definition of a class in a separate file. The main definitions are given in US_State.designer.cs and are left alone. The extra definitions are placed in the new file.

  • The main extras in US_State.designer.extra.cs are the properties to define the population bar image tag, its url, and its alt tag. Fortunately, the asp:ImageField can make use of the url to enable the bar images to be placed in the GridView. It is gap in the design of asp:ImageField that the alt tag cannot actually be set dynamically and that a title tag (for tool tips) cannot even be set.

  • The LINQ to SQL designer has a practice of appending s to a table name to create its plural. Unfortunately, Data is plural so adding the s is stupid. The tweak in the extra file is to permit the use of DataTable in place of the awkward Datas.

This LINQ to SQL code was based on an earlier example that explored the use of text files, SQL databases, and XML files to provide data for assorted markup objects. The sample site is:

13 Experiments using US state data

Here is the link to the source of this page:

~/experiments/us_state/StateData.aspx

Here are the links to the associated files in ~/app_code:

~/app_code/USStateData.cs

~/app_code/USStateList.cs

~/app_code/USStateDAL.cs

You should be able to see the relationship of these earlier files to the code utilized with LINQ to SQL.

Other files related to the 13 earlier experiments may be found in ~/app_data:

~/app_data/statedata0.txt

~/app_data/statedata.txt

~/app_data/statedata1.xml

~/app_data/statedata2.xml

~/app_data/statedata3.xml

Finally, the bars are created with the AutoBox utility. See:

~/app_code/AutoBox.cs

~/app_code/handlers/AutoBoxHandler.cs