一、配置文件:GetJson.exe.config
<?xml version="1.0" encoding="utf-8" ?>
<configuration>
<appSettings>
<!--连接串 -->
<add key="ConnString" value="Data Source=(local)\sqlserver2014;Initial Catalog=master;Integrated Security=True;" />
<!--取数据 sql , 大于用 > 代替, 小于用 < 代替( 不明白的请搜索下转义 ) -->
<add key="SQL" value="select [number] from master.dbo.spt_values WHERE [TYPE]='P' AND number < 25"/>
<!--输出的json文件 -->
<add key="FileName" value="myFile.json" />
<!--输出的路径 -->
<add key="Directory" value="d:\" />
<!--是否调试状态 (1/0) 。为1时输出信息并且最终会暂停,为0时不输出信息且操作完不暂停 -->
<add key="Debug" value="1"/>
</appSettings>
</configuration>
二、C#控制台主要代码:
using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using Util;
using System.IO;
using System.Threading;
using System.Data;
using System.Data.SqlClient;
namespace GetJson
{
class Program
{
static bool debug = true;
static void Main(string[] args)
{
try
{
string connString = System.Configuration.ConfigurationSettings.AppSettings["ConnString"];
string sql = System.Configuration.ConfigurationSettings.AppSettings["SQL"];
string fileName = System.Configuration.ConfigurationSettings.AppSettings["FileName"];
string directory = System.Configuration.ConfigurationSettings.AppSettings["Directory"];
string fullPath = string.Format("{0}\\{1}", directory, fileName);
debug = System.Configuration.ConfigurationSettings.AppSettings["Debug"] == "1";
ConsoleWriteLine("配置项:");
ConsoleWriteLine("SQL: {0}", sql);
ConsoleWriteLine("FileName: {0}", fileName);
ConsoleWriteLine("Directory: {0}", directory);
ConsoleWriteLine("输出完整路径: {0}\r\n" , fullPath );
DataTable dt = new DataTable();
dt.TableName = fileName;
DateTime beginT = DateTime.Now;
for (int i = 1; i <= 10; i++)
{
Console.WriteLine("第 {0} 次试导出json", i);
try
{
using (SqlConnection conn = new SqlConnection(connString))
{
conn.Open();
ConsoleWriteLine("1.连接成功!");
SqlCommand cmd = new SqlCommand(sql, conn);
SqlDataAdapter adapter = new SqlDataAdapter(cmd);
adapter.Fill(dt);
ConsoleWriteLine("2.取数据成功!");
string json = JsonHelper.DataTableToJson(dt);
File.WriteAllText(fullPath, json);
ConsoleWriteLine("3.已输出文件至: {0} , 消耗秒数: {1} ", fullPath, DateTime.Now.Subtract(beginT).TotalSeconds);
}
break;
}
catch (Exception ex)
{
Console.WriteLine("第 {0} 次试导出失败,原因:{1}", i, ex.Message);
Thread.Sleep(30*1000);
}
}
ConsoleWriteLine("End");
if (debug)
{
Console.Read();
}
}
catch (Exception ex)
{
Console.WriteLine(ex.Message);
}
}
static void ConsoleWriteLine(string formatStr, params object[] objArr)
{
if (debug)
{
Console.WriteLine(formatStr, objArr);
}
}
}
}
三、JsonHelper:
using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Data;
using System.IO;
using Newtonsoft.Json;
using System.Web;
using System.Reflection;
using System.Collections;
using System.Text.RegularExpressions;
//缺少编译器要求的成员“ystem.Runtime.CompilerServices.ExtensionAttribute..ctor”
namespace System.Runtime.CompilerServices
{
public class ExtensionAttribute : Attribute { }
}
namespace Util
{
/// <summary>
/// Author : yenange
/// Date : 2013-12-11
/// Description : json 辅助类
/// </summary>
public class JsonHelper
{
/// <summary>
/// DataSet 转 JSON . add by ngye, on 2013-02-18.
/// </summary>
/// <param name="ds">需要转换的数据源ds</param>
/// <returns>json字符串</returns>
public static string DataSetToJson(DataSet ds)
{
string json = string.Empty;
if (ds == null)
return json;
StringBuilder sb = new StringBuilder();
StringWriter sw = new StringWriter(sb);
try
{
using (JsonWriter jw = new JsonTextWriter(sw))
{
JsonSerializer ser = new JsonSerializer();
jw.WriteStartArray();
foreach (DataTable dt in ds.Tables)
{
jw.WriteStartObject();
jw.WritePropertyName(dt.TableName);
jw.WriteStartArray();
foreach (DataRow dr in dt.Rows)
{
jw.WriteStartObject();
foreach (DataColumn dc in dt.Columns)
{
jw.WritePropertyName(dc.ColumnName);
ser.Serialize(jw, dr[dc].ToString());
}
jw.WriteEndObject();
}
jw.WriteEndArray();
jw.WriteEndObject();
}
jw.WriteEndArray();
sw.Close();
jw.Close();
}
}
catch (Exception ex)
{
return ex.Message;
}
return sb.ToString();
}
/// <summary>
/// DataTable 转 JSON
/// </summary>
/// <param name="atable_data">数据源DataTable</param>
/// <returns>返回DataTable转换成功的Json格式</returns>
///
public static string DataTableToJson(DataTable dt)
{
string s_jsonstring = string.Empty;
//参数检测
if (dt == null)
return s_jsonstring;
StringBuilder sb = new StringBuilder();
StringWriter sw = new StringWriter(sb);
using (JsonWriter jw = new JsonTextWriter(sw))
{
JsonSerializer ser = new JsonSerializer();
jw.WriteStartArray();
foreach (DataRow dr in dt.Rows)
{
jw.WriteStartObject();
foreach (DataColumn dc in dt.Columns)
{
jw.WritePropertyName(dc.ColumnName);
ser.Serialize(jw, dr[dc].ToString());
}
jw.WriteEndObject();
}
jw.WriteEndArray();
sw.Close();
jw.Close();
}
return sb.ToString();
}
/// <summary>
/// 把DataRow转换为Json格式
/// </summary>
/// <param name="dr">行记录信息</param>
/// <returns>返回DataRow转换成功的Json格式</returns>
public static string DataRowToJson(DataRow dr)
{
string jsonString = string.Empty;
//参数检测
if (dr == null)
return jsonString;
StringBuilder sb = new StringBuilder();
StringWriter sw = new StringWriter(sb);
DataTable tableData = dr.Table;
using (JsonWriter jw = new JsonTextWriter(sw))
{
JsonSerializer ser = new JsonSerializer();
jw.WriteStartArray();
jw.WriteStartObject();
foreach (DataColumn dc in tableData.Columns)
{
jw.WritePropertyName(dc.ColumnName);
ser.Serialize(jw, dr[dc].ToString());
}
jw.WriteEndObject();
jw.WriteEndArray();
sw.Close();
jw.Close();
}
return sb.ToString();
}
#region Json的序列化和反序列化
/// <summary>
///
/// </summary>
/// <param name="obj"></param>
/// <returns></returns>
public static string ToJson(object obj)
{
string strJson = string.Empty;
try
{
strJson = Newtonsoft.Json.JsonConvert.SerializeObject(obj, new DateTimeJsonConverter(), new LongJsonConverter());
}
catch (Exception ex)
{
//NengLong.CMP.Log.Base.Logging.Logger.Current.Fatal(ex);
}
return strJson;
}
/// <summary>
///
/// </summary>
/// <typeparam name="T"></typeparam>
/// <returns></returns>
public static T FromJson<T>(string sJson)
{
try
{
return Newtonsoft.Json.JsonConvert.DeserializeObject<T>(sJson, new DateTimeJsonConverter(), new LongJsonConverter());
}
catch (Exception ex)
{
//NengLong.CMP.Log.Base.Logging.Logger.Current.ErrorFormat("sJson={0}", ex, sJson);
//NengLong.CMP.Log.Base.Logging.Logger.Current.Fatal(ex);
}
return default(T);
}
#endregion
private class DateTimeJsonConverter : Newtonsoft.Json.JsonConverter
{
public override bool CanConvert(Type objectType)
{
return typeof(DateTime).IsAssignableFrom(objectType);
}
public override void WriteJson(Newtonsoft.Json.JsonWriter writer, object value, Newtonsoft.Json.JsonSerializer serializer)
{
DateTime dt = (DateTime)value;
writer.WriteValue(dt.ToString("yyyy-MM-dd HH:mm:ss"));
}
public override object ReadJson(Newtonsoft.Json.JsonReader reader, Type objectType, object existingValue, Newtonsoft.Json.JsonSerializer serializer)
{
return DateTime.Parse(reader.Value.ToString());
}
}
private class LongJsonConverter : Newtonsoft.Json.JsonConverter
{
public override bool CanConvert(Type objectType)
{
return typeof(long).IsAssignableFrom(objectType);
}
public override void WriteJson(Newtonsoft.Json.JsonWriter writer, object value, Newtonsoft.Json.JsonSerializer serializer)
{
writer.WriteValue(value.ToString());
}
public override object ReadJson(Newtonsoft.Json.JsonReader reader, Type objectType, object existingValue, Newtonsoft.Json.JsonSerializer serializer)
{
long v = 0;
long.TryParse(reader.Value.ToString(), out v);
return v;
}
}
private class StringJsonConverter : Newtonsoft.Json.JsonConverter
{
public override bool CanConvert(Type objectType)
{
return typeof(string).IsAssignableFrom(objectType);
}
public override void WriteJson(Newtonsoft.Json.JsonWriter writer, object value, Newtonsoft.Json.JsonSerializer serializer)
{
writer.WriteValue(value.ToString());
}
public override object ReadJson(Newtonsoft.Json.JsonReader reader, Type objectType, object existingValue, Newtonsoft.Json.JsonSerializer serializer)
{
return System.Web.HttpUtility.UrlDecode(reader.Value.ToString());
}
}
}
}