Sunday, June 30, 2013

DAO 3.6 - Repair and Compact Database

There is something I came across a while back when migrating an old VB6 application to .NET. Everything was fine but had some roadblocks with DAO in VB6. Typically in VB6 all the data objects are created using DAO and migrating it to ADO.NET is simple except one area. There is a functionality of RepairDatabase which is not present in the ADO.NET. I left that as it is and to do that imported Interop.DAO.dll (3.51 version) in new .NET application. RepairDatabase method is not present in DAO 3.6 onward, will discuss it later.

The issue faced, even though it is working well in development environment but in production environment it is throwing me error.
System.Runtime.InteropServices.COMException (0x80040112): Creating an instance of the COM component with CLSID {00000010-0000-0010-8000-00AA006D2EA4} from the IClassFactory failed due to the following error: 80040112.

Googled and got confused, nothing specific and nothing conclusive. So, started gathering information from different sources and tried to get to some conclusion...

What is DAO?
(Data Access Objects) was the first object-oriented interface that exposed the Microsoft Jet database engine (used by Microsoft Access) and allowed Visual Basic developers to directly connect to Access tables - as well as other databases - through ODBC. DAO is suited best for either single-system applications or for small, local deployments. Here is a good post to help you out.

Reason for this error is Data Access Objects (DAO) is not properly registered. But when you try to register using regsvr32, DAO 3.51 it fails as there is no entry point. but thats not an issue with DAO 3.6. There are some major difference between 3.5 and 3.6. The later has been totally revamped, this is by design to match Microsoft Jet 4.0.

As per the Microsoft recommendation
RDO and ADO can still be used in code from Visual Basic 2008, with some trivial modifications. However, Visual Basic 2008 does not support DAO and RDO data binding to controls, data controls, or RDO User connection. We recommend that if your applications contain DAO or RDO data binding you either leave them in Visual Basic 6.0 or upgrade the DAO or RDO data binding to ADO before upgrading your project to Visual Basic 2008, as ADO data binding is supported in Windows Forms. Information on how to upgrade DAO or RDO to ADO in Visual Basic 6.0 is available in the Visual Basic 6.0 Help.

Here is a surprise...
In Data Access Object (DAO) 3.6, the RepairDatabase method is no longer available or supported. This is by design to match Microsoft Jet 4.0. If you need this functionality, you can use the CompactDatabase method, which also repairs a Microsoft Jet database. Registering DAO 3.6 dll has no issue. Compacting a Jet/ACE database first detects if there are any problems in need of repair and if there are none, it skips the repair step and just compacts the file (rewriting data and index pages in contiguous data files and discarding unused data pages and updating all statistics and internal pointers, etc.).

In JRO if you compact a Access 97 database it will convert to Access 2000 format because even though JRO can read the Access 97 file it no longer support it and it is by design.

Following research implementation has two sections one Repairing and Compacting database using DAO and JRO. You may need to include the DAO 3.6 dlls in your application folder.
For that Add Following COM reference:
  • Microsoft DAO 3.6 Object Library
  • Microsoft Jet and Replication Objects 2.6 Library
namespace RepairDB
{
    class Program
    {
        static void Main(string[] args)
        {
            
            try
            {
                DAO.Database dd;
                DAO.DBEngine db = new DAO.DBEngine();
                dd = db.OpenDatabase(@"d:\KSDB1.mdb", null, null, ";pwd=KSTEST1");
                dd.Close();
                
            }
            catch (Exception e)
            {
                Console.WriteLine(e.Message);
            }
            Console.WriteLine("Open and Close of database successful using DAO.");
            try
            {
                File.Delete(@"d:\KSDB1_Tmp.mdb");
                if (File.Exists(@"d:\KSDB1.mdb"))
                {
                    DAO.DBEngine db = new DAO.DBEngine();
                    db.CompactDatabase(@"d:\KSDB1.mdb", @"d:\KSDB1_Tmp.mdb", null, null, ";pwd=KSTEST1");
                    File.Delete(@"d:\KSDB1.mdb");
                    File.Move(@"d:\KSDB1_Tmp.mdb", @"d:\KSDB1.mdb");
                }   
            }
            catch (Exception e)
            {
                Console.WriteLine(e.Message);
            }
            Console.WriteLine("Compacting and Repair database successful using DAO.");
            try
            {
                File.Delete(@"d:\KSDB1_Tmp.mdb");
                if (File.Exists(@"d:\KSDB1.mdb"))
                {
                    JRO.JetEngine jj = new JRO.JetEngine();
                    jj.CompactDatabase(@"Provider=Microsoft.Jet.OLEDB.4.0;Data Source=d:\KSDB1.mdb;Jet OLEDB:Database Password=KSTEST1",
                        @"Provider=Microsoft.Jet.OLEDB.4.0;Data Source=d:\KSDB1_Tmp.mdb;Jet OLEDB:Database Password=KSTEST1");
                    File.Delete(@"d:\KSDB1.mdb");
                    File.Move(@"d:\KSDB1_Tmp.mdb", @"d:\KSDB1.mdb");
                }
            }
            catch (Exception e)
            {
                Console.WriteLine(e.Message);
            }
            Console.WriteLine("Compacting and Repair database successful using JRO.");
            Console.ReadLine();
        }
    }
}

Furthur Readings
http://msdn.microsoft.com/en-us/library/office/aa164825(v=office.10).aspx
http://msdn.microsoft.com/en-us/library/aa984815(v=vs.71).aspx
http://msdn.microsoft.com/en-us/library/e80y5yhx(v=vs.110).aspx

Friday, June 7, 2013

Indexer Enumerator etc. etc. examples

It is so simple to work with an Array or List of string. But when it comes to use List of complex class objects it becomes nightmare. Some simply prefer to go on implementing long inefficient codes for it. At the same time due to lots of code and complexity quality and scalability impacted.

Lets now give a second thought to it and understand how can we implement it efficiently with minimal effort.

What is an Indexer?

Defining an indexer allows you to create classes that act like "virtual arrays." Instances of that class can be accessed using the [] array access operator. Defining an indexer in C# is similar to defining operator [] in C++, but is considerably more flexible. For classes that encapsulate array- or collection-like functionality, using an indexer allows the users of that class to use the array syntax to access the class. An indexer is often used to implement a stack so that its contents may be accessed without item removal.

An indexer's simple syntax helps client applications access element groups as an array object member (type, class, or struct). An indexer provides an indirect method of inserting boundary checking logic. Due to its intuitive nature, an indexer improves code readability.

Now, in the implementation below there are multiple implementations how we can use Indexer and Collection for simple implementation of complex class / struct objects. A Class is a reference type so the objects needed to created explicitly. Where as a struct in value type and there is no need to create object explicitly.

* First implementation
A simple Test1 class with string array variable and using Indexer exposing string array.

* Second implementation
Extending First implementation to make it two dimensional array.

* Third implementation
Implementation of array using struct, being value type implementation is as simple as siring array. Extending it is easy and so just a simple implementation.

* Fourth implementation
It is simple class array implementation but it not that simple. If you see after defining length of array each node object needed to be created. This is an overhead but if implemented in a better way will be more simpler and scalable.

* Fifth implementation
This is extension of previous implementation but this time created new list class using IEnumerable<> interface and implementing GetEnumerator. This way even though it is complicated for simple implementation but for complicated class object it is simple.

namespace Indexer
{
    class Program
    {
        static void Main(string[] args)
        {
            // First implementation
            // implementing class using indexer
            Console.WriteLine("implementing class using indexer");
            Test1 EE = new Test1();
            EE[0] = "this 1";
            EE[1] = "this 2";
            //foreach (var X1 in EE) cannot implement because it doesn't have public definition of GetNumerator
            Console.WriteLine("{0}", EE[0].ToString());
            Console.WriteLine("{0}", EE[1].ToString());

            // Second implementation
            // extending indexable class to build 2 dimentional array
            Console.WriteLine("extending indexable class to build 2 dimentional array");
            Test1[] XX = new Test1[2];
            XX[0] = new Test1();
            XX[1] = new Test1();
            XX[0][0] = "XXX";
            XX[0][1] = "YYY";
            XX[1][0] = "aaa";
            XX[1][1] = "bbb";
            foreach (var X1 in XX)
                Console.WriteLine(X1[0] + " ; " + X1[1]);

            // Third implementation
            // simple implementation of array of object using struct
            Console.WriteLine("simple implementation of array of abject using struct");
            objStruct[] ddd = new objStruct[2];
            ddd[0].iCnt = 1;
            ddd[0].sName = "name 1";
            ddd[1].iCnt = 2;
            ddd[1].sName = "name 2";
            foreach (var Xd in ddd)
                Console.WriteLine(Xd.iCnt.ToString() + " ; " + Xd.sName);
            
            // Fourth implementation
            // simple implementation of array of object using class
            Console.WriteLine("simple implementation of array of object using class");
            objClass[] sss = new objClass[2];
            sss[0] = new objClass();
            sss[1] = new objClass();
            sss[0].iCnt = 1;
            sss[0].sName = "name 1";
            sss[1].iCnt = 2;
            sss[1].sName = "name 2";
            foreach (var Xd in sss)
                Console.WriteLine(Xd.iCnt.ToString() + " ; " + Xd.sName);

            // Fifth implementation
            // extending class object to make it enumerable using IEnumerable interface
            Console.WriteLine("extending it enumerable using IEnumerable interface");
            objClassList ssX = new objClassList();
            ssX.Add(new objClass() { iCnt = 1, sName = "XXC" });
            ssX.Add(new objClass() { iCnt = 2, sName = "CCC" });
            ssX.Add(new objClass() { iCnt = 3, sName = "BBB" });
            foreach (var w in ssX)
                Console.WriteLine(w.iCnt + " # " + w.sName);
            Console.ReadLine();
        }
    }
    class Test1
    {
        private string[] names;
        public Test1()
        {
            this.names = new string[2];
        }
        public string this[int i]
        {
            get { return this.names[i]; }
            set { this.names[i] = value; }
        }
    }
    struct objStruct
    {
        public int iCnt;
        public string sName;
    }
    class objClass
    {
        public int iCnt;
        public string sName;

    }
    class objClassList : IEnumerable<objclass>
    {
        private List<objclass> objList = new List<objclass>();
        public int Count 
        { 
            get 
            { 
                return objList.Count; 
            } 
        }
        public objClass this[int index]
        {
            get
            {
                return objList[index];
            }
        }
        public void Add(objClass objX)
        {
            objList.Add(objX);
        }
        public void Remove(objClass objX)
        {
            objList.Remove(objX);
        }
        public IEnumerator<objclass> GetEnumerator()
        {
            return this.objList.GetEnumerator();
        }
        System.Collections.IEnumerator System.Collections.IEnumerable.GetEnumerator()
        {
            return this.GetEnumerator();
        }
    }
}

Console Output
implementing class using indexer                         
this 1                                                   
this 2                                                   
extending indexable class to build 2 dimentional array   
XXX ; YYY                                                
aaa ; bbb                                                
simple implementation of array of abject using struct    
1 ; name 1                                               
2 ; name 2                                               
simple implementation of array of object using class     
1 ; name 1                                               
2 ; name 2                                               
extending it enumerable using IEnumerable interface      
1 # XXC                                                  
2 # CCC                                                  
3 # BBB