Insert Data Into SQL Server Database Using WCF Service

Rohatash Kumar

7y458.6k30100Article

WCF WCF ServiceWCF tutorialsCreate WCF ServiceWCF service with SQL ServerInsert Data Using WCF

30 Comments

Join the conversation! Your thoughts help the community grow.

mohamed hashik5y ago
Could you please sent me the web config file to [email protected]
Dinesh Gabhane7y ago
Thanks Great effort. Really Good
Govind Mishra7y ago
Thanks Great effort
Rajeshwari sakharkar7y ago
Could you please send web config file..
jaime aviles9y ago
Hello Manuel could you please send me the web.configof the wcf service to my email [email protected], thanks in advance.
chenna gadicherla9y ago
This is below code is after adding WSDL file Reference page code, public FormpakAddon.ServiceReference1.ProjectResponseInfo[] executeAddProject(FormpakAddon.ServiceReference1.ProjectDto[] listProject) { FormpakAddon.ServiceReference1.executeAddProject inValue = new FormpakAddon.ServiceReference1.executeAddProject(); inValue.listProject = listProject; FormpakAddon.ServiceReference1.executeAddProjectResponse1 retVal = ((FormpakAddon.ServiceReference1.ProjectWebServicesSEI)(this)).executeAddProject(inValue); return retVal.@return; } FormpakAddon.ServiceReference1.ProjectWebServicesSEIClient objServiceClientobjService = new FormpakAddon.ServiceReference1.ProjectWebServicesSEIClient(); List<ProjectDto> ListPrjDetails = new List<ProjectDto>(); ProjectDto objPrjDetails = new ProjectDto(); objPrjDetails.projectTemplateName = "Chenna"; objPrjDetails.status = "Active"; objPrjDetails.statusComment = "Thisistest"; ListPrjDetails.Add(objPrjDetails); objServiceClientobjService.executeAddProject( ) .Now how can i insert this above details into server database. Any one have idea please help me.
hnieef9y ago
Hi. how bout insert master and child table like supplier and product detail. thank you
kalu singh rao10y ago
Nice...
sahar movahed11y ago
i have an e-shop with mysql (php) and a warehouse website with SQL server 2012(asp), i want write a service with WCF that when customer bye a good from my e-shop,insert a record into both database.how can i do this? or is there another secure solution for insert into different database?
Yamini Dwivedi11y ago
No connection could be made because the target machine actively refused it
- Rashmi Ransingh10y ago
  the service needs to be running at the time of program execution
Ken Cini11y ago
Thanks this article helped.
Gabriel Mandefu12y ago
i have follow up the instruction in this document. unfortunately it doesn't work for me. Got an internal error stating the server was unable to process the request due to an internatl error.
Jitendra Kumar12y ago
Thanks Sir ji..
ibrahim haruna12y ago
Hi Thanks for your article. Please how about if the values that i need to insert is coming from another table from another database. How am i going to do about this? thanks.
Prerana Tiwari12y ago
Good article sir
dwi rio12y ago
This article helped me, Thanks a lot
Hima12y ago
Nice Article, Thanks a lot...
anil babu13y ago
Hi, This is my table CustId(PK) ID Email PhoneNO 1 101 [email protected] 22222222 2 201 [email protected] 33333333 3 301 [email protected] 44444444 I want Create wcf service to take tag ID email/phone number and send back CustId How can i do this? Pls give the code for sample for this
teja vishwa13y ago
how to insert image into database with help of wcf
sudeep kushwaha13y ago
Inserting into database will create problem when multiple clients e.g 200-250 will access the service at a time? Is there any other way to do so.?
Santosh Yadav14y agoedited
very helpful article to begin with wcf. Thank you. But i want to ask you one thing while inserting data using wcf why we declare variables when we have properties for the same.....
joaquim passinhas14y ago
Hi sir. In IService1.cs file i was following your code, but in [OperationContract] string InsertUserDetails(UserDetails userInfo); UserDetails not appear. Please tell me which references use
Rohatash Kumar14y ago
Thanks. Sushil
Ravin Singh14y ago
hi sir! we need access the web service from html web page using javascript how we do? for example access the data from the sql server using webservice and display the data's in html page that is our requirement please help me
- Harish Nani13y ago
  Hi Ravin singh did u get your requirement. If so plz let me know. My requirement is also some what same as yours.
sushil kumar14y ago
Thank U for giving a knowledge of wcf.
balaji thirunavukarasu14y ago
how to add wcf test client service.please give me idea
Rohatash Kumar14y ago
Thank you sir. I will do it in my next article.
Mahesh Chand14y ago
This is good Rohtash. What you should do is, build an application that will display data in a GridView and do add, update, and delete operations.

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:

Create Database Table
Create WCF Service
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

Database name: Registration
Database table name: RegistrationTable

RegistrationTable Table

CREATE TABLE [dbo].[RegistrationTable]
(
[UserName] [varchar](100) NOT NULL,
[Password] [varchar](20) NOT NULL,
[Country] [varchar](100) NOT NULL,
[Email] [varchar](200) NOT NULL
)

Step 2 - Creating WCF Service

Now you have to create a WCF Service:

Go to Visual Studio 2010
New-> Select a project

Now click on the project and select WCF Service Application and provide a name for the service:

Now click on the Ok Button. 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 data into the database you 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 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:

public interface IService1
{
[OperationContract]
string InsertUserDetails(UserDetails userInfo);
}

Now add types to service operations in the DataContract section:

public class UserDetails
{
string username = string.Empty;
string password = string.Empty;
string country = string.Empty;
string email = string.Empty;
[DataMember]
public string UserName
{
get { return username; }
set { username = value; }
}
[DataMember]
public string Password
{
get { return password; }
set { password = value; }
}
[DataMember]
public string Country
{
get { return country; }
set { country = value; }
}
[DataMember]
public string Email
{
get { return email; }
set { email = value; }
}
}

Service.svc.cs File

In this file we define the definition of the function InsertUserDetails(UserDetails userInfo).

And replace the code with the following:

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.Data.SqlClient;
using System.Data;
namespace WCFServiceForInsert
{
// 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
{
public string InsertUserDetails(UserDetails userInfo)
{
string Message;
SqlConnection con = new SqlConnection("Data Source=.;Initial Catalog=Rajesh;User ID=sa;Password=wintellect");
con.Open();
SqlCommand cmd = new SqlCommand("insert into RegistrationTable(UserName,Password,Country,Email) values(@UserName,@Password,@Country,@Email)", con);
cmd.Parameters.AddWithValue("@UserName", userInfo.UserName);
cmd.Parameters.AddWithValue("@Password", userInfo.Password);
cmd.Parameters.AddWithValue("@Country", userInfo.Country);
cmd.Parameters.AddWithValue("@Email", userInfo.Email);
int result = cmd.ExecuteNonQuery();
if (result == 1)
{
Message = userInfo.UserName + " Details inserted successfully";
}
else
{
Message = userInfo.UserName + " Details not inserted successfully";
}
con.Close();
return Message;
}
}
}

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.vcs-> open in browser:

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.

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

Add the service reference in web application

Now add the service reference.

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.

Now create a new website and drag and drop controls onto the aspx page. The aspx code is the following:

<%@ Page Language="C#" AutoEventWireup="true" CodeFile="Registration.aspx.cs" Inherits="Registration" %>
<!DOCTYPE html PUBLIC "-//W3C//DTD XHTML 1.0 Transitional//EN" "http://www.w3.org/TR/xhtml1/DTD/xhtml1-transitional.dtd">
<html xmlns="http://www.w3.org/1999/xhtml">
<head runat="server">
<title></title>
</head>
<body>
<form id="form1" runat="server">
<div>
<table width="84%" cellpadding="0" cellspacing="0" style="border: solid 1px #3366CC;">
<tr>
<td colspan="4" style="height: 30px; background-color: #f5712b;">
<span class="TextTitle" style="color: #FFFFFF;">Registration Form</span>
</td>
</tr>
<tr>
<td height="20px" colspan="0">
</td>
</tr>
<tr>
<td width="50%" valign="top">
<table id="TableLogin" class="HomePageControlBGLightGray" cellpadding="4" cellspacing="4"
runat="server" width="100%">
<tr>
<td colspan="3" align="center">
<asp:Label ID="LabelMessage" ForeColor="Red" runat="server" EnableViewState="False"
Visible="False"></asp:Label><br>
</td>
</tr>
<tr style="font-weight: normal; color: #000000">
<td align="right">
<span>UserName:</span>;
</td>
<td align="left" style="padding-left: 10px;">
<asp:TextBox ID="TextBoxUserName" runat="server" CssClass="textbox" Width="262px"
MaxLength="50" Height="34px"></asp:TextBox>
</td>
</tr>
<tr>
<td align="right">
<span class="TextTitle">Password:</span>
</td>
<td align="left" style="padding-left: 10px;">
<asp:TextBox ID="TextBoxPassword" runat="server" CssClass="textbox" Width="261px"
MaxLength="50" TextMode="Password" Height="34px"></asp:TextBox>
<br />
</td>
</tr>
<tr>
<td align="right">
<span class="TextTitle">Country:</span>
</td>
<td align="left" style="padding-left: 10px;">
<asp:TextBox ID="TextBoxCountry" runat="server" CssClass="textbox" Width="258px"
MaxLength="50" Height="34px"></asp:TextBox>
<br />
</td>
</tr>
<tr>
<td align="right">
<span class="TextTitle">Email:</span>
</td>
<td align="left" style="padding-left: 10px;">
<asp:TextBox ID="TextBoxEmail" runat="server" CssClass="textbox" Width="258px"
MaxLength="50" Height="34px"></asp:TextBox>
<br />
</td>
</tr>
<tr>
<td align="right">
</td>
<td align="left" style="padding-left: 10px;">
<asp:Button ID="Button1" runat="server" Text="Button" OnClick="Button1_Click" Width="87px" />
<br />
</td>
</tr>
</table>
</td>
</tr>
</table>
</div>
</form>
</body>
</html>

The form looks like:

Double-click the Button, and add the following code in the Click event handler:

using System;
using System.Collections.Generic;
using System.Linq;
using System.Web;
using System.Web.UI;
using System.Web.UI.WebControls;
using ServiceReference1;
public partial class Registration : System.Web.UI.Page
{
ServiceReference1.Service1Client objServiceClientobjService = new ServiceReference1.Service1Client();
protected void Page_Load(object sender, EventArgs e)
{
}
protected void Button1_Click(object sender, EventArgs e)
{
UserDetails userInfo = new UserDetails();
userInfo.UserName = TextBoxUserName.Text;
userInfo.Password = TextBoxPassword.Text;
userInfo.Country = TextBoxCountry.Text;
userInfo.Email = TextBoxEmail.Text;
string result = objServiceClientobjService.InsertUserDetails(userInfo);
LabelMessage.Text = result;
}
}

Now run the application.

Press CTRL+F5 to run the project:

Now enter the UserName, Password, country and Email and click on the button.

Data has been inserted into the SQL Server database table and check it.