ReddyInfoSoft: 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">

.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">

</asp:GridView>

</p>

<asp:Button ID="btnShow" runat="server" Text="Show" onclick="btnShow_Click" />

<asp:Button ID="btnInsert" runat="server" Text="Insert"

onclick="btnInsert_Click" />

</p>

<p>

<tr>

Emp No :</td>

<td>

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

</td>

<td>

</td>

</tr>

<tr>

Emp Name :</td>

<td>

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

</td>

<td>

</td>

</tr>

<tr>

Em Sal :</td>

<td>

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

</td>

<td>

 </td>

</tr>

<tr>

Emp Dept No :</td>

<td>

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

</td>

<td>

</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:

ReddyInfoSoft

Sunday, April 15, 2012

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

No comments:

Post a Comment