How to Insert ,Update,Dlete and Find Data into sql Server Database using WCF services

This artical show you how to insert data into sql server using wcf service in asp.net.
To insert data into database using wcf you have to do 3 thing:
1>Create a database table.
2>create a wcf service
3>create a web application

Step1:

First create database table.

create table RegistrationTable(UserName varchar(50),Password varchar(50),Country varchar(50,Email varchar(50));

execute this code.

Step2:

Now its time to create WCF service.

Open Visual studio>go to file menu> open new project > click on wcf application.> give a namr to it.> click on ok button.

Now you will get three files like

1. IService.cs
2. Service.svc
3. Service.svc.cs

Code For 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;

// NOTE: You can use the "Rename" command on the "Refactor" menu to change the interface name "IService" in both code and config file together.
[ServiceContract]
public interface IService
{

    [OperationContract]
    string GetData(int value);

    [OperationContract]
    CompositeType GetDataUsingDataContract(CompositeType composite);
    string InsertUserDetails(UserDetails userInfo);
    string DeleteUserDetails(UserDetails userInfo);
    DataSet FindUserDetails(UserDetails userInfo);
    //string UpdateUserDetails(UserDetails userInfo);
    // TODO: Add your service operations here
}

// Use a data contract as illustrated in the sample below to add composite types to service operations.
[DataContract]
public class UserDetails
{

    string username = string.Empty;

    string password = string.Empty;

    string country = string.Empty;

    string email = string.Empty;



    [DataMember]

    public string UserName
    {

        get { return username; }

        set { username = value; }

    }

    [DataMember]

    public string Password
    {

        get { return password; }

        set { password = value; }

    }

    [DataMember]

    public string Country
    {

        get { return country; }

        set { country = value; }

    }

    [DataMember]

    public string Email
    {

        get { return email; }

        set { email = value; }

    }

}
public class CompositeType
{
    bool boolValue = true;
    string stringValue = "Hello ";

    [DataMember]
    public bool BoolValue
    {
        get { return boolValue; }
        set { boolValue = value; }
    }

    [DataMember]
    public string StringValue
    {
        get { return stringValue; }
        set { stringValue = value; }
    }
}
 

For inserting data into database you have to write code in service.cs file

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;
using System.Data.SqlClient;
// NOTE: You can use the "Rename" command on the "Refactor" menu to change the class name "Service" in code, svc and config file together.
public class Service : IService
{
    SqlConnection con = new SqlConnection("Data Source =SUSHIL-70F1F267; Initial Catalog =sushil; Integrated Security=true");
    public string GetData(int value)
    {
        return string.Format("You entered: {0}", value);
    }

    public CompositeType GetDataUsingDataContract(CompositeType composite)
    {
        if (composite == null)
        {
            throw new ArgumentNullException("composite");
        }
        if (composite.BoolValue)
        {
            composite.StringValue += "Suffix";
        }
        return composite;
    }
   
    //this is used for inserting updating data from database
    public string InsertUserDetails(UserDetails userInfo)
    {
        string Message;
        con.Open();
        string cmd = "insert into RegistrationTable(UserName,Password,Country,Email) values(@UserName,@Password,@Country,@Email)";
        SqlCommand com = new SqlCommand(cmd,con);
        com.Parameters.AddWithValue("@UserName", userInfo.UserName);
        com.Parameters.AddWithValue("@Password", userInfo.Password);
        com.Parameters.AddWithValue("@Country", userInfo.Country);
        com.Parameters.AddWithValue("@Email", userInfo.Email);
        int result = com.ExecuteNonQuery();
        if (result == 1)
        {
            Message=userInfo.UserName + " Details inserted successfully";

        }
        else
            {
                Message = userInfo.UserName + " Details not inserted successfully";
            }
            con.Close();
            return Message;
        }
    public string DeleteUserDetails(UserDetails userInfo)
    {
        string Message;
        con.Open();
        string cmd = " delete from  RegistrationTable where UserName=@UserName";
        SqlCommand com = new SqlCommand(cmd,con);
        com.Parameters.AddWithValue("@UserName",userInfo.UserName);
        int result= com.ExecuteNonQuery();
        if (result == 1)
        {
            Message = userInfo.UserName + " Details inserted successfully";

        }
        else
        {
            Message = userInfo.UserName + " Details not inserted successfully";
        }
        con.Close();
        return Message;
    }
    public DataSet FindUserDetails(UserDetails userInfo)
    {
       
       
        string cmd = "select * from RegistrationTable where UserName=@UserName";
        SqlCommand com = new SqlCommand(cmd,con);
        com.Parameters.AddWithValue("@UserName", userInfo.UserName);
        SqlDataAdapter da = new SqlDataAdapter(com);
        con.Open();

        DataSet ds = new DataSet();

        da.Fill(ds);

        con.Close();

        return ds;
       
    }
    public string UpdateUserDetails(UserDetails userInfo)
    {
        string Message;
        con.Open();
        string cmd = "update RegistrationTable set Password=@Password,Country=@Country,Email=@Email where UserName=@UserName";
        SqlCommand com = new SqlCommand(cmd, con);
        com.Parameters.AddWithValue("@UserName", userInfo.UserName);
        com.Parameters.AddWithValue("@Password", userInfo.Password);
        com.Parameters.AddWithValue("@Country", userInfo.Country);
        com.Parameters.AddWithValue("@Email", userInfo.Email);
        int result = com.ExecuteNonQuery();
        if (result == 1)
        {
            Message = userInfo.UserName + " Update successfully";

        }
        else
        {
            Message = userInfo.UserName + " Do not Update successfully";
        }
        con.Close();
        return Message;
    }
}

now press F5 to run the  service file.

Step3:

Now add one web page into .

Go to solution explorer > right click on project solution > add new item > click on web page> give a name > click on ok button.

aspx.page:

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

<!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>Insert Update and Delete in asp.net using WCF services</title>
</head>
<body>
    <form id="form1" runat="server">
    <div>
        <table width="84%" cellpadding="0" cellspacing="0" style="border: solid 1px #3366CC;">

            <tr>

                <td colspan="4" style="height: 30px; background-color: #f5712b;">

                    <span class="TextTitle" style="color: #FFFFFF;">Registration Form</span>

                </td>

            </tr>

            <tr>

                <td height="20px" colspan="0">

                </td>

            </tr>

            <tr>

                <td width="50%" valign="top">

                    <table id="TableLogin" class="HomePageControlBGLightGray" cellpadding="4" cellspacing="4"

                        runat="server" width="100%">

                        <tr>

                            <td colspan="3" align="center">

                                <asp:Label ID="LabelMessage" ForeColor="Red" runat="server" EnableViewState="False"

                                    Visible="False"></asp:Label><br/>

                            </td>

                        </tr>

                        <tr style="font-weight: normal; color: #000000">

                            <td align="right">

                                <span>UserName:</span>;

                            </td>

                            <td align="left" style="padding-left: 10px;">

                                <asp:TextBox ID="TextBoxUserName" runat="server" CssClass="textbox" Width="262px"

                                    MaxLength="50" Height="34px"></asp:TextBox>

                            </td>

                        </tr>

                        <tr>

                            <td align="right">

                                <span class="TextTitle">Password:</span>

                            </td>

                            <td align="left" style="padding-left: 10px;">

                                <asp:TextBox ID="TextBoxPassword" runat="server" CssClass="textbox" Width="261px"

                                    MaxLength="50" TextMode="Password" Height="34px"></asp:TextBox>

                                <br />

                            </td>

                        </tr>

                        <tr>

                            <td align="right">

                                <span class="TextTitle">Country:</span>

                            </td>

                            <td align="left" style="padding-left: 10px;">

                                <asp:TextBox ID="TextBoxCountry" runat="server" CssClass="textbox" Width="258px"

                                    MaxLength="50" Height="34px"></asp:TextBox>

                                <br />

                            </td>

                        </tr>

                        <tr>

                            <td align="right">

                                <span class="TextTitle">Email:</span>

                            </td>

                            <td align="left" style="padding-left: 10px;">

                                <asp:TextBox ID="TextBoxEmail" runat="server" CssClass="textbox" Width="258px"

                                    MaxLength="50" Height="34px"></asp:TextBox>

                                <br />

                            </td>

                        </tr>

                        <tr>

                            <td align="right">

                            </td>

                            <td align="left" style="padding-left: 10px;">

                                <asp:Button ID="Button1" runat="server" Text="Button" OnClick="Button1_Click" Width="87px" />
                                <asp:Button ID="Buttbtn_delete" runat="server" Text="Delete"  Width="87px"
                                    onclick="Buttbtn_delete_Click" />
                                    <asp:Button ID="btn_find" runat="server" Text="Find"  Width="87px"
                                    onclick="btn_find_Click"  />
                                    <asp:Button ID="btn_update" runat="server" Text="Update"  Width="87px" onclick="btn_update_Click"
                                    />
                                <br />

                            </td>

                        </tr>

                    </table>

                </td>

            </tr>

        </table>
    </div>
    </form>
</body>
</html>

code for .cs file:

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

public partial class InsertUpdateDelete : System.Web.UI.Page
{
    Service service = new Service();
    protected void Page_Load(object sender, EventArgs e)
    {

    }
    protected void Button1_Click(object sender, EventArgs e)
    {
        UserDetails ud = new UserDetails();
        ud.UserName = TextBoxUserName.Text;
        ud.Password = TextBoxPassword.Text;
        ud.Country = TextBoxCountry.Text;
        ud.Email = TextBoxEmail.Text;
        string result = service.InsertUserDetails(ud);
        LabelMessage.Text = result;
    }
    protected void Buttbtn_delete_Click(object sender, EventArgs e)
    {
        UserDetails ud = new UserDetails();
        ud.UserName = TextBoxUserName.Text;
        service.DeleteUserDetails(ud);
    }
    protected void btn_find_Click(object sender, EventArgs e)
    {
        UserDetails udfind = new UserDetails();
        DataSet ds = new DataSet();
        udfind.UserName = TextBoxUserName.Text;
        ds = service.FindUserDetails(udfind);

        if (ds.Tables[0].Rows.Count > 0)

        {
            TextBoxPassword.Text = ds.Tables[0].Rows[0].ItemArray[1].ToString();
            TextBoxCountry.Text = ds.Tables[0].Rows[0].ItemArray[2].ToString();
            TextBoxEmail.Text = ds.Tables[0].Rows[0].ItemArray[3].ToString();
        }
        else
        {
            LabelMessage.Visible = true;
            LabelMessage.Text = "No such Record is available..Sorry";
        }
    }
    protected void btn_update_Click(object sender, EventArgs e)
    {
        UserDetails udupdate = new UserDetails();
        udupdate.UserName = TextBoxUserName.Text;
        udupdate.Password = TextBoxPassword.Text;
        udupdate.Country = TextBoxCountry.Text;
        udupdate.Email = TextBoxEmail.Text;
        string result = service.UpdateUserDetails(udupdate);
        LabelMessage.Visible = true;
        LabelMessage.Text = result;
    }
}

Run the code.