Connecting OleDB using ADO.Net (C#)

Connecting OleDB in .Net is no different than connecting other database engines, you only need to use a Connection object, a Command object and then make the operations you were planning to do.

First of all, you should import the libraries we need for connecting OleDB, so if for connecting to SQL Server you use System.Data.SQLServer, now you will use System.Data.OleDB:

using System.Data.OleDb;

Now we’ll need a ConnectionString in which the connection details will be set, and which has a different structure for OleDB than for SQL Server.

Provider=Microsoft.Jet.OLEDB.4.0;Data Source=DataBaseFileSource;User Id=Admin;Password=”

And here we go:

        public static string srcDB = "c:\\development\databases\bd1.mdb";
        public static OleDbDataReader ExecuteReader(string cmdText){
            OleDbConnection conex = new OleDbConnection("Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" + srcDB + ";User Id=Admin;Password=''");
            conex.Open();
        }

As you can see, I’ve made a public property in case we want to connect to a different DB to establish were the database file is located. Then I’ve made a connection object and gave it the connection string with the needed data. Finally you only need to open the connection.

Once you have opened the connection with the database you will need a Command object to make operations, so we’ll make it using the Connection object:

        public static OleDbDataReader ExecuteReader(string cmdText){
            OleDbConnection conex = new OleDbConnection(connString);
            conex.Open();
            OleDbCommand cmd = conex.CreateCommand();
        }

That done, we can now, for example, call a method that will get some data from the database and will return it as a DataReader object, in this case, an OleDBDataReader since we are working with the OleDB library. For doing that we need to assign the query text to the command.

Also, we can establish a CommandBehavior to, for example, Close the connection after making the request when we call the method.

        public static OleDbDataReader ExecuteReader(string cmdText){
            OleDbConnection conex = new OleDbConnection(connString);
            conex.Open();
            OleDbCommand cmd = conex.CreateCommand();
            cmd.CommandText = "SELECT * FROM Table1";
            return cmd.ExecuteReader(CommandBehavior.CloseConnection);
        }

Finally, let’s write some control code and see it all together:

        using System.Data.OleDb;

        public static string srcDB = "c:\\bd1.mdb";
        public static OleDbDataReader ExecuteReader(string cmdText){
            OleDbConnection conex = new OleDbConnection("Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" + srcDB + ";User Id=Admin;Password=''");

            try
            {
                if (!System.IO.File.Exists(srcDB)) { throw new Exception("DB not found."); }

                conex.Open();
                OleDbCommand cmd = conex.CreateCommand();

                if (cmd.Equals(null)) { throw new ArgumentNullException("cmd"); }
                if (cmdText.Equals(null) || cmdText.Length.Equals(0)) { throw new ArgumentNullException("cmdText"); }

                cmd.CommandText = cmdText;
                return cmd.ExecuteReader(CommandBehavior.CloseConnection);
            }
            catch (InvalidCastException e)
            {
                throw (e);
            }
        }

Finally, it is extremely important that you never forget to close the connection. In this case we are using a DataReader that will be close once all the rows are read.