From 00a72fff219241eb5b0405a066cc859d07cf7735 Mon Sep 17 00:00:00 2001
From: 啊鑫 <t2856754968@163.com>
Date: 星期三, 17 九月 2025 10:23:44 +0800
Subject: [PATCH] 1111
---
DB/DbHelperSQL.cs | 677 ++++++++++++++++++++++++++++++++++++++++++++++++++++++++
1 files changed, 677 insertions(+), 0 deletions(-)
diff --git a/DB/DbHelperSQL.cs b/DB/DbHelperSQL.cs
new file mode 100644
index 0000000..cf0d708
--- /dev/null
+++ b/DB/DbHelperSQL.cs
@@ -0,0 +1,677 @@
+锘縰sing System.Collections;
+using System.Data;
+using System.Data.SqlClient;
+using NewPdaSqlServer.util;
+
+namespace NewPdaSqlServer.DB;
+
+/// <summary>
+/// 鏁版嵁璁块棶绫�
+/// </summary>
+public abstract class DbHelperSQL
+{
+ //鏁版嵁搴撹繛鎺ュ瓧绗︿覆(web.config鏉ラ厤缃�)
+ public static readonly string strConn =
+ AppsettingsUtility.Settings.DataBaseConn;
+
+ #region 鍏敤鏂规硶
+
+ /// <summary>
+ /// 鍙栧埌鏈�澶у瓧娈靛��
+ /// </summary>
+ /// <param name="FieldName"></param>
+ /// <param name="TableName"></param>
+ /// <returns></returns>
+ public static int GetMaxID(string FieldName, string TableName)
+ {
+ var strsql = "select max(" + FieldName + ") from " + TableName;
+ var obj = GetSingle(strsql);
+ if (obj == null)
+ return 0;
+ return int.Parse(obj.ToString());
+ }
+
+ /// <summary>
+ /// 鏄惁瀛樺湪鏌愭潯璁板綍
+ /// </summary>
+ /// <param name="strSql"></param>
+ /// <param name="cmdParms"></param>
+ /// <returns></returns>
+ public static bool Exists(string strSql, params SqlParameter[] cmdParms)
+ {
+ var obj = GetSingle(strSql, cmdParms);
+ int cmdresult;
+ if (Equals(obj, null) || Equals(obj, DBNull.Value))
+ cmdresult = 0;
+ else
+ cmdresult = int.Parse(obj.ToString());
+ if (cmdresult == 0)
+ return false;
+ return true;
+ }
+
+ #endregion
+
+ #region 鎵ц绠�鍗昐QL璇彞
+
+ /// <summary>
+ /// 鎵цSQL璇彞锛岃繑鍥炲奖鍝嶇殑璁板綍鏁�
+ /// </summary>
+ /// <param name="SQLString">SQL璇彞</param>
+ /// <returns>褰卞搷鐨勮褰曟暟</returns>
+ public static int ExecuteSql(string SQLString)
+ {
+ using (var connection = new SqlConnection(strConn))
+ {
+ using (var cmd = new SqlCommand(SQLString, connection))
+ {
+ try
+ {
+ connection.Open();
+ var rows = cmd.ExecuteNonQuery();
+ return rows;
+ }
+ catch (SqlException E)
+ {
+ connection.Close();
+ throw new Exception(E.Message);
+ }
+ }
+ }
+ }
+
+ /// <summary>
+ /// 鎵ц澶氭潯SQL璇彞锛屽疄鐜版暟鎹簱浜嬪姟銆�
+ /// </summary>
+ /// <param name="SQLStringList">澶氭潯SQL璇彞</param>
+ public static void ExecuteSqlTran(ArrayList SQLStringList)
+ {
+ using (var conn = new SqlConnection(strConn))
+ {
+ conn.Open();
+ var cmd = new SqlCommand();
+ cmd.Connection = conn;
+ var tx = conn.BeginTransaction();
+ cmd.Transaction = tx;
+ try
+ {
+ for (var n = 0; n < SQLStringList.Count; n++)
+ {
+ var strsql = SQLStringList[n].ToString();
+ if (strsql.Trim().Length > 1)
+ {
+ cmd.CommandText = strsql;
+ cmd.ExecuteNonQuery();
+ }
+ }
+
+ tx.Commit();
+ }
+ catch (SqlException E)
+ {
+ tx.Rollback();
+ throw new Exception(E.Message);
+ }
+ }
+ }
+
+ /// <summary>
+ /// 鎵ц甯︿竴涓瓨鍌ㄨ繃绋嬪弬鏁扮殑鐨凷QL璇彞銆�
+ /// </summary>
+ /// <param name="SQLString">SQL璇彞</param>
+ /// <param name="content">鍙傛暟鍐呭,姣斿涓�涓瓧娈垫槸鏍煎紡澶嶆潅鐨勬枃绔狅紝鏈夌壒娈婄鍙凤紝鍙互閫氳繃杩欎釜鏂瑰紡娣诲姞</param>
+ /// <returns>褰卞搷鐨勮褰曟暟</returns>
+ public static int ExecuteSql(string SQLString, string content)
+ {
+ using (var connection = new SqlConnection(strConn))
+ {
+ var cmd = new SqlCommand(SQLString, connection);
+ var myParameter = new SqlParameter("@content", SqlDbType.NText);
+ myParameter.Value = content;
+ cmd.Parameters.Add(myParameter);
+ try
+ {
+ connection.Open();
+ var rows = cmd.ExecuteNonQuery();
+ return rows;
+ }
+ catch (SqlException E)
+ {
+ throw new Exception(E.Message);
+ }
+ finally
+ {
+ cmd.Dispose();
+ connection.Close();
+ }
+ }
+ }
+
+ /// <summary>
+ /// 鍚戞暟鎹簱閲屾彃鍏ュ浘鍍忔牸寮忕殑瀛楁(鍜屼笂闈㈡儏鍐电被浼肩殑鍙︿竴绉嶅疄渚�)
+ /// </summary>
+ /// <param name="strSQL">SQL璇彞</param>
+ /// <param name="fs">鍥惧儚瀛楄妭,鏁版嵁搴撶殑瀛楁绫诲瀷涓篿mage鐨勬儏鍐�</param>
+ /// <returns>褰卞搷鐨勮褰曟暟</returns>
+ public static int ExecuteSqlInsertImg(string strSQL, byte[] fs)
+ {
+ using (var connection = new SqlConnection(strConn))
+ {
+ var cmd = new SqlCommand(strSQL, connection);
+ var myParameter = new SqlParameter("@fs", SqlDbType.Image);
+ myParameter.Value = fs;
+ cmd.Parameters.Add(myParameter);
+ try
+ {
+ connection.Open();
+ var rows = cmd.ExecuteNonQuery();
+ return rows;
+ }
+ catch (SqlException E)
+ {
+ throw new Exception(E.Message);
+ }
+ finally
+ {
+ cmd.Dispose();
+ connection.Close();
+ }
+ }
+ }
+
+ /// <summary>
+ /// 鎵ц涓�鏉¤绠楁煡璇㈢粨鏋滆鍙ワ紝杩斿洖鏌ヨ缁撴灉锛坥bject锛夈��
+ /// </summary>
+ /// <param name="SQLString">璁＄畻鏌ヨ缁撴灉璇彞</param>
+ /// <returns>鏌ヨ缁撴灉锛坥bject锛�</returns>
+ public static object GetSingle(string SQLString)
+ {
+ using (var connection = new SqlConnection(strConn))
+ {
+ using (var cmd = new SqlCommand(SQLString, connection))
+ {
+ try
+ {
+ connection.Open();
+ var obj = cmd.ExecuteScalar();
+ if (Equals(obj, null) || Equals(obj, DBNull.Value))
+ return null;
+ return obj;
+ }
+ catch (SqlException e)
+ {
+ connection.Close();
+ throw new Exception(e.Message);
+ }
+ }
+ }
+ }
+
+ /// <summary>
+ /// 鎵ц鏌ヨ璇彞锛岃繑鍥濻qlDataReader
+ /// </summary>
+ /// <param name="strSQL">鏌ヨ璇彞</param>
+ /// <returns>SqlDataReader</returns>
+ public static SqlDataReader ExecuteReader(string strSQL)
+ {
+ var connection = new SqlConnection(strConn);
+ var cmd = new SqlCommand(strSQL, connection);
+ try
+ {
+ connection.Open();
+ var myReader = cmd.ExecuteReader();
+ return myReader;
+ }
+ catch (SqlException e)
+ {
+ throw new Exception(e.Message);
+ }
+ }
+
+ /// <summary>
+ /// 鎵ц鏌ヨ璇彞锛岃繑鍥濪ataSet
+ /// </summary>
+ /// <param name="SQLString">鏌ヨ璇彞</param>
+ /// <returns>DataSet</returns>
+ public static DataSet Query(string SQLString)
+ {
+ using (var connection = new SqlConnection(strConn))
+ {
+ var ds = new DataSet();
+ try
+ {
+ connection.Open();
+ var command = new SqlDataAdapter(SQLString, connection);
+ command.Fill(ds, "ds");
+ }
+ catch (SqlException ex)
+ {
+ throw new Exception(ex.Message);
+ }
+
+ return ds;
+ }
+ }
+
+ #endregion
+
+ #region 鎵ц甯﹀弬鏁扮殑SQL璇彞
+
+ /// <summary>
+ /// 鎵цSQL璇彞锛岃繑鍥炲奖鍝嶇殑璁板綍鏁�
+ /// </summary>
+ /// <param name="SQLString">SQL璇彞</param>
+ /// <returns>褰卞搷鐨勮褰曟暟</returns>
+ public static int ExecuteSql(string SQLString,
+ params SqlParameter[] cmdParms)
+ {
+ using (var connection = new SqlConnection(strConn))
+ {
+ using (var cmd = new SqlCommand())
+ {
+ try
+ {
+ PrepareCommand(cmd, connection, null, SQLString, cmdParms);
+ var rows = cmd.ExecuteNonQuery();
+ cmd.Parameters.Clear();
+ return rows;
+ }
+ catch (SqlException E)
+ {
+ throw new Exception(E.Message);
+ }
+ }
+ }
+ }
+
+ /// <summary>
+ /// 鎵ц澶氭潯SQL璇彞锛屽疄鐜版暟鎹簱浜嬪姟銆�
+ /// </summary>
+ /// <param name="SQLStringList">SQL璇彞鐨勫搱甯岃〃锛坘ey涓簊ql璇彞锛寁alue鏄璇彞鐨凷qlParameter[]锛�</param>
+ public static void ExecuteSqlTran(Hashtable SQLStringList)
+ {
+ using (var conn = new SqlConnection(strConn))
+ {
+ conn.Open();
+ using (var trans = conn.BeginTransaction())
+ {
+ var cmd = new SqlCommand();
+ try
+ {
+ //寰幆
+ foreach (DictionaryEntry myDE in SQLStringList)
+ {
+ var cmdText = myDE.Key.ToString();
+ var cmdParms = (SqlParameter[])myDE.Value;
+ PrepareCommand(cmd, conn, trans, cmdText, cmdParms);
+ var val = cmd.ExecuteNonQuery();
+ cmd.Parameters.Clear();
+
+ //trans.Commit();
+ }
+
+ trans.Commit();
+ }
+ catch
+ {
+ trans.Rollback();
+ throw;
+ }
+ }
+ }
+ }
+
+ /// <summary>
+ /// 鎵ц澶氭潯SQL璇彞锛屽疄鐜版暟鎹簱浜嬪姟銆�
+ /// </summary>
+ /// <param name="SQLStringList">SQL璇彞鐨勫搱甯岃〃锛坘ey涓簊ql璇彞锛寁alue鏄璇彞鐨凷qlParameter[]锛�</param>
+ public static int ExecuteSqlTranRtn(Hashtable SQLStringList)
+ {
+ var d = 0;
+ using (var conn = new SqlConnection(strConn))
+ {
+ conn.Open();
+ using (var trans = conn.BeginTransaction())
+ {
+ var cmd = new SqlCommand();
+ try
+ {
+ //寰幆
+ foreach (DictionaryEntry myDE in SQLStringList)
+ {
+ var cmdText = myDE.Key.ToString();
+ var cmdParms = (SqlParameter[])myDE.Value;
+ PrepareCommand(cmd, conn, trans, cmdText, cmdParms);
+ var val = cmd.ExecuteNonQuery();
+ d = d + val;
+ cmd.Parameters.Clear();
+
+ //trans.Commit();
+ }
+
+ trans.Commit();
+ }
+ catch
+ {
+ trans.Rollback();
+ throw;
+ }
+ }
+ }
+
+ return d;
+ }
+
+ /// <summary>
+ /// 鎵ц澶氭潯SQL璇彞锛屽疄鐜版暟鎹簱浜嬪姟
+ /// </summary>
+ /// <param name="SQLStringList">SQL璇彞鐨勫搱甯岃〃锛坘ey涓烘爣璇嗭紝value鏄璇彞鐨凷qlParameter[]锛�</param>
+ public static void ExecuteSqlTran(string sqltxt, Hashtable SQLStringList)
+ {
+ using (var conn = new SqlConnection(strConn))
+ {
+ conn.Open();
+ using (var trans = conn.BeginTransaction())
+ {
+ var cmd = new SqlCommand();
+ try
+ {
+ //寰幆
+ foreach (DictionaryEntry myDE in SQLStringList)
+ {
+ var cmdText = sqltxt;
+ var cmdParms = (SqlParameter[])myDE.Value;
+ PrepareCommand(cmd, conn, trans, cmdText, cmdParms);
+ var val = cmd.ExecuteNonQuery();
+ cmd.Parameters.Clear();
+ }
+
+ trans.Commit();
+ }
+ catch
+ {
+ trans.Rollback();
+ throw;
+ }
+ }
+ }
+ }
+
+ /// <summary>
+ /// 鎵ц涓�鏉¤绠楁煡璇㈢粨鏋滆鍙ワ紝杩斿洖鏌ヨ缁撴灉锛坥bject锛夈��
+ /// </summary>
+ /// <param name="SQLString">璁＄畻鏌ヨ缁撴灉璇彞</param>
+ /// <returns>鏌ヨ缁撴灉锛坥bject锛�</returns>
+ public static object GetSingle(string SQLString,
+ params SqlParameter[] cmdParms)
+ {
+ using (var connection = new SqlConnection(strConn))
+ {
+ using (var cmd = new SqlCommand())
+ {
+ try
+ {
+ PrepareCommand(cmd, connection, null, SQLString, cmdParms);
+ var obj = cmd.ExecuteScalar();
+ cmd.Parameters.Clear();
+ if (Equals(obj, null) || Equals(obj, DBNull.Value))
+ return null;
+ return obj;
+ }
+ catch (SqlException e)
+ {
+ throw new Exception(e.Message);
+ }
+ }
+ }
+ }
+
+ /// <summary>
+ /// 鎵ц鏌ヨ璇彞锛岃繑鍥濻qlDataReader
+ /// </summary>
+ /// <param name="strSQL">鏌ヨ璇彞</param>
+ /// <returns>SqlDataReader</returns>
+ public static SqlDataReader ExecuteReader(string SQLString,
+ params SqlParameter[] cmdParms)
+ {
+ var connection = new SqlConnection(strConn);
+ var cmd = new SqlCommand();
+ try
+ {
+ PrepareCommand(cmd, connection, null, SQLString, cmdParms);
+ var myReader = cmd.ExecuteReader();
+ cmd.Parameters.Clear();
+ return myReader;
+ }
+ catch (SqlException e)
+ {
+ throw new Exception(e.Message);
+ }
+ }
+
+ /// <summary>
+ /// 鎵ц鏌ヨ璇彞锛岃繑鍥濪ataSet
+ /// </summary>
+ /// <param name="SQLString">鏌ヨ璇彞</param>
+ /// <returns>DataSet</returns>
+ public static DataSet Query(string SQLString,
+ params SqlParameter[] cmdParms)
+ {
+ using (var connection = new SqlConnection(strConn))
+ {
+ var cmd = new SqlCommand();
+ PrepareCommand(cmd, connection, null, SQLString, cmdParms);
+ using (var da = new SqlDataAdapter(cmd))
+ {
+ var ds = new DataSet();
+ try
+ {
+ da.Fill(ds, "ds");
+ cmd.Parameters.Clear();
+ }
+ catch (SqlException ex)
+ {
+ throw new Exception(ex.Message);
+ }
+
+ return ds;
+ }
+ }
+ }
+
+ //private static void PrepareCommand(SqlCommand cmd, SqlConnection conn, SqlTransaction trans, string cmdText, SqlParameter[] cmdParms)
+ //{
+
+ // if (conn.State != ConnectionState.Open)
+ // conn.Open();
+ // cmd.Connection = conn;
+ // cmd.CommandText = cmdText;
+ // if (trans != null)
+ // cmd.Transaction = trans;
+ // cmd.CommandType = CommandType.Text;//cmdType;
+ // if (cmdParms != null)
+ // {
+ // foreach (SqlParameter parameter in cmdParms)
+ // {
+ // if ((parameter.Direction == ParameterDirection.InputOutput || parameter.Direction == ParameterDirection.Input) &&
+ // (parameter.Value == null))
+ // {
+ // parameter.Value = DBNull.Value;
+ // }
+ // cmd.Parameters.Add(parameter);
+ // }
+ // }
+ //}
+ private static void PrepareCommand(SqlCommand cmd, SqlConnection conn,
+ SqlTransaction trans, string cmdText, SqlParameter[] cmdParms)
+ {
+ if (conn.State != ConnectionState.Open)
+ conn.Open();
+ cmd.Connection = conn;
+ cmd.CommandText = cmdText;
+ if (trans != null)
+ cmd.Transaction = trans;
+ cmd.CommandType = CommandType.Text; //cmdType;
+ if (cmdParms != null)
+ foreach (var parm in cmdParms)
+ {
+ //cmd.Parameters.Add(parm);
+ if ((parm.Direction == ParameterDirection.InputOutput ||
+ parm.Direction == ParameterDirection.Input) &&
+ parm.Value == null)
+ parm.Value = DBNull.Value;
+ cmd.Parameters.Add(parm);
+ }
+ }
+
+ #endregion
+
+ #region 瀛樺偍杩囩▼鎿嶄綔
+
+ /// <summary>
+ /// 鎵ц瀛樺偍杩囩▼
+ /// </summary>
+ /// <param name="storedProcName">瀛樺偍杩囩▼鍚�</param>
+ /// <param name="parameters">瀛樺偍杩囩▼鍙傛暟</param>
+ /// <returns>SqlDataReader</returns>
+ public static SqlDataReader RunProcedure(string storedProcName,
+ IDataParameter[] parameters)
+ {
+ var connection = new SqlConnection(strConn);
+ SqlDataReader returnReader;
+ connection.Open();
+ var command = BuildQueryCommand(connection, storedProcName, parameters);
+ command.CommandType = CommandType.StoredProcedure;
+ returnReader = command.ExecuteReader();
+ return returnReader;
+ }
+
+ /// <summary>
+ /// 鎵ц甯﹁緭鍑哄弬鏁扮殑瀛樺偍杩囩▼
+ /// </summary>
+ /// <param name="storedProcName">瀛樺偍杩囩▼鍚�</param>
+ /// <param name="parameters">瀛樺偍杩囩▼鍙傛暟,OutName 杈撳嚭鍙傛暟鐨勫悕瀛楋紙濡傛灉鏄涓互@涓哄垎鍓茬锛�</param>
+ /// <returns>string(浠鍒嗗壊鐨勮緭鍑哄弬鏁扮殑鍊�)</returns>
+ public static string RunProcedureOut(string storedProcName,
+ IDataParameter[] parameters, string outName)
+ {
+ var connection = new SqlConnection(strConn);
+ var returnstring = string.Empty;
+ connection.Open();
+ var command = BuildQueryCommand(connection, storedProcName, parameters);
+ command.CommandType = CommandType.StoredProcedure;
+ command.ExecuteNonQuery();
+ var str = string.Empty;
+ //瑙ｆ瀽绌胯繃鏉ョ殑鍙傛暟
+ var Arraystr = outName.Split('|');
+ foreach (var st in Arraystr)
+ {
+ var a = st;
+ if (str == string.Empty)
+ str = command.Parameters[a].Value.ToString();
+ else
+ str = str + "|" + command.Parameters[a].Value;
+ }
+
+ return str;
+ }
+
+ /// <summary>
+ /// 鎵ц瀛樺偍杩囩▼
+ /// </summary>
+ /// <param name="storedProcName">瀛樺偍杩囩▼鍚�</param>
+ /// <param name="parameters">瀛樺偍杩囩▼鍙傛暟</param>
+ /// <param name="tableName">DataSet缁撴灉涓殑琛ㄥ悕</param>
+ /// <returns>DataSet</returns>
+ public static DataSet RunProcedure(string storedProcName,
+ IDataParameter[] parameters, string tableName)
+ {
+ using (var connection = new SqlConnection(strConn))
+ {
+ var dataSet = new DataSet();
+ connection.Open();
+ var sqlDA = new SqlDataAdapter();
+ sqlDA.SelectCommand =
+ BuildQueryCommand(connection, storedProcName, parameters);
+ sqlDA.Fill(dataSet, tableName);
+ connection.Close();
+ return dataSet;
+ }
+ }
+
+ /// <summary>
+ /// 鏋勫缓 SqlCommand 瀵硅薄(鐢ㄦ潵杩斿洖涓�涓粨鏋滈泦锛岃�屼笉鏄竴涓暣鏁板��)
+ /// </summary>
+ /// <param name="connection">鏁版嵁搴撹繛鎺�</param>
+ /// <param name="storedProcName">瀛樺偍杩囩▼鍚�</param>
+ /// <param name="parameters">瀛樺偍杩囩▼鍙傛暟</param>
+ /// <returns>SqlCommand</returns>
+ private static SqlCommand BuildQueryCommand(SqlConnection connection,
+ string storedProcName, IDataParameter[] parameters)
+ {
+ var command = new SqlCommand(storedProcName, connection);
+ command.CommandType = CommandType.StoredProcedure;
+ foreach (SqlParameter parameter in parameters)
+ command.Parameters.Add(parameter);
+ return command;
+ }
+
+ /// <summary>
+ /// 鎵ц瀛樺偍杩囩▼锛岃繑鍥炲奖鍝嶇殑琛屾暟
+ /// </summary>
+ /// <param name="storedProcName">瀛樺偍杩囩▼鍚�</param>
+ /// <param name="parameters">瀛樺偍杩囩▼鍙傛暟</param>
+ /// <param name="rowsAffected">褰卞搷鐨勮鏁�</param>
+ /// <returns></returns>
+ public static int RunProcedure(string storedProcName,
+ IDataParameter[] parameters, out int rowsAffected)
+ {
+ using (var connection = new SqlConnection(strConn))
+ {
+ int result;
+ connection.Open();
+ var command =
+ BuildIntCommand(connection, storedProcName, parameters);
+ rowsAffected = command.ExecuteNonQuery();
+ result = (int)command.Parameters["ReturnValue"].Value;
+ //Connection.Close();
+ return result;
+ }
+ }
+
+ /// <summary>
+ /// 鎵ц瀛樺偍杩囩▼锛岃繑鍥炲奖鍝嶈鏁�
+ /// </summary>
+ /// <param name="storedProcName">string绫诲瀷瀛樺偍杩囩▼鍚�</param>
+ /// <param name="parameters">SqlParameters[]瀛樺偍杩囩▼鍙傛暟</param>
+ /// <returns>int 褰卞搷琛屾暟</returns>
+ public static int RunProcedure_NonQuery(string storedProcName,
+ IDataParameter[] parameters)
+ {
+ using (var connection = new SqlConnection(strConn))
+ {
+ connection.Open();
+ var command =
+ BuildQueryCommand(connection, storedProcName, parameters);
+ return command.ExecuteNonQuery();
+ }
+ }
+
+ /// <summary>
+ /// 鍒涘缓 SqlCommand 瀵硅薄瀹炰緥(鐢ㄦ潵杩斿洖涓�涓暣鏁板��)
+ /// </summary>
+ /// <param name="storedProcName">瀛樺偍杩囩▼鍚�</param>
+ /// <param name="parameters">瀛樺偍杩囩▼鍙傛暟</param>
+ /// <returns>SqlCommand 瀵硅薄瀹炰緥</returns>
+ private static SqlCommand BuildIntCommand(SqlConnection connection,
+ string storedProcName, IDataParameter[] parameters)
+ {
+ var command = BuildQueryCommand(connection, storedProcName, parameters);
+ command.Parameters.Add(new SqlParameter("ReturnValue",
+ SqlDbType.Int, 4, ParameterDirection.ReturnValue,
+ false, 0, 0, string.Empty, DataRowVersion.Default, null));
+ return command;
+ }
+
+ #endregion
+}
\ No newline at end of file
--
Gitblit v1.9.3