如何使用 Web 服务在 HTML 页中显示 SQL 服务器数据的实际结果

Question

我正在使用下面的存储过程。

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

在此，它返回的结果如下格式

<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>

我正在使用下面的网络服务代码。在这种情况下，我如何在 Web 服务中显示相同的输出。在此 Web 服务输出不起作用中，其显示为空 [ ]。

[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);
                }
            }
        }

需要 Web 服务来显示 sql 服务器的确切输出(无需序列化或更改代码(。

Answer 1

将 print 替换为 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

返回不带任何序列化的结果

[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();
            }
        }