DotNet World: How to Insert and Retrieve data to GridView from SQL 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;

}

Web Service Url Link :

Source Code :

</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"><img src="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"><img src="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"><img src="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"><img src="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">

<PagerStyle BackColor="PaleGoldenrod" ForeColor="DarkSlateBlue"

HorizontalAlign="Center" />

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

DotNet World

Monday 9 April 2012

How to Insert and Retrieve data to GridView from SQL database using WCF Service in Asp.net.

No comments:

Post a Comment

Popular Posts