/// <summary>
/// 功能描述：执行无参数SQL语句或者存储过程，返回Scalar值；
/// 创建人： 陈国迎
/// 创建日期：2006-5-15
/// </summary>
/// <param name="strCommandText">命令文本</param>
/// <returns>Object</returns>
public static object ExecuteScalar(string strCommandText)
{
//解决SQL的COUNT语句无法进行ORDER BY的问题
if (strCommandText.Contains("count(*)") && strCommandText.Contains("order by"))
{
strCommandText = strCommandText.Substring(0, strCommandText.IndexOf("order by"));
}
SqlCommand objCommand = new SqlCommand();
object intReturn = null;
using (SqlConnection objConnection = new SqlConnection(strConnectionStatic))
{
PrepareCommand(objCommand, objConnection, null, CommandType.Text, strCommandText);
try
{
intReturn = objCommand.ExecuteScalar();
}
catch (Exception ex)
{
Tips.AppendLine(ex.Message);
}
finally
{
objCommand.Dispose();
objConnection.Close();
objConnection.Dispose();
}
return(intReturn);
}
}
/// <summary>
/// 功能描述：执行无参数SQL语句或者存储过程，返回影响行数
/// 创建人： 陈国迎
/// 创建日期：2006-5-15
/// </summary>
/// <param name="strCommandType">命令类型</param>
/// <param name="strCommandText">命令文本</param>
/// <returns>影响行数</returns>
public static int ExecuteNonQuery(CommandType strCommandType, string strCommandText)
{
SqlCommand objCommand = new SqlCommand();
int intReturn = -1;
using (SqlConnection objConnection = new SqlConnection(strConnectionStatic))
{
PrepareCommand(objCommand, objConnection, null, strCommandType, strCommandText);
try
{
intReturn = objCommand.ExecuteNonQuery();
}
catch (Exception ex)
{
Tips.AppendLine(ex.Message);
}
finally
{
objCommand.Dispose();
objConnection.Close();
objConnection.Dispose();
}
return(intReturn);
}
}
/// <summary>
/// 功能描述：执行无参数SQL语句列表或者存储过程，返回执行结果
/// 创建人： 石磊
/// 创建日期：2008-01-04
/// </summary>
/// <param name="strCommandText">命令文本列表</param>
/// <returns>返回执行结果</returns>
public static bool ExecuteSQLTextList(string[] strCommandTextList)
{
SqlCommand objCommand = new SqlCommand();
objCommand.CommandType = CommandType.Text;
SqlConnection objConnection = new SqlConnection(strConnectionStatic);
if (objConnection.State != ConnectionState.Open)
{
objConnection.Open();
}
SqlTransaction objTransaction = objConnection.BeginTransaction();
try
{
ExecuteListCommand(objCommand, objConnection, objTransaction, CommandType.Text, strCommandTextList);
objTransaction.Commit();
}
catch (Exception ex)
{
objTransaction.Rollback();
Tips.AppendLine(ex.Message);
return(false);
}
finally
{
objCommand.Dispose();
objConnection.Close();
objConnection.Dispose();
}
return(true);
}
/// <summary>
/// 功能描述：执行无参数SQL语句或者存储过程，返回DatatSet
/// 创建人： 陈国迎
/// 创建日期：2006-5-15
/// </summary>
/// <param name="strConnection">连接字符串</param>
/// <param name="strCommandType">命令类型</param>
/// <param name="strCommandText">命令文本</param>
/// <returns>DataSet</returns>
public static DataSet ExecuteDataSet(string strConnection, CommandType strCommandType, string strCommandText)
{
SqlCommand objCommand = new SqlCommand(strCommandText);
objCommand.CommandType = strCommandType;
SqlConnection objConnection = new SqlConnection(strConnection);
SqlDataAdapter objAdapter = new SqlDataAdapter(objCommand);
DataSet objDataSet = new DataSet();
try
{
PrepareCommand(objCommand, objConnection, null, strCommandType, strCommandText);
objAdapter.Fill(objDataSet);
}
catch (Exception ex)
{
Tips.AppendLine(ex.Message);
}
finally
{
objAdapter.Dispose();
objCommand.Dispose();
objConnection.Close();
objConnection.Dispose();
}
return(objDataSet);
}
/// <summary>
/// 功能描述：执行无参数SQL语句或者存储过程，返回DataTable
/// 创建人： 陈国迎
/// 创建日期：2006-5-15
/// </summary>
/// <param name="strCommandText">命令文本</param>
/// <returns>DataTable</returns>
public static DataTable ExecuteDataTable(string strCommandText)
{
SqlCommand objCommand = new SqlCommand(strCommandText);
objCommand.CommandType = CommandType.Text;
SqlConnection objConnection = new SqlConnection(strConnectionStatic);
SqlDataAdapter objAdapter = new SqlDataAdapter(objCommand);
DataTable objTable = new DataTable();
try
{
PrepareCommand(objCommand, objConnection, null, CommandType.Text, strCommandText);
objAdapter.Fill(objTable);
}
catch (Exception ex)
{
Tips.AppendLine(ex.Message);
}
finally
{
objAdapter.Dispose();
objCommand.Dispose();
objConnection.Close();
objConnection.Dispose();
}
return(objTable);
}
/// <summary>
/// 执行存储过程,返回执行结果,无需connection
/// </summary>
/// <param name="OracleCmd">执行命令</param>
/// <returns></returns>
public static bool ExecuteProcedure(OracleCommand OracleCmd)
{
OracleConnection objConnection = new OracleConnection(strConnectionStatic);
try
{
if (objConnection.State != ConnectionState.Open)
{
objConnection.Open();
}
OracleCmd.Connection = objConnection;
OracleCmd.ExecuteNonQuery();
}
catch (Exception ex)
{
Tips.AppendLine(ex.Message);
return(false);
}
finally
{
objConnection.Close();
OracleCmd.Dispose();
objConnection.Dispose();
}
return(true);
}
/// <summary>
/// 功能描述：执行无参数SQL语句或者存储过程，返回DataReader
/// 创建人： 陈国迎
/// 创建日期：2006-5-15
/// </summary>
/// <param name="strCommandText">命令文本</param>
/// <returns>DataReader</returns>
public static SqlDataReader ExecuteReader(string strCommandText)
{
SqlCommand objCommand = new SqlCommand();
SqlConnection objConnection = new SqlConnection(strConnectionStatic);
SqlDataReader objDataReader = null;
try
{
PrepareCommand(objCommand, objConnection, null, CommandType.Text, strCommandText);
objDataReader = objCommand.ExecuteReader(CommandBehavior.CloseConnection);
}
catch (Exception ex)
{
Tips.AppendLine(ex.Message);
}
finally
{
objDataReader.Dispose();
objCommand.Dispose();
objConnection.Close();
objConnection.Dispose();
}
return(objDataReader);
}
/// <summary>
/// 功能描述：执行无参数SQL语句或者存储过程，返回Scalar值；
/// 创建人： 陈国迎
/// 创建日期：2006-5-15
/// </summary>
/// <param name="strCommandType">命令类型</param>
/// <param name="strCommandText">命令文本</param>
/// <returns>Object</returns>
public static object ExecuteScalar(CommandType strCommandType, string strCommandText)
{
OracleCommand objCommand = new OracleCommand();
object intReturn = null;
using (OracleConnection objConnection = new OracleConnection(strConnectionStatic))
{
PrepareCommand(objCommand, objConnection, null, strCommandType, strCommandText);
try
{
intReturn = objCommand.ExecuteScalar();
}
catch (Exception ex)
{
Tips.AppendLine(ex.Message);
}
finally
{
objCommand.Dispose();
objConnection.Close();
objConnection.Dispose();
}
return(intReturn);
}
}
/// <summary>
/// 执行存储过程，返回DataTable
/// </summary>
/// <param name="OracleCmd"></param>
/// <returns></returns>
public static DataTable ExecuteDataTable(OracleCommand OracleCmd)
{
DataTable objTable = new DataTable();
OracleConnection objConnection = new OracleConnection(strConnectionStatic);
OracleDataAdapter objAdapter = new OracleDataAdapter();
try
{
if (objConnection.State != ConnectionState.Open)
{
objConnection.Open();
}
OracleCmd.Connection = objConnection;
objAdapter.SelectCommand = OracleCmd;
objAdapter.Fill(objTable);
}
catch (Exception ex)
{
Tips.AppendLine(ex.Message);
}
finally
{
objConnection.Close();
objAdapter.Dispose();
OracleCmd.Dispose();
objConnection.Close();
objConnection.Dispose();
}
return(objTable);
}
/// <summary>
/// 执行存储过程，返回影响行数
/// </summary>
/// <param name="OracleCmd"></param>
/// <returns></returns>
public static int ExecuteNonQuery(OracleCommand OracleCmd)
{
int intReturn = -1;
using (OracleConnection objConnection = new OracleConnection(strConnectionStatic))
{
try
{
if (objConnection.State != ConnectionState.Open)
{
objConnection.Open();
}
OracleCmd.Connection = objConnection;
intReturn = OracleCmd.ExecuteNonQuery();
}
catch (Exception ex)
{
Tips.AppendLine(ex.Message);
}
finally
{
objConnection.Close();
OracleCmd.Dispose();
objConnection.Dispose();
}
return(intReturn);
}
}
/// <summary>
/// 执行事务（刘静楠 2013/7/15）
/// </summary>
/// <param name="strSql">一个或多个要执行的sql语句</param>
/// <returns></returns>
public static string ExecuteListSQLByTransaction(ArrayList strSql)
{
string Error = string.Empty;
if (strSql.Count < 1)
{
return(Error = "传入的集合为空");
}
SqlCommand objCommand = new SqlCommand();
SqlTransaction objTransaction;
using (SqlConnection objConnection = new SqlConnection(strConnectionStatic))
{
if (objConnection.State != ConnectionState.Open)
{
objConnection.Open();
}
objTransaction = objConnection.BeginTransaction();
try
{
foreach (string str in strSql)
{
PrepareCommand(objCommand, objConnection, objTransaction, CommandType.Text, str);
objCommand.ExecuteNonQuery();
}
objTransaction.Commit();
return(Error);
}
catch (Exception ex)
{
Tips.AppendLine(ex.Message);
objTransaction.Rollback();
return(Error = ex.Message);
}
finally
{
objCommand.Dispose();
objConnection.Close();
objConnection.Dispose();
objTransaction.Dispose();
}
}
}
/// <summary>
/// 执行事务
/// </summary>
/// <param name="strSql">一个或多个要执行的sql语句</param>
/// <returns></returns>
public static bool ExecuteSQLByTransaction(ArrayList strSql)
{
if (strSql.Count < 1)
{
return(true);
}
OracleCommand objCommand = new OracleCommand();
OracleTransaction objTransaction;
using (OracleConnection objConnection = new OracleConnection(strConnectionStatic))
{
if (objConnection.State != ConnectionState.Open)
{
objConnection.Open();
}
objTransaction = objConnection.BeginTransaction();
try
{
foreach (string str in strSql)
{
PrepareCommand(objCommand, objConnection, objTransaction, CommandType.Text, str);
objCommand.ExecuteNonQuery();
}
objTransaction.Commit();
return(true);
}
catch (Exception ex)
{
Tips.AppendLine(ex.Message);
objTransaction.Rollback();
return(false);
}
finally
{
objCommand.Dispose();
objConnection.Close();
objConnection.Dispose();
objTransaction.Dispose();
}
}
}
/// <summary>
/// 执行SQL存储过程，返回string类型
/// </summary>
/// <param name="StoreName">存储过程名称</param>
/// <param name="Parameters">参数集</param>
/// <returns></returns>
public static string ExecuteStored(string StoreName, string[] paramNames, DbType[] paramTypes, int[] paramSizes, ParameterDirection[] pd, object[] InputparamValues)
{
SqlCommand objCommand = new SqlCommand();
SqlConnection objConnection = new SqlConnection(strConnectionStatic);
string OutParameterName = "";
string ReturnValue = "";
try
{
for (int i = 0; i < paramNames.Length; ++i)
{
DbParameter p = objCommand.CreateParameter();
p.ParameterName = "@" + paramNames[i].Replace("@", "");
p.Direction = pd[i];
p.Size = paramSizes[i];
p.DbType = paramTypes[i];
p.Value = InputparamValues[i];
objCommand.Parameters.Add(p);
if (pd[i] == ParameterDirection.Output)
{
OutParameterName = p.ParameterName;
}
}
PrepareCommand(objCommand, objConnection, null, CommandType.StoredProcedure, StoreName);
objCommand.ExecuteNonQuery();
ReturnValue = objCommand.Parameters[OutParameterName].Value.ToString();
}
catch (Exception ex)
{
Tips.AppendLine(ex.Message);
}
finally
{
objCommand.Dispose();
objConnection.Close();
objConnection.Dispose();
}
return(ReturnValue);
}