自己写一个类，封装后，用来C#数据库连接模块

来源：互联网发布：pixhawk地面站软件编辑：程序博客网时间：2024/05/16 13:46

using System;
using System.Text;
using System.Data.SqlClient;
using System.Data;

namespace StudentLib.Common
{
/// <summary>
/// 提示对数据操作的基类
/// </summary>
public class DataBaseOperate : IDisposable
{
/// <summary>
/// 数据库连接标志
/// </summary>
private bool _db_Connectioned;

/// <summary>
/// 开启事务标志
/// </summary>
private bool _db_Transactioned;

/// <summary>
/// 数据库连接字符串
/// </summary>
private string _db_ConnectionString;

/// <summary>
/// 数据库连接对象
/// </summary>
private SqlConnection _db_Connection;

/// <summary>
/// 数据操作对象
/// </summary>
private SqlCommand _db_Command;

/// <summary>
/// 事务操作对象
/// </summary>
private SqlTransaction _db_Transaction;

/// <summary>
/// 构造一个具有默认连接字符串的对象
/// </summary>
public DataBaseOperate():this( "server=localhost;database=stuDB;uid=sa;pwd=sa ")
{
}

/// <summary>
/// 构造一个具有指定连接字符串的对象
/// </summary>
/// <param name= "connectionString "> 连接字符串 </param>
public DataBaseOperate(string connectionString)
{
_db_ConnectionString = connectionString;
_db_Connectioned = false;
_db_Transactioned = false;
}

/// <summary>
/// 打开数据连接
/// </summary>
/// <returns> 成功返回true </returns>
public bool Open()
{
if (_db_Connectioned)
return true;
else
{
try
{
if (_db_Connection == null)
{
_db_Connection = new SqlConnection(_db_ConnectionString);
_db_Connection.Open();
}
if (_db_Command == null)
{
_db_Command = new SqlCommand();
_db_Command.Connection = _db_Connection;
}
_db_Connectioned = true;
}
catch (SqlException ex)
{
throw ex;
}
}
return true;
}

/// <summary>
/// 关闭数据库连接
/// </summary>
/// <returns> 成功返回true </returns>
public bool Close()
{
Dispose();
return true;
}
/// <summary>
/// 除去对象资源
/// </summary>
public void Dispose()
{
Dispose(true);
GC.SuppressFinalize(true);
}

/// <summary>
/// 释放对象实例变量
/// </summary>
/// <param name= "bDispose "> </param>
protected virtual void Dispose(bool bDispose)
{
if (!bDispose)
return;
if (_db_Connectioned)
{
if (_db_Connection.State != System.Data.ConnectionState.Closed)
{
_db_Connection.Close();
_db_Connection.Dispose();
_db_Transaction = null;
_db_Command = null;
_db_Connection = null;
_db_Connectioned = false;
_db_Transactioned = false;
}
}
}

/// <summary>
/// 执行对数据库的增，删，改操作
/// </summary>
/// <param name= "sql "> 要执行的Sql语句 </param>
/// <returns> 执行成功返回影响的行数 </returns>
public int ExecuteNonQuery(string sqlString)
{
int count = 0;
if (!Open())
throw new ApplicationException( "没有建立与数据的连接 ");
_db_Command.CommandType = System.Data.CommandType.Text;
_db_Command.CommandText = sqlString;
try
{
count=_db_Command.ExecuteNonQuery();
}
catch (SqlException ex)
{
if (_db_Transactioned) //如果是事件，则回滚
_db_Transaction.Rollback();
throw ex;
}
return count;
}

/// <summary>
/// 执行对数据的查询操作
/// </summary>
/// <param name= "sQuery "> 要执行的查询语句 </param>
/// <param name= "table "> 返回的数据集的表名 </param>
/// <returns> 数据集 </returns>
public DataSet ExecuteDataSet(string sQuery,string table)
{
if(!Open())
throw new ApplicationException( "没有建立与数据的连接 ");
DataSet dbDataSet = new DataSet();
try
{
SqlDataAdapter dbAdapter = new SqlDataAdapter(sQuery, _db_Connection);
dbAdapter.Fill(dbDataSet, table);
}
catch (SqlException ex)
{
if (_db_Transactioned)
_db_Transaction.Rollback();
throw ex;
}
return dbDataSet;
}

public object ExecuteScalar(string sQuery)
{
if (!Open())
throw new ApplicationException( "没有建立与数据的连接 ");
object returnObj = null;
try
{
_db_Command = new SqlCommand(sQuery, _db_Connection);
returnObj = _db_Command.ExecuteScalar();

if (returnObj == null)
returnObj = 1;
}
catch (SqlException ex)
{
if (_db_Transactioned)
_db_Transaction.Rollback();
throw ex;
}
return returnObj;
}

/// <summary>
/// 开始事务
/// </summary>
public void begionTransaction()
{
if (!_db_Connectioned)
throw new ApplicationException( "没有建立与数据的连接 ");
_db_Transactioned = true;
_db_Transaction = _db_Connection.BeginTransaction(IsolationLevel.ReadCommitted);
_db_Command.Transaction = _db_Transaction;

}

/// <summary>
/// 提交事务
/// </summary>
/// <returns> 成功则返回true </returns>
public bool Commit()
{
//如果是没有开始事务，则直接返回true
if (!_db_Transactioned)
return true;
try
{
_db_Transaction.Commit();
}
catch (SqlException ex)
{
_db_Transaction.Rollback();
throw ex;
}
return true;
}
}
}