Special Class Of C# Series - Part Two - OLE DB (Extended Properties)

Introduction:

In this series of C# Special class, we are going to see the special feature available with the “OleDbConnection” class and its specialties.

What is OleDbConnection?

An OleDbConnection object represents a unique connection to a data source. With a client/server database system, it is equivalent to a network connection to the server. Depending on the functionality supported by the native OLE DB provider, some methods or properties of an OleDbConnection object may not be available, such as -

 public sealed class OleDbConnection : DbConnection, ICloneable, IDbConnection, IDisposable

Reference

https://msdn.microsoft.com/en-us/library/system.data.oledb.oledbconnection(v=vs.110).aspx

When to use OleDbConnection?

When the connection is established between C# application and the specified data source, SQL commands will execute with the help of the Connection Object and retrieve or manipulate the data in the database.

What is the group of OleDb?

The OleDb has a set of dependent classes which is used to build the connection with the external data sources and also, adapter classes are used to read the data from the source of data.

1. OleDbConnection
2. OleDbDataAdapter
3. OleDbSchemaGuid

Where to use this OleDbConnection – Extended Properties?

The Extended Properties help to identify the type of data sources and it will customize itself to read the data from the sources, say, for example, I am going to show you how to read the data from the EXCEL data sources.

The Extended Properties are responsible to specify the type of sources.

Microsoft.Jet.OLEDB.4.0; data source={0};Extended Properties=Excel 8.0

Sample Code

The oledb should need the supporting framework files to be included in the project.

using System;
using System.Data;
using System.Data.OleDb;

Connection String

This is the place which provides the exact location of the file available.

string connectionString = string.Format("provider=Microsoft.Jet.OLEDB.4.0; data source={0};Extended Properties=Excel 8.0;", fileName);

Sample Excel used

This is single excel file which has three sheets and different data in each of the sheets,

• Admin
• HR
• Account
  
  
  
  
  
  
  

Code Implementation

This part of the code snippet is used to define the Excel sheet names and then fetch data.

static string[] GetExcelSheetNames(string connectionString)
      {
          OleDbConnection con = null;
          DataTable dt = null;
          con = new OleDbConnection(connectionString);
          con.Open();
          dt = con.GetOleDbSchemaTable(OleDbSchemaGuid.Tables, null);

          if (dt == null)
          {
              return null;
          }

          String[] excelSheetNames = new String[dt.Rows.Count];
          int i = 0;

          foreach (DataRow row in dt.Rows)
          {
              excelSheetNames[i] = row["TABLE_NAME"].ToString();
              i++;
          }

          return excelSheetNames;
      }

Data Set manipulation

This part of code is responsible for manipulation of data and getting the data to assign to the appropriate data table and then add all the data tables to the data set.

static DataSet Parse(string fileName)
        {
            string connectionString = string.Format("provider=Microsoft.Jet.OLEDB.4.0; data source={0};Extended Properties=Excel 8.0;", fileName);

            DataSet data = new DataSet();

            foreach (var sheetName in GetExcelSheetNames(connectionString))
            {
                using (OleDbConnection con = new OleDbConnection(connectionString))
                {
                    var dataTable = new DataTable();
                    dataTable.TableName = Convert.ToString(sheetName).TrimEnd('$');
                    string query = string.Format("SELECT * FROM [{0}]", sheetName);
                    con.Open();
                    OleDbDataAdapter adapter = new OleDbDataAdapter(query, con);
                    adapter.Fill(dataTable);
                    data.Tables.Add(dataTable);
                }
            }

            return data;
        }

Full Code Implementation

using System;
using System.Data;
using System.Data.OleDb;

namespace ReadExcel
{
    class Program
    {
        static void Main(string[] args)
        {
            var ds = Parse(@"C:\Selenium\samp.xls");
        }

        static DataSet Parse(string fileName)
        {
            string connectionString = string.Format("provider=Microsoft.Jet.OLEDB.4.0; data source={0};Extended Properties=Excel 8.0;", fileName);

            DataSet data = new DataSet();

            foreach (var sheetName in GetExcelSheetNames(connectionString))
            {
                using (OleDbConnection con = new OleDbConnection(connectionString))
                {
                    var dataTable = new DataTable();
                    dataTable.TableName = Convert.ToString(sheetName).TrimEnd('$');
                    string query = string.Format("SELECT * FROM [{0}]", sheetName);
                    con.Open();
                    OleDbDataAdapter adapter = new OleDbDataAdapter(query, con);
                    adapter.Fill(dataTable);
                    data.Tables.Add(dataTable);
                }
            }

            return data;
        }

        static string[] GetExcelSheetNames(string connectionString)
        {
            OleDbConnection con = null;
            DataTable dt = null;
            con = new OleDbConnection(connectionString);
            con.Open();
            dt = con.GetOleDbSchemaTable(OleDbSchemaGuid.Tables, null);

            if (dt == null)
            {
                return null;
            }

            String[] excelSheetNames = new String[dt.Rows.Count];
            int i = 0;

            foreach (DataRow row in dt.Rows)
            {
                excelSheetNames[i] = row["TABLE_NAME"].ToString();
                i++;
            }

            return excelSheetNames;
        }


    }
}

Result of the Dataset

Account Table



Admin Table



HR Table



Conclusion

This is one of the features where the OLEDB classes are used and in most of the data source transactions and connections, this class plays a major role.

Post your questions in the comments.

Happy learning.