How to display the actual result of sql server data in the HTML page using Web Service?

Question

I'm Using the below Store Procedure.

declare @body varchar(max)

set @body = cast( (
select td = cast( iVoucher_Type_id as varchar(30)) + '</td><td>' + cast( cVoucher_Type as varchar(30)) + '</td><td>' + cast( dVoucher_date as varchar(30))
from (
      select 
        iVoucher_Type_id,
        cVoucher_Type,
        dVoucher_date
        from master.dbo.Mast_Voucher_Type
      ) as d
for xml path( 'tr' ), type ) as varchar(max) )

set @body = '<tr><th>Voucher Type ID</th><th>Voucher Type</th><th>Voucher Date</th></tr>'
          + replace( replace( @body, '&lt;', '<' ), '&gt;', '>' )

print @body

In this its return the result like below format

<tr>
  <th>Voucher Type ID</th>
  <th>Voucher Type</th>
  <th>Voucher Date</th>
</tr>
<tr>
  <td>1</td>
  <td>test 1</td>
  <td>Mar 27 2016 4:08PM</td>
</tr>

I'm using below Web Service Code. In this how can I display the same output in Web Service. In this Web service output is not working its shows empty [ ].

[WebMethod()]
        public string Getdataset(string strQuery, string strCon)
        {
            DataTable dt = new DataTable();
            using (System.Data.SqlClient.SqlConnection conn = new System.Data.SqlClient.SqlConnection())
            {
                conn.ConnectionString = System.Configuration.ConfigurationManager.AppSettings["BB_CONSTR"];
                using (System.Data.SqlClient.SqlCommand cmd = new System.Data.SqlClient.SqlCommand())
                {
                    cmd.CommandText = strQuery;
                    cmd.Connection = conn;
                    conn.Open();
                    SqlDataAdapter da = new SqlDataAdapter(cmd);
                    da.Fill(dt);
                    System.Web.Script.Serialization.JavaScriptSerializer serializer = new System.Web.Script.Serialization.JavaScriptSerializer();
                    List<Dictionary<string, object>> rows = new List<Dictionary<string, object>>();
                    Dictionary<string, object> row = default(Dictionary<string, object>);
                    foreach (DataRow dr in dt.Rows)
                    {
                        row = new Dictionary<string, object>();
                        foreach (DataColumn col in dt.Columns)
                        {
                            row.Add(col.ColumnName, dr[col]);
                        }
                        rows.Add(row);
                    }
                    return serializer.Serialize(rows);
                }
            }
        }

Need web service to display the exact output of the sql server(without Serialize or change the code).

Answer 1

Replace print with select:

declare @body varchar(max)

set @body = cast( (
select td = cast( iVoucher_Type_id as varchar(30)) + '</td><td>' + cast( cVoucher_Type as varchar(30)) + '</td><td>' + cast( dVoucher_date as varchar(30))
from (
      select 
        iVoucher_Type_id,
        cVoucher_Type,
        dVoucher_date
        from master.dbo.Mast_Voucher_Type
      ) as d
for xml path( 'tr' ), type ) as varchar(max) )

set @body = '<tr><th>Voucher Type ID</th><th>Voucher Type</th><th>Voucher Date</th></tr>'
          + replace( replace( @body, '&lt;', '<' ), '&gt;', '>' )

SELECT @body

Return result without any serialization

[WebMethod()]
        public string Getdataset(string strQuery, string strCon)
        {
            string connStr = System.Configuration.ConfigurationManager.AppSettings["BB_CONSTR"];
            using (SqlConnection conn = new SqlConnection(connStr))
            {
                SqlCommand cmd = new SqlCommand()
                cmd.CommandText = strQuery;
                cmd.Connection = conn;
                conn.Open();
                object result = cmd.ExecuteScalar();
                if (result == DBNull.Value) throw new ApplicationException("Oh no");
                return result.ToString();
            }
        }