Map3D Industry Model : Select job topic states

select * from tb_job_topic jt,
TB_JOB_TOPIC_STATE jts,
S497_NETWORK_UPDATE nu,
S497_NETWORK_UPDATE_JOB nuj,
TB_JOB j
where jt.ID = jts.JOB_TOPIC_ID
and nu.NWUP_ID = nuj.NWUP_ID
and j.id = nuj.JOB_ID
and nuj.JOB_ID = 837
and jt.JOB_ID = 837
and jts.SELECTED = :state;

select * from tb_job_topic jt,
TB_JOB_TOPIC_STATE jts,
S497_NETWORK_UPDATE nu,
S497_NETWORK_UPDATE_JOB nuj,
TB_JOB j
where jt.ID = jts.JOB_TOPIC_ID
and nu.NWUP_ID = nuj.NWUP_ID
and j.id = nuj.JOB_ID
and nuj.JOB_ID = :jid
and jt.JOB_ID = :jid
and jts.SELECTED = :state;

Map3D Industry Model : Modify Job States & Job State Transitions

By default Map 3D industry models have 4 job states and their corresponding job state transitions.
It is possible to add some extra states and modify or add corresponding state transitions if the defaults are insufficient for your implementation.

Default job states

select * from tb_job_state;

Default job state transitions

select * from tb_job_state_transition;

Transition Direction

• (F,Forward)
• (B,Backward)

Transition Type

• (M,Merge the features)
• (D,Delete the job)
• (N, Do nothing)

SQL Example

--insert new job states into table tb_job_state

delete from tb_job_state where id in(5,6,7,8,9);

delete from TB_JOB_STATE_TRANSITION where id in(6,7,8,9,10);

delete from TB_JOB_STATE_TRANSITION where id in(12,13,14,15,16,22);

delete from TB_JOB_STATE_TRANSITION where id in(17,18,19,20,21);

insert into tb_job_state (ID, DESCRIPTION, FEATURES_EDITABLE,INITIAL_STATE,NAME,USE_JOBID) VALUES(5,null,1,1,'Planning',1);

insert into tb_job_state (ID, DESCRIPTION, FEATURES_EDITABLE,INITIAL_STATE,NAME,USE_JOBID) VALUES(6,null,1,0,'Design',0);

insert into tb_job_state (ID, DESCRIPTION, FEATURES_EDITABLE,INITIAL_STATE,NAME,USE_JOBID) VALUES(7,null,1,0,'Phase Planning',0);

insert into tb_job_state (ID, DESCRIPTION, FEATURES_EDITABLE,INITIAL_STATE,NAME,USE_JOBID) VALUES(8,null,1,0,'Construction',0);

insert into tb_job_state (ID, DESCRIPTION, FEATURES_EDITABLE,INITIAL_STATE,NAME,USE_JOBID) VALUES(9,null,1,0,'Asset Management',0);

--insert new job state transitions forward into table tb_job_state_transition

insert into TB_JOB_STATE_TRANSITION (ID,PASSWORD, PREDECESSOR_ID, SUCCESSOR_ID, TRANSITION_DIRECTION, TRANSITION_TYPE) VALUES(6,null,5,6,'F','M');

insert into TB_JOB_STATE_TRANSITION (ID,PASSWORD, PREDECESSOR_ID, SUCCESSOR_ID, TRANSITION_DIRECTION, TRANSITION_TYPE) VALUES(7,null,6,7,'F','M');

insert into TB_JOB_STATE_TRANSITION (ID,PASSWORD, PREDECESSOR_ID, SUCCESSOR_ID, TRANSITION_DIRECTION, TRANSITION_TYPE) VALUES(8,null,7,8,'F','M');

insert into TB_JOB_STATE_TRANSITION (ID,PASSWORD, PREDECESSOR_ID, SUCCESSOR_ID, TRANSITION_DIRECTION, TRANSITION_TYPE) VALUES(9,null,8,9,'F','M');

insert into TB_JOB_STATE_TRANSITION (ID,PASSWORD, PREDECESSOR_ID, SUCCESSOR_ID, TRANSITION_DIRECTION, TRANSITION_TYPE) VALUES(10,null,9,1,'F','M');

--insert new job state transitions delete into table tb_job_state_transition

insert into TB_JOB_STATE_TRANSITION (ID,PASSWORD, PREDECESSOR_ID, SUCCESSOR_ID, TRANSITION_DIRECTION, TRANSITION_TYPE) VALUES(12,null,5,4,'F','D');

insert into TB_JOB_STATE_TRANSITION (ID,PASSWORD, PREDECESSOR_ID, SUCCESSOR_ID, TRANSITION_DIRECTION, TRANSITION_TYPE) VALUES(13,null,6,4,'F','D');

insert into TB_JOB_STATE_TRANSITION (ID,PASSWORD, PREDECESSOR_ID, SUCCESSOR_ID, TRANSITION_DIRECTION, TRANSITION_TYPE) VALUES(14,null,7,4,'F','D');

insert into TB_JOB_STATE_TRANSITION (ID,PASSWORD, PREDECESSOR_ID, SUCCESSOR_ID, TRANSITION_DIRECTION, TRANSITION_TYPE) VALUES(15,null,8,4,'F','D');

insert into TB_JOB_STATE_TRANSITION (ID,PASSWORD, PREDECESSOR_ID, SUCCESSOR_ID, TRANSITION_DIRECTION, TRANSITION_TYPE) VALUES(16,null,9,4,'F','D');

insert into TB_JOB_STATE_TRANSITION (ID,PASSWORD, PREDECESSOR_ID, SUCCESSOR_ID, TRANSITION_DIRECTION, TRANSITION_TYPE) VALUES(22,null,1,4,'F','D');

--insert new job state transitions backwards into table tb_job_state_transition

insert into TB_JOB_STATE_TRANSITION (ID,PASSWORD, PREDECESSOR_ID, SUCCESSOR_ID, TRANSITION_DIRECTION, TRANSITION_TYPE) VALUES(17,null,6,5,'B','N');

insert into TB_JOB_STATE_TRANSITION (ID,PASSWORD, PREDECESSOR_ID, SUCCESSOR_ID, TRANSITION_DIRECTION, TRANSITION_TYPE) VALUES(18,null,7,6,'B','N');

insert into TB_JOB_STATE_TRANSITION (ID,PASSWORD, PREDECESSOR_ID, SUCCESSOR_ID, TRANSITION_DIRECTION, TRANSITION_TYPE) VALUES(19,null,8,7,'B','N');

insert into TB_JOB_STATE_TRANSITION (ID,PASSWORD, PREDECESSOR_ID, SUCCESSOR_ID, TRANSITION_DIRECTION, TRANSITION_TYPE) VALUES(20,null,9,8,'B','N');

insert into TB_JOB_STATE_TRANSITION (ID,PASSWORD, PREDECESSOR_ID, SUCCESSOR_ID, TRANSITION_DIRECTION, TRANSITION_TYPE) VALUES(21,null,1,9,'B','N');

commit;

Result Job States

Result Job State Transitions

Map3D Industry Model : SQL Query Logical Topology Connection & Jobs

SQL Query

select topo.fid, topo.FID_FROM, topo.F_CLASS_ID_FROM,

dic2.F_CLASS_NAME CLASSNAME_FROM, topo.FID_TO, topo.F_CLASS_ID_TO, dic.F_CLASS_NAME CLASSNAME_TO,  topo.FLOW,
j.id, j.NAME, j.COMMENTARY,
jv.STATE
from 
RNT_NODE_EDGE_TOPO_CONN topo, TB_DICTIONARY dic, TB_DICTIONARY dic2, TB_JOB j, TB_JOB_VERSION jv
where dic.F_CLASS_ID = topo.F_CLASS_ID_TO
and dic2.F_CLASS_ID = topo.F_CLASS_ID_FROM
and j.id = jv.JOB_ID
and jv.JOB_VERSION = topo.JOB_VERSION;



flow 

• (1,forward) 
• (2,backward) 
• (3, forward and backward)

state

select * from tb_job_state;

MAP3D (Topobase) can't split linestring due to duplicate points

Apparently if you look at the geometry using the API (feature) the collection of the linestring has double starting points and double endpoints.

A validation on oracle doesn't detect the problem in the collection of the linestring.

Performing a spatial function, either rectify or a simplify, on the geometry will solve the problem.

select * from tb_job where name = 'Aansluiting 67 G split';
exec job3.setjob(45);
select * from in_geo_segment where FID = 43141;
update in_geo_segment set geom = SDO_UTIL.SIMPLIFY(geom,0.01,0.005) where fid = 43141;
commit;

select * from tb_job where name = 'Aansluiting 108 G split';
exec job3.setjob(48);
select * from in_geo_segment where FID = 46655;
update in_geo_segment set geom = SDO_UTIL.SIMPLIFY(geom,0.01,0.005) where fid = 46655;
update in_geo_segment set geom = SDO_UTIL.SIMPLIFY(geom,0.01,0.005) where fid = 46651;
update in_geo_segment set geom = SDO_UTIL.SIMPLIFY(geom,0.01,0.005) where fid = 47169;
commit;

Brute Oracle Uninstall

If you, like me, run into a situation where you are unable to uninstall Oracle products, in my case the client and database,  then this post might be able to provide you a little guidance.

Before you use this method plz use the deinstall method from Oracle first or download the Uninstall tools for your particular version of client and database provided on the Oracle web site!!

Only use this appraoch as a last resort to remove an oracle[client,database] install from the PC if other uninstall methods provided by Oracle fail or the Universal installer doesn't get the job done.

1. Uninstall all Oracle components using the Oracle Universal Installer (OUI).
2. Run regedit.exe and delete the HKEY_LOCAL_MACHINE/SOFTWARE/ORACLE key. This contains registry entries for all Oracle products.
3. Delete any references to Oracle services left behind in the following part of the registry (HKEY_LOCAL_MACHINE/SYSTEM/CurrentControlSet/Services/Ora*). It should be pretty obvious which ones relate to Oracle.
4. Reboot your machine.
5. Delete the "C:\Oracle" directory, or whatever directory is your ORACLE_BASE.
6. Delete the "C:\Program Files\Oracle" directory.
7. Empty the contents of your "C:\temp" directory.
8. Empty your recycle bin.
9. Remove any path variable from your environment system variables.
10. Remove ddl references from GAC(Make sure you remove them with Full Admin rights)

If all went well you can reinstall Oracle products correctly or you can descide to use other db software products who are easier to uninstall *grin*.

Stumbled upon a youtube movie of someone showing how to uninstall 11gr2 and 12c

DataAccess In Action Oracle

Complete Project can be downloaded

This is a small demonstration of  the project DataAccess in action.
The project used can be found in a previous post "DataAccess In Action"

Database used is the HR database of the Oracle 11g R2 x64 install.
Programming environment => MSVS 2010 C#.NET

Obviously, you need a correctly configured datastore to use this assembly. As mentioned in a previous post there are different types of datastores that can be used. Some datastores might have some limitation but most methods should work perfectly.

I will use a console application in C# to demonstrate the methods in the DataAccess class.

1. Create a new console application in c#.
2. Add a reference to the DataAccess assembly.
3. Setup and define your connection with the database.
4. List DataProviders recognized on the machine.
5. List all employees in the HR database shipped with Oracle 11gR2.
6. List details of the View EMP_DETAILS_VIEW.
7. Execute a stored procedure from the HR database.
8. Execute a select/view with parameters

1. Console Application

I don't think this needs a lot of explaining. You could do the same in a WPF or WinForms environment.

2. Add a reference to the assembly

Now this one might be a little tricky. Make sure your console application is targetting the correct solution platform. As I run this in a x64 environment I had to change the console its target framework to a any CPU solution. You can configure this in the Configuration manager of your c# solution.

3. Database Connection

This is a connection with a local Oracle 11gR2 Express database.  You could easely adapt this to connect to a SQLServer database providing the correct connectionstring and dataprovider.

static void InitializeConnection()

        {

            const string connectionString = "User Id = HR; Password = admin; Data Source = DB11R2";

            _dataAccess = new DataAccess.DataAccess("Oracle.DataAccess.Client", connectionString);

        }

4. List DataProviders

If you want to know what dataproviders are elegible to use as parameter in the constructor of the DataAccess class you could use the following method.

static void ListDataProviders()

        {

            //The result is a DataTable holding all available dataproviders recognized on the machine

            DataTable dataTable = DbProviderFactories.GetFactoryClasses();

            //Loop through all results in the datatable and write them to the console

            foreach (DataRow row in dataTable.Rows)

            {

                Console.WriteLine(row[2].ToString());

            }

        }

Console Output

5. List all employees

Doesn't need any explanation. Straightforward method to get all employees in the HR database and write them to the console. Just for sake of presentation I only send the Last_Name and First_Name column to the console.

static void ListEmployees()

        {

            //Get a command from the factory matching your dataprovider

            IDbCommand command = _dataAccess.DbProviderFactory.CreateCommand();

            if (command != null)

            {

                //Create your sql commandtext

                command.CommandText = "Select * from Employees";

                //Loop through the result and display it in the console

                foreach (DataRow row in _dataAccess.GetDataTable(command).Rows)

                {

                    string lastName = row["LAST_NAME"].ToString();

                    string firstName = row["FIRST_NAME"].ToString();

                    Console.WriteLine(lastName + " " + firstName);

                }

            }

        }

Console Output

6. List some details from the EMP_DETAILS_VIEW view.

The same as previous method I only took a few columns from the result View and send them to the console.

static void ListDetailEmpoyeesView()

        {

            //Get a command from the factory matching your dataprovider

            IDbCommand command = _dataAccess.DbProviderFactory.CreateCommand();

            if (command != null)

            {

                //Assign the type of command. Text is default so not really nessecary.

                command.CommandType = CommandType.Text;

                //Create your sql commandtext

                command.CommandText = "Select * from EMP_DETAILS_VIEW";

                //Loop through the result and display it in the console

                foreach (DataRow row in _dataAccess.GetDataTable(command).Rows)

                {

                    string lastName = row["LAST_NAME"].ToString();

                    string firstName = row["FIRST_NAME"].ToString();

                    string jobTitle = row["JOB_TITLE"].ToString();

                    string depName = row["DEPARTMENT_NAME"].ToString();

                    Console.WriteLine(lastName + " " + firstName + " " + depName + " " + jobTitle);

                }

            }

        }

Console Output

7. Execute stored procedure

The stored procedure used in this example doesn't make use of parameters. The DataAccess class does provide a method where you can pass on a list of IN or OUT  DbParameters .

The stored procedure will throw an exception when working on the database outside office hours. This means you wont see anything written in the console during office hours.

static void ExecuteStoredProcedure()

        {

            IDbCommand command = _dataAccess.DbProviderFactory.CreateCommand();

            if (command != null)

            {

                command.CommandType = CommandType.StoredProcedure;

                try

                {

                    _dataAccess.ExecuteStoredProcedure("SECURE_DML");

                }

                catch (Exception ex)

                {

                    Console.WriteLine(ex.Message);

                }

            }

        }

8.  Execute a select/view with parameters

This select will fetch all employees of a particular department.

static void ExecuteSelectWithParam()

        {

            IDbCommand dbCommand = _dataAccess.DbProviderFactory.CreateCommand();

            if (dbCommand != null)

            {

                dbCommand.CommandType = CommandType.Text;

                dbCommand.CommandText = "Select LAST_NAME, FIRST_NAME FROM EMPLOYEES WHERE DEPARTMENT_ID = " + ":DEPARTMENT_ID";

                DbParameter dbParameter = _dataAccess.DbProviderFactory.CreateParameter();

                if (dbParameter != null)

                {

                    dbParameter.DbType = DbType.Int32;

                    dbParameter.Value = 100;

                    dbParameter.ParameterName = ":DEPARTMENT_ID";

                    dbCommand.Parameters.Add(dbParameter);

                }

            }

            //Loop through the result and display it in the console

            foreach (DataRow row in _dataAccess.GetDataTable(dbCommand).Rows)

            {

                string lastName = row["LAST_NAME"].ToString();

                string firstName = row["FIRST_NAME"].ToString();

                Console.WriteLine(lastName + " " + firstName);

            }

        }

Complete Code Sample

using System;

using System.Data.Common;

using System.Data;

namespace Testing

{

    class Program

    {

        private static DataAccess.DataAccess _dataAccess;

        static void Main(string[] args)

        {

            InitializeConnection();

            ListDataProviders();

            Console.WriteLine();

            ListEmployees();

            Console.WriteLine();

            ListDetailEmpoyeesView();

            Console.WriteLine();

            ExecuteStoredProcedure();

            Console.WriteLine();

            ExecuteSelectWithParam();

            Console.ReadLine();

        }

        static void ListDataProviders()

        {

            //The result is a DataTable holding all available dataproviders recognized on the machine

            DataTable dataTable = DbProviderFactories.GetFactoryClasses();

            //Loop through all results in the datatable and write them to the console

            foreach (DataRow row in dataTable.Rows)

            {

                Console.WriteLine(row[2].ToString());

            }

        }

        static void InitializeConnection()

        {

            const string connectionString = "User Id = HR; Password = admin; Data Source = DB11R2";

            _dataAccess = new DataAccess.DataAccess("Oracle.DataAccess.Client", connectionString);

        }

        static void ListEmployees()

        {

            //Get a command from the factory matching your dataprovider

            IDbCommand command = _dataAccess.DbProviderFactory.CreateCommand();

            if (command != null)

            {

                //Create your sql commandtext

                command.CommandText = "Select * from Employees";

                //Loop through the result and display it in the console

                foreach (DataRow row in _dataAccess.GetDataTable(command).Rows)

                {

                    string lastName = row["LAST_NAME"].ToString();

                    string firstName = row["FIRST_NAME"].ToString();

                    Console.WriteLine(lastName + " " + firstName);

                }

            }

        }

        static void ListDetailEmpoyeesView()

        {

            //Get a command from the factory matching your dataprovider

            IDbCommand command = _dataAccess.DbProviderFactory.CreateCommand();

            if (command != null)

            {

                //Assign the type of command. Text is default so not really nessecary.

                command.CommandType = CommandType.Text;

                //Create your sql commandtext

                command.CommandText = "Select * from EMP_DETAILS_VIEW";

                //Loop through the result and display it in the console

                foreach (DataRow row in _dataAccess.GetDataTable(command).Rows)

                {

                    string lastName = row["LAST_NAME"].ToString();

                    string firstName = row["FIRST_NAME"].ToString();

                    string jobTitle = row["JOB_TITLE"].ToString();

                    string depName = row["DEPARTMENT_NAME"].ToString();

                    Console.WriteLine(lastName + " " + firstName + " " + depName + " " + jobTitle);

                }

            }

        }

        static void ExecuteStoredProcedure()

        {

            IDbCommand command = _dataAccess.DbProviderFactory.CreateCommand();

            if (command != null)

            {

                command.CommandType = CommandType.StoredProcedure;

                try

                {

                    _dataAccess.ExecuteStoredProcedure("SECURE_DML");

                }

                catch (Exception ex)

                {

                    Console.WriteLine(ex.Message);

                }

            }

        }

    }

}

static void ExecuteSelectWithParam()

        {

            IDbCommand dbCommand = _dataAccess.DbProviderFactory.CreateCommand();

            if (dbCommand != null)

            {

                dbCommand.CommandType = CommandType.Text;

                dbCommand.CommandText = "Select LAST_NAME, FIRST_NAME FROM EMPLOYEES WHERE DEPARTMENT_ID = " + ":DEPARTMENT_ID";

                DbParameter dbParameter = _dataAccess.DbProviderFactory.CreateParameter();

                if (dbParameter != null)

                {

                    dbParameter.DbType = DbType.Int32;

                    dbParameter.Value = 100;

                    dbParameter.ParameterName = ":DEPARTMENT_ID";

                    dbCommand.Parameters.Add(dbParameter);

                }

            }

            //Loop through the result and display it in the console

            foreach (DataRow row in _dataAccess.GetDataTable(dbCommand).Rows)

            {

                string lastName = row["LAST_NAME"].ToString();

                string firstName = row["FIRST_NAME"].ToString();

                Console.WriteLine(lastName + " " + firstName);

            }

        }

DataAccess in C#

While I was running through the ODP.NET samples on Connection I thought of implementing DbProviderFactory in a previous project I made.

This class has been made to use with different types of DataProviders.

A few datastores that are eligible:

•         SQLServer
•         Oracle
•          MySql
•          Access
•          Excell
•          XML
•         ....

I use this class in all my projects from now on. Although it might contain some minor bugs still, I am pretty happy with it so far. I placed it on github where you can download the project.

Next post I will put this baby into action. 

I'll execute some code on the HR database shipped with a Oracle 11g R2 x64 Express install.

Most likely I ll make a video and post the code with it.

Enjoy!!

Class Diagram

IDataAccess Interface

using System.Data;

using System.Data.Common;

using System;

using System.Collections.Generic;

namespace DataAccess

{

    public interface IDataAccess : IDisposable

    {

        IDbConnection DbConnection { get; }

        string DataProvider { get; }

        DbProviderFactory DbProviderFactory { get; }

      

        bool CheckConnection();

       

        DataSet GetDataSet(IDbCommand command);

       

        DataTable GetDataTable(IDbCommand command);

        DataRow GetDataRow(IDbCommand command);

        int ExecuteNonQuery(IDbCommand command);

        void BeginTransaction();

        void BeginTransaction(IsolationLevel isolationLevel);

        void CommitTransaction();

        void RollBackTransaction();

        DataTable ExecuteStoredProcedure(string spName);

        DataTable ExecuteStoredProcedure(string spName, IList<DbParameter> inParameters,

                                                IList<DbParameter> outParameters, DbParameter returnValue);

    }

}

DataAccess class

using System;

using System.Data;

 using System.Transactions;

 using System.Collections.Generic;

using System.Data.Common;

namespace DataAccess

{

    public class DataAccess : IDataAccess

    {

        #region Variables

        /// <summary>

        /// Private readonly variable Connection of type IDbConnection. Holding all neccesary information about the connection.

        /// </summary>

        private readonly IDbConnection _connection;

        /// <summary>

        /// Private readonly variable DataProvider. Exposing information about the DataProvider.

        /// </summary>

        private readonly string _dataProvider;

        /// <summary>

        /// Private readonly variable Transaction of type IDbTransaction. holding information about the Transaction object.

        /// </summary>

        private IDbTransaction _transaction;

        private readonly DbProviderFactory _dbProviderFactory;

        #endregion

        #region Public Properties

        /// <summary>

        /// Public Property Connection of type IDbConnection. Holding all neccesary information about the connection.

        /// </summary>

        public IDbConnection DbConnection

        {

            get

            {

                return _connection;

            }

        }

        /// <summary>

        /// Name of the Data Provider

        /// </summary>

        public string DataProvider

        {

            get { return _dataProvider; }

        }

        /// <summary>

        /// DbProvider factory linked to the DataProvider

        /// </summary>

        public DbProviderFactory DbProviderFactory

        {

            get { return _dbProviderFactory; }

        }

        #endregion

        #region Constructors

        /// <summary>

        /// Overloaded Class Constructor.

        /// </summary>

        /// <param name="dataProvider">name of the dataprovider</param>

        /// <param name="connectionString">The connectionstring used for the dataprovider</param>

        public DataAccess(string dataProvider, string connectionString)

        {

            if (String.IsNullOrEmpty(connectionString))

                throw new ArgumentNullException("connectionString");

            if (String.IsNullOrEmpty(dataProvider))

                throw new ArgumentNullException("dataProvider");

            _dataProvider = dataProvider;

            //get the correct DbFactory providing the DB dataprovider

            _dbProviderFactory = DbProviderFactories.GetFactory(dataProvider);

            //Create a connection

            _connection = _dbProviderFactory.CreateConnection();

            //Set the connections connectionstring

            if (_connection != null)

                _connection.ConnectionString = connectionString;

        }

        #endregion

        #region Public Methods

        /// <summary>

        /// Check if you can make a connection with the database.

        /// </summary>

        /// <returns>Returns true if a connection is possible.</returns>

        public bool CheckConnection()

        {

            try

            {

                _connection.Open();

                _connection.Close();

                return true;

            }

            catch

            {

                return false;

            }

        }

        /// <summary>

        /// Generic method used to excecute a IDbCommand statement without parameters.

        /// </summary>

        /// <param name="command">Command statement of type IDbCommand</param>

        /// <returns>Returns the number of rows affected.</returns>

        public int ExecuteNonQuery(IDbCommand command)

        {

            int result;

            using (command)

            {

                _connection.Open();

                command.Connection = _connection;

                try

                {

                    //Using transactionscopes doesnt work too well on oracle databases.

                    using (TransactionScope scope = new TransactionScope())

                    {

                        result = command.ExecuteNonQuery();

                        scope.Complete();

                    }

                }

                finally

                {

                    _connection.Close();

                }

            }

            return result;

        }

        /// <summary>

        /// Start a transaction on the connection

        /// </summary>

        public void BeginTransaction()

        {

            if(_transaction == null)

                _transaction = DbConnection.BeginTransaction();

        }

        /// <summary>

        /// Begin a tracsaction on the connection by defining its isolationlevel

        /// </summary>

        /// <param name="isolationLevel"></param>

        public void BeginTransaction(System.Data.IsolationLevel isolationLevel)

        {

            if (_transaction == null)

                _transaction = DbConnection.BeginTransaction(isolationLevel);

        }

        /// <summary>

        /// Commit the transaction on the connection

        /// </summary>

        public void CommitTransaction()

        {

            if(_transaction != null)

            {

                _transaction.Commit();

                _transaction.Dispose();  

            }

        }

        /// <summary>

        /// Rollback transaction on the connection

        /// </summary>

        public void RollBackTransaction()

        {

            if(_transaction != null)

            {

                _transaction.Rollback();

                _transaction.Dispose();  

            }

        }

        #region Read Data

        /// <summary>

        /// Generic method that allows you to retrieve data from your datastore in a dataset.

        /// </summary>

        /// <param name="command">Command statement of type IDbCommand</param>

        /// <returns>A dataset with values as result of the command</returns>

        public DataSet GetDataSet(IDbCommand command)

        {

            DataSet ds = new DataSet();

            _connection.Open();

            using (command)

            {

                command.Connection = _connection;

                IDbDataAdapter da = _dbProviderFactory.CreateDataAdapter();

                if (da != null)

                {

                    da.SelectCommand = command;

                    try

                    {

                        da.Fill(ds);

                    }

                    finally

                    {

                        _connection.Close();

                    }

                }

            }

            return ds;

        }

        /// <summary>

        /// Generic method that allows you to retrieve data from your datastore in a datatable.

        /// </summary>

        /// <param name="command">Command statement of type IDbCommand</param>

        /// <returns></returns>

        public DataTable GetDataTable(IDbCommand command)

        {

            return GetDataSet(command).Tables[0];

        }

        /// <summary>

        /// Generic method that allows you to retrieve data from your datastore in a datarow.

        /// </summary>

        /// <param name="command">Command statement of type IDbCommand</param>

        /// <returns></returns>

        public DataRow GetDataRow(IDbCommand command)

        {

            DataRow dr = null;

            DataTable dt = GetDataTable(command);

            if (dt.Rows.Count > 0)

            {

                dr = dt.Rows[0];

            }

            return dr;

        }

        #endregion

        #region Implementing IDisposable

        /// <summary>

        /// Disposing our connection object.

        /// </summary>

        public void Dispose()

        {

            _connection.Dispose();

        }

        #endregion

        /// <summary>

        /// Public method to execute a stored procedure on the database.

        /// </summary>

        /// <param name="spName">Name of the stored procedure of the type string</param>

        /// <returns>A DataTable containing data returned by the stored procedure</returns>

        public DataTable ExecuteStoredProcedure(string spName)

        {

            return ExecuteStoredProcedure(spName, null, null, null);

        }

        /// <summary>

        ///  Public method to execute a stored procedure on the database providing parameters

        /// </summary>

        /// <param name="spName">Parameter of type String.Name of the stored procedure.</param>

        /// <param name="inParameters">List of In Parameters of type DbParameter</param>

        /// <param name="outParameters">List of Our Parameters of type DbParameter</param>

        /// <param name="returnValue"></param>

        /// <returns>Return a datatable with whatever returns from the database.</returns>

        public DataTable ExecuteStoredProcedure(string spName, IList<DbParameter> inParameters, IList<DbParameter> outParameters, DbParameter returnValue)

        {

            var command = _dbProviderFactory.CreateCommand();

            if (command != null)

            {

                _connection.Open();

                command.Connection = (DbConnection)DbConnection;

                command.CommandType = CommandType.StoredProcedure;

                command.CommandText = spName;

                if (returnValue != null)

                {

                    returnValue.Direction = ParameterDirection.ReturnValue;

                    command.Parameters.Add(returnValue);

                }

                if (inParameters != null)

                {

                    foreach (var parameter in inParameters)

                    {

                        parameter.Direction = ParameterDirection.Input;

                        command.Parameters.Add(parameter);

                    }

                }

                if (outParameters != null)

                {

                    foreach (var parameter in outParameters)

                    {

                        parameter.Direction = ParameterDirection.Output;

                        command.Parameters.Add(parameter);

                    }

                }

            }

            //Does returning this datatable actually work every time?

            DataTable dataTable = null;

            try

            {

                if (command != null)

                {

                    using (IDataReader dataReader = command.ExecuteReader())

                    {

                        dataTable = new DataTable();

                        dataTable.Load(dataReader);

                    }

                }

            }

            finally

            {

                _connection.Close();

            }

            return dataTable;

        }

        #endregion

        #region Public Static Methods

        // This example assumes a reference to System.Data.Common.

        public static DataTable GetProviderFactoryClasses()

        {

            // Retrieve the installed providers and factories.

            return DbProviderFactories.GetFactoryClasses();

        }

        #endregion

    }

}