using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Data;
using System.Data.SqlClient;
/*
* 数据库常用操作
* author qjf
* */
namespace DbHelper.SQLServer
{
public class MsSqlServer
{
/// <summary>
/// 连接字符串
/// </summary>
private string connectionString = null;
/// <summary>
/// 连接字符串
/// </summary>
public string ConnectionString
{
get
{
return connectionString;
}
set
{
connectionString = value;
}
}
/// <summary>
/// 数据库连接
/// </summary>
private SqlConnection sqlCon = null;
/// <summary>
/// 获取当前的数据库连接
/// </summary>
public SqlConnection SqlCon
{
get
{
return sqlCon;
}
}
private SqlCommand sqlCmd = null;
/// <summary>
/// 获取当前执行的Transact-SQL或者存储过程
/// </summary>
public SqlCommand SqlCmd
{
get
{
return SqlCmd;
}
}
private bool isClose = true;
/// <summary>
/// 获取当前数据库是否关闭
/// </summary>
public bool IsClose
{
get
{
return isClose;
}
}
private bool isOpen = false;
/// <summary>
/// 获取当前数据库是否打开
/// </summary>
public bool IsOpen
{
get
{
return isOpen;
}
}
/// <summary>
/// 是否开始事务
/// </summary>
private bool isBeginTransaction = false;
/// <summary>
/// 获取当前事务是否开始
/// </summary>
public bool IsBeginTransaction
{
get
{
return isBeginTransaction;
}
}
private SqlTransaction transaction;
public SqlTransaction Transaction
{
get
{
return transaction;
}
}
private SqlDataAdapter sqlAdapter = null;
/// <summary>
/// 获取当前用于填充数据集的适配器
/// </summary>
public SqlDataAdapter SqlAdapter
{
get
{
return sqlAdapter;
}
}
/// <summary>
/// 构造函数
/// </summary>
public MsSqlServer()
{
}
public MsSqlServer(string connectionString)
{
this.connectionString = connectionString;
}
/ <summary>
/ 析构函数
/ </summary>
//~MsSqlServer()
//{
// if (sqlCon.State == ConnectionState.Open)
// Close();
// this.Dispose();
//}
/// <summary>
/// 打开链接
/// </summary>
public bool Open()
{
try
{
sqlCon = new SqlConnection(connectionString);
sqlCon.Open();
return isOpen = true;
}
catch (SqlException ex)
{
throw ex;
}
}
/// <summary>
/// 关闭当前链接
/// </summary>
public bool Close()
{
try
{
sqlCon.Close();
isOpen = false;
return isClose = true;
}
catch (SqlException ex)
{
throw ex;
}
}
/// <summary>
/// 释放当前资源
/// </summary>
public void Dispose()
{
if (sqlCmd != null)
{
sqlCmd.Dispose();
}
if (sqlAdapter != null)
{
sqlAdapter.Dispose();
}
if (transaction != null)
{
transaction.Dispose();
}
if (sqlCon != null)
{
sqlCon.Dispose();
}
}
/// <summary>
/// 设置用于填充DataSet的适配器
/// </summary>
/// <param name="cmd">用于填充的查询</param>
/// <returns></returns>
private SqlDataAdapter GetAdapter(SqlCommand cmd)
{
SqlDataAdapter adapter = new SqlDataAdapter();
adapter.SelectCommand = cmd;
return adapter;
}
/// <summary>
/// 设置查询语句
/// </summary>
/// <param name="query">查询语句</param>
/// <returns></returns>
public SqlCommand GetCmd(string query)
{
SqlCommand cmd = new SqlCommand();
cmd.CommandType = CommandType.Text;
cmd.CommandText = query;
cmd.Connection = sqlCon;
return cmd;
}
/// <summary>
/// 设置事务
/// </summary>
/// <param name="transaction"></param>
/// <returns></returns>
public SqlCommand GetCmd(SqlTransaction transaction)
{
SqlCommand cmd = new SqlCommand();
cmd.Transaction = transaction;
cmd.Connection = sqlCon;
return cmd;
}
/// <summary>
/// 设置存储过程
/// </summary>
/// <param name="storeName">存储过程名</param>
/// <param name="param">存储过程参数</param>
/// <returns></returns>
public SqlCommand GetCmd(string storeName,SqlParameter[] param)
{
SqlCommand cmd = new SqlCommand();
cmd.CommandType = CommandType.StoredProcedure;
cmd.CommandText = storeName;
if (param != null)
{
foreach (SqlParameter sp in param)
{
cmd.Parameters.Add(sp);
}
}
cmd.Connection = sqlCon;
return cmd;
}
/// <summary>
/// 获取数据集
/// </summary>
/// <param name="query">Transact-SQL语句</param>
/// <param name="tableName">表名</param>
/// <returns></returns>
public DataSet GetDataSet(string query,string tableName)
{
DataSet dataSet = new DataSet();
try
{
if (query != null && query.Trim().Length>0)
{
if(isClose)
Open();
sqlCmd = GetCmd(query);
sqlAdapter = GetAdapter(sqlCmd);
sqlAdapter.Fill(dataSet,tableName);
}
return dataSet;
}
catch (SqlException ex)
{
throw ex;
}
finally
{
if(isOpen)
Close();
}
}
/// <summary>
/// 获取数据集
/// </summary>
/// <param name="query">Transact-SQL语句</param>
/// <returns></returns>
public DataSet GetDataSet(string query)
{
DataSet dataSet = new DataSet();
try
{
if (query != null && query.Trim().Length>0)
{
if (isClose)
Open();
sqlCmd = GetCmd(query);
sqlAdapter = GetAdapter(sqlCmd);
sqlAdapter.Fill(dataSet);
}
return dataSet;
}
catch (SqlException ex)
{
throw ex;
}
finally
{
if(isOpen)
Close();
}
}
public DataSet GetDataSet(string storeName, SqlParameter[] param)
{
DataSet dataset = new DataSet();
try
{
if (storeName != null && storeName.Trim().Length>0)
{
if (isClose)
Open();
sqlCmd = GetCmd(storeName, param);
sqlAdapter = GetAdapter(sqlCmd);
sqlAdapter.Fill(dataset);
}
return dataset;
}
catch (SqlException ex)
{
throw ex;
}
finally
{
if (isOpen)
Close();
}
}
/// <summary>
/// 获取查询表数据
/// </summary>
/// <param name="query">Transact-SQL语句</param>
/// <returns></returns>
public DataTable GetDataTable(string query)
{
DataTable dt = GetDataSet(query).Tables[0];
return dt;
}
/// <summary>
/// 获取查询表数据
/// </summary>
/// <param name="query">Transact-SQL语句</param>
/// <param name="tableName">表名</param>
/// <returns></returns>
public DataTable GetDataTable(string query, string tableName)
{
DataTable dt = GetDataSet(query, tableName).Tables[0];
return dt;
}
/// <summary>
/// 获取查询表数据
/// </summary>
/// <param name="storeName">存储过程名</param>
/// <param name="param">存储过程参数</param>
/// <returns></returns>
public DataTable GetDataTable(string storeName, SqlParameter[] param)
{
DataTable dt = GetDataSet(storeName, param).Tables[0];
return dt;
}
/// <summary>
/// 执行SQL语句并返回受影响的记录数
/// </summary>
/// <param name="query">Transact-SQL语句</param>
/// <returns></returns>
public int ExectueNonQuery(string query)
{
int i = 0;
if (query != null && query.Trim().Length>0)
{
try
{
if (isClose)
Open();
sqlCmd = GetCmd(query);
i = sqlCmd.ExecuteNonQuery();
}
catch (SqlException ex)
{
throw ex;
}
finally
{
if (isOpen)
{
Close();
}
}
}
return i;
}
/// <summary>
/// 获取数据
/// </summary>
/// <param name="query">Transact-SQL语句</param>
/// <returns></returns>
public SqlDataReader GetDataReader(string query)
{
if (query != null && query.Trim().Length>0)
{
try
{
if (isClose)
Open();
sqlCmd = GetCmd(query);
SqlDataReader dr = sqlCmd.ExecuteReader();
return dr;
}
catch (SqlException ex)
{
throw ex;
}
finally
{
if (isOpen)
Close();
}
}
else
return null;
}
/// <summary>
/// 获取数据
/// </summary>
/// <param name="storeName">存储过程名</param>
/// <param name="param">存储过程参数</param>
/// <returns></returns>
public SqlDataReader GetDataReader(string storeName, SqlParameter[] param)
{
if (storeName != null && storeName.Trim().Length>0)
{
try
{
if (isClose)
Open();
sqlCmd = GetCmd(storeName, param);
SqlDataReader dr = sqlCmd.ExecuteReader();
return dr;
}
catch (SqlException ex)
{
throw ex;
}
finally
{
if (isOpen)
Close();
}
}
else
return null;
}
/// <summary>
/// 开始事务
/// </summary>
public SqlTransaction BeginTransaction()
{
try
{
if (isClose)
Open();
transaction = sqlCon.BeginTransaction();
sqlCmd = GetCmd(transaction);
return transaction;
}
catch (SqlException ex)
{
throw ex;
}
finally
{
if (isOpen)
Close();
}
}
/// <summary>
/// 开始事务
/// </summary>
/// <param name="transactionName">事务名称</param>
public SqlTransaction BeginTransaction(string transactionName)
{
try
{
if (isClose)
Open();
transaction = sqlCon.BeginTransaction(transactionName);
sqlCmd = GetCmd(transaction);
isBeginTransaction = true;
return transaction;
}
catch (SqlException ex)
{
throw ex;
}
}
/// <summary>
/// 提交事务
/// </summary>
public void CommitTransaction()
{
try
{
if (isBeginTransaction)
{
transaction.Commit();
}
}
catch (SqlException ex)
{
throw ex;
}
finally
{
if (isOpen)
Close();
}
}
/// <summary>
/// 提交事务
/// </summary>
/// <param name="transaction">事务对象</param>
public void CommitTransaction(SqlTransaction transaction)
{
try
{
if (isBeginTransaction)
{
transaction.Commit();
}
}
catch (SqlException ex)
{
throw ex;
}
finally
{
if (isOpen)
Close();
}
}
}
}
3883
被折叠的 条评论
为什么被折叠?
到【灌水乐园】发言
