How to Insert and Retrieve data from database using WCF Service in Asp.net.
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;
}
}
}
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=sridhar;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;
}
}
Source Code :
<html xmlns="http://www.w3.org/1999/xhtml">
<head runat="server">
<title></title>
<style type="text/css">
.style1
{
width: 34%;
height: 174px;
}
.style2
{
width: 128px;
}
.style3
{
width: 34%;
}
.style4
{
color: #FF3300;
text-align: center;
}
</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"><img src="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"><img src="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"><img src="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"><img src="delete.png"
/></asp:RequiredFieldValidator>
</td>
</tr>
<tr>
<td class="style2">
</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>
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;
}
}
Output :
