ADO.NET Gotcha - Remember System.Data

Why isn't this working?

by William Ryan

Recently, I was coding something using ADO.NET and my IDE appeared to be acting really wierd.  All of a sudden my intellisense stopped working on a few items like ConnectionState and SqlDbType.

Here's the code that wasn't working (although the exact same code seemed to work everywhere else):

try {      if(cn.State != ConnectionState.Open){cn.Open();}//This caused      //The type or namespace name 'ConnectionState' could not be found      //(are you missing a using directive or an assembly reference?) } catch(SqlException ex){ Debug.Assert(false, ex.ToString());} cmd.CommandType = CommandType.StoredProcedure; //This line caused this exception: //C:\PhoneManager\InfoProMessage.cs(145): The type or namespace name 'CommandType' //could not be found (are you missing a using directive or an assembly reference?)

And if this wasn't enough, I got even more...

cmd.Parameters.Add("@Department", SqlDbType.VarChar, 50).Value = this.Company; cmd.Parameters.Add("@Contact", SqlDbType.VarChar, 50).Value = this.From; cmd.Parameters.Add("@MessageTaker", SqlDbType.VarChar, 50).Value = Environment.UserName; cmd.Parameters.Add("@Regarding", SqlDbType.VarChar, 50).Value = this.Regarding; cmd.Parameters.Add("@Message", SqlDbType.VarChar, 8000).Value = this.Message; //The above lines all caused similar exceptions... //C:\PhoneManager\InfoProMessage.cs(149): The type or namespace name 'SqlDbType' //could not be found (are you missing a using directive or an assembly reference?)

The first thing I thought might be doing it was forgetting the using directive (not to be confused with the using statement).  However, I looked at the top and there was System.Data.SqlClient clear as day.  So what could it be?

Well, I went Google and looked up both SqlDbType and ConnectionState enum and quickly figured it out.  SqlDbType is a member of the System.Data namespace, not SqlClient!  That seemed a bit wierd to me, but it's nonetheless true.  The same thing holds true for ConnectionState.  That was also a bit surprising, after all, I figured it would be a property of a SqlConnection or OledbConnection object.  But if you check the members of either of them, there's no ConnectionState.  It's an enumeration shared by both SqlClient and OleDbClient (and OracleClient if you are under the 1.1 framework).

Anyway, it was really simple to fix and most of the time, I usually include System.Data before I even include SqlClient or OleDbClient so that's why it normally works but didn't this time (Next I'll work on fixing the run-on sentence or comma splice).  After trying to reference both of the with SqlClient/SqlConnection and every other possible method, I went ahead and read the instructions and that fixed everything. Hopefully you won't do the same.