VB.Net SQL 结果为 Ajax 的 JSON

VB.Net SQL results to JSON for Ajax

提问by Dave

New programmer, I'm building an aspx page with VB.Net code and I'm trying to create a D3 chart with data from our database, but I can't get the return value into the correct format.

新程序员，我正在用 VB.Net 代码构建一个 aspx 页面，我正在尝试使用我们数据库中的数据创建一个 D3 图表，但我无法将返回值转换为正确的格式。

In my VB code I can get a string into the exact format I want:

在我的 VB 代码中，我可以将字符串转换为我想要的确切格式：

[{ name: "PROAIR", value: 7}],{ name: "NASONEX", value: 4}] 

but when I pass it back it ends up with:

但是当我把它传回去时，它最终是：

{"d":"[{name: \"PROAIR\", value: 7},{name: \"NASONEX\", value: 4}]"}

I know (now) that it's attempting to serialize the return value so I have tried using a datatable and serializing that, and then a List(Of Object) and serializing that, but every time I get different results with more quotes and escape backspaces in the return value. I've looked at the Microsoft Person example and some of the Stack Contacts examples, but is it really necessary for me to build a whole new class and add new items to it? My string builder code below:

我知道（现在）它正在尝试序列化返回值，所以我尝试使用数据表并序列化它，然后使用 List(Of Object) 并序列化它，但每次我得到不同的结果时，更多的引号和转义退格返回值。我已经查看了 Microsoft Person 示例和一些 Stack Contacts 示例，但是我真的有必要构建一个全新的类并向其中添加新项目吗？我的字符串生成器代码如下：

Dim sData As String
Dim sqlCmd As New SqlCommand(strSql, cn)
Using sdr As SqlDataReader = sqlCmd.ExecuteReader()
  Dim sb As New StringBuilder()
  sb.Append("[")
  While sdr.Read()
    sb.Append("{")
    System.Threading.Thread.Sleep(50)
    sb.Append(String.Format("name: '{0}', value: {1}", sdr(0), sdr(1)))
    sb.Append("},")
  End While
  sb = sb.Remove(sb.Length - 1, 1)
  sb.Append("]")
  sData = sb.ToString
  sData = sData.Replace("'", ControlChars.Quote)
  cn.Close()

  Return sData
End Using

If I try and serialize that string:

如果我尝试序列化该字符串：

Dim serializer As New JavaScriptSerializer()
Dim SerializedResult = serializer.Serialize(sData)
Return SerializedResult 

I end up with : {"d":"\"[{name: \\\"PROAIR\\\", value: 7},{name: \\\"NASONEX\\\", value: 4}]\""}

我最终得到： {"d":"\"[{name: \\\"PROAIR\\\", value: 7},{name: \\\"NASONEX\\\", value: 4}]\""}

I tried to deserialize it with Dim deserializedResult = serializer.Deserialize(Of String)(sData) but it errors out with an array type failure so apparently you can't do that :)

我试图用 Dim deserializedResult = serializer.Deserialize(Of String)(sData) 反序列化它，但它因数组类型失败而出错，所以显然你不能这样做:)

In the aspx page I have hard coded:

在 aspx 页面中，我进行了硬编码：

success: function (r) {
var data = [{ name: "PROAIR", value: 7}],{ name: "NASONEX", value: 4}]

and my chart builds perfectly, so it's just a matter of returning the sql data in the correct format and changing the script code to var data = r;

我的图表构建完美，所以只需以正确的格式返回 sql 数据并将脚本代码更改为 var data = r;

EDITWorking code below:

编辑下面的工作代码：

aspx page:

aspx页面：

    $(function () {
        GetData();
    });

    function GetData() {
        $.ajax({
            type: "post",
            url: "D3PieChart.aspx/GetData",
            contentType: "application/json; charset=utf-8",
            dataType: "json",
            success: function (r) {

                data = r.d;
                data = jQuery.parseJSON(data)

-- Build D3 chart commands in this section --

            },
            error: function (xhr, status, error) {
                OnFailure(error);
                alert('Error');
            }
        });
   }

</script>

VB page:

VB页面：

<WebMethod()> _
Public Shared Function GetData() As String     

    Dim sConnect As String = ConfigurationManager.ConnectionStrings("SQLConnectionString").ConnectionString
    Dim cn As New SqlConnection(sConnect)
Dim strSql As String = "SELECT name and a count of stuff "
strSql += "FROM a bunch of tables WHERE stuff = stuff "

    Dim dtb As New DataTable
    cn.Open()
    If (cn.State And ConnectionState.Open) <> 0 Then
        Dim sqlCmd As New SqlCommand(strSql, cn)
        Dim sqlDad As New SqlDataAdapter(sqlCmd)
        sqlDad.Fill(dtb)
        dtb.Columns(0).ColumnName = "Name"
        dtb.Columns(1).ColumnName = "value"
        Dim sjData As String = GetJson(dtb)
        Return sjData
    End If
    Return -1
End Function



Public Shared Function GetJson(ByVal dt As DataTable) As String
    Dim serializer As New System.Web.Script.Serialization.JavaScriptSerializer()
    serializer.MaxJsonLength = Integer.MaxValue

    Dim rows As New List(Of Dictionary(Of String, Object))()
    Dim row As Dictionary(Of String, Object) = Nothing
    Dim row2 As Dictionary(Of Integer, Object) = Nothing
    For Each dr As DataRow In dt.Rows
        row = New Dictionary(Of String, Object)()
        row2 = New Dictionary(Of Integer, Object)()
        For Each dc As DataColumn In dt.Columns
            row.Add(dc.ColumnName.Trim(), dr(dc))
        Next
        rows.Add(row)
    Next
    Return serializer.Serialize(rows)
End Function
End Class

This is a simple chart that doesn't take any parameters it's all hard coded in, but it's pulling the chart data from a SQL Server database.

这是一个简单的图表，它不接受任何硬编码的参数，但它从 SQL Server 数据库中提取图表数据。

采纳答案by Anubrij Chandra

Get your Sql result in a datatable and the simple pass it to the GetJson method to get the json string.

在数据表中获取您的 Sql 结果，并将其简单地传递给 GetJson 方法以获取 json 字符串。

Dim da As New OleDb.OleDbDataAdapter(strSql, cn)
Dim dt As DataTable = New DataTable
da.Fill(dt)

Dim sData As string = GetJson(dt)



 Public Shared Function GetJson(ByVal dt As DataTable) As String
        Dim serializer As New System.Web.Script.Serialization.JavaScriptSerializer()
        serializer.MaxJsonLength = Integer.MaxValue

        Dim rows As New List(Of Dictionary(Of String, Object))()
        Dim row As Dictionary(Of String, Object) = Nothing
        For Each dr As DataRow In dt.Rows
            row = New Dictionary(Of String, Object)()
            For Each dc As DataColumn In dt.Columns

                row.Add(dc.ColumnName.Trim(), dr(dc))

            Next
            rows.Add(row)
        Next
        Return serializer.Serialize(rows)
    End Function

Add reference of System.Collections.Generic

添加 System.Collections.Generic 的引用

On aspx page

在aspx页面

success: function (r) {
var data = r.d;

回答by Sam

I do something similar in my project hopefully it will help you:

我在我的项目中做了类似的事情，希望它能帮助你：

In my aspx page I'm using -

在我的 aspx 页面中，我正在使用 -

$.ajax({
            ...
            error: function () {

            },
            success: function (result) {
                $("#MainContent_EmployeesGridView").find('span[id="MainContent_EmployeesGridView_minIncrease_' + rowNum + '"]').html(result.d[0]);
                $("#MainContent_EmployeesGridView").find('span[id="MainContent_EmployeesGridView_maxIncrease_' + rowNum + '"]').html(result.d[1]);
            }
        });

The important part to note is that I'm using result.d[0] to set the html text value in my gridview.

需要注意的重要部分是我使用 result.d[0] 在我的 gridview 中设置 html 文本值。

So for your question it would look something like:

所以对于你的问题，它看起来像：

success: function (r) {
                var data = r.d[0]; 
                var data2 = r.d[1]; }

Here's an explanation that I found covering the .d

这是我发现涵盖 .d 的解释