using System;
using System.Data;
using System.Configuration;
using System.Data.SqlClient;
namespace DAL
{
/// <summary>
/// 数据库帮助类
/// </summary>
public abstract class DatabaseHelper
{
# region 成员变量
/// <summary>
/// 数据库连接字符串
/// </summary>
private static string m_ConnectionString = null;
# endregion
# region 属性
/// <summary>
/// 获取数据库连接字符串
/// </summary>
/// <returns></returns>
public string ConnectionString
{
get
{
if (m_ConnectionString == null)
{
m_ConnectionString = ReadConnectionString("ConnectionString");
}
return m_ConnectionString;
}
}
# endregion
# region 构造函数
public DatabaseHelper()
{
m_ConnectionString = ReadConnectionString("ConnectionString");
}
public DatabaseHelper(string connectionString)
{
m_ConnectionString = connectionString;
}
# endregion
# region 静态方法
/// <summary>
/// 从配置文件读取数据库连接字符串
/// </summary>
/// <param name="configKeyName"></param>
/// <returns></returns>
private static string ReadConnectionString(string configKeyName)
{
string connectionString = "";
object obj = ConfigurationManager.ConnectionStrings[configKeyName];
if (obj != null)
{
connectionString = obj.ToString();
}
return connectionString;
}
/// <summary>
/// 获取数据库连接字符串
/// </summary>
/// <returns></returns>
public static string GetConnectionString()
{
if (m_ConnectionString == null)
{
m_ConnectionString = ReadConnectionString("ConnectionString");
}
return m_ConnectionString;
}
#endregion
#region 抽象方法
protected abstract object DataReaderToEntity(IDataReader dataReader);
#endregion
#region 数据库操作方法
/// <summary>
/// 构造数据库操作命令
/// </summary>
/// <param name="conn">数据库连接对象</param>
/// <param name="cmdString">sql语句</param>
private SqlCommand CreateCommand(SqlConnection conn, string cmdString)
{
SqlCommand cmd = new SqlCommand();
cmd.Connection = conn;
cmd.CommandType = CommandType.Text;
cmd.CommandText = cmdString;
return cmd;
}
/// <summary>
/// 清空参数,释放SqlCommand资源
/// </summary>
/// <param name="cmd"></param>
private void DisposeCommand(SqlCommand cmd)
{
if (cmd != null)
{
cmd.Parameters.Clear();
cmd.Dispose();
}
}
/// <summary>
/// 绑定SqlCommand参数,参数值为空时不添加
/// </summary>
/// <param name="cmd"></param>
/// <param name="parameters"></param>
private void AttachParameters(SqlCommand cmd, params SqlParameter[] parameters)
{
if (parameters != null)
{
foreach (SqlParameter para in parameters)
{
if (para != null)
{
if (para.Direction == ParameterDirection.InputOutput ||
(para.Direction == ParameterDirection.Input && para.Value == null))
{
para.Value = DBNull.Value;
}
cmd.Parameters.Add(para);
}
}
}
}
/// <summary>
/// 执行一个查询,返回查询的结果集
/// </summary>
/// <param name="sqlString">sql语句</param>
/// <param name="parameters">参数化参数</param>
/// <returns>查询的结果集</returns>
public DataTable ExecuteDataTable(string sqlString, params SqlParameter[] parameters)
{
DataTable dataTable = new DataTable();
using (SqlConnection conn = new SqlConnection(m_ConnectionString))
{
conn.Open();
SqlCommand cmd = CreateCommand(conn, sqlString);
AttachParameters(cmd, parameters);
SqlDataAdapter adapter = new SqlDataAdapter(cmd);
adapter.Fill(dataTable);
DisposeCommand(cmd);
}
return dataTable;
}
/// <summary>
/// 执行sql操作,返回受影响的行数
/// </summary>
/// <param name="sqlString">sql语句</param>
/// <param name="parameters">参数化参数</param>
/// <returns>受影响的行数</returns>
public int ExecuteNonQuery(string sqlString, params SqlParameter[] parameters)
{
int num = 0;
using (SqlConnection conn = new SqlConnection(m_ConnectionString))
{
conn.Open();
SqlCommand cmd = CreateCommand(conn, sqlString);
AttachParameters(cmd, parameters);
num = cmd.ExecuteNonQuery();
DisposeCommand(cmd);
}
return num;
}
/// <summary>
/// 执行查询,并返回查询所返回的结果集中第一行的第一列。忽略其他列或行。
/// </summary>
/// <param name="sqlString">sql语句</param>
/// <param name="parameters">参数化参数</param>
/// <returns>结果集中第一行的第一列;如果结果集为空,则为空引用</returns>
public object ExecuteScalar(string sqlString, params SqlParameter[] parameters)
{
object obj = new object();
using (SqlConnection conn = new SqlConnection(m_ConnectionString))
{
conn.Open();
SqlCommand cmd = CreateCommand(conn, sqlString);
AttachParameters(cmd, parameters);
obj = cmd.ExecuteScalar();
DisposeCommand(cmd);
}
return obj;
}
public SqlDataReader ExecuteReader(string sqlString, params SqlParameter[] parameters)
{
SqlDataReader reader = null;
SqlConnection conn = new SqlConnection(m_ConnectionString);
conn.Open();
SqlCommand cmd = CreateCommand(conn, sqlString);
AttachParameters(cmd, parameters);
reader = cmd.ExecuteReader(System.Data.CommandBehavior.CloseConnection);
DisposeCommand(cmd);
return reader;
}
# endregion
}
}
ADO.NET 封装的数据库帮助类
最新推荐文章于 2024-08-17 02:29:26 发布