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

namespace de.ITVisions.DALUtil
{
 public class DBUtil
 {
  public enum Providers
  {

   MSSQL,
   ACCESS
  }

  public static string CurrentConnString;
  public static string CurrentProvider = "System.Data.SqlClient";

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

   CurrentConnString = ConnString;
   //Console.WriteLine("Database: " + ConnString);
  }

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

  public static DbConnection GetConnection(string CONNECTIONSTRING)
  {
   // --- Factory erzeugen
   DbProviderFactory provider = DbProviderFactories.GetFactory(CurrentProvider);
   // --- Verbindung aufbauen
   DbConnection conn = provider.CreateConnection();
   conn.ConnectionString = CONNECTIONSTRING;
   conn.Open();
   return conn;
  }

  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;
  }

  public static DbDataAdapter GetAdapter(string SQL, DbConnection conn)
  {
   return GetAdapter(GetCommand(SQL, GetConnection()), conn);
  }

  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;
  }

  public static DbDataReader GetReader(string SQL)
  {
   return GetCommand(SQL, GetConnection()).ExecuteReader();
  }

  public static DbCommandBuilder GetCommandBuilder()
  {
   DbProviderFactory provider = DbProviderFactories.GetFactory(CurrentProvider);
   DbCommandBuilder cb = provider.CreateCommandBuilder();
   return cb;
  }

  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;
  }

  // === SQL-Befehle in einer Transaktion ausführen
  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;
  }

  public static long ClearTable(string table)
  {
   string SQL = "delete from " + table;
   return Execute(SQL);
  }

  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);
  }

  public static long Count(string SQL)
  {
   object zelle = GetCommand(SQL, GetConnection()).ExecuteScalar();
   return Convert.ToInt64(zelle);
  }

  public static object GetValue(IDataReader dr, string Spalte)
  {
   return null;
  }

 }
}
