/*
*
* User: CoderLu
* Date: 2010/10/28
* Time: 21:16
*
*/
using System;
using System.Text;
using System.Data;
using System.Data.SqlClient;
using System.Configuration;
using System.Collections.Generic;
namespace DAL
{
/// <summary>
/// 执行数据基础操作的数据库访问类
/// </summary>
public class DataAccess
{
#region 定义连接字符串connStr
/// <summary>
/// 定义连接字符串connStr
/// </summary>
private string connStr = ConfigurationManager.ConnectionStrings["conn"].ToString();
#endregion
#region 声明连接对象,暂不实例化
private SqlConnection connection;
#endregion
#region 默认构造方法,不打开连接
/// <summary>
/// 默认构造方法,不打开连接
/// </summary>
public DataAccess()
{
//OpenConn();
}
#endregion
#region 当连接对象资源被系统回收时，调用关闭连接方法
/// <summary>
/// 当连接对象资源被系统回收时，调用关闭连接方法
/// </summary>
~DataAccess()
{
try
{
if (this.connection != null)
this.connection.Close();
}
catch (Exception) { }
try
{
Dispose();
}
catch { }
}
#endregion
#region 获得连接字符串,仅用于测试而已
/// <summary>
/// 获得连接字符串,仅用于测试而已
/// </summary>
/// <returns>连接字符串</returns>
public string GetConnString()
{
if (this.connStr == null)
{
return "连接还没有打开!";
}
else
{
return this.connStr;
}
}
#endregion
#region 打开数据库连接
/// <summary>
/// 打开数据库连接
/// </summary>
/// <returns>打开成功或原本就已经打开返回true,失败返回false</returns>
public bool OpenConn()
{
bool flag = false;
//如果连接尚未创建,将它实例化
if (this.connection == null)
{
try
{
this.connection = new SqlConnection(this.connStr);
this.connection.Open();
flag = true;
}
catch (Exception){ }
}
//如果连接已经关闭,重新打开
else if (this.connection.State.Equals(ConnectionState.Closed))
{
try
{
this.connection.Open();
flag = true;
}
catch (Exception){ }
}
else { flag = true; }
return flag;
}
#endregion
#region 关闭数据库连接
/// <summary>
/// 关闭数据库连接
/// </summary>
/// <returns>关闭成功返回true,关闭失败返回false</returns>
public bool CloseConn()
{
try
{
if (this.connection != null)
{
this.connection.Close();
}
return true;
}
catch (Exception)
{
return false;
}
}
#endregion
#region 获得连接
/// <summary>
/// 获得连接
/// </summary>
/// <param name="open">输入true返回已经打开好的连接，false返回对象当前默认状态下的连接</param>
/// <returns>返回当前连接对象</returns>
public SqlConnection GetConnection(bool open)
{
if (open == true) this.OpenConn();
return this.connection;
}
#endregion
#region 对SqlCommand的参数进行预先配置
/// <summary>
/// 对SqlCommand的参数进行预先配置
/// </summary>
/// <param name="cmd">要进行参数配置的SqlCommand对象</param>
/// <param name="ct">SqlCommand对象的命令类型</param>
/// <param name="cmdTxt">SqlCommand对象的操作语句</param>
/// <param name="cmdParms">存储SqlCommand对象参数的数组</param>
/// <returns>执行成功返回true,失败返回false</returns>
public bool PrepareCommand(SqlCommand cmd, CommandType ct, string cmdTxt, SqlParameter[] cmdParms)
{
try
{
this.OpenConn();//打开连接
cmd.Connection = this.GetConnection(false);//将命令与连接相关联
cmd.CommandText = cmdTxt;
cmd.CommandType = ct;
//遍历将命令参数添加进来
if (cmdParms != null)
{
foreach (SqlParameter parm in cmdParms)
{
cmd.Parameters.Add(parm);
}
}
return true;
}
catch (Exception) { return false; }
}
#endregion
#region 对数据库进行增、删、改操作
/// <summary>
/// 执行命令，对数据库进行增、删、改操作
/// </summary>
/// <param name="ct">SqlCommand对象的命令类型</param>
/// <param name="cmdTxt">SqlCommand对象的操作语句</param>
/// <param name="cmdParms">存储SqlCommand对象参数的数组</param>
/// <returns>操作执行成功返回true,失败返回false</returns>
public int ExecuteNonQuery(CommandType ct, string cmdTxt, SqlParameter[] cmdParms)
{
try
{
SqlCommand cmd = new SqlCommand();
this.PrepareCommand(cmd, ct, cmdTxt, cmdParms);
int result = cmd.ExecuteNonQuery();
cmd.Parameters.Clear();
return result;
}
catch (Exception)
{
return -1;
}
}
/// <summary>
/// 执行无参的SQL语句，对数据库进行增、删、改操作
/// </summary>
/// <param name="SqlTxt">无参的SQL语句</param>
/// <returns>操作执行成功返回true,失败返回false</returns>
public int ExecuteNonQuery(string SqlTxt)
{
try
{
SqlCommand cmd = new SqlCommand(SqlTxt, this.GetConnection(true));
int result = cmd.ExecuteNonQuery();
return result ;
}
catch (Exception)
{
return -1;
}
}
#endregion
#region 执行聚合函数命令或SQL语句，返回查询结果
/// <summary>
/// 执行聚合函数命令，返回查询结果
/// </summary>
/// <param name="ct">SqlCommand对象的命令类型</param>
/// <param name="cmdTxt">SqlCommand对象的操作语句</param>
/// <param name="cmdParms">存储SqlCommand对象参数的数组</param>
/// <returns>查询结果，大于0表示正常，等于0表示查找无结果，等于-1表示执行异常</returns>
public int ExecuteScalar(CommandType ct, string cmdTxt, SqlParameter[] cmdParms)
{
try
{
SqlCommand cmd = new SqlCommand();
this.PrepareCommand(cmd, ct, cmdTxt, cmdParms);
return (Int32)cmd.ExecuteScalar();
}
catch (Exception)
{
return -1;
}
}
/// <summary>
/// 执行聚合函数SQL语句，返回查询结果
/// </summary>
/// <param name="SqlTxt">SQL查询语句</param>
/// <returns>查询结果，大于0表示正常，等于0表示查找无结果，等于-1表示执行异常</returns>
public int ExecuteScalar(string SqlTxt)
{
try
{
SqlCommand cmd = new SqlCommand(SqlTxt, this.GetConnection(true));
return (Int32)cmd.ExecuteScalar();
}
catch (Exception)
{
return -1;
}
}
#endregion
#region 执行查询语句或命令,返回数据阅读器DataReader
/// <summary>
/// 执行命令,返回数据阅读器DataReader
/// </summary>
/// <param name="ct">SqlCommand对象的命令类型</param>
/// <param name="cmdTxt">SqlCommand对象的操作语句</param>
/// <param name="cmdParms">存储SqlCommand对象参数的数组</param>
/// <returns>DataReader数据阅读器，执行失败或异常返回null</returns>
public SqlDataReader GetDataReader(CommandType ct, string cmdTxt, SqlParameter[] cmdParms)
{
try
{
SqlCommand cmd = new SqlCommand();
this.PrepareCommand(cmd, ct, cmdTxt, cmdParms);
//将CommandBehavior设置为CloseConnection，如果关闭SqlDataReader 对象，则与它关联的 Connection 对象也将关闭。
return cmd.ExecuteReader(CommandBehavior.CloseConnection);
}
catch (Exception)
{
return null;
}
}
/// <summary>
/// 执行SQL语句,返回数据阅读器DataReader
/// </summary>
/// <param name="sqlTxt">不带参数的SQL语句</param>
/// <returns>DataReader数据阅读器，执行失败或异常返回null</returns>
public SqlDataReader GetDataReader(string sqlTxt)
{
try
{
SqlCommand cmd = new SqlCommand(sqlTxt, this.GetConnection(true));
return cmd.ExecuteReader(CommandBehavior.CloseConnection);
}
catch (Exception)
{
return null;
}
}
#endregion
#region 执行查询命令，返回数据适配器SqlDataAdapter
/// <summary>
/// 执行查询命令，返回数据适配器SqlDataAdapter
/// </summary>
/// <param name="ct">SqlCommand对象的命令类型</param>
/// <param name="cmdTxt">SqlCommand对象的操作文本</param>
/// <param name="cmdParms">存储SqlCommand对象参数的数组</param>
/// <param name="option">决定SqlDataAdapter的执行命令类型<br/>1为选择，2为插入，3为删除，4为更新</param>
/// <returns>数据适配器SqlDataAdapter，当失败或异常时返回null</returns>
public SqlDataAdapter GetDataAdapter(CommandType ct, string cmdTxt, SqlParameter[] cmdParms, int option)
{
try
{
SqlDataAdapter sda = new SqlDataAdapter();
SqlCommand cmd = new SqlCommand();
this.PrepareCommand(cmd, ct, cmdTxt, cmdParms);
switch (option)
{
case 1:
sda.SelectCommand = cmd;
break;
case 2:
sda.InsertCommand = cmd;
break;
case 3:
sda.DeleteCommand = cmd;
break;
case 4:
sda.UpdateCommand = cmd;
break;
default:
break;
}
return sda;
}
catch (Exception)
{
return null;
}
}
#endregion
#region 执行查询命令或SQL语句，返回数据集DataSet
/// <summary>
/// 执行查询命令，返回数据集DataSet
/// </summary>
/// <param name="ct">SqlCommand对象的命令类型</param>
/// <param name="cmdTxt">SqlCommand对象的操作文本</param>
/// <param name="cmdParms">存储SqlCommand对象参数的数组</param>
/// <returns>成功查找返回数据集DataSet,异常返回null</returns>
public DataSet GetDataSet(CommandType ct, string cmdTxt, SqlParameter[] cmdParms)
{
try
{
SqlDataAdapter sda = this.GetDataAdapter(ct, cmdTxt, cmdParms, 1);
DataSet ds = new DataSet();
sda.Fill(ds);
return ds;
}
catch (Exception)
{
return null;
}
}
/// <summary>
/// 执行SQL语句，返回数据集DataSet
/// </summary>
/// <param name="SqlTxt">SQL查询语句</param>
/// <returns>成功查找返回数据集DataSet,异常返回null</returns>
public DataSet GetDataSet(string SqlTxt)
{
try
{
SqlDataAdapter sda = new SqlDataAdapter(SqlTxt, this.connStr);
DataSet ds = new DataSet();
sda.Fill(ds);
return ds;
}
catch (Exception)
{
return null;
}
}
#endregion
#region 释放连接对象Connection所占用的系统资源
/// <summary>
/// 释放连接对象Connection所占用的系统资源
/// </summary>
public void Dispose()
{
// 确保连接被关闭
try
{
if (this.connection != null)
{
this.connection.Dispose();
this.connection = null;
}
}
catch (Exception)
{
}
}
#endregion
}
}
浙公网安备 33010602011771号