using System;
using System.Collections;
using System.Collections.Specialized;
using System.Data;
using System.Data.SqlClient;
using System.Configuration;
using System.Data.Common;
using System.Collections.Generic;
namespaceDAL
{
publicabstract classDbHelperSQL
{
#region mine
/// <summary>
/// DBHelper.cs
/// </summary>
publicclass DBHelper
{
privatestatic stringConnectionString = ConfigurationManager.AppSettings["ConnectionString"];
protectedstatic SqlConnection conn;
publicDBHelper()
{
}
/// <summary>
/// 创建SqlConnection连接
/// </summary>
/// <returns>返回SqlConnection对象</returns>
publicstatic SqlConnection CreateConnection()
{
conn =new SqlConnection (ConnectionString);
conn.Open();
returnconn;
}
/// <summary>
/// 执行存储过程
/// </summary>
/// <param name="storedProcedure">存储过程语句</param>
/// <returns>返回SqlCommand对象</returns>
publicstatic SqlCommand GetStoredProcCommand(stringstoredProcedure)
{
SqlCommand cmd =new SqlCommand (storedProcedure,conn);
cmd.CommandType= CommandType.StoredProcedure;
returncmd;
}
/// <summary>
/// 执行Sql语句
/// </summary>
/// <param name="sqlQuery">Sql语句</param>
/// <returns>返回SqlCommand对象</returns>
publicstatic SqlCommand GetSqlStringCommand(stringsqlQuery)
{
SqlCommand cmd =new SqlCommand (sqlQuery,conn);
cmd.CommandType= CommandType.Text;
returncmd;
}
#region 增加参数
/// <summary>
/// 增加参数
/// </summary>
/// <param name="cmd">cmd对象</param>
/// <param name="ParametersCollection">SqlParameterCollection数据集合</param>
publicvoid AddParameterCollection(SqlCommand cmd, SqlParameterCollection ParametersCollection)
{
foreach(SqlParameter Parameters inParametersCollection)
{
cmd.Parameters.Add(Parameters);
}
}
/// <summary>
/// 增加参数
/// </summary>
/// <param name="cmd">cmd对象</param>
/// <param name="parameterName">parameterName</param>
/// <param name="sqlType">类型</param>
/// <param name="size">大小</param>
publicstatic voidAddInParameter(SqlCommand cmd, stringparameterName, SqlDbType sqlType, intsize)
{
SqlParameter parameters = cmd.CreateParameter();
parameters.SqlDbType = sqlType;
parameters.ParameterName = parameterName;
parameters.Size = size;
parameters.Direction = ParameterDirection.Input;
cmd.Parameters.Add(parameters);
}
publicstatic voidAddInParameter(SqlCommand cmd, stringparameterName, SqlDbType sqlType, objectvalue)
{
SqlParameter parameters = cmd.CreateParameter();
parameters.SqlDbType = sqlType;
parameters.ParameterName = parameterName;
parameters.Value = value;
parameters.Direction = ParameterDirection.Input;
cmd.Parameters.Add(parameters);
}
publicstatic voidAddInParameter(SqlCommand cmd, stringparameterName, SqlDbType sqlType)
{
SqlParameter parameters = cmd.CreateParameter();
parameters.SqlDbType = sqlType;
parameters.ParameterName = parameterName;
parameters.Direction= ParameterDirection.Input;
cmd.Parameters.Add(parameters);
}
publicstatic SqlParameter GetParameter(SqlCommand cmd,string parameterName)
{
returncmd.Parameters[parameterName];
}
#endregion
#region 执行
/// <summary>
/// 执行SqlCommand命令，返回DataSet对象
/// </summary>
/// <param name="cmd">SqlCommand对象</param>
/// <returns>返回DataSet对象</returns>
publicstatic DataSet ExecuteDataSet(SqlCommand cmd)
{
SqlDataAdapter da =new SqlDataAdapter (cmd);
DataSet ds =new DataSet ();
da.Fill(ds);
returnds;
}
/// <summary>
/// 执行SqlCommand命令，返回DataTable对象
/// </summary>
/// <param name="cmd">SqlCommand命令</param>
/// <returns>返回DataTable对象</returns>
publicstatic DataTable ExecuteDataTable(SqlCommand cmd)
{
SqlDataAdapter da =new SqlDataAdapter (cmd);
DataTable dt =new DataTable ();
da.Fill(dt);
returndt;
}
/// <summary>
/// 执行SqlCommand命令，返回SqlDataReader对象
/// </summary>
/// <param name="cmd">SqlCommand命令</param>
/// <returns>返回SqlDataReader对象</returns>
publicstatic SqlDataReader ExecuteReader(SqlCommand cmd)
{
SqlConnection conn = DBHelper.CreateConnection();
SqlDataReader reader = cmd.ExecuteReader();
returnreader;
}
/// <summary>
/// 执行SqlCommand命令，返回Int
///</summary>
/// <param name="cmd">SqlCommand命令</param>
/// <returns>返回Int</returns>
publicstatic intExecuteNonQuery(SqlCommand cmd)
{
SqlConnection sqlconn = DBHelper.CreateConnection();
intret = cmd.ExecuteNonQuery();
sqlconn.Close();
returnret;
}
/// <summary>
/// 执行SqlCommand命令，返回Object
/// </summary>
/// <param name="cmd">SqlCommand命令</param>
/// <returns>返回Object</returns>
publicstatic objectExecuteScalar(SqlCommand cmd)
{
SqlConnection sqlconn = DBHelper.CreateConnection();
objectret = cmd.ExecuteScalar();
sqlconn.Close();
returnret;
}
#endregion
#endregion
publicstatic stringconnectionString = ConfigurationManager.ConnectionStrings["ConnStr"].ToString();
publicDbHelperSQL()
{
}
#region 公用方法
/// <summary>
/// 判断是否存在某表的某个字段
/// </summary>
/// <param name="tableName">表名称</param>
/// <param name="columnName">列名称</param>
/// <returns>是否存在</returns>
publicstatic boolColumnExists(stringtableName, stringcolumnName)
{
stringsql = "select count(1) from syscolumns where [id]=object_id('"+ tableName + "') and [name]='"+ columnName + "'";
objectres = GetSingle(sql);
if(res == null)
{
returnfalse;
}
returnConvert.ToInt32(res) > 0;
}
publicstatic intGetMaxID(stringFieldName, stringTableName)
{
stringstrsql = "select max("+ FieldName + ")+1 from "+ TableName;
objectobj = GetSingle(strsql);
if(obj == null)
{
return1;
}
else
{
returnint.Parse(obj.ToString());
}
}
publicstatic boolExists(stringstrSql)
{
objectobj = 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;
}
}
/// <summary>
/// 表是否存在
/// </summary>
/// <param name="TableName"></param>
/// <returns></returns>
publicstatic boolTabExists(stringTableName)
{
stringstrsql = "select count(*) from sysobjects where id = object_id(N'["+ TableName + "]') and OBJECTPROPERTY(id, N'IsUserTable') = 1";
objectobj = 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;
}
}
publicstatic boolExists(stringstrSql, paramsSqlParameter[] cmdParms)
{
objectobj = 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;
}
}
#endregion
#region 执行简单SQL语句
/// <summary>
/// 执行SQL语句，返回影响的记录数
/// </summary>
/// <param name="SQLString">SQL语句</param>
/// <returns>影响的记录数</returns>
publicstatic intExecuteSql(stringSQLString)
{
using(SqlConnection connection = newSqlConnection(connectionString))
{
using(SqlCommand cmd = newSqlCommand(SQLString, connection))
{
try
{
connection.Open();
introws = cmd.ExecuteNonQuery();
returnrows;
}
catch(System.Data.SqlClient.SqlException e)
{
connection.Close();
throwe;
}
}
}
}
publicstatic intExecuteSqlByTime(stringSQLString, intTimes)
{
using(SqlConnection connection = newSqlConnection(connectionString))
{
using(SqlCommand cmd = newSqlCommand(SQLString, connection))
{
try
{
connection.Open();
cmd.CommandTimeout = Times;
introws = cmd.ExecuteNonQuery();
returnrows;
}
catch(System.Data.SqlClient.SqlException e)
{
connection.Close();
throwe;
}
}
}
}
/// <summary>
/// 执行多条SQL语句，实现数据库事务。
/// </summary>
/// <param name="SQLStringList">多条SQL语句</param>
publicstatic intExecuteSqlTran(List<String> SQLStringList)
{
using(SqlConnection conn = newSqlConnection(connectionString))
{
conn.Open();
SqlCommand cmd =new SqlCommand();
cmd.Connection = conn;
SqlTransaction tx = conn.BeginTransaction();
cmd.Transaction = tx;
try
{
intcount = 0;
for(int n = 0; n < SQLStringList.Count; n++)
{
stringstrsql = SQLStringList[n];
if(strsql.Trim().Length > 1)
{
cmd.CommandText = strsql;
count += cmd.ExecuteNonQuery();
}
}
tx.Commit();
returncount;
}
catch
{
tx.Rollback();
return0;
}
}
}
/// <summary>
/// 执行带一个存储过程参数的的SQL语句。
/// </summary>
/// <param name="SQLString">SQL语句</param>
/// <param name="content">参数内容,比如一个字段是格式复杂的文章，有特殊符号，可以通过这个方式添加</param>
/// <returns>影响的记录数</returns>
publicstatic intExecuteSql(stringSQLString, stringcontent)
{
using(SqlConnection connection = newSqlConnection(connectionString))
{
SqlCommand cmd =new SqlCommand(SQLString, connection);
System.Data.SqlClient.SqlParameter myParameter =new System.Data.SqlClient.SqlParameter("@content", SqlDbType.NText);
myParameter.Value = content;
cmd.Parameters.Add(myParameter);
try
{
connection.Open();
introws = cmd.ExecuteNonQuery();
returnrows;
}
catch(System.Data.SqlClient.SqlException e)
{
throwe;
}
finally
{
cmd.Dispose();
connection.Close();
}
}
}
/// <summary>
/// 执行带一个存储过程参数的的SQL语句。
/// </summary>
/// <param name="SQLString">SQL语句</param>
/// <param name="content">参数内容,比如一个字段是格式复杂的文章，有特殊符号，可以通过这个方式添加</param>
/// <returns>影响的记录数</returns>
publicstatic objectExecuteSqlGet(stringSQLString, stringcontent)
{
using(SqlConnection connection = newSqlConnection(connectionString))
{
SqlCommand cmd =new SqlCommand(SQLString, connection);
System.Data.SqlClient.SqlParameter myParameter =new System.Data.SqlClient.SqlParameter("@content", SqlDbType.NText);
myParameter.Value = content;
cmd.Parameters.Add(myParameter);
try
{
connection.Open();
objectobj = cmd.ExecuteScalar();
if((Object.Equals(obj, null)) || (Object.Equals(obj, System.DBNull.Value)))
{
returnnull;
}
else
{
returnobj;
}
}
catch(System.Data.SqlClient.SqlException e)
{
throwe;
}
finally
{
cmd.Dispose();
connection.Close();
}
}
}
/// <summary>
/// 向数据库里插入图像格式的字段(和上面情况类似的另一种实例)
/// </summary>
/// <param name="strSQL">SQL语句</param>
/// <param name="fs">图像字节,数据库的字段类型为image的情况</param>
/// <returns>影响的记录数</returns>
publicstatic intExecuteSqlInsertImg(stringstrSQL, byte[] fs)
{
using(SqlConnection connection = newSqlConnection(connectionString))
{
SqlCommand cmd =new SqlCommand(strSQL, connection);
System.Data.SqlClient.SqlParameter myParameter =new System.Data.SqlClient.SqlParameter("@fs", SqlDbType.Image);
myParameter.Value = fs;
cmd.Parameters.Add(myParameter);
try
{
connection.Open();
introws = cmd.ExecuteNonQuery();
returnrows;
}
catch(System.Data.SqlClient.SqlException e)
{
throwe;
}
finally
{
cmd.Dispose();
connection.Close();
}
}
}
/// <summary>
/// 执行一条计算查询结果语句，返回查询结果（object）。
/// </summary>
/// <param name="SQLString">计算查询结果语句</param>
/// <returns>查询结果（object）</returns>
publicstatic objectGetSingle(stringSQLString)
{
using(SqlConnection connection = newSqlConnection(connectionString))
{
using(SqlCommand cmd = 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.SqlException e)
{
connection.Close();
throwe;
}
}
}
}
publicstatic objectGetSingle(stringSQLString, intTimes)
{
using(SqlConnection connection = newSqlConnection(connectionString))
{
using(SqlCommand cmd = newSqlCommand(SQLString, connection))
{
try
{
connection.Open();
cmd.CommandTimeout = Times;
objectobj = cmd.ExecuteScalar();
if((Object.Equals(obj, null)) || (Object.Equals(obj, System.DBNull.Value)))
{
returnnull;
}
else
{
returnobj;
}
}
catch(System.Data.SqlClient.SqlException e)
{
connection.Close();
throwe;
}
}
}
}
/// <summary>
/// 执行查询语句，返回SqlDataReader ( 注意：调用该方法后，一定要对SqlDataReader进行Close )
/// </summary>
/// <param name="strSQL">查询语句</param>
/// <returns>SqlDataReader</returns>
publicstatic SqlDataReader ExecuteReader(stringstrSQL)
{
SqlConnection connection =new SqlConnection(connectionString);
SqlCommand cmd =new SqlCommand(strSQL, connection);
try
{
connection.Open();
SqlDataReader myReader = cmd.ExecuteReader(CommandBehavior.CloseConnection);
returnmyReader;
}
catch(System.Data.SqlClient.SqlException e)
{
throwe;
}
}
/// <summary>
/// 执行查询语句，返回DataSet
/// </summary>
/// <param name="SQLString">查询语句</param>
/// <returns>DataSet</returns>
publicstatic DataSet Query(stringSQLString)
{
using(SqlConnection connection = newSqlConnection(connectionString))
{
DataSet ds =new DataSet();
try
{
connection.Open();
SqlDataAdapter command =new SqlDataAdapter(SQLString, connection);
command.Fill(ds,"ds");
}
catch(System.Data.SqlClient.SqlException ex)
{
thrownew Exception(ex.Message);
}
returnds;
}
}
publicstatic DataSet Query(stringSQLString, intTimes)
{
using(SqlConnection connection = newSqlConnection(connectionString))
{
DataSet ds =new DataSet();
try
{
connection.Open();
SqlDataAdapter command =new SqlDataAdapter(SQLString, connection);
command.SelectCommand.CommandTimeout = Times;
command.Fill(ds,"ds");
}
catch(System.Data.SqlClient.SqlException ex)
{
thrownew Exception(ex.Message);
}
returnds;
}
}
#endregion
#region 执行带参数的SQL语句
/// <summary>
/// 执行SQL语句，返回影响的记录数
/// </summary>
/// <param name="SQLString">SQL语句</param>
/// <returns>影响的记录数</returns>
publicstatic intExecuteSql(stringSQLString, paramsSqlParameter[] cmdParms)
{
using(SqlConnection connection = newSqlConnection(connectionString))
{
using(SqlCommand cmd = newSqlCommand())
{
try
{
PrepareCommand(cmd, connection,null, SQLString, cmdParms);
introws = cmd.ExecuteNonQuery();
cmd.Parameters.Clear();
returnrows;
}
catch(System.Data.SqlClient.SqlException e)
{
throwe;
}
}
}
}
/// <summary>
/// 执行多条SQL语句，实现数据库事务。
/// </summary>
/// <param name="SQLStringList">SQL语句的哈希表（key为sql语句，value是该语句的SqlParameter[]）</param>
publicstatic voidExecuteSqlTran(Hashtable SQLStringList)
{
using(SqlConnection conn = newSqlConnection(connectionString))
{
conn.Open();
using(SqlTransaction trans = conn.BeginTransaction())
{
SqlCommand cmd =new SqlCommand();
try
{
foreach(DictionaryEntry myDE inSQLStringList)
{
stringcmdText = myDE.Key.ToString();
SqlParameter[] cmdParms = (SqlParameter[])myDE.Value;
PrepareCommand(cmd, conn, trans, cmdText, cmdParms);
intval = cmd.ExecuteNonQuery();
cmd.Parameters.Clear();
}
trans.Commit();
}
catch
{
trans.Rollback();
throw;
}
}
}
}
/// <summary>
/// 执行多条SQL语句，实现数据库事务。
/// </summary>
/// <param name="SQLStringList">SQL语句的哈希表（key为sql语句，value是该语句的SqlParameter[]）</param>
publicstatic intExecuteSqlTran(System.Collections.Generic.List<CommandInfo> cmdList)
{
using(SqlConnection conn = newSqlConnection(connectionString))
{
conn.Open();
using(SqlTransaction trans = conn.BeginTransaction())
{
SqlCommand cmd =new SqlCommand();
try
{
intcount = 0;
foreach(CommandInfo myDE incmdList)
{
stringcmdText = myDE.CommandText;
SqlParameter[] cmdParms = (SqlParameter[])myDE.Parameters;
PrepareCommand(cmd, conn, trans, cmdText, cmdParms);
if(myDE.EffentNextType == EffentNextType.WhenHaveContine || myDE.EffentNextType == EffentNextType.WhenNoHaveContine)
{
if(myDE.CommandText.ToLower().IndexOf("count(") == -1)
{
trans.Rollback();
return0;
}
objectobj = cmd.ExecuteScalar();
boolisHave = false;
if(obj == null&& obj == DBNull.Value)
{
isHave =false;
}
isHave = Convert.ToInt32(obj) > 0;
if(myDE.EffentNextType == EffentNextType.WhenHaveContine && !isHave)
{
trans.Rollback();
return0;
}
if(myDE.EffentNextType == EffentNextType.WhenNoHaveContine && isHave)
{
trans.Rollback();
return0;
}
continue;
}
intval = cmd.ExecuteNonQuery();
count += val;
if(myDE.EffentNextType == EffentNextType.ExcuteEffectRows && val == 0)
{
trans.Rollback();
return0;
}
cmd.Parameters.Clear();
}
trans.Commit();
returncount;
}
catch
{
trans.Rollback();
throw;
}
}
}
}
/// <summary>
/// 执行多条SQL语句，实现数据库事务。
/// </summary>
/// <param name="SQLStringList">SQL语句的哈希表（key为sql语句，value是该语句的SqlParameter[]）</param>
publicstatic voidExecuteSqlTranWithIndentity(System.Collections.Generic.List<CommandInfo> SQLStringList)
{
using(SqlConnection conn = newSqlConnection(connectionString))
{
conn.Open();
using(SqlTransaction trans = conn.BeginTransaction())
{
SqlCommand cmd =new SqlCommand();
try
{
intindentity = 0;
foreach(CommandInfo myDE inSQLStringList)
{
stringcmdText = myDE.CommandText;
SqlParameter[] cmdParms = (SqlParameter[])myDE.Parameters;
foreach(SqlParameter q incmdParms)
{
if(q.Direction == ParameterDirection.InputOutput)
{
q.Value = indentity;
}
}
PrepareCommand(cmd, conn, trans, cmdText, cmdParms);
intval = cmd.ExecuteNonQuery();
foreach(SqlParameter q incmdParms)
{
if(q.Direction == ParameterDirection.Output)
{
indentity = Convert.ToInt32(q.Value);
}
}
cmd.Parameters.Clear();
}
trans.Commit();
}
catch
{
trans.Rollback();
throw;
}
}
}
}
/// <summary>
/// 执行多条SQL语句，实现数据库事务。
/// </summary>
/// <param name="SQLStringList">SQL语句的哈希表（key为sql语句，value是该语句的SqlParameter[]）</param>
publicstatic voidExecuteSqlTranWithIndentity(Hashtable SQLStringList)
{
using(SqlConnection conn = newSqlConnection(connectionString))
{
conn.Open();
using(SqlTransaction trans = conn.BeginTransaction())
{
SqlCommand cmd =new SqlCommand();
try
{
intindentity = 0;
foreach(DictionaryEntry myDE inSQLStringList)
{
stringcmdText = myDE.Key.ToString();
SqlParameter[] cmdParms = (SqlParameter[])myDE.Value;
foreach(SqlParameter q incmdParms)
{
if(q.Direction == ParameterDirection.InputOutput)
{
q.Value = indentity;
}
}
PrepareCommand(cmd, conn, trans, cmdText, cmdParms);
intval = cmd.ExecuteNonQuery();
foreach(SqlParameter q incmdParms)
{
if(q.Direction == ParameterDirection.Output)
{
indentity = Convert.ToInt32(q.Value);
}
}
cmd.Parameters.Clear();
}
trans.Commit();
}
catch
{
trans.Rollback();
throw;
}
}
}
}
/// <summary>
/// 执行一条计算查询结果语句，返回查询结果（object）。
/// </summary>
/// <param name="SQLString">计算查询结果语句</param>
/// <returns>查询结果（object）</returns>
publicstatic objectGetSingle(stringSQLString, paramsSqlParameter[] cmdParms)
{
using(SqlConnection connection = newSqlConnection(connectionString))
{
using(SqlCommand cmd = 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.SqlException e)
{
throwe;
}
}
}
}
/// <summary>
/// 执行查询语句，返回SqlDataReader ( 注意：调用该方法后，一定要对SqlDataReader进行Close )
/// </summary>
/// <param name="strSQL">查询语句</param>
/// <returns>SqlDataReader</returns>
publicstatic SqlDataReader ExecuteReader(stringSQLString, paramsSqlParameter[] cmdParms)
{
SqlConnection connection =new SqlConnection(connectionString);
SqlCommand cmd =new SqlCommand();
try
{
PrepareCommand(cmd, connection,null, SQLString, cmdParms);
SqlDataReader myReader = cmd.ExecuteReader(CommandBehavior.CloseConnection);
cmd.Parameters.Clear();
returnmyReader;
}
catch(System.Data.SqlClient.SqlException e)
{
throwe;
}
}
/// <summary>
/// 执行查询语句，返回DataSet
/// </summary>
/// <param name="SQLString">查询语句</param>
/// <returns>DataSet</returns>
publicstatic DataSet Query(stringSQLString, paramsSqlParameter[] cmdParms)
{
using(SqlConnection connection = newSqlConnection(connectionString))
{
SqlCommand cmd =new SqlCommand();
PrepareCommand(cmd, connection,null, SQLString, cmdParms);
using(SqlDataAdapter da = newSqlDataAdapter(cmd))
{
DataSet ds =new DataSet();
try
{
da.Fill(ds,"ds");
cmd.Parameters.Clear();
}
catch(System.Data.SqlClient.SqlException ex)
{
thrownew Exception(ex.Message);
}
returnds;
}
}
}
privatestatic voidPrepareCommand(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;
if(cmdParms != null)
{
foreach(SqlParameter parameter incmdParms)
{
if((parameter.Direction == ParameterDirection.InputOutput || parameter.Direction == ParameterDirection.Input) &&
(parameter.Value ==null))
{
parameter.Value = DBNull.Value;
}
cmd.Parameters.Add(parameter);
}
}
}
#endregion
#region 存储过程操作
/// <summary>
/// 执行存储过程，返回SqlDataReader ( 注意：调用该方法后，一定要对SqlDataReader进行Close )
/// </summary>
/// <param name="storedProcName">存储过程名</param>
/// <param name="parameters">存储过程参数</param>
/// <returns>SqlDataReader</returns>
publicstatic SqlDataReader RunProcedure(stringstoredProcName, IDataParameter[] parameters)
{
SqlConnection connection =new SqlConnection(connectionString);
SqlDataReader returnReader;
connection.Open();
SqlCommand command = BuildQueryCommand(connection, storedProcName, parameters);
command.CommandType = CommandType.StoredProcedure;
returnReader = command.ExecuteReader(CommandBehavior.CloseConnection);
returnreturnReader;
}
/// <summary>
/// 执行存储过程
/// </summary>
/// <param name="storedProcName">存储过程名</param>
/// <param name="parameters">存储过程参数</param>
/// <param name="tableName">DataSet结果中的表名</param>
/// <returns>DataSet</returns>
publicstatic DataSet RunProcedure(stringstoredProcName, IDataParameter[] parameters, stringtableName)
{
using(SqlConnection connection = newSqlConnection(connectionString))
{
DataSet dataSet =new DataSet();
connection.Open();
SqlDataAdapter sqlDA =new SqlDataAdapter();
sqlDA.SelectCommand = BuildQueryCommand(connection, storedProcName, parameters);
sqlDA.Fill(dataSet, tableName);
connection.Close();
returndataSet;
}
}
publicstatic DataSet RunProcedure(stringstoredProcName, IDataParameter[] parameters, stringtableName, intTimes)
{
using(SqlConnection connection = newSqlConnection(connectionString))
{
DataSet dataSet =new DataSet();
connection.Open();
SqlDataAdapter sqlDA =new SqlDataAdapter();
sqlDA.SelectCommand = BuildQueryCommand(connection, storedProcName, parameters);
sqlDA.SelectCommand.CommandTimeout = Times;
sqlDA.Fill(dataSet, tableName);
connection.Close();
returndataSet;
}
}
/// <summary>
/// 构建 SqlCommand 对象(用来返回一个结果集，而不是一个整数值)
/// </summary>
/// <param name="connection">数据库连接</param>
/// <param name="storedProcName">存储过程名</param>
/// <param name="parameters">存储过程参数</param>
/// <returns>SqlCommand</returns>
privatestatic SqlCommand BuildQueryCommand(SqlConnection connection,string storedProcName, IDataParameter[] parameters)
{
SqlCommand command =new SqlCommand(storedProcName, connection);
command.CommandType = CommandType.StoredProcedure;
foreach(SqlParameter parameter inparameters)
{
if(parameter != null)
{
if((parameter.Direction == ParameterDirection.InputOutput || parameter.Direction == ParameterDirection.Input) &&
(parameter.Value ==null))
{
parameter.Value = DBNull.Value;
}
command.Parameters.Add(parameter);
}
}
returncommand;
}
/// <summary>
/// 执行存储过程，返回影响的行数
/// </summary>
/// <param name="storedProcName">存储过程名</param>
/// <param name="parameters">存储过程参数</param>
/// <param name="rowsAffected">影响的行数</param>
/// <returns></returns>
publicstatic intRunProcedure(stringstoredProcName, IDataParameter[] parameters, outint rowsAffected)
{
using(SqlConnection connection = newSqlConnection(connectionString))
{
intresult;
connection.Open();
SqlCommand command = BuildIntCommand(connection, storedProcName, parameters);
rowsAffected = command.ExecuteNonQuery();
result = (int)command.Parameters["ReturnValue"].Value;
returnresult;
}
}
/// <summary>
/// 创建 SqlCommand 对象实例(用来返回一个整数值)
/// </summary>
/// <param name="storedProcName">存储过程名</param>
/// <param name="parameters">存储过程参数</param>
/// <returns>SqlCommand 对象实例</returns>
privatestatic SqlCommand BuildIntCommand(SqlConnection connection,string storedProcName, IDataParameter[] parameters)
{
SqlCommand command = BuildQueryCommand(connection, storedProcName, parameters);
command.Parameters.Add(newSqlParameter("ReturnValue",
SqlDbType.Int, 4, ParameterDirection.ReturnValue,
false, 0, 0,string.Empty, DataRowVersion.Default,null));
returncommand;
}
#endregion
}
publicclass CommandInfo
{
publicobject ShareObject =null;
publicobject OriginalData =null;
eventEventHandler _solicitationEvent;
publicevent EventHandler SolicitationEvent
{
add
{
_solicitationEvent += value;
}
remove
{
_solicitationEvent -= value;
}
}
publicvoid OnSolicitationEvent()
{
if(_solicitationEvent != null)
{
_solicitationEvent(this,new EventArgs());
}
}
publicstring CommandText;
publicSystem.Data.Common.DbParameter[] Parameters;
publicEffentNextType EffentNextType = EffentNextType.None;
publicCommandInfo()
{
}
publicCommandInfo(stringsqlText, SqlParameter[] para)
{
this.CommandText = sqlText;
this.Parameters = para;
}
publicCommandInfo(stringsqlText, SqlParameter[] para, EffentNextType type)
{
this.CommandText = sqlText;
this.Parameters = para;
this.EffentNextType = type;
}
}
publicenum EffentNextType
{
/// <summary>
/// 对其他语句无任何影响
/// </summary>
None,
/// <summary>
/// 当前语句必须为"select count(1) from .."格式，如果存在则继续执行，不存在回滚事务
/// </summary>
WhenHaveContine,
/// <summary>
/// 当前语句必须为"select count(1) from .."格式，如果不存在则继续执行，存在回滚事务
/// </summary>
WhenNoHaveContine,
/// <summary>
/// 当前语句影响到的行数必须大于0，否则回滚事务
/// </summary>
ExcuteEffectRows,
/// <summary>
/// 引发事件-当前语句必须为"select count(1) from .."格式，如果不存在则继续执行，存在回滚事务
/// </summary>
SolicitationEvent
}
}
