/// <summary>
/// 数据库帮助类
/// <author>vito</author>
/// </summary>
public class DBHelper
{
8
/// <summary>
/// 数据库连接字符串
/// </summary>
private string connStr = System.Configuration.ConfigurationManager.AppSettings["connStr"].ToString(); /// <summary>
/// 一个有效的数据库连接对象
/// </summary>
private SqlConnection _MyConnection;
public SqlConnection MyConnection
{
get
{
if(_MyConnection == null)
{
_MyConnection = new SqlConnection(connStr);
}
return _MyConnection;
}
} /// <summary>
/// 返回受影响行数
/// </summary>
/// <param name="cmdText">SQL语句</param>
/// <param name="commandParameters">参数集</param>
/// <returns></returns>
public int MyExecuteNonQuery(string cmdText, params SqlParameter[] commandParameters)
{
int result = ;
SqlCommand cmd = new SqlCommand();
SqlConnection conn = MyConnection;
try
{
PrepareCommand(cmd, conn, null, cmdText, commandParameters);
result = cmd.ExecuteNonQuery();
}
catch(SqlException ex)
{
throw new Exception(ex.Message);
}
finally
{
cmd.Parameters.Clear();
}
return result;
} /// <summary>
/// 返回DataSet
/// </summary>
/// <param name="cmdText">SQL语句</param>
/// <param name="commandParameters">参数集</param>
/// <returns></returns>
public DataSet MyExecuteDataset(string cmdText, params SqlParameter[] commandParameters)
{
DataSet ds = new DataSet();
SqlConnection conn = MyConnection;
SqlCommand cmd = new SqlCommand();
PrepareCommand(cmd, conn, null, cmdText, commandParameters);
using (SqlDataAdapter da = new SqlDataAdapter(cmd))
{
try
{
da.Fill(ds);
}
catch (SqlException ex)
{
throw new Exception(ex.Message);
}
finally
{
cmd.Parameters.Clear();
}
}
return ds;
} /// <summary>
/// 返回DataReader (使用后请关闭DataReader)
/// </summary>
/// <param name="cmdText">SQL语句</param>
/// <param name="commandParameters">参数集</param>
/// <returns></returns>
public SqlDataReader MyExecuteReader(string cmdText, params SqlParameter[] commandParameters)
{
SqlConnection conn = MyConnection;
SqlCommand cmd = new SqlCommand();
try
{
PrepareCommand(cmd,conn,null,cmdText,commandParameters);
SqlDataReader myReader = cmd.ExecuteReader(CommandBehavior.CloseConnection);//关闭DataReader的同时，Connection也将同时关闭
return myReader;
}
catch(SqlException ex)
{
throw new Exception(ex.Message);
}
finally
{
cmd.Parameters.Clear();
}
} /// <summary>
/// 返回首行首列的值
/// </summary>
/// <param name="cmdText">SQL语句</param>
/// <param name="commandParameters">参数集</param>
/// <returns></returns>
public object MyExecuteScalar(string cmdText,params SqlParameter[] commandParameters)
{
SqlConnection conn = MyConnection;
SqlCommand cmd = new SqlCommand();
try
{
PrepareCommand(cmd,conn,null,cmdText,commandParameters);
return cmd.ExecuteScalar();
}
catch(SqlException ex)
{
throw new Exception(ex.Message);
}
finally
{
cmd.Parameters.Clear();
}
} /// <summary>
/// 设置SqlCommand
/// </summary>
/// <param name="cmd">执行对象</param>
/// <param name="conn">连接对象</param>
/// <param name="trans">事务</param>
/// <param name="cmdText">SQL语句</param>
/// <param name="cmdParms">参数集</param>
private 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;
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);
}
}
}
}