using System;
using System.Data;
using System.Data.Odbc;
using System.Data.OleDb;
using System.Data.SqlClient;
using System.Data.OracleClient;


    [Serializable]
    public class SQLHelper
    {
        public enum dataType
        {
            VarChar,
            NVarChar,
            DateTime,
            Int,
            SmallInt,
            VarBinary,
            Bool,
            Money,
            BigInt,
            Text,
            CharFixed,
            Dec,
        }
        
        private enum ConnectionType
        {
            SQL,
            OleDb,
            ODBC,
            Oracle,
        }

        public static IDbCommand MakeCommand(string command, IDbConnection con, CommandType commandType)
        {
            try
            {
                if(con.GetType() == typeof(SqlConnection))
                {
                    SqlCommand cmd;
                    cmd = new SqlCommand(command, (SqlConnection)con);
                    cmd.CommandType = commandType;
                    return cmd;
                }       
                else if(con.GetType() == typeof(OleDbConnection))
                {
                    OleDbCommand cmd;
                    cmd = new OleDbCommand(command, (OleDbConnection)con);
                    cmd.CommandType = commandType;
                    return cmd;
                }     
                else if(con.GetType() == typeof(OdbcConnection))
                {
                    OdbcCommand cmd;
                    cmd = new OdbcCommand(command, (OdbcConnection)con);
                    cmd.CommandType = commandType;
                    return cmd;
                }
                else if (con.GetType() == typeof(OracleConnection))
                {
                    OracleCommand cmd;
                    cmd = new OracleCommand(command, (OracleConnection)con);
                    cmd.CommandType = commandType;
                    return cmd;
                } 
                else
                    return null;
            }
            catch(Exception exc)
            {
                throw new Exception("Error in MakeCommand", exc);
            }
        }

        public static IDataParameter MakeParameter(IDbCommand command, string name, dataType type, ParameterDirection direction)
        {
            try
            {
                if(command.GetType() == typeof(SqlCommand))
                {
                    SqlParameter parameter;
                    parameter = new SqlParameter();
                    parameter.ParameterName = name;
                    parameter.Direction = direction;
                    parameter.SqlDbType = (SqlDbType)ConvertDbType(ConnectionType.SQL, type);
                    command.Parameters.Add(parameter);
                    return parameter;
                }     
                else if(command.GetType() == typeof(OleDbCommand))
                {
                    OleDbParameter parameter;
                    parameter = new OleDbParameter();
                    parameter.ParameterName = name;
                    parameter.Direction = direction;
                    parameter.DbType = (DbType)ConvertDbType(ConnectionType.OleDb, type);
                    command.Parameters.Add(parameter);
                    return parameter;
                }    
                else if(command.GetType() == typeof(OracleCommand))
                {
                    OracleParameter parameter;
                    parameter = new OracleParameter();
                    parameter.ParameterName = name;
                    parameter.Direction = direction;
                    parameter.OracleType = (OracleType)ConvertDbType(ConnectionType.Oracle, type);
                    command.Parameters.Add(parameter);
                    return parameter;
                }
                else if (command.GetType() == typeof(OdbcCommand))
                {
                    OdbcParameter parameter;
                    parameter = new OdbcParameter();
                    parameter.ParameterName = name;
                    parameter.Direction = direction;
                    parameter.OdbcType = (OdbcType)ConvertDbType(ConnectionType.ODBC, type);
                    command.Parameters.Add(parameter);
                    return parameter;
                } 
                else
                    return null;
            }    
            catch(Exception exc)
            {
                throw new Exception("Error in MakeParameter", exc);
            }         
        }
        
        public static IDataAdapter MakeDataAdapter(IDbCommand commandSelect)
        {
            try
            {
                if(commandSelect.Connection.GetType() == typeof(SqlConnection))
                {
                    SqlDataAdapter adapter;
                    adapter = new SqlDataAdapter((SqlCommand)commandSelect);
                    return adapter;
                }
                    
                else if(commandSelect.Connection.GetType() == typeof(OleDbConnection))
                {
                    OleDbDataAdapter adapter;
                    adapter = new OleDbDataAdapter((OleDbCommand)commandSelect);
                    return adapter;
                }
                else if(commandSelect.Connection.GetType() == typeof(OdbcConnection))
                {
                    OdbcDataAdapter adapter;
                    adapter = new OdbcDataAdapter((OdbcCommand)commandSelect);
                    return adapter;
                }
                else if (commandSelect.Connection.GetType() == typeof(OracleConnection))
                {
                    OracleDataAdapter adapter;
                    adapter = new OracleDataAdapter((OracleCommand)commandSelect);
                    return adapter;
                } 
                else
                    return null;
            }
            catch(Exception exc)
            {
                throw new Exception("Error in MakedataAdapter(p_commandSelect)", exc);
            }    
        }

        public static IDataAdapter MakeDataAdapter(IDbCommand commandSelect, IDbCommand commandDelete, IDbCommand commandInsert, IDbCommand commandUpdate)
        {
            try
            {
                if(commandSelect.Connection.GetType() == typeof(SqlConnection))
                {
                    SqlDataAdapter adapter;
                    adapter = (SqlDataAdapter)SQLHelper.MakeDataAdapter(commandSelect);
                    adapter.DeleteCommand = (SqlCommand)commandDelete;
                    adapter.InsertCommand = (SqlCommand)commandInsert;
                    adapter.UpdateCommand = (SqlCommand)commandUpdate;
                    return adapter;
                }
                    
                else if(commandSelect.Connection.GetType() == typeof(OleDbConnection))
                {
                    OleDbDataAdapter adapter;
                    adapter = (OleDbDataAdapter)SQLHelper.MakeDataAdapter(commandSelect);
                    adapter.DeleteCommand = (OleDbCommand)commandDelete;
                    adapter.InsertCommand = (OleDbCommand)commandInsert;
                    adapter.UpdateCommand = (OleDbCommand)commandUpdate;
                    return adapter;
                }
                
                else if(commandSelect.Connection.GetType() == typeof(OdbcConnection))
                {
                    OdbcDataAdapter adapter;
                    adapter = (OdbcDataAdapter)SQLHelper.MakeDataAdapter(commandSelect);
                    adapter.DeleteCommand = (OdbcCommand)commandDelete;
                    adapter.InsertCommand = (OdbcCommand)commandInsert;
                    adapter.UpdateCommand = (OdbcCommand)commandUpdate;
                    return adapter;
                }

                else if (commandSelect.Connection.GetType() == typeof(OracleConnection))
                {
                    OracleDataAdapter adapter;
                    adapter = (OracleDataAdapter)SQLHelper.MakeDataAdapter(commandSelect);
                    adapter.DeleteCommand = (OracleCommand)commandDelete;
                    adapter.InsertCommand = (OracleCommand)commandInsert;
                    adapter.UpdateCommand = (OracleCommand)commandUpdate;
                    return adapter;
                }
                else
                    return null;
            }      
            catch(Exception exc)
            {
                throw new Exception("Error in MakedataAdapter(commandSelect, commandDelete, commandInsert, commandUpdate)", exc);
            }
        }