|
|
Posted on 2006-04-20 16:39 Programmer 阅读(529) 评论(3) 编辑 收藏 网摘 所属分类: .Net
SQL implementation of DataProviderBase: SqlDataProviderBase
The SQLDataProviderBase class inherits from DataProviderBase class. It greatly simplifies program code required by a Microsoft SQL Server opeation.
- /// <summary>
- /// </summary>
- public abstract class SqlDataProviderBase : DataProviderBase
- {
- /// <summary>
- /// </summary>
- protected SqlDataProviderBase()
- {
- _allowTrans = false;
- }
-
- /// <summary>
- /// </summary>
- protected SqlDataProviderBase(SqlDataProviderBase instance)
- : base(instance)
- {
- _allowTrans = true;
- _underTrans = true;
-
- _connectionString = instance._connectionString;
- _sqlConnection = instance._sqlConnection;
- _sqlTransaction = instance._sqlTransaction;
- }
-
- /// <summary>
- /// </summary>
- /// <param name="allowPerformTransaction">
- /// </param>
- /// <param name="connectionString">
- /// </param>
- protected SqlDataProviderBase(bool allowPerformTransaction, string connectionString)
- {
- _allowTrans = allowPerformTransaction;
- _connectionString = connectionString;
- }
- /// <summary>
- /// </summary>
- public override void BeginTransaction()
- {
- if( !_allowTrans ) {
- throw new InvalidOperationException(
- "You are not allowed to begin a transaction.");
- }
-
- if( _underTrans ) {
- throw new InvalidOperationException(
- "A transaction has already been started.");
- }
-
- _sqlConnection = new SqlConnection(_connectionString);
- _sqlConnection.Open();
-
- _sqlTransaction = _sqlConnection.BeginTransaction();
- _underTrans = true;
- }
-
- /// <summary>
- /// </summary>
- public override void Commit()
- {
- if( !_underTrans ) {
- throw new InvalidOperationException("No transaction has already been started.");
- }
- _sqlTransaction.Commit();
- _sqlTransaction.Dispose();
- _sqlTransaction = null;
-
- Dispose();
- }
-
- /// <summary>
- /// </summary>
- public override void Rollback()
- {
- if( !_underTrans ) {
- throw new InvalidOperationException("No transaction has already been started.");
- }
- _sqlTransaction.Rollback();
- _sqlTransaction.Dispose();
- _sqlTransaction = null;
-
- Dispose();
- }
-
- /// <summary>
- /// </summary>
- public override bool UnderTransaction
- {
- get { return _underTrans; }
- }
-
- /// <summary>
- /// </summary>
- protected override void Dispose(bool disposing)
- {
- if( disposing ) {
- lock( this ) {
- if( _sqlConnection != null ) {
- _sqlConnection.Dispose();
- _sqlConnection = null;
- }
- }
- }
- }
-
- /// <summary>
- /// </summary>
- /// <param name="procName">
- /// </param>
- /// <param name="parameters">
- /// </param>
- protected object RunScalar(string procName, SqlParameter[] parameters)
- {
- SqlConnectionHolder connectionHolder;
- SqlCommand sqlCommand;
-
- using( connectionHolder = GetConnectionHolder() ) {
- using( sqlCommand = BuildSQLCommand(connectionHolder, procName, parameters) ) {
- return sqlCommand.ExecuteScalar();
- }
- }
- }
-
- /// <summary>
- /// </summary>
- /// <param name="procName">
- /// </param>
- /// <param name="parameters">
- /// </param>
- /// <param name="returnValue">
- /// </param>
- protected object RunScalar(string procName, SqlParameter[] parameters, out int returnValue)
- {
- SqlConnectionHolder connectionHolder;
- SqlCommand sqlCommand;
- SqlParameter param;
- object firstObject;
-
- using( connectionHolder = GetConnectionHolder() ) {
- using( sqlCommand = BuildSQLCommand(connectionHolder, procName, parameters, true) ) {
- firstObject = sqlCommand.ExecuteScalar();
-
-
- param = sqlCommand.Parameters[s_returnValueParamName];
- returnValue = (param.Value == null) ? -1 : (int)param.Value;
-
- return firstObject;
- }
- }
- }
-
- /// <summary>
- /// </summary>
- /// <param name="procName">
- /// </param>
- /// <param name="parameters">
- /// </param>
- protected SqlDataReader RunDataReader(string procName, SqlParameter[] parameters)
- {
- SqlConnectionHolder connectionHolder;
- SqlCommand sqlCommand;
-
- using( connectionHolder = GetConnectionHolder() ) {
- connectionHolder.AutoClose = false;
-
- using( sqlCommand = BuildSQLCommand(connectionHolder, procName, parameters) ) {
- if( _underTrans ) {
- return sqlCommand.ExecuteReader(CommandBehavior.Default);
- }
- else {
- return sqlCommand.ExecuteReader(CommandBehavior.CloseConnection);
- }
- }
- }
- }
-
- /// <summary>
- /// </summary>
- /// <param name="procName">
- /// </param>
- /// <param name="parameters">
- /// </param>
- /// <param name="returnValue">
- /// </param>
-
-
-
-
-
- /// <summary>
- /// </summary>
- /// <param name="procName">
- /// </param>
- /// <param name="parameters">
- /// </param>
- /// <param name="returnValue">
- /// </param>
- /// <returns>
- /// </returns>
- protected int RunSQLCommand(string procName, SqlParameter[] parameters, out int returnValue)
- {
- SqlConnectionHolder connectionHolder;
- SqlCommand sqlCommand;
- SqlParameter param;
- int rowsAffected;
-
- using( connectionHolder = GetConnectionHolder() ) {
- using( sqlCommand = BuildSQLCommand(connectionHolder, procName, parameters, true) ) {
- rowsAffected = sqlCommand.ExecuteNonQuery();
-
-
- param = sqlCommand.Parameters[s_returnValueParamName];
- returnValue = (param.Value != null) ? (int)param.Value : -1;
- }
- }
-
- return rowsAffected;
- }
-
- /// <summary>
- /// </summary>
- /// <param name="procName">
- /// </param>
- /// <param name="parameters">
- /// </param>
- /// <returns>
- /// </returns>
- protected int RunSQLCommand(string procName, SqlParameter[] parameters)
- {
- SqlConnectionHolder connectionHolder;
- SqlCommand sqlCommand;
-
- using( connectionHolder = GetConnectionHolder() ) {
- using( sqlCommand = BuildSQLCommand(connectionHolder, procName, parameters) ) {
- return sqlCommand.ExecuteNonQuery();
- }
- }
- }
-
- /// <summary>
- /// </summary>
- /// <param name="paramName">
- /// </param>
- /// <param name="dbType">
- /// </param>
- /// <param name="value">
- /// </param>
- protected SqlParameter CreateInputParam(string paramName, SqlDbType dbType, object value)
- {
- SqlParameter param = new SqlParameter(paramName, dbType);
- if( value == null ) {
- param.IsNullable = true;
- param.Value = DBNull.Value;
- }
- else {
- param.Value = value;
- }
-
- return param;
- }
-
- /// <summary>
- /// </summary>
- /// <param name="paramName">
- /// </param>
- /// <param name="dbType">
- /// </param>
- /// <param name="value">
- /// </param>
- /// <param name="paramSize">
- /// </param>
- protected SqlParameter CreateInputParam(string paramName, SqlDbType dbType, int paramSize, object value)
- {
- SqlParameter param = new SqlParameter(paramName, dbType, paramSize);
- if( value == null ) {
- param.IsNullable = true;
- param.Value = DBNull.Value;
- }
- else {
- param.Value = value;
- }
-
- return param;
- }
-
- /// <summary>
- /// </summary>
- /// <param name="paramName">
- /// </param>
- /// <param name="dbType">
- /// </param>
- protected SqlParameter CreateOutputParam(string paramName, SqlDbType dbType)
- {
- SqlParameter param = new SqlParameter(paramName, dbType);
- param.Direction = ParameterDirection.Output;
-
- return param;
- }
-
- /// <summary>
- /// </summary>
- /// <param name="paramName">
- /// </param>
- /// <param name="dbType">
- /// </param>
- /// <param name="paramSize">
- /// </param>
- protected SqlParameter CreateOutputParam(string paramName, SqlDbType dbType, int paramSize)
- {
- SqlParameter param = new SqlParameter(paramName, dbType, paramSize);
- param.Direction = ParameterDirection.Output;
-
- return param;
- }
-
- /// <summary>
- /// </summary>
- /// <param name="paramName">
- /// </param>
- /// <param name="dbType">
- /// </param>
- /// <param name="paramSize">
- /// </param>
- /// <param name="value">
- /// </param>
- protected SqlParameter CreateOutputParam(string paramName, SqlDbType dbType, int paramSize, object value)
- {
- SqlParameter param = new SqlParameter(paramName, dbType, paramSize);
- param.Direction = ParameterDirection.Output;
-
- if( value == null ) {
- param.IsNullable = true;
- param.Value = DBNull.Value;
- }
- else {
- param.Value = value;
- }
-
- return param;
- }
-
- private SqlConnectionHolder GetConnectionHolder()
- {
- if( _allowTrans ) {
- if( !_underTrans ) {
- throw new InvalidOperationException(
- "To perform a transaction, you should invoke 'BeginTransaction' method first.");
- }
-
- return new SqlConnectionHolder(_sqlConnection, _sqlTransaction);
- }
- else {
- return new SqlConnectionHolder(_connectionString);
- }
- }
-
- /// <summary>
- /// </summary>
- /// <param name="sqlConnection">
- /// </param>
- /// <param name="procName">
- /// </param>
- /// <param name="parameters">
- /// </param>
- private SqlCommand BuildSQLCommand(SqlConnectionHolder sqlConnection, string procName, SqlParameter[] parameters)
- {
- return BuildSQLCommand(sqlConnection, procName, parameters, false);
- }
-
- /// <summary>
- /// </summary>
- /// <param name="sqlConnection">
- /// </param>
- /// <param name="procName">
- /// </param>
- /// <param name="parameters">
- /// </param>
- private SqlCommand BuildSQLCommand(SqlConnectionHolder sqlConnection, string procName, SqlParameter[] parameters, bool getReturnValue)
- {
- SqlCommand sqlCommand;
-
-
- sqlCommand = new SqlCommand(procName, sqlConnection.Connection);
- sqlCommand.CommandType = CommandType.StoredProcedure;
- if( _underTrans ) {
- sqlCommand.Transaction = _sqlTransaction;
- }
-
-
- if( parameters != null && parameters.Length > 0 ) {
- foreach( SqlParameter param in parameters ) {
- sqlCommand.Parameters.Add(param);
- }
- }
-
- if( getReturnValue ) {
- SqlParameter param;
-
-
-
- param = new SqlParameter(s_returnValueParamName, SqlDbType.Int);
- param.Direction = ParameterDirection.ReturnValue;
-
- sqlCommand.Parameters.Add(param);
- }
-
- return sqlCommand;
- }
-
- /// <summary>
- /// </summary>
- public bool AllowTransaction
- {
- get { return _allowTrans; }
- }
-
- /// <summary>
- /// </summary>
- protected string ConnectionString
- {
- get { return _connectionString; }
- set { _connectionString = value; }
- }
-
- private SqlConnection _sqlConnection;
- private SqlTransaction _sqlTransaction;
- private bool _underTrans;
- private bool _allowTrans;
-
- private string _connectionString;
-
- private const string s_returnValueParamName = "@ReturnValue";
- }
- internal sealed class SqlConnectionHolder : IDisposable
- {
- internal SqlConnectionHolder(string connectionString)
- {
- try {
- _connection = new SqlConnection(connectionString);
- }
- catch( ArgumentException innerException ) {
- throw new ArgumentException("Invalid connection string.", innerException);
- }
-
-
- Open();
- }
-
- internal SqlConnectionHolder(SqlConnection sqlConnection, SqlTransaction sqlTransaction)
- {
- if( sqlConnection == null ) {
- throw new ArgumentNullException("sqlConnection");
- }
- if( sqlTransaction == null ) {
- throw new ArgumentNullException("sqlTransaction");
- }
- if( !object.ReferenceEquals(sqlTransaction.Connection, sqlConnection) ) {
- throw new ArgumentException(
- "The Connection of the 'sqlTransaction' must be equal to 'sqlConnection'",
- "sqlTransaction");
- }
-
- _connection = sqlConnection;
- _transaction = sqlTransaction;
- _isOpened = true;
- _underTrans = true;
- }
-
- /// <summary>
- /// </summary>
- public void Dispose()
- {
- Close();
- }
-
- /// <summary>
- /// </summary>
- public void Open()
- {
- if( !_isOpened ) {
- _connection.Open();
- _isOpened = true;
- }
- }
-
- /// <summary>
- /// </summary>
- public void Close()
- {
- if( _isOpened && _autoClose && !_underTrans ) {
- _connection.Dispose();
- _connection = null;
-
- _isOpened = false;
- }
- }
-
- /// <summary>
- /// </summary>
- public bool AutoClose
- {
- get { return _autoClose; }
- set { _autoClose = value; }
- }
-
- internal SqlConnection Connection
- {
- get { return _connection; }
- }
-
- internal SqlTransaction Transaction
- {
- get { return _transaction; }
- }
-
- private SqlConnection _connection;
- private SqlTransaction _transaction;
- private bool _isOpened;
- private bool _underTrans;
- private bool _autoClose = true;
-
- }
The UML diagram illustrates inheritance relationship.
Feedback
|