using System;
using System.Collections;
using System.Collections.Generic;
using System.Data;
using System.Data.SqlClient;
using System.Configuration;namespace MTBug.sqlHelper
{/// /// 数据访问抽象基础类/// Copyright (C) Maticsoft /// public abstract class DbHelperSQL{//数据库连接字符串(web.config来配置)&＃xff0c;多数据库可使用DbHelperSQLP来实现.//public static string connectionString &＃61; &＃64;"server&＃61;localhost;database&＃61;MTBugList;uid&＃61;sa;pwd&＃61;123";public static string connectionString &＃61; &＃64;"server&＃61;localhost;database&＃61;MTBugList;uid&＃61;sa;pwd&＃61;aaaaaa";public DbHelperSQL(){//connectionString &＃61; &＃64;"server&＃61;" &＃43; System.Net.Dns.GetHostName() &＃43; ";database&＃61;TianZhong;uid&＃61;sa;pwd&＃61;" &＃43; myHelper.getXMLValue("dbUserPW");connectionString &＃61; &＃64;"server&＃61;" &＃43; myHelper.getXMLValue("dbAddr") &＃43; ";database&＃61;MTBugList;uid&＃61;sa;pwd&＃61;" &＃43; myHelper.getXMLValue("dbUserPW");}#region 公用方法/// /// 判断是否存在某表的某个字段/// /// 表名称/// 列名称/// 是否存在public static bool ColumnExists(string tableName, string columnName){string sql &＃61; "select count(1) from syscolumns where [id]&＃61;object_id(&＃39;" &＃43; tableName &＃43; "&＃39;) and [name]&＃61;&＃39;" &＃43; columnName &＃43; "&＃39;";object res &＃61; GetSingle(sql);if (res &＃61;&＃61; null){return false;}return Convert.ToInt32(res) > 0;}public static int GetMaxID(string FieldName, string TableName){string strsql &＃61; "select max(" &＃43; FieldName &＃43; ")&＃43;1 from " &＃43; TableName;object obj &＃61; GetSingle(strsql);if (obj &＃61;&＃61; null){return 1;}else{return int.Parse(obj.ToString());}}public static bool Exists(string strSql){object obj &＃61; GetSingle(strSql);int cmdresult;if ((Object.Equals(obj, null)) || (Object.Equals(obj, System.DBNull.Value))){cmdresult &＃61; 0;}else{cmdresult &＃61; int.Parse(obj.ToString()); //也可能&＃61;0
}if (cmdresult &＃61;&＃61; 0){return false;}else{return true;}}/// /// 表是否存在/// /// /// public static bool TabExists(string TableName){string strsql &＃61; "select count(*) from sysobjects where id &＃61; object_id(N&＃39;[" &＃43; TableName &＃43; "]&＃39;) and OBJECTPROPERTY(id, N&＃39;IsUserTable&＃39;) &＃61; 1";//string strsql &＃61; "SELECT count(*) FROM sys.objects WHERE object_id &＃61; OBJECT_ID(N&＃39;[dbo].[" &＃43; TableName &＃43; "]&＃39;) AND type in (N&＃39;U&＃39;)";object obj &＃61; GetSingle(strsql);int cmdresult;if ((Object.Equals(obj, null)) || (Object.Equals(obj, System.DBNull.Value))){cmdresult &＃61; 0;}else{cmdresult &＃61; int.Parse(obj.ToString());}if (cmdresult &＃61;&＃61; 0){return false;}else{return true;}}public static bool Exists(string strSql, params SqlParameter[] cmdParms){object obj &＃61; GetSingle(strSql, cmdParms);int cmdresult;if ((Object.Equals(obj, null)) || (Object.Equals(obj, System.DBNull.Value))){cmdresult &＃61; 0;}else{cmdresult &＃61; int.Parse(obj.ToString());}if (cmdresult &＃61;&＃61; 0){return false;}else{return true;}}#endregion#region 执行简单SQL语句/// /// 执行SQL语句&＃xff0c;返回影响的记录数/// /// SQL语句/// 影响的记录数public static int ExecuteSql(string SQLString){using (SqlConnection connection &＃61; new SqlConnection(connectionString)){using (SqlCommand cmd &＃61; new SqlCommand(SQLString, connection)){try{connection.Open();int rows &＃61; cmd.ExecuteNonQuery();return rows;}catch (System.Data.SqlClient.SqlException e){connection.Close();throw e;}}}}public static int ExecuteSqlByTime(string SQLString, int Times){using (SqlConnection connection &＃61; new SqlConnection(connectionString)){using (SqlCommand cmd &＃61; new SqlCommand(SQLString, connection)){try{connection.Open();cmd.CommandTimeout &＃61; Times;int rows &＃61; cmd.ExecuteNonQuery();return rows;}catch (System.Data.SqlClient.SqlException e){connection.Close();throw e;}}}}/// /// 执行多条SQL语句&＃xff0c;实现数据库事务。/// /// 多条SQL语句 public static int ExecuteSqlTran(List SQLStringList){using (SqlConnection conn &＃61; new SqlConnection(connectionString)){conn.Open();SqlCommand cmd &＃61; new SqlCommand();cmd.Connection &＃61; conn;SqlTransaction tx &＃61; conn.BeginTransaction();cmd.Transaction &＃61; tx;try{int count &＃61; 0;for (int n &＃61; 0; n ){string strsql &＃61; SQLStringList[n];if (strsql.Trim().Length > 1){cmd.CommandText &＃61; strsql;count &＃43;&＃61; cmd.ExecuteNonQuery();}}tx.Commit();return count;}catch{tx.Rollback();return 0;}}}/// /// 执行带一个存储过程参数的的SQL语句。/// /// SQL语句/// 参数内容,比如一个字段是格式复杂的文章&＃xff0c;有特殊符号&＃xff0c;可以通过这个方式添加/// 影响的记录数public static int ExecuteSql(string SQLString, string content){using (SqlConnection connection &＃61; new SqlConnection(connectionString)){SqlCommand cmd &＃61; new SqlCommand(SQLString, connection);System.Data.SqlClient.SqlParameter myParameter &＃61; new System.Data.SqlClient.SqlParameter("&＃64;content", SqlDbType.NText);myParameter.Value &＃61; content;cmd.Parameters.Add(myParameter);try{connection.Open();int rows &＃61; cmd.ExecuteNonQuery();return rows;}catch (System.Data.SqlClient.SqlException e){throw e;}finally{cmd.Dispose();connection.Close();}}}/// /// 执行带一个存储过程参数的的SQL语句。/// /// SQL语句/// 参数内容,比如一个字段是格式复杂的文章&＃xff0c;有特殊符号&＃xff0c;可以通过这个方式添加/// 影响的记录数public static object ExecuteSqlGet(string SQLString, string content){using (SqlConnection connection &＃61; new SqlConnection(connectionString)){SqlCommand cmd &＃61; new SqlCommand(SQLString, connection);System.Data.SqlClient.SqlParameter myParameter &＃61; new System.Data.SqlClient.SqlParameter("&＃64;content", SqlDbType.NText);myParameter.Value &＃61; content;cmd.Parameters.Add(myParameter);try{connection.Open();object obj &＃61; cmd.ExecuteScalar();if ((Object.Equals(obj, null)) || (Object.Equals(obj, System.DBNull.Value))){return null;}else{return obj;}}catch (System.Data.SqlClient.SqlException e){throw e;}finally{cmd.Dispose();connection.Close();}}}/// /// 向数据库里插入图像格式的字段(和上面情况类似的另一种实例)/// /// SQL语句/// 图像字节,数据库的字段类型为image的情况/// 影响的记录数public static int ExecuteSqlInsertImg(string strSQL, byte[] fs){using (SqlConnection connection &＃61; new SqlConnection(connectionString)){SqlCommand cmd &＃61; new SqlCommand(strSQL, connection);System.Data.SqlClient.SqlParameter myParameter &＃61; new System.Data.SqlClient.SqlParameter("&＃64;fs", SqlDbType.Image);myParameter.Value &＃61; fs;cmd.Parameters.Add(myParameter);try{connection.Open();int rows &＃61; cmd.ExecuteNonQuery();return rows;}catch (System.Data.SqlClient.SqlException e){throw e;}finally{cmd.Dispose();connection.Close();}}}/// /// 执行一条计算查询结果语句&＃xff0c;返回查询结果&＃xff08;object&＃xff09;。/// /// 计算查询结果语句/// 查询结果&＃xff08;object&＃xff09;public static object GetSingle(string SQLString){using (SqlConnection connection &＃61; new SqlConnection(connectionString)){using (SqlCommand cmd &＃61; new SqlCommand(SQLString, connection)){try{connection.Open();object obj &＃61; cmd.ExecuteScalar();if ((Object.Equals(obj, null)) || (Object.Equals(obj, System.DBNull.Value))){return null;}else{return obj;}}catch (System.Data.SqlClient.SqlException e){connection.Close();throw e;}}}}public static object GetSingle(string SQLString, int Times){using (SqlConnection connection &＃61; new SqlConnection(connectionString)){using (SqlCommand cmd &＃61; new SqlCommand(SQLString, connection)){try{connection.Open();cmd.CommandTimeout &＃61; Times;object obj &＃61; cmd.ExecuteScalar();if ((Object.Equals(obj, null)) || (Object.Equals(obj, System.DBNull.Value))){return null;}else{return obj;}}catch (System.Data.SqlClient.SqlException e){connection.Close();throw e;}}}}/// /// 执行查询语句&＃xff0c;返回SqlDataReader ( 注意&＃xff1a;调用该方法后&＃xff0c;一定要对SqlDataReader进行Close )/// /// 查询语句/// SqlDataReaderpublic static SqlDataReader ExecuteReader(string strSQL){SqlConnection connection &＃61; new SqlConnection(connectionString);SqlCommand cmd &＃61; new SqlCommand(strSQL, connection);try{connection.Open();SqlDataReader myReader &＃61; cmd.ExecuteReader(CommandBehavior.CloseConnection);return myReader;}catch (System.Data.SqlClient.SqlException e){throw e;}}/// /// 执行查询语句&＃xff0c;返回DataSet/// /// 查询语句/// DataSetpublic static DataSet Query(string SQLString){using (SqlConnection connection &＃61; new SqlConnection(connectionString)){DataSet ds &＃61; new DataSet();try{connection.Open();SqlDataAdapter command &＃61; new SqlDataAdapter(SQLString, connection);command.Fill(ds, "ds");}catch (System.Data.SqlClient.SqlException ex){throw new Exception(ex.Message);}return ds;}}public static DataSet Query(string SQLString, int Times){using (SqlConnection connection &＃61; new SqlConnection(connectionString)){DataSet ds &＃61; new DataSet();try{connection.Open();SqlDataAdapter command &＃61; new SqlDataAdapter(SQLString, connection);command.SelectCommand.CommandTimeout &＃61; Times;command.Fill(ds, "ds");}catch (System.Data.SqlClient.SqlException ex){throw new Exception(ex.Message);}return ds;}}#endregion#region 执行带参数的SQL语句/// /// 执行SQL语句&＃xff0c;返回影响的记录数/// /// SQL语句/// 影响的记录数public static int ExecuteSql(string SQLString, params SqlParameter[] cmdParms){using (SqlConnection connection &＃61; new SqlConnection(connectionString)){using (SqlCommand cmd &＃61; new SqlCommand()){try{PrepareCommand(cmd, connection, null, SQLString, cmdParms);int rows &＃61; cmd.ExecuteNonQuery();cmd.Parameters.Clear();return rows;}catch (System.Data.SqlClient.SqlException e){throw e;}}}}/// /// 执行多条SQL语句&＃xff0c;实现数据库事务。/// /// SQL语句的哈希表&＃xff08;key为sql语句&＃xff0c;value是该语句的SqlParameter[]&＃xff09;public static void ExecuteSqlTran(Hashtable SQLStringList){using (SqlConnection conn &＃61; new SqlConnection(connectionString)){conn.Open();using (SqlTransaction trans &＃61; conn.BeginTransaction()){SqlCommand cmd &＃61; new SqlCommand();try{//循环foreach (DictionaryEntry myDE in SQLStringList){string cmdText &＃61; myDE.Key.ToString();SqlParameter[] cmdParms &＃61; (SqlParameter[])myDE.Value;PrepareCommand(cmd, conn, trans, cmdText, cmdParms);int val &＃61; cmd.ExecuteNonQuery();cmd.Parameters.Clear();}trans.Commit();}catch{trans.Rollback();throw;}}}}/// /// 执行多条SQL语句&＃xff0c;实现数据库事务。/// /// SQL语句的哈希表&＃xff08;key为sql语句&＃xff0c;value是该语句的SqlParameter[]&＃xff09;public static int ExecuteSqlTran(System.Collections.Generic.List cmdList){using (SqlConnection conn &＃61; new SqlConnection(connectionString)){conn.Open();using (SqlTransaction trans &＃61; conn.BeginTransaction()){SqlCommand cmd &＃61; new SqlCommand();try{int count &＃61; 0;//循环foreach (CommandInfo myDE in cmdList){string cmdText &＃61; myDE.CommandText;SqlParameter[] cmdParms &＃61; (SqlParameter[])myDE.Parameters;PrepareCommand(cmd, conn, trans, cmdText, cmdParms);if (myDE.EffentNextType &＃61;&＃61; EffentNextType.WhenHaveContine || myDE.EffentNextType &＃61;&＃61; EffentNextType.WhenNoHaveContine){if (myDE.CommandText.ToLower().IndexOf("count(") &＃61;&＃61; -1){trans.Rollback();return 0;}object obj &＃61; cmd.ExecuteScalar();bool isHave &＃61; false;if (obj &＃61;&＃61; null && obj &＃61;&＃61; DBNull.Value){isHave &＃61; false;}isHave &＃61; Convert.ToInt32(obj) > 0;if (myDE.EffentNextType &＃61;&＃61; EffentNextType.WhenHaveContine && !isHave){trans.Rollback();return 0;}if (myDE.EffentNextType &＃61;&＃61; EffentNextType.WhenNoHaveContine && isHave){trans.Rollback();return 0;}continue;}int val &＃61; cmd.ExecuteNonQuery();count &＃43;&＃61; val;if (myDE.EffentNextType &＃61;&＃61; EffentNextType.ExcuteEffectRows && val &＃61;&＃61; 0){trans.Rollback();return 0;}cmd.Parameters.Clear();}trans.Commit();return count;}catch{trans.Rollback();throw;}}}}/// /// 执行多条SQL语句&＃xff0c;实现数据库事务。/// /// SQL语句的哈希表&＃xff08;key为sql语句&＃xff0c;value是该语句的SqlParameter[]&＃xff09;public static void ExecuteSqlTranWithIndentity(System.Collections.Generic.List SQLStringList){using (SqlConnection conn &＃61; new SqlConnection(connectionString)){conn.Open();using (SqlTransaction trans &＃61; conn.BeginTransaction()){SqlCommand cmd &＃61; new SqlCommand();try{int indentity &＃61; 0;//循环foreach (CommandInfo myDE in SQLStringList){string cmdText &＃61; myDE.CommandText;SqlParameter[] cmdParms &＃61; (SqlParameter[])myDE.Parameters;foreach (SqlParameter q in cmdParms){if (q.Direction &＃61;&＃61; ParameterDirection.InputOutput){q.Value &＃61; indentity;}}PrepareCommand(cmd, conn, trans, cmdText, cmdParms);int val &＃61; cmd.ExecuteNonQuery();foreach (SqlParameter q in cmdParms){if (q.Direction &＃61;&＃61; ParameterDirection.Output){indentity &＃61; Convert.ToInt32(q.Value);}}cmd.Parameters.Clear();}trans.Commit();}catch{trans.Rollback();throw;}}}}/// /// 执行多条SQL语句&＃xff0c;实现数据库事务。/// /// SQL语句的哈希表&＃xff08;key为sql语句&＃xff0c;value是该语句的SqlParameter[]&＃xff09;public static void ExecuteSqlTranWithIndentity(Hashtable SQLStringList){using (SqlConnection conn &＃61; new SqlConnection(connectionString)){conn.Open();using (SqlTransaction trans &＃61; conn.BeginTransaction()){SqlCommand cmd &＃61; new SqlCommand();try{int indentity &＃61; 0;//循环foreach (DictionaryEntry myDE in SQLStringList){string cmdText &＃61; myDE.Key.ToString();SqlParameter[] cmdParms &＃61; (SqlParameter[])myDE.Value;foreach (SqlParameter q in cmdParms){if (q.Direction &＃61;&＃61; ParameterDirection.InputOutput){q.Value &＃61; indentity;}}PrepareCommand(cmd, conn, trans, cmdText, cmdParms);int val &＃61; cmd.ExecuteNonQuery();foreach (SqlParameter q in cmdParms){if (q.Direction &＃61;&＃61; ParameterDirection.Output){indentity &＃61; Convert.ToInt32(q.Value);}}cmd.Parameters.Clear();}trans.Commit();}catch{trans.Rollback();throw;}}}}/// /// 执行一条计算查询结果语句&＃xff0c;返回查询结果&＃xff08;object&＃xff09;。/// /// 计算查询结果语句/// 查询结果&＃xff08;object&＃xff09;public static object GetSingle(string SQLString, params SqlParameter[] cmdParms){using (SqlConnection connection &＃61; new SqlConnection(connectionString)){using (SqlCommand cmd &＃61; new SqlCommand()){try{PrepareCommand(cmd, connection, null, SQLString, cmdParms);object obj &＃61; cmd.ExecuteScalar();cmd.Parameters.Clear();if ((Object.Equals(obj, null)) || (Object.Equals(obj, System.DBNull.Value))){return null;}else{return obj;}}catch (Exception e){Console.WriteLine(e.Message);return null;//throw e;
}}}}/// /// 执行查询语句&＃xff0c;返回SqlDataReader ( 注意&＃xff1a;调用该方法后&＃xff0c;一定要对SqlDataReader进行Close )/// /// 查询语句/// SqlDataReaderpublic static SqlDataReader ExecuteReader(string SQLString, params SqlParameter[] cmdParms){SqlConnection connection &＃61; new SqlConnection(connectionString);SqlCommand cmd &＃61; new SqlCommand();try{PrepareCommand(cmd, connection, null, SQLString, cmdParms);SqlDataReader myReader &＃61; cmd.ExecuteReader(CommandBehavior.CloseConnection);cmd.Parameters.Clear();return myReader;}catch (System.Data.SqlClient.SqlException e){throw e;}// finally// {// cmd.Dispose();// connection.Close();// }
}/// /// 执行查询语句&＃xff0c;返回DataSet/// /// 查询语句/// DataSetpublic static DataSet Query(string SQLString, params SqlParameter[] cmdParms){using (SqlConnection connection &＃61; new SqlConnection(connectionString)){SqlCommand cmd &＃61; new SqlCommand();PrepareCommand(cmd, connection, null, SQLString, cmdParms);using (SqlDataAdapter da &＃61; new SqlDataAdapter(cmd)){DataSet ds &＃61; new DataSet();try{da.Fill(ds, "ds");cmd.Parameters.Clear();}catch (System.Data.SqlClient.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 !&＃61; ConnectionState.Open)conn.Open();cmd.Connection &＃61; conn;cmd.CommandText &＃61; cmdText;if (trans !&＃61; null)cmd.Transaction &＃61; trans;cmd.CommandType &＃61; CommandType.Text;//cmdType;if (cmdParms !&＃61; null){foreach (SqlParameter parameter in cmdParms){if ((parameter.Direction &＃61;&＃61; ParameterDirection.InputOutput || parameter.Direction &＃61;&＃61; ParameterDirection.Input) &&(parameter.Value &＃61;&＃61; null)){parameter.Value &＃61; DBNull.Value;}cmd.Parameters.Add(parameter);}}}#endregion#region 存储过程操作/// /// 执行存储过程&＃xff0c;返回SqlDataReader ( 注意&＃xff1a;调用该方法后&＃xff0c;一定要对SqlDataReader进行Close )/// /// 存储过程名/// 存储过程参数/// SqlDataReaderpublic static SqlDataReader RunProcedure(string storedProcName, IDataParameter[] parameters){SqlConnection connection &＃61; new SqlConnection(connectionString);SqlDataReader returnReader;connection.Open();SqlCommand command &＃61; BuildQueryCommand(connection, storedProcName, parameters);command.CommandType &＃61; CommandType.StoredProcedure;returnReader &＃61; command.ExecuteReader(CommandBehavior.CloseConnection);return returnReader;}/// /// 执行存储过程/// /// 存储过程名/// 存储过程参数/// DataSet结果中的表名/// DataSetpublic static DataSet RunProcedure(string storedProcName, IDataParameter[] parameters, string tableName){using (SqlConnection connection &＃61; new SqlConnection(connectionString)){DataSet dataSet &＃61; new DataSet();connection.Open();SqlDataAdapter sqlDA &＃61; new SqlDataAdapter();sqlDA.SelectCommand &＃61; BuildQueryCommand(connection, storedProcName, parameters);sqlDA.Fill(dataSet, tableName);connection.Close();return dataSet;}}public static DataSet RunProcedure(string storedProcName, IDataParameter[] parameters, string tableName, int Times){using (SqlConnection connection &＃61; new SqlConnection(connectionString)){DataSet dataSet &＃61; new DataSet();connection.Open();SqlDataAdapter sqlDA &＃61; new SqlDataAdapter();sqlDA.SelectCommand &＃61; BuildQueryCommand(connection, storedProcName, parameters);sqlDA.SelectCommand.CommandTimeout &＃61; Times;sqlDA.Fill(dataSet, tableName);connection.Close();return dataSet;}}/// /// 构建 SqlCommand 对象(用来返回一个结果集&＃xff0c;而不是一个整数值)/// /// 数据库连接/// 存储过程名/// 存储过程参数/// SqlCommandprivate static SqlCommand BuildQueryCommand(SqlConnection connection, string storedProcName, IDataParameter[] parameters){SqlCommand command &＃61; new SqlCommand(storedProcName, connection);command.CommandType &＃61; CommandType.StoredProcedure;foreach (SqlParameter parameter in parameters){if (parameter !&＃61; null){// 检查未分配值的输出参数,将其分配以DBNull.Value.if ((parameter.Direction &＃61;&＃61; ParameterDirection.InputOutput || parameter.Direction &＃61;&＃61; ParameterDirection.Input) &&(parameter.Value &＃61;&＃61; null)){parameter.Value &＃61; DBNull.Value;}command.Parameters.Add(parameter);}}return command;}/// /// 执行存储过程&＃xff0c;返回影响的行数 /// /// 存储过程名/// 存储过程参数/// 影响的行数/// public static int RunProcedure(string storedProcName, IDataParameter[] parameters, out int rowsAffected){using (SqlConnection connection &＃61; new SqlConnection(connectionString)){int result;connection.Open();SqlCommand command &＃61; BuildIntCommand(connection, storedProcName, parameters);rowsAffected &＃61; command.ExecuteNonQuery();result &＃61; (int)command.Parameters["ReturnValue"].Value;//Connection.Close();return result;}}/// /// 创建 SqlCommand 对象实例(用来返回一个整数值) /// /// 存储过程名/// 存储过程参数/// SqlCommand 对象实例private static SqlCommand BuildIntCommand(SqlConnection connection, string storedProcName, IDataParameter[] parameters){SqlCommand command &＃61; 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}
}
京公网安备 11010802041100号