Luckycall/ReportWebCore/DbLib/SqlStatement.cs

using System;
using System.Collections.Generic;
using System.Text;
using System.IO;
using System.Data;
using System.Reflection;
using Oracle.ManagedDataAccess.Client;
using System.Threading;
using System.Data.Common;
using Npgsql;
using MySql.Data.MySqlClient;

namespace Db_Core
{
    public class SqlStatement
    {
        private String strSQL;
        private List<int> questionMarkPos = new List<int>();
        private object[] values;
        private string checkConnectionCmd = "select 1 from dual";

        public string connectionString;
        DbConnection conn;

        private SqlStatement() { }

        public SqlStatement(SqlConnString sqlConn)
        {
            this.connectionString = SqlCommon.GetConnectionString(sqlConn);
        }
        public SqlStatement(String sqlConn)
        {
            this.connectionString = sqlConn;
        }

        public SqlStatement(string strSQL, SqlConnString sqlConn)
        {
            this.strSQL = strSQL;
            this.connectionString = SqlCommon.GetConnectionString(sqlConn);

            int openSquareBracketCount = 0, closeSquareBracketCount = 0;
            int firstOpenSquareBracketPos = -2, firstCloseSquareBrackPos = -1;
            for (int i = 0; i < strSQL.Length; i++)
            {
                if (strSQL[i] == '?')
                    questionMarkPos.Add(i);
                if (strSQL[i] == '[')
                    openSquareBracketCount++;
                if (strSQL[i] == ']')
                    closeSquareBracketCount++;
            }

            // check validate SQL template
            if (openSquareBracketCount != closeSquareBracketCount || firstOpenSquareBracketPos > firstCloseSquareBrackPos)
                throw new Exception("SQL template incorrect");

            values = new object[questionMarkPos.Count];
            for (int i = 0; i < values.Length; i++)
                values[i] = Missing.Value;
        }

        public SqlStatement(string strSQL, String connectionString)
        {
            this.strSQL = strSQL;
            this.connectionString = connectionString;

            int openSquareBracketCount = 0, closeSquareBracketCount = 0;
            int firstOpenSquareBracketPos = -2, firstCloseSquareBrackPos = -1;
            for (int i = 0; i < strSQL.Length; i++)
            {
                if (strSQL[i] == '?')
                    questionMarkPos.Add(i);
                if (strSQL[i] == '[')
                    openSquareBracketCount++;
                if (strSQL[i] == ']')
                    closeSquareBracketCount++;
            }

            // check validate SQL template
            if (openSquareBracketCount != closeSquareBracketCount || firstOpenSquareBracketPos > firstCloseSquareBrackPos)
                throw new Exception("SQL template incorrect");

            values = new object[questionMarkPos.Count];
            for (int i = 0; i < values.Length; i++)
                values[i] = Missing.Value;
        }

        public static SqlStatement FromString(string strSQL, SqlConnString sqlConn)
        {
            return new SqlStatement(strSQL, sqlConn);
        }

        public static SqlStatement FromFile(string sqlFilePath, SqlConnString sqlConn)
        {
            StreamReader sr = new StreamReader(sqlFilePath);
            String str = sr.ReadToEnd();
            sr.Close();
            return FromString(str, sqlConn);
        }

        public static String GetSqlStringFromFile(string sqlFilePath)
        {
            StreamReader sr = new StreamReader(sqlFilePath);
            String str = sr.ReadToEnd();
            sr.Close();
            return str;
        }

        /// <summary>
        /// Set parameter into command.
        /// Start index from 0
        /// </summary>
        /// <param name="index"></param>
        /// <param name="value"></param>
        public void SetParameter(int index, object value)
        {
            values[index] = value;
        }

        public void SetParameters(params object[] args)
        {
            for (int i = 0; i < args.Length; i++)
                SetParameter(i, args[i]);
        }

        public string GetSQL()
        {
            StringBuilder result = new StringBuilder(strSQL);
            for (int i = 0; i < questionMarkPos.Count; i++)
                if (!(values[i] is Missing))
                {
                    int pos = questionMarkPos[i];
                    int leftBracketPos = pos - 1;
                    while (leftBracketPos >= 0 && result[leftBracketPos] != '[' && result[leftBracketPos] != ']')
                        leftBracketPos--;
                    if (leftBracketPos == -1)
                        continue;
                    if (result[leftBracketPos] == ']')
                        continue;
                    int rightBracketPos = pos + 1;
                    while (rightBracketPos < result.Length && result[rightBracketPos] != '[' && result[rightBracketPos] != ']')
                        rightBracketPos++;
                    if (rightBracketPos == result.Length)
                        continue;
                    if (result[rightBracketPos] == '[')
                        continue;
                    result[leftBracketPos] = ' ';
                    result[rightBracketPos] = ' ';
                }

            bool replace = false;
            for (int i = 0; i < result.Length; i++)
            {
                char ch = result[i];
                if (ch == '[')
                    replace = true;
                if (replace)
                    result[i] = ' ';
                if (ch == ']')
                    replace = false;
            }

            int whereIndex = result.ToString().IndexOf("where", StringComparison.OrdinalIgnoreCase);
            for (int i = questionMarkPos.Count - 1; i >= 0; i--)
                if (!(values[i] is Missing))
                {
                    String temp = null;
                    if (values[i] == null)
                    {
                        for (int j = questionMarkPos[i] - 1; j >= 0; j--)
                        {
                            if (result[j] == ' ')
                                continue;
                            if (result[j] == '=')
                            {
                                if (whereIndex < j)
                                {
                                    result[j] = ' ';
                                    temp = "is null";
                                }
                                else
                                    temp = "null";
                                break;
                            }
                            else
                                temp = "null";
                        }
                    }
                    else if (values[i] is Enum)
                        temp = ((int)values[i]).ToString();
                    else if (values[i] is string)
                        temp = "'" + values[i].ToString().Replace("'", "''") + "'";
                    else if (values[i] is DateTime)
                        temp = "TO_DATE('" + ((DateTime)values[i]).ToString("dd/MM/yyyy HH:mm:ss") + "', 'dd/MM/yyyy hh24:mi:ss')";
                        //temp = "'" + ((DateTime)values[i]).ToString("yyyy/MM/dd HH:mm:ss") + "'";
                    else
                        temp = values[i].ToString();
                    result.Remove(questionMarkPos[i], 1).Insert(questionMarkPos[i], temp);
                }

            return result.ToString();
        }

        public OracleConnection GetConnection()
        {
            conn = null;
            OracleCommand command = null;
            int retry = 0;
            while (conn == null && retry < 3)
            {
                try
                {
                    conn = new OracleConnection(connectionString);
                    if (conn == null)
                        throw new Exception("Too many connections to database");
                    conn.Open();
                    //test command
                    command = new OracleCommand(checkConnectionCmd, (OracleConnection)conn);
                    command.ExecuteReader().Close();
                    return (OracleConnection)conn;
                }
                catch (Exception ex)
                {
                    retry++;
                    try
                    {
                        if (conn != null)
                        {
                            if (conn.State != ConnectionState.Closed)
                                conn.Close();
                        }
                    }
                    catch { }
                    finally
                    {
                        conn = null;
                    }
                }
            }
            return (OracleConnection)conn;
        }

        public DbConnection GetConnection(ConnectionType connType)
        {
            conn = null;
            DbCommand command = null;
            int retry = 0;
            while (conn == null)
            {
                try
                {
                    if (connType == ConnectionType.POSTGRE)
                    {
                        conn = new NpgsqlConnection(connectionString);
                        if (conn == null)
                            throw new Exception("Too many connections to database");
                        conn.Open();
                        //test command
                        //command = new NpgsqlCommand(checkConnectionCmd, (NpgsqlConnection)conn);
                        //command.ExecuteReader().Close();
                        return conn;
                    }
                    else if (connType == ConnectionType.MYSQL)
                    {
                        conn = new MySqlConnection(connectionString);
                        if (conn == null)
                            throw new Exception("Too many connections to database");
                        conn.Open();
                        //test command
                        //command = new NpgsqlCommand(checkConnectionCmd, (NpgsqlConnection)conn);
                        //command.ExecuteReader().Close();
                        return conn;
                    }
                    else
                    {
                        conn = new OracleConnection(connectionString);
                        if (conn == null)
                            throw new Exception("Too many connections to database");
                        conn.Open();
                        //test command
                        // default is oracle
                        command = new OracleCommand(checkConnectionCmd, (OracleConnection)conn);
                        command.CommandTimeout = 120;
                        command.ExecuteReader().Close();
                        return conn;
                    }
                }
                catch (Exception ex)
                {
                    retry++;
                    try
                    {
                        if (conn != null)
                        {
                            if (conn.State != ConnectionState.Closed)
                                conn.Close();
                        }
                    }
                    catch { }
                    finally
                    {
                        conn = null;
                    }

                    if (retry >= 3)
                    {
                        throw ex;
                    }
                }
            }
            return conn;
        }

        public OracleDataReader ExecuteReader()
        {
            OracleConnection conn = null;
            OracleCommand command = null;
            string sql = GetSQL();
            try
            {
                conn = GetConnection();
                command = new OracleCommand(sql, conn);
                return command.ExecuteReader(CommandBehavior.CloseConnection);
            }
            catch (Exception e)
            {
                if (conn != null)
                    if (conn.State != ConnectionState.Closed)
                        conn.Close();
                throw e;
            }
        }

        public DbDataReader ExecuteReader(ConnectionType connType)
        {
            DbConnection conn = null;
            DbCommand command = null;
            string sql = GetSQL();
            try
            {
                conn = GetConnection(connType);
                if (connType == ConnectionType.POSTGRE)
                {
                    command = new NpgsqlCommand(sql, (NpgsqlConnection)conn);
                    return command.ExecuteReader(CommandBehavior.CloseConnection);
                }
                else if (connType == ConnectionType.MYSQL)
                {
                    command = new MySqlCommand(sql, (MySqlConnection)conn);
                    return command.ExecuteReader(CommandBehavior.CloseConnection);
                }
                else
                {
                    command = new OracleCommand(sql, (OracleConnection)conn);
                    return command.ExecuteReader(CommandBehavior.CloseConnection);
                }
            }
            catch (Exception e)
            {
                if (conn != null)
                    if (conn.State != ConnectionState.Closed)
                        conn.Close();
                throw e;
            }
        }

        public void CloseConnection()
        {
            try
            {
                if (conn != null)
                    if (conn.State != ConnectionState.Closed)
                        conn.Close();
            }
            catch
            { }
        }

        public object ExecuteScalar()
        {
            OracleConnection conn = null;
            string sql = GetSQL();
            try
            {
                conn = GetConnection();
                OracleCommand command = new OracleCommand(sql, conn);
                object result = command.ExecuteScalar();
                conn.Close();
                return result;
            }
            catch (Exception e)
            {
                if (conn != null)
                    if (conn.State != ConnectionState.Closed)
                        conn.Close();
                throw e;
            }
        }

        public int ExecuteNonQuery()
        {
            OracleConnection conn = null;
            string sql = GetSQL();
            try
            {
                conn = GetConnection();
                OracleCommand command = new OracleCommand(sql, conn);
                int result = command.ExecuteNonQuery();
                conn.Close();
                return result;
            }
            catch (Exception e)
            {
                if (conn != null)
                    if (conn.State != ConnectionState.Closed)
                        conn.Close();
                throw e;
            }
        }

        public int ExecuteNonQuery(ConnectionType connType)
        {
            DbConnection conn = null;
            string sql = GetSQL();
            try
            {
                conn = GetConnection(connType);
                if (connType == ConnectionType.POSTGRE)
                {
                    NpgsqlCommand command = new NpgsqlCommand(sql, (NpgsqlConnection)conn);
                    int result = command.ExecuteNonQuery();
                    conn.Close();
                    return result;
                }
                else if (connType == ConnectionType.MYSQL)
                {
                    MySqlCommand command = new MySqlCommand(sql, (MySqlConnection)conn);
                    int result = command.ExecuteNonQuery();
                    conn.Close();
                    return result;
                }
                else
                {
                    OracleCommand command = new OracleCommand(sql, (OracleConnection)conn);
                    int result = command.ExecuteNonQuery();
                    conn.Close();
                    return result;
                }
            }
            catch (Exception e)
            {
                if (conn != null)
                    if (conn.State != ConnectionState.Closed)
                        conn.Close();
                throw e;
            }
        }
    }
}