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


namespace de.ITVisions.DALUtil
{
 /// <summary>
 /// Dies ist ein Beispiel für eine Implementierung einer Hilfsbibliothek zur Nutzung 
 /// von ADO.NET, aus der Sicht des Nutzers viele Funktionen von ADO.NET auf eine 
 /// Codezeile reduziert.<br/>
 /// Alternative 2: Provierneutrale Implementierung mit Providerfabriken
 /// <remarks>Die einzelnen Methoden haben sprechende Namen und sind daher nicht mit Kommentaren versehen!</remarks>
 /// </summary>
 public class DBUtil
 {
  public enum Providers
  {
   MSSQL,
   ACCESS
  }

  static private string _CurrentConnString;
  static public string CurrentConnString
  {
   get
   {
    if (_CurrentConnString == null) _CurrentConnString = System.Configuration.ConfigurationManager.ConnectionStrings["StandardVerbindung"].ConnectionString;
    return _CurrentConnString;
   }
   set { _CurrentConnString = value; }
  }

  static private string _CurrentProvider;
  static public string CurrentProvider
  {
   get
   {
    if (_CurrentProvider == null) _CurrentProvider = System.Configuration.ConfigurationManager.ConnectionStrings["StandardVerbindung"].ProviderName;
    return _CurrentProvider;
   }
   set { _CurrentProvider = value; }
  }


  public static void SetDatabase(Providers Provider, string ConnString)
  {
   switch (Provider)
   {
    case Providers.ACCESS:
     CurrentProvider = "System.Data.OleDb";
     CurrentConnString = ConnString;
     break;
    case Providers.MSSQL:
     CurrentProvider = "System.Data.SqlClient";
     CurrentConnString = ConnString;
     break;
   }
  }

  public static DbConnection GetConnection()
  {
   return GetConnection(CurrentConnString);
  }

   public static DbConnection GetConfiguredConnection()
  {
   // --- Fabrik  erzeugen
   DbProviderFactory provider = DbProviderFactories.GetFactory(System.Configuration.ConfigurationManager.ConnectionStrings["WWWings"].ProviderName);
   // --- Verbindung aufbauen
   DbConnection conn = provider.CreateConnection();
   conn.ConnectionString = System.Configuration.ConfigurationManager.ConnectionStrings["WWWings"].ConnectionString;
   conn.Open();
   return conn;
  }

  /// <summary>
  /// Verbindung holen und öffnen
  /// </summary>
  /// <param name="CONNECTIONSTRING"></param>
  /// <returns></returns>
  public static DbConnection GetConnection(string CONNECTIONSTRING)
  {
   // --- Fabrik  erzeugen
   DbProviderFactory provider = DbProviderFactories.GetFactory(CurrentProvider);
   // --- Verbindung aufbauen
   DbConnection conn = provider.CreateConnection();
   conn.ConnectionString = CONNECTIONSTRING;
   conn.Open();
   return conn;
  }

  /// <summary>
  /// Befehlsobjekt erzeugen
  /// </summary>
  /// <param name="SQL"></param>
  /// <param name="conn"></param>
  /// <returns></returns>
  public static DbCommand GetCommand(string SQL, DbConnection conn)
  {
   // --- Factory erzeugen
   DbProviderFactory provider = DbProviderFactories.GetFactory(CurrentProvider);
   // --- Teil 1: DataReader
   // Befehl erzeugen
   DbCommand cmd = provider.CreateCommand();
   cmd.CommandText = SQL;
   cmd.Connection = conn;
   return cmd;
  }

  /// <summary>
  /// Datenadapter erzeugen
  /// </summary>
  /// <param name="SQL"></param>
  /// <param name="conn"></param>
  /// <returns></returns>
  public static DbDataAdapter GetAdapter(string SQL, DbConnection conn)
  {
   return GetAdapter(GetCommand(SQL, GetConnection()), conn);
  }

  /// <summary>
  /// Datenadapter erzeugen
  /// </summary>
  /// <param name="command"></param>
  /// <param name="conn"></param>
  /// <returns></returns>
  public static DbDataAdapter GetAdapter(DbCommand command, DbConnection conn)
  {
   // --- Factory erzeugen
   DbProviderFactory provider = DbProviderFactories.GetFactory(CurrentProvider);
   // DataAdapter erzeugen
   DbDataAdapter adapter = provider.CreateDataAdapter();
   adapter.SelectCommand = command;
   return adapter;
  }

  /// <summary>
  /// Daten holen im Datareader
  /// </summary>
  /// <param name="SQL"></param>
  /// <returns></returns>
  public static DbDataReader GetReader(string SQL)
  {
   return GetCommand(SQL, GetConnection()).ExecuteReader();
  }

  /// <summary>
  /// CommandBuilder erzeugen
  /// </summary>
  /// <returns></returns>
  public static DbCommandBuilder GetCommandBuilder()
  {
   DbProviderFactory provider = DbProviderFactories.GetFactory(CurrentProvider);
   DbCommandBuilder cb = provider.CreateCommandBuilder();
   return cb;
  }

  /// <summary>
  /// Daten holen im Dataset
  /// </summary>
  /// <param name="SQL"></param>
  /// <returns></returns>
  public static DataSet GetDataSet(string SQL)
  {
   //System.Web.HttpContext.Current.Trace.Write("DBUtil.GetDataSet: " + SQL);
   DbConnection conn = GetConnection();
   DbDataAdapter da = GetAdapter(SQL, conn);
   // --- DataSet erzeugen
   DataSet ds = new DataSet();
   // --- Daten abholen
   da.Fill(ds);
   // --- Verbindung jetzt schon schließen!
   conn.Close();
   return ds;
  }

/// <summary>
  /// SQL-Befehle in einer Transaktion ausführen
/// </summary>
/// <param name="SQL"></param>
/// <returns></returns>
  public static long Execute(string SQL)
  {
   //System.Web.HttpContext.Current.Trace.Write("DBUtil.Execute: " + SQL);
   Console.WriteLine("DBUtil.Execute: " + SQL);
   DbConnection conn = GetConnection();
   DbCommand cmd = GetCommand(SQL, conn);
   long result = cmd.ExecuteNonQuery();
   conn.Close();
   return result;
  }

  /// <summary>
  /// Tabelle löschen
  /// </summary>
  /// <param name="table"></param>
  /// <returns></returns>
  public static long ClearTable(string table)
  {
   string SQL = "delete from " + table;
   return Execute(SQL);
  }

  /// <summary>
  /// Dataset aktualisieren
  /// </summary>
  /// <param name="ds"></param>
  /// <param name="SQL"></param>
  public static void UpdateDS(DataSet ds, string SQL)
  {
   DbDataAdapter da = GetAdapter(SQL, GetConnection());
   DbCommandBuilder cb = GetCommandBuilder();
   cb.DataAdapter = da;
   //da.UpdateBatchSize = 100;
   da.Update(ds);
  }

  /// <summary>
  /// Datensätze zählen
  /// </summary>
  /// <param name="SQL"></param>
  /// <returns></returns>
  public static long Count(string SQL)
  {
   object zelle = GetCommand(SQL, GetConnection()).ExecuteScalar();
   return Convert.ToInt64(zelle);
  }

  /// <summary>
  /// Ein Wert aus einem Datareader holen
  /// </summary>
  /// <param name="dr"></param>
  /// <param name="Spalte"></param>
  /// <returns></returns>
  public static object GetValue(IDataReader dr, string Spalte)
  {
   return null;
  }

 }
}
