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
  }

 

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

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

  // public static DbConnection GetConfiguredConnection()
  //{
  // // --- Fabrik  erzeugen
  // DbProviderFactory provider = DBConfig.
  // // --- 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>
  private static DbConnection AktiveConnection = null;

  public static DbConnection GetConnection(string CONNECTIONSTRING)
  {
   if (AktiveConnection == null)  
   {
   // --- Fabrik  erzeugen
   DbProviderFactory provider = DbProviderFactories.GetFactory(DBConfig.CurrentProvider);
   // --- Verbindung aufbauen
   AktiveConnection = provider.CreateConnection();
   AktiveConnection.ConnectionString = CONNECTIONSTRING;
   }
   if (AktiveConnection.State == ConnectionState.Closed)
   {
    //DBConfig.TraceSource.TraceData(System.Diagnostics.TraceEventType.Warning,10,"Opening Connection: " + CONNECTIONSTRING);
    AktiveConnection.Open();
   }
   return AktiveConnection;
  }

  /// <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(DBConfig.CurrentProvider);

   DBConfig.Trace("SQL: " + SQL);
   // --- 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(DBConfig.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(DBConfig.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>
  /// Hole Daten in einer DataTable
  /// </summary>
  /// <param name="SQL"></param>
  /// <returns></returns>
  public static DataTable GetDataTable(string SQL)
  {
   return GetDataSet(SQL).Tables[0];
  }

  /// <summary>
  /// Hole eine einzelne DataRow
  /// </summary>
  /// <param name="SQL"></param>
  /// <returns></returns>
  public static string[] GetColumn(string SQL)
  {
   DataTable dt = GetDataSet(SQL).Tables[0];
   string[] Werte = new string[dt.Rows.Count];
   for (int c = 0; c < dt.Rows.Count; c++)
   {
    Werte[c] = dt.Rows[c].ItemArray[0].ToString();
   }
   return Werte;
  }

  /// <summary>
  /// Hole Daten in einer DataRow
  /// </summary>
  /// <param name="SQL"></param>
  /// <returns></returns>
  public static DataRow GetDataRow(string SQL)
  {
   return GetDataSet(SQL).Tables[0].Rows[0];
  }

/// <summary>
  /// SQL-Befehle in einer Transaktion ausführen
/// </summary>
/// <param name="SQL"></param>
/// <returns></returns>
  public static long Execute(string SQL)
  {
   //DBConfig.Trace("DBUtil.Execute: " + 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;
  }

 }
}
