Get Access tables using C#

Assalamu Alaykum..
Here’s an application I has made which connects to access database using C# and get table names and column names.This is a screen shot from the application:

1

How to do that ?

First, you should add reference [Microsoft.Office.interop.Access]

1

Add these ‘using’ statements:


using Microsoft.Office.Interop.Access;
using System.Data.Common;
using System.Data.OleDb;

Declare these variables:  Access.Application object and DbProviderFactory object..


//Variables..
Microsoft.Office.Interop.Access.Application AccessObj = null;
DbProviderFactory factory = DbProviderFactories.GetFactory("System.Data.OleDb");
string ConnectionString;

public Form1()
{
     InitializeComponent();
     //---
     //Create a new acess object..
     AccessObj = new Microsoft.Office.Interop.Access.Application();
}

Write the implementation of GoAccess button click event, which gets table names from the database:


//Get table names from database..
        private void btnGoAccess_Click(object sender, EventArgs e)
        {
            try
            {
                listBox1.Items.Clear();
                listBox2.Items.Clear();

                //Create ConnectionString to Access Database through it's path
                ConnectionString = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" + txtProjectPath.Text;
                //Datatable which will carry User tables
                DataTable userTables = null;

                //Create new connection
                DbConnection connection = factory.CreateConnection();

                // c:\test\test.mdb
                connection.ConnectionString = ConnectionString;

                // We want user tables only, not system tables
                string[] restrictions = new string[4];
                restrictions[3] = "Table";

                //Open connection..
                connection.Open();

                // Get list of user tables
                userTables = connection.GetSchema("Tables", restrictions);

                //Close connection
                connection.Close();

                // Add list of table names to listBox
                for (int i = 0; i < userTables.Rows.Count; i++)
                {
                    listBox1.Items.Add(userTables.Rows[i][2].ToString());
                }
            }
            catch (Exception ex)
            {
                MessageBox.Show(ex.Message);
            }
        }

Using the factory object to create a database connection and specify its connection string with Provider = OLEDB and Data Source = your database path.. Then open the connection and get schema of type Tables and restrictions[3] determines that you want user tables only and system tables will be not included. By iterating on userTables datatable rows, display rows[i][2] which carries the table name.

Now, we will write the implementation of GetColumns button click event, which gets column names from the selected table:

 //Get column names from selected table..
        private void btnGetColumns_Click(object sender, EventArgs e)
        {
            if (listBox1.SelectedItem != null)
            {
                try
                {
                    bool PK = false;
                    listBox2.Items.Clear();

                    //Create ConnectionString and open the connection..
                    ConnectionString = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" + txtProjectPath.Text;
                    OleDbConnection _connection = new OleDbConnection(ConnectionString);
                    _connection.Open();

                    //Return primary key tables..
                    DataTable PKTable = _connection.GetOleDbSchemaTable(OleDbSchemaGuid.Primary_Keys, new object[] { null, null, listBox1.SelectedItem.ToString() });

                    //Create OleDbDataReader and OleDbCommand to return all data from selected table..
                    OleDbDataReader reader;
                    OleDbCommand cmd = new OleDbCommand("SELECT * FROM " + listBox1.SelectedItem.ToString(), _connection);
                    reader = cmd.ExecuteReader();

                    //Create schemaTable
                    DataTable schemaTable = reader.GetSchemaTable();
                    for (int i = 0; i < schemaTable.Rows.Count; i++)
                    {
                        PK = false;
                        for (int j = 0; j < PKTable.Rows.Count; j++)
                        {
                              if (schemaTable.Rows[i][0].ToString() == PKTable.Rows[j][3].ToString())
                              {
                                    PK = true;
                                    break;
                              }
                        }
                        // schemaTable.Rows[i][0] --> Column Name..
                        if (PK) { listBox2.Items.Add(schemaTable.Rows[i][0].ToString() + ": PrimaryKey"); }
                        else { listBox2.Items.Add(schemaTable.Rows[i][0].ToString()); }
                    }

                    //Close connection
                    _connection.Close();
                }
                catch (Exception ex)
                {
                    MessageBox.Show(ex.Message);
                }
            }
        }

In the previous code snippet, we declare an OleDbConnection to the desired database, OleDbCommand and OleDbDataReader.. Then we initiates cmd object with constructor which takes command text as its parameter “SELECT * FROM Table Name” : Table Name is the selected item from the first list box, Which will return all table data,  But we want only the schema table [which contains column names, column data typea, ..]. Then declare PKTable Data table which contains all primary keys by calling the function GetOleDbSchemaTable.

Finally, iterate on schema table and check if it’s exist in PKTable, then it is a primary key. Then close the Connection .. It’s pretty easy!

Download Source Code

Advertisements

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

%d bloggers like this: