Linq is one of the best things that has happened in recent past in .Net framework world. It has made data manipulation so much easy. Yes, Linq has its own set of limitations but for most part it does the job. For those special cases you can always go back to classic ways. In this post, I will describe use of Linq for Sql and provide some simple answers to questions like:

• How to connect to database using Linq?
• How to query data from a table using Linq?
• How to use Linq with GridView, DataGrid etc.?

Namespace and reference to use Linq with Sql

The classes that you need to use Linq with Sql live in System.Data.Linq namespace. So in your project you will have to add the following line at top of your source code file.

This namespace lives in System.Data.Linq assembly. That means that you will have to add reference to this assembly in your project. Now you are all set to us Linq.

How to connect to the databae?

You have been using ADO.Net for a while now and first thing we all do is have a connection object that will be used to connect to database, open it and later on close it. Well when you are using, all that plumbing is taken care of for you by Linq frameework. The entry point to all actions in Linq for Sql is DataContext object. This object takes care of establishing connection with the database. You can provider either a connection string or connection object to create instance of DataConext object. Rest will get taken care of for you.

var dataContext = 
new DataContext(ConfigurationManager.ConnectionStrings["blogengine"].ConnectionString);

Query data using Linq

Now you have set up DataContext object, it is time to get some data from the database. You will notice that DataContext object provides methods like GetTable, ExecuteQuery, ExecuteCommand etc. All the methods that return collection of data, expect another parameter which is Type of an object that represent the data returned from query or command. In otherwords, the method is looking for a mapping between table in the database to an object. Here is an object definition that I used in my code to map to fields from a datatable that I wanted to fetch.

[Table(Name="be_Posts")]
public class BlogPost
{
  [Column(IsPrimaryKey=true)]
  public Guid PostID
  {get;set;}

  [Column]
  public string Title
  {get;set;}

  [Column]
  public string MiniUrl
  {get;set;}
}

There are few attributes on this class that are to be notices. First, there is TableAttribute on the class itself. By default Linq assumes that name of the class or object is same as table in the database. But if your table name does not match with the class name, then you can use this attribute to provide the name of the table that maps witht this obejct. For example in my case table name be_Posts is to be mapped to BlogPosts object which is my ViewModel. Next attribute is ColumnAttribute. You will assign this attribute to properties or fields that needs to be queried. If your column name does match with name of the property or field, you can provide that mapping as well. There are more values you can set in the column attribute. I will discuss those in subsequent posts. For now this simple definition of .Net object will work for our simple query purposes.

Bind Linq To GridView

Now we have our Linq query set to go, rest is just setting this collection to our GridView object on ASP.Net page and rest is all taken care of for us. Following code snippet shows how in few lines we are able to connect to database, query the data and bind it to a GridView.

void BindGrid()
{
 var dataContext = 
  new DataContext(ConfigurationManager.ConnectionStrings["blogengine"].ConnectionString);
 var posts = dataContext.GetTable<BlogPost>();
 postsGridView.DataSource = posts;
 postsGridView.DataBind();
}

It is as simple as these 4 lines of code to connect to database, query the table and bind to a gridview using Linq to Sql.

Download Sample Project

This is based on a question asked by one of my blog readers.

I have 2 datagrids. Each Grid has the same amount of columns and each grid has a select column as the last column on the right added from the "gridview/ edit Columns/ CommandField/ Add" sequence. The first GridView has the Select column as a Link The second GridView has the select Column as a button I want to be able to change the text for both the Link and button in cell(4), setting them to the value in cell(1) from the same row using the GridView_RowDataBound event. However, using "cell(4).text = cell(1).text" just overwrites the text value removing the hyperlink and button.

The behavior described in this question is as expected. When you set text of a cell in grid, it directly affects HTML that is going to be rendered. When you set text value of a cell, it means that you are setting innerText of the cell. The column that GridView creates for command fields (Edit, Delete and Select) are a (anchor) or button elements. So you can see what will happen if you set text value in that cell. It will wipe out those link or button controls and replace them with simple text string.

There are properties like EditText, DeleteText and SelectText for CommandField column in grid view. If you try to set these values using a server side method by passing it DataContainer object, you will get following exception thrown.

Databinding expressions are only supported on objects that have a DataBinding event. 
System.Web.UI.WebControls.CommandField does not have a DataBinding event.

After looking at the requirements, i realized that requirements are as simple as replacing text with value from another cell in the same row. There is no need for doing any server side tricks or things like that. I can simple put together a simple client side java script that will take values from cell 1 and put them in whatever cell I want. Abd I came up with this small javascript solution using jQuery. This small code snippet shows how you can manipulate GridView or DataGrid on client side using jQuery. Let me show you the client side javascript that I added on the page. Then I will explain what this code is doing.

<script type="text/javascript">
function updateCommandLinks() {
 var $gridTable = $('#productsGrid');
 var rows = $gridTable.find('tbody > tr');
 var slicedRows = rows.slice(1, rows.length - 2);
 slicedRows.each(function() {
  var cells = $(this).find('td');
  var cellElem1 = cells.get(1);
  var cellElem5 = cells.get(5);
  $(cellElem5).find('a').each(function() {
   $(this).addClass('commandlink').append(" " + cellElem1.innerText);
  });
 });
}

$(document).ready(function() {
  updateCommandLinks();
});
</script>

The above code may look little verbose considering you can write very concise code using jQuery. But for sale of explaining and debugging, I decided to make it little bit verbose. I am sure you can reduce it to half the lines of code that I have written.

The implementation adds a handler for document load event. In that event handler here are the steps it follows:

1. Finds the element that has id of productsGrid. In our case, that is HTML element ID of our grid view.
2. In the table, it gets collection of all the rows, identified by tr tag.
3. Since in my implementation I have header and pager, that adds three rows into the collection. One top row for the header and then the last row itself will contain another table that contains a row for paging elements. To keep it simple, I decided to use slice function to remove first and last 2 rows from collection. You will need to modify this implementation depending on your grid rendering.
4. It iterates over each row in the collection.
5. For each row it then finds all cells, identified by td tag.
6. In my case, I want to replace text in command links with text from second column. So I saved reference to cell at index 1 by using get function.
7. Then I extracted all elements with tag a from sixth column.
8. Then it iterates over collection of anchor a tags and appends text from second cell to text in each of the links.

I think that is a simple implementation that serves the purpose without making any changes on the server side. The attached project has the complete implementation for this grid. This is a Visual Studio 2010 project. But you should be able to copy the script from the page to your implementation.

Download Sample Project

This was a question was asked by one of my site visitors, Mike. Following is the text of the question:

This is a similar question to one you have already answered in formatting Grid Views. However there are 2 main differences. First: I want to change the value of a column (not the format) in any row if the row preceding it has a certain value in the same column.

This question translates to How do you change value of a data grid column based on values of previous row(s). I generalized this question to cover all previous rows and not just the proceeding row. Answer to all such questions relies on handling events like RowDataBound or RowCreated events. When a data grid or grid view renders, RowDataBound fires when row is being data bound and then RowCreated is fired after it has been data bound and row has been created. So depending on at what stage of rendering you want to change behavior of a row, you will subscribe either of these events. In the sample project, I am subscribing to RowDataBound event.

Next step is to access values from previous rows. Here you have choice. One, you can keep some local vaiable that stores values from previous row(s) and then use them in current row event handling. Two, you can access the previous GridRow based on index. In this sample i will discuss the approach of accessing previous row based on index and then extracting values from certain cells.

In RowDataBound event handler, GridViewRowEventArgs provides you access to DataItem associated with current row only. You do not have access to DataItem associated with previous rows. But at this point, previous rows have been prepared for rendering. You have access to all the cell values associated with previous row. You can access GridRow object of previous rows and extract text from cells that you are interested in. In the sample project, I am accessing ListPrice from fourth column and then displaying it in current row along with price associated with current row. Well, this does not sound like something that is very interesting or useful. But it serves the purpose of demonstrating you will accomplish the task.

Here is the code snippet from sample project.

protected void OnRowDataBound(object sender, GridViewRowEventArgs e)
{
 if (e.Row.RowType == DataControlRowType.DataRow)
 {
  double price = -1.0;
  double prevPrice = -1.0 ;
  // Access the previous row.
  if (e.Row.RowIndex != 0)
  {
   GridViewRow prevRow = this.productsGrid.Rows[e.Row.RowIndex - 1];
   if (null != prevRow && 
    prevRow.RowType == DataControlRowType.DataRow)
   {
    double.TryParse(prevRow.Cells[3].Text, out prevPrice);
   }
  }
  var thisRowData = e.Row.DataItem as DataRowView;
  if (!Convert.IsDBNull(thisRowData["ListPrice"]))
  {
   double.TryParse(thisRowData["ListPrice"].ToString(), out price);
  }
  var ctrl = e.Row.FindControl("prevPriceLabel") as Label;
  if (null != ctrl)
  {
   ctrl.Text = string.Format("{0} - {1}", prevPrice, price);
  }
 }
}

Feel free to send me any request for any other grid view implementation you would like to be answered or implemented.

Download Sample Project

While working on a car dealership listings web site, I was experimenting with inserting google maps into the data grid. The grid will show name and address of top rated car dealers in certain categories. And along side their information, it will show their location on google map. To accomplish this task, I needed the longitude and latitude of car dealership's address or location. Some time back in my earlier post Convert Address and/or IP address to Geo Location I described how you can utilize google map's api to convert a physical address to geo location coordinates. This is the same technique that I used in Backyardtweets to show tweets in a specified location. So use the google map api to get these coordinates and insert these coordinates in the java script.

Now that you have geo location corresponding to an address, your task is to insert that little piece of java script in each row of the data grid. The way google map java script works is that it requires a HTML element where it can insert the map image. So I inserted a div with unique id in a cell in each row and then passed that unique id to java script that is used to render the map. Add an event handler for RowDataBound event and there you can add the element and register a client script for map as well. Here is some code snippet that shows how this all is accomplished.

protected void OnDealerRowDataBound(object sender, GridViewRowEventArgs e)
{
 if (e.Row.RowType == DataControlRowType.DataRow)
 {
	var carDealer = e.Row.DataItem as CarDealer;
	var divId = string.Format("dealloc_{0}", carDealer.Id);
	var mapPanel = e.Row.Cells[2].FindControl("mapPanel") as Panel;
	var div = new HtmlGenericControl("div");
	div.Attributes.Add("id", divId);
	div.Attributes.Add("class", "mapdiv");
	mapPanel.Controls.Add(div);
	string js = GetGoogleMapScript(carDealer, divId);
	ScriptManager.RegisterStartupScript
	  (this.Page, this.GetType(), "_map_" + carDealer.Id, js, true);
 }
}

private string GetGoogleMapScript(CarDealer dealer, string ctlId)
{
 string loc = string.Empty;
 if (!string.IsNullOrEmpty(dealer.State))
 {
	loc = string.Format("{0},", dealer.State);
 }
 loc += dealer.Country;
 decimal longitude, latitude;
 GoogleMapUtility.GetGeoLocationFromGoogle(loc, out longitude, out latitude);
 dealer.Location.Latitude = latitude;
 dealer.Location.Longitude = longitude;
 return GetJScriptForGeoLocations(longitude, latitude, ctlId);
}

public static string GetJScriptForGeoLocations(decimal longitude, decimal latitude, string elemId)
{
 StringBuilder sb = new StringBuilder();
 sb.Append("function initialize_" + elemId + "() {{");
 sb.Append(" if (GBrowserIsCompatible()) {{ ");
 sb.Append(" var map = new GMap2(document.getElementById(\"{0}\"));");
 sb.Append(" map.setCenter(new GLatLng({1}, {2}), 13);");
 sb.Append(" map.setUIToDefault();}}}} initialize_" + elemId + "();");
 return string.Format(sb.ToString(), elemId, latitude, longitude);
}

The attached project with this post has all the implementation for this sample implementation. And this is a Visual Studio 2010 project. I have not used any 2010 specific namespaces or code in this implementation so you should be able to take all the code and move it into a VS2008 or VS2005 project. And you will need to get key for google map api use from google as well.

Feel free to drop us a line if you have something in mind that you would like us to prototype and write about it.