Well, after battling it out in my own head, as well as a meeting I called for with my fellow programmers at work, and finally my posts to online message boards, I ended up with a solution to my Menu Permission dilemma. My intension was to have the Server.exe take care of the initial menu security settings and then allow the client to make "manual updates" to it. My problem was that in order for the Server.exe to make the initial menu permissions when creating the database, it needed to know what the "current" menu structure was on the Client.exe. Which meant that either the Server.exe had to somehow "dynamically" retrieve the most current menu structure from some outside source, or it had to be hard coded into the Server.exe itself. Needless to say I was not fond of the latter of those two choices. So I started brainstorming (with help) on to what I could design to accomplish our needs. The final idea was to add another field to a "App-System" table found in the database, which would house the version number of the last Client.exe menu structure to be created/updated in the database. Doing this would allow the Client.exe to check the database during load-up and realize that the menu structure stored on the database "may" be outdated. Knowing that the Client.exe currently loading up is newer then the last menu structure stored, the Client.exe would then go through the process of "updating" the menu structure on the database. When it is done, it stamps its own version number into the table, and goes along its merry way.

Of course any good programmer should foresee that this design could cause problems. Specifically if two (or more) Client.exe's happen (by chance) to load at the same exact time. This would mean that those Client's involved, would all be trying to update the same Table at the same time. So, how can we avoid this potential problem? My first though was to create yet another field in the table, a bit field called IsMenuUpdating. Then when one client begins it's update, all other clients will wait until it is completed. Problem is, if I performed a simple "check if IsMenuUpdating = True, if not, make IsMenuUpdating = True else wait" routine, there is still the chance that two or more clients checked the table at the same instant when the fields value was equal to false. So I am still in the same predicament as before...

Now to see if there is a way to implement Pessimistic Table locking on an SQL Server...

---

If I had more time to properly develop this application that I am working on, I know I would be able to come up with a better structure then I currently devised. But because I am pressed for time, this is the only way that I can think of to perform what I need to do.

I have an SQL database that has a table which holds a map of the menu structure of me Client.exe. The reason I do this is pretty straight forward, every user has permissions to see only certain menu items that they have been granted, those permissions are stored in other tables. The problem is, that in order for an Administrator of the application to assign these permission I need to display a list of all the menu items for the Administrator to either allow or deny the user and/or group. I thought about hardcoding the menu structure into the application, but then that means I would have to remember to change the code whenever I changed the Menu. So, I decided to give the Server.exe the responsibility of making sure that the MenuStructure table in the database was up-to-date. In order to do this though, the Server.exe needed to know what the current MenuStructure was, and that was a problem because it resided on another Executable, the Client.exe. So how do I get one Exe to read the other Exe's data...?

That is the question of the day...and I am currently in the process of finding out. I'll keep you posted.

---

Well, apparently I have been working so much on this project that I am missing some of the most obvious things that would make you laugh. I spent nearly 3 days trying to understand why I was unable to programmatically create a stored procedure (SP) in an SQL database. Well, let me rephrase that, in the "proper" SQL database. I ended up creating the SP in the "master" database. I figured that maybe I needed to send a "USE ;" command as the first statement in the batch I was sending via ADO's ExecuteNonQuery() method. Only to realize that CREATE DATABASE has to be the first statement executed in a batch. Well after racking my brain for days, I realized my problem. I was missing the INITIAL CATALOG in my Connection String. The only reason I didn't supply it before was that immediately before the creation of my SP I created the DB itself, so adding in the catalog name of a non-existing DB would cause it to crash. Such a simple little thing and I slaved over it for three whole days...

posted at 8:48 PM by Tekk Guy | Permalink | 0 Comments

---