DDMesApi.git - Gitblit

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


			/// <summary>
			/// Dapper查询返回List<T>
			/// Dapper查询返回List
			/// </summary>
			/// <typeparam name="T">需要返回的对象类型</typeparam>
			/// <param name="sql">Sql语句</param>
			@@ -52,7 +53,12 @@
			LogHelper.WriteLog(ex);
			throw ex;
			}

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

			}
			return list;
			}
			@@ -81,6 +87,11 @@
			LogHelper.WriteLog(ex);
			throw ex;
			}
			finally
			{
			conn.Dispose();
			conn.Close();
			}
			}
			return result;
			}
			@@ -108,6 +119,11 @@
			LogHelper.WriteLog(ex);
			throw ex;
			}
			finally
			{
			conn.Dispose();
			conn.Close();
			}
			}
			return t;
			}
			@@ -122,25 +138,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, skip, pageIndex * pageSize);
			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,18 +163,23 @@
			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>
			/// Dapper查询返回datatable数据
			/// Dapper查询返回datatable数据(带参数)
			/// </summary>
			/// <param name="sql"></param>
			/// <param name="parm"></param>
			@@ -167,23 +187,193 @@
			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>
			/// Dapper查询返回datatable数据(不带参数)
			/// </summary>
			/// <param name="sql"></param>
			/// <returns></returns>
			public static DataTable selecttable(string sql)
			{
			//sql语句
			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();
			}
			}
			}

			/// <summary>
			/// Dapper执行存储过程返回datatable数据(带参数)
			/// </summary>
			/// <param name="sql">存储过程名</param>
			/// <param name="parm">参数</param>
			/// <returns></returns>
			public static DataTable selectProcedure(string sql, Object parm)
			{
			//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 存储过程
			table.Load(res);
			return table;
			}
			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>
			@@ -196,19 +386,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;
			}
			}

			@@ -218,24 +413,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>
			@@ -254,7 +454,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;
			@@ -267,25 +467,66 @@
			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)
			@@ -293,13 +534,78 @@
			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;
			}

			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();
			}
			}
			}
			}