In this article I will explain a simple database Web Service Tutorial in ASP.Net with example using C# and VB.Net.
In this tutorial a simple Web Method will be created in Web Service to fetch data from database and the fetched data will be displayed in GridView.
Database
I have made use of the following table Customers with the schema as follows.
![Retrieve (get) data from Database using a Web Service in ASP.Net using C# and VB.Net]()
I have already inserted few records in the table.
![Retrieve (get) data from Database using a Web Service in ASP.Net using C# and VB.Net]()
Note: You can download the database table SQL by clicking the download link below.
Building the Web Service
1. First step is to add a Web Service.
![Retrieve (get) data from Database using a Web Service in ASP.Net using C# and VB.Net]()
2. Once the Web Service is added. You will need to add 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
3. Third step is to add the WebMethod for getting data from database.
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;
}
}
}
}
}
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
4. Finally you will need to add the Web Reference of the Web Service we just created to the project as shown below.
![Retrieve (get) data from Database using a Web Service in ASP.Net using C# and VB.Net]()
HTML Markup
The HTML Markup consists of an ASP.Net GridView which will be populated using Web Service.
<asp:GridView ID="GridView1" runat="server" AutoGenerateColumns="false">
<Columns>
<asp:TemplateField HeaderText="Name" ItemStyle-Width="50">
<ItemTemplate>
<asp:Label ID="lblId" runat="server" Text='<%# Eval("CustomerId") %>'></asp:Label>
</ItemTemplate>
</asp:TemplateField>
<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>
Binding the GridView using Web Service
The GridView is populated from the database inside the Page Load event of the page.
C#
protected void Page_Load(object sender, EventArgs e)
{
if (!this.IsPostBack)
{
this.BindGrid();
}
}
private void BindGrid()
{
CRUD_Service.ServiceCS service = new CRUD_Service.ServiceCS();
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_ServiceVB.ServiceVB()
GridView1.DataSource = service.[Get]()
GridView1.DataBind()
End Sub
Following is the GridView populated using Web Service.
![Retrieve (get) data from Database using a Web Service in ASP.Net using C# and VB.Net]()
Downloads
