Skip to content

Monthly Archives: 五月 2014

Json.NET 反序列化 多层次 泛型 复杂对象 json(如List)到数据实体对象

做了一天有关于JSON的工作,解析为JSON难度到不大,用.Net中微软自己的方法也很好用。

多层次泛型复杂对象(不是简单的 List<T> 而是  List<<List<T>>>)到数据实体对象,花了大半天时间从下午到晚上,一直研究到快凌晨。。。

最后还是放弃微软的方法。使用了 Json.NET 的 Newtonsoft.Json.dll 来反序列化。

 

随便推荐一个网站: 在线JSON校验格式化工具(K JSON) – json解析,json格式化,json 在线校验  http://www.kjson.com/

可以把要反解析的json放进去效验一下,避免错误格式,比如我今天遇到几次收到的 json 压根就是格式不正确的json … 非常耽误时间

 

下面给个例子供参考:

( 例子是在Handler1.ashx中做的,首先引用 “using Newtonsoft.Json;”)

using System;
using System.Collections.Generic;
using System.Web;
using System.IO;
using Newtonsoft.Json;

namespace WebApp1
{
    /// <summary>
    /// Handler1 的摘要说明
    /// </summary>
    public class Handler1 : IHttpHandler
    {
        public void ProcessRequest(HttpContext context)
        {
            string strJson = @"{
                                    'success': true,
                                    'Object': {
                                        'ID': -1,
                                        'MoveID': 'D09-242',
                                        'EX_Unit': 00,
                                        'In_Unit': 00,
                                        'Remark': 'OK',
                                        'Detail': [
                                            {
                                                'ID': 1,
                                                'M_ID': null,
                                                'DVID': '11',
                                                'DVName': 'aa0',
                                                'DVType': null
                                            },
                                            {
                                                'ID': 2,
                                                'M_ID': null,
                                                'DVID': '22',
                                                'DVName': 'aa1',
                                                'DVType': null
                                            },
                                            {
                                                'ID': 3,
                                                'M_ID': null,
                                                'DVID': '33',
                                                'DVName': 'aa2',
                                                'DVType': null
                                            }
                                        ]
                                    },
                                    'msg': '成功'
                                }";
            ///大{}内 JSONObject 数据, 最外层
            JSONObject<MoveInfo<MoveDetailInfo>> obj = Newtonsoft.Json.JsonConvert.DeserializeObject<JSONObject<MoveInfo<MoveDetailInfo>>>(strJson);
            string msg = obj.msg;
            string success = obj.success.ToString();

            ///'Object'是实体对象类"MoveInfo"的实体类的数据
            MoveInfo<MoveDetailInfo> info = obj.Object;
            int Mv_id1 = info.ID;   //可以直接赋值给MoveInfo的对象获取到值
            int Mv_id2 = obj.Object.ID; //还可以用上层的Object.ID获取到值

            ///info.Detail 或 obj.Object.Detail 都是实体对象类"MoveDetailInfo"的实体类的数据
            string DVName1 = "";
            string DVName2 = "";
            string DVName3 = "";

            DVName1 = obj.Object.Detail[0].DVName;      //方法1: 从最上次对象实体中取子属性

            foreach(MoveDetailInfo mvinfo in info.Detail)
                DVName2 += mvinfo.DVName;       //方法2: 迭代上层 info.Detail 对象"MoveDetailInfo"获取属性

            MoveDetailInfo dvinfo =  info.Detail[0];
            DVName3 = dvinfo.DVName;        //方法3: 再赋值给MoveDetailInfo对象类后获取

            context.Response.ContentType = "text/plain";
            context.Response.Write(string.Format("0;{0}\r1:{1};\r2:{2};", DVName1, DVName2, DVName3));
        }

        public bool IsReusable
        {
            get
            {
                return false;
            }
        }
    }

    public class JSONObject<T>
    {
        private bool _success;
        /// <summary>
        /// 是否成功
        /// </summary>
        public bool success
        {
            get { return _success; }
            set { _success = value; }
        }

        private T _Object;
        /// <summary>
        /// 业务实体对象
        /// </summary>
        public T Object
        {
            get { return _Object; }
            set { _Object = value; }
        }

        private string _msg;
        /// <summary>
        /// 消息
        /// </summary>
        public string msg
        {
            get { return _msg; }
            set { _msg = value; }
        }
    }

    /// <summary>
    /// MoveInfo 调拨单
    /// </summary>
    public class MoveInfo<T>
    {
        private int _ID;
        public int ID
        {
            get { return _ID; }
            set { _ID = value; }
        }

        private string _MoveID;
        public string MoveID
        {
            get { return _MoveID; }
            set { _MoveID = value; }
        }

        private int _EX_Unit;
        public int EX_Unit
        {
            get { return _EX_Unit; }
            set { _EX_Unit = value; }
        }

        private int _In_Unit;
        public int In_Unit
        {
            get { return _In_Unit; }
            set { _In_Unit = value; }
        }

        private List<MoveDetailInfo> _Detail;
        public List<MoveDetailInfo> Detail
        {
            get { return _Detail; }
            set { _Detail = value; }
        }
    }

    /// <summary>
    /// 调拨明细信息
    /// </summary>
    public class MoveDetailInfo
    {
        private int _ID;
        public int ID
        {
            get { return _ID; }
            set { _ID = value; }
        }

        private string _M_ID;
        public string M_ID
        {
            get { return _M_ID; }
            set { _M_ID = value; }
        }

        private string _DVID;
        public string DVID
        {
            get { return _DVID; }
            set { _DVID = value; }
        }

        private string _DVName;
        public string DVName
        {
            get { return _DVName; }
            set { _DVName = value; }
        }

        private string _DVType;
        public string DVType
        {
            get { return _DVType; }
            set { _DVType = value; }
        }
    }
}

 

参考文章 :

.net泛型在序列化、反序列化JSON数据中的应用  http://www.cnblogs.com/jdmei520/archive/2009/09/19/1569600.html

C# 将javascript的JSON反序列化为数组,泛型List,对象  –  http://hi.baidu.com/jiang_yy_jiang/item/c32aff05bcce12ca915718e8

 

 

些许会常用到的sql语句

这里记录些许个会经常用到的sql语句。

 

P.S.   [Your_Table]  = 你的表名, [nid] = 你表中的主键唯一列.

/*按分页数量取表记录中的数据*/
/*以nid排序, 取20条之后的10条*/SELECT TOP 10 *
FROM Your_Table
WHERE (nid NOT IN
          (SELECT TOP 20 nid
         FROM Your_Table
         ORDER BY nid))
ORDER BY nid
/*取传入条件记录的前一条数据*/
/*取 nid='533' 记录的前一条, 返回记录 nid='532'*/
SELECT *
FROM Your_Table
WHERE (nid =
          (SELECT MAX(nid)
         FROM Your_Table
         WHERE nid < 533))
/*取传入条件记录的后一条数据*/
/*取 nid='533' 记录的后一条, 返回记录 nid='532'*/		 
SELECT *
FROM Your_Table
WHERE (nid =
          (SELECT MIN(nid)
         FROM Your_Table
         WHERE nid > 534))
		 
		 

 

分页存储过程,SQL 2000 数据库下使用的:

-- =============================================

-- DocumentName       分页存储过程 SQL 2000数据库下使用的

-- 描述               利用SQL查询语句进行分页

-- 输入

--       {

--        @SQL            :    SQL查询语句,示例:'Select * from [TableName]'

--        @Order        :    排序,示例:[ColumnName] [ASC | DESC]

--        @CurPage        :    当前页,示例:0..9

--        @PageRows        :    每页显示的行数,示例:0..9

--        @TotalRecorder:    查询记录总数(输出参数)

--          @IsXML        :    表示返回的结果,0表示以表格形式记录,1表示以XML格式返回记录;默认为0

--       }

-- =============================================



ALTER PROCEDURE [dbo].[SeparatePage]

    -- Add the parameters for the stored procedure here

    @SQL Nvarchar(2000),

    @Order Nvarchar(20),

    @CurPage int,

    @PageRows int,

    @TotalRecorder int output,

    @IsXML bit = 0

AS

BEGIN

    -- SET NOCOUNT ON added to prevent extra result sets from

    SET NOCOUNT ON;

    declare @ExceSQL nvarchar(4000)

    

    --设置开始行号

    declare  @start_row_num AS int

    SET @start_row_num = (@CurPage - 1) * @PageRows

   

    if @CurPage > 1 

        BEGIN

            SET @start_row_num = @start_row_num + 1;

            SET @PageRows = @PageRows - 1

        END

    else

        SET @start_row_num = @start_row_num

    

    --获取总记录数

    set @ExceSQL = 'SELECT tb.* into [tb_Temp] FROM ('+ @SQL +') tb ORDER BY'+ @order

    execute(@ExceSQL)

    SELECT @TotalRecorder = COUNT(*) from [tb_Temp]



    ALTER TABLE [tb_Temp] add [RowNumber] int



    --设置查询语句

    SET @ExceSQL = 'SELECT TOP '+ convert(varchar(10),@PageRows) +' * 

                    FROM (SELECT TOP '+ convert(varchar(10),@PageRows) +' * 

                    FROM (SELECT TOP '+ convert(varchar(10),@TotalRecorder-@start_row_num+1) +' *

                    FROM [tb_Temp] ORDER BY '+ REPLACE(@Order,'DESC','ASC') +') AS a

                    ORDER BY '+ REPLACE(@Order,'ASC','DESC') +') AS b'



    IF(@IsXML = 1)

        SET @ExceSQL = @ExceSQL + ' FOR XML AUTO,ELEMENTS'

 

    EXECUTE(@ExceSQL)

    DROP Table [tb_Temp]

END

 

分页存储过程,SQL 2005 数据库下使用的:

set ANSI_NULLS ON

set QUOTED_IDENTIFIER ON

go


-- =============================================

-- DocumentName       分页存储过程  SQL 2005数据库下使用

-- 描述                 利用SQL查询语句进行分页

-- 输入

--       {

--        @SQL            :    SQL查询语句,示例:'Select * from [TableName]'

--        @Order        :    排序,示例:[ColumnName] [ASC | DESC]

--        @CurPage        :    当前页,示例:0..9

--        @PageRows        :    每页显示的行数,示例:0..9

--        @TotalRecorder:    查询记录总数(输出参数)

--          @IsXML        :    表示返回的结果,0表示以表格形式记录,1表示以XML格式返回记录;默认为0

--       }

-- =============================================



ALTER PROCEDURE [dbo].[SeparatePage]

    -- Add the parameters for the stored procedure here

    @SQL Nvarchar(2000),

    @Order Nvarchar(20),

    @CurPage int,

    @PageRows int,

    @TotalRecorder int output,

    @IsXML bit = 0

AS

BEGIN

    -- SET NOCOUNT ON added to prevent extra result sets from

    SET NOCOUNT ON;

    declare @ExceSQL nvarchar(4000)

    

    --设置开始行号

    declare  @start_row_num AS int

    SET @start_row_num = (@CurPage - 1) * @PageRows

   

    if @CurPage > 1 

        BEGIN

            SET @start_row_num = @start_row_num + 1;

            SET @PageRows = @PageRows - 1

        END

    else

        SET @start_row_num = @start_row_num

    

    --设置标签语句

    declare @RowNumber nvarchar(100)

    set @RowNumber = ', ROW_NUMBER() OVER(ORDER BY ' + @Order + ') as RowNumber from '



    set @SQL = Replace(@SQL,' from ',@RowNumber)



    --获取总记录数

    set @ExceSQL = 'WITH [TempTable] AS (' + @SQL + ') 

        select @TotalRecorder=max(RowNumber) from [TempTable]'



    execute sp_executesql @ExceSQL,N'@TotalRecorder int output',@TotalRecorder output



    --设置查询语句

    set @ExceSQL = 'WITH [TempTable] AS (' + @SQL + ') 

        select * from [TempTable] where RowNumber between ' + Convert(nvarchar,@start_row_num)

        + ' And ' + Convert(nvarchar,@start_row_num+@PageRows)

    

    IF(@IsXML = 1)SET @ExceSQL = @ExceSQL + 'FOR XML AUTO,ELEMENTS'

 

    execute(@ExceSQL)



END

 

查询获取表中, 字段,数据类型的sql

存档一个今天用到的sql,  可以用来查询表中, 字段, 数据类型的sql.

SELECT OBJECT_NAME(c.id) AS 表名, c.name AS 字段名, t.name AS 数据类型, 
      c.prec AS 长度
FROM syscolumns c INNER JOIN
      systypes t ON c.xusertype = t.xusertype
WHERE (objectproperty(c.id, 'IsUserTable') = 1) AND 
      (c.id = OBJECT_ID('你的表名'))

 

如果要查视图的话, 要修改一下where条件,  把where的objectproperty放到上面增加一个case判断,  AS 出类型

SELECT     CASE WHEN (OBJECTPROPERTY(c.id, 'IsUserTable') = 1) THEN 'Table' WHEN (OBJECTPROPERTY(c.id, 'IsView') = 1) THEN 'View' END AS 类型, OBJECT_NAME(c.id) 
                      AS 表名, c.name AS 字段名, t.name AS 数据类型, c.prec AS 长度
FROM         sys.syscolumns AS c INNER JOIN
                      sys.systypes AS t ON c.xusertype = t.xusertype
WHERE     (c.id = OBJECT_ID('View_1'))

(OBJECTPROPERTY(c.id, ‘IsUserTable’) = 1) 是表,  (OBJECTPROPERTY(c.id, ‘IsView’) = 1) 是视图

关于 OBJECTPROPERTY 的详细用法和参数, 请查看: http://technet.microsoft.com/zh-cn/library/ms176105(v=sql.90).aspx

C# 中的 Json

一直是做的winform多一些, 可最近做的活儿都是常用JSON。

项目的服务交互是 jquery easyui , Handler.ashx做的, 后台是工具配置的数据.

 

fastJSON -> fastJSON.dll

Json.NET -> Newtonsoft.Json.dll

DataContractJsonSerializer -> System.Runtime.Serialization.Json

速度上对比:
fastJSON  > Json.NET > Windows.Data.Json 
参考:
http://james.newtonking.com/json/help/html/JsonNetVsDotNetSerializers.htm
http://james.newtonking.com/json/help/html/JsonNetVsWindowsDataJson.htm