sqlServer (DataProvider)

/************************************************************************
--[ License ] -----------------------------------------------------------

  This program is free software; you can redistribute it and/or
  modify it under the terms of the GNU General Public License
  as published by the Free Software Foundation; either version 3
  of the License, or (at your option) any later version.

  This program is distributed in the hope that it will be useful,
  but WITHOUT ANY WARRANTY; without even the implied warranty of
  MERCHANTABILITY or FITNESS FOR A PARTICULAR PURPOSE.  See the
  GNU General Public License for more details.

  You should have received a copy of the GNU General Public License
  along with this program; if not, write to the Free Software
  Foundation, Inc., 59 Temple Place - Suite 330, Boston, MA  02111-1307, USA.

  You may also access the licence here: http://www.gnu.org/licenses/gpl.html

 ------------------------------------------------------------------------
  Copyright © 2009-11 Gomez & Associates
/************************************************************************/

using System;
using System.Collections;
using System.Collections.Generic;
using System.Data;
using System.Data.SqlClient;
using System.Text;
using System.Text.RegularExpressions;
using System.Windows.Forms;

namespace sqlServer
{
    public partial class DataProvider
    {
        internal string defaultInstance = Properties.Settings.Default.MachineName + @"\" +
                                          Properties.Settings.Default.DefaultInstance;

        internal bool   _cmudFormat      = Properties.Settings.Default.cmudFormat;
        internal string _currConn        = "";
        internal string _dQuote          = @"""";
        internal string _hashEntrySep    = Properties.Settings.Default.hashEntrySep;
        internal string _keyValueSep     = Properties.Settings.Default.keyValueSep;
        internal string _errormsg        = "";
        internal string _null            = @"""""";
        internal bool   _open            = false;
        internal string _dbName          = "";
        internal string _instance        = "";
        internal string _machineName     =  Properties.Settings.Default.MachineName;
        internal string _defaultInstance = Properties.Settings.Default.DefaultInstance;
        internal string _provider        = Properties.Settings.Default.Provider;
        internal string _resultSep       = Properties.Settings.Default.resultSep;
        internal bool   _useJson         = Properties.Settings.Default.jsonFormat;
        internal bool   _useMap          = Properties.Settings.Default.useMap;
        internal bool   _debug           = Properties.Settings.Default.Debug;
        internal string _version         = "00.00.0000.00";

        SqlConnection _conn = null; // sqlServer connection reference.

        SqlCommand cmd = null;

        string connStr = null;

        private void getOpenParms(string input)
        {
/*
            string[] temp = input.Split(new char[] { '\\' });

            if (temp.Length < 1 || temp.Length > 2) throw new Exception("Invalid parameter passed to Open method");

            if (temp.Length == 1)
            {
                _instance = defaultInstance;
                _dbName = temp[0];
            }

            else
            {
                _instance = temp[0];
                _dbName = temp[1];
            }

            _currConn = _instance + @"\" + _dbName;
*/
            // Added on 12/05/2011 to allow connections to LocalDB instances.
            int i = input.LastIndexOf(@"\");

            if (i != -1)
            {
                _instance = input.Substring(0, i);
                _dbName   = input.Substring(i + 1);
            }

            else
            {
                _instance = defaultInstance;
                _dbName   = input;
            }

            _currConn = _instance + @"\" + _dbName;
        }

        private void init(string dbName, bool json)
        {
            if (dbName != null) this.open(dbName);

            this.JSONFormat = json;

            initDelegateTable();
        }

        public void open(string dbName)
        {
            getOpenParms(dbName);

            if (_open)
            {
                if (_currConn != dbName)
                {
                    close();

                    open();
                }
            }

            else open();
        }

        public void open()
        {
            _errormsg = "";

 //           connStr = Properties.Settings.Default.connectionString.Replace(defaultInstance, _instance) + _dbName + @";";
            connStr = "Server=" + _instance + Properties.Settings.Default.connectionString + _dbName + @";"; // 12/05/2011

            try
            {
                _conn = new SqlConnection(connStr);

                _conn.Open();
            }

            catch (SqlException e)
            {
                _errormsg = e.Message;

                if (_conn != null) _conn.Close();
            }

            if (_conn.State.ToString() == "Open")
            {
                _open = true;
            }
        }

        public void close()
        {
            _errormsg = "";

            if (_open)
            {
                try
                {
                    _conn.Close();
                }

                catch (SqlException e)
                {
                    _errormsg = e.Message;
                }
            }

            else _errormsg = "Database is not open.";

            _open = false;
        }

        internal object execOpen(string sql, Hashtable args)
        {
            _errormsg = "";

            _instance = (string) args["instance"];

            _dbName = (string) args["dbName"];

            connStr = Properties.Settings.Default.connectionString.Replace(defaultInstance, _instance) + _dbName + @";";

            try
            {
                _conn = new SqlConnection(connStr);

                _conn.Open();
            }

            catch (SqlException e)
            {
                _errormsg = e.Message;

                if (_conn != null) _conn.Close();
            }

            if (_conn.State.ToString() == "Open") _open = true;

            return null;
        }

        internal object execClose(string sql, Hashtable args)
        {
            _errormsg = "";

            if (_open)
            {
                try
                {
                    _conn.Close();
                }

                catch (SqlException e)
                {
                    _errormsg = e.Message;
                }
            }

            else _errormsg = "Database is not open.";

            _open = false;

            return null;
        }

        internal object execInsert(string sql, Hashtable args)
        {
            return null;
        }

        internal object execDelete(string sql, Hashtable args)
        {
            return null;
        }

        internal object execNonQuery(string sql, Hashtable args)
        {
            _errormsg = "";

            cmd = new SqlCommand(sql, _conn);

            cmd.CommandType = CommandType.Text;

            cmd.Parameters.Clear();

            try
            {
                if (args != null)
                {
                    foreach (DictionaryEntry kv in args)
                    {
                        if (kv.Value.GetType() == typeof(String)) cmd.Parameters.AddWithValue((string) kv.Key, kv.Value);

                        else cmd.Parameters.AddWithValue((string) kv.Key, kv.Value.ToString());
                    }
                }

                return cmd.ExecuteNonQuery();
            }

            catch (Exception e)
            {
                _errormsg = e.Message;

                return 0;
            }
        }

        internal object execProc(string sql, Hashtable args)
        {
            _errormsg = "";

            SqlDataReader dr = null;

            cmd = new SqlCommand(sql, _conn);

            cmd.CommandType = CommandType.StoredProcedure;

            cmd.Parameters.Clear();

            try
            {
                if (args != null)
                {
                    foreach (DictionaryEntry kv in args)
                    {
                        if (kv.Value.GetType() == typeof(String)) cmd.Parameters.AddWithValue((string) kv.Key, kv.Value);

                        else cmd.Parameters.AddWithValue((string) kv.Key, kv.Value.ToString());
                    }
                }

                dr = cmd.ExecuteReader();

                return getData(dr);
            }

            catch (Exception e)
            {
                _errormsg = e.Message;

                return null;
            }

            finally
            {
                if (!(dr == null || dr.IsClosed)) dr.Close();
            }
        }

        internal object execProcNR(string sql, Hashtable args)
        {
            _errormsg = "";

            cmd = new SqlCommand(sql, _conn);

            cmd.CommandType = CommandType.StoredProcedure;

            cmd.Parameters.Clear();

            try
            {
                if (args != null)
                {
                    foreach (DictionaryEntry kv in args)
                    {
                        if (kv.Value.GetType() == typeof(String)) cmd.Parameters.AddWithValue((string) kv.Key, kv.Value);

                        else cmd.Parameters.AddWithValue((string) kv.Key, kv.Value.ToString());
                    }
                }

                return cmd.ExecuteNonQuery();
            }

            catch (Exception e)
            {
                _errormsg = e.Message;

                return 0;
            }
        }

        internal object execSelect(string sql, Hashtable args)
        {
            _errormsg = "";

            cmd = new SqlCommand(sql, _conn);

            cmd.CommandType = CommandType.Text;

            cmd.Parameters.Clear();

            try
            {
                if (args != null)
                {
                    foreach (DictionaryEntry kv in args)
                    {
                        if (kv.Value.GetType() == typeof(String)) cmd.Parameters.AddWithValue((string) kv.Key, kv.Value);

                        else cmd.Parameters.AddWithValue((string) kv.Key, kv.Value.ToString());
                    }
                }

                SqlDataReader dr = cmd.ExecuteReader();

                return getData(dr);
            }

            catch (Exception e)
            {
                _errormsg = e.Message;

                return null;
            }
        }

        internal string execScalar(string sql, Hashtable args)
        {
            _errormsg = "";

            cmd = new SqlCommand(sql, _conn);

            cmd.CommandType = CommandType.Text;

            cmd.Parameters.Clear();

            try
            {
                if (args != null)
                {
                    foreach (DictionaryEntry kv in args)
                    {
                        if (kv.Value.GetType() == typeof(String)) cmd.Parameters.AddWithValue((string) kv.Key, kv.Value);

                        else cmd.Parameters.AddWithValue((string) kv.Key, kv.Value.ToString());
                    }
                }

                object result = cmd.ExecuteScalar();

//                if (_useJson) return JsonConvert.SerializeObject(result);

                if (_useJson) return JSON.JSON.JsonEncode(result);

                return result.ToString();
            }

            catch (Exception e)
            {
                _errormsg = e.Message;

//                if (_useJson) return JsonConvert.SerializeObject("");

                if (_useJson) return JSON.JSON.JsonEncode("");

                return null;
            }
        }

        internal object execUpdate(string sql, Hashtable args)
        {
            return null;
        }

        private string testnull(string arg)
        {
            return (arg != null) ? arg : _null;
        }

        private string normalize(string arg)
        {
            string m = @"\""|\|";
            string s = arg.Replace("\"", "\"\"");

            return Regex.IsMatch(s, m) == false ? s : "\"" + s + "\"";
        }

        private string getJsonData(SqlDataReader dr)
        {
            return JSON.JSON.JsonEncode(getJsonDataRecord(dr));
        }

        private string getData(SqlDataReader dr)
        {
            if (_useJson) return getJsonData(dr);

            List<string> list = new List<string>(), result = new List<string>();

            if (dr.HasRows)
            {
                do
                {
                    while (dr.Read())
                    {
                        string k, v;

                        for (int i = 0; i < dr.FieldCount; i++)
                        {
                            k = dr.GetName(i);
                            v = testnull(dr.GetValue(i).ToString());

                            if (this._useMap) list.Add(normalize(k) + this._keyValueSep + normalize(v));

                            else list.Add(normalize(v));
                        }

                        result.Add(string.Join(_hashEntrySep, list.ToArray()));
                    }
                }

                while (dr.NextResult());
            }

            dr.Close();

            return string.Join(ResultSep, list.ToArray());
        }

        private ArrayList getJsonDataRecord(SqlDataReader dr)
        {
            ArrayList record = new ArrayList();

            Hashtable h = null;

            if (dr.HasRows)
            {
                do
                {
                    while (dr.Read())
                    {
                        string k;
                        object v;

                        double nv;

                        if (_useMap) h = new Hashtable();

                        for (int i = 0; i < dr.FieldCount; i++)
                        {
                            k = dr.GetName(i);
                            v = dr.GetValue(i);

                            if (!double.TryParse(v.ToString(), out nv)) v = testnull(v.ToString());

                            else v = (Int64) Math.Floor(nv);

                            if (_useMap) h.Add(k, v);

                            else record.Add(v);
                        }

                        if (_useMap) record.Add(h);
                    }
                }

                while (dr.NextResult());
            }

            dr.Close();

            return record;
        }
    }
}