Database Connectivity using WebService

Introduction:

This article shows how to use Web Service and how to get data from database using Web Service. The Web Service behavior enables a client-side script to invoke remote methods exposed by Web Services or other web servers that support the SOAP and Web Services Description Language (WSDL) 1.1. This behavior provides developers the opportunity to use and leverage SOAP without requiring expert knowledge of its implementation. The Web Service behavior supports the use of a wide variety of data types, including intrinsic SOAP data types, arrays, objects and XML data. 
 
Web Services have Two Uses:

Reusable application components
Web Services can offer application components as services such as currency conversion, weather reports or even language translation.

Ideally, there will only be one type of each application component, and anyone can use it in his or her application.

Connect existing software

Web Services help solve the interoperability problem by giving different applications a way to link their data.

Using Web Services you can exchange data between different applications and different platforms.

Let's starts now by adding a new class in your application (Web Service) using the following information:

I am using default a database in my application; you can use whatever data you want to use. Put this web method in your web service class. I am using the Northwind database and customer table.

[WebMethod(Description = "Get all customers from customer table")]

    public DataSet GetLatestCustomers()

    {
         using (SqlConnection connection = new SqlConnection 
         (ConfigurationManager.ConnectionString"dataConnectionString"].ConnectionString))

         {
             string Query = "SELECT * FROM [Customers] ORDER BY [CustomerID] DESC";

             SqlCommand command = new SqlCommand(Query, connection);

             command.CommandType = CommandType.Text;           

             connection.Open();

             SqlDataReader reader = command.ExecuteReader();

 

             DataTable myTable = new DataTable("myTable");

             myTable.Columns.Add("CustomerID", typeof(string));

             myTable.Columns.Add("CompanyName", typeof(string));

             myTable.Columns.Add("ContactName", typeof(string));           

             myTable.Columns.Add("ContactTitle", typeof(string));

             myTable.Columns.Add("Address", typeof(string));

             myTable.Columns.Add("City", typeof(string));

             myTable.Columns.Add("Region", typeof(string));

             myTable.Columns.Add("PostalCode", typeof(string));

             myTable.Columns.Add("Country", typeof(string));

             myTable.Columns.Add("Phone", typeof(string));

             myTable.Columns.Add("Fax", typeof(string));

 

            while (reader.Read())

            {
                 myTable.Rows.Add(new object[]

                 {

                      reader["CustomerID"].ToString(), reader["CompanyName"].ToString(),
                      reader["ContactName"].ToString(), reader["ContactTitle"], reader
                      ["Address"], reader["City"], reader["Region"], reader["PostalCode"], 
                      reader["Country"],reader["Phone"], reader["Fax"] });

                 }

                 //myTable.Load(reader);

                 myTable.AcceptChanges();

                 DataSet ds = new DataSet();

                 ds.Tables.Add(myTable);

                 ds.AcceptChanges();

                 return ds;

            }
    } 

Put your class in the App_Code folder. I am using a GridView control to show data on the page.

 

Now make an object to call your class like this: 

Service service = new Service(); 

And put these two lines of code on the page load event.

GridView1.DataSource = service.GetLatestCustomers();
GridView1.DataBind();
 
That's it. After running your application, the results should come like this. For more information I am attaching sample application.



I hope you guys will like this article. If so, drop me a line in the c-sharpcorner comment section.