using Newtonsoft.Json; using Newtonsoft.Json.Linq; using System; using System.Collections.Generic; using System.Linq; using System.Net; using System.Net.Http; using System.Text; using System.Threading.Tasks; namespace ConsoleApp3 { class Program { static void Main(string[] args) { var queryJson = ""; var queryParam = queryJson.ToJObject(); DateTime startTime = queryParam["StartTime"].ToDate(); DateTime endTime = queryParam["EndTime"].ToDate(); //DateTime endTime = queryParam["EndTime"].ToDate().AddDays(1);//结束时间 //Ling比较 List<test> query = new List<test>(); var list = query.Where(t => t.F_LogTime>= startTime && t.F_LogTime < endTime); //SQL比较 var sql = "SELECT" + DbTimeType.DbTimeTypeFormat("F_LogTime", DbHelper.DbType) +" FROM DUAL WHERE F_LogTime>=" + DbTimeType.DateTimeToDbTimeType(startTime, DbHelper.DbType) + "AND F_LogTime<=" + DbTimeType.DateTimeToDbTimeType(endTime, DbHelper.DbType); sql += " and F_LogTime>=" + DbTimeType.DateTimeToDbTimeType(startTime, DbHelper.DbType) + " and F_LogTime<" + DbTimeType.DateTimeToDbTimeType(endTime, DbHelper.DbType); } } public class test { public DateTime? F_LogTime { get; set; } } }
一、建立一个数据库帮助类 DbHelper
using System; using System.Collections.Generic; using System.Data; using System.Data.Common; using System.Linq; using System.Text; using System.Threading.Tasks; namespace ConsoleApp3 { public class DbHelper { /// <summary> /// 数据库类型 /// </summary> public static DatabaseType DbType { get; set; } #region 构造函数 /// <summary> /// 构造方法 /// </summary> public DbHelper(DbConnection _dbConnection) { dbConnection = _dbConnection; dbCommand = dbConnection.CreateCommand(); dbCommand.CommandTimeout = CommandTimeout = 30; } #endregion #region 属性 /// <summary> /// 执行超时时间 /// </summary> public int CommandTimeout { get { return dbCommand.CommandTimeout; } set { dbCommand.CommandTimeout = value; } } /// <summary> /// 数据库连接对象 /// </summary> private DbConnection dbConnection { get; set; } /// <summary> /// 执行命令对象 /// </summary> private IDbCommand dbCommand { get; set; } /// <summary> /// 关闭数据库连接 /// </summary> public void Close() { if (dbConnection != null) { dbConnection.Close(); dbConnection.Dispose(); } if (dbCommand != null) { dbCommand.Dispose(); } } #endregion /// <summary> /// 执行SQL返回 DataReader /// </summary> /// <param name="cmdType">命令的类型</param> /// <param name="strSql">Sql语句</param> /// <returns></returns> public IDataReader ExecuteReader(CommandType cmdType, string strSql) { //Oracle.DataAccess.Client return ExecuteReader(cmdType, strSql, null); } /// <summary> /// 执行SQL返回 DataReader /// </summary> /// <param name="cmdType">命令的类型</param> /// <param name="strSql">Sql语句</param> /// <param name="dbParameter">Sql参数</param> /// <returns></returns> public IDataReader ExecuteReader(CommandType cmdType, string strSql, params DbParameter[] dbParameter) { try { PrepareCommand(dbConnection, dbCommand, null, cmdType, strSql, dbParameter); IDataReader rdr = dbCommand.ExecuteReader(CommandBehavior.CloseConnection); return rdr; } catch (Exception) { Close(); throw; } } /// <summary> /// 执行查询，并返回查询所返回的结果集 /// </summary> /// <param name="cmdType">命令的类型</param> /// <param name="strSql">Sql语句</param> /// <returns></returns> public object ExecuteScalar(CommandType cmdType, string strSql) { return ExecuteScalar(cmdType, strSql); } /// <summary> /// 执行查询，并返回查询所返回的结果集 /// </summary> /// <param name="cmdType">命令的类型</param> /// <param name="strSql">Sql语句</param> /// <param name="dbParameter">Sql参数</param> /// <returns></returns> public object ExecuteScalar(CommandType cmdType, string cmdText, params DbParameter[] parameters) { try { PrepareCommand(dbConnection, dbCommand, null, cmdType, cmdText, parameters); object val = dbCommand.ExecuteScalar(); dbCommand.Parameters.Clear(); return val; } catch (Exception) { Close(); throw; } } /// <summary> /// 为即将执行准备一个命令 /// </summary> /// <param name="conn">SqlConnection对象</param> /// <param name="cmd">SqlCommand对象</param> /// <param name="isOpenTrans">DbTransaction对象</param> /// <param name="cmdType">执行命令的类型（存储过程或T-SQL，等等）</param> /// <param name="cmdText">存储过程名称或者T-SQL命令行, e.g. Select * from Products</param> /// <param name="dbParameter">执行命令所需的sql语句对应参数</param> private void PrepareCommand(DbConnection conn, IDbCommand cmd, DbTransaction isOpenTrans, CommandType cmdType, string cmdText, params DbParameter[] dbParameter) { if (conn.State != ConnectionState.Open) conn.Open(); cmd.Connection = conn; cmd.CommandText = cmdText;//DbParameters.ToDbSql(cmdText); if (isOpenTrans != null) cmd.Transaction = isOpenTrans; cmd.CommandType = cmdType; if (dbParameter != null) { dbParameter = DbParameters.ToDbParameter(dbParameter); foreach (var parameter in dbParameter) { cmd.Parameters.Add(parameter); } } } } }
二、建立一个数据库类型类 DatabaseType
using System; using System.Collections.Generic; using System.Linq; using System.Text; using System.Threading.Tasks; namespace ConsoleApp3 { public enum DatabaseType { /// <summary> /// 数据库类型：SqlServer /// </summary> SqlServer, /// <summary> /// 数据库类型：MySql /// </summary> MySql, /// <summary> /// 数据库类型：Oracle /// </summary> Oracle, /// <summary> /// 数据库类型：Access /// </summary> Access, /// <summary> /// 数据库类型：SQLite /// </summary> SQLite } }
三、日期处理类 DbTimeType
using System; using System.Collections.Generic; using System.Linq; using System.Text; using System.Threading.Tasks; namespace ConsoleApp3 { public class DbTimeType { public static string DateTimeToDbTimeType(DateTime time, DatabaseType dbType = DatabaseType.Oracle) { string result = string.Empty; switch (dbType) { case DatabaseType.SqlServer: result = "'" + time.ToString("yyyy-MM-dd HH:mm:ss") + "'"; break; case DatabaseType.MySql: result = "'" + time.ToString("yyyy-MM-dd HH:mm:ss") + "'"; break; case DatabaseType.Oracle: result = "TO_DATE('" + time.ToString("yyyy-MM-dd HH:mm:ss") + "', 'yyyy-MM-dd HH24:MI:ss')"; break; default: throw new Exception("数据库类型目前不支持！"); } return result; } public static string DbTimeTypeFormat(string colunmName, DatabaseType dbType = DatabaseType.SqlServer) { string result = string.Empty; switch (dbType) { case DatabaseType.SqlServer: { result = "CONVERT(varchar(100)," + colunmName + ", 102)"; break; } case DatabaseType.MySql: { result = "DATE_FORMAT(" + colunmName + ",'%Y-%m-%d')"; break; } case DatabaseType.Oracle: { result = "to_char(" + colunmName + ",'yyyy-MM-dd')"; break; } default: { throw new Exception("数据库类型目前不支持！"); } } return result; } } }
四、Json操作类 Json
using Newtonsoft.Json; using Newtonsoft.Json.Converters; using Newtonsoft.Json.Linq; using System; using System.Collections.Generic; using System.Data; using System.Linq; using System.Text; using System.Threading.Tasks; namespace ConsoleApp3 { /// <summary> /// Json操作 /// </summary> public static class Json { public static object ToJson(this string Json) { return Json == null ? null : JsonConvert.DeserializeObject(Json); } public static string ToJson(this object obj) { var timeConverter = new IsoDateTimeConverter { DateTimeFormat = "yyyy-MM-dd HH:mm:ss" }; return JsonConvert.SerializeObject(obj, timeConverter); } public static string ToJson(this object obj, string datetimeformats) { var timeConverter = new IsoDateTimeConverter { DateTimeFormat = datetimeformats }; return JsonConvert.SerializeObject(obj, timeConverter); } public static T ToObject<T>(this string Json) { return Json == null ? default(T) : JsonConvert.DeserializeObject<T>(Json); } public static List<T> ToList<T>(this string Json) { return Json == null ? null : JsonConvert.DeserializeObject<List<T>>(Json); } public static DataTable ToTable(this string Json) { return Json == null ? null : JsonConvert.DeserializeObject<DataTable>(Json); } public static JObject ToJObject(this string Json) { return Json == null ? JObject.Parse("{}") : JObject.Parse(Json.Replace(" ", "")); } } }
五、类型转换扩展类 Extensions
using System; using System.Collections.Generic; using System.Linq; using System.Text; using System.Threading.Tasks; namespace ConsoleApp3 { /// <summary> /// 类型转换扩展 /// </summary> public static partial class Extensions { #region 数值转换 /// <summary> /// 转换为整型 /// </summary> /// <param name="data">数据</param> public static int ToInt(this object data) { if (data == null) return 0; int result; var success = int.TryParse(data.ToString(), out result); if (success) return result; try { return Convert.ToInt32(ToDouble(data, 0)); } catch (Exception) { return 0; } } /// <summary> /// 转换为可空整型 /// </summary> /// <param name="data">数据</param> public static int? ToIntOrNull(this object data) { if (data == null) return null; int result; bool isValid = int.TryParse(data.ToString(), out result); if (isValid) return result; return null; } /// <summary> /// 转换为双精度浮点数 /// </summary> /// <param name="data">数据</param> public static double ToDouble(this object data) { if (data == null) return 0; double result; return double.TryParse(data.ToString(), out result) ? result : 0; } /// <summary> /// 转换为双精度浮点数,并按指定的小数位4舍5入 /// </summary> /// <param name="data">数据</param> /// <param name="digits">小数位数</param> public static double ToDouble(this object data, int digits) { return Math.Round(ToDouble(data), digits); } /// <summary> /// 转换为可空双精度浮点数 /// </summary> /// <param name="data">数据</param> public static double? ToDoubleOrNull(this object data) { if (data == null) return null; double result; bool isValid = double.TryParse(data.ToString(), out result); if (isValid) return result; return null; } /// <summary> /// 转换为高精度浮点数 /// </summary> /// <param name="data">数据</param> public static decimal ToDecimal(this object data) { if (data == null) return 0; decimal result; return decimal.TryParse(data.ToString(), out result) ? result : 0; } /// <summary> /// 转换为高精度浮点数,并按指定的小数位4舍5入 /// </summary> /// <param name="data">数据</param> /// <param name="digits">小数位数</param> public static decimal ToDecimal(this object data, int digits) { return Math.Round(ToDecimal(data), digits); } /// <summary> /// 转换为可空高精度浮点数 /// </summary> /// <param name="data">数据</param> public static decimal? ToDecimalOrNull(this object data) { if (data == null) return null; decimal result; bool isValid = decimal.TryParse(data.ToString(), out result); if (isValid) return result; return null; } /// <summary> /// 转换为可空高精度浮点数,并按指定的小数位4舍5入 /// </summary> /// <param name="data">数据</param> /// <param name="digits">小数位数</param> public static decimal? ToDecimalOrNull(this object data, int digits) { var result = ToDecimalOrNull(data); if (result == null) return null; return Math.Round(result.Value, digits); } #endregion #region 日期转换 /// <summary> /// 转换为日期 /// </summary> /// <param name="data">数据</param> public static DateTime ToDate(this object data) { if (data == null) return DateTime.MinValue; DateTime result; return DateTime.TryParse(data.ToString(), out result) ? result : DateTime.MinValue; } /// <summary> /// 转换为可空日期 /// </summary> /// <param name="data">数据</param> public static DateTime? ToDateOrNull(this object data) { if (data == null) return null; DateTime result; bool isValid = DateTime.TryParse(data.ToString(), out result); if (isValid) return result; return null; } #endregion #region 布尔转换 /// <summary> /// 转换为布尔值 /// </summary> /// <param name="data">数据</param> public static bool ToBool(this object data) { if (data == null) return false; bool? value = GetBool(data); if (value != null) return value.Value; bool result; return bool.TryParse(data.ToString(), out result) && result; } /// <summary> /// 获取布尔值 /// </summary> private static bool? GetBool(this object data) { switch (data.ToString().Trim().ToLower()) { case "0": return false; case "1": return true; case "是": return true; case "否": return false; case "yes": return true; case "no": return false; default: return null; } } /// <summary> /// 转换为可空布尔值 /// </summary> /// <param name="data">数据</param> public static bool? ToBoolOrNull(this object data) { if (data == null) return null; bool? value = GetBool(data); if (value != null) return value.Value; bool result; bool isValid = bool.TryParse(data.ToString(), out result); if (isValid) return result; return null; } #endregion #region 字符串转换 /// <summary> /// 转换为字符串 /// </summary> /// <param name="data">数据</param> public static string ToString(this object data) { return data == null ? string.Empty : data.ToString().Trim(); } #endregion } }
