Wednesday, August 29, 2012

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);
            }
        }

Posted by at 11:06 PM 1 comments
Labels: , , , , , , , , , , , ,

Saturday, August 25, 2012

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
    }
}
Posted by at 11:07 AM 2 comments
Labels: , , , , , , , , , , , ,
Subscribe to: Posts (Atom)