using System;
using System.ComponentModel;
using System.Collections;
using System.Diagnostics;
using System.Data;
using System.Data.SqlClient;
using System.Configuration;
namespace myApps
{
  public class dbTools : IDisposable
  {
    private SqlConnection con;
    //Per l'esecuzione di stored procedures
    public int RunProcedure(string procName)
    {
      SqlCommand cmd = CreateCommand(procName, null);
      cmd.ExecuteNonQuery();
      this.CloseConnection();
      return (int)cmd.Parameters["ReturnValue"].Value;
    }
    //Per l'esecuzione di stored procedures con parametri
    public int RunProcedure(string procName, SqlParameter[] prams)
    {
      SqlCommand cmd = CreateCommand(procName, prams);
      cmd.ExecuteNonQuery();
      this.CloseConnection();
      return (int)cmd.Parameters["ReturnValue"].Value;
    }
    //Per l'esecuzione di stored procedures (restituisce un datareader)
    public void RunProcedure(string procName, out SqlDataReader dataReader)
    {
      SqlCommand cmd = CreateCommand(procName, null);
      dataReader = cmd.ExecuteReader(System.Data.CommandBehavior.CloseConnection);
    }
    //Per l'esecuzione di stored procedures con parametri(restituisce un datareader)
    public void RunProcedure(string procName, SqlParameter[] prams, out SqlDataReader dataReader)
    {
      SqlCommand cmd = CreateCommand(procName, prams);
      dataReader = cmd.ExecuteReader(System.Data.CommandBehavior.CloseConnection);
    }
    //Crea un oggetto command
    private SqlCommand CreateCommand(string procName, SqlParameter[] prams)
    {
      OpenConnection();
      SqlCommand cmd = new SqlCommand(procName, con);
      cmd.CommandType = CommandType.StoredProcedure;
      if (prams != null)
      {
        foreach (SqlParameter parameter in prams)
          cmd.Parameters.Add(parameter);
      }
      cmd.Parameters.Add(
      new SqlParameter("ReturnValue", SqlDbType.Int, 4,
      ParameterDirection.ReturnValue, false, 0, 0,
      string.Empty, DataRowVersion.Default, null));
      return cmd;
    }
    //apre la connessione
    private void OpenConnection()
    {
      if (con == null)
      {
        con = new SqlConnection(ConfigurationSettings.AppSettings["ConnectionString"]);
        con.Open();
      }
    }
    //chiude la connessione
    public void CloseConnection()
    {
      if (con != null)
        con.Close();
    }
    public void Dispose()
    {
      if (con != null)
      {
        con.Dispose();
        con = null;
      }
    }
    public SqlParameter MakeInParam(string ParamName, SqlDbType DbType, int Size, object Value)
    {
      return MakeParam(ParamName, DbType, Size, ParameterDirection.Input, Value);
    }
    public SqlParameter MakeOutParam(string ParamName, SqlDbType DbType, int Size)
    {
      return MakeParam(ParamName, DbType, Size, ParameterDirection.Output, null);
    }
    public SqlParameter MakeParam(string ParamName, SqlDbType DbType, Int32 Size,
      ParameterDirection Direction, object Value)
    {
      SqlParameter param;
      if(Size > 0)
        param = new SqlParameter(ParamName, DbType, Size);
      else
        param = new SqlParameter(ParamName, DbType);
      param.Direction = Direction;
      if (!(Direction == ParameterDirection.Output ++ Value == null))
        param.Value = Value;
      return param;
    }
    public DataTable GetDataTable(string Sql,string ConnectString)
    {
      SqlDataAdapter da = new SqlDataAdapter(Sql,ConnectString);
      DataTable dt = new DataTable();
      int rows = da.Fill(dt);
      return dt;
    }
  }
}