Working with Complex Data in a Web Service

Introduction

To work with complex data, we generally use a dataset. We will create a web service and use a dataset in the web service application that returns records from a student table. There is a database "STUDENT" and a database table "student_detail". So, let's create a web service.

Create an  ASP.NET Web Service Application and replace the code with the following code.

 using System;
 using System.Collections.Generic;
 using System.Linq;
 using System.Web;
 using System.Web.Services;
 using System.Data.SqlClient;
 using System.Data;
  
 namespace WebService2
 {
     /// <summary>
     /// Summary description for Service1
     /// </summary>
     [WebService(Namespace = "http://tempuri.org/")]
     [WebServiceBinding(ConformsTo = WsiProfiles.BasicProfile1_1)]
     [System.ComponentModel.ToolboxItem(false)]
     // To allow this Web Service to be called from script, using ASP.NET AJAX, uncomment the following line.
     // [System.Web.Script.Services.ScriptService]
     public class Service1 : System.Web.Services.WebService
     {
         string connstring = "Database=STUDENT;server=.;user=XXXX;password=XXXXXXXX";
         DataSet ds;
         DataView dv;
         SqlDataAdapter da;
         [WebMethod(Description= "to show record")]
         public DataSet show()
         {
             da = new SqlDataAdapter("select * from STUDENT_DETAIL", connstring);
             ds = new DataSet();
             da.Fill(ds);
             return ds;
         }      
     }
 }
 
Run the service application.

Output:



Now we perform some more operatiosn. Now, I am creating a web service application which can search the records and filter the records in a different way. Again createa Web Service Application and replace the code with the following code.

 using System;
 using System.Collections.Generic;
 using System.Linq;
 using System.Web;
 using System.Web.Services;
 using System.Data.SqlClient;
 using System.Data;
  
 namespace WebService2
 {
     /// <summary>
     /// Summary description for Service1
     /// </summary>
     [WebService(Namespace = "http://tempuri.org/")]
     [WebServiceBinding(ConformsTo = WsiProfiles.BasicProfile1_1)]
     [System.ComponentModel.ToolboxItem(false)]
     // To allow this Web Service to be called from script, using ASP.NET AJAX, uncomment the following line.
     // [System.Web.Script.Services.ScriptService]
     public class Service1 : System.Web.Services.WebService
     {
         string connstring = "Database=STUDENT;server=.;user=XXXX;password=XXXXXXXX";
         DataSet ds;
         DataView dv;
         SqlDataAdapter da;
         [WebMethod(Description= "to show record")]
         public DataSet show()
         {
             da = new SqlDataAdapter("select * from STUDENT_DETAIL", connstring);
             ds = new DataSet();
             da.Fill(ds);
             return ds;
         }
         [WebMethod(Description = "simple filter")]
         public DataTable filter(string column)
         {
             da = new SqlDataAdapter("select * from STUDENT_DETAIL", connstring);
             DataSet ds = new DataSet();
             da.Fill(ds);
             DataView dv = new DataView(ds.Tables[0]);
             dv.RowFilter = "f_name like '"+column+"%' ";
             return dv.ToTable();
         }
         [WebMethod(Description = "advance sort")]
         public DataTable advancesort(string column, string sort)
         {
             da = new SqlDataAdapter("select * from STUDENT_DETAIL", connstring);
             DataSet ds = new DataSet();
             da.Fill(ds);
             DataView dv = new DataView(ds.Tables[0]);
             dv.Sort = " "+column+" " + ""+sort+"";
             return dv.ToTable();
         }
         [WebMethod(Description = "advancefilter")]
         public DataTable advancefilter(string column, string value)
         {
             da = new SqlDataAdapter("select * from STUDENT_DETAIL", connstring);
             DataSet ds = new DataSet();
             da.Fill(ds);
             DataView dv = new DataView(ds.Tables[0]);
             dv.RowFilter = " "+column+" = '" + value + "' ";
             return dv.ToTable();
         }
         [WebMethod(Description="simple sorting")]
         public DataTable sort(string column)
         {
             da = new SqlDataAdapter("select * from STUDENT_DETAIL", connstring);
             DataSet ds = new DataSet();
             da.Fill(ds);
             DataView dv = new DataView(ds.Tables[0]);
             dv.Sort =  " "+ column +" ";
             return dv.ToTable();
  
         }
     }
}
 
Create a web application and consume the service. Arrange the UI controls the same as the below figure on the design page.

Now write the following code on the .aspx.cs page.

using System;
using System.Collections.Generic;
using System.Linq;
using System.Web;
using System.Web.UI;
using System.Web.UI.WebControls;
using System.Data;
 
namespace WebApplication3
{
    public partial class WebForm1 : System.Web.UI.Page
    {
        protected void Page_Load(object sender, EventArgs e)
        {
 
        }
       protected void btnshowfullrecord_Click(object sender, EventArgs e)
        {
            localhost.Service1 obj=new localhost.Service1();
            DataSet ds = obj.show();
            GridView1.DataSource = ds.Tables[0];
            GridView1.DataBind();
        }
 
        protected void btnsort_Click(object sender, EventArgs e)
        {
            if (txtvalue.Text == "")
            {
                lblerror.Text = "Plz, Enter Column name";
            }
            else
            {
                localhost.Service1 obj = new localhost.Service1();
                GridView1.DataSource = obj.sort(txtcolumn1.Text);
                GridView1.DataBind();
            }
        }
         protected void btnadvancesort_Click(object sender, EventArgs e)
        {
            if (txtcolumn2.Text == "" || txtascordesc.Text == "")
            {
                lblerror.Text = "Plz, Fill Both (Column anme and Asc/Desc ";

            }
            else
            {
                localhost.Service1 obj = new localhost.Service1();
                GridView1.DataSource = obj.advancesort(txtcolumn2.Text, txtascordesc.Text);
                GridView1.DataBind();
            }
        }
 
        protected void advancefilter_Click(object sender, EventArgs e)
        {
            localhost.Service1 obj = new localhost.Service1();
            GridView1.DataSource = obj.advancefilter(txtcolumnfilter.Text, txtvaluefilter.Text);
            GridView1.DataBind();
        }
 
        protected void btnfilter_Click(object sender, EventArgs e)
       {
           if (txtvalue.Text == "")

           {
 
           }
           else
           {
               localhost.Service1 obj = new localhost.Service1();
               GridView1.DataSource = obj.filter(txtvalue.Text);
               GridView1.DataBind();
           }
       }
    }
}

Run the application.

Output:

To show all records, Click the "Show Full Record" button. It will show all records of student.

Output:




To sort all record saccording to a column name (in ascending order), write the column name in the textbox and click the "sort" button.

Output:




To sort  aspecified column value in ascending or descending order, write the column name and sorting order( Asc/Desc) and click the "sort" (second sort button) button.

Output:



To search any record by the student name, you can write the full name or some characters of a name. It will show all records of the student table with the matching character.

Output:



To filter the records according to a specified column and column value, write the desired column name and column value and click the "filter" button. It will show the filtered records. Here I am filtering the record with "l_name"( column name) as "kumar"( column value).

Output: