using System;
using System.Data;
using System.Data.SqlClient;
using System.Collections.Generic;
using System.Text;
using System.Xml;

namespace DAL
{
  public abstract class StoredProcedure : MarshalByRefObject, IDisposable
  {
    private bool externalConnection = false;

    // Класс для выполнения вложенных процедур
    private class NestedProcedure : StoredProcedure
    {
      public NestedProcedure(StoredProcedure caller, string commandText, IDictionary<string, object> parameters)
        : base(caller)
      {
        Command.CommandText = commandText;
        Command.CommandType = CommandType.Text;
        foreach (string key in parameters.Keys)
        {
          Command.Parameters.AddWithValue(key, parameters[key]);
        }
      }
    }

    private static string connectionString = string.Empty;

    private SqlConnection connection = null;
    private SqlCommand command = null;

    protected SqlCommand Command
    {
      get
      {
        return command;
      }
    }

    private static string ConnectionString
    {
      get
      {
        return SqlConnectionManager.ConnectionString;
      }
    }

    private static SqlConnection NewConnection
    {
      get
      {
        return new SqlConnection(ConnectionString);
      }
    }

    protected SqlConnection Connection
    {
      get
      {
        if (connection == null)
          connection = StoredProcedure.NewConnection;
        return connection;
      }
    }

    #region IDisposable Members
    private bool disposed = false;

    public void Dispose()
    {
      Dispose(true);
      GC.SuppressFinalize(this);
    }

    protected void Dispose(bool disposing)
    {
      if (disposed) return;
      if (disposing)
      {
        //Если соединение було инициализировано не нами, то мы не должны его закрывать
        if (!externalConnection) this.connection.Dispose();
        this.connection = null;
      }
      disposed = true;
    }

    ~StoredProcedure()
    {
      Dispose(false);
    }

    #endregion

    protected StoredProcedure()
    {

      // Получаем ссылку на типа класса
      Type t = this.GetType();
      // Получаем атрибуты SqlCommand
      SqlCommandAttribute[] commandAtts = (SqlCommandAttribute[])t.GetCustomAttributes(typeof(SqlCommandAttribute), false);
      // Проверяем количество атрибутов (должно быть 1)
      if (commandAtts.Length > 1)
        throw new Exception(string.Format("%s: Слишком много атрибутов SqlCommandAttribute", t.Name));
      // Создаем SqlCommand с указанными параметрами для текущего соединения
      if (commandAtts.Length == 1)
      {
        // Если команда описана явно
        command = commandAtts[0].BuildCommand(Connection);
      }
      else
      {
        // Если описание отсутствует - берем имя текущего класса
        command = new SqlCommand(t.Name, Connection);
        command.CommandType = CommandType.StoredProcedure;
      }
      // Получаем список параметров вызова
      SqlParameterAttribute[] parameters = (SqlParameterAttribute[])t.GetCustomAttributes(typeof(SqlParameterAttribute), false);
      // Добавляем параметры к объекту SqlCommand
      foreach (SqlParameterAttribute parameter in parameters)
      {
        parameter.AddToSqlCommand(command);
      }
    }

    protected StoredProcedure(StoredProcedure caller) : this()
    {
      if (caller == null) throw new ArgumentNullException("caller");
      this.connection = caller.Connection;
      externalConnection = true;
    }

    private void Open()
    {
      Command.Connection = Connection;
      if (Connection.State != ConnectionState.Open)
      {
        Command.CommandTimeout = 0;
        Connection.Open();
      }
    }

    protected object ExecuteScalar()
    {
      Open();
      return Command.ExecuteScalar();
    }

    protected void ExecuteReader(DataSet dataSet, LoadOption loadOption, string[] tables)
    {
      Open();
      using (SqlDataReader reader = Command.ExecuteReader())
      {
        dataSet.Load(reader, loadOption, tables);
      }
    }

    protected XmlReader ExecuteXmlReader()
    {
      Open();
      return Command.ExecuteXmlReader();
    }

    protected void ExecuteNonQuery()
    {
      Open();
      // TODO: Здесь команду надо сначала запускать с параметрами проверки?
      Command.ExecuteNonQuery();
    }

    protected void ExecuteNonQuery(string commandText, IDictionary<string, object> parameters)
    {
      NestedProcedure proc = new NestedProcedure(this, commandText, parameters);
      proc.ExecuteNonQuery();
    }

  }
}

using System;
using System.Collections.Generic;
using System.Data;
using System.Text;

namespace BLL
{
  public sealed class ReturnWork : StoredProcedure
  {
    IDictionary<string, object> parameters = new Dictionary<string, object>();

    public static void Execute(DataTable table, StoredProcedure caller)
    {
      using (ReturnWork proc = new ReturnWork(caller))
      {
        proc.Execute(table);
      }
    }

    private ReturnWork(StoredProcedure caller) : base(caller) { }

    private void Execute(DataTable table)
    {
      string cmdText = BLL.ReturnWork_GenerateQuery.Execute(table, this);
      if (cmdText == string.Empty) return;

      WorkDelete(table.TableName);

      int handler = PrepareXml(table);

      parameters.Add("@hdoc", handler);

      ExecuteNonQuery(cmdText, parameters);

      RemoveXml(handler);

    }
  }
}