ADO.NET
public static class SqlHelper
{
public static string ConStr { get; set; } = string.Empty;
/// <summary>
/// 执行一条语句
/// </summary>
/// <param name="cmd">cmd对象</param>
/// <param name="error">错误信息</param>
/// <returns>受影响行数</returns>
public static int ExecuteNonQuery(SqlCommand cmd, out string error)
{
using (var con = new SqlConnection(ConStr))
{
cmd.Connection = con;
try
{
cmd.Connection.Open();
var changeCount = cmd.ExecuteNonQuery();
error = string.Empty;
return changeCount;
}
catch (Exception e)
{
cmd.Connection.Close();
cmd.Dispose();
error = e.Message;
return 0;
}
}
}
/// <summary>
/// 执行一条语句
/// </summary>
/// <param name="cmd">cmd对象</param>
/// <param name="cmdType">操作类型是存储过程还是sql语句</param>
/// <param name="error">错误信息</param>
/// <returns>受影响行数</returns>
public static int ExecuteNonQuery(SqlCommand cmd, CommandType cmdType, out string error)
{
using (var con = new SqlConnection(ConStr))
{
cmd.Connection = con;
try
{
cmd.Connection.Open();
cmd.CommandType = cmdType;
var changeCount = cmd.ExecuteNonQuery();
error = string.Empty;
return changeCount;
}
catch (Exception e)
{
cmd.Connection.Close();
cmd.Dispose();
error = e.Message;
return 0;
}
}
}
/// <summary>
/// 执行一条语句,开启事务
/// </summary>
/// <param name="cmd">cmd对象</param>
/// <param name="cmdType">操作类型是存储过程还是sql语句</param>
/// <param name="trans">事物对象</param>
/// <param name="error">错误信息</param>
/// <returns>受影响行数</returns>
public static int ExecuteNonQuery(SqlCommand cmd, CommandType cmdType, ref SqlTransaction trans, out string error)
{
using (var con = new SqlConnection(ConStr))
{
cmd.Connection = con;
try
{
cmd.Connection.Open();
trans = trans ?? cmd.Connection.BeginTransaction();
cmd.Transaction = trans;
cmd.CommandType = cmdType;
var changeCount = cmd.ExecuteNonQuery();
error = string.Empty;
return changeCount;
}
catch (Exception e)
{
cmd.Connection.Close();
cmd.Dispose();
error = e.Message;
return 0;
}
}
}
/// <summary>
/// 返回查询结果的第一列第一行
/// </summary>
/// <param name="cmd">cmd对象</param>
/// <param name="error">错误信息</param>
/// <returns>返回值</returns>
public static string ExecuteScalar(SqlCommand cmd, out string error)
{
using (var con = new SqlConnection(ConStr))
{
cmd.Connection = con;
try
{
cmd.Connection.Open();
var obj = cmd.ExecuteScalar();
error = string.Empty;
return obj.ToString();
}
catch (Exception e)
{
cmd.Connection.Close();
cmd.Dispose();
error = e.Message;
return string.Empty;
}
}
}
/// <summary>
/// 返回 datatable格式的查询结果,有异常会返回 null
/// </summary>
/// <param name="cmd">cmd对象</param>
/// <param name="error">错误信息</param>
/// <returns>datatable格式</returns>
public static DataTable ExecuteAdapter(SqlCommand cmd, out string error)
{
using (var con = new SqlConnection(ConStr))
{
cmd.Connection = con;
try
{
cmd.Connection.Open();
SqlDataAdapter adapter = new SqlDataAdapter(cmd);
DataTable dataTable = new DataTable();
error = string.Empty;
return dataTable;
}
catch (Exception e)
{
cmd.Connection.Close();
cmd.Dispose();
error = e.Message;
return null;
}
}
}
}
DataTable 转JSON字符串 和DataTable 转 匿名类数组 可支持 Lamda Link 有 智能提示
public static class JsonHelper
{
/// <summary>
/// 转换对象时,是否大写小写或者不设置
/// </summary>
public enum ToJsonFieldCaseEnums
{
None = 0,
ToUpper = 1,
ToLower
}
/// <summary>
/// DsataTale 转换为JSON字符串, 使用包 Newtonsoft.Json来序列化生成的List达成目的
/// </summary>
/// <param name="table">Datatable对象</param>
/// <returns>JSON字符串</returns>
public static string DataTableToJson(this DataTable table)
{
return DataTableToJson(table, null, ToJsonFieldCaseEnums.None);
}
/// <summary>
/// DsataTale 转换为JSON字符串, 使用包 Newtonsoft.Json来序列化生成的List达成目的
/// </summary>
/// <param name="table">Datatable对象</param>
/// <param name="fieldCase">大小写转换或者 none 不转换 </param>
/// <returns>JSON字符串</returns>
public static string DataTableToJson(this DataTable table, ToJsonFieldCaseEnums fieldCase = ToJsonFieldCaseEnums.None)
{
return DataTableToJson(table, null, fieldCase);
}
/// <summary>
/// DsataTale 转换为JSON字符串, 使用包 Newtonsoft.Json来序列化生成的List达成目的
/// </summary>
/// <param name="table">Datatable对象</param>
/// <param name="fieldMap">对应字典,会替换掉Datatable中的列头 旧,新 </param>
/// <param name="fieldCase">大小写转换或者 none 不转换 </param>
/// <returns>JSON字符串</returns>
public static string DataTableToJson(this DataTable table, Dictionary<string, string> fieldMap, ToJsonFieldCaseEnums fieldCase = ToJsonFieldCaseEnums.None)
{
if (table == null) return null;
if (fieldCase != ToJsonFieldCaseEnums.None)
{
for (int i = 0; i < table.Columns.Count; i++)
{
if (fieldCase == ToJsonFieldCaseEnums.ToUpper)
{
table.Columns[i].ColumnName = table.Columns[i].ColumnName.ToUpper();
}
else
{
table.Columns[i].ColumnName = table.Columns[i].ColumnName.ToLower();
}
}
}
if (fieldMap != null && fieldMap.Count > 0)
{
foreach (var item in fieldMap)
{
if (!string.IsNullOrWhiteSpace(item.Key) && !string.IsNullOrWhiteSpace(item.Value))
{
if (table.Columns.Contains(item.Key))
{
table.Columns[item.Key].ColumnName = item.Value;
}
}
}
}
string tmpStr = table.ToJson();
return tmpStr;
}
/// <summary>
/// Jin 20180804 将DATATABLE转成匿名JSON对象列表
/// 将Json字符串反序列化为匿名类型,可以支持Lamda表达式,Link表达式,但是必须传入匿名类型的结构 FormateObj
/// 使用示例 var formatObj = new[]{{name = string.empty,age = 18, isFriend = true }} var data = DataTable实例对象.DataTableToJsonList(formatObj)
/// </summary>
/// <typeparam name="T">泛型如果是对象就传入new{name = string.empty,age = 18, isFriend = true } 数组格式:new[]{{name = string.empty,age = 18, isFriend = true }}</typeparam>
/// <param name="table">DataTable对象</param>
/// <param name="formatObj">结构对象,反序列化的匿名类属性将会以此为准例如 var formatObj = new[]{{name = string.empty,age = 18, isFriend = true }}</param>
/// <returns>匿名类型的对象,支持Lamda,Link,可以智能提示属性</returns>
public static T DataTableToJsonList<T>(this DataTable table, T formatObj)
{
return DataTableToJsonList(table, formatObj, null, ToJsonFieldCaseEnums.None);
}
/// <summary>
/// Jin 20180804 将DATATABLE转成匿名JSON对象列表
/// 将Json字符串反序列化为匿名类型,可以支持Lamda表达式,Link表达式,但是必须传入匿名类型的结构 FormateObj
/// 使用示例 var formatObj = new[]{{name = string.empty,age = 18, isFriend = true }} var data = DataTable实例对象.DataTableToJsonList(formatObj)
/// </summary>
/// <typeparam name="T">泛型如果是对象就传入new{name = string.empty,age = 18, isFriend = true } 数组格式:new[]{{name = string.empty,age = 18, isFriend = true }}</typeparam>
/// <param name="table">DataTable对象</param>
/// <param name="formatObj">结构对象,反序列化的匿名类属性将会以此为准例如 var formatObj = new[]{{name = string.empty,age = 18, isFriend = true }}</param>
/// <param name="fieldCase">属性名称大小写控制</param>
/// <returns>匿名类型的对象,支持Lamda,Link,可以智能提示属性</returns>
public static T DataTableToJsonList<T>(this DataTable table, T formatObj, ToJsonFieldCaseEnums fieldCase = ToJsonFieldCaseEnums.None)
{
return DataTableToJsonList(table, formatObj, null, fieldCase);
}
/// <summary>
/// Jin 20180804 将DATATABLE转成匿名JSON对象列表
/// 将Json字符串反序列化为匿名类型,可以支持Lamda表达式,Link表达式,但是必须传入匿名类型的结构 FormateObj
/// 使用示例 var formatObj = new[]{{name = string.empty,age = 18, isFriend = true }} var data = DataTable实例对象.DataTableToJsonList(formatObj)
/// </summary>
/// <typeparam name="T">泛型如果是对象就传入new{name = string.empty,age = 18, isFriend = true } 数组格式:new[]{{name = string.empty,age = 18, isFriend = true }}</typeparam>
/// <param name="table">DataTable对象</param>
/// <param name="formatObj">结构对象,反序列化的匿名类属性将会以此为准例如 var formatObj = new[]{{name = string.empty,age = 18, isFriend = true }}</param>
/// <param name="fieldMap">属性映射表</param>
/// <param name="fieldCase">属性名称大小写控制</param>
/// <returns>匿名类型的对象,支持Lamda,Link,可以智能提示属性</returns>
public static T DataTableToJsonList<T>(this DataTable table, T formatObj, Dictionary<string, string> fieldMap, ToJsonFieldCaseEnums fieldCase = ToJsonFieldCaseEnums.None)
{
if (table == null) return default(T);
if (fieldCase != ToJsonFieldCaseEnums.None)
{
for (int i = 0; i < table.Columns.Count; i++)
{
if (fieldCase == ToJsonFieldCaseEnums.ToUpper)
{
table.Columns[i].ColumnName = table.Columns[i].ColumnName.ToUpper();
}
else
{
table.Columns[i].ColumnName = table.Columns[i].ColumnName.ToLower();
}
}
}
if (fieldMap != null && fieldMap.Count > 0)
{
foreach (var item in fieldMap)
{
if (!string.IsNullOrWhiteSpace(item.Key) && !string.IsNullOrWhiteSpace(item.Value))
{
if (table.Columns.Contains(item.Key))
{
table.Columns[item.Key].ColumnName = item.Value;
}
}
}
}
string tmpStr = table.ToJson();
if (string.IsNullOrWhiteSpace(tmpStr))
{
return default(T);
}
return tmpStr.ToList(formatObj);
}
/// <summary>
/// 将Json字符串反序列化为匿名类型,可以支持Lamda表达式,Link表达式,但是必须传入匿名类型的结构 FormateObj
/// 使用示例 var formatObj = new[]{{name = string.empty,age = 18, isFriend = true }} var data = DataTable实例对象.DataTableToJsonList(formatObj)
/// </summary>
/// <typeparam name="T">泛型如果是对象就传入new{name = string.empty,age = 18, isFriend = true } 数组格式:new[]{{name = string.empty,age = 18, isFriend = true }}</typeparam>
/// <param name="jsonStr">Json字符串</param>
/// <param name="formatObj">匿名类型结构对象</param>
/// <returns>支持Lamda,Link表达式的匿名对象,可以智能提示属性</returns>
public static T ToList<T>(this string jsonStr, T formatObj)
{
return string.IsNullOrEmpty(jsonStr) ? default(T) : JsonConvert.DeserializeAnonymousType(jsonStr, formatObj);
}
/// <summary>
/// 序列化
/// </summary>
/// <param name="obj">序列化对象</param>
/// <returns>返回JSON字符串</returns>
public static string ToJson(this object obj)
{
var timeConverter = new IsoDateTimeConverter { DateTimeFormat = "yyyy-MM-dd HH:mm:ss" };
var numConverter = new JsonNumberConverter();
return JsonConvert.SerializeObject(obj, timeConverter, numConverter);
}
}