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

Wednesday 19 September 2007

IE Developer Toolbar released

I've talked about the IE developer toolbar before, but at that time it was beta.

MS have now (I say now, it was actually in May but hey I had a baby recently so forgive the delay) released an official version for download so you can feel confident in installing it on your work PC (yeh right). Looks very much like beta 3 to me but apparently there are various bug fixes and it is 'more reliable'. Also Vista compatible.

Download it here: http://www.microsoft.com/downloads/details.aspx?FamilyId=E59C3964-672D-4511-BB3E-2D5E1DB91038&displaylang=en

For me, the top features are:

  • browser resize to fixed resolutions (without bookmarklets)
  • html, css validation
  • dom tree views
  • see CSS styles 'firing" on elements
  • View partial/element source
  • easily kill cookies/session, cache
  • color picker

Worth a look.

Here's a good overview (albeit of beta 3) on MSDN if you've not seen it before.

Thursday 16 August 2007

N-Tier development revisited

Discussion forums on the web are full of .Net N-tier questions, especially on approaches for achieving loosely coupled layers and lightweight data passing mechanisms. Is it ok to pass a DataSet or DataTable? Should I use Custom classes? This article by Imar Spaanjaars is one of the better ones I have seen on the topic:

http://imar.spaanjaars.com/QuickDocId.aspx?quickdoc=416

He describes the pros/cons of the different approaches. He is a fan of pulling the "data only bit" out of your business layer objects and putting them into separate "business objects" (only containing data). This aids passing of data between tiers and project referencing.

Question though - does pulling the data out of your business layer objects go against pure OO principles? "An object should consist of the data and operations allowed on that data" etc. Abstract data types (ADTs), encapsulation etc bla bla...

Also, I don't like the BO (business object) terminology as it is used, as this implies more than just data... I'd prefer something like CustomerBDO (business data object) or even CustomerEntity. You could then perhaps lose the "*Manager" suffix notation he has adopted for the business logic layer.

Regardless of these points though, I think custom classes are the correct way forward for "passing data", and datatables etc should be saved for when the types of the results are dynamic, e.g. "adhoc reporting" result sets. 

Friday 27 July 2007

Dell XPS M1710 – Vista Ultimate Upgrade

Further to my last post on Vista and my new Dell M1710 laptop.

As I said before, it came with Home Premium on it. I chose Home Premium because it seemed the logical choice for what I do (see Vista Editions)

What the comparison chart does not tell you is that some useful software won’t run on Premium, for example I came to install Virtual PC 2007, but check out system requirements and low and behold you need Vista Ultimate (despite it being Windows XP Prof compatible)! This is madness. Why should VPC need Ultimate Ed?

Anyway, I needed the software so thought I would “simply upgrade” my Home Premium edition to Ultimate via the much advertised “Vista Windows Anytime Upgrade” facility. So I went online to the Windows Anytime Upgrade site,

It indicated that I needed a windows anytime upgrade compatible Vista install disk, so I checked the Dell Vista DVD and… no official “windows anytime upgrade logo” on it. Daaarn. Would Dell really ship an install DVD that was not “upgrade” compliant?

I went ahead and purchased the Ultimate upgrade online (which automatically downloads/installs a file “product key” ready for upgrading) but played safe and paid the extra £5 for a windows anytime upgrade DVD (posted out) and subsequently closed the windows upgrade. I was assured by a message that I could put the upgrade disc in anytime in the future.

Not being very patient to wait for the DVD to arrive (I still could believe that Dell would not ship an upgrade compliant disk - digging around on the Net confirmed my thinking that all Vista DVDs come with all 6 editions on them), I put in the Dell Vista install disk and got the usual “Windows Vista – Install Now” blue/green screen… no mention of the upgrade though. Was it simply going to install Home Premium again? Aaargh! Did the Ultimate product key register correctly? I reluctantly clicked “install now” (still no mention of the Ultimate upgrade) and went through the install process.

At the end of the install I rebooted and waited with great anticipation to see what had been installed – hooray, Ultimate was now on my machine! It had worked!

So to summarise:

- if you wish to upgrade a Dell M1710 from Home Premium to Ultimate, based on my experience, you do not need to buy a separate Windows Anytime Upgrade DVD (I take no responsibility if your system is different though!)
- Despite the very poor installation wizard user feedback (come on Microsoft how did this get past your usability team?), have faith, your new edition is being installed despite what the user interface implies.

Once upgraded, all worked fine except I had no sound (a common problem with upgrades). Reinstalling the latest sound card drivers from Dell gave no joy either. Uninstalling the drivers and then letting Vista itself install appropriate drivers fixed this though. All is now fine.

Virtual PC 2007 now installs correctly and I can safely take a look at Visual Studio 2008 and .Net 3.5 ;-)

Other Useful link: Windows Anytime Upgrade Installation Overview and FAQ

Thursday 26 July 2007

Tool for choosing a colour palette

Being more of a techy than a designer, choosing a suitable colour palette for a web site is not exactly my forte.

Here's a tool that can help: http://www.nickherman.com/colormatch/

Define a colour using the RGB sliders and the system will suggest 6 matching colours for you. Works very well.

Thursday 5 July 2007

Running VS 2005, Oracle 10g client and ODT/ODP.Net on Vista

I've not long received a new laptop (went for the Dell M1710 in the end, not the most portable by any means but a real powerhouse desktop replacement style laptop, just the job when you are running VMs, virtual PCs, Oracle, SQL Server etc all on one machine). Gorgeous 17" screen 1920x1200 which copes nicely with the IDEs of today, although I am not convinced by the current trend of glossy reflective screens - sexy looking they may be but I don't really want to see a reflection of myself whilst coding thank you very much - non-reflective screens (as on my old Inspiron 5150) are much more usable in bright environments. Viewing angles are good though. Came with Vista Home Premium on.

Anyway... once I'd uninstalled all the free 30day trial 'crap' that is installed by default I set out to install all my standard apps that I use day to day.

On the list was of course trusty Visual Studio 2005, along with Oracle 10g client, ODP.Net and Oracle developer tools (ODT).

VS 2005 Prof and SP1 were not a problem, although I did get a couple of "known compatibility issue" pop-ups.

The Oracle versions I'd been using on XP, however were not compatible with Vista (unless you did registry hacks). Oracle have now released officially supported products.

Hence here's what you need if you want Vista compatibility 'out of the box' with Oracle:

Oracle Database 10g Client Release 2 (10.2.0.3)
http://www.oracle.com/technology/software/products/database/oracle10g/htdocs/10203vista.html

Seems to install fine although if you use the Aero interface in Vista you will see a message indicating that the installer is not compatible with that.

ODAC 10.2.0.2.21 (inc. ODP.net plus ODT if you want it) see
http://www.oracle.com/technology/software/tech/windows/odpnet/index.html

Note only the installer has been upgraded to make it compatible with Vista, not the individual ODAC products, so if you are using ODAC 10.2.0.2.20 on another Windows platform (as I do on XP), you don't need to upgrade. See C Shay's article http://cshay.blogspot.com/ for more info.

The above is official released software so can be used in production code.

There is also some nice beta stuff in the pipeline, see 11g stuff http://www.oracle.com/technology/software/tech/windows/odpnet/index_11gbeta.html

Tuesday 12 June 2007

Live Writer - on/off-line publishing to your blogs

This is worth a look if you blog a lot (still beta):

http://writer.live.com/

Talks to Windows Live Spaces, Sharepoint, WordPress, Blogger, LiveJournal, TypePad, Moveable Type, Community Server plus others. I've not tried it in 'offline mode' yet but online seems to work fine. Just got it working easily with blogger and community server (which needs a little more work, see http://wlwplugins.com/how-to-configure-windows-live-writer-for-community-server.php).
Great for managing multiple blogs.

IIS6 app pools and session state

We've intermittently been having some sessions dropping on various web sites, after about 20mins (umm sounds familiar that value...), despite having session timeout settings of say 2 hours in our web app config files (both ASP and ASP.net). After some digging, I came across this:

http://blogs.msdn.com/david.wang/archive/2005/09/19/Why_do_I_lose_ASP_Session_State_on_IIS6.aspx

If you use in-proc session state (which is the default and very popular), your application pool's "shut down worker process after being idle" setting in IIS6 is likely to be 20 mins. Thus ignoring your web app config setting if your application pool is *idle* for 20 mins or more.

Easiest solution is to increase this idle timeout value. The other option is to store state out of process.

Wednesday 23 May 2007

ViewState improvements in ASP.Net 2

I’ve recently been upgrading a project from ASP.Net 1.1 to ASP.Net 2. I’d seen documented that ViewState sizes had been significantly reduced, quoted as up to 50% smaller in some cases. A new serialization format, incorporated in a new formatter class (ObjectStateFormatter) is behind the improvements. See http://www.nikhilk.net/ViewStateImprovements.aspx for further info on how it all works.

Anyhow, I thought it would be interesting to see how much of an improvement I would get on some 'heavy' DataGrid driven data entry intranet pages. Here’s the stats in bytes:

ViewState Size ASP.Net 1.1 (ASP.Net 2)

Page 1: 9892 (7456 75%)
Page 2: 44492 (25696 58%)
Page 3: 39900 (24800 62%)
Page 4: 46300 (23500 51%)

So my worst case was ‘only’ a 75% reduction, but on some pages my ViewState was 51% of the original. Not bad. My advice of course is still to turn off ViewState when not needed ;-)

Monday 30 April 2007

.Net 3.5 beta 1 is here

In case you missed it, .Net Framework 3.5 Beta 1 is now available for download. Obviously not something to install on a production (!) box but it is interesting in that .Net 3.5 is now "officially" on its way to becoming a production reality (rather than in technnology preview form, Orcas downloads etc).

As expected it includes LINQ (Language Integrated Query) and ASP.Net AJAX.

Friday 20 April 2007

WPF/E becomes Silverlight

Microsoft's project name WPF/E (Windows Presentation Foundation / Everywhere) has now been officially named Microsoft Silverlight. Although still only on February CTP, watch this space as the full version is due later in the year (July/Aug'ish).

I like the direction this vector-based technology is going in - decarative XAML markup all nice and separate from the code (I'm a big advocate of decarative UI building). I also like the way it can be embedded into existing HTML/ASPX pages, thus offering a doorway for developers to use the technology in existing ASP.Net apps (it does not have to be an all or nothing approach).

Wednesday 11 April 2007

Usefulness of Code Reviews

When I chat with developers about code reviews, generally I get a positive response in terms of their usefulness 'in theory', although most are not exactly forthcoming when it comes to actually offering to take part in them. I think some see them as a 'threat' or an 'opportunity to receive criticism of their precious code'! This shouldn't be the case and those that believe this I think have missed the point. I see code reviews (or more often in my case, 'system reviews') as opportunities to pause for thought, take a step back, look at what you've achieved and do a critique of how 'well' it has been designed and developed. Every review I have ever done has always had positive outputs.

I've just recently completed a code review of a medium sized project written in ASP.Net/Oracle. I did it 'paired' with another developer and was a joint effort. We concentrated on the following points:



  • The Solution and how it was organised into projects and subdirectory structures.

  • Shared libraries and their usage

  • Consistency in 'style of development'

  • Source control/versions. How Visual Source Safe (VSS) is used for (e.g. source code only or some docs too?)

  • Coding style and conformance to coding standards (and are the standards we have in place up to date/suitable?)

  • Any areas where it would benefit from refactoring

  • Upgrade comments (recently undertaken) from .Net 1.1 to .Net 2/VS 2005. Where could the project benefit from refactoring/rewritten using .Net 2 features

  • Use of the N-tier approach, coupling and possible compromising of layers (e.g. SQL in code behind files?!)

  • Stored procs (Oracle) and adopted standards

  • Pass thru SQL vs Stored procs

  • General concerns and feedback on development so far


Here's the outputs from our session:



  • the project could have benefited from being broken down into more sub-projects

  • our coding standards need updating to bring in new .Net 2 features, new controls, app_themes etc, and JavaScript coding guidelines.

  • N-Tier approach used very well in most places by developers although some SQL present in 'higher layers' from earlier coding (!). 

  • There were some areas where standards were not followed but mostly affecting 'early code'. Most of latest code followed guidelines and best practise

  • Would benefit from the use of Web Services in the shared 'site list' library: 

  • Source control working well. Oracle stored procs, views etc also in VSS.. but would be better if integrated with TOAD. To investigate. 

  • Better versioning needed of shared libs. 

  • Some of the system would benefit from some .Net 2 enhancements (eg master pages, themes, new .net 2 menu tree) but decided to only bring in new .Net 2 features on either brand new modules or where a significant change is needed on an existing part of the system. Consistency important though.


All of this I think is good positive feedback and result in actions where necessary. It was a joint effort, which is important. Even if you are reviewing a new starter's code, there is still opportunity to learn from their past experiences.


If anyone else has experience of doing such reviews, do let me know.

Thursday 29 March 2007

SPA 2007 Conference

Just got back from SPA 2007, held at Cambridge University’s Homerton College.

Held across 4 days (Sun-Wed), it consisted of a mixture of workshops, tutorials, case studies and more open ‘think tank/gold fish bowl’ type sessions.

I presented on ASP.Net / Oracle including lessons learned from a global intranet project I’m currently working on. It triggered some good discussions, both during and after the session, especially regarding the backend platform used (i.e. Oracle rather than SQL Server), state management, along with data and workflow modelling.

Here are some of the other sessions I attended too:

Strategies and Patterns for Systems Continuity
Lessons Learned from Scaling XP
Agile modelling practices on innovative projects
The whys and wherefores of Web 2.0
Effective error handling
Architecting the next generation of .Net applications

Quality of the sessions varied but most were well run and worth attending. I didn’t perhaps ‘learn’ as much as I’d hoped, but two points I did take away were:

1) Web 2.0 is not clearly defined (you don’t say!). I don’t like the term anyway. To me the web is constantly evolving, with new tools appearing and becoming established all the time. To label a ‘moment in web time’ like this does not make sense. There was no clear agreement on details at the conference but most did agree that Web 2.0 will/is having a significant impact on the Internet and business.
2) Agile development (XP, Scrum, Lean) is becoming more widespread and is being used to good effect. I’ve not yet used any of these methods ‘formally’ yet but do already use some of the techniques they encompass. Armed with the knowledge I’ve gained (and some new books!), I’m definitely going to see if I can make use of more Agile techniques in the future.

For me though, the session that I remember most was the plenary by the infamous Professor Tony Hoare… acknowledged widely for his Quicksort algorithm, Hoare Logic, CSP (for formally describing interacting concurrent processes) and his research leading to the Z specification language.

He’s a scientist whom I have looked up to since at University. His plenary was on ‘Assertions and Test driven design’. The talk maybe did not contain as much ‘brilliance’ as his stature and background deserves, but nevertheless none of this matters – just to see him present in person was more than enough for me. A man worthy of his Turing Award (1980) and Knighthood (2000).

Wednesday 14 March 2007

Upgrading projects from ODP.Net 9.2 to ODP.Net 10.2

I've recently been upgrading a system from ASP.Net 1.1 to 2.0. At the same time we are upgrading the Oracle client from 9.2 to 10.2, and thus to ODP.Net (vs10.2.0.2.20 to be precise).

I've now completed the first pass of the conversion and all main parts of the system appear to be working fine - note we have left the Oracle back-end on 9.2 for now, but this is also going to be upgraded to 10.2 too... let's do it in stages ;-)

Here's a couple of issues I've come across so far regarding the upgrade:

a) -1 Result from ExecuteNonQuery

It is expected behavior that ExecuteNonQuery returns -1 when executing a stored procedure (regardless of what the stored proc does), but there was a bug in earlier 9.2.x versions where it returned 1 instead of -1. If you have any code that checks this result, it's worth a scan to see if this affects any of your logic. I was caught out in one of my calls to a stored proc that updated a number of rows, and I had incorrectly assumed it would return "number of rows UPDATEd" like a standard UPDATE statement would (and in this case I was expecting an update count of 1 for 'success' so it all worked fine thanks to the 9.2 bug!). Digging deep into the Oracle ODP.Net docs come up with this:

-------
ExecuteNonQuery returns the number of rows affected, for the following:
If the command is UPDATE, INSERT, or DELETE and the XmlCommandType property is set to OracleXmlCommandType.None.
If the XmlCommandType property is set to OracleXmlCommandType.Insert, OracleXmlCommandType.Update, OracleXmlCommandType.Delete.
For all other types of statements, the return value is -1.
-------

So I read this as meaning all stored procs return -1 for success. If you want the number of recs affected, you'll need to do this yourself by adding an output param instead. If anyone has found out something different please shout ;-)


b) Runtime data types of output parameters:

Ever had problems mapping .Net types to native database field types (and vice versa)? Umm me too. DBNULLS vs nulls, casting issues, loss of accuracy, I'm sure you've been there. Anyway, here's a couple of general issues to watch out for when moving to ODP.Net 10.2:

Example 1:

OracleCommand cmd = new OracleCommand();
cmd.CommandText = "EPR_Month_PKG.Get_Month_YTD";
cmd.Parameters.Add("p_isd_id", OracleDbType.Int32,ParameterDirection.Input).Value = isdID;
cmd.Parameters.Add("p_mp_id", OracleDbType.Int32,ParameterDirection.Input).Value = mpID;
cmd.Parameters.Add("p_month_ytd", OracleDbType.Double).Direction = ParameterDirection.Output;
DBTool.ExecuteStoredProc(cmd);
// if (cmd.Parameters["p_month_ytd"].Value != System.DBNull.Value) // fine in 9.2 but condition never fires in 10.2
if (!((Oracle.DataAccess.Types.OracleDecimal)(cmd.Parameters["p_month_ytd"].Value)).IsNull) // works fine in 10.2
{
// not null... rest of code here
}
cmd.Dispose();

(Note DBTool is simply a DAL helper class that sits as part of a larger DAL library we have).

The commented out if statement worked fine in ODP.Net 9.2. but in 10.2 the output param (specified as OracleDbType.Double) now comes back as an OracleDecimal (hence my cast in the if statement). In fact I've also got cases where the output param type is specified as OracleDbType.Int32 and this still comes back as OracleDecimal. Oracle really needs to be consistent on all this, especially across versions.

Example 2:

Old working code in .Net 1.1/ODP.net 9.2

OracleCommand cmd = new OracleCommand();
cmd.CommandText = "EPR_Month_PKG.Get_Max_MP_ID";
cmd.Parameters.Add("p_max_mp_id", OracleDbType.Int32).Direction = ParameterDirection.Output;
DBTool.ExecuteStoredProc(cmd);
MaxMPID = (int) cmd.Parameters["p_max_mp_id"].Value; // this bit no longer works, you get System.InvalidCastException
cmd.Dispose();

Again, this now fails in 10.2 due to output param data type being returned as OracleDecimal (!).

This new code works fine though in 10.2:

OracleCommand cmd = new OracleCommand();
cmd.CommandText = "EPR_Month_PKG.Get_Max_MP_ID";
OracleParameter ParMaxMPID = new OracleParameter();
ParMaxMPID.Direction = ParameterDirection.Output;
ParMaxMPID.DbType = DbType.Int32;
ParMaxMPID.ParameterName = "p_max_mp_id";
cmd.Parameters.Add(ParMaxMPID);
DBTool.ExecuteStoredProc(cmd);
MaxMPID = (int) cmd.Parameters["p_max_mp_id"].Value;
cmd.Dispose();

The key bit is the specifying of DbType (instead of using OracleDbType). Doing this infers an OracleDbType (you can see a table of mappings in ODP.Net docs) and also ensures a correct mapping to the .Net int type. We could use this "DbType approach" to solve the issue in example 1 above too.

Strings as output params can be handled this way too. If you don't do it this way, I found nulls and empty strings were handled inconsistently in 10.2.


LATEST UPDATE (16 Mar 2007)

Having converted a number of calls to cater for the issues above I have added a static member to DAL class, e.g. DBTool.GetOracleParameterByDbType(...) so typical code can now look as follows:

cmd.CommandText = "EPR_MonthlyReport_PKG.Get_Target";
cmd.Parameters.Add("p_year", OracleDbType.Int32,ParameterDirection.Input).Value = year;
cmd.Parameters.Add("p_year_offset", OracleDbType.Int32,ParameterDirection.Input).Value = (int) Target.YearOffSet.TargetPlus1;
cmd.Parameters.Add("p_mp_id", OracleDbType.Int32,ParameterDirection.Input).Value = mpID;
cmd.Parameters.Add("p_person_id", OracleDbType.Int32,ParameterDirection.Input).Value = personId;
cmd.Parameters.Add(DBTool.GetOracleParameterByDbType("p_target", DbType.Double, ParameterDirection.Output));
DBTool.ExecuteStoredProc(cmd);
if (cmd.Parameters["p_target"].Value != System.DBNull.Value)
{
target = Convert.ToDouble(cmd.Parameters["p_target"].Value);
}

This is nice and tidy. Whether we should also be passing Input params by DbType is another matter but not wanting to interfere with existing code too much maybe go with this for now. Note passing a DbType also ensures that System.DBNull.Value works as expected too.

The helper method by the way is simply:

public static OracleParameter GetOracleParameterByDbType(string paramName, DbType dbType, ParameterDirection paramDirection)
{
OracleParameter NewParam = new OracleParameter();
NewParam.Direction = paramDirection;
NewParam.DbType = dbType;
NewParam.ParameterName = paramName;
return NewParam;
}

Wednesday 21 February 2007

.Net 2.0 Framework redist does not include configuration manager

Interestingly, MS decided not to include the control panel/admin/.net configuration tool with the redist version of .Net 2. Seems a bit odd as this is a useful tool for configuring .Net on servers and it does come with the 1.1 redist. To get it now you need to install the 2.0 SDK (some 300+Mb) where the config manager now resides.

For reasons as to why they moved it and workarounds, see Aaron Stebner's Blog Entry on the topic. I still disagree that the manager should come only with the SDK - the tool is very useful for managing the assembly cache for example. Looking at developer feedback, my guess is that it will be added back into the redist for future versions...

Friday 16 February 2007

Visual Studio 'Orcas' looks promising

Having spent the last year or so getting to grips with Visual Studio 2005 and establishing ASP.Net migration paths from .Net 1.1 to .Net 2/3 (especially when you are considering shared hosting, third party components, Oracle's ODP.Net/ODT etc), I'm now starting to investigate the next incarnation of VS, aka project 'Orcas'/.Net 3.5).

Scheduled for an estimated release in the 2nd half of this year, it will offer many features that we will all be keen to get our hands on:

- Full 'integrated' LINQ support
- AJAX support (environment support for this is improving all the time)
- Target .Net 2, 3 and 3.5 from the same environment
- Split WYSIWYG/code editor and rapid code/design view switching
- Nested master pages
- WPF project types
- Much improved (and W3C standards based!) CSS support.
- Improvements on code editing, intellisense and debugging (e.g. editing Javascript will support 'type inferencing').

If you want to take a sneak preview, you can download the January 2007 CTP, but (unless you are desperate!) I'd probably wait until the February 2007 CTP which will include the new WYSIWYG designer features mentioned above. Note the downloads are 'thoughtfully' provided as Virtual PC images (similar to VMware), so you will need to download/install Virtual PC first. A great way of evaluating s/w without breaking your day-to-day development environment.

And for those like me having to consider migration paths on corporate desktops... the road from VS 2005 to 'Orcas' looks a lot easier than the 'one way wizard upgrade approach' we had with VS 2003 to VS 2005. From what I have read, you 'should be able to simply' open up VS 2005 solutions. Time will tell if this is the reality...

For more information, see Scott's 'First look at Orcas' piece (which includes some screenshots)