In this article i'm going to explain how to insert delete edit and update records to SQL Server database using WCF.

About WCF:

Windows Communication Foundation (WCF) is an extension of the .NET framework to build and run connected systems. Windows Communication provides a unified framework for building secure and reliable transacted Web services. Windows Communication Foundation  combines and extends the capabilities of Distributed Systems, Microsoft .NET Remoting, Web Services, and Web Services Enhancements (WSE), to develop and deliver unified secured systems.

Table Design:

Column Name

Data Type

empid

varchar(50)

name

varchar(100)

designation

varchar(50)

city

varchar(50)

country

varchar(50)

 

Create table script:

CREATE TABLE [dbo].[EmployeeDetails](

          [empid] [varchar](50) NULL,

          [name] [varchar](100) NULL,

          [designation] [varchar](100) NULL,

          [city] [varchar](50) NULL,

          [country] [varchar](50) NULL

) ON [PRIMARY]      

Step-1:

          Open visual studio and start new Project

     
Step-2:
           Select WCF Service Application

                
Step-3:
           Now click on the Ok . Then you will get 3 files in your project folder
  1. IService.cs
  2. Service.svc
  3. Service.svc.cs


here you should know about following Contracts

Service Contract:

              Service contract is a contract that specifies the direction and type of the messages in a conversation. It is an interface or a class that defines the service contract in a Windows  Communication Foundation (WCF)  application. A service contract is the gateway to a service for external applications to make use of the service   functions, and at least one service contract should be available in a service.

Operation Contract:

              An operation contract defines the methods of the service that are accessible by external systems. The OperationContract attribute needs to be applied for all these methods, these are  also like web methods in a  web service.

Data Contract:

             A data contract defines a type with a set of data members or fields that will be used as the composite   type in a service contract. It is a loosely-coupled model that is defined outside the implementation of the  service and accessible by services in other platforms. To define a  data contract, apply the DataContract  attribute to the class to serialize the class by a serializer,  and apply the DataMember attribute to the fields in   the class that must be serialized.

Data Member:

            A data member specifies the type which is part of a data contract used as a composite type member of   the contract. To define a data member, apply the DataMember attribute to the fields that must be serialized.   The DataMember attribute can be applied to private properties, but they will be serialized and deserialized,  and  will be accessible to the user or process. 


Step-4:
          Add following code to IService.cs 

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.Data;

[ServiceContract]

public interface IService

{

    [OperationContract]

    string InsertEmloyeeDetails(Employee_Details objempdetails);

    [OperationContract]

    DataSet BindEmployeeDetails();

    [OperationContract]  

bool DeleteEmployeeDetails(Employee_Details objempdetails);

    [OperationContract]

    void UpdateEmployeeDetails(Employee_Details objempdetails);

}

[DataContract]

public class Employee_Details

{

    private string _empid, _name, _designation, _city, _country;

    [DataMember]

    public string empid

    {

        get { return _empid; }

        set { _empid = value; }

    }

    [DataMember]

    public string name

    {

        get { return _name; }

        set { _name = value; }

    }

    [DataMember]

    public string designation

    {

      get { return _designation; }

        set { _designation = value; }

    }

    [DataMember]

    public string city

    {

        get { return _city; }

        set {_city = value; }

    }

    [DataMember]

    public string country

    {

        get { return _country; }

        set { _country = value; }

    }

}

Step-5:
          Add following code to Service.svc.cs 

 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.Data.SqlClient;

using System.Data; 

public class Service : IService

{

    SqlConnection conn = new SqlConnection("Data Source=SPIDER;Initial Catalog=Demo;Integrated Security=True");

    public string InsertEmloyeeDetails(Employee_Details objempdetails)

    {

        string MessageOutput;       

        conn.Open();

        string cmdstr="insert into EmployeeDetails(empid,name,designation,city,country) values(@empid,@name,@designation,@city,@country)";

        SqlCommand cmd = new SqlCommand(cmdstr, conn);

        cmd.Parameters.AddWithValue("@empid", objempdetails.empid);

        cmd.Parameters.AddWithValue("@name", objempdetails.name);

        cmd.Parameters.AddWithValue("@designation", objempdetails.designation);

        cmd.Parameters.AddWithValue("@city", objempdetails.city);

        cmd.Parameters.AddWithValue("@country", objempdetails.country);

        int result = cmd.ExecuteNonQuery();

        if (result == 1)

        {

            MessageOutput = "Records Inserted";

        }

        else

        {

            MessageOutput = " Records not Inserted";

        }

        conn.Close();

        return MessageOutput;

    } 

    public DataSet BindEmployeeDetails()

    {

        conn.Open();

        string cmdstr = "Select * from EmployeeDetails";

        SqlCommand cmd = new SqlCommand(cmdstr, conn);

        SqlDataAdapter adp = new SqlDataAdapter(cmd);

        DataSet ds = new DataSet();

        adp.Fill(ds);

        conn.Close();

        return ds;

    } 

    public bool DeleteEmployeeDetails(Employee_Details objempdetails)

    {

        conn.Open();

        string cmdstr="delete from EmployeeDetails where empid=@empid";

        SqlCommand cmd = new SqlCommand(cmdstr, conn);

        cmd.Parameters.AddWithValue("@empid", objempdetails.empid);

        cmd.ExecuteNonQuery();

        conn.Close();

        return true;

    } 

    public void UpdateEmployeeDetails(Employee_Details objempdetails)

    {

        conn.Open();

        string cmdstr = "update EmployeeDetails set name=@name,designation=@designation,city=@city,country=@country where empid=@empid";

        SqlCommand cmd = new SqlCommand(cmdstr, conn);

        cmd.Parameters.AddWithValue("@empid", objempdetails.empid);

        cmd.Parameters.AddWithValue("@name", objempdetails.name);

        cmd.Parameters.AddWithValue("@designation", objempdetails.designation);

        cmd.Parameters.AddWithValue("@city", objempdetails.city);

        cmd.Parameters.AddWithValue("@country", objempdetails.country);

        cmd.ExecuteNonQuery();

        conn.Close();

    }

} 

  Step-6:


          Press F5 to run the application. Now WCF Test Client will be displayed and it will be
 load your service.
Step-7:
          Now you just double click the function which is you have created. There you will be
 able to see the  function's details which is include Name, Value and Type 
    Now your service will be created successfully. Now you can open your service in the
 browser by right click to view in browser option in the Service1.svc file. 
    
Step-8:
           Now you have to create new website by right click to the solution file 
Step-9:
          Now you just add new App_WebReferences folder by right click your website 
Step-10:
          Now we have to add service reference by right click the App_WebReferences folder. 
Step-11:
      Now you just click Discover button, it will be find service reference. here you can
 change the "namespace"  name 
Step-12:
      Now your service reference will be added successfully 
    After created your service reference, the end point will be created automatically in your
 web config. 
Step-13:
      After created the service reference if you did any changes to the service then you should
 update the service  reference
Step-14:
      Now you can create your aspx page      

Designer Source Code:

Ex: Default.aspx
 

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

<!DOCTYPE html PUBLIC "-//W3C//DTD

XHTML 1.0 Transitional//EN" "http://www.w3.org/TR/xhtml1/DTD/xhtml1-transitional.dtd">

<html xmlns="http://www.w3.org/1999/xhtml">

<head runat="server">

    <title></title>

</head>

<body>

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

    <div>

        <table width="800px" align="center">

            <tr>

                <td colspan="2" align="center"><b>Employee Details</b></td>

            </tr>         

            <tr>

            <td colspan="2">

            <asp:GridView ID="gvEmployeeDetails" runat="server" Width="100%"

                    AutoGenerateColumns="false" ShowFooter="true"

                    onrowcommand="gvEmployeeDetails_RowCommand"

                    onrowdeleting="gvEmployeeDetails_RowDeleting"

                    onrowupdating="gvEmployeeDetails_RowUpdating"

                    onrowcancelingedit="gvEmployeeDetails_RowCancelingEdit"

                    onrowediting="gvEmployeeDetails_RowEditing">

                <Columns>           

                    <asp:TemplateField HeaderText="Employee ID">

                        <ItemTemplate>

                            <asp:Label ID="lblEmpID" runat="server" Text='<%#DataBinder.Eval(

Container.DataItem,"empid") %>'></asp:Label>

                        </ItemTemplate>

                        <EditItemTemplate>           

                            <asp:Label ID="lblEditEmpID" runat="server" Text='<%#DataBinder.Eval(

Container.DataItem, "empid") %>'></asp:Label>           

                        </EditItemTemplate>

                        <FooterTemplate>

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

                        </FooterTemplate>

                    </asp:TemplateField>

                    <asp:TemplateField HeaderText="Name">

                        <ItemTemplate>

                            <asp:Label ID="lblName" runat="server" Text='<%#DataBinder.Eval(

Container.DataItem, "name") %>'></asp:Label>

                        </ItemTemplate>

                        <EditItemTemplate>           

                            <asp:TextBox ID="txtEditName" runat="server" Text='<%#DataBinder.Eval(

Container.DataItem,"name") %>'></asp:TextBox> 

                        </EditItemTemplate>

                        <FooterTemplate>

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

                        </FooterTemplate>

                    </asp:TemplateField>

                    <asp:TemplateField HeaderText="Designation">

                        <ItemTemplate>

                            <asp:Label ID="lblDesignation" runat="server" Text='<%#DataBinder.Eval(

Container.DataItem,"designation") %>'></asp:Label>

                        </ItemTemplate>

                        <EditItemTemplate>           

                          <asp:TextBox ID="txtEditDesignation" runat="server" Text='<%#DataBinder.Eval(

Container.DataItem, "designation") %>'></asp:TextBox>    

                        </EditItemTemplate>

                        <FooterTemplate>

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

                        </FooterTemplate>

                    </asp:TemplateField>

                    <asp:TemplateField HeaderText="City">

                        <ItemTemplate>

                            <asp:Label ID="lblCity" runat="server" Text='<%#DataBinder.Eval(

Container.DataItem,"city") %>'></asp:Label>

                        </ItemTemplate>

                        <EditItemTemplate>           

                            <asp:TextBox ID="txtEditCity" runat="server" Text='<%#DataBinder.Eval(

Container.DataItem,"city") %>'></asp:TextBox>           

                        </EditItemTemplate>

                       <FooterTemplate>

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

                        </FooterTemplate>

                    </asp:TemplateField>

                    <asp:TemplateField HeaderText="Country">

                        <ItemTemplate>

                            <asp:Label ID="lblCountry" runat="server" Text='<%#DataBinder.Eval(

Container.DataItem,"country") %>'></asp:Label>

                        </ItemTemplate>

                        <EditItemTemplate>           

                            <asp:TextBox ID="txtEditCountry" runat="server" Text='<%#DataBinder.Eval(

Container.DataItem, "country") %>'></asp:TextBox>           

                        </EditItemTemplate>

                        <FooterTemplate>

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

                        </FooterTemplate>

                    </asp:TemplateField>

                    <asp:TemplateField HeaderText="Action">

                        <ItemTemplate>

                           <asp:ImageButton ID="imgbtnEdit" runat="server" CommandName="Edit" ImageUrl="~/Images/icon-edit.png" Height="32px" Width="32px"/>

                           <asp:ImageButton ID="imgbtnDelete" runat="server" CommandName="Delete" ImageUrl="~/Images/Delete.png"/>

                        </ItemTemplate>

                        <EditItemTemplate>

                           <asp:ImageButton ID="imgbtnUpdate" runat="server" CommandName="Update" ImageUrl="~/Images/icon-update.png"/>

                          <asp:ImageButton ID="imgbtnCancel" runat="server" CommandName="Cancel" ImageUrl="~/Images/icon-Cancel.png"/>

                        </EditItemTemplate>

                        <FooterTemplate>

                           <asp:LinkButton ID="lbtnAdd" runat="server" CommandName="ADD" Text="Add" Width="100px"></asp:LinkButton>

                        </FooterTemplate>

                    </asp:TemplateField>

                </Columns>            

            </asp:GridView>

            </td>

            </tr>

        </table>

    </div>

    </form>

</body>

</html>

Code Behind:
Ex.Default.aspx.cs

using System;

using System.Collections.Generic;

using System.Linq;

using System.Web;

using System.Web.UI;

using System.Web.UI.WebControls;

using System.Xml;

using System.Data;

using Service;

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

{

    protected void Page_Load(object sender, EventArgs e)

    {

        if(!IsPostBack)

        {

            BindData();

        }

    }

    protected void BindData()

    {

        ServiceClient client = new ServiceClient();

        DataSet ds = new DataSet();

        DataTable FromTable = new DataTable();

        client.Open();

        ds = client.BindEmployeeDetails();       

FromTable = ds.Tables[0];

        if (FromTable.Rows.Count > 0)

        {

            gvEmployeeDetails.DataSource = FromTable;

            gvEmployeeDetails.DataBind();

        }

        else

        {           

FromTable.Rows.Add(FromTable.NewRow());

            gvEmployeeDetails.DataSource = FromTable;

            gvEmployeeDetails.DataBind();

            int TotalColumns = gvEmployeeDetails.Rows[0].Cells.Count;

            gvEmployeeDetails.Rows[0].Cells.Clear();           

gvEmployeeDetails.Rows[0].Cells.Add(new TableCell());           

gvEmployeeDetails.Rows[0].Cells[0].ColumnSpan = TotalColumns;           

gvEmployeeDetails.Rows[0].Cells[0].Text = "No records Found";

            client.Close();

        }

    }

    protected void gvEmployeeDetails_RowDeleting(object sender, GridViewDeleteEventArgs e)

    {

        Label lblEmpID = (Label)gvEmployeeDetails.Rows[e.RowIndex].FindControl("lblEmpID");

        ServiceClient client = new ServiceClient();

        Employee_Details objemp = new Employee_Details();

        objemp.empid = lblEmpID.Text;

        client.DeleteEmployeeDetails(objemp);

        client.Close();

        BindData();

    }

    protected void gvEmployeeDetails_RowCommand(object sender, GridViewCommandEventArgs e)

    {

        ServiceClient client = new ServiceClient();

        Employee_Details objemp = new Employee_Details();

        if (e.CommandName.Equals("ADD"))

        {

            TextBox txtAddEmpID = (TextBox)gvEmployeeDetails.FooterRow.FindControl("txtAddEmpID");

            TextBox txtAddName = (TextBox)gvEmployeeDetails.FooterRow.FindControl("txtAddName");

            TextBox txtAddDesignation = (TextBox)gvEmployeeDetails.FooterRow.

FindControl("txtAddDesignation");

            TextBox txtAddCity = (TextBox)gvEmployeeDetails.FooterRow.FindControl("txtAddCity");

    TextBox txtAddCountry = (TextBox)gvEmployeeDetails.FooterRow.FindControl("txtAddCountry");

            objemp.empid = txtAddEmpID.Text;           

objemp.name = txtAddName.Text;

            objemp.designation = txtAddDesignation.Text;

            objemp.city = txtAddCity.Text;

            objemp.country = txtAddCountry.Text;           

client.InsertEmloyeeDetails(objemp);

            client.Close();

            BindData();

        }

    }

    protected void gvEmployeeDetails_RowUpdating(object sender, GridViewUpdateEventArgs e)

    {

        Label lblEditEmpID = (Label)gvEmployeeDetails.Rows[e.RowIndex].FindControl("lblEditEmpID");

    TextBox txtEditName = (TextBox)gvEmployeeDetails.Rows[e.RowIndex].FindControl("txtEditName");

        TextBox txtEditDesignation = (TextBox)gvEmployeeDetails.Rows[e.RowIndex].

FindControl("txtEditDesignation");

   TextBox txtEditCity = (TextBox)gvEmployeeDetails.Rows[e.RowIndex].FindControl("txtEditCity");

 TextBox txtEditCountry = (TextBox)gvEmployeeDetails.Rows[e.RowIndex].

FindControl("txtEditCountry");

        ServiceClient client = new ServiceClient();

        Employee_Details objemp = new Employee_Details();

        objemp.empid = lblEditEmpID.Text;

        objemp.name = txtEditName.Text;

        objemp.designation = txtEditDesignation.Text;

        objemp.city = txtEditCity.Text;

        objemp.country = txtEditCountry.Text;

        client.UpdateEmployeeDetails(objemp);

        client.Close();

        gvEmployeeDetails.EditIndex = -1;

        BindData();

    }  

protected void gvEmployeeDetails_RowCancelingEdit(object sender, GridViewCancelEditEventArgs e)

    {

        gvEmployeeDetails.EditIndex = -1;

        BindData();

    }

    protected void gvEmployeeDetails_RowEditing(object sender, GridViewEditEventArgs e)

    {

        gvEmployeeDetails.EditIndex = e.NewEditIndex;

        BindData();

    }

}



View demo Download
http://www.dotnetfox.com/Document/1005/1005Demo1.gif

If you enjoyed this article, get email updates (it's free).


Related Articles
  • How to add edit delete update records in Grid view ASP.NET Introduction: In this article i'm going to explain how to add delete edit records in Grid view. Description: If we want to add delete edit records in grid view we should use following grid view events, 1. onrowcommand 2. onrowdeleting 3. onrowupdating 4. onrowcancelingedit 5. onrowediting Also we should use following template field in Grid View. 1.ItemTemplate : ItemTemplate is used to display rec...
  • How to insert edit delete update records in Grid view using WCF In this article i'm going to explain how to insert records using WCF. Windows Communication Foundation (WCF) is an extension of the .NET framework to build and run connected systems. Windows Communication Foundation provides a unified framework for building secure and reliable transacted Web services. Windows Communication Foundation combines and extends the capabilities of Distributed System...
  • File Upload in Gridview ASP.NETIn this article I’m going to explain how to use file upload control in grid view for uploading images or files to the server. The FileUpload control allows you to provide users with a way to send a file from their computer to the server. The control is useful for allowing users to upload images, text files or other files. Here I’ll show you how to use file upload control in gridview to upload the ...
  • Check or Uncheck all check box in ASP.NET Gridview using JavaScriptIn this article I’m going to explain how to add CheckBox control in Gridview to Check or Uncheck all checkbox using JavaScript. To implement this concept we have to add one Checkbox control in Gridview header template and one more checkbox control in Gridview item template. here we need to use javascript to avoid postback and change gridview row color if checkbox was checked or unchecked.
  • Insert delete update records in XML file -ASP.NET using c# In this article I’m going to explain how to insert delete update records in XML file using ASP.NET and C#. XML was created to structure, store and transport data. What is XML? XML stands for EXtensible Markup Language XML is a markup language much like HTML XML was designed to carry data, not to display data XML tags are not predefined. You must define your own tags
  • How to use DropDownList control to GridView in ASP.NET using c# In this article I’m going to explain how to use DropDownList control in ASP.NET using C#. ASP.NET allows you to use any server controls in GridView. In this article I’ll show you how to use DropDownList control in GridView. DropDownList can be used to select only one item at a time item. It’s very easy to use DropDownList control in GridView. We need to use OnRowDataBound event to bind DropDownLis...
  • Export GridView data to excel using ASP.NET and c# In this article I’m going to explain how to export GridView data to excel using ASP.NET and C#. Sometimes we need to export gridview data to excel. Here I’ll show you how to export GridView data to Excel by using RkLib ExportData. RKLib ExportData is a class library used to export data to excel. So here we have to create table and fetch the records to DataTable and we can export to excel.
  • How to use Ajax CalenderExtender in ASP.NET GridViewIn this article I’m going to explain how to use Ajax CalenderExtender in ASP.NET GridView. Today one of my blog subscriber asked me a question that’s how to use Ajax CalenderExtender in ASP.NET GridView. I hope you already know how to use Ajax CalenderExtender in normal page. There is no different to use Ajax CalenderExtender in normal page and GridView.
  • Create dynamic GridView or programmatically create ASP.NET GridView with dynamic BoundField In this article I’m going to explain how to create dynamic GridView or programmatically create GridView with dynamic BoundField in ASP.NET using C#. In some scenario it is necessary to create dynamic GridView with BoundField instead of static one. Here I’ll explain how to create dynamic GridView which includes dynamic BoundFiled, apply custom styles to header row and data row. After creating dyna...
  • How to export GridView data to PDF document in ASP.NET with C# using iTextSharpIn this article I’m going to explain how to export GridView to PDF document. Other articles related to iTextSharp. iTextSharp is a C# port of iText, and open source Java library for PDF generation and manipulation. It can be used to create PDF documents from scratch, to convert XML to PDF (using the extra XFA Worker DLL), to fill out interactive PDF forms, to stamp new content on existing PDF doc...

Comments
comments powered by Disqus

Thanks for downloading..!!

×
Subscribe via Email
    
message to display in fancybox