Friday, 22 February 2008

Mix 2008... just over a week to go

Mix 2008 is less than two weeks away. I missed it last year and reading the post event blogs etc, kicked myself for not attending. This year I made no such mistake and registered early to avoid the inevitable sell out (last year various Microsoft employees themselves were not allowed past the entrance, having failed to register in time... "your name's not down you're not coming in"...).

Choosing which conference(s) to attend each year is often a difficult call. I usually allocate myself one "attend only" conference plus a "speak at" conf if I have something useful (rare I know) to talk about. This is separate from the single day seminar stuff we often attend during the year. One conference a year seems reasonable from a budget, time, family commitments, ROI etc point of view.

So, if you are attending only one, which one should it be? I try to vary it. Obvious candidates for me are the US based PDC and Tech·Ed along with the UK based SPA. SPA I've been to before a number of times, with PDC and Tech·Ed on my "must go there sometime if I can justify it" list.

This year, Mix was at the top of the list. When choosing, I often go out of my comfort zone to learn new stuff and being a day-to-day techy server-side kind of a guy rather than a whizzy flash, silverlight, wpf type of soul, Mix fits the bill nicely.  Mix will be an ideal opportunity to dedicate some time to acquiring new techniques and technologies (albeit at surface level), being a Web conference biased toward creativity, media, design,[hopefully] usability and rich client-side UIs - I'm especially interested in seeing where "we are up to" with Silverlight 2, WPF, .Net 3.5, Expression Blend, XAML, Web 2 (i dislike that phrase) and generally where the Web interface is heading (see the full session list here, which includes keynotes by CEO Steve Ballmer and .Net Framework team founder Scott Guthrie). I'm also especially interested in chatting with fellow delegates to see what they are up to.

The conference looks set to be entertaining too, with some non-developer focused events such as:

  • Tue eve: The King of Kong: A Fistful of Quarters (movie screening and Q&A with Steve Weibe, one of the stars of the movie, Ed Cunningham the producer, and Twin Galaxies referee Walter Day).
  • Wed eve: Party at club TAO
  • Thu eve: RockBand tournament, on a real stage, massive sound system and huge plasmas!

Glitzy Vegas is of course another attractor...

Tuesday, 15 January 2008

Using ExcelWriter on 64bit Kit

I've recently been migrating some web apps to 64bit Windows 2003 Server. So far all testing has gone pretty well, with most pure ASP.Net apps running on the kit with little or no code changes.

One issue you may have though, is if your app uses any COM interop with old 32bit components. Earlier ExcelWriter versions used exactly this approach, providing dot net wrapper classes for what were essentially 32bit COM objects on the box. You will find these will no longer work on the new kit (unless you use WOW64 emulation) so the key is to ensure you now use the "pure dot net" versions of ExcelWriter.

Conversion is quite straight forward. Here's some old "dot net wrapped" 32bit only code:

SAExcelApplicationDotNet Xlw = new SAExcelApplicationDotNet();  // old 32bit wrapper ExcelApplication
SoftArtisans.ExcelWriter.SAWorksheet Sheet = Xlw.Worksheets[1];
Sheet.Cells[1, 1].Value = 20;
Xlw.Save(@"test.xls", SoftArtisans.ExcelWriter.SASaveMethod.saOpenInPlace, SoftArtisans.ExcelWriter.SAFileFormat.saFileFormatExcel2000);

The above code also requires ASPCompat=True in your aspx page to ensure single-threaded apartment (STA) mode is used for that page's thread. Not ideal for web apps, see COM Compatibility Page for full run down.

Anyhow, here's the pure dot net version:

ExcelApplication Xlw = new ExcelApplication();   // new pure dot net ExcelApplication
Workbook WB = Xlw.Create();
Worksheet WS = WB.Worksheets[0];
WS.Cells[1, 1].Value = 20;
Xlw.Save(WB, Page.Response, "test.xls", false);

Easy... even I can follow the above!

Tuesday, 6 November 2007

MS Search Server 2008

Well it's been a long time coming but it looks as if Microsoft have decided to release a standalone 'crawler-based search engine' (based on its SharePoint offering) set to replace its rather dated (albeit free) file-based index server in the first half of 2008.

There will be two editions plus the full blown Office SharePoint Server.

It is worth investigating the free express edition at least, which looks like it may suffice for smaller projects... you can get the RC version here.

VS 2008 released end of November

Looks like we all need to get those licenses organised... Visual Studio 2008 (aka 'Orcas' and .Net 3.5) is due end of this month:

http://www.microsoft.com/presspass/press/2007/nov07/11-05TechEdDevelopersPR.mspx

Friday, 2 November 2007

Book on ODP.Net

Do a book search on Amazon for most .Net topics and you will usually be rewarded with a wealth of doorstops to choose from. Enter the search term “ODP.Net” though and you get erm… one result. This I find a little surprising, especially as, according to Oracle at least, ODP.Net (Oracle’s Data Provider for .Net) offers [probably] the best performance and richness for accessing Oracle back-ends. I've used ODP.Net myself on some fairly large scale developments and find the provider reliable, functionally more rich than Microsoft's offering (see a comparison article on MSDN, albeit now a little dated) and has excellent performance.

Anyhow, the aforementioned Amazon "only resulting" book (entitled ODP.NET Developer's Guide) recently landed on my doormat from the publisher PackT who contacted me to take a peruse at it. I've not had chance to look at it in detail yet but here's a chapter summary in case you are looking for such a volume...

The Chapters

Summary of the content:

Chapter 1: introduces the concept of Oracle Database Extensions for .NET and provides information about Oracle Developer tools for Visual Studio.

Chapter 2: introduces the Provider Independent Model in ADO.NET 2.0 and how it relates to Oracle.

Chapter 3: shows you several methods to retrieve data from an Oracle database. You will work with the core ODP.NET classes like OracleCommand, OracleDataReader, OracleDataAdapter etc.

Chapter 4: is all about CRUD operations but also includes caching, array binding, offline data, transactions and error handling

Chapter 5: PL/SQL stored procedures and executing routines in PL/SQL packages. Array parameters and ref cursors are also covered.

Chapter 6: dedicated to dealing with Large objects in Oracle. This chapter illustrates concepts, configurations, and programming for BFILE, BLOB, and CLOB (or NCLOB) in conjunction with ODP.NET.

Chapter 7: Oracle XML DB. It provides information about generating XML from existing rows in tables, manipulating rows in a table using XML, and working with native XML in the Oracle database.

Chapter 8: a bit of a mix this one. Covers database change notifications, Asynchronous Application development, Web Application development, Web Reporting (including grouping, sub-totals, charts etc.), Object-Oriented Development with ODP.NET and ASP.NET, XML Web Services development using ODP.NET and Smart Device Application development (for clients like the Pocket PC etc.).

Chapter 9: introduces you to Oracle Developer Tools (ODT) for Visual Studio 2005.

All .Net code examples are in VB.Net.

Tuesday, 2 October 2007

Upgrading from Visual SourceSafe 6d to VSS 2005

We've recently just upgraded from Visual SourceSafe 6d to 2005. The process was (surprisingly) painless.

This is worth a read before you start: MSDN Visual SourceSafe Install Documentation

 Here's the steps I went through:

Server:

  • created a Virtual Machine (VM) running windows 2003 to host the VSS 2005 server. We decided to use a VM this time to host the service, thus aiding portability and backups/DR.
  • Connected to the VSS 2005 iso install image file as a mapped drive (via VMWare's excellent "CD Rom use iso image" option).
  • Installed VSS 2005 (I chose full install).
  • Got all developers to "check in" their code in the old 6d database.
  • Copied over the VSS 6d database files to the new VM (I used robocopy as I dislike explorer's "sorry I've had to stop copying EVERYTHING because one file was in use" etc...). Copying them across is OK because the file structures are compatible which makes migration easier.  TIP: allow plenty of time for the copy to take place if you have a large database.
  • Give relevant Windows user(s)/groups modify access to the database dir (ntfs permissions).
  • Opening up Visual SourceSafe, gives a prompt for database the location. Browse to the dir where you copied your database to. All done. VSS Users come across too with access rights.
  • I also then ran VSS analyze (you are prompted first time in) to pick up any corrupt or missing files in the database.

Client:

  • Uninstall VSS 6d via add/remove programs. (if you don't, I found that 2005 got installed separately and left 6d there too, despite what some documentation says).
  • Rebooted
  • Install VSS 2005 (choose custom and untick server components).

Initial impressions? Firstly, here's the relevant "what's new in VSS 2005":

  • Access via IIS (via web services). Mainly useful for off site working. Not tried it yet.
  • LAN booster service (Wow.. anything with "boost" in it I want!). Once enabled you will see a new service SSService.exe appear in service list. Its job... to compress files for transfer, check for newer files, handle chatting to client blah d bla bla. Note this only works with the Visual Studio (VS) plug in.
  • Improved VS Plug in. For example, to open a new project from source safe you now simply select File/open project/ and "Sourcesafe (LAN)" appears as an option in the dialog. Also, files are now retrieved asynchronously (background thread) so that annoying "lockup while fetching files" should be a thing of the past.

So far my feeling is that VS integration is better. File access seems quicker (but I doubt by the "3-5 times faster" quoted by Microsoft). Less VS hanging during "get latest" type activities and generally more responsive. Apart from that you are unlikely to notice any major changes.

What about VS 2003 Support?

For those lucky developers supporting .Net 1.1 projects, you still need trusty VS 2003 so it was a relief when I fired VS03 up and the VSS add in worked fine. I successfully fetched an ASP.Net 1.1 project, checked out some files and all seemed normal. Performance was back to VSS 6d standards though.

If I come across any issues I'll post them here.

Wednesday, 26 September 2007

Passing lists to SQL server stored procedures

One much needed feature missing from SQL Server 2005 is the ability to pass "a list of values" from .Net as a parameter to a T-SQL based stored procedure. Loads of scenarios spring to mind but here's a couple of obvious ones: 

  • INSERT a list of values into the database in one "chunky" call (e.g. some IDs from a CheckBoxList)
  • SELECT rows where IDs are IN (<list of IDs>)

You get the idea. Taking the INSERT as an example, there are various approaches you can adopt to achieve the desired result:

  • Use dynamic SQL! I'm not even going to talk about this as this blog entry is on stored procs and dynamic SQL is rarely the ideal solution for obvious reasons ;-)
  • Make a stored proc call for each ID to insert. This is the most common approach I see in various projects, mainly because it is the easiest to implement. The drawback of course is if I were to insert 60 values, it would result in 60 "chatty" calls to the database. Ummm... me hates chatty repetitive db calls plz.
  • Pass comma separated values via a VARCHAR (or similar) parameter. This works fine but has messy "string splitting" in the stored procedure to extract the IDs and then build the SQL statement in the procedure itself. Prone to SQL injection and not the best performance.
  • Pass the list as an XML parameter. This is nicer and is my preferred option (see below)

There are other approaches too (see a detailed description of arrays and lists in sql server by Erland Sommarskog).

Using XML

Using XML for "list passing" has a number of benefits, in particular the ability to pass lists of more "complex types" rather than just single values.  Here's a stored procedure I'm using in a current project (I've stripped out irrelevant code) which takes Study data and inserts it in "one set based query".

CREATE PROCEDURE [dbo].[Study_SaveData](
    @subjectStudyID int,
    @entryAB char(1),
    @studyDataXML XML
    )
AS
BEGIN
    INSERT INTO StudyData (subjectStudyID, entryAB, studyParID, tpID, dataValue)
    SELECT    @subjectStudyID AS SubjectStudyID,
            @entryAB AS EntryAB,
            StudyTab.StudyCol.value('StudyParID[1]','int') AS StudyParID,
            StudyTab.StudyCol.value('TPID[1]','int') AS TPID,
            StudyTab.StudyCol.value('DataValue[1]','float') AS DataValue
    FROM @studyDataXML.nodes('//StudyDataList/StudyData') AS StudyTab(StudyCol)
END

To call this in T-SQL, you would have something like this:

EXEC    [dbo].[Study_SaveData]
        @subjectStudyID = 34,
        @entryAB = 'A',
        @studyDataXML = '<StudyDataList><StudyData><StudyParID>931</StudyParID><TPID>2732</TPID><DataValue>1</DataValue></StudyData>
<StudyData><StudyParID>931</StudyParID><TPID>2733</TPID><DataValue>2</DataValue></StudyData>
<StudyData><StudyParID>931</StudyParID><TPID>2734</TPID><DataValue>3</DataValue></StudyData>
</StudyDataList>'

In your application's DAL layer, your C# calling code could be (again code simplified for brevity):

public static void SaveData(StudyData studyData)
 {
     DBHelper DBH = new DBHelper();
     DBH.AddParameter("@subjectStudyID", studyData.SubjectStudyID);
     DBH.AddParameter("@entryAB", studyData.EntryAB);
     // pass all data values in one go to avoid 'orrible chatty round trips ;-)
     DBH.AddParameter("@studyDataXML", GetStudyDataXMLString(studyData));
     DBH.ExecuteNonQuery("Study_SaveData", CommandType.StoredProcedure);
 }

which calls the method below to translate the DataValueList property (utilising generics) into an XML string:

 private static string GetStudyDataXMLString(StudyData studyData)
 {
     StringBuilder XMLString = new StringBuilder();
     XMLString.AppendFormat("<StudyDataList>");
     foreach (KeyValuePair<StudyData.StudyDataKey, double?> SDKV in studyData.DataValueList)
     {
         XMLString.AppendFormat("<StudyData>");
         XMLString.AppendFormat("<StudyParID>{0}</StudyParID>", SDKV.Key.StudyParID);
         XMLString.AppendFormat("<TPID>{0}</TPID>", SDKV.Key.TpID);
         XMLString.AppendFormat("<DataValue>{0}</DataValue>", SDKV.Value);
         XMLString.AppendFormat("</StudyData>");
     }
     XMLString.AppendFormat("</StudyDataList>");
     return XMLString.ToString();
 }

You get the idea. studyData.DataValueList contains the "list data" to be passed in and inserted into the database. I used StringBuilder for the xml concatenation as in this case I think it fits the bill but purists might prefer an XmlTextWriter approach.

This will ding dang do then! I don't think the code needs detailed explanation, but comments and queries welcome.

In summary, it performs very well and is adaptable for various lists of objects and more complex structures.

Clarkey

Useful Refs