Tuesday, 10 April 2012

Inserting and Retrieving data from database using WCF Service in Asp.net || Basic WCF Example for Inserting data into Database in Asp.net


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

·         Create one WCF service
·         Create one Client Application  (means Design web forms and all)

First Create one WCF Service:

File à New à Project  à Visual C#  à WCF  àWCF Service Application  à Give the name for service  à OK


Then you will get 3 files
·         IService.cs
·         Service.svc
·         Service.svc.cs
 
For inserting and retrieving data from database you need to write the following code in IService.cs file

IService.cs Page :


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<UserDetails> GetUserDetails(string Username);

    [OperationContract]
    string InsertUserDetails(UserDetails userInfo);
}

// 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 firstname = string.Empty;
    string lastname = string.Empty;
    string location = string.Empty;

    [DataMember]
    public string UserName
    {
        get { return username; }
        set { username = value; }
    }
    [DataMember]
    public string FirstName
    {
        get { return firstname; }
        set { firstname = value; }
    }
    [DataMember]
    public string LastName
    {
        get { return lastname; }
        set { lastname = value; }
    }
    [DataMember]
    public string Location
    {
        get { return location; }
        set { location = value; }
    }
}

  
And Write the following code in Service.cs file:

Service.cs Page :
  
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=naresh;Initial Catalog=Register;User ID=sa;Password=123");
    public List<UserDetails> GetUserDetails(string Username)
    {
        List<UserDetails> userdetails = new List<UserDetails>();
        {
            con.Open();
            SqlCommand cmd = new SqlCommand("select * from UserInfo where UserName Like '%'+@Name+'%'", con);
            cmd.Parameters.AddWithValue("@Name", Username);
            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++)
                {
                    UserDetails userInfo = new UserDetails();
                    userInfo.UserName = dt.Rows[i]["UserName"].ToString();
                    userInfo.FirstName = dt.Rows[i]["FirstName"].ToString();
                    userInfo.LastName = dt.Rows[i]["LastName"].ToString();
                    userInfo.Location = dt.Rows[i]["Location"].ToString();
                    userdetails.Add(userInfo);
                }
            }
            con.Close();
        }
        return userdetails;
    }

    public string InsertUserDetails(UserDetails userInfo)
    {
        string strMessage = string.Empty;
        con.Open();
        SqlCommand cmd = new SqlCommand("insert into UserInfo(UserName,FirstName,LastName,Location) values(@Name,@FName,@LName,@Location)", con);
        cmd.Parameters.AddWithValue("@Name", userInfo.UserName);
        cmd.Parameters.AddWithValue("@FName", userInfo.FirstName);
        cmd.Parameters.AddWithValue("@LName", userInfo.LastName);
        cmd.Parameters.AddWithValue("@Location", userInfo.Location);
        int result = cmd.ExecuteNonQuery();
        if (result == 1)
        {
            strMessage = userInfo.UserName + " Details inserted successfully";
        }
        else
        {
            strMessage = userInfo.UserName + " Details not inserted successfully";
        }
        con.Close();
        return strMessage;
    }
}


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

WCF Service Url Link :



Now Create your Client Application in your system:
Create Client Application:

Create one Website add your Service Reference to Client Application.

Select Your Website àRight click on it à Add Service Reference à then Enter your Service URL (Paste what ever copied from your browser earlier) and Click Go àGive name for your service à OK


Then automatically Proxy will Create in your Client System.

Then Write the following code in your source code:

Source Code :

<html xmlns="http://www.w3.org/1999/xhtml">
<head runat="server">
    <title></title>
    <style type="text/css">
        .style1
        {
            width34%;
            height174px;
        }
        .style2
        {
            width128px;
        }
        .style3
        {
            width34%;
        }
        .style4
        {
            color#FF3300;
            text-aligncenter;
        }
    </style>
</head>
<body>
    <form id="form1" runat="server">
    <div>
   
        <h2 class="style4">
            <strong><em>Registration Form</em></strong></h2>
   
    </div>
    <table align="center" class="style1">
        <tr>
            <td class="style2">
                UserName</td>
            <td>
                <asp:TextBox ID="txtUserName" runat="server"></asp:TextBox>
                <asp:RequiredFieldValidator ID="RequiredFieldValidator1" runat="server"
                    ControlToValidate="txtUserName" ToolTip="Username Required"><imgsrc="delete.png" /></asp:RequiredFieldValidator>
            </td>
        </tr>
        <tr>
            <td class="style2">
                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 class="style2">
                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 class="style2">
                Location</td>
            <td>
                <asp:TextBox ID="txtlocation" runat="server"></asp:TextBox>
                <asp:RequiredFieldValidator ID="RequiredFieldValidator4" runat="server"
                    ControlToValidate="txtlocation" ToolTip="Location Required"><imgsrc="delete.png" /></asp:RequiredFieldValidator>
            </td>
        </tr>
        <tr>
            <td class="style2">
                &nbsp;</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,
using ServiceReference1;

Then Create one Object for ServiceReference and use that object to call methods from your service.

Write the following code in your aspx.cs file
Default.aspx.cs page :

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<UserDetails> objUserDetails = new List<UserDetails>();
        objUserDetails = objService.GetUserDetails("");

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

    protected void btnSubmit_Click(object sender, EventArgs e)
    {
        UserDetails userInfo = new UserDetails();
        userInfo.UserName = txtUserName.Text;
        userInfo.FirstName = txtfname.Text;
        userInfo.LastName = txtlname.Text;
        userInfo.Location = txtlocation.Text;
        string result = objService.InsertUserDetails(userInfo);
        lblResult.Text = result;
        BindUserDetails();
        txtUserName.Text = string.Empty;
        txtfname.Text = string.Empty;
        txtlname.Text = string.Empty;
        txtlocation.Text = string.Empty;
    }
}


Then Inserted your data into Database and showed in Gridview.                                                                                                   



25 comments:

  1. Your blog looking very informative, wish you good luck

    ReplyDelete
  2. You are updating your blogs excellent :) way to go dude, Yo Dude :)

    Karan

    ReplyDelete
  3. Hi all,

    WCF supports interoperability with WCF applications running on the same Windows machine or WCF running on a different Windows machines or standard Web services built on platforms such as Java running on Windows or other operating systems. Thanks a lot.....

    ReplyDelete
  4. hi
    i m manish from faridabad your all article is very nice pls update new article.
    thank you.

    ReplyDelete
  5. Thanks .. It worked for me.
    I was struggling with DataTable as it was giving error with connection to the service. But using List solved my Problem. :)

    Regards
    Hemant

    ReplyDelete
  6. Hey, this article is very good..It helped me a lot..

    ReplyDelete
  7. its working,
    but i am using Html5&jquery frentend ,
    how its values insert

    ReplyDelete
  8. This blog helps me a lot...
    Thanks..

    ReplyDelete
  9. Tell me Update,delete also as soon as possible

    ReplyDelete
  10. Awesome blog thank you so much.plz add on this blog update,delete code also.
    regards
    sumit kaushal

    ReplyDelete
  11. Nice Good One...I like the Information you Given Thanks Very Much to Share Your knowledge

    ReplyDelete
  12. thanks a lot really helpful....

    ReplyDelete
  13. Thanak a lot...very helpful for me....

    ReplyDelete
  14. Failed to add a service. Service metadata may not be accessible. Make sure your service is running and exposing metadata.


    I'm Getting this error while running service.Please help me out.

    ReplyDelete
  15. I tried following your step.. however in the button code, i can't seem to get my userInfo.Username... any idea where went wrong?

    ReplyDelete
  16. Hi I am getting error when objUserDetails = objService.GetUserDetails("");
    it says cannot implicitly convert type userdtls to a list....the service on its own runs fine...but when I try to call it from client app I get this error

    ReplyDelete
  17. i got errors like these
    1)Error 1 Cannot implicitly convert type 'System.Collections.Generic.List' to 'System.Collections.Generic.List' D:\Yash\Yashpal Modi_IMP_Data\C# TRINING\ITSOURCE COMPANY\PRACTICALS PROGS\C# WEB APPLICATIONS\WEB_WCF_DB_DEMO\Default.aspx.cs 23 25 D:\...\WEB_WCF_DB_DEMO\
    2)Error 2 The best overloaded method match for 'ServiceReference1.ServiceClient.addUserDetails(ServiceReference1.UserDetails)' has some invalid arguments D:\Yash\Yashpal Modi_IMP_Data\C# TRINING\ITSOURCE COMPANY\PRACTICALS PROGS\C# WEB APPLICATIONS\WEB_WCF_DB_DEMO\Default.aspx.cs 35 25 D:\...\WEB_WCF_DB_DEMO\
    3)Error 3 Argument 1: cannot convert from 'UserDetails' to 'ServiceReference1.UserDetails' D:\Yash\Yashpal Modi_IMP_Data\C# TRINING\ITSOURCE COMPANY\PRACTICALS PROGS\C# WEB APPLICATIONS\WEB_WCF_DB_DEMO\Default.aspx.cs 35 51 D:\...\WEB_WCF_DB_DEMO\

    ReplyDelete
  18. what is the use of using wcf service for insertion,deletion,updation in real time projects ?

    ReplyDelete
  19. Awesome tutorial for WCF and very supportive blog we ever seen .

    ReplyDelete