In my last post on this subject, I suggested that deploying MySQL ODBC drivers on client PC was not a good design. I spoke about Service Oriented Architecture or more specifically about Rest Services. There will be so many things to write about SOA and Rest Services but it's not the subject. I will just technically describe how I created a Rest service that returns records from my MySql Database.
First of all a small architecture picture:
In my sample, a Web Site on IIS is exposing REST Services over HTTP. These services expose data from my database called "bCatalog" and specifically from my table tProducts. The return data are xml formatted, more specifically in an atom format. Data may be accessed through specific urls such as:
- "http://localhost:2522/ProductService.svc/tproducts: get all records from the table "tProducts". I highlighted the important tags in resulting xml document:
- "http://localhost:2522/ProductService.svc/tproducts('Box01WithLabel')": get the specific product from de table "tProducts" where "objectID" (the primary key) is equal to "Box01WithLabel".
In the solution you can download (at the end of the post), you will see I used two Microsoft technologies: ADO.NET Data Services to create Rest Services and ADO.NET Entity Framework to request data from my sql database. Moreover, this time I used the managed MySql driver you can download here.
The next step was to update my Nova Script in VB.NET to request data on http. So I just proceeded as follow:
- I replaced my database Connection String by a simple URL to request all data from "tProducts" table.
-
I completely rewrote the existing "LoadObjectsFromDatabase" method to take into account an http query. Technically, the .NET Framework offers all you need to make http requests and to interpret resulting XML data. The class to use to request is "HttpWebRequest" and the class to use to manipulate XML is "XmlDocument":
The harder task was to manipulate the atom XML Document. I lost a couple of minutes thinking why my xpath request (to get all entry tags, i.e. database records) didn't return anything. In fact, it was quite simple! Indeed, in atom format, there are default namespaces that must prefix all tags. The rest of the job was just finding nodes, route nodes and gatting and storage of values (database fields).
So, with this solution, my database is no more directly exposed to the web and especially I have not to deploy MySql ODBC drivers on each client. In the following zip, you will find:
- the mxb file I used,
- the corresponding nss file,
- the nsr file that contains the previous scripting in case,
- MySql database scripting.
- the web exposing Rest Services (you will need Visual Studio 2008 SP1 to load the project)
VerticeSample-Last.zip (66.48 kb)
Enjoy!
Let me know if you are interested with the code to load data only on user interaction (with cache store) by leaving me a comment for this post. Moreover if you have any questions about SOA, please contact me.