What is the best way to connect to a local database that will be used by two apps?

Question

I'm writting an application in C# that connects to a database that is used by other application. I'm coding the class that access the database like this:

class conexionBD
{
    string connString;

    protected void miConexion(string ruta)
    {
        connString = String.Concat("Provider=Microsoft.Jet.OLEDB.4.0;Data Source=", ruta);
    }

    protected DataTable misEmpleados()
    {            
        string query = "SELECT Fiel1, Field2 FROM Table1";
        DataTable dTable = miDatatable(query);
        return dTable;
    }    

    protected DataColumn misDptos()
    {
        DataTable dTable = miDatatable("SELECT OtherField from OtherTable");
        return dTable.Columns[0];       
    }

    private DataTable miDatatable(string sqlQuery)
    {
        OleDbDataAdapter dAdapter = new OleDbDataAdapter(sqlQuery, connString);
        OleDbCommandBuilder cBuilder = new OleDbCommandBuilder(dAdapter);
        DataTable dTable = new DataTable();
        dAdapter.Fill(dTable);
        return dTable;        
    }    
}

The app firts calls the method "miConexion" to set the path to the database in the hard disc. Then the app connects and disconnects each time I want to get the data stored in "Table1" and "OtherTable".

The database is likely to be accessed and modified by both apps at the same time. "Connecting and disconnecting" is the best way to access the database in this case?

The responses to this question may provide you some insight: http://stackoverflow.com/questions/50303/persistent-db-connections-yea-or-nay — Bob Kaufman, Jan 03 '12 at 20:48

score 1 · Answer 1 · edited Jan 03 '12 at 20:52

1

there are 3 improvements you can make:

abstract the specific database adapter to the configuration file.
dispose of ado.net objects when you are finished with them
use transaction when reading and writing to/from the db.

edited Jan 03 '12 at 20:52

Murph

9,985
2
26
41

answered Jan 03 '12 at 20:47

Jason Meckley

7,589
1
24
45

score 0 · Accepted Answer · answered Jan 03 '12 at 20:45

The database is likely to be accessed and modified by both apps at the same time.

Then you would need to use transactions.

Also check that you open and close connection and the connection remains open for the shortest possible time. OleDb stack is really old if this is a new application consider moving to ORM or at least ADO.NET.

score 0 · Answer 3 · answered Jan 03 '12 at 21:16

Use an ORM like Entity Framework (EF) or NHibernate to manage the state of the object (if both applications are .Net)

I would recommend NHibernate as you are using OLE data adapter (but see if there are any connectors for the database you are using for EF as it is far easier to setup)..

score 0 · Answer 4 · answered Jan 04 '12 at 17:17

This is the modification of the method "miDatatable", it connects to an Access Database:

private DataTable miDatatable(string sqlQuery)
        {
            using (OleDbConnection connDB = new OleDbConnection(connString))
            {
                OleDbDataAdapter dAdapter;
                OleDbCommandBuilder cBuilder;
                OleDbCommand command = new OleDbCommand();
                DataTable dTable = new DataTable();
                OleDbTransaction trans = null;

                try
                {
                    connDB.Open();
                    trans = connDB.BeginTransaction(IsolationLevel.ReadCommitted);

                    command.Connection = connDB;
                    command.Transaction = trans;
                    command.CommandText = sqlQuery;

                    dAdapter = new OleDbDataAdapter(sqlQuery, connDB);
                    cBuilder = new OleDbCommandBuilder(dAdapter);

                    dAdapter.SelectCommand.Transaction = trans;
                    dAdapter.Fill(dTable);
                    trans.Commit();
                }

                catch 
                {
                    try
                    {
                        trans.Rollback();
                    }
                    catch { }
                }

                return dTable;
            }
        }

What is the best way to connect to a local database that will be used by two apps?

4 Answers4