WCF example for inserting and displaying data from a SQL Server Database Using WCF Service in ASP.NET.

Introduction

In this article I will show you a practical example of a WCF service for inserting data into a database using ASP.NET.

Using the Code

For inserting data into a database using a WCF service in ASP.Net, we have to do the following steps:

Part 1: Create a WCF Service

  1. Open Visual Studio 2010
  2. New WCF Service Application
  3. Give the name for service Customer Service
  4. Press ok

Create-WCF-Service.jpg

After that a new project is created, the CustomerService project.

Then you will get 3 files:

IService.cs Page

  1. using System;
  2. using System.Collections.Generic;
  3. using System.Linq;
  4. using System.Runtime.Serialization;
  5. using System.ServiceModel;
  6. using System.ServiceModel.Web;
  7. using System.Text;
  8. using System.Collections.Generic;
  9. using System.Runtime.Serialization;
  10. using System.ServiceModel;
  11. [ServiceContract]
  12. public interface IService
  13. {
  14. [OperationContract]
  15. List<CustomerDetails> GetCustomerDetails(string CutomerName);
  16. [OperationContract]
  17. string InsertCustomerDetails(CustomerDetails customerInfo);
  18. }
  19. [DataContract]
  20. public class CustomerDetails
  21. {
  22. string CutomerName = string.Empty;
  23. string firstname = string.Empty;
  24. string lastname = string.Empty;
  25. string address = string.Empty;
  26. [DataMember]
  27. public string CutomerName
  28. {
  29. get { return CutomerName; }
  30. set { CutomerName = value; }
  31. }
  32. [DataMember]
  33. public string FirstName
  34. {
  35. get { return firstname; }
  36. set { firstname = value; }
  37. }
  38. [DataMember]
  39. public string LastName
  40. {
  41. get { return lastname; }
  42. set { lastname = value; }
  43. }
  44. [DataMember]
  45. public string Address
  46. {
  47. get { return address; }
  48. set { address = value; }
  49. }
  50. }

And write the following code in the Service.cs file:

Service.cs Page

  1. using System;
  2. using System.Collections.Generic;
  3. using System.Linq;
  4. using System.Runtime.Serialization;
  5. using System.ServiceModel;
  6. using System.ServiceModel.Web;
  7. using System.Text;
  8. using System.Collections.Generic;
  9. using System.Configuration;
  10. using System.Data;
  11. using System.Data.SqlClient;
  12. public class Service : IService
  13. {
  14. SqlConnection con = new SqlConnection("Data Source=Sujeet;Initial Catalog=Register;User ID=sa;Password=123");
  15. public List<CustomerDetails> GetCustomerDetails(string CutomerName)
  16. {
  17. List<CustomerDetails> CustomerDetails = new List<CustomerDetails>();
  18. {
  19. con.Open();
  20. SqlCommand cmd = new SqlCommand("select * from CustomerInfo where CutomerName Like '%'+@Name+'%'", con);
  21. cmd.Parameters.AddWithValue("@Name", CutomerName);
  22. SqlDataAdapter da = new SqlDataAdapter(cmd);
  23. DataTable dt = new DataTable();
  24. da.Fill(dt);
  25. if (dt.Rows.Count > 0)
  26. {
  27. for (int i = 0; i < dt.Rows.Count; i++)
  28. {
  29. CustomerDetails customerInfo = new CustomerDetails();
  30. customerInfo.CutomerName = dt.Rows[i]["CutomerName"].ToString();
  31. customerInfo.FirstName = dt.Rows[i]["FirstName"].ToString();
  32. customerInfo.LastName = dt.Rows[i]["LastName"].ToString();
  33. customerInfo.Address = dt.Rows[i]["Address"].ToString();
  34. CustomerDetails.Add(customerInfo);
  35. }
  36. }
  37. con.Close();
  38. }
  39. return CustomerDetails;
  40. }
  41. public string InsertCustomerDetails(CustomerDetails customerInfo)
  42. {
  43. string strMessage = string.Empty;
  44. con.Open();
  45. SqlCommand cmd = new SqlCommand("insert into CustomerInfo(CutomerName,FirstName,LastName,Address) values(@Name,@FName,@LName,@Address)", con);
  46. cmd.Parameters.AddWithValue("@Name", customerInfo.CutomerName);
  47. cmd.Parameters.AddWithValue("@FName", customerInfo.FirstName);
  48. cmd.Parameters.AddWithValue("@LName", customerInfo.LastName);
  49. cmd.Parameters.AddWithValue("@Address", customerInfo.Address);
  50. int result = cmd.ExecuteNonQuery();
  51. if (result == 1)
  52. {
  53. strMessage = customerInfo.CutomerName + " inserted successfully";
  54. }
  55. else
  56. {
  57. strMessage = customerInfo.CutomerName + " not inserted successfully";
  58. }
  59. con.Close();
  60. return strMessage;
  61. }
  62. }

Build your service successfully first, then run your service in your browser, then you will get one URL link as in the following (copy that URL):

WCF-service-builds.jpg

In this way your WCF service builds successfully.

Part 2: Create a Web Based Application (Client)

Now create your Client Application in your system in the following way:

  1. Create one Website

  2. Add a Service Reference to the Web Application

  3. Select Your Website

  4. Right-click on it, add a Service Reference, then enter your Service URL and click Go

  5. Give the name for your service then click the OK buton

    Create-Web-Based-Application.jpg

  6. Then a Proxy will be created automatically in your client system.

  7. Write the following code in your source code:

Source Code

  1. <html xmlns="http://www.w3.org/1999/xhtml">
  2. <head id="Head1" runat="server">
  3. <title></title>
  4. </head>
  5. <body>
  6. <form id="form1" runat="server">
  7. <div>
  8. <h2>
  9. <strong>Cutomer Form</strong></h2>
  10. </div>
  11. <table align="center" class="style1">
  12. <tr>
  13. <td>
  14. CutomerName
  15. </td>
  16. <td>
  17. <asp:TextBox ID="txtCutomerName" runat="server"></asp:TextBox>
  18. <asp:RequiredFieldValidator ID="RequiredFieldValidator1" runat="server" ControlToValidate="txtCutomerName"
  19. ToolTip="CutomerName Required"><imgsrc="delete.png" /></asp:RequiredFieldValidator>
  20. </td>
  21. </tr>
  22. <tr>
  23. <td>
  24. First Name
  25. </td>
  26. <td>
  27. <asp:TextBox ID="txtfname" runat="server"></asp:TextBox>
  28. <asp:RequiredFieldValidator ID="RequiredFieldValidator2" runat="server" ControlToValidate="txtfname"
  29. ToolTip="Firstname Required"><imgsrc="delete.png" /></asp:RequiredFieldValidator>
  30. </td>
  31. </tr>
  32. <tr>
  33. <td>
  34. Last Name
  35. </td>
  36. <td>
  37. <asp:TextBox ID="txtlname" runat="server"></asp:TextBox>
  38. <asp:RequiredFieldValidator ID="RequiredFieldValidator3" runat="server" ControlToValidate="txtlname"
  39. ToolTip="Lastname Required"><imgsrc="delete.png" /></asp:RequiredFieldValidator>
  40. </td>
  41. </tr>
  42. <tr>
  43. <td>
  44. Address
  45. </td>
  46. <td>
  47. <asp:TextBox ID="txtAddress" runat="server"></asp:TextBox>
  48. <asp:RequiredFieldValidator ID="RequiredFieldValidator4" runat="server" ControlToValidate="txtAddress"
  49. ToolTip="Address Required"><imgsrc="delete.png" /></asp:RequiredFieldValidator>
  50. </td>
  51. </tr>
  52. <tr>
  53. <td>
  54. </td>
  55. <td>
  56. <asp:Button ID="btnSubmit" runat="server" Text="Submit" OnClick="btnSubmit_Click" />
  57. </td>
  58. </tr>
  59. </table>
  60. <table align="center" class="style3">
  61. <tr>
  62. <td>
  63. <asp:Label ID="lblResult" runat="server" />
  64. <br />
  65. <br />
  66. <asp:GridView ID="GridView1" runat="server" BackColor="LightGoldenrodYellow" BorderColor="Tan"
  67. BorderWidth="1px" CellPadding="2" ForeColor="Black" GridLines="None" Style="text-align: left"
  68. Width="304px">
  69. <AlternatingRowStyle BackColor="PaleGoldenrod" />
  70. <FooterStyle BackColor="Tan" />
  71. <HeaderStyle BackColor="Tan" Font-Bold="True" />
  72. <PagerStyle BackColor="PaleGoldenrod" ForeColor="DarkSlateBlue" HorizontalAlign="Center" />
  73. <SelectedRowStyle BackColor="DarkSlateBlue" ForeColor="GhostWhite" />
  74. <SortedAscendingCellStyle BackColor="#FAFAE7" />
  75. <SortedAscendingHeaderStyle BackColor="#DAC09E" />
  76. <SortedDescendingCellStyle BackColor="#E1DB9C" />
  77. <SortedDescendingHeaderStyle BackColor="#C2A47B" />
  78. </asp:GridView>
  79. </td>
  80. </tr>
  81. </table>
  82. </form>
  83. </body>
  84. </html>
  85. <usercontrol x:class="SilverlightRIAInsert.MainPage" xmlns="http://schemas.microsoft.com/winfx/2006/xaml/presentation"
  86. xmlns:x="http://schemas.microsoft.com/winfx/2006/xaml" xmlns:d="http://schemas.microsoft.com/expression/blend/2008"
  87. xmlns:mc="http://schemas.openxmlformats.org/markup-compatibility/2006" mc:ignorable="d"
  88. d:designheight="300" d:designwidth="543">
  89. <Grid x:Name="LayoutRoot" ><TextBlock Height="23" HorizontalAlignment="Left" Margin="152,29,0,0" Name="textBlock1" Text="FirsName" FontFamily="Verdana" FontSize="15" VerticalAlignment="Top" /><TextBox Height="23" HorizontalAlignment="Left" Margin="252,25,0,0" Name="textBox1" VerticalAlignment="Top" Width="120" /><TextBlock Height="23" HorizontalAlignment="Left" Margin="150,72,0,0" Name="textBlock2" Text="LastName" FontFamily="Verdana" FontSize="15" VerticalAlignment="Top" /><TextBox Height="23" HorizontalAlignment="Left" Margin="252,68,0,0" Name="textBox2" VerticalAlignment="Top" Width="120" /><TextBlock Height="23" HorizontalAlignment="Left" Margin="150,113,0,0" Name="textBlock3" Text="Age" FontFamily="Verdana" FontSize="15" VerticalAlignment="Top" /><TextBox Height="23" HorizontalAlignment="Left" Margin="252,113,0,0" Name="textBox3" VerticalAlignment="Top" Width="120" /><Button Content="Insert" FontFamily="Verdana" FontSize="19" Background="DeepSkyBlue" Height="44" HorizontalAlignment="Left" Margin="252,156,0,0" Name="button1" VerticalAlignment="Top" Width="120" Click="button1_Click" /></Grid>
  90. </usercontrol>

8. Add your service reference on the top:

  1. using ServiceReference1;
9. Then create one Object for Service Reference and use that object to call methods from your service.

10. Write the following code in your aspx.cs file:

Default.aspx.cs page

  1. using System;
  2. using System.Collections.Generic;
  3. using System.Linq;
  4. using System.Web;
  5. using System.Web.UI;
  6. using System.Web.UI.WebControls;
  7. using System.Collections.Generic;
  8. using ServiceReference1;
  9. public partial class _Default : System.Web.UI.Page
  10. {
  11. ServiceReference1.ServiceClient objService = new ServiceReference1.ServiceClient();
  12. protected void Page_Load(object sender, EventArgs e)
  13. {
  14. if (!IsPostBack)
  15. {
  16. BindUserDetails();
  17. }
  18. }
  19. protected void BindUserDetails()
  20. {
  21. IList<CustomerDetails> objUserDetails = new List<CustomerDetails>();
  22. objUserDetails = objService.GetCustomerDetails("");
  23. GridView1.DataSource = objUserDetails;
  24. GridView1.DataBind();
  25. }
  26. protected void btnSubmit_Click(object sender, EventArgs e)
  27. {
  28. CustomerDetails customerInfo = new CustomerDetails();
  29. customerInfo.CutomerName = txtCutomerName.Text;
  30. customerInfo.FirstName = txtfname.Text;
  31. customerInfo.LastName = txtlname.Text;
  32. customerInfo.Address = txtlocation.Text;
  33. string result = objService.InsertCustomerDetails(customerInfo);
  34. lblResult.Text = result;
  35. BindUserDetails();
  36. txtCutomerName.Text = string.Empty;
  37. txtfname.Text = string.Empty;
  38. txtlname.Text = string.Empty;
  39. txtAddress.Text = string.Empty;
  40. }
  41. }

By using this you have successfully inserted data in the database and you are also shown this in a grid view.

Happy Programming!