一.准备需转为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(); } }
开源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()); } } } } }
三.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转竖表
运行后结果(转横表)
运行后结果(转竖表)