in Search

Do you Twitter? Follow us @CodeBetter

Karl Seguin

developer @ Fuel Industries ottawa, ontario

Database Impedance Mismatch

I've always been pretty vocal about my anti-dataset views. I also don't think databases should be treated as dumb repositories - they should be leveraged. This in-between attitude, which I think is actually fairly common, means I end up writing a lot more code than either dataset users or OO purists.

Extra code isn't really a problem - I can always codegen it or write it in my sleep. I think the biggest problem with being an in-the-middle-kinda-coder is that the impedance mismatch is very present for even the most trivial task. Take for example the very typical problem I'm currently having: returning values from a stored procedure and mapping them to an enum.

I have a Friends list, and calling the sproc AddFriend might return a InvalidUserName, AlreadyAFriend or BeingIgnored status. Sure, I could raise exceptions (assuming the DB supports something like that), but I don't consider those to be exception worthy. I have an enum defined in my C# code which I'd like to map these values to, but how?

I can pass each status into my sproc as a variable, something like:

command.Parameters.Add("@InvalidFriendNameStatus", SqlDbType.Int).Value = AddFriendStatus.InvalidFriendName;

I could hard-code the values in my sproc, or use a table to replicate my enum. I could even make the entire thing DB driven, and on app startup, load fake enum values from status tables into specialized classes...I never know what's the right way to do it. I'd actually consider using SQL Server's CLR if it was an option..

I don't think impedance mismatch is actually less of a problem if you are more data-centric or more object centric. In my experience you'll run into less small problems, but more serious ones.

help...

Published Dec 20 2006, 10:19 AM by karl

Filed under: Short Outbursts

Comments

CoderJeff said:

Why not use a table to store your enums... (enum, value, name)...

And then have a code generator write the code for the enums into a single code file. If the data changes the name of an enum you've used in code, the compiler will catch it and you can do a quick rename.

Give the sp's a naming scheme that will identify the return type for you: sp_ProcessStuff_enumProcessFlag... then have a code generator write the call to the sp's with the function return type being set appropriately. The function name on the code side would be code genned to drop the prefix and the suffix... changing it to this:

ProcessFlag ProcessStuff(params...){

}

# December 20, 2006 11:19 AM

Mr_Database said:

Glad to hear you dont have a problem writing a little extra database code - it's simple code and you have full control.

I prefer to keep abstraction to a minimum. For example, I dont use ORM tools where I have to learn yet another data manipulation language - what's wrong with TSQL?

Simple code is simple to write, simple to trace, simple to debug, and simple to fix. It's also boring which is why a lot of programmers hate writing it. Me? I don't mind. I get paid to solve problems not agonize over which OMR tool to use on the current project.

---

And now my answer to your question:

I would just pass in the enum values to the sproc and be done with it. It's simple and maintainable.

# December 20, 2006 11:23 AM

Derick Bailey said:

I do this exact thing in my projects on almost a daily basis.

I find that it's easy to assign the Parameter.Value to the Enum.Value like you show in your sample code, and then on the way back out, we case from an Int being returned by the database to the Enum in question.

in C#, you are allowed to cast from an int to an Enum that's values are of type int, natively:

MyEnum var = (MyEnum)dataReader.GetInt32(myColumnIndex);

this works for us... may not be an "elegant" solution, but it lets us keep our enums in code and have a database column to represent the value.

# December 20, 2006 1:12 PM

Wyatt Barnett said:

Rather than using the Int value of the enum, I just use string values for enumed fields. Far and away easier to read in the DB, etc.

# December 20, 2006 2:24 PM

Jeremy D. Miller said:

This kind mechanical scut coding is exactly why you would want to pull an ORM off the shelf. And I'm with Wyatt, the int's might be faster, but the enum string values are better from the maintenance standpoint.

# December 20, 2006 2:41 PM

jonas said:

ever thought about getting rid of enums and instead use domain objects to represent your types?

public class InvalidFriend: FriendStatus

{

//... Code Here...

}

# December 20, 2006 11:56 PM

karl said:

Jonas: Yes, but unless I'm missing something it doesn't solve the issue.

# December 21, 2006 8:46 AM

Stephen Phillips said:

I'm a big fan of having an external lookup table which contains the integers and names that are mirrored in your C# enumeration. This improves integrity of your database, and allows you to see the enumerated named values if you're doing DB maintenance or even lightweight reporting.

# December 29, 2006 11:06 AM

Name (required)

Your URL (optional)

Comments (required)

Remember Me?