I am currently trying to design an interface for a WebService that can access several MySQL databases. There will be 4 operations available, Add(), Read(), Update() and Delete().
The WebService just needs to assemble an SQL statement based on the called method and execute it on the target database. But now I am struggling to find a good method signature that provides all needed information to the WebService and is simple to use. So if I want to assemble an INSERT statement when the Add() method was called, I don't know how I can pass all the column information with the corresponding values and types. For example a .NET DateTime should be stored with the MySQL data type for date/time. So I'd need some kind of wrapper object that check the properties via reflection to get the type.
Here are some thoughts:
Add(String db, String table, Dictionary<String, object> dataSet); Read(String db, String table, String column, filter?); Update(String db, String table, String idToReplace, Dictionary<String, object> dataSet); Delete(String db, String table, String idToDelete);
I am not sure how to specify a filter possibility for the SELECT statement in the Read() method. In addition I don't know how to pass parameter information in the Add() and Update() methods. I don't want to save only string values in the db, I'd like to have passed some type information so that the WebService can match them to the corresponding MySQL data types.