本文介绍了一个使用OleDb进行数据库连接及操作的C#类，包括执行SQL语句、获取数据表和数据集等功能，并提供了异常处理和资源释放的方法。
using System;
using System.Collections.Generic;
using System.Text;
using System.Data;
using System.Data.OleDb;
namespace DatabaseConn
{
public class Connection
{
private string connection_string;
/// <summary>
/// 数据库链接字符串
/// </summary>
public string ConnectionString
{
get
{
return connection_string;
}
set
{
connection_string = value;
}
}
private OleDbConnection connection;
/// <summary>
/// 数据库链接
/// </summary>
public OleDbConnection Conn
{
get
{
return connection;
}
set
{
connection = value;
}
}
private OleDbCommand command;
/// <summary>
/// 数据库命令执行者
/// </summary>
public OleDbCommand Command
{
get
{
return command;
}
set
{
command = value;
}
}
private OleDbDataAdapter dateadapter;
private OleDbDataReader datereader;
/// <summary>
/// 数据库读取器
/// </summary>
public OleDbDataReader DateReader
{
get
{
return datereader;
}
set
{
datereader = value;
}
}
private string sql;
/// <summary>
/// 执行SQL
/// </summary>
public string SQL
{
get
{
return sql;
}
set
{
sql = value;
}
}
private string error_info;
/// <summary>
/// 错误信息
/// </summary>
public string ErrorInfo
{
get
{
return error_info;
}
set
{
error_info = value;
}
}
/// <summary>
/// 初始化链接
/// </summary>
public Connection()
{
connection = new OleDbConnection();
command = new OleDbCommand();
command.Connection = connection;
error_info = "";
sql = "";
connection_string = "";
}
/// <summary>
/// 初始化链接
/// </summary>
/// <param name="ConnectionString">数据库链接字符串</param>
public Connection(string ConnectionString)
{
connection = new OleDbConnection(ConnectionString);
command = new OleDbCommand();
command.Connection = connection;
error_info = "";
sql = "";
connection_string = ConnectionString;
}
/// <summary>
/// 取得DataTable
/// </summary>
/// <returns>返回DataTable</returns>
public DataTable GetDataTable()
{
DataTable temp = new DataTable();
try
{
if (connection.State == ConnectionState.Closed)
{
connection.Open();
}
command.CommandType = CommandType.Text;
command.CommandText = sql;
dateadapter = new OleDbDataAdapter();
dateadapter.SelectCommand = command;
dateadapter.Fill(temp);
//清理OleDbCommand.Parameters变量。
if (command.Parameters.Count > 0)
{
command.Parameters.Clear();
}
}
catch (Exception e)
{
Dispose();
error_info = e.Message;
throw e;
}
return temp;
}
/// <summary>
/// 取得DataSet
/// </summary>
/// <returns>返回DataSet</returns>
public DataSet GetDataSet()
{
DataSet temp = new DataSet();
try
{
if (connection.State == ConnectionState.Closed)
{
connection.Open();
}
command.CommandType = CommandType.Text;
command.CommandText = sql;
dateadapter = new OleDbDataAdapter();
dateadapter.SelectCommand = command;
dateadapter.Fill(temp);
//清理OleDbCommand.Parameters变量。
if (command.Parameters.Count > 0)
{
command.Parameters.Clear();
}
}
catch (Exception e)
{
Dispose();
error_info = e.Message;
throw e;
}
return temp;
}
/// <summary>
/// 执行SQL命令
/// </summary>
/// <returns>返回影响行数</returns>
public int ExecuteSQL()
{
int temp = 0;
try
{
if (connection.State == ConnectionState.Closed)
{
connection.Open();
}
command.CommandType = CommandType.Text;
command.CommandText = sql;
command.Connection = connection;
temp = command.ExecuteNonQuery();
//清理OleDbCommand.Parameters变量。
if (command.Parameters.Count > 0)
{
command.Parameters.Clear();
}
}
catch (Exception e)
{
Dispose();
error_info = e.Message;
throw e;
}
return temp;
}
/// <summary>
/// 取得第一列第一行
/// </summary>
/// <returns>返回第一列第一行</returns>
public object ExecuteScalar()
{
object temp = null;
try
{
if (connection.State == ConnectionState.Closed)
{
connection.Open();
}
command.CommandType = CommandType.Text;
command.CommandText = sql;
command.Connection = connection;
temp = command.ExecuteScalar();
//清理OleDbCommand.Parameters变量。
if (command.Parameters.Count > 0)
{
command.Parameters.Clear();
}
}
catch (Exception e)
{
Dispose();
error_info = e.Message;
throw e;
}
return temp;
}
/// <summary>
/// 取得读取器
/// </summary>
/// <returns>返回读取器</returns>
public object GetDataReader()
{
object temp = null;
try
{
if (connection.State == ConnectionState.Closed)
{
connection.Open();
}
command.CommandType = CommandType.Text;
command.CommandText = sql;
command.Connection = connection;
temp = command.ExecuteReader();
//清理OleDbCommand.Parameters变量。
if (command.Parameters.Count > 0)
{
command.Parameters.Clear();
}
}
catch (Exception e)
{
Dispose();
error_info = e.Message;
throw e;
}
return temp;
}
/// <summary>
/// 关闭释放所有资源链接
/// </summary>
public void Dispose()
{
if (dateadapter != null)
{
dateadapter.Dispose();
}
if (datereader != null)
{
if (datereader.IsClosed == true)
{
datereader.Close();
}
datereader.Dispose();
}
if (connection != null)
{
if (connection.State != ConnectionState.Closed)
{
connection.Close();
}
connection.Dispose();
}
if (command != null)
{
command.Dispose();
}
}
}
}
扫一扫
评论
被折叠的 条评论
为什么被折叠?
到【灌水乐园】发言
查看更多评论
添加红包
