How to display actual result of sql server data in HTML page using web service?
I am using the following 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, '<', '<' ), '>', '>' )
print @body
In this, its result returns the result as 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 am using below web service code. In this case, I can display the same result in a web service. In this webservice, the output doesn't work, its show is 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);
}
}
}
Requires a web service to display the exact output of the SQL server (no serialization or code changes).
+3
source to share
1 answer
Replace seal with selection:
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, '<', '<' ), '>', '>' )
SELECT @body
Return result without 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();
}
}
+4
source to share