ZQHApi.git - Gitblit

			@@ -29,8 +29,9 @@
			return connection;
			}


			/// <summary>
			/// Dapper查询返回List<T>
			/// Dapper查询返回List
			/// </summary>
			/// <typeparam name="T">需要返回的对象类型</typeparam>
			/// <param name="sql">Sql语句</param>
			@@ -44,6 +45,8 @@
			{
			try
			{
			//用户操作记录写入数据表
			//Log4net.LogUtil.SaveMessage("PC", "操作了xxxxx", "api/Login/LoginSave", ConfigurationManager.AppSettings["FileIP"], "9999", 1);
			list = conn.Query<T>(sql, parm).ToList<T>();
			}
			catch (Exception ex)
			@@ -52,7 +55,12 @@
			LogHelper.WriteLog(ex);
			throw ex;
			}

			finally
			{
			conn.Dispose();
			conn.Close();
			}

			}
			return list;
			}
			@@ -81,6 +89,11 @@
			LogHelper.WriteLog(ex);
			throw ex;
			}
			finally
			{
			conn.Dispose();
			conn.Close();
			}
			}
			return result;
			}
			@@ -108,6 +121,11 @@
			LogHelper.WriteLog(ex);
			throw ex;
			}
			finally
			{
			conn.Dispose();
			conn.Close();
			}
			}
			return t;
			}
			@@ -122,25 +140,24 @@
			/// <param name="pageSize">当前页显示条数</param>
			/// <param name="total">结果集总数</param>
			/// <returns></returns>
			public static IEnumerable<T> GetPageList<T>(string sql,Object parm, string orderBy,string sort, int pageIndex, int pageSize, out int total)
			public static IEnumerable<T> GetPageList<T>(string sql, Object parm, string orderBy, string sort, int pageIndex, int pageSize, out int total)
			{
			try
			{
			//int skip = 1;
			//if (pageIndex > 0)
			//{
			// skip = (pageIndex - 1) * pageSize + 1;
			//}

			StringBuilder sb = new StringBuilder();
			sb.AppendFormat("SELECT COUNT(1) FROM ({0}) AS Result;", sql);
			sb.AppendFormat(@"SELECT *
			//int skip = 1;
			//if (pageIndex > 0)
			//{
			// skip = (pageIndex - 1) * pageSize + 1;
			//}
			StringBuilder sb = new StringBuilder();
			sb.AppendFormat("SELECT COUNT(1) FROM ({0}) AS Result;", sql);
			sb.AppendFormat(@"SELECT *
			FROM(SELECT *,ROW_NUMBER() OVER(ORDER BY {1} {2}) AS RowNum
			FROM ({0}) AS Temp) AS Result
			WHERE RowNum >= {3} AND RowNum <= {4}
			ORDER BY {1} {2}", sql, orderBy, sort, pageIndex, pageSize); //skip, pageIndex * pageSize

			using (IDbConnection conn = sqlConnection())
			using (IDbConnection conn = sqlConnection())
			{
			try
			{
			using (var reader = conn.QueryMultiple(sb.ToString(), parm))
			{
			@@ -148,14 +165,19 @@
			return reader.Read<T>();
			}
			}
			catch (Exception ex)
			{
			//创建日志记录组件实例
			LogHelper.WriteLog(ex);
			throw ex;
			}
			finally
			{
			conn.Dispose();
			conn.Close();
			}

			}
			catch (Exception ex)
			{
			//创建日志记录组件实例
			LogHelper.WriteLog(ex);
			throw ex;
			}

			}

			/// <summary>
			@@ -167,23 +189,59 @@
			public static DataTable selectdata(string sql, Object parm)
			{
			//sql语句
			try
			using (IDbConnection conn = sqlConnection())
			{
			using (IDbConnection conn = sqlConnection())
			try
			{
			DataTable table = new DataTable();
			var data = conn.ExecuteReader(sql, parm);
			table.Load(data);
			return table;
			}
			catch (Exception ex)
			{
			//创建日志记录组件实例
			LogHelper.WriteLog(ex);
			throw ex;
			}
			finally
			{
			conn.Dispose();
			conn.Close();
			}
			}
			catch (Exception ex)
			}

			/// <summary>
			/// Dapper查询返回datatable数据(带参数)
			/// </summary>
			/// <param name="sql"></param>
			/// <param name="parm"></param>
			/// <returns></returns>
			public static DataTable selectlist(string sql, Object parm)
			{
			//sql语句
			using (IDbConnection conn = sqlConnection())
			{
			//创建日志记录组件实例
			LogHelper.WriteLog(ex);
			throw ex;
			try
			{
			DataTable table = new DataTable();
			var data = conn.ExecuteReader(sql, parm);
			table.Load(data);
			return table;
			}
			catch (Exception ex)
			{
			//创建日志记录组件实例
			LogHelper.WriteLog(ex);
			throw ex;
			}
			finally
			{
			conn.Dispose();
			conn.Close();
			}
			}

			}

			/// <summary>
			@@ -194,23 +252,27 @@
			public static DataTable selecttable(string sql)
			{
			//sql语句
			try
			using (IDbConnection conn = sqlConnection())
			{
			using (IDbConnection conn = sqlConnection())
			try
			{
			DataTable table = new DataTable();
			var data = conn.ExecuteReader(sql);
			table.Load(data);
			return table;
			}
			catch (Exception ex)
			{
			//创建日志记录组件实例
			LogHelper.WriteLog(ex);
			throw ex;
			}
			finally
			{
			conn.Dispose();
			conn.Close();
			}
			}
			catch (Exception ex)
			{
			//创建日志记录组件实例
			LogHelper.WriteLog(ex);
			throw ex;
			}

			}

			/// <summary>
			@@ -222,9 +284,9 @@
			public static DataTable selectProcedure(string sql, Object parm)
			{
			//sql语句
			try
			using (IDbConnection conn = sqlConnection())
			{
			using (IDbConnection conn = sqlConnection())
			try
			{
			DataTable table = new DataTable();
			//var data = conn.ExecuteReader(sql, parm);
			@@ -232,14 +294,88 @@
			table.Load(res);
			return table;
			}
			}
			catch (Exception ex)
			{
			//创建日志记录组件实例
			LogHelper.WriteLog(ex);
			throw ex;
			catch (Exception ex)
			{
			//创建日志记录组件实例
			LogHelper.WriteLog(ex);
			throw ex;
			}
			finally
			{
			conn.Dispose();
			conn.Close();
			}
			}

			}

			/// <summary>
			/// Dapper执行存储过程返回datatable数据(带参数)
			/// </summary>
			/// <param name="sql">存储过程名</param>
			/// <param name="parm">参数</param>
			/// <returns></returns>
			public static bool IsProcedure(string sql, Object parm)
			{
			bool result;
			//sql语句
			using (IDbConnection conn = sqlConnection())
			{
			try
			{
			DataTable table = new DataTable();
			//var data = conn.ExecuteReader(sql, parm);
			var res = conn.ExecuteReader(sql, parm, commandType: CommandType.StoredProcedure);//sql 存储过程
			result = true;
			}
			catch (Exception ex)
			{
			//创建日志记录组件实例
			LogHelper.WriteLog(ex);
			result = false;
			throw ex;
			}
			finally
			{
			conn.Dispose();
			conn.Close();
			}
			return result;
			}
			}

			/// <summary>
			/// Dapper执行存储过程返回datatable数据(不带参数)
			/// </summary>
			/// <param name="sql">存储过程名</param>
			/// <returns></returns>
			public static bool IsProcedureNo(string sql)
			{
			bool result;
			//sql语句
			using (IDbConnection conn = sqlConnection())
			{
			try
			{
			DataTable table = new DataTable();
			//var data = conn.ExecuteReader(sql, parm);
			var res = conn.ExecuteReader(sql);//sql 存储过程
			result = true;
			}
			catch (Exception ex)
			{
			//创建日志记录组件实例
			LogHelper.WriteLog(ex);
			result = false;
			throw ex;
			}
			finally
			{
			conn.Dispose();
			conn.Close();
			}
			return result;
			}
			}

			/// <summary>
			@@ -252,19 +388,24 @@
			{
			//sql语句
			int result = 0;
			try
			using (IDbConnection conn = sqlConnection())
			{
			using (IDbConnection conn = sqlConnection())
			try
			{
			result = conn.Query(sql, parm).Count();
			}
			catch (Exception ex)
			{
			//创建日志记录组件实例
			LogHelper.WriteLog(ex);
			throw ex;
			}
			finally
			{
			conn.Dispose();
			conn.Close();
			}
			return result;
			}
			catch (Exception ex)
			{
			//创建日志记录组件实例
			LogHelper.WriteLog(ex);
			throw ex;
			}
			}

			@@ -274,24 +415,29 @@
			/// <param name="sql"></param>
			/// <param name="parametere"></param>
			/// <returns></returns>
			public static int SQL(string sql,Object parametere)
			public static int SQL(string sql, Object parametere)
			{
			//sql语句
			int result = 0;
			try
			using (IDbConnection conn = sqlConnection())
			{
			using (IDbConnection conn = sqlConnection())
			try
			{
			result = conn.Execute(sql, parametere);
			}
			return result;
			catch (Exception ex)
			{
			//创建日志记录组件实例
			LogHelper.WriteLog(ex);
			throw ex;
			}
			finally
			{
			conn.Dispose();
			conn.Close();
			}
			}
			catch (Exception ex)
			{
			//创建日志记录组件实例
			LogHelper.WriteLog(ex);
			throw ex;
			}
			return result;
			}

			/// <summary>
			@@ -310,7 +456,7 @@
			tranction = conn.BeginTransaction();
			for (int i = 0; i < sqlList.Count; i++)
			{
			conn.Execute(sqlList[i].GetType().GetProperty("str").GetValue(sqlList[i], null).ToString(), sqlList[i].GetType().GetProperty("parm").GetValue(sqlList[i], null),tranction);
			conn.Execute(sqlList[i].GetType().GetProperty("str").GetValue(sqlList[i], null).ToString(), sqlList[i].GetType().GetProperty("parm").GetValue(sqlList[i], null), tranction);
			}
			tranction.Commit();
			result = true;
			@@ -323,25 +469,65 @@
			tranction.Rollback();
			throw ex;
			}
			finally
			{
			conn.Dispose();
			conn.Close();
			}
			return result;
			}
			}

			/// <summary>
			/// 增加，删除，修改使用的事务方法 Sqllist为依次执行
			/// </summary>
			/// <param name="sqlList"></param>
			/// <returns></returns>
			public static int DoTransactionCont(List<object> sqlList)
			{
			int result = 0;
			IDbTransaction tranction = null;
			using (IDbConnection conn = sqlConnection())
			{
			try
			{
			tranction = conn.BeginTransaction();
			for (int i = 0; i < sqlList.Count; i++)
			{
			result += conn.Execute(sqlList[i].GetType().GetProperty("str").GetValue(sqlList[i], null).ToString(), sqlList[i].GetType().GetProperty("parm").GetValue(sqlList[i], null), tranction);
			}
			tranction.Commit();
			}
			catch (Exception ex)
			{
			//创建日志记录组件实例
			LogHelper.WriteLog(ex);
			tranction.Rollback();
			throw ex;
			}
			finally
			{
			conn.Dispose();
			conn.Close();
			}
			return result;
			}
			}

			/// <summary>
			/// Dapper插入返回自增主键Id
			/// Dapper插入返回自增主键Id（备份）
			/// </summary>
			/// <param name="sql"></param>
			/// <param name="parameter"></param>
			/// <param name="tableName">待插入数据的表名</param>
			/// <returns></returns>
			public static int insertReturnId(string sql, Object parameter, String tableName)
			public static int insertReturnId0(string sql, Object parameter, String tableName)
			{

			int result = 0;
			try
			using (IDbConnection conn = sqlConnection())
			{
			using (IDbConnection conn = sqlConnection())
			try
			{
			result = conn.Execute(sql, parameter);
			if (result != 0)
			@@ -349,13 +535,111 @@
			result = conn.Query<int>("select ident_current(@table)", new { table = tableName }).Single<int>();
			}
			}
			return result;
			catch (Exception ex)
			{
			//创建日志记录组件实例
			LogHelper.WriteLog(ex);
			throw ex;
			}
			finally
			{
			conn.Dispose();
			conn.Close();
			}
			}
			catch (Exception ex)
			{
			//创建日志记录组件实例
			LogHelper.WriteLog(ex);
			throw ex;
			return result;
			}


			/// <summary>
			/// Dapper 返回自增主键当前数据表最大Id
			/// </summary>
			/// <param name="tableName">待插入数据的表名</param>
			/// <returns></returns>
			public static int insertReturnId(String tableName)
			{

			int result = 0;
			using (IDbConnection conn = sqlConnection())
			{
			try
			{
			if (result == 0)
			{
			result = conn.Query<int>("select ident_current(@table)", new { table = tableName }).Single<int>();
			}
			}
			catch (Exception ex)
			{
			//创建日志记录组件实例
			LogHelper.WriteLog(ex);
			throw ex;
			}
			finally
			{
			conn.Dispose();
			conn.Close();
			}
			}
			return result;
			}
			/// <summary>
			/// 执行sql执行语句,返回字符串
			/// </summary>
			/// <param name="sql"></param>
			/// <returns></returns>
			public static string sqlstr(String sql)
			{

			string result = "";
			using (IDbConnection conn = sqlConnection())
			{
			try
			{
			if (result == "")
			{
			result = conn.Query<string>(sql).First();
			}
			}
			catch (Exception ex)
			{
			//创建日志记录组件实例
			LogHelper.WriteLog(ex);
			throw ex;
			}
			finally
			{
			conn.Dispose();
			conn.Close();
			}
			}
			return result;
			}

			public static DataTable lissql(string sql)
			{
			//sql语句
			using (IDbConnection conn = sqlConnection())
			{
			try
			{
			DataTable table = new DataTable();
			//var data = conn.ExecuteReader(sql, parm);
			var res = conn.ExecuteReader(sql);//sql 存储过程
			table.Load(res);
			return table;
			}
			catch (Exception ex)
			{
			//创建日志记录组件实例
			LogHelper.WriteLog(ex);
			throw ex;
			}
			finally
			{
			conn.Dispose();
			conn.Close();
			}
			}
			}
			}