.Net ramblings
# Tuesday, 26 June 2007
Mounting ISO images in Vista
Just in case anyone was as stuck as i was today trying to virtually load an ISO image.  the Virtual CD Control Panel thing doesn't work in Vista, and for some reason VirtualCloneDrive wouldn't work for me either.
i eventually found a free tool called PowerISO which works great.  you can create several virtual drives and mount an ISO in each one.  i'm currently installing Orcas Beta 1 from an ISO mounted across the network, i wasn't sure if it could do that but it had no problems.


Tuesday, 26 June 2007 16:48:13 (GMT Daylight Time, UTC+01:00)  #    Comments [0]  .Net General | General

# Thursday, 24 May 2007
Upgrading May CTP Web Site projects to Orcas Beta 1 Web Application Projects
This has involved lots of hand-coding in my experience, and i've done a bit of work to automate the process. 

SQL Metal Stored Procedure Names

Firstly i found that SqlMetal no longer removes the underscore characters with Orcas Beta 1, which typically leaves hundreds of compile errors that are time consuming to fix by hand.  Visual Studio can replace these using regular expressions with a little preparation, depending on your naming conventions.  All my stored procedures are of the form TableName_SELECT_Xyz, or TableName_UPDATE_Xyz etc.  I found i could fix all the compile errors by scanning for {[a-zA-Z]}SELECT and replacing with \1_SELECT to cover the left hand side underscore, and similarly you can do the reverse to insert a right hand side underscore if there is an a-z character immediately after the SELECT (if there are brackets or punctuation then you probably don't want to insert an underscore).  I ran this for SELECT INSERT UPDATE and DELETE, 2 times each.  takes a minute but saved me a lot of time.
More info on using regular expressions within Visual Studio.

Adding Namespaces when upgrading from a 'web site' project.

The Orcas 'convert to web application' facility is very good i must say.  it actually works.  no more half baked designer.cs files or unsynchronised control members in code behind etc.  But it doesn't add namespaces to your aspx or code-behind files, which is a real pain if you have 50+ aspx pages in your site, and several projects to convert.  I wrote a simple winform app to attempt to relieve the hand-coding required.  it also takes out a few of the retired namespaces from MayCTP and adds in the Beta 1 / Linq replacements.  You can customise the namespaces to add/remove, and specify the new namespace to add to the web site aspx + code behind files.  it comes with no warranty and may screw up your files so always take a backup before running!
the idea is that you run the tool on your web site project directory, then create a blank WAP project in Orcas and copy in the files.  Then run the Orcas 'convert to WAP' command on the project root.  if it is successful you will see each aspx page expanded in the project tree.  some pages don't convert because of compile errors with custom controls etc.   i usually found it took a bit of working through the compile errors first before some of the pages would successfully convert to the WAP format.  here's a screenshot and the code if anyone wants to run it:



Source code + executable (zip 36 Kb)
Thursday, 24 May 2007 17:36:00 (GMT Daylight Time, UTC+01:00)  #    Comments [2]  

# Thursday, 17 May 2007
Deploying Crystal Reports for Orcas Beta 1

I know we're not supposed to be going live with .Net 3.5 / Orcas Beta 1 yet, but hey. 

I ran into a Crystal Reports deployment problem with an asp.net web application developed in Orcas beta 1, using the bundled version of crystal reports.  The problem is that the crystal report dlls used in a Beta 1 project are 10.5.3700.0 but there don't appear to be any merge modules available to support this version number, so there is no supported way to run the Orcas version of Crystal Reports on a server, without installing Orcas itself.  I tried numerous options of digging out the 10.5.3700.0 dlls from program files\common files\business objects etc and putting them in the web site bin directory on the server, but that didn't work.  The obvious error message that comes up is as follows:

Could not load file or assembly 'CrystalDecisions.CrystalReports.Engine, 
Version=10.5.3700.0, Culture=neutral, PublicKeyToken=692fbea5521e1304' or one of its dependencies.
The system cannot find the file specified.

what i ended up doing was instructing the web application to bind to the 10.2.3600.0 versions of the assemblies, which are already deployed using the normal CR server install.

here is what i added to the end of my web.config file to get it going:

     ....
<runtime>
        <assemblyBinding xmlns="urn:schemas-microsoft-com:asm.v1">
            <dependentAssembly>
                <assemblyIdentity name="CrystalDecisions.CrystalReports.Engine" publicKeyToken="692fbea5521e1304"/>
                <bindingRedirect oldVersion="10.5.3700.0" newVersion="10.2.3600.0"/>
            </dependentAssembly>
            <dependentAssembly>
                <assemblyIdentity name="CrystalDecisions.CrystalReports.Shared" publicKeyToken="692fbea5521e1304"/>
                <bindingRedirect oldVersion="10.5.3700.0" newVersion="10.2.3600.0"/>
            </dependentAssembly>
            <dependentAssembly>
                <assemblyIdentity name="CrystalDecisions.Shared" publicKeyToken="692fbea5521e1304"/>
                <bindingRedirect oldVersion="10.5.3700.0" newVersion="10.2.3600.0"/>
            </dependentAssembly>
        </assemblyBinding>
    </runtime>
</configuration>

Thursday, 17 May 2007 17:36:06 (GMT Daylight Time, UTC+01:00)  #    Comments [1]  .Net General | Asp.Net

# Wednesday, 16 May 2007
Sql Triggers for Dummies
i always found the syntax of SQL triggers very difficult to remember, so i'm just posting a sample one here.  it performs a cascade delete on a table that doesn't have referential integrity (because there are 2 potential foreign keys).
ALTER TRIGGER [dbo].[Table1CascadeDeleteTable2]
ON [dbo].[Table1]
FOR DELETE
AS
BEGIN
declare @Ref int;
SELECT @Ref = Ref FROM Deleted;
delete from Table2 where Ref = @Ref;
END


Wednesday, 16 May 2007 18:36:37 (GMT Daylight Time, UTC+01:00)  #    Comments [0]  Database

# Wednesday, 02 May 2007
Crystal Reports: Incorrect Log on Parameters for Dataset source
i spent hours trying to troubleshoot this.  obviously the error makes no sense because CR should not be logging on to anything in a dataset scenario.
anyway, a thousand thanks to Jason for his post on the MSDN forums with the simple answer: set the datasource to the DataTable, not the DataSet.

i can't wait to lose Crystal reports altogether and move to the microsoft reporting thingy, i haven't had time to play about with it yet but at least it will be properly programmed and it won't contain the 10 years of bugs that crystal reports have carried through each release of their software.  </RANT>


Wednesday, 02 May 2007 18:43:46 (GMT Daylight Time, UTC+01:00)  #    Comments [0]  .Net General | Asp.Net

# Tuesday, 01 May 2007
SQL: format numeric to x decimal places
i found this was very hard to figure out.  i read lots of approaches to hack the data into char(5) etc to give you "23.54" using Round() etc.  but that seemed messy.  the key is to cast to type Numeric(size, decimalPlaces).  for example:
CONVERT(Numeric(10,2), (Count(ID) * 100. / @Total)) as Percentage

the above will display "23.54" (for example), including rounding, as a number.


Tuesday, 01 May 2007 12:53:15 (GMT Daylight Time, UTC+01:00)  #    Comments [3]  Database

# Monday, 30 April 2007
LINQ: upgrading May CTP projects to Orcas Beta 1
here are a few of my main findings/hurdles encountered when upgrading May CTP web projects to Orcas beta 1 Web Application Projects.  I'm targeting version 3.5 of the framework for deployment on a Server 2003 with .Net 2.0 runtime installed, i will update this article if it doesn't work out. 
  • if you had any code in the "App_Code" folder, VS will probably have set the compile action to "content", it should be changed to "compile".  otherwise you will get errors like: The type or namespace name 'xyz' could not be found (are you missing a using directive or an assembly reference?)
  • you'll have to remove any reference to the System.Query and System.Expressions, these are not part of the new LINQ spec.
  • Replace System.Data.DLinq with System.Data.Linq.
  • if you want to use any LINQ expressions, like "from x in db.Table select x" then you need to include the System.Linq namespace.  otherwise you will get errors like 'System.Data.Linq.Table<xyz>' does not contain a definition for 'Where' and no extension method 'Where' accepting a first argument of type 'System.Data.Linq.Table<xyz>' could be found (are you missing a using directive or an assembly reference?)
  • you will also need to include System.Linq.Expressions if you are using "language-level code expressions to be represented as objects in the form of expression trees".
The old LINQ assemblies are versioned 1.0.2319.19044 (May CTP) but the new ones for Beta 1 are versioned 2.0.0.0.  Have a check through the referenced assemblies in your project to make sure you have the latest versions. 

Crystal reports

look out for the new crystal report assemblies, 10.5.3700.0.  a web app referencing the new versions in web.config will complain if these versions are not available on the server.  i'll update soon when i have found out how to do this.


Monday, 30 April 2007 11:50:28 (GMT Daylight Time, UTC+01:00)  #    Comments [0]  .Net General | Asp.Net

# Tuesday, 17 April 2007
SQL: optional parameters in stored procedure where clause
I found this out today, if you want a stored procedure to handle optional values without limiting the result set when the parameter is NULL, you can use a CASE/WHEN statement to ignore the parameter in the where clause if it is null.  you just set ColumnX=ColumnX via the CASE statement, which will always be true, effectively resulting in (TRUE AND {remaining condition})
Procedure dbo.Select_Totals_For_Year
(
@Name NvarChar(200),
@Year int
)
AS
BEGIN
select Name, Count(ID) AS [Total]
FROM Table1
WHERE Name = @Name AND
Year = (CASE WHEN @Year is null THEN Year ELSE @Year END)
group by Name
order by Name
END

Tuesday, 17 April 2007 14:43:30 (GMT Daylight Time, UTC+01:00)  #    Comments [6]  Database

# Friday, 13 April 2007
SQL: Display full name in "Surname, Firstname" format.
not the most elegant solution you'll ever see, but i find it very useful when i don't want to store several columns for the different parts of a person's name in a database table.
it takes the surname based on the last space in the name.  so "Paddy Joe Gonzales" will be listed as "Gonzales, Paddy Joe".
Right(FullName, PatIndex('% %', Reverse(FullName))) + ', ' + Left(FullName, Len(FullName) - PatIndex('% %', Reverse(FullName)))    


Friday, 13 April 2007 14:13:30 (GMT Daylight Time, UTC+01:00)  #    Comments [0]  Database

# Monday, 02 April 2007
How to get YYYY-MM date formats in SQL
not exactly rocket science, but i always forget this sort of thing. 
CONVERT(char(7), GetDate(), 121)

this gives a date like "2007-03".  it works because SQL formats the dates like YYYY-MM-DD etc., so you can just take the first X characters to take as much of the date as you want.

another handy one i use is to get DD/MM/YYYY format:

Convert(NVarChar(10), GetDate(), 103)

the full list of date styles is on msdn2

Monday, 02 April 2007 14:28:57 (GMT Daylight Time, UTC+01:00)  #    Comments [0]  Database