Introduction

This article introduces how to insert data into SQL Server 2008 using a WCF service from C# code. To insert data into a database using a WCF service, we must do the following 3 things:
  1. Create Database Table
  2. Create WCF Service
  3. Create web Application
In the first step we will create a table in SQL Server; after that we create a simple function to insert data into the database using a WCF service. In a web application, add a reference of the service and data to be inserted will be sent to the web services function which will be inserted into the database table. 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

  1. Database name: Registration
  2. Database table name: RegistrationTable
RegistrationTable Table
  1. CREATE TABLE [dbo].[RegistrationTable]
  2. (
  3. [UserName] [varchar](100) NOT NULL,
  4. [Password] [varchar](20) NOT NULL,
  5. [Country] [varchar](100) NOT NULL,
  6. [Email] [varchar](200) NOT NULL
  7. )

Step 2 - Creating WCF Service

Now you have to create a WCF Service:
img1.jpg
Now click on the project and select WCF Service Application and provide a name for the service:
img2.jpg
Now click on the Ok Button. Then you will get 3 files in Solution Explorer.
  1. IService.cs
  2. Service.svc
  3. Service.svc.cs
The following image shows the following files:
img3.jpg
For inserting data into the database you need to write the following code in the IService1.cs file which contains the two sections:
  1. OperationContract
  2. DataContract
The OperationContract section is used to add service operations and DataContract is used to add types to service operations.
Iservice1.cs File
Now we create a function in the OperationContract section of the Iservice1.cs file:
  1. public interface IService1
  2. {
  3. [OperationContract]
  4. string InsertUserDetails(UserDetails userInfo);
  5. }
Now add types to service operations in the DataContract section:
  1. public class UserDetails
  2. {
  3. string username = string.Empty;
  4. string password = string.Empty;
  5. string country = string.Empty;
  6. string email = string.Empty;
  7. [DataMember]
  8. public string UserName
  9. {
  10. get { return username; }
  11. set { username = value; }
  12. }
  13. [DataMember]
  14. public string Password
  15. {
  16. get { return password; }
  17. set { password = value; }
  18. }
  19. [DataMember]
  20. public string Country
  21. {
  22. get { return country; }
  23. set { country = value; }
  24. }
  25. [DataMember]
  26. public string Email
  27. {
  28. get { return email; }
  29. set { email = value; }
  30. }
  31. }
Service.svc.cs File
In this file we define the definition of the function InsertUserDetails(UserDetails userInfo).
And replace the code with the following:
  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.Data.SqlClient;
  9. using System.Data;
  10. namespace WCFServiceForInsert
  11. {
  12. // NOTE: You can use the "Rename" command on the "Refactor" menu to change the class name "Service1" in code, svc and config file together.
  13. public class Service1 : IService1
  14. {
  15. public string InsertUserDetails(UserDetails userInfo)
  16. {
  17. string Message;
  18. SqlConnection con = new SqlConnection("Data Source=.;Initial Catalog=Rajesh;User ID=sa;Password=wintellect");
  19. con.Open();
  20. SqlCommand cmd = new SqlCommand("insert into RegistrationTable(UserName,Password,Country,Email) values(@UserName,@Password,@Country,@Email)", con);
  21. cmd.Parameters.AddWithValue("@UserName", userInfo.UserName);
  22. cmd.Parameters.AddWithValue("@Password", userInfo.Password);
  23. cmd.Parameters.AddWithValue("@Country", userInfo.Country);
  24. cmd.Parameters.AddWithValue("@Email", userInfo.Email);
  25. int result = cmd.ExecuteNonQuery();
  26. if (result == 1)
  27. {
  28. Message = userInfo.UserName + " Details inserted successfully";
  29. }
  30. else
  31. {
  32. Message = userInfo.UserName + " Details not inserted successfully";
  33. }
  34. con.Close();
  35. return Message;
  36. }
  37. }
  38. }
Testing the Service
Press F5 to run the service. A WCF Test Client form will be displayed and it will load the service.
img4.jpg
Now double-click the InserUserDetails() method under IService1. The InserUserDetails tab will be displayed.
img5.jpg
The service was added successfully.
Now open the service in the browser.
Now right-click on the service1.vcs-> open in browser:
img16.jpg
Now copy the URL.
http://localhost:2268/Service1.svc

Step 3 - Create Web Application (Accessing the Service)

Now, you have to create a web site.
image1.gif
Now add a new page to the website:
image2.gif
Add the service reference in web application
Now add the service reference.
img7.jpg
When we click on the add the service reference the following window will be opened:
ing9.jpg
Now paste the above URL in the address and click on the go Button.
img8.jpg
Click on the ok Button. Now the reference has been added in the Solution Explorer.
img10.jpg
Now create a new website and drag and drop controls onto the aspx page. The aspx code is the following:
  1. <%@ Page Language="C#" AutoEventWireup="true" CodeFile="Registration.aspx.cs" Inherits="Registration" %>
  2. <!DOCTYPE html PUBLIC "-//W3C//DTD XHTML 1.0 Transitional//EN" "http://www.w3.org/TR/xhtml1/DTD/xhtml1-transitional.dtd">
  3. <html xmlns="http://www.w3.org/1999/xhtml">
  4. <head runat="server">
  5. <title></title>
  6. </head>
  7. <body>
  8. <form id="form1" runat="server">
  9. <div>
  10. <table width="84%" cellpadding="0" cellspacing="0" style="border: solid 1px #3366CC;">
  11. <tr>
  12. <td colspan="4" style="height: 30px; background-color: #f5712b;">
  13. <span class="TextTitle" style="color: #FFFFFF;">Registration Form</span>
  14. </td>
  15. </tr>
  16. <tr>
  17. <td height="20px" colspan="0">
  18. </td>
  19. </tr>
  20. <tr>
  21. <td width="50%" valign="top">
  22. <table id="TableLogin" class="HomePageControlBGLightGray" cellpadding="4" cellspacing="4"
  23. runat="server" width="100%">
  24. <tr>
  25. <td colspan="3" align="center">
  26. <asp:Label ID="LabelMessage" ForeColor="Red" runat="server" EnableViewState="False"
  27. Visible="False"></asp:Label><br>
  28. </td>
  29. </tr>
  30. <tr style="font-weight: normal; color: #000000">
  31. <td align="right">
  32. <span>UserName:</span>;
  33. </td>
  34. <td align="left" style="padding-left: 10px;">
  35. <asp:TextBox ID="TextBoxUserName" runat="server" CssClass="textbox" Width="262px"
  36. MaxLength="50" Height="34px"></asp:TextBox>
  37. </td>
  38. </tr>
  39. <tr>
  40. <td align="right">
  41. <span class="TextTitle">Password:</span>
  42. </td>
  43. <td align="left" style="padding-left: 10px;">
  44. <asp:TextBox ID="TextBoxPassword" runat="server" CssClass="textbox" Width="261px"
  45. MaxLength="50" TextMode="Password" Height="34px"></asp:TextBox>
  46. <br />
  47. </td>
  48. </tr>
  49. <tr>
  50. <td align="right">
  51. <span class="TextTitle">Country:</span>
  52. </td>
  53. <td align="left" style="padding-left: 10px;">
  54. <asp:TextBox ID="TextBoxCountry" runat="server" CssClass="textbox" Width="258px"
  55. MaxLength="50" Height="34px"></asp:TextBox>
  56. <br />
  57. </td>
  58. </tr>
  59. <tr>
  60. <td align="right">
  61. <span class="TextTitle">Email:</span>
  62. </td>
  63. <td align="left" style="padding-left: 10px;">
  64. <asp:TextBox ID="TextBoxEmail" runat="server" CssClass="textbox" Width="258px"
  65. MaxLength="50" Height="34px"></asp:TextBox>
  66. <br />
  67. </td>
  68. </tr>
  69. <tr>
  70. <td align="right">
  71. </td>
  72. <td align="left" style="padding-left: 10px;">
  73. <asp:Button ID="Button1" runat="server" Text="Button" OnClick="Button1_Click" Width="87px" />
  74. <br />
  75. </td>
  76. </tr>
  77. </table>
  78. </td>
  79. </tr>
  80. </table>
  81. </div>
  82. </form>
  83. </body>
  84. </html>
The form looks like:
img11.jpg
Double-click the Button, and add the following code in the Click event handler:
  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 ServiceReference1;
  8. public partial class Registration : System.Web.UI.Page
  9. {
  10. ServiceReference1.Service1Client objServiceClientobjService = new ServiceReference1.Service1Client();
  11. protected void Page_Load(object sender, EventArgs e)
  12. {
  13. }
  14. protected void Button1_Click(object sender, EventArgs e)
  15. {
  16. UserDetails userInfo = new UserDetails();
  17. userInfo.UserName = TextBoxUserName.Text;
  18. userInfo.Password = TextBoxPassword.Text;
  19. userInfo.Country = TextBoxCountry.Text;
  20. userInfo.Email = TextBoxEmail.Text;
  21. string result = objServiceClientobjService.InsertUserDetails(userInfo);
  22. LabelMessage.Text = result;
  23. }
  24. }
Now run the application.
Press CTRL+F5 to run the project:
img15.jpg
Now enter the UserName, Password, country and Email and click on the button.
img12.jpg
Data has been inserted into the SQL Server database table and check it.
Clipboard14.jpg