WCF Example for Inserting and Displaying Data from SQL Server Database Using WCF Service in ASP.NET

Sep 28 2012 5:55 AM

WCF Example for Inserting and Displaying Data from SQL Server Database Using WCF Service in ASP.NET

Introduction

In this article I will show you practical example of WCF service for inserting data into database using ASP.NET.

Using the Code

For inserting data into database by using WCF service in asp.net, we have to do the following steps:

Create WCF service
Create Web based application

Part 1: Create a WCF Service

Open Visual Studio 2010
New WCF Service Application
Give the name for service Customer Service
Press ok

After that new project is created CustomerService project

Then you will get 3 files

IService.cs
Service.svc
Service.svc.cs

IService.cs Page

Collapse | Copy Code

//
using System;
using System.Collections.Generic;
using System.Linq;
using System.Runtime.Serialization;
using System.ServiceModel;
using System.ServiceModel.Web;
using System.Text;
using System.Collections.Generic;
using System.Runtime.Serialization;
using System.ServiceModel;


[ServiceContract]
public interface IService
{

    [OperationContract]
    List<CustomerDetails> GetCustomerDetails(string CutomerName);

    [OperationContract]
    string InsertCustomerDetails(CustomerDetails customerInfo);
}

 [DataContract]
public class CustomerDetails
{
    string CutomerName = string.Empty;
    string firstname = string.Empty;
    string lastname = string.Empty;
    string address = string.Empty;

    [DataMember]
    public string CutomerName
    {
        get { return CutomerName; }
        set { CutomerName = value; }
    }
    [DataMember]
    public string FirstName
    {
        get { return firstname; }
        set { firstname = value; }
    }
    [DataMember]
    public string LastName
    {
        get { return lastname; }
        set { lastname = value; }
    }
    [DataMember]
    public string Address
    {
        get { return address; }
        set { address = value; }
    }
}

And write the following code in Service.cs file:

Service.cs Page

Collapse | Copy Code

using System;
using System.Collections.Generic;
using System.Linq;
using System.Runtime.Serialization;
using System.ServiceModel;
using System.ServiceModel.Web;
using System.Text;
using System.Collections.Generic;
using System.Configuration;
using System.Data;
using System.Data.SqlClient;


public class Service : IService
{
    SqlConnection con = new SqlConnection("Data Source=Sujeet;Initial Catalog=Register;User ID=sa;Password=123");

    public List<CustomerDetails> GetCustomerDetails(string CutomerName)
    {
        List<CustomerDetails> CustomerDetails = new List<CustomerDetails>();
        {
            con.Open();
            SqlCommand cmd = new SqlCommand("select * from CustomerInfo where CutomerName Like '%'+@Name+'%'", con);
            cmd.Parameters.AddWithValue("@Name", CutomerName);
            SqlDataAdapter da = new SqlDataAdapter(cmd);
            DataTable dt = new DataTable();
            da.Fill(dt);
            if (dt.Rows.Count > 0)
            {
                for (int i = 0; i < dt.Rows.Count; i++)
                {
                    CustomerDetails customerInfo = new CustomerDetails();
                    customerInfo.CutomerName = dt.Rows[i]["CutomerName"].ToString();
                    customerInfo.FirstName = dt.Rows[i]["FirstName"].ToString();
                    customerInfo.LastName = dt.Rows[i]["LastName"].ToString();
                    customerInfo.Address = dt.Rows[i]["Address"].ToString();
                    CustomerDetails.Add(customerInfo);
                }
            }
            con.Close();
        }
        return CustomerDetails;
    }

    public string InsertCustomerDetails(CustomerDetails customerInfo)
    {
        string strMessage = string.Empty;
        con.Open();
        SqlCommand cmd = new SqlCommand("insert into CustomerInfo(CutomerName,FirstName,LastName,Address) values(@Name,@FName,@LName,@Address)", con);
        cmd.Parameters.AddWithValue("@Name", customerInfo.CutomerName);
        cmd.Parameters.AddWithValue("@FName", customerInfo.FirstName);
        cmd.Parameters.AddWithValue("@LName", customerInfo.LastName);
        cmd.Parameters.AddWithValue("@Address", customerInfo.Address);
        int result = cmd.ExecuteNonQuery();
        if (result == 1)
        {
            strMessage = customerInfo.CutomerName + " inserted successfully";
        }
        else
        {
            strMessage = customerInfo.CutomerName + " not inserted successfully";
        }
        con.Close();
        return strMessage;
    }
}

Build your service successful first then Run your service in your browser then you will get one URL link like below copy that URL:

In this way your WCF service builds successfully.

Part 2: Create a Web Based Application (Client)

Now create your Client Application in your system:

Create one Website
Add Service Reference to Web Application.
Select Your Website
. Right click on it à Add Service Reference à then Enter your Service URL and Click Go
Give name for your service -> OK
Then automatically Proxy will Create in your Client System.
Write the following code in your source code:

Source Code

Collapse | Copy Code

<html xmlns="http://www.w3.org/1999/xhtml">
<head runat="server">
    <title></title>
</head>
<body>
    <form id="form1" runat="server">
    <div>
   
        <h2  >
            <strong>Cutomer  Form</strong></h2>

   
    </div>
    <table align="center" class="style1">
        <tr>
            <td>
                CutomerName</td>
            <td>
                <asp:TextBox ID="txtCutomerName" runat="server"></asp:TextBox>
                <asp:RequiredFieldValidator ID="RequiredFieldValidator1" runat="server"
                    ControlToValidate="txtCutomerName" ToolTip="CutomerName Required"><imgsrc="delete.png" /></asp:RequiredFieldValidator>

            </td>
        </tr>
        <tr>
            <td>
                First Name</td>
            <td>
                <asp:TextBox ID="txtfname" runat="server"></asp:TextBox>
                <asp:RequiredFieldValidator ID="RequiredFieldValidator2" runat="server"
                    ControlToValidate="txtfname" ToolTip="Firstname Required"><imgsrc="delete.png" /></asp:RequiredFieldValidator>

            </td>
        </tr>
        <tr>
            <td>
                Last Name</td>
            <td>
                <asp:TextBox ID="txtlname" runat="server"></asp:TextBox>
                <asp:RequiredFieldValidator ID="RequiredFieldValidator3" runat="server"
                    ControlToValidate="txtlname" ToolTip="Lastname Required"><imgsrc="delete.png" /></asp:RequiredFieldValidator>

            </td>
        </tr>
        <tr>
            <td>
                Address</td>
            <td>
                <asp:TextBox ID="txtAddress" runat="server"></asp:TextBox>
                <asp:RequiredFieldValidator ID="RequiredFieldValidator4" runat="server"
                    ControlToValidate="txtAddress" ToolTip="Address Required"><imgsrc="delete.png" /></asp:RequiredFieldValidator>

            </td>
        </tr>
        <tr>
            <td>
                 </td>
            <td>
                <asp:Button ID="btnSubmit" runat="server" Text="Submit"
                    onclick="btnSubmit_Click" />
            </td>
        </tr>

    </table>
    <table align="center" class="style3">
        <tr>
            <td>
                <asp:Label ID="lblResult" runat="server"/>
                <br />
                <br />
                <asp:GridView ID="GridView1" runat="server"BackColor="LightGoldenrodYellow"
                    BorderColor="Tan" BorderWidth="1px" CellPadding="2"ForeColor="Black"
                    GridLines="None" style="text-align: left" Width="304px">
                    <AlternatingRowStyle BackColor="PaleGoldenrod" />

                    <FooterStyle BackColor="Tan" />
                    <HeaderStyle BackColor="Tan" Font-Bold="True" />
                    <PagerStyle BackColor="PaleGoldenrod" ForeColor="DarkSlateBlue"
                        HorizontalAlign="Center" />
                    <SelectedRowStyle BackColor="DarkSlateBlue" ForeColor="GhostWhite"/>
                    <SortedAscendingCellStyle BackColor="#FAFAE7" />
                    <SortedAscendingHeaderStyle BackColor="#DAC09E" />
                    <SortedDescendingCellStyle BackColor="#E1DB9C" />
                    <SortedDescendingHeaderStyle BackColor="#C2A47B" />
                </asp:GridView>

            </td>
        </tr>
    </table>
    </form>
    </body>
</html>

Add your service reference on the top
Collapse | Copy Code
```
using ServiceReference1;
```
Then create one Object for Service Reference and use that object to call methods from your service.
Write the following code in your aspx.cs file

Default.aspx.cs page

Collapse | Copy Code

using System;
using System.Collections.Generic;
using System.Linq;
using System.Web;
using System.Web.UI;
using System.Web.UI.WebControls;
using System.Collections.Generic;
using ServiceReference1;

public partial class _Default : System.Web.UI.Page
{
    ServiceReference1.ServiceClient objService = new ServiceReference1.ServiceClient();

    protected void Page_Load(object sender, EventArgs e)
    {
        if (!IsPostBack)
        {
            BindUserDetails();
        }
    }

    protected void BindUserDetails()
    {
        IList<CustomerDetails> objUserDetails = new List<CustomerDetails>();
        objUserDetails = objService.GetCustomerDetails("");

        GridView1.DataSource = objUserDetails;
        GridView1.DataBind();
    }

    protected void btnSubmit_Click(object sender, EventArgs e)
    {
        CustomerDetails customerInfo = new CustomerDetails();
        customerInfo.CutomerName = txtCutomerName.Text;
        customerInfo.FirstName = txtfname.Text;
        customerInfo.LastName = txtlname.Text;
        customerInfo.Address = txtlocation.Text;
        string result = objService.InsertCustomerDetails(customerInfo);
        lblResult.Text = result;
        BindUserDetails();
        txtCutomerName.Text = string.Empty;
        txtfname.Text = string.Empty;
        txtlname.Text = string.Empty;
        txtAddress.Text = string.Empty;
    }
}

By using this you have successfully inserted data in database and you also shown this in grid view.

Happy Programming!

Thanks

Sujeet

Answers (1)

WCF packet sniffer?

Why page is refreshing on onclientClick of asp button?