using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;

using System.Data;
using FirebirdSql.Data.FirebirdClient;

using System.IO;

using System.Windows;  // MessageBox


namespace DBS1 {
    public class Dbs {
        private FbConnection _cnn = null;

        private static Dbs dbs = new Dbs();

        public static Dbs getDbsInstance() {
            return dbs;
        }

        // index := getGenerator('GEN_DB');
        // SET GENERATOR GEN_DB TO 0
        //public static long getGenerator(String genname) {
        //    String sql = "SELECT GEN_ID( " + genname + ", 1 ) FROM RDB$DATABASE;";
        //    using (DataTable dt1 = dbs.getDBSData(sql)) {
        //        foreach (System.Data.DataRow row in dt1.Rows) {
        //            Object[] objs = row.ItemArray;
        //            long index = (long)objs[0];
        //            return index;
        //        }
        //        return 0;
        //    }
        //}


        public static long getSequenz(String seqname) {
            String sql = "SELECT NEXT VALUE FOR " + seqname + " FROM RDB$DATABASE;";
            using (DataTable dt1 = dbs.selectDBS(sql)) {
                foreach (System.Data.DataRow row in dt1.Rows) {
                    Object[] objs = row.ItemArray;
                    long index = (long)objs[0];
                    return index;
                }
                return 0;
            }
        }


        public void setDbs(string sDatabaseFile) {
            // Environment.GetFolderPath(Environment.SpecialFolder.Personal);
            string sPath;
            sPath = Environment.CurrentDirectory;
            //sPath = "C:\\Daten";
            if (!sPath.EndsWith("\\")) sPath += "\\";
            string sArg =
                "User=SYSDBA;" +
                "Password=masterkey;" +
                "Database=" + sDatabaseFile + ";" +
                "DataSource=localhost;" +
                "Port=3050;" +
                "Dialect=3;" +
                "Charset=NONE;" +
                "Role=;" +
                "Connection lifetime=15;" +
                "Pooling=true;" +
                "MinPoolSize=0;" +
                "MaxPoolSize=50;" +
                "Packet Size=8192;" +
                "ServerType=1";

            if (_cnn != null) {
                //if (_cnn.Close();  // pro Forma
            }
            _cnn = new FbConnection(sArg);
            if (_cnn == null)
                MessageBox.Show("Fehler beim Öffnen der Datenbank\r\n" +
                      sPath + sDatabaseFile, "Fehler");
            else
                _cnn.Open();
        }  // dbs



        //_dbs.createDatabase("C:\\Daten\\test1.fdb");
        public void createDatabase(string filename) {
            if (File.Exists(filename)) {
                File.Delete(filename);
            }
            string sArg1 =
                "User=SYSDBA;" +
                "Password=masterkey;" +
                "Database=" + filename + ";" +
                "DataSource=localhost;" +
                "Port=3050;" +
                "Dialect=3;" +
                "Charset=NONE;" +
                "Role=;" +
                "Connection lifetime=15;" +
                "Pooling=true;" +
                "MinPoolSize=0;" +
                "MaxPoolSize=50;" +
                "Packet Size=8192;" +
                "ServerType=1";

            string sArg =
                "User=SYSDBA;" +
                "Password=masterkey;" +
                "Database=" + filename + ";" +
                "DataSource=localhost;" +
                "Dialect=3;" +
                "Packet Size=8192;" +
                "ServerType=1";
            FbConnection.CreateDatabase(sArg);
            // Alternative Parameter
            //FbConnection.CreateDatabase(connectionstring, pagesize, forcedwrite, overwrite
        }

        public void Close() {
            if (_cnn != null) {
                _cnn.Close();
            }
        }

        // Ruft die internen Strukturen der Datenbanken ab
        public DataTable GetSchema(string sItem, string[] filter) {
            return _cnn.GetSchema(sItem, filter);
        }

        // hier gibt es keine Tabelle
        public void changeDBS(string sSQL) {
            FbDataAdapter da = new FbDataAdapter(sSQL, _cnn);
            try {
                {
                    DataTable dt = new DataTable("RELATIONS");
                    try {
                        da.Fill(dt);
                    }
                    finally {
                        if (dt != null) ((IDisposable)dt).Dispose();
                    }
                }
                //using (DataTable dt = new DataTable("RELATIONS")) {                    
                //    da.Fill(dt);
                //}
            }
            catch (Exception e) {
                MessageBox.Show("Fehlerhafte SQL-Anweisung\r\n" + e.Message + "\r\n" + sSQL, "SQL-Fehler", MessageBoxButton.OK, MessageBoxImage.Error);
                return;
            }
            finally {
                if (da != null) ((IDisposable)da).Dispose();
            }
        }

        public DataTable selectDBS(string sSQL) {
            try {
                FbDataAdapter da = new FbDataAdapter(sSQL, _cnn);
                DataTable dt = new DataTable("RELATIONS");
                da.Fill(dt);
                //da.Dispose();
                return dt;
            }
            catch (Exception e) {
                MessageBox.Show("Fehlerhafte SQL-Anweisung\r\n" + e.Message + "\r\n" + sSQL, "SQL-Fehler", MessageBoxButton.OK, MessageBoxImage.Error);
                return null;
            }
        }

        


    }
}