Suggestions regarding using DAL

[Preeti]

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

[Cerebrus]

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 ! ;-)

> ---------------------------------------------------------------------------­--

> And here is the code of some example calls to this Class

> ---------------------------------------------------------------------------­--

[BBetances]

C pretty much covered all of the bases, but your writing very
redundant code. I also notice your accepting strings in your
GetDataSet method. Maybe you should make it accept a DBCommand object,
instead. I could very easily drop your whole schema with a string.

[Tanvir Faraj]

Hi BBetances,
how can DBCommand restrict someone if he issues the same drop command on that?

Thanks
TFR.