Monday, August 17, 2015

Display records in Gridview from sql server database using WCF service

In this article I am going to explain how to Display records in Gridview from sql server database using WCF service in asp.net

Description:

I have created a table Movie and it has the records. 

To display the records from sql server database table we have firstly create a WCF service and website (to consume the WCF service).

Implementation:
Create store procedure to get the data from table
Create Proc Sp_GetMovieData
as
begin
Select * from Movie
End

Create a WCF service application
First of all open the web.config file and set the ConnectionString.
<connectionStrings>
  <add name="Connection" connectionString="Data Source=VIJAY-PC;Initial Catalog=Demo;Integrated Security=True"/>
</connectionStrings>

Now open IService.cs file.

Add the namespace
using System.Data;

Write the code below [serviceContarct]
[OperationContract]
    DataSet GetMovieData();

Now move to Service.cs/Service.svc.cs file.

Add the namespace
using System.Data;
using System.Data.SqlClient;
using System.Configuration;

Now write the method to get the records from database
public DataSet GetMovieData()
    {
        SqlConnection con = new SqlConnection(ConfigurationManager.ConnectionStrings["Connection"].ToString());
        SqlDataAdapter adp = new SqlDataAdapter("Sp_GetMovieData", con);
        adp.SelectCommand.CommandType = CommandType.StoredProcedure;
        DataSet ds = new DataSet();
        adp.Fill(ds);
        return ds;
    }

It is done. Now build and run the project. Keeps it running to consume.

Now we have to consume the WCF service. To consume the WCF service creates a new website.
Now go to solution explore of website and right click on it >> Add>> click on Service reference.

Display records in Gridview from sql server database using WCF service

Add service reference window will be open and paste the URL of running WCF service. After the paste the click on Go button and later on OK button.

Display records in Gridview from sql server database using WCF service

Now add a webform to website. Drag and drop the Gridview data control toolbox to webform.

HTML Mark up of Gridview:
<asp:GridView ID="grdmoviedetail" runat="server" AutoGenerateColumns="False" CellPadding="4" ForeColor="#333333" GridLines="None">
                <AlternatingRowStyle BackColor="White" ForeColor="#284775" />
                <Columns>
                    <asp:BoundField DataField="Name" HeaderText="Movie Name" >
                     <ItemStyle HorizontalAlign="Center" />
                    </asp:BoundField>
                     <asp:BoundField DataField="Genre" HeaderText="Genre"> <ItemStyle HorizontalAlign="Center" />
                    </asp:BoundField>
                     <asp:BoundField DataField="Cost" HeaderText="Budget (In Crore)"> <ItemStyle HorizontalAlign="Center" />
                    </asp:BoundField>
                     <asp:ImageField DataImageUrlField="Poster" HeaderText="Poster" ControlStyle-Width="200px">
<ControlStyle Width="200px"></ControlStyle>
                    </asp:ImageField>
                </Columns>
                <EditRowStyle BackColor="#999999" />
                <FooterStyle BackColor="#5D7B9D" Font-Bold="True" ForeColor="White" />
                <HeaderStyle BackColor="#5D7B9D" Font-Bold="True" ForeColor="White" />
                <PagerStyle BackColor="#284775" ForeColor="White" HorizontalAlign="Center" />
                <RowStyle BackColor="#F7F6F3" ForeColor="#333333" />
                <SelectedRowStyle BackColor="#E2DED6" Font-Bold="True" ForeColor="#333333" />
                <SortedAscendingCellStyle BackColor="#E9E7E2" />
                <SortedAscendingHeaderStyle BackColor="#506C8C" />
                <SortedDescendingCellStyle BackColor="#FFFDF8" />
                <SortedDescendingHeaderStyle BackColor="#6F8DAE" />
            </asp:GridView>

Add the namespace
using ServiceReference;
using System.Data;

Create the object of service reference and write the below given code to bind the gridview.

ServiceReference.ServiceClient objclient = new ServiceReference.ServiceClient();
    protected void Page_Load(object sender, EventArgs e)
    {
        if(!IsPostBack)
        {
            BindGridview();
        }
    }
public void BindGridview()
    {
        DataSet ds = new DataSet();
        ds = objclient.GetMovieData();
        grdmoviedetail.DataSource = ds;
        grdmoviedetail.DataBind();
    }

Build the project and run. See the result.
Result:
Display records in Gridview from sql server database using WCF service


Note: if you made any changes in WCF service application in that case you have to update the service reference. To update the service reference right click on App_Webreferences folder and click on update web/service references.

  In this article we have learn to how to bind the gridview using WCF service in asp.netI hope you enjoyed this article. You can like me on Facebook, Google+, Linkedin and Twitter via hit on Follow us Button and also can get update follow by Email. 

No comments:

Post a Comment