I'm running some SQL scripts to find the value of named URL parameters because I need to look through the SSRS ReportServer's history of ran reports and get a grip on which parameters users are selected. I wrote a T-SQL function to parse the value of the parameters field as follows:-

USE [ReportServer]
GO

SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO

CREATE FUNCTION [dbo].[fnParseParameter]
(
    -- Add the parameters for the function here
    @paramText varchar(1000), @paramName varchar(50)
)
RETURNS varchar(50)
AS
BEGIN


    -- Declare the return variable here
    DECLARE @paramValue varchar(50)
    declare @paramPrefix varchar(55)
    declare @lenPrefix int, @startPos int

    -- parameter name incl. equals char.
    select @paramPrefix = @paramName + '='
    select @lenPrefix = len(@paramPrefix)

    -- move cursor to end of parameter prefix
    select @startPos = charindex(@paramPrefix, @paramText) + @lenPrefix
    select @paramValue = substring(@paramText, @startPos, len(@paramText))

    -- cut off at the first '&' unless we're at end of parameter string
    if(charindex('&', @paramValue, 0) > 0)
    begin
        select @paramValue = substring(@paramValue, 0, charindex('&', @paramValue, 0))
    end
    
    RETURN @paramValue

END

Thought I'd keep a list of tools which I use for .NET code checking.

This has arisen out of a problem compiling code from SourceSafe which had been manipulated by other developers following a 2 year gap in my contribution to the source code. Checking the live DLLs with the local DLLs would help build confidence in preventing any breaking change which I could deploy to the live application.


I started down the route of using ildasm from the command-line and then using the WinMerge Utility to compare the outputs. Unfortunately the order in which the IL code gets written out to files can be markedly different between assemblies. i.e. a like-for-like comparison was too time-consuming.

I found the BitDiffer utility which does this hard work for you and to date I've been impressed with its ease of use. It definitely fits the bill and would be ideal in the QA process within a number of deployment scenarios. Wonder if VSTS or other testing environment has this sort of tool built-in?

Will update this post with any further findings on this subject.

I've been doing some ASP.NET development work in my spare time and came across a customer requirement where the Calendar control used for a reporting system needed to be able to:

1. change the day that the week started on based on a stored text value
2. select all days from the current week from today's date back to and including the starting day of the week (whatever that was configured to)

The use of different enumerations for Day surprised me and also the looping logic required took some thinking about when the comparitive integer values of Days could not be used as a means of comparison.

Screenshot and code sample below ran for today's date.

using System;
using System.Collections.Generic;
using System.Linq;
using System.Web;
using System.Web.UI;
using System.Web.UI.WebControls;

namespace WebCalendar
{
    public partial class _Default : System.Web.UI.Page
    {
        protected void Page_Load(object sender, EventArgs e)
        {
            _cal.SelectionMode = CalendarSelectionMode.DayWeek;

            // setup today's date values
            DateTime dtCurrent = DateTime.Now;
            DayOfWeek dowCurrent = dtCurrent.DayOfWeek;

            // setup day to start week
            string strDayToStartWeek = "Thursday"; // test text value - TODO: from database
            DayOfWeek dowDayToStartWeek = (DayOfWeek)Enum.Parse(typeof(DayOfWeek), strDayToStartWeek);
            FirstDayOfWeek configLeadingDOW = (FirstDayOfWeek)Enum.Parse(typeof(FirstDayOfWeek), strDayToStartWeek);

            // set calendar to start week on stored preference
            _cal.FirstDayOfWeek = configLeadingDOW;

            // select the dates
            SelectDatesForWeeklyReport(dtCurrent, dowDayToStartWeek);
        }

        /// <summary>
        /// Select all the days from today back to the day which is nominated as the beginning of the week
        /// </summary>
        /// <param name="dtCurrent">Today's date</param>
        /// <param name="dowDayToStartWeek">Day which is nominated as the beginning of the week</param>
        private void SelectDatesForWeeklyReport(DateTime dtCurrent, DayOfWeek dowDayToStartWeek)
        {
            DateTime dtSelectedDate = dtCurrent;

            bool exitLoop = true;

            while (exitLoop)
            {
                if (dtSelectedDate.DayOfWeek == dowDayToStartWeek)
                {
                    // select the start of the week
                    _cal.SelectedDates.Add(dtSelectedDate);
                    exitLoop = false;
                }
                else
                {
                    _cal.SelectedDates.Add(dtSelectedDate);
                }
                dtSelectedDate = dtSelectedDate.AddDays(-1);
            }
        }
    }
}

Although I'm fairly comfortable with the basic setting up a Connection, Command & Parameters and use of the DataReader, Chapters 3 & 4 of Sceppa's book on ADO.NET v2.0 was good at covering a refresher as well as fleshing out the details of Async calls.

// TODO: complete the following subject area - resources mapping.

Data types:  Binary Large Objects (BLOBs); Filestream, Spatial, Table Value Parameters

DataAdapter / TableAdapter

Exceptions

---

Update 24/04/2010
Have just done the MeasureUp test for this objective to gee myself along a bit. Only just got over half of the 34 questions correct. Some wrong answers were simply down to exam technique and others were down to not yet having covered the material. However, I can now re-focus on these areas. Will relate the resources I find useful on the areas which I need to brush up on. Anticipating a few more to follow up on compared to the 1st objective.

I'm hitting through all the material to take the ADO.NET exam next month. The exam objectives are currently outlined here on Microsoft Learning.

Have been using all sorts of different information sources to date and I think I will try and collate resources per exam objective. Maybe it will help others as well as being a good source of info for my own revision and future reference.

The ADO.NET chapter (ch.22) in Troelsen's Pro C# book - I've got the v3.0 version which is up to date enough for this objective. This is good for understanding the general features of DbProviderFactory classes. I ended up writing a small windows utility which mirrored the code example he used. However, Part 2 of Sceppa's book on ADO.NET v2.0 was also good in providing some tips on using the PropertyGrid control and associating it with a DbConnectionStringBuilder (instead of the SqlConnectionStringBuilder which Sceppa uses). I pushed the example a little further than Troelsen did by using DbProviderFactories.GetFactoryClasses(). The datatable returned has a defined structure. Passing the Invariant Name to the GetFactory() method brings back the applicable provider Factory instance from which you can create all your related ADO.NET objects for connecting to (and manipulating) data from your particular database.

Took the MeasureUp test just on this objective and scored 72%. Will update this post with resources matched to the my incorrect answers. Which provider for which database was a particular issue needing review.

---

Review of incorrect answers:

Edited 19/04/2010

The use of RaiseError in SQL Server will only generate a SqlException when the severity level is > 10. For message of severity 10 or less you must subscribe to the SqlConnection's InfoMessage event. This exposes an object of type SqlInfoMessageEventArgs which holds a SqlErrorCollection on its .Errors property. This allows developers to foreach per SqlError raised and show related error information.

The selection of Data Provider to access certain databases (especially when you have 2 databases and only the choice of 1 provider) requires a bit of memorising of the matrix provided on MSDN. The interesting content is under the title "Choosing a .NET Framework Data Provider".

Edited 20/04/2010

Connecting to Sql Server Express versions have their own peculiarities. Specifically, there is the possibility of configuring User Instances on a per user basis. The implications for connection strings are detailed on MSDN - refer to User Instance and AttachDBFileName and their possible values.

But I found this forum post which hopefully will work. It will HAVE to work!

Adding a dataset to your report model may use a SQL query from an external database. When selecting the ellipis (...) on the Query parameter a list of stored procedures is displayed from the database pointed to by the configured datasource property. If this query does not contain unique names amongst all columns then an error "Property value is not valid" with the detailed message "An item with the same key has already been added". I think this is due to the Dataset object cannot be created using identically named columns (DatasetFieldDefinition).

1. With datasource configured, create your dataset.




2. Select stored procedure as the Query for the Dataset




3. Error message



4. SQL query causing problems

ALTER PROCEDURE [dbo].[mikeGetData]
    -- Add the parameters for the stored procedure here
    @WhichCategory int
AS
BEGIN
    -- SET NOCOUNT ON added to prevent extra result sets from
    -- interfering with SELECT statements.
    SET NOCOUNT ON;

    -- Insert statements for procedure here
    SELECT cat.[Name] as Category, subcat.[Name] as Category
     
        FROM [Production].[ProductCategory] cat inner join [Production].[ProductSubcategory] subcat
            on cat.[ProductCategoryID] = subcat.[ProductCategoryID]

        WHERE cat.[ProductCategoryID] = @WhichCategory


END

I'm in the process of converting some web applications from v1.1 to v2.0 which is proving interesting.

Initially I used a blank ASP.NET Web Site and added in the web applications using the IIS option and C# library projects.
I got loads of errors and warnings when compiling. I have needed to qualify the use of my Menu class with its namespace as the Menu class is now part of the framework and some new methods for registering client script blocks. A forum post and some time later I decided to start again using the Web Application Project model (ironic aside: under Step 6 there is a link to a MSDN site regarding breaking changes in v2.0 - it just happens to be a broken link) which is a download for VS 2005.

Overall, I have found this better so far as there are less compilation errors. However, one of the few I had I only got 3 results from google and only 1 usable and correct solution.

The whole solution now compiles but there are some hiccups in the execution which I am still sorting out.