I think entire tomes have been written about what is a good DAL and what is not. But they all seem to be clichéd to me. Most people simply recommend using the MS DataAccess Application Block but I tend to disagree. I intend to blog extensively on this topic sometime.
There are many factors you need to consider when creating a DAL, some of which are : 1. Specificity: Just how specific to your current application does the DAL need to be ? Will it be used for other unrelated projects ? Is it intended as an organization level common library that can be called by any application ? Or is it an single-use library which will not be used except in the current project ? 2. Scope: What types of data stores does your DAL need to access ? Is it going to be restricted to SQL Server or OleDB or ODBC databases or should it work with any and all of them (is it data provider agnostic) ? 3. Security: What level of security will your DAL work under ? What permissions and authenticatin does it require ? Where does it retrieve database Connection settings from and how secure is this mode of retrieval ? 4. Level of Encapsulation: How encapsulated is your DAL ? Is it intended only as a wrapper for repetitive ADO.NET code ? Does it contain only Database access code or does it contain critical business logic as well ? Is it designed to interact with an Application Layer that will in turn encapsulate the Business logic and form the intermediary to the UI layer ? 5. Miscellaneous considerations: How Performant is your DAL ? How Scalable ? How Extensible ? If you can apply these considerations to your DAL, you should be able to evaluate just how suited your DAL is to your scenario. Keep in mind that what might be a very good DAL to one developer in one situation might be considered horrible by another developer. So, good or bad in this case is a very relative description. Therefore, I cannot tell you if this DAL is good or not. What I can tell you is that it can be considered a good foundation for an excellent DAL. Here are some of it's obvious negatives : 1. It features a dispose method but does not correctly implement the recommended Dispose pattern as recommended by M$. Further, the dispose method is being called internally in each Data access method instead of from the calling code. 2. It uses the OleDb provider which means that if it needs to SQL Server, it loses the benefits of using the SqlClient. 3. It uses the WebConfigurationManager to retrieve configuration settings (namely the ConnectionString), therefore, it can only work if included in the App_Code directory in a Website or Web service project. It cannot be compiled into a library and be used in other types of applications. 4. It does not contain additional convenience overloads for each method. For instance, what if I want to retrieve a paged dataset (i.e., I want to retrieve data only from the 10th record to the 20th record) 5. It assumes that the CommandType will always be "Text", i.e., there is no support for Stored Procedures and Parameterized statements. This is one of its biggest flaws. 6. The ExecuteScalar method returns an integer which is plainly a big mistake. There is a reason that the OleDbCommand.ExecuteScalar() method returns an Object type. 7. The GetSingleValue function seems to be quite irrelevant or useless. 8. It does not feature methods by which I can execute multiple commands without closing and reopening the connection. 9. The GetDataSet function converts the return value to XML but never uses it. That is an unnecessary expense. That's about it, I think. I think my Group members can guess that I had a pretty boring Sunday by judging the length of this post ! ;-) On Feb 1, 12:56 pm, Preeti <[email protected]> wrote: > Hi > > In one of my projects involving asp.net with MSAccess db I am given a > DAL to use for all data access code. It was written by a previous > programmer and I am supposed to use it. > > I want to know of this community if its good to use, optimized and > scalable ? > > --------------------------------------------------- > Here is the code of the DAL class : > --------------------------------------------------- > > using System; > using System.Collections.Generic; > using System.Text; > using System.Data.SqlClient; > using System.Data.OleDb; > using System.Data; > using System.Security.Cryptography; > using System.Web.UI.WebControls; > > public class dbhelper > { > > public OleDbConnection conn; > public OleDbCommand cmd; > public void openConnection() > { > > if (conn == null) > { > > conn = new OleDbConnection > (System.Web.Configuration.WebConfigurationManager.ConnectionStrings > ["ConnectionString"].ConnectionString); > cmd = new OleDbCommand(); > conn.Open(); > cmd.Connection = conn; > > } > } > > public void closeConnection() > { > if (conn != null) > { > conn.Close(); > } > } > > public void dispose() > { > if (conn != null) > { > conn.Dispose(); > conn = null; > } > } > > public DataSet GetDataSet(string Sql) > { > openConnection(); > DataSet ds = new DataSet(); > OleDbDataAdapter da = new OleDbDataAdapter(Sql, conn); > da.Fill(ds); > string ab = ds.GetXml(); > closeConnection(); > dispose(); > return ds; > } > > public int executeNonQuery(string strSql) > { > openConnection(); > int val; > cmd.CommandType = CommandType.Text; > cmd.CommandText = strSql; > //logger.Info("dbhelper query text: " + strSql); > val = this.cmd.ExecuteNonQuery(); > closeConnection(); > dispose(); > return val; > } > > public int executeScalar(string strSql) > { > openConnection(); > int Val; > cmd.CommandType = CommandType.Text; > cmd.CommandText = strSql; > Val = (int)cmd.ExecuteScalar(); > closeConnection(); > dispose(); > return Val; > } > public string getSingleValue(string colName, string strsql) > { > string returnVal = ""; > openConnection(); > OleDbDataReader oDReader; > cmd = new OleDbCommand(strsql, conn); > oDReader = cmd.ExecuteReader(); > > if (!oDReader.Read()) > { > returnVal = ""; > } > else > { > returnVal = oDReader[colName].ToString(); > } > closeConnection(); > dispose(); > return returnVal; > } > public bool ExecRead(string sQuery) > { > openConnection(); > bool bFlag; > > //string sQuery="sQuery"; > OleDbDataReader oDReader; > cmd = new OleDbCommand(sQuery, conn); > oDReader = cmd.ExecuteReader(); > > if (!oDReader.Read()) > { > bFlag = false; > } > else > { > bFlag = true; > } > closeConnection(); > dispose(); > > return bFlag; > }} > > ----------------------------------------------------------------------------- > And here is the code of some example calls to this Class > ----------------------------------------------------------------------------- > > 1) > dbhelper dbh = new dbhelper(); > string sql = "Select count(jobid) from Jobs"; > int val1 = dbh.executeScalar(sql1); > > 2) dbhelper dbh = new dbhelper(); > > dbh.openConnection(); > string sql = "insert into jobs (jobname) values ( "new job" )"; > dbh.cmd.CommandType = CommandType.Text; > dbh.cmd.CommandText = sql.ToString(); > int val = dbh.cmd.ExecuteNonQuery(); > > ----------------------------------------- > > Now everything works fine but still I want to know if its ok to use > these kind of DALs, Or it can be improved in any way.. > > Thanks > Preet
