Simple Application Using WCF and Database

In this article I'm going to explain how we can write a WCF service and invoke it on to the client side.

I am making a simple application where a WCF service returns the information about leaders which are stored in database.

Note: In this demonstration I am only using default settings of WCF , many other important aspects of WCF like instance management, concurrency management, transaction etc… that are used extensively in real live projects will be demonstrated in subsequent articles . Here we will see how we can write a very basic WCF service and invoke it.

We first need to create a table in database, enter some records and create a stored procedure .Below is the code.

USE [blog]



/****** Object: Table [dbo].[blog_Leaders] Script Date: 01/28/2012 11:28:28 ******/







CREATE TABLE [dbo].[blog_Leaders](

    [Name] [nvarchar](50) NOT NULL,

    [Country] [nvarchar](50) NOT NULL,

    [Pic] [nvarchar](max) NULL,

    [ID] [int] IDENTITY(1,1) NOT NULL,



    [ID] ASC






Below is the data which I have inserted in my table "blog_Leaders".


Insert blog_Leaders

Select 'ManMohan Singh',    'India',    'Manmohansingh04052007.jpg'    


Select 'Barak Obama','US','barack-obama111.png'


Select 'Winston Churchill','UK','Winston-Churchill.jpg'


Stored Procedure is as follows:


Create PROCEDURE [dbo].[usp_selectLeader]


@Name nvarchar(100),

@Id int=0,

@msg varchar(100) output







select * from blog_Leaders where name like @Name+'%';


return 0

END try

begin catch

select @msg='Error!!!Problem Occured During Process'

return 1

end catch

Second, we need to select WCF service from templates as shown in below picture.

Once we are done selecting the WCF service we will be provided with two files in App_Code directory .

  1. IService.cs
  2. Service.cs


    Iservice.cs file is where we are going to define our interface and methods which they contain and Service.cs is where we are going to implement our methods which are in our interface.


We will first start with defining our interface, so open your IService.cs file. In this file you will find an interface IService and a default method Void DoWork(). We will define our own method, in my case I have my own method named as GetLeadersInfo() whose return type is DataTable and passing a parameter LeadersName.


Code for IService.cs


using System;

using System.Collections.Generic;

using System.Linq;

using System.Runtime.Serialization;

using System.ServiceModel;

using System.Text;

using System.Data;

// NOTE: If you change the interface name "IService" here, you must also update the reference to "IService" in Web.config.


public interface IService



    void DoWork();




DataTable GetLeadersInfo(string LeaderName);



As you can see our interface is decorated with Service contract attribute.Now service contract defines which operations the client can perform on service and the operation contract is decorated on the method inside the interface.


After defining our method GetLeadersInfo in the IService interface, we will now implement our method GetLeadersInfo.In order to implement our interface we need to open Service.cs file, once you open the file you will be provided with DoWork() method by default but we have to implement our own method which is GetLeadersInfo().


Code for Service.cs


using System;

using System.Collections.Generic;

using System.Linq;

using System.Runtime.Serialization;

using System.ServiceModel;

using System.Text;

using System.Data.SqlClient;

using System.Data;

using System.Configuration;

// NOTE: If you change the class name "Service" here, you must also update the reference to "Service" in Web.config.

public class Service : IService


    public void DoWork()




public DataTable GetLeadersInfo(string name)


string constr = ConfigurationManager.ConnectionStrings["blogDevtechie"].ToString();

SqlConnection con = new SqlConnection();

con.ConnectionString = constr;



SqlCommand cmd = new SqlCommand("usp_selectLeader", con);

cmd.CommandType = CommandType.StoredProcedure;

cmd.Parameters.Add("@Name", SqlDbType.VarChar).Value = name;


SqlParameter parm2 = new SqlParameter("@returnval", SqlDbType.Int);

parm2.Size = 50;

parm2.Direction = ParameterDirection.ReturnValue;



SqlParameter parm1 = new SqlParameter("@msg", SqlDbType.VarChar);

parm1.Size = 50;

parm1.Direction = ParameterDirection.Output;



SqlDataAdapter adpt = new SqlDataAdapter(cmd);

DataTable dt = new DataTable("Leaders");


return dt;






After completing and saving the above work we need to add this service reference.

Below is the screen shot how you can add the service reference.


Once you have clicked Add Service Reference another window (Add Service Reference) will appear as below. Now here you can see your methods which have been implemented in the interface Iservice in our case method name is GetLeadersInfo().

You can also see the namespace which is named as "ServiceReference1", now this service reference will be used when we will be invoking this service at the client side. Although you can change the name of the namespace to a suitable name of your choice, but here in this example I am leaving it to default name which is ServiceReference1.


Below is the screen shot:



Now we will program our Client Side. In our case client is simple

In client side we have simply taken a textbox and a button and on buttons click event I have invoked the WCF service which is filling my details view.

In this service you will have to supply the name of leader in the textbox and the service will return the required details of that leader in details view.

You will also have to add image folder inside your application directory to keep the images.


Below is the screen shot.

Now as you will write the name of the leader and hit the show button the service will return the required information.



Clients code is as follows:


using System;

using System.Collections.Generic;

using System.Linq;

using System.Web;

using System.Web.UI;

using System.Web.UI.WebControls;

using System.Data.SqlClient;

using System.Data;

using System.Configuration;

public partial class _Default : System.Web.UI.Page


protected void Page_Load(object sender, EventArgs e)




protected void Button1_Click(object sender, EventArgs e)


ServiceReference1.IService Clientproxy = new ServiceReference1.ServiceClient();

DataTable dt = new DataTable();

dt = Clientproxy.GetLeadersInfo(TextBox1.Text);

DetailsView1.DataSource = dt;





Aspx code is as below:


<%@ Page Language="C#" AutoEventWireup="true" CodeFile="Default.aspx.cs" Inherits="_Default" %>


<!DOCTYPE html PUBLIC "-//W3C//DTD XHTML 1.0 Transitional//EN" "">


<html xmlns="">

<head runat="server">




<form id="form1" runat="server">




<td colspan="4" style="text-align:center;"><h1>DevTechie</h1></td>



<td> </td>


<asp:Label ID="lblnameLeader" runat="server" Text="Enter the name of Leader"></asp:Label>



<asp:TextBox ID="TextBox1" runat="server"></asp:TextBox>



<asp:Button ID="Button1" runat="server" Text="Show" onclick="Button1_Click" />





<td colspan="4">

<asp:DetailsView ID="DetailsView1" runat="server" Height="50px" Width="125px"



<asp:BoundField HeaderText="Name" DataField="Name" />

<asp:BoundField HeaderText="Id" DataField="ID" />

<asp:BoundField HeaderText="Country" DataField="Country" />

<asp:TemplateField HeaderText="Picture">



<asp:Image ID="Image1" runat="server" ImageUrl='<%# "~/images" + "/" +Eval("Pic", "{0}") %>' Height="200px" Width="200px" />
















Once we have completed the above work your service is ready to run.

Finally when you would open web.config file you will come across endpoints, I will explain the endpoints as its very basic and important to WCF.

When we talk about WCF we come across ABC

A-Address-is where you service is kept.

B-Binding-Which binding is used.

C-Contract-which interface is part of contract or which interface is exposed to client.


Now have a look at the Client Endpoints in config file

You will find


Address- http://localhost:59824/programs-application/Service.svc

Where our service is kept.


Binding-"wsHttpBinding"—we have used wsHttpBinding binding



Contract- ServiceReference1.IService

Where IService is the interface and ServiceReference1 is the namespace.


Similarly, if you see service endpoint you will find similar ABC.





<endpoint address="http://localhost:59824/programs-application/Service.svc"

binding="wsHttpBinding" bindingConfiguration="WSHttpBinding_IService"

contract="ServiceReference1.IService" name="WSHttpBinding_IService">


<dns value="localhost" />






                <behavior name="ServiceBehavior">

                    <serviceMetadata httpGetEnabled="true"/>

                    <serviceDebug includeExceptionDetailInFaults="false"/>





<service behaviorConfiguration="ServiceBehavior" name="Service">

<endpoint address="" binding="wsHttpBinding" contract="IService">


<dns value="localhost"/>



<endpoint address="mex"binding="mexHttpBinding"contract="IMetadataExchange"/>