There may be an easy solution to this, but at the moment I'm drawing a blank. I've got a table, we'll call it company. In the company table, there is a column for company type called companyType. I have declared this column as a uniqueidentifier(guid) as I plan on using a NameValueList to populate a combobox to allow the user to select a user-friendly value for this field. My problem comes in when I want to check the company type in code. I don't want to have to check for company type equal to some guid. I'd rather be able to do something like "if companyType == "Client".... Also, I'd like to be able to call a stored procedure to get a list of companies by type. Again, I'd like to be able to call this stored proc like this "Exec GetCompaniesByType 'Client' " instead of having to pass in the guid. Any ideas as to how I can accomplish this?
Thanks,
Dustin
Hmm..I'm not sure if that would help me. Let me try to explain this again. I don't necessarily have to use the nvl. I will have this situation in several different places of my app, so I want to get it figured out before I screw them all up. Basically I will have database tables that hold type values. The way I envisioned it, the type values would be uniqueidentifiers(guids), and they would basically just be a foreign key back to another table that listed all the possible types. Here's an example of a record in the company table that describes the scenario:
Name: Test Company
CompanyTypeId: bbd0a1f9-25c6-4266-88c5-235f7d656747
Address: 111 Test Road
....
My problem comes about when I try to check the company type in code. I don't want to do "if myCompany.CompanyTypeId == 'bbd0a1f9-25c6-4266-88c5-235f7d656747' ".... I'd rather do "if myCompany.CompanyType == 'Client' " or even better, although it seems impossible, "if myCompany.CompanyType == ComapanyTypes.Client ".
In other apps that I've been a part of in the past, the companyType field would just store an abbreviation of the name. So company type 'Client' may be stored in the table as 'CL' and may be checked in code as "myCompany.CompanyType = 'CL'. I'd rather not use this solution, as it isn't self explanatory when looking at CL that it stands for Client and not CertificateLiason. Every solution I can think of ends up with me putting hardcoded values in code, either "CL" or "bbd0a1f9-25c6-4266-88c5-235f7d656747", and I'm not a big fan of that.....I could see enums being useful here, except for the fact that values can be added to the table, therefore requiring the enum to be updated each time...Any ideas?
Dustin
Hi Dustin
I've been pondering your issue today. I have similar issues in my system and the way I resolve it is it to have a static class with constants for the values of the different company types.
It seems that since you want to be able to refer to the company type in code, then they must be already known at compile time which means you are able to declare constants or an enum for them. Having them in a table probably doesn't make much sense - it would seem that users can't add to the list at runtime (since then you wouldn't be able to refer to them without changing your code).
If you're worried about the codes not being self-explanatory then why not just use the full name - e.g. "Client" for the Company Type - it's not like you're going to waste any more space than a GUID would have taken up - and if you want you can put a check constraint on the table to prevent any inappropriate values going into that column. As for documentation, if you have a dedicated class with the constants in it, then this serves this purpose well.
The only issue comes if you need a combo box with all the possible company types in it - for this I tend to code up a small class similar to a Name Value List with your Name and Code as properties. You can fairly easily generalise this. Have a BindingList of these (I guess since they're known at compile time, the logical place for this BindingList would be as a static property on the class that contains your constants) and bind your combo box to it - the display property will be the name and the value property will be the code. Then it will all work nicely doing databinding to your BO.
HTH
Craig
Thank you all for the feedback. I've thought about this a little over the holiday and here's what I've come up with (By the way, this may not make much sense since most of my holiday was spent lounging on the couch watching all the bowl games ![Smile [:)]](/emoticons/emotion-1.gif)
). I'm thinking about taking the posted suggestions and "merging" them into my solution. Basically I will use an id field, probably a guid, to denote the type id in the main table. I will then have a lookup table that holds this id and a user friendly description of what it stands for. On top of that I will have a static class with properties that hold the id values for the different types. The final solution may look something like this:
Sample Company Table Record:
CompanyName: Company1
CompanyTypeId: bbd0a1f9-25c6-4266-88c5-235f7d656747
Sample Lookup Table Record:
LookupId: bbd0a1f9-25c6-4266-88c5-235f7d656747
LookupValue: Client
Then in the code I will have a static class that has a property called ClientId that holds this guid Id value. This will allow me to check the company type like this:
//myCompany is an instance of the company object
if (myCompany.CompanyTypeId == CompanyTypes.ClientId)......
The guid Id values will be loaded into this CompanyTypes class via a query of the lookup table. Does this make any sense?
Dustin
Copyright (c) Marimer LLC