locked
Access - Data access class/helper RRS feed

  • Question

  • User-567435310 posted

    Hi,

    Can anyone recommend a data access class / helper class for Access databases, similar to the sqlhelper class found in the (Data Access Application Block) please?

    Thanks,

    Adrian

    Tuesday, March 15, 2011 10:42 AM

Answers

  • User-1199946673 posted

    I understand the problem

    Really?

    and I assume that this means I cannot use an Access database with the sqlhelper class

    Correct, but the solution is very simple. Do a find a replace on:

    SqlClient replace with OleDb
    Sql replace with OleDb

    And most likely you have a OleDbHelper Class that works....

    • Marked as answer by Anonymous Thursday, October 7, 2021 12:00 AM
    Wednesday, March 16, 2011 12:31 PM
  • User3866881 posted

    Try this(From a Chinese blog http://www.cnblogs.com/vaiyanzi/archive/2009/06/22/1507988.html)

    using System;
    using System.Collections;
    using System.Collections.Generic;
    using System.Text;
    using System.Data;
    using System.Data.Common;
    using System.Data.OleDb;

    namespace Common
    {
        public static class OleDbHelper
        {
            public const string ACCESS_CONNECTIONSTRING_TEMPLATE = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source={0};";
      
            private static Hashtable parmCache = Hashtable.Synchronized(new Hashtable());

            public static int ExecuteNonQuery(string connString, CommandType cmdType, string cmdText, params OleDbParameter[] cmdParms)
            {
                OleDbCommand cmd = new OleDbCommand();

                using (OleDbConnection conn = new OleDbConnection(connString))
                {
                    PrepareCommand(cmd, conn, null, cmdType, cmdText, cmdParms, ConnectionActionType.Open);
                    int val = cmd.ExecuteNonQuery();
                    cmd.Parameters.Clear();
                    return val;
                }
            }

            public static int ExecuteNonQuery(OleDbConnection conn, CommandType cmdType, string cmdText, params OleDbParameter[] cmdParms)
            {
                OleDbCommand cmd = new OleDbCommand();
                PrepareCommand(cmd, conn, null, cmdType, cmdText, cmdParms, ConnectionActionType.AutoDetection);
                int val = cmd.ExecuteNonQuery();
                cmd.Parameters.Clear();
                return val;
            }

            public static int ExecuteNonQuery(OleDbTransaction trans, CommandType cmdType, string cmdText, params OleDbParameter[] cmdParms)
            {
                OleDbCommand cmd = new OleDbCommand();
                PrepareCommand(cmd, trans.Connection, trans, cmdType, cmdText, cmdParms, ConnectionActionType.None);
                int val = cmd.ExecuteNonQuery();
                cmd.Parameters.Clear();
                return val;
            }

            public static OleDbDataReader ExecuteReader(string connString, CommandType cmdType, string cmdText, params OleDbParameter[] cmdParms)
            {
                OleDbCommand cmd = new OleDbCommand();
                OleDbConnection conn = new OleDbConnection(connString);
                try
                {
                    PrepareCommand(cmd, conn, null, cmdType, cmdText, cmdParms, ConnectionActionType.Open);
                    OleDbDataReader rdr = cmd.ExecuteReader();
                    cmd.Parameters.Clear();
                    return rdr;
                }
                catch
                {
                    conn.Close();
                    throw;
                }
            }

            public static OleDbDataReader ExecuteReader(OleDbConnection conn, CommandType cmdType, string cmdText, params OleDbParameter[] cmdParms)
            {
                OleDbCommand cmd = new OleDbCommand();
                try
                {
                    PrepareCommand(cmd, conn, null, cmdType, cmdText, cmdParms, ConnectionActionType.AutoDetection);
                    OleDbDataReader rdr = cmd.ExecuteReader();
                    cmd.Parameters.Clear();
                    return rdr;
                }
                catch
                {
                    conn.Close();
                    throw;
                }
            }

            public static object ExecuteScalar(string connString, CommandType cmdType, string cmdText, params OleDbParameter[] cmdParms)
            {
                OleDbCommand cmd = new OleDbCommand();
                using (OleDbConnection conn = new OleDbConnection(connString))
                {
                    PrepareCommand(cmd, conn, null, cmdType, cmdText, cmdParms, ConnectionActionType.Open);
                    object val = cmd.ExecuteScalar();
                    cmd.Parameters.Clear();
                    return val;
                }
            }

            public static object ExecuteScalar(OleDbConnection conn, CommandType cmdType, string cmdText, params OleDbParameter[] cmdParms)
            {
                OleDbCommand cmd = new OleDbCommand();
                PrepareCommand(cmd, conn, null, cmdType, cmdText, cmdParms, ConnectionActionType.AutoDetection);
                object val = cmd.ExecuteScalar();
                cmd.Parameters.Clear();
                return val;
            }


            public static DataSet ExecuteDataset(string connString, CommandType cmdType, string cmdText, params OleDbParameter[] cmdParms)
            {
                OleDbCommand cmd = new OleDbCommand();
                using (OleDbConnection conn = new OleDbConnection(connString))
                {
                    PrepareCommand(cmd, conn, null, cmdType, cmdText, cmdParms, ConnectionActionType.Open);
                    OleDbDataAdapter da = new OleDbDataAdapter(cmd);
                    DataSet ds = new DataSet();
                    da.Fill(ds);
                    cmd.Parameters.Clear();
                    return ds;
                }
            }


            public static DataSet ExecuteDataset(OleDbConnection conn, CommandType cmdType, string cmdText, params OleDbParameter[] cmdParms)
            {
                OleDbCommand cmd = new OleDbCommand();
                PrepareCommand(cmd, conn, null, cmdType, cmdText, cmdParms, ConnectionActionType.AutoDetection);
                OleDbDataAdapter da = new OleDbDataAdapter(cmd);
                DataSet ds = new DataSet();
                da.Fill(ds);
                cmd.Parameters.Clear();
                return ds;
            }


            public static void CacheParameters(string cacheKey, params OleDbParameter[] cmdParms)
            {
                parmCache[cacheKey] = cmdParms;
            }


            public static OleDbParameter[] GetCachedParameters(string cacheKey)
            {
                OleDbParameter[] cachedParms = (OleDbParameter[])parmCache[cacheKey];

                if (cachedParms == null)
                    return null;

                OleDbParameter[] clonedParms = new OleDbParameter[cachedParms.Length];
                for (int i = 0, j = cachedParms.Length; i < j; i++)
                    clonedParms[i] = (OleDbParameter)((ICloneable)cachedParms[i]).Clone();

                return clonedParms;
            }


            private static void PrepareCommand(OleDbCommand cmd, OleDbConnection conn, OleDbTransaction trans, CommandType cmdType, string cmdText, OleDbParameter[] cmdParms, ConnectionActionType connActionType)
            {
                if (connActionType == ConnectionActionType.Open)
                {
                    conn.Open();
                }
                else
                {
                    if (conn.State != ConnectionState.Open)
                        conn.Open();
                }

                cmd.Connection = conn;
                cmd.CommandText = cmdText;

                if (trans != null)
                    cmd.Transaction = trans;

                cmd.CommandType = cmdType;

                if (cmdParms != null)
                {
                    foreach (OleDbParameter parm in cmdParms)
                        cmd.Parameters.Add(parm);
                }
            }

            public static DataTable ExecutePager(string connString, int pageIndex, int pageSize, string fileds, string table, string where, string order, out int pageCount, out int recordCount, string id)
            {
                if (pageIndex < 1) pageIndex = 1;
                if (pageSize < 1) pageSize = 10;
                if (string.IsNullOrEmpty(fileds)) fileds = "*";
                if (string.IsNullOrEmpty(order)) order = "ID desc";
                using (OleDbConnection conn = new OleDbConnection(connString))
                {
                    string myVw = string.Format(" {0} ", table);
                    string sqlText = string.Format(" select count(0) as recordCount from {0} {1}", myVw, where);
                    OleDbCommand cmdCount = new OleDbCommand(sqlText, conn);
                    if (conn.State == ConnectionState.Closed)
                        conn.Open();
                    recordCount = Convert.ToInt32(cmdCount.ExecuteScalar());

                    if ((recordCount % pageSize) > 0)
                        pageCount = recordCount / pageSize + 1;
                    else
                        pageCount = recordCount / pageSize;
                    OleDbCommand cmdRecord;
                    if (pageIndex == 1)
                    {
                        cmdRecord = new OleDbCommand(string.Format("select top {0} {1} from {2} {3} order by {4} ", pageSize, fileds, myVw, where, order), conn);
                    }
                    else if (pageIndex > pageCount)
                    {
                        cmdRecord = new OleDbCommand(string.Format("select top {0} {1} from {2} {3} order by {4} ", pageSize, fileds, myVw, "where 1=2", order), conn);
                    }
                    else
                    {
                        int pageLowerBound = pageSize * pageIndex;
                        int pageUpperBound = pageLowerBound - pageSize;
                        string recordIDs = RecordID(string.Format("select top {0} {1} from {2} {3} order by {4} ", pageLowerBound, id, myVw, where, order), pageUpperBound, conn);
                        cmdRecord = new OleDbCommand(string.Format("select {0} from {1} where {4} in ({2}) order by {3} ", fileds, myVw, recordIDs, order, id), conn);

                    }
                    OleDbDataAdapter dataAdapter = new OleDbDataAdapter(cmdRecord);
                    DataTable dt = new DataTable();
                    dataAdapter.Fill(dt);
                    return dt;
                }
            }

            private static string RecordID(string query, int passCount, OleDbConnection conn)
            {
                OleDbCommand cmd = new OleDbCommand(query, conn);
                string result = string.Empty;
                using (IDataReader dr = cmd.ExecuteReader())
                {
                    while (dr.Read())
                    {
                        if (passCount < 1)
                        {
                            result += "," + dr.GetInt32(0);
                        }
                        passCount--;
                    }
                }
                return result.Substring(1);
            }

            enum ConnectionActionType
            {
                None = 0,
                AutoDetection = 1,
                Open = 2
            }
        }
    }

    • Marked as answer by Anonymous Thursday, October 7, 2021 12:00 AM
    Thursday, March 17, 2011 2:04 AM

All replies

  • User371383779 posted

    If you already have DataAccess Application block(DAL) then you can use it to connect and retrieve data from a MS Access data base. Actually OLEDB type of provider is used to connect to file based data bases like Access. For a connection string to connect to Access you may visit the link below.

    http://www.connectionstrings.com/access

    Tuesday, March 15, 2011 1:27 PM
  • User-567435310 posted

    Thanks for your reply.

    When I use the sqlhelper class with an access database I get the following error message when using the ExecuteDataset Function

    Keyword not supported: 'provider'.

    Here is my connection string to my database.

    <connectionStrings>
        <add name="ConnStr" connectionString="Provider=Microsoft.Jet.OLEDB.4.0;Data Source=|DataDirectory|\Data.mdb;" providerName="System.Data.OleDb"/>
      </connectionStrings>

    My database is stored in my AppData folder.

    Do you have any suggestion or am I doing something wrong?

    Thanks.

     

    Wednesday, March 16, 2011 5:09 AM
  • User371383779 posted

    You will have to use OleDbCommand and OLEDBConnection object instead of using SQLCommand and SQLConnection objects. So if you are using DAL then you should specify which of SQL or OLEDB type of objects be created.

    Hope you understood the problem. Please visit the link which already discussed the problem you have encountered.

    http://forums.asp.net/t/979962.aspx/1?Keyword+not+supported+provider+

    Wednesday, March 16, 2011 5:47 AM
  • User-567435310 posted

    Thanks for the quick reply.

    I understand the problem and I assume that this means I cannot use an Access database with the sqlhelper class as part of my DAL. since the sqlhelper class uses sqlConnection objects throughout?

    I don't really want to have a datasource object within my page as this defeats the point of having a DAL.

    Wednesday, March 16, 2011 7:35 AM
  • User-1199946673 posted

    I understand the problem

    Really?

    and I assume that this means I cannot use an Access database with the sqlhelper class

    Correct, but the solution is very simple. Do a find a replace on:

    SqlClient replace with OleDb
    Sql replace with OleDb

    And most likely you have a OleDbHelper Class that works....

    • Marked as answer by Anonymous Thursday, October 7, 2021 12:00 AM
    Wednesday, March 16, 2011 12:31 PM
  • User3866881 posted

    Try this(From a Chinese blog http://www.cnblogs.com/vaiyanzi/archive/2009/06/22/1507988.html)

    using System;
    using System.Collections;
    using System.Collections.Generic;
    using System.Text;
    using System.Data;
    using System.Data.Common;
    using System.Data.OleDb;

    namespace Common
    {
        public static class OleDbHelper
        {
            public const string ACCESS_CONNECTIONSTRING_TEMPLATE = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source={0};";
      
            private static Hashtable parmCache = Hashtable.Synchronized(new Hashtable());

            public static int ExecuteNonQuery(string connString, CommandType cmdType, string cmdText, params OleDbParameter[] cmdParms)
            {
                OleDbCommand cmd = new OleDbCommand();

                using (OleDbConnection conn = new OleDbConnection(connString))
                {
                    PrepareCommand(cmd, conn, null, cmdType, cmdText, cmdParms, ConnectionActionType.Open);
                    int val = cmd.ExecuteNonQuery();
                    cmd.Parameters.Clear();
                    return val;
                }
            }

            public static int ExecuteNonQuery(OleDbConnection conn, CommandType cmdType, string cmdText, params OleDbParameter[] cmdParms)
            {
                OleDbCommand cmd = new OleDbCommand();
                PrepareCommand(cmd, conn, null, cmdType, cmdText, cmdParms, ConnectionActionType.AutoDetection);
                int val = cmd.ExecuteNonQuery();
                cmd.Parameters.Clear();
                return val;
            }

            public static int ExecuteNonQuery(OleDbTransaction trans, CommandType cmdType, string cmdText, params OleDbParameter[] cmdParms)
            {
                OleDbCommand cmd = new OleDbCommand();
                PrepareCommand(cmd, trans.Connection, trans, cmdType, cmdText, cmdParms, ConnectionActionType.None);
                int val = cmd.ExecuteNonQuery();
                cmd.Parameters.Clear();
                return val;
            }

            public static OleDbDataReader ExecuteReader(string connString, CommandType cmdType, string cmdText, params OleDbParameter[] cmdParms)
            {
                OleDbCommand cmd = new OleDbCommand();
                OleDbConnection conn = new OleDbConnection(connString);
                try
                {
                    PrepareCommand(cmd, conn, null, cmdType, cmdText, cmdParms, ConnectionActionType.Open);
                    OleDbDataReader rdr = cmd.ExecuteReader();
                    cmd.Parameters.Clear();
                    return rdr;
                }
                catch
                {
                    conn.Close();
                    throw;
                }
            }

            public static OleDbDataReader ExecuteReader(OleDbConnection conn, CommandType cmdType, string cmdText, params OleDbParameter[] cmdParms)
            {
                OleDbCommand cmd = new OleDbCommand();
                try
                {
                    PrepareCommand(cmd, conn, null, cmdType, cmdText, cmdParms, ConnectionActionType.AutoDetection);
                    OleDbDataReader rdr = cmd.ExecuteReader();
                    cmd.Parameters.Clear();
                    return rdr;
                }
                catch
                {
                    conn.Close();
                    throw;
                }
            }

            public static object ExecuteScalar(string connString, CommandType cmdType, string cmdText, params OleDbParameter[] cmdParms)
            {
                OleDbCommand cmd = new OleDbCommand();
                using (OleDbConnection conn = new OleDbConnection(connString))
                {
                    PrepareCommand(cmd, conn, null, cmdType, cmdText, cmdParms, ConnectionActionType.Open);
                    object val = cmd.ExecuteScalar();
                    cmd.Parameters.Clear();
                    return val;
                }
            }

            public static object ExecuteScalar(OleDbConnection conn, CommandType cmdType, string cmdText, params OleDbParameter[] cmdParms)
            {
                OleDbCommand cmd = new OleDbCommand();
                PrepareCommand(cmd, conn, null, cmdType, cmdText, cmdParms, ConnectionActionType.AutoDetection);
                object val = cmd.ExecuteScalar();
                cmd.Parameters.Clear();
                return val;
            }


            public static DataSet ExecuteDataset(string connString, CommandType cmdType, string cmdText, params OleDbParameter[] cmdParms)
            {
                OleDbCommand cmd = new OleDbCommand();
                using (OleDbConnection conn = new OleDbConnection(connString))
                {
                    PrepareCommand(cmd, conn, null, cmdType, cmdText, cmdParms, ConnectionActionType.Open);
                    OleDbDataAdapter da = new OleDbDataAdapter(cmd);
                    DataSet ds = new DataSet();
                    da.Fill(ds);
                    cmd.Parameters.Clear();
                    return ds;
                }
            }


            public static DataSet ExecuteDataset(OleDbConnection conn, CommandType cmdType, string cmdText, params OleDbParameter[] cmdParms)
            {
                OleDbCommand cmd = new OleDbCommand();
                PrepareCommand(cmd, conn, null, cmdType, cmdText, cmdParms, ConnectionActionType.AutoDetection);
                OleDbDataAdapter da = new OleDbDataAdapter(cmd);
                DataSet ds = new DataSet();
                da.Fill(ds);
                cmd.Parameters.Clear();
                return ds;
            }


            public static void CacheParameters(string cacheKey, params OleDbParameter[] cmdParms)
            {
                parmCache[cacheKey] = cmdParms;
            }


            public static OleDbParameter[] GetCachedParameters(string cacheKey)
            {
                OleDbParameter[] cachedParms = (OleDbParameter[])parmCache[cacheKey];

                if (cachedParms == null)
                    return null;

                OleDbParameter[] clonedParms = new OleDbParameter[cachedParms.Length];
                for (int i = 0, j = cachedParms.Length; i < j; i++)
                    clonedParms[i] = (OleDbParameter)((ICloneable)cachedParms[i]).Clone();

                return clonedParms;
            }


            private static void PrepareCommand(OleDbCommand cmd, OleDbConnection conn, OleDbTransaction trans, CommandType cmdType, string cmdText, OleDbParameter[] cmdParms, ConnectionActionType connActionType)
            {
                if (connActionType == ConnectionActionType.Open)
                {
                    conn.Open();
                }
                else
                {
                    if (conn.State != ConnectionState.Open)
                        conn.Open();
                }

                cmd.Connection = conn;
                cmd.CommandText = cmdText;

                if (trans != null)
                    cmd.Transaction = trans;

                cmd.CommandType = cmdType;

                if (cmdParms != null)
                {
                    foreach (OleDbParameter parm in cmdParms)
                        cmd.Parameters.Add(parm);
                }
            }

            public static DataTable ExecutePager(string connString, int pageIndex, int pageSize, string fileds, string table, string where, string order, out int pageCount, out int recordCount, string id)
            {
                if (pageIndex < 1) pageIndex = 1;
                if (pageSize < 1) pageSize = 10;
                if (string.IsNullOrEmpty(fileds)) fileds = "*";
                if (string.IsNullOrEmpty(order)) order = "ID desc";
                using (OleDbConnection conn = new OleDbConnection(connString))
                {
                    string myVw = string.Format(" {0} ", table);
                    string sqlText = string.Format(" select count(0) as recordCount from {0} {1}", myVw, where);
                    OleDbCommand cmdCount = new OleDbCommand(sqlText, conn);
                    if (conn.State == ConnectionState.Closed)
                        conn.Open();
                    recordCount = Convert.ToInt32(cmdCount.ExecuteScalar());

                    if ((recordCount % pageSize) > 0)
                        pageCount = recordCount / pageSize + 1;
                    else
                        pageCount = recordCount / pageSize;
                    OleDbCommand cmdRecord;
                    if (pageIndex == 1)
                    {
                        cmdRecord = new OleDbCommand(string.Format("select top {0} {1} from {2} {3} order by {4} ", pageSize, fileds, myVw, where, order), conn);
                    }
                    else if (pageIndex > pageCount)
                    {
                        cmdRecord = new OleDbCommand(string.Format("select top {0} {1} from {2} {3} order by {4} ", pageSize, fileds, myVw, "where 1=2", order), conn);
                    }
                    else
                    {
                        int pageLowerBound = pageSize * pageIndex;
                        int pageUpperBound = pageLowerBound - pageSize;
                        string recordIDs = RecordID(string.Format("select top {0} {1} from {2} {3} order by {4} ", pageLowerBound, id, myVw, where, order), pageUpperBound, conn);
                        cmdRecord = new OleDbCommand(string.Format("select {0} from {1} where {4} in ({2}) order by {3} ", fileds, myVw, recordIDs, order, id), conn);

                    }
                    OleDbDataAdapter dataAdapter = new OleDbDataAdapter(cmdRecord);
                    DataTable dt = new DataTable();
                    dataAdapter.Fill(dt);
                    return dt;
                }
            }

            private static string RecordID(string query, int passCount, OleDbConnection conn)
            {
                OleDbCommand cmd = new OleDbCommand(query, conn);
                string result = string.Empty;
                using (IDataReader dr = cmd.ExecuteReader())
                {
                    while (dr.Read())
                    {
                        if (passCount < 1)
                        {
                            result += "," + dr.GetInt32(0);
                        }
                        passCount--;
                    }
                }
                return result.Substring(1);
            }

            enum ConnectionActionType
            {
                None = 0,
                AutoDetection = 1,
                Open = 2
            }
        }
    }

    • Marked as answer by Anonymous Thursday, October 7, 2021 12:00 AM
    Thursday, March 17, 2011 2:04 AM