数据库连接示例
![](https://www.cnblogs.com/Images/OutliningIndicators/ContractedBlock.gif)
public class PersistenceService
{
#region Singleton
private static readonly PersistenceService _instance = new PersistenceService();
public static PersistenceService Instance
{
get { return _instance; }
}
#endregion
private ConnectionStringSettings _connectionStringSettings = null;
private SqlConnection _connection = null;
private PersistenceService()
{
this._connectionStringSettings = ConfigurationManager.ConnectionStrings["PersistenceConnectionString"];
if (null == this._connectionStringSettings)
{
this._connectionStringSettings = WebConfigurationManager.ConnectionStrings["PersistenceConnectionString"];
}
if (null == this._connectionStringSettings)
{
throw new SystemException("Can not locate the application configuration file.");
}
if (this.Connection == null)
{
throw new SystemException("Can not create database connection.");
}
}
public SqlConnection Connection
{
get
{
if (this._connection == null)
{
try
{
this._connection = new SqlConnection(this._connectionStringSettings.ConnectionString);
}
catch
{
this._connection = null;
}
}
return this._connection;
}
}
private OleDbConnection _oleDbConn;
private string _oleDbConnStr;
public string OleDbConnStr
{
get { return _oleDbConnStr; }
set { _oleDbConnStr = value; }
}
public OleDbConnection OleDbConn
{
get
{
if (this._oleDbConn == null)
{
try
{
this._oleDbConn = new OleDbConnection(this._oleDbConnStr);
}
catch
{
this._oleDbConn = null;
}
}
return this._oleDbConn;
}
}
public void OpenOleDbConnection()
{
if (this.OleDbConn == null)
{
throw new SystemException("不能创建数据库连接.");
}
CloseOleDbConnection();
try
{
this.OleDbConn.Open();
}
catch (Exception ex)
{
this._oleDbConn = null;
throw new Exception(ex.Message);
}
}
public void CloseOleDbConnection()
{
if (this.OleDbConn != null)
{
if (this.OleDbConn.State != ConnectionState.Closed)
{
this.OleDbConn.Close();
}
}
}
public void OpenConnection()
{
if (this.Connection == null)
{
throw new SystemException("不能创建数据库连接.");
}
CloseConnection();
try
{
this.Connection.Open();
}
catch (Exception ex)
{
this._connection = null;
throw new SystemException(ex.Message);
}
}
public void CloseConnection()
{
if (this.Connection != null)
{
if (this.Connection.State != ConnectionState.Closed)
{
this.Connection.Close();
}
}
}
public bool ExecuteOleDbSql(string sqlQuery)
{
using (OleDbCommand command = this.OleDbConn.CreateCommand())
{
OpenOleDbConnection();
OleDbTransaction tran = this.OleDbConn.BeginTransaction();
command.Connection = this.OleDbConn;
command.CommandType = CommandType.Text;
command.CommandText = sqlQuery;
command.Transaction = tran;
try
{
command.ExecuteNonQuery();
tran.Commit();
return true;
}
catch (Exception e)
{
try
{
tran.Rollback();
}
catch (Exception ex)
{
throw new Exception(ex.Message);
}
throw new Exception(e.Message);
}
finally
{
this.CloseOleDbConnection();
}
}
}
public bool ExecuteSql(string sqlQuery)
{
using (SqlCommand command = this.Connection.CreateCommand())
{
OpenConnection();
SqlTransaction tran = this.Connection.BeginTransaction();
command.Connection = this.Connection;
command.CommandType = CommandType.Text;
command.CommandText = sqlQuery;
command.Transaction = tran;
try
{
command.ExecuteNonQuery();
tran.Commit();
return true;
}
catch (Exception e)
{
try
{
tran.Rollback();
}
catch (Exception ex)
{
throw new Exception(ex.Message);
}
throw new Exception(e.Message);
}
finally
{
this.CloseConnection();
}
}
}
public DataTable ExecuteOleDbQuery(string query)
{
DataTable dtItem = null;
using (OleDbCommand command = OleDbConn.CreateCommand())
{
command.Connection = this.OleDbConn;
command.CommandType = CommandType.Text;
command.CommandText = query;
OleDbDataAdapter adapter = new OleDbDataAdapter();
adapter.SelectCommand = command;
dtItem = new DataTable();
try
{
this.OpenOleDbConnection();
adapter.Fill(dtItem);
}
catch (Exception ex)
{
dtItem = null;
throw new Exception(ex.Message);
}
finally
{
this.CloseOleDbConnection();
}
}
return dtItem;
}
public DataTable ExecuteQuery(string query)
{
DataTable dtItem = null;
using (SqlCommand command = this.Connection.CreateCommand())
{
command.Connection = this.Connection;
command.CommandType = CommandType.Text;
command.CommandText = query;
SqlDataAdapter adapter = new SqlDataAdapter();
adapter.SelectCommand = command;
dtItem = new DataTable();
try
{
this.OpenConnection();
adapter.Fill(dtItem);
}
catch(Exception ex)
{
dtItem = null;
throw new Exception(ex.Message);
}
finally
{
this.CloseConnection();
}
}
return dtItem;
}
}