.net connection list to json(JavaScript Object Notation) sample (1/2)

.net connection list to json(JavaScript Object Notation) sample (1/2)
 

 

 

WebForm1.aspx

<%@ Page Language="C#" AutoEventWireup="true" CodeBehind="WebForm1.aspx.cs" Inherits="connection.WebForm1" %>

<!DOCTYPE html PUBLIC "-//W3C//DTD XHTML 1.0 Transitional//EN" "http://www.w3.org/TR/xhtml1/DTD/xhtml1-transitional.dtd">

<html xmlns="http://www.w3.org/1999/xhtml">
<head runat="server">
    <title></title>
</head>
<body>
    <form id="form1" runat="server">
    <div>
            
    </div>
    </form>
</body>
</html>

WebForm1.aspx.cs

using System;
using System.Collections.Generic;
using System.Linq;
using System.Web;
using System.Web.UI;
using System.Web.UI.WebControls;
using System.Data.SqlClient;//SqlConnection,SqlCommand,SqlDataReader
using System.Web.Configuration;//WebConfigurationManager
using Newtonsoft.Json;//JsonConvert(要抓取並加入 Newtonsoft.Json.dll 在參考裡面)

namespace connection
{
    public class rsp
    {
        public string a { get; set; }
        public string b { get; set; }
    }

    public partial class WebForm1 : System.Web.UI.Page
    {
        protected void Page_Load(object sender, EventArgs e)
        {            
            string sql = @"";
            List<rsp> r = new List<rsp>();

            using (SqlConnection connection = new SqlConnection(WebConfigurationManager.ConnectionStrings["JudyConnection"].ConnectionString.ToString()))
            {
                connection.Open();
                sql = @"select * from aa";
                SqlCommand command = new SqlCommand(sql, connection);    
                using (SqlDataReader dr = command.ExecuteReader())
                {
                    while (dr.Read()) {
                        r.Add(new rsp()
                        {
                            a = dr["a"].ToString(),
                            b = dr["b"].ToString()
                        });
                    }
                }
            }
            // 將 List 轉 Json 字串
            string outJSONString = "";
            outJSONString = JsonConvert.SerializeObject(r, Newtonsoft.Json.Formatting.Indented);
            // 回傳至前端
            Response.Write(outJSONString);
        }
    }

}

Web.config

<configuration>

  <system.web>
    <compilation debug="true" targetFramework="4.0" />
  </system.web>

  <connectionStrings>
    <add name="JudyConnection" connectionString="Data Source=資料庫IP或網域名稱;Initial Catalog=預設資料庫;Integrated Security=驗證方式" />
  </connectionStrings>

</configuration>

Integrated Security=SSPI

附註 : 需加入 -> Newtonsoft.Json (dll)

# 加入 Newtonsoft.Json (dll) 步驟