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

In this article we will see that how to crate the WCF Service and consume through the Client Application in asp.net from C# code. For creating and consuming WCF service we must do the following 4 things.

Create Database Table
Create WCF Service
Create web Application
Consume the Service to the web application (Client Application)

At first we will create a table in SQL Server; after that we create a simple function to insert student record in database and fetch the data and show from the database in WCF service.

Then we will create a client application in which we add a reference of the WCF Service and do the insertion and show work on that application through the WCF service. Let's take a look at a practical example. The example application is developed in Visual Studio 2010 and SQL Server 2008.

Step 1: Creating Database Table

Database name: ExampleDB
Database table name: Student

Student Table:

Step 2: Creating WCF Service
Now we create a WCF Service:

Go to Visual Studio 2010
Now choose File à New à Project à Visual C# à WCF àWCF Service Application à Give the name for service

In example we give the name StudentService then click the OK.

Then you will get 3 files in Solution Explorer.

IService.cs
Service.svc
Service.svc.cs

The following image shows the following files:

For inserting and retrieving the data into the database we need to write the following code in the IService1.cs file which contains the two sections:

OperationContract
DataContract

The OperationContract section is used to add service operations and a DataContract is used to add types to the service operations.

Code is given below :-

IService1.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.Collections.Generic;

using System.Runtime.Serialization;

using System.ServiceModel;

namespace StudentService

{

// NOTE: You can use the "Rename" command on the "Refactor" menu to change the interface name "IService1" in both code and config file together.

[ServiceContract]

public interface IService1

{

[OperationContract]

List<UserDetails> GetUserDetails(string userid);

[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 userid = string.Empty;

string firstname = string.Empty;

string lastname = string.Empty;

string location = string.Empty;

[DataMember]

public string User_id

{

get { return userid; }

set { userid = 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; }

}

Now we double click on the file Service1.svc.cs and define the definition of the function InsertUserDetails(UserDetails userInfo) and GetUserDetails(string User_id) in this file. The code is given below :-

Service1.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.Configuration;

using System.Data;

using System.Data.SqlClient;

namespace StudentService

{

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

public class Service1 : IService1

{

SqlConnection con = new SqlConnection("Data Source=VALUED-DM5NGQVH;Initial Catalog=ExampleDB;User ID=sa;Password=cisd#428");

public List<UserDetails> GetUserDetails(string User_id)

{

List<UserDetails> userdetails = new List<UserDetails>();

{

con.Open();

SqlCommand cmd = new SqlCommand("select * from student where User_id Like '%'+@userid+'%'", con);

cmd.Parameters.AddWithValue("@userid", User_id);

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.User_id = dt.Rows[i]["User_id"].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 Student(User_id,FirstName,LastName,Location) values(@Userid,@FName,@LName,@Location)", con);

cmd.Parameters.AddWithValue("@Userid", userInfo.User_id);

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.User_id + " Details inserted successfully";

}

else

{

strMessage = userInfo.User_id + " Details not inserted successfully";

}

con.Close();

return strMessage;

}

Testing the Service

Press F5 to run the service. A WCF Test Client form will be displayed and it will load the service.

Now double-click the InserUserDetails() method under IService1. The InserUserDetails tab will be displayed.

The service was added successfully.

Now open the service in the browser.

Now right-click on the service1.svc.cs -> open in browser:

Then we see the service running in the browser.

Now Copy the url , In my example the URL is :- http://localhost:3145/Service1.svc
Let the service run in the browser or host the service in the IIS. Now our service is ready and we can consume the service . so for consuming the service we first create our client application. The process is given below :-

Step 3: Create web Application (Accessing the Service)

Now, you have to create a web site.

Go to Visual Studio 2010
New-> Select a website application
Click OK

Now add a new page to the website:

Go to the Solution Explorer
Right-click on the Project name
Select add new item
Add new web page and give it a name
Click OK

In my example i have given the name of the page is Student_information.aspx in this page for inserting the data we create the filed of the name named it Registration Form named and also place the grid view on which we will show the student informantion. The source code of aspx file and cs file is given below. Now we add the reference of the wcf in our client application. For this we right click on the application and click on Add Service Reference picture is given belw:-

When we click on the add the service reference the following window will be opened:

Now paste the above URL in the address and click on the go button.

Click on the Ok Button. Now the reference has been added in the Solution Explorer.

Student_information.aspx(code)

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

<!DOCTYPE html PUBLIC "-//W3C//DTD XHTML 1.0 Transitional//EN" "http://www.w3.org/TR/xhtml1/DTD/xhtml1-transitional.dtd">

.style1

{

width: 34%;

height: 174px;

}

.style2

{

width: 128px;

}

.style3

{

width: 34%;

}

.style4

{

color: #FF3300;

text-align: center;

}

</style>

</head>

<body>

<div>

<strong><em>Registration Form</em></strong></h2>

</div>

<tr>

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>

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>

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>

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>

<td>

<asp:Button ID="btnSubmit" runat="server" Text="Submit"

onclick="btnSubmit_Click" />

</td>

</tr>

</table>

<tr>

<td>

<asp:Label ID="lblResult" runat="server"/>

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

</asp:GridView>

</td>

</tr>

</table>

</form>

</body>

</html>

Student_information.aspx(Design)

Student_information.cs (Code)

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 StudentServiceRef;

public partial class Student_information : System.Web.UI.Page
{
StudentServiceRef.Service1Client objService = new StudentServiceRef.Service1Client();

protected void Page_Load(object sender, EventArgs e)
{
if (!IsPostBack)
{
BindUserDetails();
}
}
protected void btnSubmit_Click(object sender, EventArgs e)
{
UserDetails userInfo = new UserDetails();
userInfo.User_id = 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;
}
protected void BindUserDetails()
{
IList<UserDetails> objUserDetails = new List<UserDetails>();
objUserDetails = objService.GetUserDetails("");

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

Now run the application.

Press CTRL+F5 to run the project:

Now enter userName, First Name , Last Name Location and submit then it save the student information into the database and show it in the given gridview.

In the above example we don't have any from the database to in the client application that means student_information.cs page .But we are able to store the data into the database and retrieve the data from the database through the WCF service.

So, now we are able to create the WCF service and consume the same into the client application.

RAKESH WEB SOLUTION

Pages

Monday 17 February 2014

Creating and Consuming a WCF Service with example in ASP.Net With SQL Server. || Inserting and Retrieving data from database using WCF Service in Asp.net

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

Cheers

No comments:

Post a Comment