In this article I will explain with an example, how to insert data into database using a Web Service in ASP.Net using C# and VB.Net.
The data will be inserted to database using a Web Method of the Web Service and the inserted data will be displayed in GridView in ASP.Net using C# and VB.Net.
Database
I have made use of the following table Customers with the schema as follows. CustomerId is an Auto-Increment (Identity) column.
![Insert data into database using Web Service in ASP.Net using C# and VB.Net]()
I have already inserted few records in the table.
![Insert data into database using Web Service in ASP.Net using C# and VB.Net]()
Note: You can download the database table SQL by clicking the download link below.
Adding Web Service
You will need to add a new Web Service (ASMX) file using Add New Item Dialog of Visual Studio as shown below.
![Insert data into database using Web Service in ASP.Net using C# and VB.Net]()
Namespaces
You will need to import the following namespaces.
C#
using System.Data;
using System.Configuration;
using System.Data.SqlClient;
VB.Net
Imports System.Data
Imports System.Configuration
Imports System.Data.SqlClient
Configuring Web Service
Following is the Web Service which will be used for inserting and getting data from database.
The Web Service consists of two Web Method named Get and Insert.
The Insert Web Method accepts name and country as parameters.
C#
[WebMethod]
public DataTable Get()
{
string constr = ConfigurationManager.ConnectionStrings["constr"].ConnectionString;
using (SqlConnection con = new SqlConnection(constr))
{
using (SqlCommand cmd = new SqlCommand("SELECT * FROM Customers"))
{
using (SqlDataAdapter sda = new SqlDataAdapter())
{
cmd.Connection = con;
sda.SelectCommand = cmd;
using (DataTable dt = new DataTable())
{
dt.TableName = "Customers";
sda.Fill(dt);
return dt;
}
}
}
}
}
[WebMethod]
public void Insert(string name, string country)
{
string constr = ConfigurationManager.ConnectionStrings["constr"].ConnectionString;
using (SqlConnection con = new SqlConnection(constr))
{
using (SqlCommand cmd = new SqlCommand("INSERT INTO Customers (Name, Country) VALUES (@Name, @Country)"))
{
cmd.Parameters.AddWithValue("@Name", name);
cmd.Parameters.AddWithValue("@Country", country);
cmd.Connection = con;
con.Open();
cmd.ExecuteNonQuery();
con.Close();
}
}
}
VB.Net
<WebMethod()> _
Public Function Get() As DataTable
Dim constr As String = ConfigurationManager.ConnectionStrings("constr").ConnectionString
Using con As New SqlConnection(constr)
Using cmd As New SqlCommand("SELECT * FROM Customers")
Using sda As New SqlDataAdapter()
cmd.Connection = con
sda.SelectCommand = cmd
Using dt As New DataTable()
dt.TableName = "Customers"
sda.Fill(dt)
Return dt
End Using
End Using
End Using
End Using
End Function
<WebMethod()> _
Public Sub Insert(name As String, country As String)
Dim constr As String = ConfigurationManager.ConnectionStrings("constr").ConnectionString
Using con As New SqlConnection(constr)
Using cmd As New SqlCommand("INSERT INTO Customers (Name, Country) VALUES (@Name, @Country)")
cmd.Parameters.AddWithValue("@Name", name)
cmd.Parameters.AddWithValue("@Country", country)
cmd.Connection = con
con.Open()
cmd.ExecuteNonQuery()
con.Close()
End Using
End Using
End Sub
Adding Web Reference of Web Service
Next thing you need to do is add the Web Reference of the Web Service so that it can be used in the ASP.Net Web page.
![Insert data into database using Web Service in ASP.Net using C# and VB.Net]()
HTML Markup
The following HTML Markup consists of an ASP.Net GridView which will be populated using Web Service.
The EmptyDataText has been set for GridView to display message when no records are present.
Below the GridView, there’s a Form with two TextBoxes and a Button in order to insert data to the SQL Server database table using the Web Service.
<asp:GridView ID="GridView1" runat="server" AutoGenerateColumns="false" DataKeyNames="CustomerId"
EmptyDataText="No records has been added.">
<Columns>
<asp:TemplateField HeaderText="Name" ItemStyle-Width="150">
<ItemTemplate>
<asp:Label ID="lblName" runat="server" Text='<%# Eval("Name") %>'></asp:Label>
</ItemTemplate>
</asp:TemplateField>
<asp:TemplateField HeaderText="Country" ItemStyle-Width="150">
<ItemTemplate>
<asp:Label ID="lblCountry" runat="server" Text='<%# Eval("Country") %>'></asp:Label>
</ItemTemplate>
</asp:TemplateField>
</Columns>
</asp:GridView>
<table border="1" cellpadding="0" cellspacing="0" style="border-collapse: collapse">
<tr>
<td style="width: 150px">
Name:<br />
<asp:TextBox ID="txtName" runat="server" Width="140" />
</td>
<td style="width: 150px">
Country:<br />
<asp:TextBox ID="txtCountry" runat="server" Width="140" />
</td>
<td style="width: 100px">
<asp:Button ID="btnAdd" runat="server" Text="Add" OnClick="Insert" />
</td>
</tr>
</table>
Binding the GridView using Web Service
Inside the Page Load event handler of the page, the GridView is populated from the database by making call to the BindGrid method.
Inside the BindGrid method, the Web Service’s Get method is called and records are fetched from database and GridView DataSource is set.
Finally, GridView’s DataBind method is called.
C#
protected void Page_Load(object sender, EventArgs e)
{
if (!this.IsPostBack)
{
this.BindGrid();
}
}
private void BindGrid()
{
CRUD_Service.Service service = new CRUD_Service.Service();
GridView1.DataSource = service.Get();
GridView1.DataBind();
}
VB.Net
Protected Sub Page_Load(sender As Object, e As EventArgs) Handles Me.Load
If Not Me.IsPostBack Then
Me.BindGrid()
End If
End Sub
Private Sub BindGrid()
Dim service As New CRUD_Service.Service()
GridView1.DataSource = service.[Get]()
GridView1.DataBind()
End Sub
Inserting records using Web Service
The following event handler is executed when the Add Button is clicked. The name and the country values are fetched from their respective TextBoxes and then passed to the Web Service’s Insert Web Method for inserting the record in the database.
Finally, the GridView is again populated with data by making call to the BindGrid method.
C#
protected void Insert(object sender, EventArgs e)
{
CRUD_Service.Service service = new CRUD_Service.Service();
service.Insert(txtName.Text.Trim(), txtCountry.Text.Trim());
this.BindGrid();
}
VB.Net
Protected Sub Insert(sender As Object, e As EventArgs)
Dim service As New CRUD_Service.Service()
service.Insert(txtName.Text.Trim(), txtCountry.Text.Trim())
Me.BindGrid()
End Sub
Screenshots
GridView containing records
![Insert data into database using Web Service in ASP.Net using C# and VB.Net]()
GridView when no records are present
![Insert data into database using Web Service in ASP.Net using C# and VB.Net]()
Downloads
