Exposing a SQL DB through a WCF Service using Entity Framework
Its been a long time since I have written a blog. I had to implement a SQL database for an application and we needed to have exposed as a WCF endpoint. While there are several tutorial on how to do this, i thought it will be a good idea to show the simple and the quick way that we followed
- SQL Server
- Microsoft Visual Studio 2012
This are two parts, creating the service and then the Client DLL.
Part 1 : Creating the WCF Service
Step 1: Create the Database
Create a SQL database, its tables, its relationships as you normally would.If there are any tables with binary columns, then please be informed that it should be excluded from the WCF service.
Step 2: Creating the EDMX file
Create a new ASP.NET Empty Web Application in a new solution, call it what you like, I am it the TestDataModel
Once this is done, its time to add the EDMX file. In order to do so Right click and add new item.
Choose ADO.NET Entity Data model and give it the name TestDataModel.edmx
Select Generate from Database
Fill in the DB details
You can change the name of the entities, we are leaving it as it is
Choose the tables you want to expose using the WCF
You can please note the Model Namespace, or change it. Once you finish the step will be done
Step 2: Add a Nuget Package.
In order to create the WCF, just add the entity framework nuget package. Right click on references, and Click on manage Nuget Packages
Download and install the WCF Data Services Server
This will install all the OData Components needs by our project
Step 3: Create the Data Service
Right Click the Project Name –> Add –> WCF Data Service
Give it a name – I am calling it AzMgmtDataService (as my database is called AZMGMT)
This will create a file and open it
There are 2 main changes that we will do
1. Put the Name of the data source class – in this case azmgmtEntities and setting the rights – I am going to give rights to every one for the sake of simplicity.
Notice the two lines that are written. First one gives the rights to all users, off course you can fine tune this line. The second line is optional, i leave it on for testing so that I can check the entity framework errors from the client. In production environment, please set the line to false
That’s it, the WCF service is ready, you can deploy it in a manner you choose fit. Run a debug and it should open a browser.
It may show a 404 not found or a forbidden page, please ensure the service name (WCF service name is provided in the URI)
In my case, I added /AzmgmtDataService.svc
This shows that the WCF service is working. You can use Linqpad to test it.
Part 2: Creating the Client DLL
This is an optional step, but I recommend it as it makes the client applications easy to use.
We will use a visual studio utility (DataSvcUtil.exe)to generate a client class
Open a Visual Studio command prompt and execute
DataSvcUtil.exe /out:ClientClass.cs /uri:”http://localhost:57125/AzMgmtDataService.svc”
You should get a clientclass.cs file created
The File should look like this
Add a new project to the solution of the type Class Library. Call it AzmgmtDataModelClient
Copy paste the contents to the Class1.cs – Rename the class1.cs as AzmgmtDataModelClient.cs
Add the references
Build the Client Project. You will have got the DLL file to use it in your applications
The below shows how to use the client in other applications. So we create a test console application to do this
Add a new project of the type Console application. Add the references (All the microsoft Odata references that we added in the client class and additionally the client dll)
We will add two lines of code
Please remember that your entities name, URL and the object name will be different. In my case, I have a table called BusinessUnit in the DB that I want to use.I can use a LINQ query in order to filter my results
Setting up multiple projects for debug: Please remember that the Web Project needs to run so that the client DLL can access it. so, we need to run both projects simultaneously, so, right click on the solution and select properties
In my case, i want to debug only my test application. The client generation project doesnt need to be debugged at all.
Click on the debug button and you will see a webpage and a console open. Since I have set a breakpoint in my console application its waiting for me
As you can see the application is working as intended.
I hope this has been the easiest way to expose a database using the WCF service. If you have any questions do let me know.