asp.net用sql数据库生成json字符串并显示出来

use Shop

create table DictBase(Id int identity(1,1) primary key,Name nchar(10))

insert into DictBase

select  '包装'

UNION ALL

select  '价格'

UNION ALL

select  '品牌'

工厂方法模式

IDAL层（添加一个IDictBase接口，在里面定义一个方法）

using System;

using System.Collections.Generic;

using System.Data;

namespace Letao.IDAL

{

    /// <summary>

    /// 接口：类别字典键

    /// </summary>

    public interface IDictBase

    {

　　　　#region  获取

        /// <summary>

        /// 获取所有字典键名

        /// </summary>

        List<Model.DictBase> Get();

        #endregion

    }

}

接口：类别字典名称

DALFactory层（DataAccess新建接口实现工厂模式CreateDictBase）

using System;

using System.Reflection;

using System.Configuration;

using Letao.IDAL;

namespace Letao.DALFactory

{

    /// <summary>

    /// 工厂反射层

    /// </summary>

    public sealed class DataAccess

    {

        private static readonly string AssemblyPath = ConfigurationManager.AppSettings["SQLServerDAL"];

 /// <summary>

        /// 类别字典键名

        /// </summary>

        public static Letao.IDAL.IDictBase CreateDictBase()

        {

            string ClassNamespace = AssemblyPath + ".DictBase";

            return (IDictBase)Assembly.Load(AssemblyPath).CreateInstance(ClassNamespace);

        }

    }

}

工厂接口：类别字典名称

SQLServerDAL层（在项目中添加类DictBase，继承自接口IDictBase，实现的方法是向控制台输出所有字典键名）

using System;

using System.Data;

using System.Data.SqlClient;

using System.Collections.Generic;

namespace Letao.SQLServerDAL

{

    /// <summary>

    /// 类别字典键

    /// </summary>

    class DictBase:IDAL.IDictBase

    {

　　　　 #region 获取

        /// <summary>

        /// 获取所有字典键名

        /// </summary>

        public List<Model.DictBase>  Get()

        {

            string sql = string.Format("select * from DictBase");

            List<Model.DictBase> list = new List<Model.DictBase>();

            using(SqlDataReader dr=DBUtility.DbHelperSQL.ExecuteReader(ConnString.connReadonly,CommandType.Text,sql,null))

            {

                while(dr.Read())

                {

                    Model.DictBase model = new Model.DictBase();

                    object obj;

                    obj = dr["Id"];

                    if(obj!=null &&obj!=DBNull.Value)

                    {

                        model .Id =(int )obj;

                    }

                    model.Name = dr["Name"].ToString();

                    list.Add(model );

                }

            }

            return list;

        }

    }

}

BLL层（创建一个DictBase的工厂类，使它继承自CreateDictBase接口，创建一个方法，使它的返回值为IDictBase，最后在方法的实现里面返回实例化的DictBase）

using System;

using System.Collections.Generic;

namespace Letao.BLL

{

    /// <summary>

    /// 类别字典键

    /// </summary>

    public  class DictBase

    {

        protected static Letao.IDAL.IDictBase dal =Letao .DALFactory .DataAccess.CreateDictBase();

 /// <summary>

        /// 获取所有字典键名

        /// </summary>

        public static List<Model.DictBase> Get()

        {

            return dal.Get();

        }

    }

}

具体工厂类

Model层（DictBase类）

using System;

namespace Letao.Model

{

    /// <summary>

    /// 类别字典键

    /// </summary>

    public  class DictBase

    {

        /// <summary>

        /// 字典健名编号

        /// </summary>

        public int Id { get; set; }

        /// <summary>

        /// 字典关键字

        /// </summary>

        public string Name{ get; set; }

    }

}

具体类别字典键名类

备注：

DBUtility层（DbHelperSQL类、ConnString类）

using System;

using System.Data;

using System.Data.SqlClient;

namespace Letao.DBUtility

{

    public class DbHelperSQL

    {

 　　　　/// <summary>

        /// 返回数据内容（SqlDataReader）

        /// </summary>

        /// <param name="connectionString">数据库连接字符串</param>

        /// <param name="cmdType">执行类型（过程/语句）</param>

        /// <param name="cmdText">执行的过程名称/查询语句</param>

        /// <param name="commandParameters">command执行的SqlParameter[]数组</param>

        /// <returns>SqlDataReader 返回执行后的数据内容</returns>

        public static SqlDataReader ExecuteReader(string connectionString, CommandType cmdType, string cmdText, params SqlParameter[] commandParameters)

        {

            SqlCommand cmd = new SqlCommand();

            SqlConnection conn = new SqlConnection(connectionString);

            try

            {

                PrepareCommand(cmd, conn, null, cmdType, cmdText, commandParameters);

                SqlDataReader rdr = cmd.ExecuteReader(CommandBehavior.CloseConnection);

                cmd.Parameters.Clear();

                return rdr;

            }

            catch

            {

                //Close(conn);

                //Dispose(conn);

                throw;

            }

        }

    }

}

返回数据内容（SqlDataReader）

using System;

namespace Letao.SQLServerDAL

{

    /// <summary>

    /// 数据库连接字符串

    /// </summary>

    class ConnString

    {

        /// <summary>

        /// 读写数据库连接字符串

        /// </summary>

        public static string conn

        {

            get

            {

                if (_conn == null)

                {

                    _conn = System.Configuration.ConfigurationManager.ConnectionStrings["ConnectionStringWrite"].ToString();

                }

                return _conn;

            }

        }

        private static string _conn = null;

        /// <summary>

        /// 只读数据库连接字符串

        /// </summary>

        public static string connReadonly

        {

            get

            {

                if (_connReadonly == null)

                {

                    _connReadonly = System.Configuration.ConfigurationManager.ConnectionStrings["ConnectionStringReadonly"].ToString();

                }

                return _connReadonly;

            }

        }

        private static string _connReadonly = null;

    }

}

只读数据库连接字符串

接下来添加App.config文件，来实现系统所选择的数据库是什么数据库

<?xml version="1.0"?>

<appSettings>

    <add key="title" value="乐淘"/>

    <add key="SQLServerDAL" value ="Letao.SQLServerDAL"/>

</appSettings>

App.config文件

页面

<html xmlns="http://www.w3.org/1999/xhtml">

<head runat="server">

    <title></title>

    <script src="/Admin/Scripts/jquery-1.8.3.min.js" type="text/javascript"></script>

</head>

<body>

    <div>

     <ul id="dictbase"></ul>

    </div>

    <script type="text/javascript">

        $(function () {

            $.getJSON('test.aspx?action=get', function (json) {

                var _html = [];

                for (var key in json) {

                    _html.push('<li data="' + json[key].Id + '">' + json[key].Name + '</li>');

                }

                $("#dictbase").empty().append(_html.join(''));

            });

        })

    </script>

</body>

</html>

 protected void Page_Load(object sender, EventArgs e)

    {

        if (Request.Headers["X-Requested-With"] != null && Request.Headers["X-Requested-With"].ToLower() == "XMLHttpRequest".ToLower())

        {

            Response.Clear();

            Response.ContentType = "application/json";

            if (Request["action"] == "get")

                Response.Write(GetJson());

            Response.End();

        }

    }

    protected string GetJson()

    {

        System.Web.Script.Serialization.JavaScriptSerializer json = new System.Web.Script.Serialization.JavaScriptSerializer();

        List<Letao.Model.DictBase> list = new List<Letao.Model.DictBase>();

        list = Letao.BLL.DictBase.Get();

        return json.Serialize(list);

    }