• PCB MS SQL 存储过程(CLR) 实现Json转DataTable表的方法


     一.准备需转为DataTable的json字符串

            原json字符串数据

    [{"TechName":"ECN","TechNo":"EC_01","id":"1"},{"TechName":"流程指示","TechNo":"PI_01","id":"2"},{"TechName":"计划投料","TechNo":"FP_01","id":"3"},{"TechName":"开料","TechNo":"CC_01","id":"4"},{"TechName":"裁切","TechNo":"CC_02","id":"5"},{"TechName":"磨边","TechNo":"CC_03","id":"6"},{"TechName":"圆角","TechNo":"CC_04","id":"7"},{"TechName":"打标","TechNo":"CC_05","id":"8"},{"TechName":"烘板","TechNo":"CC_06","id":"9"},{"TechName":"钻孔","TechNo":"MD_02","id":"10"},{"TechName":"去毛刺","TechNo":"PT_01","id":"11"},{"TechName":"烘板","TechNo":"PT_05","id":"12"},{"TechName":"等离子处理","TechNo":"PT_02","id":"13"},{"TechName":"沉铜","TechNo":"PT_07","id":"14"},{"TechName":"负片电镀","TechNo":"PP_02","id":"15"},{"TechName":"磨板","TechNo":"PP_05","id":"16"},{"TechName":"外层前处理","TechNo":"OL_01","id":"17"},{"TechName":"贴膜","TechNo":"OL_03","id":"18"},{"TechName":"曝光","TechNo":"OL_04","id":"19"},{"TechName":"显影","TechNo":"OL_05","id":"20"},{"TechName":"酸性蚀刻","TechNo":"OL_06","id":"21"},{"TechName":"退膜","TechNo":"OL_07","id":"22"},{"TechName":"外层AOI","TechNo":"AO_04","id":"23"},{"TechName":"烘板","TechNo":"SM_02","id":"24"},{"TechName":"阻焊前处理","TechNo":"SM_01","id":"25"},{"TechName":"丝印","TechNo":"SM_05","id":"26"},{"TechName":"阻焊前处理","TechNo":"SM_01","id":"27"},{"TechName":"丝印","TechNo":"SM_05","id":"28"},{"TechName":"预烘","TechNo":"SM_06","id":"29"},{"TechName":"曝光","TechNo":"SM_09","id":"30"},{"TechName":"显影","TechNo":"SM_10","id":"31"},{"TechName":"终固化","TechNo":"SM_11","id":"32"},{"TechName":"喷砂","TechNo":"EN_01","id":"33"},{"TechName":"板边包胶","TechNo":"EN_02","id":"34"},{"TechName":"沉金","TechNo":"EN_04","id":"35"},{"TechName":"水洗烘干","TechNo":"EN_06","id":"36"},{"TechName":"铣板","TechNo":"FO_02","id":"37"},{"TechName":"成品清洗","TechNo":"FO_14","id":"38"},{"TechName":"小板切割","TechNo":"FO_08","id":"39"},{"TechName":"功能检查","TechNo":"FI_01","id":"40"},{"TechName":"外观检查","TechNo":"FI_02","id":"41"},{"TechName":"内包装","TechNo":"PK_01","id":"42"},{"TechName":"入库","TechNo":"DE_01","id":"43"},{"TechName":"外包装","TechNo":"DE_02","id":"44"},{"TechName":"出库","TechNo":"DE_03","id":"45"}] '

          转换后的效果(横向)

              

            转换后的效果(竖向)

             

    二.C#写SQL SERVER(CLR) json转DataTable存储过程

            利用Json反序列化为字典类型 Dictionary<string, object> key,value形式,然后再转为DataTable

      /// <summary>
            /// Json转Table
            /// </summary>
            /// <param name="jsonString"></param>
            /// <param name="isVerticalTable">是否竖表</param>
            [Microsoft.SqlServer.Server.SqlProcedure]
            public static void Json2Table(string jsonString, bool isVerticalTable)
            {
                SqlDataRecord dataRecord = new SqlDataRecord();
                var ListObject = Json.Deserialize(jsonString) as List<object>;
                var Object = Json.Deserialize(jsonString) as Dictionary<string, object>;
                if (ListObject == null && Object == null)
                    return;
                if (ListObject == null)
                {
                    ListObject = new List<object>();
                    ListObject.Add(Object);
                }
                List<SqlMetaData> sqlMetaDatas = new List<SqlMetaData>();
                Dictionary<string, object> KeyObject = (Dictionary<string, object>)ListObject[0];
                if (!isVerticalTable)
                {
                    foreach (var jProperty in KeyObject)
                    {
                        sqlMetaDatas.Add(new SqlMetaData(jProperty.Key, SqlDbType.NVarChar, 4000));
                    }
                    dataRecord = new SqlDataRecord(sqlMetaDatas.ToArray());
                    SqlContext.Pipe.SendResultsStart(dataRecord);
                    for (int i = 0; i < ListObject.Count(); i++)
                    {
                        Dictionary<string, object> DicObject = (Dictionary<string, object>)ListObject[i];
                        int j = 0;
                        foreach (var item in KeyObject)
                        {
                            dataRecord.SetString(j, DicObject[item.Key].ToStringJson());
                            j++;
                        }
                        SqlContext.Pipe.SendResultsRow(dataRecord);
                    }
                    SqlContext.Pipe.SendResultsEnd();
                }
                else
                {
                    dataRecord = new SqlDataRecord(new SqlMetaData[] { new SqlMetaData("id", SqlDbType.Int), new SqlMetaData("key", SqlDbType.NVarChar, 4000), new SqlMetaData("value", SqlDbType.NVarChar, 4000) });
                    SqlContext.Pipe.SendResultsStart(dataRecord);
                    for (int i = 0; i < ListObject.Count(); i++)
                    {
                        Dictionary<string, object> DicObject = (Dictionary<string, object>)ListObject[i];
                        foreach (var item in DicObject)
                        {
                            dataRecord.SetInt32(0, i + 1);
                            dataRecord.SetString(1, item.Key.ToString());
                            dataRecord.SetString(2, item.Value.ToStringJson());
                            SqlContext.Pipe.SendResultsRow(dataRecord);
                        }
                    }
                    SqlContext.Pipe.SendResultsEnd();
                }
                SqlContext.Pipe.Send("ok");
            }
        public static class StaticMethod
        {
            public static string ToStringJson(this object thisObject)
            {
                if (thisObject == null)
                    return "";
                if (thisObject is Dictionary<string,object>)
                    return Json.Serialize(thisObject);
                else if (thisObject is List<object>)
                    return Json.Serialize(thisObject);
                return thisObject.ToString();
            }
    
        }
    View Code

      开源MiniJSON类解析Json字符串 

    using System;
    using System.Collections;
    using System.Collections.Generic;
    using System.IO;
    using System.Linq;
    using System.Text;
    
    namespace SQLClr
    {
        /// <summary>
        /// This class encodes and decodes JSON strings.
        /// Spec. details, see http://www.json.org/
        ///
        /// JSON uses Arrays and Objects. These correspond here to the datatypes IList and IDictionary.
        /// All numbers are parsed to doubles.
        /// </summary>
        public static class Json
        {
            /// <summary>
            /// Parses the string json into a value
            /// </summary>
            /// <param name="json">A JSON string.</param>
            /// <returns>An List<object>, a Dictionary<string, object>, a double, an integer,a string, null, true, or false</returns>
            //反序列化
            public static object Deserialize(string json)
            {
                // save the string for debug information
                if (json == null)
                {
                    return null;
                }
    
                return Parser.Parse(json);
            }
            //阻止其他类从该类继承
            sealed class Parser : IDisposable
            {
                const string WORD_BREAK = "{}[],:"";
    
                public static bool IsWordBreak(char c)
                {
                    //     如果 c 是空白,则为 true;否则,为 false;报告指定 Unicode 字符在此字符串中的第一个匹配项的索引。
                    return Char.IsWhiteSpace(c) || WORD_BREAK.IndexOf(c) != -1;
                }
    
                enum TOKEN
                {
                    NONE,
                    CURLY_OPEN,
                    CURLY_CLOSE,
                    SQUARED_OPEN,
                    SQUARED_CLOSE,
                    COLON,
                    COMMA,
                    STRING,
                    NUMBER,
                    TRUE,
                    FALSE,
                    NULL
                };
                //     实现从字符串进行读取的 System.IO.TextReader。
                StringReader json;
    
                Parser(string jsonString)
                {
                    json = new StringReader(jsonString);
                }
    
                public static object Parse(string jsonString)
                {
                    using (var instance = new Parser(jsonString))
                    {
                        return instance.ParseValue();
                    }
                }
                //释放
                public void Dispose()
                {
                    json.Dispose();
                    json = null;
                }
    
                Dictionary<string, object> ParseObject()
                {
                    Dictionary<string, object> table = new Dictionary<string, object>();
    
                    // ditch opening brace
                    json.Read();
    
                    // {
                    while (true)
                    {
                        switch (NextToken)
                        {
                            case TOKEN.NONE:
                                return null;
                            case TOKEN.COMMA:
                                continue;
                            case TOKEN.CURLY_CLOSE:
                                return table;
                            default:
                                // name
                                string name = ParseString();
                                if (name == null)
                                {
                                    return null;
                                }
    
                                // :
                                if (NextToken != TOKEN.COLON)
                                {
                                    return null;
                                }
                                // ditch the colon
                                json.Read();
    
                                // value
                                table[name] = ParseValue();
                                break;
                        }
                    }
                }
    
                List<object> ParseArray()
                {
                    List<object> array = new List<object>();
    
                    // ditch opening bracket
                    json.Read();
    
                    // [
                    bool parsing = true;
                    while (parsing)
                    {
                        TOKEN nextToken = NextToken;
    
                        switch (nextToken)
                        {
                            case TOKEN.NONE:
                                return null;
                            case TOKEN.COMMA:
                                continue;
                            case TOKEN.SQUARED_CLOSE:
                                parsing = false;
                                break;
                            default:
                                object value = ParseByToken(nextToken);
    
                                array.Add(value);
                                break;
                        }
                    }
    
                    return array;
                }
    
                object ParseValue()
                {
                    TOKEN nextToken = NextToken;
                    return ParseByToken(nextToken);
                }
    
                object ParseByToken(TOKEN token)
                {
                    switch (token)
                    {
                        case TOKEN.STRING:
                            return ParseString();
                        case TOKEN.NUMBER:
                            return ParseNumber();
                        case TOKEN.CURLY_OPEN:
                            return ParseObject();
                        case TOKEN.SQUARED_OPEN:
                            return ParseArray();
                        case TOKEN.TRUE:
                            return true;
                        case TOKEN.FALSE:
                            return false;
                        case TOKEN.NULL:
                            return null;
                        default:
                            return null;
                    }
                }
    
                string ParseString()
                {
                    StringBuilder s = new StringBuilder();
                    char c;
    
                    // ditch opening quote
                    json.Read();
    
                    bool parsing = true;
                    while (parsing)
                    {
    
                        if (json.Peek() == -1)
                        {
                            parsing = false;
                            break;
                        }
    
                        c = NextChar;
                        switch (c)
                        {
                            case '"':
                                parsing = false;
                                break;
                            case '\':
                                if (json.Peek() == -1)
                                {
                                    parsing = false;
                                    break;
                                }
    
                                c = NextChar;
                                switch (c)
                                {
                                    case '"':
                                    case '\':
                                    case '/':
                                        s.Append(c);
                                        break;
                                    case 'b':
                                        s.Append('');
                                        break;
                                    case 'f':
                                        s.Append('f');
                                        break;
                                    case 'n':
                                        s.Append('
    ');
                                        break;
                                    case 'r':
                                        s.Append('
    ');
                                        break;
                                    case 't':
                                        s.Append('	');
                                        break;
                                    case 'u':
                                        var hex = new char[4];
    
                                        for (int i = 0; i < 4; i++)
                                        {
                                            hex[i] = NextChar;
                                        }
    
                                        s.Append((char)Convert.ToInt32(new string(hex), 16));
                                        break;
                                }
                                break;
                            default:
                                s.Append(c);
                                break;
                        }
                    }
    
                    return s.ToString();
                }
    
                object ParseNumber()
                {
                    string number = NextWord;
                    // 摘要:
                    //     报告指定 Unicode 字符在此字符串中的第一个匹配项的索引。
                    //
                    // 参数:
                    //   value:
                    //     要查找的 Unicode 字符。
                    //
                    // 返回结果:
                    //     如果找到该字符,则为 value 的从零开始的索引位置;如果未找到,则为 -1。
                    if (number.IndexOf('.') == -1)
                    {
                        long parsedInt;
                        //     将数字的字符串表示形式转换为它的等效 64 位有符号整数。一个指示转换是否成功的返回值。
                        Int64.TryParse(number, out parsedInt);
                        return parsedInt;
                    }
    
                    double parsedDouble;
                    Double.TryParse(number, out parsedDouble);
                    return parsedDouble;
                }
                //
                void EatWhitespace()
                {
                    //指示指定字符串中位于指定位置处的字符是否属于空白类别。
                    while (Char.IsWhiteSpace(PeekChar))
                    {
                        json.Read();
                        //摘要:
                        //     返回下一个可用的字符,但不使用它。
                        //
                        // 返回结果:
                        //     表示下一个要读取的字符的整数,或者,如果没有更多的可用字符或该流不支持查找,则为 -1。
                        if (json.Peek() == -1)
                        {
                            break;
                        }
                    }
                }
    
                char PeekChar
                {
                    get
                    {
                        //     读取输入字符串中的下一个字符并将该字符的位置提升一个字符。
                        //
                        // 返回结果:
                        //     基础字符串中的下一个字符,或者如果没有更多的可用字符,则为 -1。
                        return Convert.ToChar(json.Peek());
                    }
                }
    
                char NextChar
                {
                    get
                    {
                        return Convert.ToChar(json.Read());
                    }
                }
    
                string NextWord
                {
                    get
                    {
                        //     表示可变字符字符串。无法继承此类。
                        StringBuilder word = new StringBuilder();
    
                        while (!IsWordBreak(PeekChar))
                        {
                            // 摘要:
                            //     在此实例的结尾追加指定 Unicode 字符的字符串表示形式。
                            //
                            // 参数:
                            //   value:
                            //     要追加的 Unicode 字符。
                            //
                            // 返回结果:
                            //     完成追加操作后对此实例的引用。
                            word.Append(NextChar);
                            //下一个字符为空
                            if (json.Peek() == -1)
                            {
                                break;
                            }
                        }
                        //
                        return word.ToString();
                    }
                }
    
                TOKEN NextToken
                {
                    get
                    {
                        EatWhitespace();
    
                        if (json.Peek() == -1)
                        {
                            return TOKEN.NONE;
                        }
    
                        switch (PeekChar)
                        {
                            case '{':
                                return TOKEN.CURLY_OPEN;
                            case '}':
                                json.Read();
                                return TOKEN.CURLY_CLOSE;
                            case '[':
                                return TOKEN.SQUARED_OPEN;
                            case ']':
                                json.Read();
                                return TOKEN.SQUARED_CLOSE;
                            case ',':
                                json.Read();
                                return TOKEN.COMMA;
                            case '"':
                                return TOKEN.STRING;
                            case ':':
                                return TOKEN.COLON;
                            case '0':
                            case '1':
                            case '2':
                            case '3':
                            case '4':
                            case '5':
                            case '6':
                            case '7':
                            case '8':
                            case '9':
                            case '-':
                                return TOKEN.NUMBER;
                        }
    
                        switch (NextWord)
                        {
                            case "false":
                                return TOKEN.FALSE;
                            case "true":
                                return TOKEN.TRUE;
                            case "null":
                                return TOKEN.NULL;
                        }
    
                        return TOKEN.NONE;
                    }
                }
            }
    
            /// <summary>
            /// Converts a IDictionary / IList object or a simple type (string, int, etc.) into a JSON string
            /// </summary>
            /// <param name="json">A Dictionary<string, object> / List<object></param>
            /// <returns>A JSON encoded string, or null if object 'json' is not serializable</returns>
            public static string Serialize(object obj)
            {
                return Serializer.Serialize(obj);
            }
    
            sealed class Serializer
            {
                StringBuilder builder;
    
                Serializer()
                {
                    //创建生成器
                    builder = new StringBuilder();
                }
                //序列化
                public static string Serialize(object obj)
                {
                    var instance = new Serializer();
    
                    instance.SerializeValue(obj);
    
                    return instance.builder.ToString();
                }
                //类型
                void SerializeValue(object value)
                {
                    IList asList;
                    IDictionary asDict;
                    string asStr;
    
                    if (value == null)
                    {
                        builder.Append("null");
                    }
                    else if ((asStr = value as string) != null)
                    {
                        SerializeString(asStr);
                    }
                    else if (value is bool)
                    {
                        builder.Append((bool)value ? "true" : "false");
                    }
                    else if ((asList = value as IList) != null)
                    {
                        SerializeArray(asList);
                    }
                    else if ((asDict = value as IDictionary) != null)
                    {
                        SerializeObject(asDict);
                    }
                    else if (value is char)
                    {
                        SerializeString(new string((char)value, 1));
                    }
                    else
                    {
                        SerializeOther(value);
                    }
                }
                //序列化对象
                void SerializeObject(IDictionary obj)
                {
                    bool first = true;
    
                    builder.Append('{');
    
                    foreach (object e in obj.Keys)
                    {
                        if (!first)
                        {
                            builder.Append(',');
                        }
    
                        SerializeString(e.ToString());
                        builder.Append(':');
    
                        SerializeValue(obj[e]);
    
                        first = false;
                    }
    
                    builder.Append('}');
                }
                // 序列化数组
                void SerializeArray(IList anArray)
                {
                    builder.Append('[');
    
                    bool first = true;
    
                    foreach (object obj in anArray)
                    {
                        if (!first)
                        {
                            builder.Append(',');
                        }
    
                        SerializeValue(obj);
    
                        first = false;
                    }
    
                    builder.Append(']');
                }
                //string
                void SerializeString(string str)
                {
                    builder.Append('"');
    
                    char[] charArray = str.ToCharArray();
                    foreach (var c in charArray)
                    {
                        switch (c)
                        {
                            case '"':
                                builder.Append("\"");
                                break;
                            case '\':
                                builder.Append("\\");
                                break;
                            case '':
                                builder.Append("\b");
                                break;
                            case 'f':
                                builder.Append("\f");
                                break;
                            case '
    ':
                                builder.Append("\n");
                                break;
                            case '
    ':
                                builder.Append("\r");
                                break;
                            case '	':
                                builder.Append("\t");
                                break;
                            default:
                                int codepoint = Convert.ToInt32(c);
                                if ((codepoint >= 32) && (codepoint <= 126))
                                {
                                    builder.Append(c);
                                }
                                else
                                {
                                    //builder.Append("\u");
                                    //builder.Append(codepoint.ToString("x4"));
                                    builder.Append(c);
                                }
                                break;
                        }
                    }
    
                    builder.Append('"');
                }
                //其他
                void SerializeOther(object value)
                {
                    // NOTE: decimals lose precision during serialization.
                    // They always have, I'm just letting you know.
                    // Previously floats and doubles lost precision too.
                    //注意:小数在序列化过程中丢失精度。
                    //他们总是有,我只是让你知道。
                    //以前失去精度和双精度浮点数。
                    if (value is float)
                    {
                        builder.Append(((float)value).ToString("R"));
                    }
                    else if (value is int
                      || value is uint
                      || value is long
                      || value is sbyte
                      || value is byte
                      || value is short
                      || value is ushort
                      || value is ulong)
                    {
                        builder.Append(value);
                    }
                    else if (value is double
                      || value is decimal)
                    {
                        builder.Append(Convert.ToDouble(value).ToString("R"));
                    }
                    else
                    {
                        SerializeString(value.ToString());
                    }
                }
            }
        }
    }
    View Code

    三.SQL服务器CLR配置(允许SQL调用.net程序)

        sp_configure 'show advanced options', 1; 
        RECONFIGURE WITH override
        GO 
        sp_configure 'clr enabled', 1; 
        RECONFIGURE WITH override
        GO
        Sp_changedbowner 'sa',true   --sa改为当前登入用户名
        alter database [dbname] set trustworthy on    --bbname 改为自己的库名

    四.注册 CLR 程序集

       create  ASSEMBLY SQLfunctionAssembly   
       FROM 'D:SQLClr.dll'      --改为自己C#写的dll路径填写
       WITH PERMISSION_SET = UNSAFE;   

            创建的.net程序集数据会写入下表:

      select * from sys.assemblies 
      select  * from sys.assembly_files

        

    五.创建存储过程

       CREATE PROCEDURE[dbo].[Json2Table] (
        @jsonString NVARCHAR(max),
        @isVerticalTable bit
        )
        WITH EXECUTE AS CALLER        
        AS
        EXTERNAL NAME [SQLfunctionAssembly].[SQLClr.SQLfunction].[Json2Table]    --[SQL程序集名].[命名空间.类名].[方法名]

    六.测试Json转DataTable存储过程

    DECLARE @jsonString VARCHAR(MAX)
    SET @jsonString = '
    [{"TechName":"ECN","TechNo":"EC_01","id":"1"},{"TechName":"流程指示","TechNo":"PI_01","id":"2"},{"TechName":"计划投料","TechNo":"FP_01","id":"3"},{"TechName":"开料","TechNo":"CC_01","id":"4"},{"TechName":"裁切","TechNo":"CC_02","id":"5"},{"TechName":"磨边","TechNo":"CC_03","id":"6"},{"TechName":"圆角","TechNo":"CC_04","id":"7"},{"TechName":"打标","TechNo":"CC_05","id":"8"},{"TechName":"烘板","TechNo":"CC_06","id":"9"},{"TechName":"钻孔","TechNo":"MD_02","id":"10"},{"TechName":"去毛刺","TechNo":"PT_01","id":"11"},{"TechName":"烘板","TechNo":"PT_05","id":"12"},{"TechName":"等离子处理","TechNo":"PT_02","id":"13"},{"TechName":"沉铜","TechNo":"PT_07","id":"14"},{"TechName":"负片电镀","TechNo":"PP_02","id":"15"},{"TechName":"磨板","TechNo":"PP_05","id":"16"},{"TechName":"外层前处理","TechNo":"OL_01","id":"17"},{"TechName":"贴膜","TechNo":"OL_03","id":"18"},{"TechName":"曝光","TechNo":"OL_04","id":"19"},{"TechName":"显影","TechNo":"OL_05","id":"20"},{"TechName":"酸性蚀刻","TechNo":"OL_06","id":"21"},{"TechName":"退膜","TechNo":"OL_07","id":"22"},{"TechName":"外层AOI","TechNo":"AO_04","id":"23"},{"TechName":"烘板","TechNo":"SM_02","id":"24"},{"TechName":"阻焊前处理","TechNo":"SM_01","id":"25"},{"TechName":"丝印","TechNo":"SM_05","id":"26"},{"TechName":"阻焊前处理","TechNo":"SM_01","id":"27"},{"TechName":"丝印","TechNo":"SM_05","id":"28"},{"TechName":"预烘","TechNo":"SM_06","id":"29"},{"TechName":"曝光","TechNo":"SM_09","id":"30"},{"TechName":"显影","TechNo":"SM_10","id":"31"},{"TechName":"终固化","TechNo":"SM_11","id":"32"},{"TechName":"喷砂","TechNo":"EN_01","id":"33"},{"TechName":"板边包胶","TechNo":"EN_02","id":"34"},{"TechName":"沉金","TechNo":"EN_04","id":"35"},{"TechName":"水洗烘干","TechNo":"EN_06","id":"36"},{"TechName":"铣板","TechNo":"FO_02","id":"37"},{"TechName":"成品清洗","TechNo":"FO_14","id":"38"},{"TechName":"小板切割","TechNo":"FO_08","id":"39"},{"TechName":"功能检查","TechNo":"FI_01","id":"40"},{"TechName":"外观检查","TechNo":"FI_02","id":"41"},{"TechName":"内包装","TechNo":"PK_01","id":"42"},{"TechName":"入库","TechNo":"DE_01","id":"43"},{"TechName":"外包装","TechNo":"DE_02","id":"44"},{"TechName":"出库","TechNo":"DE_03","id":"45"}] '
     exec dbo.Json2Table @jsonString,0  --0转横表   1转竖表

     运行后结果(转横表)

     

    运行后结果(转竖表)

  • 相关阅读:
    springboot项目打war包流程
    ant配置文件详解(转)
    如何提升java服务器并发性能
    find用法
    基姆拉尔森计算公式 推导计算星期几
    递归第二弹:分类强化
    拨钟问题
    POJ1222熄灯问题【位运算+枚举】
    POJ1013称硬币【枚举】
    4148生理周期
  • 原文地址:https://www.cnblogs.com/pcbren/p/10122287.html
Copyright © 2020-2023  润新知