Sunday, April 15, 2012

Insert and Retrieve data from database using WCF Service in Asp / WinForms


Insert and Retrieve data from database using WCF Service in Asp.net || Beginner WCF Example to insert data into Database in Asp.net or Windows Forms.

To insert data into database using WCF service in asp.net, we have to do the following 3 steps as stated below:

1.      Create Database Table.
2.      Create one WCF service / Website (here website was created by me).
3.      Create Client Application (web / windows).

Create Database and One Table:
1.      Database name as wcfdb
2.      Database table as empinfo

create database wcfdb
create table empinfo
(
      empno int,
      ename varchar(150),
      sal float,
      deptno int
)

Create one WCF service / Website:
File à New à WebSie  à Visual C#  à Asp.Net Web Site  à Give the name for website  à OK



Go to web.config and give connection string name as EmpCon
Add new item WCFService as DBService.svc as shown in the figure



 Now 3 files are created as shown in the figure
















To insert and retrieve data from database we need to write the following code in IDBService.cs file as follows
using System;
using System.Collections.Generic;
using System.Linq;
using System.Runtime.Serialization;
using System.ServiceModel;
using System.Text;

// NOTE: You can use the "Rename" command on the "Refactor" menu to change the interface name "IDBService" in both code and config file together.
[ServiceContract]
public interface IDBService
{
    [OperationContract]
    List<Emp> getAllEmps();
    [OperationContract]
    void insertEmp(Emp empobj);     
}
[DataContract]
public class Emp
{
    int _empno;
    string _ename;
    double _sal;
    int _deptno;
    [DataMember]
    public int empno
    {
        get { return _empno; }
        set { _empno = value; }
    }
    [DataMember]
    public string ename
    {
        get { return _ename; }
        set { _ename = value; }
    }
    [DataMember]
    public int deptno
    {
        get { return _deptno; }
        set { _deptno = value; }
    }
    [DataMember]
    public double sal
    {
        get { return _sal; }
        set { _sal = value; }
    }
}

And now write the code in DBService.cs file
using System;
using System.Collections.Generic;
using System.Linq;
using System.Runtime.Serialization;
using System.ServiceModel;
using System.Text;
using System.Data;
using System.Configuration;
using System.Data.SqlClient;

// NOTE: You can use the "Rename" command on the "Refactor" menu to change the class name "DBService" in code, svc and config file together.
public class DBService : IDBService
{

    #region IDBService Members

    public List<Emp> getAllEmps()
    {
        SqlConnection con = new SqlConnection();
        con.ConnectionString = ConfigurationManager.ConnectionStrings["EmpCon"].ConnectionString;
        SqlCommand cmd = new SqlCommand();
        cmd.CommandText = "select * from empinfo";
        cmd.Connection = con;
        SqlDataReader reader;
        List<Emp> emplist = new List<Emp>();
        con.Open();
        reader = cmd.ExecuteReader();
        while (reader.Read())
        {
            Emp empobj = new Emp();
            empobj.empno = int.Parse(reader["empno"].ToString());
            empobj.ename = reader["ename"].ToString();
            empobj.sal = double.Parse(reader["sal"].ToString());
            empobj.deptno = int.Parse(reader["deptno"].ToString());
            emplist.Add(empobj);
        }
        con.Close();
        return emplist;
    }

    public void insertEmp(Emp empobj)
    {
        SqlConnection con = new SqlConnection(ConfigurationManager.ConnectionStrings["EmpCon"].ConnectionString);
        SqlCommand cmd = new SqlCommand();
        cmd.CommandText = "insert into empinfo (empno,ename,sal,deptno) values (@empno,@ename,@sal,@deptno)";
        cmd.Parameters.AddWithValue("@empno",empobj.empno);
        cmd.Parameters.AddWithValue("@ename",empobj.ename);
        cmd.Parameters.AddWithValue("@sal",empobj.sal);
        cmd.Parameters.AddWithValue("@deptno",empobj.deptno);
        cmd.Connection = con;
        con.Open();
        cmd.ExecuteNonQuery();
        con.Close();
    }

    #endregion
}

Up to now we created  WCFservice
Now create another Asp.Net website as DataContractWCFWebClient
Now add ServiceReference for the website as shown in the figure
1.      Press discover
2.      Press Go. Ones Go button was pressed you can see a url for reference 
3.      Give namespace as SR
4.      Last press OK Button.



In default.aspx
<%@ Page Title="Home Page" Language="C#" MasterPageFile="~/Site.master" AutoEventWireup="true"
    CodeBehind="Default.aspx.cs" Inherits="DataContractWCFWebClient._Default" %>

<asp:Content ID="HeaderContent" runat="server" ContentPlaceHolderID="HeadContent">
    <style type="text/css">
        .style1
        {
            width: 132px;
        }
    </style>
</asp:Content>
<asp:Content ID="BodyContent" runat="server" ContentPlaceHolderID="MainContent">
    <h2>
        Welcome to WCF DataConract!
    </h2>
   <p>
       <asp:GridView ID="GridView1" runat="server" CellPadding="4" ForeColor="#333333"
           GridLines="None">
           <AlternatingRowStyle BackColor="White" />
           <EditRowStyle BackColor="#2461BF" />
           <FooterStyle BackColor="#507CD1" Font-Bold="True" ForeColor="White" />
           <HeaderStyle BackColor="#507CD1" Font-Bold="True" ForeColor="White" />
           <PagerStyle BackColor="#2461BF" ForeColor="White" HorizontalAlign="Center" />
           <RowStyle BackColor="#EFF3FB" />
           <SelectedRowStyle BackColor="#D1DDF1" Font-Bold="True" ForeColor="#333333" />
           <SortedAscendingCellStyle BackColor="#F5F7FB" />
           <SortedAscendingHeaderStyle BackColor="#6D95E1" />
           <SortedDescendingCellStyle BackColor="#E9EBEF" />
           <SortedDescendingHeaderStyle BackColor="#4870BE" />
       </asp:GridView>
   </p>
   <p align="center">
       <asp:Button ID="btnShow" runat="server" Text="Show" onclick="btnShow_Click" />
       &nbsp;
       <asp:Button ID="btnInsert" runat="server" Text="Insert"
           onclick="btnInsert_Click" />
   </p>
   <p>
   <table style="width: 100%;">
    <tr>
        <td class="style1">
            &nbsp;
            Emp No :</td>
        <td>          
            <asp:TextBox ID="txtEmpNo" runat="server"></asp:TextBox>
        </td>
        <td>
            &nbsp;
        </td>
    </tr>
    <tr>
        <td class="style1">
            &nbsp;
            Emp Name :</td>
        <td>          
            <asp:TextBox ID="txtEmpNmae" runat="server"></asp:TextBox>
        </td>
        <td>
            &nbsp;
        </td>
    </tr>
    <tr>
        <td class="style1">
            Em Sal :</td>
        <td>
            <asp:TextBox ID="txtEmpSal" runat="server"></asp:TextBox>
        </td>
        <td>
            &nbsp;</td>
    </tr>
    <tr>
        <td class="style1">
            &nbsp;
            Emp Dept No :</td>
        <td>          
            <asp:TextBox ID="txtEmpDeptno" runat="server"></asp:TextBox>
        </td>
        <td>
            &nbsp;
        </td>
    </tr>
</table>
   </p>
</asp:Content>


In Default.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.ServiceModel;
using System.Runtime.Serialization;
using DataContractWCFWebClient;
using DataContractWCFWebClient.SR;

namespace DataContractWCFWebClient
{
    public partial class _Default : System.Web.UI.Page
    {
        protected void Page_Load(object sender, EventArgs e)
        {
            if(!Page.IsPostBack)
                show();
        }

        protected void btnShow_Click(object sender, EventArgs e)
        {
            show();
        }

        protected void btnInsert_Click(object sender, EventArgs e)
        {
            Emp empobj = new Emp();
            empobj.empno = int.Parse(txtEmpNo.Text);
            empobj.ename = txtEmpNmae.Text;
            empobj.sal = double.Parse(txtEmpSal.Text);
            empobj.deptno = int.Parse(txtEmpDeptno.Text);
            DBServiceClient client = new DBServiceClient();
            client.insertEmp(empobj);
        }

        private void show()
        {
            DBServiceClient client = new DBServiceClient();
            GridView1.DataSource = client.getAllEmps();
            GridView1.DataBind();
        }
    }
}

Out put:


No comments:

Post a Comment