您现在的位置是:首页 > .NET

.NET

适用于Asp.net的完整的DbHelperSQL类

2020-11-21 11:29:59 .NET admin
usingSystem;usingSystem.Collections;usingSystem.Collections.Generic;usingSystem.Configuration;usingSystem.Data;usingSyst
usingSystem;
usingSystem.Collections;
usingSystem.Collections.Generic;
usingSystem.Configuration;
usingSystem.Data;
usingSystem.Data.SqlClient;

namespaceDBUtility
{
publicabstractclassDbHelperSQL
{
publicDbHelperSQL()
{

}

//获取解密后的字符串
protectedstaticstringconnectionString=newSymmetricMethod().Decrypto(ConfigurationSettings.AppSettings["connectionString"].ToString());

publicstaticintGetMaxID(stringFieldName,stringTableName)
{
stringstrsql="selectisnull(max("+FieldName+"),0)+1from"+TableName;
objectobj=DbHelperSQL.GetSingle(strsql);
if(obj==null)
{
return1;

}
else
{
returnint.Parse(obj.ToString());
}
}

publicstaticboolExists(stringstrSql)
{
objectobj=DbHelperSQL.GetSingle(strSql);
intcmdresult;
if((Object.Equals(obj,null))||(Object.Equals(obj,System.DBNull.Value)))
{
cmdresult=0;
}
else
{
cmdresult=int.Parse(obj.ToString());
}
if(cmdresult==0)
{
returnfalse;
}
else
{
returntrue;
}
}

publicstaticboolExists(stringstrSql,paramsSqlParameter[]cmdParms)
{
objectobj=DbHelperSQL.GetSingle(strSql,cmdParms);
intcmdresult;
if((Object.Equals(obj,null))||(Object.Equals(obj,System.DBNull.Value)))
{
cmdresult=0;
}
else
{
cmdresult=int.Parse(obj.ToString());
}
if(cmdresult==0)
{
returnfalse;
}
else
{
returntrue;
}
}

///<summary>
///返回连接
///</summary>
///<returns></returns>
publicstaticSqlConnectionGetConnection()
{
stringcurrentConnectionString=connectionString;
if(!string.IsNullOrEmpty(System.Web.HttpContext.Current.User.Identity.Name))
{
currentConnectionString=currentConnectionString+";ApplicationName=ForegroundUserID="+System.Web.HttpContext.Current.User.Identity.Name;
}
returnnewSqlConnection(currentConnectionString);
}

///<summary>
///执行SQL语句,返回影响的记录数
///</summary>
///<paramname="SQLString">SQL语句</param>
///<returns>影响的记录数</returns>
publicstaticintExecuteSql(stringSQLString)
{
using(SqlConnectionconnection=GetConnection())
{
using(SqlCommandcmd=newSqlCommand(SQLString,connection))
{
try
{
connection.Open();
introws=cmd.ExecuteNonQuery();
returnrows;
}
catch(System.Data.SqlClient.SqlExceptionE)
{
thrownewException(E.Message);
}
finally
{
connection.Close();
}
}
}
}

///<summary>
///执行一条计算查询结果语句,返回查询结果(object)。
///</summary>
///<paramname="SQLString">计算查询结果语句</param>
///<returns>查询结果(object)</returns>
publicstaticobjectGetSingle(stringSQLString)
{
using(SqlConnectionconnection=GetConnection())
{
using(SqlCommandcmd=newSqlCommand(SQLString,connection))
{
try
{
connection.Open();
objectobj=cmd.ExecuteScalar();
if((Object.Equals(obj,null))||(Object.Equals(obj,System.DBNull.Value)))
{
returnnull;
}
else
{
returnobj;
}
}
catch(System.Data.SqlClient.SqlExceptione)
{
thrownewException(e.Message);
}
finally
{
cmd.Dispose();
connection.Close();
}
}
}
}

publicstaticobjectExecuteScalar(stringstrSQL)
{
using(SqlConnectionconn=GetConnection())
{
if(conn.State==ConnectionState.Closed)
conn.Open();

SqlCommandcmd=newSqlCommand();
cmd.Connection=conn;
cmd.CommandType=CommandType.Text;
cmd.CommandText=strSQL;

objectresult=cmd.ExecuteScalar();

cmd.Parameters.Clear();
returnresult;
}
}

publicstaticobjectExecuteScalar(stringstrSQL,paramsSqlParameter[]paramter)
{
using(SqlConnectionconn=GetConnection())
{
if(conn.State==ConnectionState.Closed)
conn.Open();

SqlCommandcmd=newSqlCommand();
cmd.Connection=conn;
cmd.CommandType=CommandType.Text;
cmd.CommandText=strSQL;

if(paramter!=null)
{
foreach(SqlParameterparinparamter)
{
cmd.Parameters.Add(par);
}
}

objectresult=cmd.ExecuteScalar();

cmd.Parameters.Clear();
returnresult;
}
}

///<summary>
///执行查询语句,返回DataSet
///</summary>
///<paramname="SQLString">查询语句</param>
///<returns>DataSet</returns>
publicstaticDataSetQuery(stringSQLString)
{
using(SqlConnectionconnection=GetConnection())
{
DataSetds=newDataSet();
try
{
connection.Open();
SqlDataAdaptercommand=newSqlDataAdapter(SQLString,connection);
command.Fill(ds,"ds");
}
catch(System.Data.SqlClient.SqlExceptionex)
{
#ifDEBUG
thrownewException(ex.Message+SQLString);
#endif
thrownewException(ex.Message);
}
finally
{
connection.Close();
}
returnds;
}
}

///<summary>
///获取数据集
///</summary>
///<paramname="SQLString"></param>
///<paramname="dtname"></param>
///<returns></returns>
publicstaticDataSetQuery(stringSQLString,stringdtname)
{
using(SqlConnectionconnection=GetConnection())
{
DataSetds=newDataSet();
try
{
connection.Open();
SqlDataAdaptercommand=newSqlDataAdapter(SQLString,connection);
command.Fill(ds,dtname);
}
catch(System.Data.SqlClient.SqlExceptionex)
{
thrownewException(ex.Message);
}
finally
{
connection.Close();
}
returnds;
}
}

///<summary>
///
///</summary>
///<paramname="SQLString"></param>
///<paramname="dtname"></param>
///<paramname="ds"></param>
///<returns></returns>
publicstaticDataSetQuery(stringSQLString,stringdtname,refDataSetds)
{
using(SqlConnectionconnection=GetConnection())
{
try
{
connection.Open();
SqlDataAdaptercommand=newSqlDataAdapter(SQLString,connection);
command.Fill(ds,dtname);
}
catch(System.Data.SqlClient.SqlExceptionex)
{
thrownewException(ex.Message);
}
finally
{
connection.Close();
}
returnds;
}
}

///<summary>
///执行SQL语句,返回影响的记录数
///</summary>
///<paramname="SQLString">SQL语句</param>
///<returns>影响的记录数</returns>
publicstaticintExecuteSql(stringSQLString,paramsSqlParameter[]cmdParms)
{
using(SqlConnectionconnection=GetConnection())
{
using(SqlCommandcmd=newSqlCommand())
{
try
{
PrepareCommand(cmd,connection,null,SQLString,cmdParms);
introws=cmd.ExecuteNonQuery();
cmd.Parameters.Clear();
returnrows;
}
catch(System.Data.SqlClient.SqlExceptionE)
{
thrownewException(E.Message);
}
finally
{
}
}
}
}

///<summary>
///执行多条SQL语句,实现数据库事务。
///</summary>
///<paramname="SQLStringList">SQL语句的哈希表(key为sql语句,value是该语句的SqlParameter[])</param>
publicstaticvoidExecuteSqlTran(HashtableSQLStringList)
{
using(SqlConnectionconn=GetConnection())
{
conn.Open();
using(SqlTransactiontrans=conn.BeginTransaction())
{
objectmyDeValue=null;
SqlCommandcmd=newSqlCommand();
try
{
foreach(DictionaryEntrymyDEinSQLStringList)
{
SqlParameter[]cmdParms=(SqlParameter[])myDE.Value;
myDeValue=myDE.Value;

PrepareCommand(cmd,conn,trans,myDE.Key.ToString(),cmdParms);
intval=cmd.ExecuteNonQuery();
cmd.Parameters.Clear();
}
}
catch(Exceptionex)
{
throwex;
}
trans.Commit();
}
}
}

publicstaticList<int>ExecuteSqlTranRunID(IList<DictionaryEntry>SQLStringList,SqlConnectionconn,SqlTransactiontrans)
{
List<int>list=newList<int>();
if(conn.State==ConnectionState.Closed)conn.Open();
SqlCommandcmd=newSqlCommand();
try
{
foreach(DictionaryEntrymyDEinSQLStringList)
{
SqlParameter[]cmdParms=(SqlParameter[])myDE.Value;
PrepareCommand(cmd,conn,trans,myDE.Key.ToString(),cmdParms);
list.Add(int.Parse(cmd.ExecuteScalar().ToString()));
cmd.Parameters.Clear();
}
}
catch(Exceptionex)
{
trans.Rollback();
throwex;
}
finally
{

}
returnlist;
}

///<summary>
///执行事务,且事务中第一条Sql的返回值作为后面所有Sql最后一个参数的值
///</summary>
///<paramname="SqlStringList"></param>
publicstaticvoidExecuteSqlTrans(List<DictionaryEntry>SqlStringList)
{
using(SqlConnectionconn=GetConnection())
{
conn.Open();
using(SqlTransactiontrans=conn.BeginTransaction())
{
SqlCommandcmd=newSqlCommand();
intidentity=0;
stringcmdText;
SqlParameter[]parameter;
try
{
for(inti=0;i<SqlStringList.Count;i++)
{
cmdText=SqlStringList[i].Key.ToString();
parameter=(SqlParameter[])SqlStringList[i].Value;
if(i==0)
{
PrepareCommand(cmd,conn,trans,cmdText,parameter);
identity=int.Parse(cmd.ExecuteScalar().ToString());
cmd.Parameters.Clear();
}
else
{
if(parameter[parameter.Length-1].Value==DBNull.Value||decimal.Parse(parameter[parameter.Length-1].Value.ToString())==0)
{
parameter[parameter.Length-1].Value=identity;
}
PrepareCommand(cmd,conn,trans,cmdText,parameter);
cmd.ExecuteNonQuery();
cmd.Parameters.Clear();
}
}
trans.Commit();
}
catch(Exceptione)
{
trans.Rollback();
throwe;
}
}
}
}

///<summary>
///执行多条SQL语句,实现数据库事务。
///</summary>
///<paramname="SQLStringList">SQL语句的有序表(key为sql语句,value是该语句的SqlParameter[])</param>
publicstaticvoidExecuteSqlTran(IList<DictionaryEntry>SQLStringList)
{
using(SqlConnectionconn=GetConnection())
{
conn.Open();
using(SqlTransactiontrans=conn.BeginTransaction())
{
objectmyDeValue=null;
SqlCommandcmd=newSqlCommand();
try
{
//循环
foreach(DictionaryEntrymyDEinSQLStringList)
{
if(myDE.Key==null||string.IsNullOrEmpty(myDE.Key.ToString()))
{
continue;
}
else
{
stringcmdText=myDE.Key.ToString();
SqlParameter[]cmdParms=(SqlParameter[])myDE.Value;
myDeValue=myDE.Value;
PrepareCommand(cmd,conn,trans,cmdText,cmdParms);
intval=cmd.ExecuteNonQuery();
cmd.Parameters.Clear();
}
}
trans.Commit();

}
catch(SqlExceptionex)
{
trans.Rollback();
throwex;
}

}

}
}

///<summary>
///执行一条计算查询结果语句,返回查询结果(object)。
///</summary>
///<paramname="SQLString">计算查询结果语句</param>
///<returns>查询结果(object)</returns>
publicstaticobjectGetSingle(stringSQLString,paramsSqlParameter[]cmdParms)
{
using(SqlConnectionconnection=GetConnection())
{
using(SqlCommandcmd=newSqlCommand())
{
try
{
PrepareCommand(cmd,connection,null,SQLString,cmdParms);
objectobj=cmd.ExecuteScalar();
cmd.Parameters.Clear();
if((Object.Equals(obj,null))||(Object.Equals(obj,System.DBNull.Value)))
{
returnnull;
}
else
{
returnobj;
}
}
catch(System.Data.SqlClient.SqlExceptione)
{
thrownewException(e.Message);
}
finally
{

}
}
}
}

///<summary>
///执行查询语句,返回DataSet
///</summary>
///<paramname="SQLString">查询语句</param>
///<returns>DataSet</returns>
publicstaticDataSetQuery(stringSQLString,paramsSqlParameter[]cmdParms)
{
using(SqlConnectionconnection=GetConnection())
{
SqlCommandcmd=newSqlCommand();
PrepareCommand(cmd,connection,null,SQLString,cmdParms);
using(SqlDataAdapterda=newSqlDataAdapter(cmd))
{
DataSetds=newDataSet();
try
{
da.Fill(ds,"ds");
cmd.Parameters.Clear();
}
catch(System.Data.SqlClient.SqlExceptionex)
{
#ifDEBUG
thrownewException(ex.Message+SQLString);
#endif
thrownewException(ex.Message);
}
finally
{
}
returnds;
}
}
}

privatestaticvoidPrepareCommand(SqlCommandcmd,SqlConnectionconn,SqlTransactiontrans,stringcmdText,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;
if(cmdParms!=null)
{
foreach(SqlParameterparameterincmdParms)
{
if((parameter.Direction==ParameterDirection.InputOutput||parameter.Direction==ParameterDirection.Input)&&
(parameter.Value==null))
{
parameter.Value=DBNull.Value;
}

if(parameter.Direction==ParameterDirection.InputOutput||parameter.Direction==ParameterDirection.Input)
{
if(parameter.Value.ToString()==DateTime.MinValue.ToString()||parameter.Value.ToString()==int.MinValue.ToString())
{
parameter.Value=DBNull.Value;
}
}

cmd.Parameters.Add(parameter);
}
}
}

///<summary>
///执行存储过程
///</summary>
///<paramname="storedProcName">存储过程名</param>
///<returns>SqlDataReader</returns>
publicstaticDataSetRunProcedure(stringstoredProcName)
{
using(SqlConnectionconnection=GetConnection())
{
DataSetds=newDataSet();
SqlDataAdapterda=newSqlDataAdapter();
connection.Open();

SqlCommandcommand=newSqlCommand();
command.CommandType=CommandType.StoredProcedure;
command.CommandText=storedProcName;
command.Connection=connection;
da.SelectCommand=command;

da.Fill(ds);
connection.Close();
returnds;
}
}

///<summary>
///执行存储过程
///</summary>
///<paramname="storedProcName">存储过程名</param>
///<paramname="parameters">存储过程参数</param>
///<paramname="tableName">DataSet结果中的表名</param>
///<returns>DataSet</returns>
publicstaticDataSetRunProcedure(stringstoredProcName,IDataParameter[]parameters,stringtableName)
{
try
{
using(SqlConnectionconnection=GetConnection())
{
DataSetdataSet=newDataSet();
connection.Open();
SqlDataAdaptersqlDA=newSqlDataAdapter();
sqlDA.SelectCommand=BuildQueryCommand(connection,storedProcName,parameters);
sqlDA.Fill(dataSet);
connection.Close();
returndataSet;
}
}
catch(System.Data.SqlClient.SqlExceptionE)
{
thrownewException(E.Message);
}
}

///<summary>
///执行存储过程
///</summary>
///<paramname="storedProcName">存储过程名</param>
///<paramname="parameters">存储过程参数</param>
///<paramname="tableName">DataSet结果中的表名</param>
///<returns></returns>
publicstaticDataSetRunProcedureDT(stringstoredProcName,IDataParameter[]parameters,stringtableName,refDataSetdataSet)
{
using(SqlConnectionconnection=GetConnection())
{
connection.Open();
SqlDataAdaptersqlDA=newSqlDataAdapter();
sqlDA.SelectCommand=BuildQueryCommand(connection,storedProcName,parameters);
sqlDA.Fill(dataSet);
connection.Close();
returndataSet;
}
}

///<summary>
///构建SqlCommand对象(用来返回一个结果集,而不是一个整数值)
///</summary>
///<paramname="connection">数据库连接</param>
///<paramname="storedProcName">存储过程名</param>
///<paramname="parameters">存储过程参数</param>
///<returns>SqlCommand</returns>
privatestaticSqlCommandBuildQueryCommand(SqlConnectionconnection,stringstoredProcName,IDataParameter[]parameters)
{
SqlCommandcommand=newSqlCommand(storedProcName,connection);
command.CommandType=CommandType.StoredProcedure;
foreach(SqlParameterparameterinparameters)
{
if(parameter!=null)
{
//检查未分配值的输出参数,将其分配以DBNull.Value.
if((parameter.Direction==ParameterDirection.InputOutput||parameter.Direction==ParameterDirection.Input)&&
(parameter.Value==null))
{
parameter.Value=DBNull.Value;
}
command.Parameters.Add(parameter);
}
}
returncommand;
}
}
}