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

Things needed

• 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

 

FINAL (Optional)

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.