WCF-Service Contract and Database Binding

Service Contract with database connectivityc

Create table and insert data

NULLS ON

GO

SET QUOTED_IDENTIFIER ON

GO

SET ANSI_PADDING ON

GO

CREATE TABLE [dbo].[tblprice1](

      [PriceID] [int] NULL,

      [FundID] [varchar](50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,

      [Pricedate] [datetime] NULL

) ON [PRIMARY]

GO

SET ANSI_PADDING OFF

 

insert into tblprice values(1,'FD001','01/01/2012')

insert into tblprice values(2,'FD001','03/11/2012')

insert into tblprice values(3,'FD001','03/26/2012')

insert into tblprice values(4,'FD001','04/01/2012')

insert into tblprice values(5,'FD002','01/01/2012')

insert into tblprice values(6,'FD002','03/23/2012')

insert into tblprice values(7,'FD002','03/12/2012')

insert into tblprice values(8,'FD002','04/10/2012')

insert into tblprice values(9,'FD003','01/04/2012')

insert into tblprice values(10,'FD003','03/17/2012')

insert into tblprice values(11,'FD003','03/22/2012')

insert into tblprice values(12,'FD003','04/11/2012')

Class  :  FundpriceClass.cs

namespace ServiceContractSR

{

    public class FundPriceClass

    {

        int _PriceID;

        string _FundId;

        DateTime _PriceDate;

        public int PriceID

        {

            get { return _PriceID; }

            set { _PriceID = value; }

        }

        public string FundId

        {

            get { return _FundId; }

            set { _FundId = value; }

        }

        public DateTime PriceDate

        {

            get { return _PriceDate; }

            set { _PriceDate = value; }

        }

    }

}

 Service Interface IService1.cs

In this service Interface we are declaring  two methods one is Getdata() which return Generic List with type STRING  and menthod implimentation return only one coloum (PriceID) and other is GetFundDataByClass which accept one argument priceID and return Generic List with FundpriceClass which return multiple coloums according to PriceID

namespace ServiceContractSR

{

   [ServiceContract]

    public interface IService1

    {

        [OperationContract]

        List<string> GetData();

        [OperationContract]

        List<FundPriceClass> GetFundDataByClass(int PriceID);

 

    }

  }

Service Interface IService2.cs

In this service Interface we are declaring method FundPriceByClass which accept one argument FundID and return Generic List.

namespace ServiceContractSR

{

    [ServiceContract]

    public interface IService2

    {                 

        [OperationContract]

        List<FundPriceClass> GetAll_FundPriceByClass(string FundID);

       

    }

}

Service Interface IService2_1.cs

In this service Interface we are declaring method FundPriceByClassDate which accept one argument Pricedate and return Generic List with class .

namespace ServiceContractSR

{

    [ServiceContract]

    public interface IService2_1

    {

        [OperationContract]

        List<FundPriceClass> GetAll_FundPriceByClassDate(DateTime Picedate);

    }

 }

Service1.svc

using System.Collections.Generic;

using System.Runtime.Serialization;

using System.ServiceModel;

using System.ServiceModel.Web;

using System.Data.SqlClient;

using System.Data; 

namespace ServiceContractSR

{

    public class Service1 : IService1

    {

        SqlConnection Conn;

        public Service1()

        {

            Conn = new SqlConnection("Connection String");

        }

        public List<string> GetData()

        {

            List<string> lstFunds = new List<string>();

            Conn.Open();

            SqlCommand Cmd = new SqlCommand("Select top 5 PriceID from tblprice order by pricedate desc", Conn);

            SqlDataReader Reader = Cmd.ExecuteReader();

            while (Reader.Read())

            {

                lstFunds.Add(Reader["PriceID"].ToString());

            }

            Reader.Close();

            Conn.Close();

            return lstFunds;

        }

        public List<FundPriceClass> GetFundDataByClass(int PriceID)

        {

            Conn.Open();

            SqlCommand Cmd = new SqlCommand("Select top 10 PriceID,FundID,Pricedate from tblprice where PriceID=" + PriceID + " ", Conn);

            SqlDataReader dr = Cmd.ExecuteReader();

            FundPriceClass dc = new FundPriceClass();

            while (dr.Read())

            {

                dc.PriceID = Convert.ToInt32(dr["PriceID"].ToString());

                dc.FundId = dr["FundID"].ToString();

                dc.PriceDate = Convert.ToDateTime(dr["Pricedate"].ToString());

            }

            dr.Close();

            Conn.Close();

            List<FundPriceClass> list = new List<FundPriceClass>();

            list.Add(dc);

            return list;

        }

    }

}

 Service2.svc

In this service Class service2 Implement two Interfaces Iservice2 and Iservice2_1 In other words we can say that service is implementing multiple service contracts. In this service method FundPriceByClassDate throwing fault contract as well to show proper error message on client side.

namespace ServiceContractSR

{ 

    public class Service2 : IService2 , IService2_1

    {

         SqlConnection Conn;

         public Service2()

        {

            Conn = new SqlConnection("Connection String");

        }

        public List<FundPriceClass> GetAll_FundPriceByClass(string FundID)

         {

            List<FundPriceClass> fundPriceClass1 = new List<FundPriceClass>();

            Conn.Open();

            SqlCommand Cmd = new SqlCommand("Select top 5 PriceID,FundID,Pricedate from tblprice where FundID='" + FundID + "' ", Conn);

            SqlDataReader dr = Cmd.ExecuteReader();

            FundPriceClass dc = new FundPriceClass();

               while (dr.Read())

                {

                    dc.PriceID = Convert.ToInt32(dr["PriceID"].ToString());

                    dc.FundId = dr["FundID"].ToString();

                    dc.PriceDate = Convert.ToDateTime(dr["Pricedate"].ToString());

                    fundPriceClass1.Add(dc);

                }       

            dr.Close();

            Conn.Close();

            return fundPriceClass1;

        }

        public List<FundPriceClass> GetAll_FundPriceByClassDate(DateTime Picedate)

        {

            List<FundPriceClass> fundPriceClass1 = new List<FundPriceClass>();

            try

            {

                Conn.Open();

                SqlCommand Cmd = new SqlCommand("Select top 5 PriceID,FundID,Pricedate  from tblprice where Pricedate<'" + Picedate + "' ", Conn);

                SqlDataReader dr = Cmd.ExecuteReader();

                FundPriceClass dc = new FundPriceClass();

                 while (dr.Read())

                {

                    dc.PriceID = Convert.ToInt32(dr["PriceID"].ToString());

                    dc.FundId = dr["FundID"].ToString();

                    dc.PriceDate = Convert.ToDateTime(dr["Pricedate"].ToString());

                    fundPriceClass1.Add(dc);

                }

                dr.Close();

                Conn.Close();            }

            catch

            {

              //Fault contract sent by service in case error raise

             throw new FaultException("There was a problem reading from the database.",new FaultCode("DataReaderFault"));

             }

            return fundPriceClass1;

        }

    }

}

End point configuration in WEBCONFIG file

<system.serviceModel>

   

    <behaviors>     

        <endpointBehaviors>

              <behavior name="Endpointbehavior_1">

                <enableWebScript/>

              </behavior>             

        </endpointBehaviors>

           

        <serviceBehaviors>

       

            <behavior name="Serbehavior1">

              <serviceMetadata httpGetEnabled="true" />

              <serviceDebug includeExceptionDetailInFaults="false" />

            </behavior>

       

            <behavior name="">

              <serviceMetadata httpGetEnabled="true" />

              <serviceDebug includeExceptionDetailInFaults="false" />

            </behavior>

       

        </serviceBehaviors>

    </behaviors>

   

    <serviceHostingEnvironment multipleSiteBindingsEnabled="true" />

       

     <services>

              <service name="ServiceContractSR.Service1"  behaviorConfiguration="Serbehavior1">

     

                      <endpoint name="Binding_1"    address=""     binding="basicHttpBinding"     contract="ServiceContractSR.IService1"  />

                      <endpoint name="Binding_1_1"    address="/GetFundDataByClass"     binding="basicHttpBinding"     contract="ServiceContractSR.IService1"  />

   

                     <host>

                        <baseAddresses>

                           <add baseAddress="http://localhost:61615/Service1.svc" />     

                        </baseAddresses>

                    </host>

              </service >

     

            <service name="ServiceContractSR.Service2" >

                      <endpoint name="Binding_2"    address="/GetAll_FundPriceByClass"     binding="basicHttpBinding"     contract="ServiceContractSR.IService2"  />

                      <endpoint name="Binding_2_1"  address="/GetAll_FundPriceByClassDate"     binding="basicHttpBinding"  contract="ServiceContractSR.IService2_1"  />

       

                    <host>

                      <baseAddresses>          

                           <add baseAddress="http://localhost:61615/Service2.svc" />

                      </baseAddresses>

                    </host>

            </service>

       

    </services>

  </system.serviceModel>

TEST Services 

eg : Test Service1.svc

Step 1. Right click on service1.svc and choose view in browser it will show following screen. 

Step 2.  Open Visual studio Command prompt and type WCFTSTSCLIENT. it will open WCF Test client  screen.Choose file menu and click on add service

it will open add service prompt where you need to enter URL for service which you can get in step1.click ok then it will show following screen with  service contract and methods.

Now you need to double click on method name after that click on invoke button after that result will shown below.

 

Client WEB   

 Add Service reference : 

Right click on client web project choose add service reference

Click on discover button in above screen then two services will appear on services left side part. next select one by one both services to add reference.

Note : We can host our service on IIS or self host or in window services also. 

 

default.aspx Page

HTML Page

  <div style="text-align:center">

    <h2>

        Welcome to ASP.NET!

    </h2>

    <br />

   

    Enter PriceID

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

   

    &nbsp;&nbsp;

    <asp:Button ID="Search" runat="server" onclick="Search_Click" Text="Search" />

   

    <br />

&nbsp;&nbsp;

    <br />

    <br />

    <asp:DataGrid ID="DGFund" HorizontalAlign="Center"    runat="server" BackColor="LightGoldenrodYellow"

        BorderColor="Tan" BorderWidth="1px" CellPadding="2" ForeColor="Black"

        GridLines="None" Width="430px" Caption="Grid with Binding1">

        <AlternatingItemStyle BackColor="PaleGoldenrod" />

        <FooterStyle BackColor="Tan" />

        <HeaderStyle BackColor="Tan" Font-Bold="True" />

        <PagerStyle BackColor="PaleGoldenrod" ForeColor="DarkSlateBlue"

            HorizontalAlign="Center" />

        <SelectedItemStyle BackColor="DarkSlateBlue" ForeColor="GhostWhite" />

   

    </asp:DataGrid> 

    <br />

    <asp:DataGrid ID="DGFund0" HorizontalAlign="Center"    runat="server" BackColor="White"

        BorderColor="White" BorderWidth="2px" CellPadding="3"

        GridLines="None" Width="430px" BorderStyle="Ridge"

        Caption="Grid with Binding1_1" CellSpacing="1">

        <FooterStyle BackColor="#C6C3C6" ForeColor="Black" />

        <HeaderStyle BackColor="#4A3C8C" Font-Bold="True" ForeColor="#E7E7FF" />

        <ItemStyle BackColor="#DEDFDE" ForeColor="Black" />

        <PagerStyle BackColor="#C6C3C6" ForeColor="Black"

            HorizontalAlign="Right" />

        <SelectedItemStyle BackColor="#9471DE" ForeColor="White" Font-Bold="True" />

   

    </asp:DataGrid> 

    <br />

    <asp:DataGrid ID="DGFundBinding2" HorizontalAlign="Center"    runat="server" BackColor="White"

        BorderColor="#CC9966" BorderWidth="1px" CellPadding="4" Width="430px" BorderStyle="None"

        Caption="Grid with Binding2_1">

        <FooterStyle BackColor="#FFFFCC" ForeColor="#330099" />

        <HeaderStyle BackColor="#990000" Font-Bold="True" ForeColor="#FFFFCC" />

        <ItemStyle BackColor="White" ForeColor="#330099" />

        <PagerStyle BackColor="#FFFFCC" ForeColor="#330099"

            HorizontalAlign="Center" />

        <SelectedItemStyle BackColor="#FFCC66" ForeColor="#663399" Font-Bold="True" />

   

    </asp:DataGrid> 

    <br />

    <asp:DataGrid ID="DgFundService2Second" HorizontalAlign="Center"   

        runat="server" BackColor="White"

        BorderColor="#E7E7FF" BorderWidth="1px" CellPadding="3" Width="430px" BorderStyle="None"

        Caption="Grid with Second Contract (Service2)" GridLines="Horizontal">

        <AlternatingItemStyle BackColor="#F7F7F7" />

        <FooterStyle BackColor="#B5C7DE" ForeColor="#4A3C8C" />

        <HeaderStyle BackColor="#4A3C8C" Font-Bold="True" ForeColor="#F7F7F7" />

        <ItemStyle BackColor="#E7E7FF" ForeColor="#4A3C8C" />

        <PagerStyle BackColor="#E7E7FF" ForeColor="#4A3C8C"

            HorizontalAlign="Right" Mode="NumericPages" />

        <SelectedItemStyle BackColor="#738A9C" ForeColor="#F7F7F7" Font-Bold="True" />

   

    </asp:DataGrid> 

    <br />

    </div>

Design Page

default.aspx.cs

protected void Page_Load(object sender, EventArgs e)

        {

            if (Page.IsPostBack == false)

            {

                ServiceReference1.Service1Client ObjSer = new ServiceReference1.Service1Client("Binding_1");

                DGFund.DataSource = ObjSer.GetData();

                DGFund.DataBind();

                ServiceReference1.Service1Client ObjSer1 = new ServiceReference1.Service1Client("Binding_1_1");

                DGFund0.DataSource = ObjSer1.GetFundDataByClass(55779);

                DGFund0.DataBind();

                ServiceReference2.Service2Client ObjSer2 = new ServiceReference2.Service2Client("Binding_2");

                DGFundBinding2.DataSource = ObjSer2.GetAll_FundPriceByClass("FD001");

                DGFundBinding2.DataBind();

                ServiceReference2.Service2_1Client ObjSer2_1 = new ServiceReference2.Service2_1Client("Binding_2_1");

                try

                {

                    DgFundService2Second.DataSource = ObjSer2_1.GetAll_FundPriceByClassDate(Convert.ToDateTime("03/23/2012"));

                    DgFundService2Second.DataBind();

                }

                catch (FaultException faultEx)

                {

                    switch (faultEx.Code.Name)

                    {

                        case "ConnectionFault":

                            Response.Write(faultEx.Message + "Connection problem");

                            break;

                        case "DataReaderFault":

                            Response.Write(faultEx.Message + "Datareader problem");

                            break;

                        default:

                            Response.Write(faultEx.Message + "general Exception");

                            break;

                    }

                }

            }

         }

        protected void Search_Click(object sender, EventArgs e)

        {

            ServiceReference1.Service1Client ObjSer = new ServiceReference1.Service1Client("Binding_1");

            DGFund.DataSource = ObjSer.GetFundDataByClass(Convert.ToInt32(TextBox1.Text));

            DGFund.DataBind();

        }

Run Project :