[si3iep:2011] - QueryExecutor by nemanja.kojic@etf.rs

Pozdrav,
Nemanjausing System;
using System.Collections.Generic;
using System.Text;
using System.Data.Common;
using System.Data;

namespace EposApplication
{
    class QueryExecutor
    {
        private static QueryExecutor inst = null;
        protected DbProviderFactory myDBF = null;
        protected string connectionString = null;
        public static QueryExecutor instance()
        {
            if (inst == null) inst = new QueryExecutor();
            return inst;
        }

        
        public DataTable executeQuery(string query)
        {   
            IDbConnection conn = getNewConnection();
            // IDbConnection conn = new MySqlConnection(Properties.Settings.Default.epos_dbConnectionString);
            try
            {
                conn.Open();

                IDbCommand cmd = conn.CreateCommand();
                cmd.CommandText = query;

                IDataReader reader = cmd.ExecuteReader();

                DataTable dt = new DataTable(reader.GetSchemaTable().TableName);
                dt.Load(reader);

                return dt;
            }
            finally
            {
                conn.Close();
                conn.Dispose();
            }
        }

        /// <summary>
        /// 
        /// </summary>
        /// <param name="sql"></param>
        /// <returns></returns>
        /// <exception cref="InvalidOperationException"/>
        public int executeUpdate(string sql) 
        {
            IDbConnection conn = getNewConnection();
            IDbTransaction trans = null;
            // new MySqlConnection(Properties.Settings.Default.epos_dbConnectionString);
            try
            {
                int rowsAffected;
                conn.Open();
                trans = conn.BeginTransaction();
                {
                    IDbCommand cmd = conn.CreateCommand();
                    cmd.CommandText = sql;
                    cmd.Transaction = trans;
                    rowsAffected = cmd.ExecuteNonQuery();

                }
                trans.Commit();
                return rowsAffected;
            }
            catch (InvalidOperationException ioe)
            {
                if (trans != null) trans.Rollback();
                throw ioe;
            }
            finally
            {
                conn.Close();
                conn.Dispose();
            }
        }

        /// <summary>
        /// 
        /// </summary>
        /// <param name="sqlBatch"></param>
        /// <returns></returns>
        /// <exception cref="InvalidOperationException"/>
        public int[] executeUpdateBatch(params string[] sqlBatch)
        {
            IDbConnection conn = getNewConnection();
            IDbTransaction trans = null;
                //new MySqlConnection(Properties.Settings.Default.epos_dbConnectionString);
            try
            {
                int rowsAffected;
                conn.Open();
                trans = conn.BeginTransaction();
                {
                    IDbCommand cmd = conn.CreateCommand();
                    cmd.Transaction = trans;
                    foreach (string sql in sqlBatch)
                    {
                        cmd.CommandText = sql;
                        rowsAffected = cmd.ExecuteNonQuery();
                    }
                }
                trans.Commit();
                return null;
            }
            catch (InvalidOperationException ioe)
            {
                if (trans != null) trans.Rollback();
                throw ioe;
            }
            finally
            {
                conn.Close();
                conn.Dispose();
            }
        }

        public DataTable executeParameterizedQuery(string query)
        {
            IDbConnection con = null;
            try
            {
                con = getNewConnection();
                con.Open();
                IDbCommand cmd = con.CreateCommand();
                cmd.CommandText = "select * from studenti where name=@name";
                {
                    IDataParameter param = cmd.CreateParameter();
                    {
                        param.ParameterName = "@name";
                        param.Value = "Pera";
                        param.DbType = DbType.String;
                    }
                    cmd.Parameters.Add(param);
                }
                IDataReader rd = cmd.ExecuteReader();
                DataTable dt = new DataTable(rd.GetSchemaTable().TableName);
                dt.Load(rd);
                return dt;
            }
            finally
            {
                con.Close();
                con.Dispose();
            }
        }

        /// <summary>
        /// Protected/private methods.
        /// </summary>
        private QueryExecutor()
        {
            myDBF = DbProviderFactories.GetFactory(obtainDBProviderInvariantName());
            this.connectionString = Properties.Settings.Default.epos_dbConnectionString;
            // this.connectionString = Properties.Settings.Default.iepConnectionString;
        }

        protected IDbConnection getNewConnection()
        {
            IDbConnection c = myDBF.CreateConnection();
            c.ConnectionString = connectionString;
            return c;
        }


        protected string obtainDBProviderInvariantName()
        {
            return "MySql.Data.MySqlClient";
        }

    }
}