using System;
using System.Web;
using System.Data;
using System.Data.SqlClient;
namespace DataAccess
{
/// <summary>
/// DataAccess 的摘要说明。用于访问数据库
/// 数据层访问基类,定义数据层访问公共的变量,方法
/// </summary>
public class data
{
private SqlConnection conn;
private System.Data.SqlClient.SqlTransaction transaction;
/// <summary>
/// 构造函数
/// </summary>
public data()
{
if (this.conn == null)
this.conn = new SqlConnection(DbPath);
}
private const string DbPath ="**********";
public string constr//数据库连接字符串
{
get
{
return DbPath;
}
}
/// <summary>
/// 判断连接池是否打开
/// </summary>
public bool IsOpen
{
get
{
return this.conn.State == System.Data.ConnectionState.Open;
}
}
/// <summary>
/// 打开数据库
/// </summary>
public void Open()
{
if (this.conn.State != System.Data.ConnectionState.Open)
this.conn.Open();
}
/// <summary>
/// 关闭数据库
/// </summary>
public void Close()
{
if (this.conn.State == System.Data.ConnectionState.Open)
this.conn.Close();
}
/// <summary>
/// 判断记录是否存在
/// </summary>
/// <param name="SqlCommand"></param>
/// <returns></returns>
public bool datareader(string SqlCommand)
{
System.Data.DataTable table = this.QueryTable(SqlCommand);
return table.Rows.Count > 0;
}
/// <summary>
/// 添加更新记录
/// </summary>
/// <param name="SqlCommand"></param>
/// <returns></returns>
public int moddata(string SqlCommand)
{
int count = 0;
bool open = this.IsOpen;
if (!open)
this.Open();
SqlCommand cmd = new SqlCommand(SqlCommand, this.conn);
if(transaction != null)
cmd.Transaction = this.transaction;
count = cmd.ExecuteNonQuery();
if (!open)
this.Close();
return count;
}
/// <summary>
/// 执行数据查询,返回一个数据集。
/// </summary>
/// <param name="SqlCommand">查询的数据库命令。</param>
/// <returns>返回包含数据的数据集</returns>
public System.Data.DataTable QueryTable(string SqlCommand)
{
return QueryDataSet(SqlCommand).Tables[0];
}
/// <summary>
/// 执行数据查询,返回一个数据集。
/// </summary>
/// <param name="SqlCommand">查询的数据库命令。</param>
/// <returns>返回包含数据的数据集</returns>
public DataSet QueryDataSet(string SqlCommand) //起绑定数据集的作用
{
bool open = this.IsOpen;
if (!open)
this.Open();
SqlCommand cmd = new SqlCommand(SqlCommand, this.conn);
SqlDataAdapter adapter = new SqlDataAdapter(cmd);
DataSet dataSet = new DataSet();
adapter.Fill(dataSet, "mytable");
if (!open)
this.Close();
return dataSet;
}
/// <summary>
/// 查询,返回一个DataReader阅读器,对象。
/// </summary>
/// <param name="SqlCommand">查询处理的sq命令。</param>
/// <returns>返回一个SqlDataReader阅读器。</returns>
public SqlDataReader QueryReader(string SqlCommand)
{
bool open = this.IsOpen;
if (!open)
this.Open();
SqlCommand cmd = new SqlCommand(SqlCommand, this.conn);
SqlDataReader reader;
reader = cmd.ExecuteReader();
if (!open)
this.Close();
return reader;
}
/// <summary>
/// 数据查询,读取一行单值,即查询的结果的第一行的第1列值。
/// </summary>
/// <param name="SqlCommand">查询的SQL语句。</param>
/// <returns></returns>
public string QueryValue(string SqlCommand)
{
try
{
return this.QueryTable(SqlCommand).Rows[0][0].ToString();
}
catch
{
return "";
}
}
/// <summary>
/// 数据查询,读取一行单值,即查询的结果的第一行的第X列值。
/// </summary>
/// <param name="SqlCommand">查询的SQL语句。</param>
/// <param name="ColName">要读取列名称</param>
/// <returns></returns>
public string QueryValue(string SqlCommand, string ColName)
{
try
{
return this.QueryTable(SqlCommand).Rows[0][ColName].ToString();
}
catch
{
return "";
}
}
/// <summary>
/// 数据查询,读取一行单值,即查询的结果的第一行的第X列值。
/// </summary>
/// <param name="SqlCommand">查询的SQL语句。</param>
/// <param name="RowNumber">要读取列索引</param>
/// <returns></returns>
public string readout(string SqlCommand, int RowNumber)
{
try
{
return this.QueryTable(SqlCommand).Rows[0][RowNumber].ToString();
}
catch
{
return "";
}
}
/// <summary>
/// 启动一个数据库事务。
/// </summary>
/// <exception cref="InvalidOperationException">数据访问环境还没有打开或者已经释放。</exception>
/// <remarks>启动的事务必须使用 CommitTransaction 方法结束。</remarks>
public void BeginTransaction()
{
if (!IsOpen)
throw new System.InvalidOperationException("数据连接没有打开或者已经关闭。");
this.transaction = this.conn.BeginTransaction();
}
/// <summary>
/// 提交已经通过 BeginTransaction 方法开始的事务。
/// </summary>
/// <remarks>如果事务还没有启动,则不会产生任何效果,也不会抛出异常。</remarks>
public void CommitTransaction()
{
if (this.transaction == null)
return;
this.transaction.Commit();
this.transaction = null;
}
/// <summary>
/// 回滚已经通过 BeginTransaction 方法开始的事务。
/// </summary>
/// <remarks>如果事务还没有启动,则不会产生任何效果,也不会抛出异常。</remarks>
public void RollbackTransaction()
{
if (this.transaction == null)
return;
this.transaction.Rollback();
this.transaction = null;
}
}
}