Suggestions regarding improving 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
Preeti

 

[bruce barker]

its just a simple helper routine. ts main flaw is lack of error
handling. any error leaves dangling connections and lost resources. the
routines should all have a finally clause that releases the recourses
via close or dispose.

-- bruce (sqlwork.com)

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
Preeti

 

[Preeti]

Thanks bruce for the suggestion.
I will add finally clause on all the functions and dispose the
resources.

Any other suggestions are welcome

Thanks
Preeti