Plip's Weblog

Phil Winstanley - British .NET chap based in Lancashire. Enjoys tea and tech. Working for Microsoft.

March 2005 - Posts

Which parameter marker should I use? ADO.NET 2.0, Sql Server @, Oracle :

It's really easy in ADO.NET 2.0 to get the correct Parameter marker or prefix. This can be done dynamically at run time, returning the correct parameter marker for the provider you're using.

This code will take "Phil" and return either "@Phil" for SqlClient use or ":Phil" for OracleClient use.

public static string FormatParameter(string ParameterName)
    DbConnection Conn = Connection;
    Conn.ConnectionString = ConnectionString;
    string ParameterMarkerFormat = Conn.GetSchema("DataSourceInformation").Rows[0]["ParameterMarkerFormat"].ToString();
    return String.Format(ParameterMarkerFormat,ParameterName);

Pretty cool I think you'll agree.

Posted: Mar 24 2005, 12:07 AM by Plip | with 50 comment(s)
Filed under: , ,
DeveloperDeveloperDeveloper - Free UK Event - May 14th - Microsoft Campus

Fancy spending a Saturday networking and learning with other Developers? 

Then come and hear about Microsoft technologies presented by non-Microsoft speakers in an informal environment… may even want to take the opportunity to present yourself. 

DeveloperDeveloperDeveloper takes place on the 14th May in Reading at the Microsoft Campus

Activities will include independent speakers on a wide range of topics, a networking area and fun zone.  And as it’s often it’s hard to justify a day off work to attend training and events, the DeveloperDeveloperDeveloper day is being held on a Saturday and is FREE.

It’s FREE and I’m going to be there, so would love to meet as many of you as possible, anyone going to go?

Register here: -


14 May 2005 09:30 - 14 May 2005 16:30 (GMT) GMT, London
Welcome Time: 09:00

Language: English

Microsoft Ltd
Chicago 1, Chicago 2, Memphis
Building 3
Microsoft Campus Thames Valley Park Reading Berkshire RG6 1WG
United Kingdom

General Event Information
Products: MSDN.

Recommended Audience: Developer.

For the first time ever in the UK, Microsoft is hosting a unique event for Developers to learn, share and hear from other Developers - NO Microsoft speakers will present.  Held on a Saturday, DeveloperDeveloperDeveloper is designed to be an informal day; the agenda will be determined by the developer community.  You will have the opportunity to see new faces present in a relaxed atmosphere.  Activities will include presentations on a wide range of topics (see suggestions below), a networking area and fun zone.

• Preparing for ASP.NET 2.0 - I'm giving that one ERK!
• .NET Debugging Facilities
• Patterns of Data Access in .NET
• An Overview Of ClickOnce Deployment
• What’s New In Internationalization In .NET 2.0?
• Unit Testing Using Visual Studio 2005 Team Test
• Introduction To FxCop And Writing Custom Rules
• Refactoring Using Visual Studio 2005
• An Introduction to Test-Driven Development (TDD)
• Developing for the Compact Framework with C#
• Custom Attributes in .NET
• Useful Free .NET Tools
• SQL Server Finding and Resolving Performance Problems

All this is FREE and lunch and coffees will also be thrown in.

Sign up now to be guaranteed a place!

Posted: Mar 22 2005, 10:45 AM by Plip | with 2 comment(s)
Filed under: ,
To DbException or to SqlException / OdbcException / OleDbexception / OracleException

ADO.NET 2.0 offers some great new features in the Provider model which I've talked about previously, what someone asked me though sparked some interest.

Frans Bouma stated that unless the providers allowed for generic Exception handling they were next to useless: -

"Every provider has its own exception for reporting errors, and they're not derived from a generic ADO.NET exception.This thus makes it hard to catch db specific exceptions in generic code and handle it. (i.e.: you then have to go back to db specific code, in other words: back to square 1)" 

Looking at the callee graph of System.Data.Common.DbException in ADO.NET 2.0 I can see that it's referenced by the following other classes: -

  • OdbcException : DbException
  • OleDbException : DbException
  • OracleException : DbException
  • SqlException : DbException

This has changed a bit from ADO.NET 1.1 where the above classes inherited directly from SystemException: -

public sealed class SqlException : SystemException

A bit like this: -

Error Classes

Now in ADO.NET 2.0 they inherit from DbException: -

public sealed class SqlException : DbException

DbException inherits from ExternalException: -

public abstract class DbException : ExternalException

Which inherits from SystemException (the class ADO.NET 1.1 uses): -

public class ExternalException : SystemException

And that inherits from Exception: -

public class SystemException : Exception

So what I've done is knock together a quick sample which looks at the DbException class when it is thrown during the normal course of a Sql lookup: -

public void CatchAGenericExceptionInDetail()
    System.Data.Common.DbConnection Conn = CodeSamples.GenericFactoryHelper.Connection;
    Conn.ConnectionString = CodeSamples.GenericFactoryHelper.ConnectionString;
    System.Data.Common.DbCommand Comm = Conn.CreateCommand();
    Comm.CommandText = ("SELECT * FROM NORTHWIND.dbo.TableDoesntExist");
    Comm.CommandType = System.Data.CommandType.Text;

    System.Data.DataTable Dt = new System.Data.DataTable();
    catch (System.Data.Common.DbException Ex)
    catch (System.Exception Ex)
        Assert.Fail("An Exception was thrown with the message '" + Ex.Message + "'");

Now, the "generic" DbException exposes all the information a provider specific exception would (in fact it is a provider specific exception class under the hood) meaning you can handle each provider differently but from a central place in your code, one single catch(DbException Ex): -

Invalid object name 'NORTHWIND.dbo.TableDoesntExist'.
   at System.Data.SqlClient.SqlConnection.OnError(SqlException exception, Boolean breakConnection)
   at System.Data.SqlClient.SqlInternalConnection.OnError(SqlException exception, Boolean breakConnection)
   at System.Data.SqlClient.TdsParser.ThrowExceptionAndWarning(TdsParserStateObject stateObj)
   at System.Data.SqlClient.TdsParser.Run(RunBehavior runBehavior, SqlCommand cmdHandler, SqlDataReader dataStream, BulkCopySimpleResultSet bulkCopyHandler, TdsParserStateObject stateObj)
   at System.Data.SqlClient.SqlDataReader.ConsumeMetaData()
   at System.Data.SqlClient.SqlDataReader.get_MetaData()
   at System.Data.SqlClient.SqlCommand.FinishExecuteReader(SqlDataReader ds, RunBehavior runBehavior, String resetOptionsString)
   at System.Data.SqlClient.SqlCommand.RunExecuteReaderTds(CommandBehavior cmdBehavior, RunBehavior runBehavior, Boolean returnStream, Boolean async)
   at System.Data.SqlClient.SqlCommand.RunExecuteReader(CommandBehavior cmdBehavior, RunBehavior runBehavior, Boolean returnStream, String method, DbAsyncResult result)
   at System.Data.SqlClient.SqlCommand.RunExecuteReader(CommandBehavior cmdBehavior, RunBehavior runBehavior, Boolean returnStream, String method)
   at System.Data.SqlClient.SqlCommand.ExecuteReader(CommandBehavior behavior, String method)
   at System.Data.SqlClient.SqlCommand.ExecuteDbDataReader(CommandBehavior behavior)
   at System.Data.Common.DbCommand.ExecuteReader()
   at CodeSampleTests.Exceptions.CatchAGenericExceptionInDetail()

Is this good enough for a generic factory?

Posted: Mar 22 2005, 12:33 AM by Plip | with 5 comment(s)
Filed under: ,
TomTom GO - Satellite Navigation

I am a useless navigator, I read a sign post, then only after I have gone past a turning do I realise it was talking about the turning I wanted.

There's a place a bit north of me in Scotland that embodies my navigation skills: -

So for a while I wondered, maps, asking directions, or getting a sattelite navigation unit. Well I plumped for the Sattelite navigation route and got myself a TomGom GO.

Lost Sign

The unit works a dream and has taken me on several trips already, it's even got all the UK speed cameras loaded on to it so as I approch a Camera in a 50 zone it squeals "GATZO 50" at me, meaning I get a bit of advanced warning before people in front of me slam on their breaks (because I never speed officer).

I'd 100% reccomend this unit, it's transferrable and battery powered so it can be moved between cars as simply as picking it up and putting it down again. Works throughout Europe and America.

Posted: Mar 21 2005, 10:53 PM by Plip | with 4 comment(s)
Filed under:
.NET CLR Data counters are wrong - offical.

Just found a lovely line in the Change information for .NET 2.0

"Original counters (in V1.0 and V1.1) reported wrong numbers in almost all scenarios."

"We’ve created a new set of Performance Counters in V2.0 that are specific to each provider. We're obsoleting all the counters under the .NET CLR Data category. We have a new set of performance counters which expose one performance object per data access provider.
Original counters (in V1.0 and V1.1) reported wrong numbers in almost all scenarios. The new counters will show accurate numbers. Previously these performance counters never decremented and in order to “reset” the counters, you had to end the process that was doing the work, wait 5-10 minutes, and then a developer could start with a clean set of performance counters. If developers did not do this, the counters just kept increasing for items like open connections. We’re keeping the old performance counters so that we don’t break people that check for them programmatically, however, we will no longer populate them."

Posted: Mar 21 2005, 10:16 PM by Plip | with 62 comment(s)
Filed under: , ,
ADO.NET 2.0 Schema MetaData Information

All the schema information is embedded within the System.Data.dll and System.Data.OracleClient.dll inside these XML files: -


Use reflector to take a peek inside.

Posted: Mar 21 2005, 04:51 PM by Plip | with no comments
Filed under: ,
Do you use SqlCommand.Parameters.Add("@Name","Value"); ?

Well, you're in for a shock, it's been depricated in ADO.NET 2.0.

When you move to 2.0, you should instead be using: -


While the depricated code will still compile it will throw a compiler warning: -

System.Data.SqlClient.SqlParameterCollection.Add(string, object) is obsolete: Add(String parameterName, Object value) has been deprecated.

Use AddWithValue(String parameterName, Object value).

The depricated method will not work in .NET v-Next (2.0 +).

There are some other API changes in the framework, you can see a full list of obsolete and depricated methods for all versions of the framework on the GotDotNet .NET Framework Changes site.

Update: Reason for this change is posted here:

Posted: Mar 21 2005, 04:47 PM by Plip | with 16 comment(s)
Filed under: ,
ADO.NET 2.0 Schema Information

I love it, Schema Metadata (Is it MetaData, Meta data, or just Metadata?) in ADO.NET 2.0 makes the life of a developer so much tidier.

Rather, it lets you make life tidier, but in reality, how useful is it?

There is no denying the elegance of the ADO.NET 2.0 Schema system, it's clean, smooth and increadibly easy to impliment. What I am curious to know however is how often do you as a developer need to programatically look at the structure or properties of a database schema?

The place I see this being the most use is in custom reporting solutions, to allow people to click on the columns they way to report on, from the tables they're allowed to see, but other than that - what use is it?

Plip needs you to tell him how you will use ADO.NET 2.0 Schema MetaData!

Where will you use the Schema MetaData?

Posted: Mar 21 2005, 12:50 AM by Plip | with 6 comment(s)
Filed under: ,
Will you use a Generic Provider Factory?

So you have your SqlClient code, and you have your OracleClient code, crikey, you might even have VB.NET code running against Access (you poor souls), but will you move to a generic model when ADO.NET 2.0 comes around?

Now, it's really easy to do, so there's no excuse, and the code performs comparibly.

Not everyone will switch, I understand this, but I'd like to see the reasons why people are against it, reasons for as I see them (feel free to add to the list with the comments): -

  • One API to standardise all your code on.
  • One API to remember.
  • Ease at which code can be switched between Data Sources.
  • The ability to code without caring what the underlying Data Source is.

What are the reasons against? A few I could think of are here: -

  • Can't be bothered learning a new API.
  • All my code is not Generic now, why should I have two ways of writing and maintaining code?
  • It's imperitive to have the best performance possible at any cost.

I'm writing a couple of chapters for a book (Professional ADO.NET 2: Programming with SQL Server 2005, Oracle, and MySQL) at the moment, and want to know how important Generic Provider Factories are to you, I think they're the future, but perhaps I'm wrong (and let's face it, it would not be the first time!), I'd like to think everyone will switch to them when they're writing ADO.NET 2.0 code, perhaps the Pig is right though ....?


Flying Pig

Posted: Mar 21 2005, 12:40 AM by Plip | with 10 comment(s)
Filed under: ,
My name is Phil, and I am an emailaholic.

I have a bit of a reputation in work, for sending links around. When I find something useful I share it, because that's the kinda guy I am, friendly, caring and open.

Unfortunately, sometimes, I send too many, and now, I'm suffering the wrath of my colleagues, a rebellion has started with me and my link e-mailing ways as the target.

I opened my e-mail today to discover this waiting for me. I am the point of ridicule and alas, there is no rebute from this affliction I suffer.

Emailing Links

Worse than all that, I'm obsessive compulsive, I have a tendancy to reply to e-mails, always, regardless of their source of origin, I have a need, a want, an urge to reply. This has gotten me in to trouble too, and I recieved these embedded in e-mails (which of course I replied to) the other day: -

Last Word Last Word

Can anyone help me? Should I go email cold turkey, I get the shakes if I'm away from my e-mail for long ... what is a web programmer to do?

Posted: Mar 15 2005, 11:19 PM by Plip | with 18 comment(s)
Filed under:
More Posts