WCF and SQL Server 2012

  • [アーティクル]

Welcome to a tutorial on how to connect SQL Server with your WCF service.

PRE REQUISITE : 

1. Visual Studio 2013 (I am using the Ultimate version)

2. SQL Server 2012 Local DB (SQL Server Data Tools comes as a part of SQL 2012 and onwards, which is a VS Shell, which would do all that we need to do using VS.

Lets get started!

PART - I Connecting WCF to SQL Server

1. Open VS 2013 -> file -> new -> project -> Visual C# -> WCF. Name the project as TestService and click 'ok'.

 2.  Add the Database. Right click on the project name in solution explorer -> Add  -> New Item ->Data -> SQL Server Database. Name the database as TestDatabase and click 'Add'.

Note : Click on 'Yes'.

3. Open the Server Explorer. To open Server Explorer, click on 'VIEW' on the top menu bar, find Server Explorer and click on it. It should be the third from the top in the list. Now, in the Server Explorer, right click on the Data Connection and click refresh. Click on TestDatabase . Right click on Tables -> click on Add New Table.

4. Change the name of the table at the bottom from Table to TestTable. Edit the columns and their DataType. In this table I am going to have two columns TestId and TestName of data type Int and varchar respectively. Then, click on "Update" at the top left. Refresh the TestDatabase and Tables, you will find your newly created table listed.

5. Now we have our table created. Lets explore how we can call this table in our service. See the two files IService1.cs and Service1.svc in the Solution Explorer? These are the two files we are going to be editing.

a) The "I" file, which is IService1.cs :  this one contains all the interfaces for the methods that we are going to be using. Describe your ServiceContract, OperationContract and DataContract here.

b) The Service1.svc file : all the interfaces that you describe in IService1.cs file are implemented in this file.

The first thing we are going to do, define two OperationContracts (interfaces) and one DataContract (class for our TestTable).

 6. Open Service1.svc.cs file. Add "using System.Data.SqlClient;". Next write the below code in "public string GetData(TestTable tb)".

The most important point here is to get the correct connection string. To get the connection string, right click on TestTable.mdf in Server Explorer -> click on Properties. From the properties pop-up, copy the connection string.

Another most important point is to edit the connection string in appropriate format. For this make two changes. One, change "\" with "\\", Second, change the " " " with " ' ".

The connection string that you will get will look like :

"Data Source=(LocalDB)\v11.0;AttachDbFilename="C:\Users\sakhare\documents\visual studio 2013\Projects\TestService\TestService\App_Data\TestDatabase.mdf";Integrated Security=True"

Make it look like :

"Data Source=(LocalDB)\\v11.0;AttachDbFilename='C:\\Users\\sakhare\\documents\\visual studio 2013\\Projects\\TestService\\TestService\\App_Data\\TestDatabase.mdf';Integrated Security=True"

 7. Write the code for "public TestTable PutData()"

 8. Press F5. This will run your service. Ideally browser should open up displaying all the files listed. From there click on Services1.svc and open it in new tab. This is the URL we are going to use for referencing this service in our client.

 

PART - II Referencing the Service in Client

1. Open Visual Studio 2013 -> New-> Websites

 2. Open ASP .NET Web Forms Site. Name it as TestPortal and click 'ok'.

 3. To make simpler for us, Right Click on TestPortal -> Add New Item -> Web Form. Name it as "Input.aspx" and click "Add".

 4. One more thing, Right click on TestPortal in Solution Explorer, click on Start Options.. and change the Start page to Input.aspx.

 5. Cool, now use the Toolbox (VIEWS->Toolbox) and design a dashboard. My Dashboard looks very minimalistic, you can make it fancy if you want to!

 6. What we want to do is, on the On_Click event for "Submit" button, we want to call the GetData() method of our service and on the On_Click event of "Fetch from DB" button we want to call the PutData() method of our service and display the appropriate data in the text boxes. to use the methods from our service we will have to reference the service in this project. To do that, Right Click on TestPortal in Solution Explorer -> Add -> Service Reference. A pop will open up. Fill the URL of our service (refer step no. 8 in Part -I) and click 'Go'. Once that URL returns a service click 'Ok'.

7. Great! now put the using statement for your service "using ServiceReference1;". Make an object for your service and call the methods for each button's On_Click Events. Code as follows.

8. Press F5 and run the project. Fill the data and press the buttons.

If you like, you can check the Database for the entry of the data. REMEBER to refresh the Database Connection, TestDatabase.mdf, Tables, TestTable. Incase you are not able to see the updated values in the table keep refreshing your Database. Here we go!

 

I have attached the sample Code for your reference. Hope this helps! :)

Please don't forget to Rate/Comment below!

 

https://1drv.ms/SegIfo

Comments

  • Anonymous
    January 01, 2003
    Thank You Ankit and freetechportal! Let me know if you want me to try out some specific technical topics and blog about them, I will to try to cover them.
  • Anonymous
    May 21, 2014
    Super, Just Awesome :)
  • Anonymous
    May 21, 2014
    Incredible Efforts :) Lots appreciation to u mam :) [sam ] :P
  • Anonymous
    June 19, 2014
    Hello Sam,can u tell me how to add live maps in a windows store app for the locations that we have in our app..?
  • Anonymous
    December 26, 2014
    Can't get the service to accept the connection; it is actively refused; metadata problem?
  • Anonymous
    January 20, 2015
    Hi Ankit
    Awesome Information good work
  • Anonymous
    January 20, 2015
    can u describe more about http://tekslate.com/tutorials/wcf-tutorials/">WCF
  • Anonymous
    May 19, 2015
    Awesome tutorial.
    Thank you for sharing.
  • Anonymous
    June 08, 2015
    Great post. Thanks for that!
  • Anonymous
    June 08, 2015
    Thanks a lot :)
  • Anonymous
    June 21, 2015
    Good blog! For more tutorials and queries on wcf, http://tekslate.com/ > Check this sitefor indepth WCF tutorials.http://tekslate.com/tutorials/wcf-tutorials/ > Go hereif you’re looking for tutorials on WCF.
  • Anonymous
    November 01, 2015
    Thanks, great insight into wcf and sql
  • Anonymous
    December 13, 2015
    Greate ..... Thank you. You made my day!
  • Anonymous
    March 07, 2016
    Thanks, great insight into wcf and sql