Design question [modified]
-
Hi all, I'm very new to designing and working with DB based apps so please forgive me if I write down things that can make seasoned developer's cry in aguish. We are right now developping a Remote application scenario with a client app and a server app. The server app will be connected to a DB, SQL Server for more info, which is not, theoretically, reachable from any other machine in the intranet or internet. Only access is through the server app. Ok the thing is we want the developers of our client app and the client app itself,which is basically 99% of the project workload, to "know as little as possible" of the DB behind the whole project and make all query and update to the DB as simple as possible (through the server app of course). The design we (I) came up with is the following: (Shuts his eyes and prays he hasnt blown all major rules of designing applications and if so please forgive me) The server app will have a main worker class named DbEngine which basically does all the work with a few helper classes. The method used to execute selection queries and bring back info to the client app would be the following: byte[] ExecuteCommand(string commandName, Dictionary<string,object> parameters). byte[] is a serialized datatable, compressed and most likely, allthough not yet encrypted. The only thing the client application needs to know is what commandname to call and what parameterName and value to send if any. Please not that the parameters Dictionary does not store any Parameter class, its simply a string key with the parameter name (@columnname) and its value. Its pretty lightweight and easy to code. The whole work is done in the server side. The thing is, we are storing a table in the SQL Server Database, which we would call TBL_SYS_COMMANDS with all the SQL/Stored Procedure commands our app is going to use. The info stored in this table would be: [CommmandName] (Primary Unique Key) [CommandText] (this would be a stored procedure name or a SQL command) [CommmandType] (Text, StoredProcedure, etc. Server app would parse the string into the corresponding .NET enum) [Parameters] (would contain custom serialization of parameter(s) info, except Value of course). The server app would recieve the ExecuteCommand, look up the commandname in the cached TBL_SYS_COMMANDS datatable and if found, build up command and the parameters through reflection, assign their values, execute the command and send back info compressed in a byte array. Security measures taken: The DbEngine cla