Compact Access 2007 Database using JRO / DAO

In one of my winform project, we are using Sql Server as well as Microsoft Access 97 as Database. And since size of MS Access DB file grows, at some point of time we Compact the Access Database file through coding by .Net. Today, I am sharing my experience on using different techniques to compact Access DB file using .Net.

We are using Microsoft Jet and Replication Objects 2.6 Library (JRO) to compact Access 2007 Database. But during last week we found two problems in using JRO:

1. JRO was NOT working on 64 bit Computer. I tested on Windows Server 2008 R2 and it fail to Load. (JRO might work on 64 bit if you have MS Office installed)
2. When JRO compacts DB (on XP 32bit), database file created by compaction is of Access 2002 - 2003 File format. So if your Original file is of 2007 Format then after compaction by JRO, DB File is converted to 2002 - 2003 File format.

For solving above 2 problems, I have then used DAO to compact database, which is more accurate and also worked on 64bit Server.

Here, I am giving steps to implement Compaction using JRO and DAO both.

Steps to compact Access DB using JRO:

App.config file:
<?xml version="1.0"?>
<configuration>
<appSettings>
<add key="SourceDB" value="Provider=Microsoft.ACE.OLEDB.12.0;Data Source=D:\Test\Test2007.accdb;Jet OLEDB:Engine Type=5"/>
<add key="DestDB" value="Provider=Microsoft.ACE.OLEDB.12.0;Data Source=D:\Test\Test2007BK.accdb;Jet OLEDB:Engine Type=5"/>
</appSettings>
</configuration>


1. Create a winform application and add a reference of Jet and Replication Objects 2.6 Library. See the diagram below:

2. Add form to solution and add below code in form Load event:

string SrcDBName = ConfigurationManager.AppSettings["SourceDB"];
string DestDBName = ConfigurationManager.AppSettings["DestDB"];

        int ReturnCode = 0;
        JRO.JetEngine objJRO = null;
        try
        {
            objJRO = new JRO.JetEngine();
            objJRO.CompactDatabase(SrcDBName, DestDBName);
        }
        catch (Exception ex)
        {
            StackTrace STrace = new StackTrace(ex, true);
            StackFrame StkFrame = STrace.GetFrame(STrace.FrameCount - 1);
            string Disp_Msg = "Message:\t" + ex.Message + Environment.NewLine;
            Disp_Msg += "Error Date:\t" + DateTime.Now.ToString("dddd, MMM d yyyy HH:mm:ss");

            File.AppendAllText(Path.GetDirectoryName(Application.ExecutablePath) + @"\CompactErr.txt", Disp_Msg + Environment.NewLine + "Stack Trace:\t" + ex.StackTrace + Environment.NewLine + Environment.NewLine);
        }
        finally
        {
            Marshal.ReleaseComObject(objJRO);
            objJRO = null;
        }

3. Most Important: change the Target Platform to x86 (From Project property window -> Click on Build tab and then change Platform Target to x86.

4. Run the application and will create new compacted Access DB file. But as I mention earlier its format will always be 2002-2003 format.

Steps using DAO:

1. Create a winform application and Add a reference of DAO - Microsoft Office 12.0 Access Database Engine Object Library, and the DLL is ACEDAO.DLL. See the diagram below:

2. Add form to solution and add below code in form Load event:

using AccInterop = Microsoft.Office.Interop.Access;

AccInterop.Dao.DBEngine objDBEngine = null;

string SrcDBName = ConfigurationManager.AppSettings["SourceDB"];
string DestDBName = ConfigurationManager.AppSettings["DestDB"];
objDBEngine = new AccInterop.Dao.DBEngine();
objDBEngine.CompactDatabase(SrcDBName, DestDBName);

3. You can keep target platform to "Any CPU" or x86. If you keep Any CPU then you need to install MS Access Database Engine 2010 - x64 version. And if you keep x86 then you need to install MS Access Database Engine 2010 - x86 version.

4. Run the application and will create new compacted Access DB file. Format of compacted file will be same as Original file format.

5. we need to install 2 components for DAO to work correctly.

5.1. Microsoft Access Database Engine 2010 (x86 or x64).
Link from MSDN to download: http://www.microsoft.com/download/en/details.aspx?id=13255

5.2. Microsoft Office 2010: Primary Interop Assemblies.
Link from MSDN to download: http://www.microsoft.com/download/en/details.aspx?id=3508

If you have any queries, you can write comments and I will be glad to reply you.

Happy Programming !!!

Background Worker Example - Filling dataset in separate thread - Part 2

In my previous article [BackgroundWorker Component Overview in Winforms - Part 1], I described the working of BackgroundWorker component in Winform Application. In this article, I will demonstrate how to use the BackgroundWorker component to run a time-consuming operation on a separate thread. Example fills the dataset in background thread and also shows progress-bar while method is running.

First I created a class which inherits from BackgroundWorker Class which contains following key elements:

1. An Enum ActionType : which contains Enum of Actions to do in background. i.e. FillCustData, DownloadCustImgs, etc will be used in method OnDoWork
2. A public variable DoWorkArgs of type Dictionary<String, Object> for passing Arguments in key, value pair to worker method.
3. A public class ResultData which contains properties like ResultObject, MsgText, PassedArgs etc to pass information from worker thread to UI layer. So UI layer can know what happened during background and take action based on Result.
4. A protected override void OnDoWork (DoWorkEventArgs e) method - a key method which is executed during in separate thread.
5. A winform with Button to start process in background and progressbar component to show progress while method is executing in background.

Here is the code for custom APKWorker Class which inherits from BackgroundWorker Class:

using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Data;
using System.ComponentModel;

namespace TemplateApp
{
    public class APKWorker : BackgroundWorker
    {
        private ActionType ActionName { get; set; }
        private DataTable dtResult = null;
        public Dictionary<String, Object> DoWorkArgs = new Dictionary<string, object>();
       
        public APKWorker()
        {
            this.WorkerReportsProgress = true;
            this.WorkerSupportsCancellation = false;
        }

        public APKWorker(ActionType ActionName) : this()
        {
            this.ActionName = ActionName;
        }

        protected override void OnDoWork(DoWorkEventArgs e)
        {
            switch (this.ActionName)
            {
                case ActionType.RegisterUser:
                    //Call to method RegisterUser()
                    break;
                case ActionType.Insert_Usage_Statistic:
                    //Call to method Insert_Usage_Statistic()
                    break;
                case ActionType.FillUsers:

                    int deptid = 0;
                    if (DoWorkArgs.Keys.Contains("deptid"))
                        deptid = (int)DoWorkArgs["deptid"];

                    dtResult = new DataTable();
                    dtResult.Columns.Add("UID", Type.GetType("System.Int32"));
                    dtResult.Columns.Add("UserName", Type.GetType("System.String"));
                    DataRow dr = null;
                    this.ReportProgress(30, "Started");
                    System.Threading.Thread.Sleep(2000);
                    for (int i = 0; i < 5; i++)
                    {
                        dr = dtResult.NewRow();
                        dr["UID"] = i;
                        dr["UserName"] = "Test" + i.ToString();
                        dtResult.Rows.Add(dr);
                    }
                    this.ReportProgress(80, "Data Filled");
                    System.Threading.Thread.Sleep(2000);
                    e.Result = new ResultData { PassedArgs = DoWorkArgs, MsgText = "Data Filled.", MsgType = ResultData.MessageType.Success, ResultObject = dtResult };
                    this.ReportProgress(100, "Completed");
                    break;
            }
            base.OnDoWork(e);
        }

        public enum ActionType
        {
            RegisterUser,
            Insert_Usage_Statistic,
            FillUsers
        }
    }

    public class ResultData
    {
        public Object ResultObject { get; set; }
        public string MsgText { get; set; }
        public MessageType MsgType { get; set; }
        public Dictionary<String, Object> PassedArgs { get; set; }

        public enum MessageType
        {
            Success,
            Failure
        }       
    }

}

Code to write in Winform for Calling background worker class:

        private void btnTest_Click(object sender, EventArgs e)
        {
            this.progressBar1.Minimum = 0;
            this.progressBar1.Maximum = 100;
            APKWorker objWorker = new APKWorker(APKWorker.ActionType.FillUsers);
            objWorker.DoWorkArgs.Add("userid", 10);
            objWorker.DoWorkArgs.Add("deptid", 3);
            objWorker.RunWorkerCompleted += new RunWorkerCompletedEventHandler(objWorker_RunWorkerCompleted);
            objWorker.ProgressChanged += new ProgressChangedEventHandler(objWorker_ProgressChanged);
            objWorker.RunWorkerAsync();

        //Implementation Example 2:
        //APKWorker objWorker = new APKWorker();
        //objWorker.DoWork += new DoWorkEventHandler(objWorker_DoWork);
        //objWorker.RunWorkerAsync();
        //void objWorker_DoWork(object sender, DoWorkEventArgs e)
        //{
        //    MessageBox.Show("hello");
        //}
        //Above example is useful, if you need to implement some custom logic in DoWork method. Here, first don't pass Actionname from Constructor,
        //then add the 2nd line - objWorker.DoWork += new DoWorkEventHandler(objWorker_DoWork); and write custom logic in objWorker_DoWork

        }       

        void objWorker_ProgressChanged(object sender, ProgressChangedEventArgs e)
        {
            this.progressBar1.Value = e.ProgressPercentage;
            this.lblMsg.Text = (string)e.UserState;
        }

        void objWorker_RunWorkerCompleted(object sender, RunWorkerCompletedEventArgs e)
        {
            ResultData ResultObj = (ResultData)e.Result;
            DataTable dtUser = (DataTable)ResultObj.ResultObject;
            MessageBox.Show(ResultObj.PassedArgs["deptid"].ToString());
            MessageBox.Show(dtUser.Rows[2]["UserName"].ToString());
            MessageBox.Show("Done");
        }

Hope this example will help to all who wants to use Background worker class to run long running methods in separate thread. Feel free to post your comments.

Background Worker Overview with Example in Winforms - Part 1

Recently, while working on Winform Application, we require certain time consuming tasks on Form load to run in separate Thread. We decided to use Background Worker Component in our application. However, there is need that Background Component will be used in multiple forms. So I decided to create a class which inherits from BackgroundWorker Class and implemented certain common functionality over there. I like to share the code and overview of Background worker class.

BackgroundWorker Component Overview:

The BackgroundWorker class allows you to run an operation on a separate, dedicated thread. Time-consuming operations like

•     Image downloads
•     Web service invocations
•     File downloads and uploads (including for peer-to-peer applications)
•     Complex local computations
•     Database transactions
•     Local disk access

can cause your user interface to hang while they are running. When you want a responsive UI and you are faced with long delays associated with such operations, the BackgroundWorker component provides a convenient solution.

The BackgroundWorker component gives you the ability to execute time-consuming operations asynchronously ("in the background"), on a thread different from your application's main UI thread.

To use a BackgroundWorker, you simply tell it what time-consuming worker method to execute in the background, and then you call the RunWorkerAsync method. Your calling thread continues to run normally while the worker method runs asynchronously. When the method is finished, the BackgroundWorker alerts the calling thread by firing the RunWorkerCompleted event, which optionally contains the results of the operation.

The BackgroundWorker component is available from the Toolbox, in the Components tab. To add a BackgroundWorker to your form, drag the BackgroundWorker component onto your form. It appears in the component tray, and its properties appear in the Properties window.

By using a pattern of method calls (RunWorkerAsync, ReportProgress) and event callbacks (DoWork, ProgressChanged, RunWorkerCompleted), BackgroundWorker helps you easily create a background thread and factor out the stuff that should be running on the background thread versus the foreground thread. 

If your background operation requires a parameter, call RunWorkerAsync with your parameter. Inside the DoWork event handler, you can extract the parameter from the DoWorkEventArgs.Argument property. 

Below image shows the flow of working of Background Worker model.

Above is the brief overview of how Background worker class works and what are its key events and methods. In the next article [Background worker example - Filling dataset in separate thread - Part 2], I explained working of Background worker class with code, which fills the Dataset in background and also shows the progress-bar while running the process in background.

Simple Tips for Healthy Living for Software Engineers

Simple tips for healthy living for Software Engineers

This tips are specially for Software Engineers who usually sits for 8-10 hours a day in front of computers solving complex problems of world and have no time to take care of health. One can minimize ill effects of stress and stay healthy by following a few simple tips as below.

1. Get adequate sleep:

Remember that not getting enough sleep will do you harm in the long run. you will feel drowsy during the day, unable to concentrate on simple tasks and feel irritable. Try to get at least seven hours of sleep every day. Avoid keeping Televisions, Computer and gadgets in your bedroom, don’t eat large meals before bedtime, follow regular bedtimes and wake times. Ensure your bedroom is quite and relaxing.

2. Drink Hot Water:

While it may take a little while to get used to having hot water; it will greatly benefit you. It purifies your body and helps remove unwanted properties as well. Your digestive system will work smoothly and also clear out your skin.

3. Spend lesser time on Social Networking Sites:

Researchers say that an increasingly high number of people prefer to spend time surfing the internet and spending time on social networking sites instead of pursuing a hobby. Ask yourself how much time you spend on networking sites every week and whether it is worth it. Opt for a hobby, which will give you a chance to enjoy, learn something new and meet new people.

4. Exercise:

You don’t need to pump iron six days a week in a gym to stay in shape. A brisk walk for 30 minutes every day in your neighborhood will do you a whole lot of good. Do a few simple stretches and exercises every alternate day at home or alternatively start doing yoga and meditation. Walking, cycling, swimming or even dancing are great stress busters and also keep you in shape.

5. Quit Smoking:

If you have finally made up your mind to kick this habit, it’s really good for your health. To quit successfully you need encouragement and support from your doctor, family, friends, and coworkers. A doctor will help you tailor an approach that suits you best.

6. Follow a healthy diet:

Try eating a whole grain sandwich before your big meal, eat smaller portions of high calorie dishes, ask yourself if you’re an emotional eater, and avoid reaching out for snacks while watching TV. Include more vegetables and fruits in your daily diet. If you're trying to loose weight, don't starve yourself.

7. Blink your eyes at regular interval:

Follow the 20-20-20 rule. After every 20 minutes of work, look 20 feet away from the computer screen and blink 20 times. Also lubricate your eyes at regular interval to avoid dryness of eyes. Use lubricating eye drops in both the eyes before every session on the computer. Consult your eye surgeon before using any eye drops.

Multiple Active Result Sets (MARS) in SQL Server

Before few days, one of my colleague came with one issue - he was getting error while opening multiple DataReader with single Connection object i.e. he is trying to open 2nd datareader with same connection object in Loop. And he got error - There is already an open DataReader associated with this Command which must be closed first. I search and get way to open multiple DataReader connections with SQLServer and like to share with all.

Sql Server supports MARS (Multiple Active Result Sets) and we need to set MultipleActiveResultSets=true in connection string. Below is Connection string syntax and Code to demonstrate opening datareader in while loop (multiple datareader). MARS is supported from SQL Server 2005 Version.

Connection String:

<add name ="DBConnstr" connectionString ="Data Source=xxx.yyy.zzz.aa;Initial Catalog=name;User Id=id;Integrated Security=False;Password=pwd;MultipleActiveResultSets=true"  providerName ="System.Data.SqlClient" />

Code:

string strConn = ConfigurationManager.ConnectionStrings["DBConnstr"].ConnectionString;
string strSql = "select * From OrderDetail where OrderID = {0}";
string strOutput = "OrdNumber: {0} - Desc: {1}";

using (SqlConnection con = new SqlConnection(strConn))
{
 //Opening Connection   
 con.Open();

 //Creating two commands form current connection   
 SqlCommand cmd1 = con.CreateCommand();
 SqlCommand cmd2 = con.CreateCommand();

 //Set the comment type   
 cmd1.CommandType = CommandType.Text;
 cmd2.CommandType = CommandType.Text;

 //Setting the command text to first command   
 cmd1.CommandText = "select OrderID From [OrderMaster]";

 //Execute the first command   
 IDataReader idr1 = cmd1.ExecuteReader();
 while (idr1.Read())
 {
  //Read the first Tree value from data source       
  int intTree = Convert.ToInt32(idr1.GetInt16(0));

  //create another command, which get values based on Tree value       
  cmd2.CommandText = string.Format(strSql, intTree);

  //Execute the reader       
  IDataReader idr2 = cmd2.ExecuteReader(); // If MARS is not enable in Conn string ; error will come at this line.
  while (idr2.Read())
  {
   //Read the values from Reader 2
   Console.WriteLine(string.Format(strOutput, idr2.GetString(1), idr2.GetString(4)));
  }
  //Dont forgot to close second reader, this will just close reader not connection       
  idr2.Close();
 }
 idr1.Close();
}