LINQ2SQL – Updating Stored Procedures (VS2008)

I’m currently working on a project using LINQ2SQL with stored procedures. I quickly ended up with a problem that rang bells, remdinding me of when I last worked with LINQ2SQL and SPROCS (Some time back), that problem being that once a SPROC has been added to the DataContext, its not possible to (easily) update the output of it, or the signiture.

I remember my previous fix to this was to rename the stored procedure and re-add it (Deleting the old one from the DataContext). This works fine on smaller systems with 1-2 developers – Renaming a Stored Procedure on a larger system may not alwasy be an option.

So, here is a better fix for this scenario – Its not elegant, and still irritating, but it does work…
(Note: Only tested in Visual Studio 2008 – I dont know if this problem exists in Visual Studio 2010)

1. Delete stored proc from DBML file
2. Close DBML file
3. Right click on DBML in solution explorer – Open with > XML Editor then Save it (No need to make any changes)
4. Open DBML file as normal
5. Right click on SPROC in server explorer > refresh (To ensure Visual Studio knows about the changes to your stored procedure)
6. Drag and Drop SPROC to your DBML to re-add it with the new signature/return type
7. Hoorah!

LINQ – Group by multiple columns, with join

In order to get data to drive a new report I was developing this morning, I needed a Group By query. I have done this before, but really could not remember the syntax, hence this post, for my furture reference, and hopefully helpful to others!

var result = (from tbl1 in YourDayaContext.Messages
                           join tbl2 in YourDayaContext.Table2 on tbl1.id equals tbl2.id
                           join tbl3 in YourDayaContext.Table3 on tbl2.id equals tbl3.id
                           group tbl1 by
                           new
                           {
                               tbl1.tbl1_Col1,
                               tbl1.tbl1_Col2,
                               tbl2.tbl2_Col1,
                               tbl3.tbl3_Col1,
                           } into g
                           where g.Count() > 1
                           select new
                           {
                               g.Key.tbl1_Col1,
                               g.Key.tbl1_Col2,
                               g.Key.tbl2_Col1,
                               g.Key.tbl3_Col1,
                               Quantity = g.Sum(tbl1 => tbl1.tbl1_Col1)
                           });

Probably worth mentioning at this stage that I changed the vae/table/column names in the code above from a working version to protect my database schema security – I have not compiled/tested the above.

Lambda Loop

Very quick tip…instead of a ‘traditional’ For Each loop:

foreach (String s in stringList)
{
    LabelOutput.Text += s + "<br/>";
}

Consider a Lambda loop:

stringList.ForEach(s => LabelOutput.Text += s + "<br/>")

The result is less code, more readable code (IMHO!) and having tested both with 10,000 strings in a list, it seems there is no impact on performance.

You can of course filter the list using a Lambda expression, still on the 1 line of code:

stringList.Where(s => s.Contains(foo)).ForEach(s => LabelOutput.Text += s + "<br/>")

Update data following control event in GridView

This week, the request for an additional administration screen one of the web based LOB applications I manage/develop gave me the opportunity to build an elegant, user friendly interface. The functionality required was the ability to update one property of a very simple object, with a very low chance of needing to make updates to other properties in future. It is also worth bearing in mind that the collection of objects concerned is minimal and fixed in quantity (Less than 30 objects).

I decided the best way to achieve this would be to present the objects in a GridView, with no paging, on the basis that it would be easiest for the user if they could see all objects on one screen, and that loading 30 or so objects in one hit would actually be less of a performance hit that the process of querying and displaying paged data. I also wanted all functionality to be provided from within the GridView – Again due to simplicity of the objects concerned – Of which we only need to update one property.

So we have a GridView with three columns:

1. Bound Column: Object Reference
2. Bound Column: Object Name
3. Template Column: Containing a DropDownList, set to AutoPostBack

It is worth mentioning at this stage that all pages main content area are contained within AJAX partial update panels. I dislike AutoPostBack in general, it can make the application seem clunky and slow, although with efficient coding and correct use of AJAX can of course achieve quite the opposite effect/result.

So, the user visits the page, locates the record they wish to update (listed in alphabetical order) and changes the value of the DropDownList, which should then update the associated record/object.

There are two potentially challenging aspects to this setup:

1) How to fill the DropDownList with appropriate values, and select the correct value for each row
(To show the current value, before and after DropDownList change event).

To achieve this, we need to:

• Get a reference to the DropDownList associated with each record/object
• Add appropriate ListItems
• Ensure that the current value is selected

protected void GridViewFoo_RowDataBound(object sender, GridViewRowEventArgs e)
{
    DropDownList dropDownListFoo = null;
    // We are only interested in DataRows (Not headers/footers)
    if (e.Row.RowType == DataControlRowType.DataRow)
    {
        // Get a reference to the DropDownList
        dropDownListLeague = (DropDownList)e.Row.Cells[1].Controls[1];

        // Retrieve the value of the property that will determine the value to set the DropDownList to
        Boolean fooMyObjValue = Boolean.Parse(DataBinder.Eval(e.Row.DataItem, "PropertyName").ToString());

        // Define the values for the DropDownList
        ListItem liYes = new ListItem("Yes", "Y");
        ListItem liNo = new ListItem("No", "N");

        // Depending on the value of the Object proeprty, set the appropriate ListItems selected property
        if (fooMyObjValue == true)
        {
            liYes.Selected = true;
        }
        else
        {
            liNo.Selected = true;
        }

        // Add the items to the DropDownList
        dropDownListLeague.Items.Add(liYes);
        dropDownListLeague.Items.Add(liNo);
    }
}

2) How to update the Object when the user changes the value, via the DropDownList

For this, I made use of the DataKeyNames property of the GridView – Our objects have an ID property – This is the unique identifier for each one, so its simply a case of telling the GridView what property of its Data Bound collection is the key:

<asp:GridView ID="GridViewFoo" runat="server" onrowdatabound="GridViewFoo_RowDataBound" DataKeyNames="ID"...

The DropDownList’s SelectedIndexChanged event is wired up, and runs the following code:

protected void DropDownListFoo_SelectedIndexChanged(object sender, EventArgs e)
    {
        // Get the calling DropDownList
        DropDownList dropDownListFoo = (DropDownList)sender as DropDownList;

        // Get the GridViewRow the DropDownList was called from
        GridViewRow row = (GridViewRow)dropDownListLeague.NamingContainer as GridViewRow;

        // Get the DataKey of the row - Which is our objects 'ID' property
        // because we set the GridViews DataKeyNames property to 'ID'
        int id = (int)GridViewFoo.DataKeys[row.RowIndex].Value;

        // Now we have the ID, we can of course call a method in our BLL (Or run a SQL query if your architecture is not quite ideal!) to update our object property to the new value, derived from dropDownListFoo.SelectedValue

Debug Pre-Processor Directives

If you use any of the methods in the Debug class (such as Debug.Write), you should be aware that these statements will still be in your compiled code (release or debug build) and will still run on your production server.

It could be that such statements make a negligable difference to the performance of your code, but imagine the scenario where the developer wants to check the sum or 3 or 4 items, or worse still a string concatenation to show maybe 3 or 4 variables, within a Loop – This is additional processing that is completely needless!

To avoid this, I use Pre-Processor Directives – Don’t worry about the name, like alot of development related speak they sound scary and complicated, when in fact its a very simple thing to do in order to avoid all they needless processing…

// The first line in your code, define the DEBUG symbol
#define DEBUG

// Then, where ever you use the DEBUG class, wrap your code in an if statement as follows
#if DEBUG
    DateTime startTime = DateTime.Now;
    Debug.WriteLine("ProcessRequest() Commenced: " + startTime);            
#endif

The code within the #if/#endif will be excluded from the compiled code, except of course when you are debugging.

This is a much more elegant solution than having these statements run in release code, or having to strip them all out before a release build.

Highlighting ListItems in a DropDownList

I’ve just completed adding functionality to a system that allowed the user to select a persons name from a DropDownList. The list was structured hierarchically, in that each person in the list was shown indented under his/her manager. To make the structure clearer, I wanted to highlight managers in a different colour. Heres how I did it:

In the loop, where I was creating ListItems for each person (Which were than added to the DropDownList) I added an attribute to the ListItem to apply a CSS class:

Method 1
When items are explicitally added to the DropDownList

foreach (DataRow drSomeRow in someDataTable.Rows)
        {
            String name= drSomeRow ["Name"].ToString();
            String id = drSomeRow ["ID"].ToString();

            ListItem liPerson = new ListItem(name, id);

            liPerson.Attributes.Add("class", "highlightedListItem");
            // Note: In this example, the CSS class is applied to all items in the list
            // In reality you would probably wrap this line in an if statement, checking a record value

            SomeDropDownList.Items.Add(liPerson);
}

Method 2
DropDownLists that are DataBound to a list of objects/DataTable etc?

Often, DropDownLists (and other DataBound controls) will be bound to a DataSource, rather than populated item by item as in example 1 above. In this case, we need to loop the items in the DropDownList after they have been added. Note that this method does depend upon either:

A) Having some way of determining whether or not the ListItem should be highlighted, based on either the Value or Text properties
Or
B) Making an external call passing either the Text or Value propertie values as a key to retrieve some other information
(I would suggest steering clear of B as this potentially means a database/webservice call for each item in your list – Inefficient and slow!

foreach (ListItem liPerson in DropDownListTeams)
            {
                if liPerson.Value.Contains(SomeValue) {
                    liPerson.Attributes.Add("class", "highlightedListItem");
                }
            }

 

hierarchically

Sorting a DataTable

Just a quick tip this one. To sort a DataTable…

1. Clone the Unsorted DataTable
2. Generate a DataRow array, from the unsorted DataTable using .Select, specifying the search criteria (The first argument is the select criteria, which I have left Null so we get all rows)
3. Loop the DataRow array, importing the rows into the new table

 

DataTable dtSorted = dt.Clone();
DataRow[] dataRowsSorted = dtUnSorted.Select(null, "PercentSurveysCompleted ASC");
foreach (DataRow dataRow in dataRowsSorted)
{
    dtSorted.ImportRow(dataRow);
}

Using an Enum as a DataSource

I tend to use Enumurations quite alot in my code – They are a very handy way of restricting the values of data whizzing around your application.

Today I encountered a scenario where I needed to allow the user to select from a bunch of enum entries presented in a drop down list.

This is actually a very simple task, heres how I did it:

DropDownListFoo.DataSource = Enum.GetNames(typeof(MyClass.EnumName));


Enum.GetNames() takes an enum and returns a String array containing the values within the enum.

The key here is that an array is a valid DataSource, where an an enum is not as it does not have any suitable interfaces.

Wizard Control not starting on step 1?

I quite often make use of the Wizard control – Its very flexible, works well and saves me loads of donkey work to get the desired functionality.

One thing that seems to trip me up, is viewing the individual wizard steps within Visual Studio (Design mode). I view/work on a specific step, perhaps change some code, compile, run it and Wham! Where did step 1 go? I’m now starting at step 3!!

Why? When you save a webform that contains a Wizard control in Visual Studio, the step you are editing is set as the ActiveStep, for example, view step 3 in desaign mode, then save your page and the declarative code for the Wizard will look like this:
(Note that ActiveStepIndex=”3″)

<asp:Wizard ID="WizardRegistration" runat="server" DisplaySideBar="False"
onactivestepchanged="WizardRegistration_ActiveStepChanged"
ActiveStepIndex="3" >

I’m not really sure why Visual Studio does this, seems a bit odd to me – The fix?
1) Always remember return to step 1 on your wizard before you save
2) Add the following code, which will ensure that your Wizard always starts at step 1: (My preferred method)

protected void Page_Load(object sender, EventArgs e) {
    if (!IsPostBack) {
        // Set registration wizard to first step
        WizardRegistration.ActiveStepIndex = 0;
    }
}

Checking for a QueryString value does not require a nested if for the null check

You no doubt often find yourself gathering QueryString values, and writing code dependent upon the values found.

You might write:

if (Request.QueryString["foo"] == 1)
 {
     // Do something
 }

Fine, but this will fail if ‘foo’ is not a valid entry found in the QueryString. You’ll get a null reference exception.

So how ahout this:

if (Request.QueryString["foo"] != null)
 {
     if (Request.QueryString["foo"] == 1)
     {
         // Do something
     }
 }

Yup, that’ll work, but its not exactly very elegant code!

Heres another way…

if (context.Request.QueryString["foo"] != null && context.Request.QueryString["foo"] == 1)
 {
     // Do something
 }

The key to this is the fact that we use ‘&&’ (Double ampersand) instead of a single ‘&’ for the comparison operator. The double & operator means the second (right hand side) test is only evaluated if the first (left hand side) test evaluates to true, so our null check happens first, if this is Ok the check for the value we are looking for happens. Assuming this is a match, the code inside our if block will run.

On the flipside of this, using a single ‘&’ means both tests are evaludated – If either or both result in false, the result is – False, otherwise, the result is true.

There is another option, which kind of negates the need for my demo of the double ‘&’ operator, and to be honest this is the best way

• Declare your object at the level you need (e.g. Class, Method)
• Initialise your object at the earliest opportunity, at the same point carrying out the null check, and probably substituting with value, depending on the logic of your application. You may also want to introduce some logic and security here by checking the value is within and expected ranges and of the expected type
• Use the object, knowing it already has a sensible value