Gotcha with OleDbConnection and SQL Server 2000 databases

I was bug hunting today and ran across some weird behaviour with OleDbConnection. Before we go on, I’d like to point out that the code I was looking at was NOT mine (Who’d connect with an OleDbConnection, when there is a perfectly good SqlConnection?). Anyway, the thing I noticed was that when you have an OleDbConnection object connecting to a SQL Server 2000 database, with the Password parameter set to something. After a call to Open(), the connection string doesn’t have the Password parameter in it anymore.

Here’s some code I wrote to confirm it:

class Class1
{
  [STAThread]
  static void Main(string[] args)
  {
    OleDbConnection connection = new OleDbConnection(
"Provider=SQLOLEDB.1;Persist Security Info=False;Password=password;"+
"User ID=user;Initial Catalog=catalog;Data Source=COMPUTERNAME;" );
    Console.WriteLine("Before {0}",connection.ConnectionString );
    connection.Open();
    Console.WriteLine("After {0}",connection.ConnectionString);
    Console.ReadLine();
  }
}

And the output:

Before: Provider=SQLOLEDB.1;Persist Security Info=False;Password=password;User ID=user
;Initial Catalog=catalog;Data Source=COMPUTERNAME;
After: Provider=SQLOLEDB.1;Persist Security Info=False;User ID=user;
Initial Catalog=catalog;Data Source=COMPUTERNAME;

The names were changed to protect the innocent. To try it out, change Data Source to a real machine name, User ID to a real user id and Initial Catalog to a real db.

Why? Well, I can only speculate, but I’m guessing that it has to do with security, so no one can steal the information once the application has connected to the db. Could be the same for SqlConnection too. Anyone wanna try it out?

Auto increment in MS Access

This took me longer to find on Google than I wanted. If you want to have an auto-incrementing primary key in MS Access (sucks), then you want something like the following in the your create table statement:

CREATE TABLE tablename (
  id COUNTER NOT NULL CONSTRAINT constraintName_pk PRIMARY KEY,
  intColumn INTEGER,
  description MEMO
)

The primary key is ‘id’ and it’s of type COUNTER. Consider the above opposed to the SQL Server 2000 CREATE statement:

CREATE TABLE tablename (
  id int identity (1, 1) NOT NULL PRIMARY KEY,
  intColumn int,
  description varchar(2048)
)

Hopefully, with SQL Server 2005 Express coming out, MS Access (sucks) might go the way of the dodo in 10 years.

 

Code Snippets in Visual Studio C# Express

I know I said earlier that I would talk about the features in the upcoming Visual Studio 2005, but a certain something has occupied my time for the last few months. Right before I left for Brazil I downloaded some of the express editions that microsoft posted. I’ve only installed the C# edition, so far. At first I was skeptical about these express editions, but it seems to be pretty good so far.

I started playing with the new framework and the IDE (can we call it that, still?). Right now, it’s just the equivalent of doodling, but I do have some project ideas. My framework play has so far only been with System.IO. There are some nifty improvements in that namespace, and they’re not even that flashy. I wanted to look at what some of the new properties spit out, so I whipped up a quick Console App. One of the things I type a lot when debugging(yeah, yeah unit tests, I know) is

Console.WriteLine(”Some string {0}”, someobject.Property);

My fingers can fly across the keyboard when I type that. Sometimes they fly too fast and I start off with “COnsole,” and then intellisense doesn’t show up, etc. Is there a way to reduce typing? With VS 2005, there is! Yay! The folks at MS have setup a lot of the “code-based RAD” features as code snippets: refactoring, expansions, surround with.

Code snippets are XML files that contain templates for the particular expansion you want. For instance, if you type foreach and then press Tab the expansion will show up like so:

So I tried my hand at creating a new one for Console.WriteLine(). If you click Tools > Code Snippet Manager, you get an almost useless dialog that shows you where the expansions can be found. It installs two directories by default:

  • [Program Files]\Visual Studio 8\VC#\Expansions\1033\Expansions
  • [My Documents]\Visual Studio\Whidbey\CodeSnippets\Code Snippets\VC#\My Code Snippets

Something like those anyway. The first is where the default ones are kept. The second is a path that isn’t created by VS, you have to do it. Once you create it, though you can dump your expansions in there, and the IDE should find them. The reason they don’t create it has something to do with this being beta stuff.

To create mine, I just copied one at random from the first path into the second path, renamed it and edited the xml. This is what I got:

<CodeSnippet Format="1.0.0">
  <Header>
    <Title>Console.WriteLine</Title>
    <Shortcut>writeline</Shortcut>
    <Description>Expansion for Console.WriteLine</Description>
    <SnippetTypes>
      <SnippetType>Expansion</SnippetType>
      <SnippetType>SurroundsWith</SnippetType>
    </SnippetTypes>
  </Header>
  <Snippet>
    <Code Language="csharp" Format="CData">Console.WriteLine("{0}", $selected$ );
        $end$
    </Code>
  </Snippet>
</CodeSnippet>

As you can see, it’s a very simple expansion. The real work is in Snippet tag, I think. (I did this without google or documentation, so I can only guess at meanings and that’s bad.) There you can see the code that I put in. The ‘$selected$’ placeholder is for any selected text, so you can surround a selection with this particular code. I’m not sure what ‘$end$’ does, but I speculate it’s telling the IDE that it’s reached the end of the CDATA section. One thing I found is that $selected$ has to be separated from the rest of text by whitespace: “$selected$)” would fail; but “$selected$ )” wouldn’t.

Easy, eh? Now in the IDE, I can type writeline (note the Shortcut tag above), hit Tab and out pops Console.WriteLine(). Very cool and very powerful. I have a feeling I’ll be using this a lot. I also have a hunch that templates for whole files are based on this (I’m pretty sure I read that somewhere). I’ll look into that and maybe come back with something more formal and more detailed. Here’s a link to the xml file for Console.WriteLine().