none
DBEngineClass doesn't release database file. RRS feed

  • Question

  • Hi.

    I'm using DAO as COM object in C# to create JET Database. But DBEngineClass doesn' release database file. Even if I release COM object by hands.

    using dao;
    ...
    private void Do()
    {
      createDatabase();
      con = new System.Data.OleDb.OleDbConnection("Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" + dbfPath);
      con.Open(); //EXCEPTION HERE: A first chance exception of type 'System.Data.OleDb.OleDbException' occurred in System.Data.dll
    //Additional information: File already in use.
    }
    private void createDatabase()
    {
      DBEngineClass engine = new DBEngineClass();
      engine.CreateDatabase(dbfPath, dao.LanguageConstants.dbLangGeneral, null);
      releaseObject(engine);
    }         
    private void releaseObject(object obj)
    {
      try{
        System.Runtime.InteropServices.Marshal.ReleaseComObject(obj);
      obj = null;
      }
      catch{
        obj = null;
      }
      finally{
      GC.Collect();
      }
    }
    
    
    Process explorer also shows me that file in use.

    Any ideas, please.

     

    Friday, July 23, 2010 9:06 AM

Answers

  • CreateDatabase should return a reference to a DAO Database object. I would suspect that since you haven't set a variable to this class instance the reference is implicit and remains in memory because it's never destroyed or closed.

    I would set a variable object to this reference. You might need to close the Database object but if not you will have an object reference that you can supply to ReleaseComObject.

     


    Paul ~~~~ Microsoft MVP (Visual Basic)
    Friday, July 23, 2010 2:18 PM
  • I wouldn't recommend relying on DAO's DBEngine to create an Access database from C#.  Here are two ways I'd recommend instead.

    While it still relies on COM interop, you can use ADOX from C#.  Here's a snippet of code that uses the new ACE OLE DB provider, an updated version of the old Jet OLE DB provider that's now available in 64-bit.  You could use the same approach with the older Jet OLE DB provider as well.

     

    string path = @".\MyNewJetDB.mdb";
    if (File.Exists(path)) File.Delete(path);
    
    string provider = "Microsoft.ACE.OLEDB.12.0";
    
    OleDbConnectionStringBuilder builder = new OleDbConnectionStringBuilder();
    builder.Provider = provider;
    builder.DataSource = path;
    string connectionString = builder.ConnectionString;
    
    ADOX.Catalog catalog = new ADOX.Catalog();
    catalog.Create(connectionString);
    ((ADODB.Connection)catalog.ActiveConnection).Close();
    catalog.ActiveConnection = null;
    
    OleDbConnection connection = new OleDbConnection(connectionString);
    connection.Open();
    

     

    There's another standard approach for this problem that doesn't involve having to rely on the database engine at all - good old file copy.  I've spoken with a number of developers in the past who would have a blank or template database as part of their project and then copy the file in this type of scenario.  There's a slightly less clunky variation on this - using an embedded resource.  Create an empty or templated database ahead of time and embed the file as a resource in your project.  Then, you can access the resource via code to create the file at run-time.  Here's some code I put together to demonstrate the approach.  Keep in mind I'm no expert with the Stream classes.  There may be a better way to write the contents of the resource to a new file, but this code worked for me.

    //Create file via embedded resource
    Assembly asm = Assembly.GetExecutingAssembly();
    string resourceName = "CreateJetDatabase.MyNewJetDB.mdb";
    //If you're not sure of the exact resource names, 
    //check asm.GetManifestResourceNames()
    using (Stream resourceStream = 
        asm.GetManifestResourceStream(resourceName))
    {
        int bufferSize = 64 * 1024;
        byte[] buffer = new byte[bufferSize];
        int bytesRead = -1;
        using (FileStream fileStream =
            new FileStream(path, FileMode.CreateNew, FileAccess.Write))
        {
            do
            {
                bytesRead = resourceStream.Read(buffer, 0, bufferSize);
                if (bytesRead == 0) break;
                fileStream.Write(buffer, 0, bytesRead);
            } while (bytesRead == bufferSize);
            fileStream.Flush();
        }
    }
    

    I hope this information proves helpful.


    David Sceppa
    Friday, July 23, 2010 7:31 PM
    Moderator

All replies

  • CreateDatabase should return a reference to a DAO Database object. I would suspect that since you haven't set a variable to this class instance the reference is implicit and remains in memory because it's never destroyed or closed.

    I would set a variable object to this reference. You might need to close the Database object but if not you will have an object reference that you can supply to ReleaseComObject.

     


    Paul ~~~~ Microsoft MVP (Visual Basic)
    Friday, July 23, 2010 2:18 PM
  • I wouldn't recommend relying on DAO's DBEngine to create an Access database from C#.  Here are two ways I'd recommend instead.

    While it still relies on COM interop, you can use ADOX from C#.  Here's a snippet of code that uses the new ACE OLE DB provider, an updated version of the old Jet OLE DB provider that's now available in 64-bit.  You could use the same approach with the older Jet OLE DB provider as well.

     

    string path = @".\MyNewJetDB.mdb";
    if (File.Exists(path)) File.Delete(path);
    
    string provider = "Microsoft.ACE.OLEDB.12.0";
    
    OleDbConnectionStringBuilder builder = new OleDbConnectionStringBuilder();
    builder.Provider = provider;
    builder.DataSource = path;
    string connectionString = builder.ConnectionString;
    
    ADOX.Catalog catalog = new ADOX.Catalog();
    catalog.Create(connectionString);
    ((ADODB.Connection)catalog.ActiveConnection).Close();
    catalog.ActiveConnection = null;
    
    OleDbConnection connection = new OleDbConnection(connectionString);
    connection.Open();
    

     

    There's another standard approach for this problem that doesn't involve having to rely on the database engine at all - good old file copy.  I've spoken with a number of developers in the past who would have a blank or template database as part of their project and then copy the file in this type of scenario.  There's a slightly less clunky variation on this - using an embedded resource.  Create an empty or templated database ahead of time and embed the file as a resource in your project.  Then, you can access the resource via code to create the file at run-time.  Here's some code I put together to demonstrate the approach.  Keep in mind I'm no expert with the Stream classes.  There may be a better way to write the contents of the resource to a new file, but this code worked for me.

    //Create file via embedded resource
    Assembly asm = Assembly.GetExecutingAssembly();
    string resourceName = "CreateJetDatabase.MyNewJetDB.mdb";
    //If you're not sure of the exact resource names, 
    //check asm.GetManifestResourceNames()
    using (Stream resourceStream = 
        asm.GetManifestResourceStream(resourceName))
    {
        int bufferSize = 64 * 1024;
        byte[] buffer = new byte[bufferSize];
        int bytesRead = -1;
        using (FileStream fileStream =
            new FileStream(path, FileMode.CreateNew, FileAccess.Write))
        {
            do
            {
                bytesRead = resourceStream.Read(buffer, 0, bufferSize);
                if (bytesRead == 0) break;
                fileStream.Write(buffer, 0, bytesRead);
            } while (bytesRead == bufferSize);
            fileStream.Flush();
        }
    }
    

    I hope this information proves helpful.


    David Sceppa
    Friday, July 23, 2010 7:31 PM
    Moderator