McNelis.biz

I’ve been doing work in the software development industry for a number of years now, always on an open source platform, with open source IDEs, sometimes proprietary databases. The bulk of my work used either PHP or Java and either MySQL or Oracle. Now, as I’m preparing to do a little side work with someone with a lot of .Net and C# experience, I decided I might as well take the plunge and check out what he keeps tauting.

So I went and bought my first PC in over 5 years, a slick little Lenovo T60p, which I must say I like a whole lot. Installed Vista on it, along with MS Visual Studio, MS SQL, and a host of other apps that will hopefully be of great benefit to me.

I did have some issues to start with. First off, I’ve never done anything in C# / .Net before so I really don’t know what the project structure should end up looking like. I also don’t know much about configuring IIS. Give me Apache and I can make it do all sorts of fun things, IIS, just don’t know much about it yet. My biggest hurdle, to start, was installing MS SQL Server Express.

I had a downloaded version that I tried, on multiple occasions, to install, but nothing worked. Eventually I turned to MSDN, where, at least at one point, I was able to find an article detailing how to get SQL Express running in Vista. I am having trouble finding the article that helped me, but there is a different article here. The first article I read had me download MS SQL Server Express SP1 and install it. Then run it as administrator and add my user account as a user in the security settings.

That was my first major hurdle to climb. Once I was able to start creating a database things got much smoother. There were some data types that I was not completely familiar with, but was able to figure out what I needed for my schema. (I should note that the end goal of all of this is to get SideMonkey.com up and running).

One thing that did take me a while was getting the primary key columns set up so that they would autoincrement. I know how to do this in Oracle using sequences and triggers, but though I could create a trigger in MS SQL, I saw nothing of sequences. I set out looking for information only about adding an “identity” tag to my primary key fields. I found information on how to do it from scratch with the SQL “create table” scripts…but damn it, I’m in a nice gui for DB design for the first time in my life, I should be able to modify the table through the GUI to have an autoincrementing id field.

Finally, after much frustration I did find a way to do this at the bottom of the window (right click your table and click modify). In the bottom pain there is an “Identity” heading that you can expand. Once I expanded that heading I could turn identity on for the column I wanted, specify the start number and the increment to increase the identity. Success! Now I had my initial schema created with Primary Keys, Indexes, and “identity” created.

Next was foreign keys. Foreign keys aren’t overly complex in an of themselves. All they really function to do (in what I work on) is ensure data integrity. I’m not going to go into all of the possible functionality associated with foreign keys here either, except to say that Cascade was my friend in this initial set up. What it meant was that if I changed a column definition in one table, the change was propogated whereever that column was linked to in another table through foreign keys.

I was a little confused at first with the gui means of setting up a foreign key, but after a little bit of experimenting realized that, when adding a foreign key, the left side of the dialog box is the where you want to select the table and column that you are externally linking to. The right side of the dialog is to select the column in your current table that is associated with the foreign table. Sounds confusing enough right? Try it and it will make a lot more sense.

I was also surprised at the simplicity of using stored procedures, creating them as well, compared to its contemporaries, particularly Oracle, which is where the bulk of my stored procedure experience lies. Defining the input and output parameters were easy, though the syntax a little different from what I was used to, as were constructing the internals of the procedures. The only ones I’ve done so far are for inserting or updating records in a couple of tables, which required a could of if statements, and it will be interesting to see how it goes when my procedures get more complex….and they will….very much so I think.

Anyway, my final thoughts on working with MS SQL is that some parts of using it are just simple. Simple in a way that you don’t expect when working with an enterprise DB solution (read: Oracle)…but maybe I just never had the gui’s that made life so simple in the other products I’ve tried. It remains to be seen how complex it can get, and I’ll be sure to report how all that goes, but so far so good. Tonight I plan to add some more to the db (to allow user registration on the site), and maybe start coding my first DAOs in C# / .Net using an MS SQL backend.