Menu
i am creating a database applicatin in .Net. I am using a DataAccessLayer for communicating .net objects with database but i am not sure that this class is correct or not Can anyone cross check it and rectify any mistakes
namespace IDataaccess
{
#region Collection Class
public class SPParamCollection : List<SPParams>
{
}
public class SPParamReturnCollection : List<SPParams>
{
}
#endregion
#region struct
public struct SPParams
{
public string Name
{
get;
set;
}
public object Value
{
get;
set;
}
public ParameterDirection ParamDirection
{
get;
set;
}
public SqlDbType Type
{
get;
set;
}
public int Size
{
get;
set;
}
public string TypeName
{
get;
set;
}
// public string datatype;
}
#endregion
/// <summary>
/// Interface DataAccess Layer implimentation New version
/// </summary>
public interface IDataAccess
{
DataTable getDataUsingSP(string spName);
DataTable getDataUsingSP(string spName, SPParamCollection spParamCollection);
DataSet getDataSetUsingSP(string spName);
DataSet getDataSetUsingSP(string spName, SPParamCollection spParamCollection);
SqlDataReader getDataReaderUsingSP(string spName);
SqlDataReader getDataReaderUsingSP(string spName, SPParamCollection spParamCollection);
int executeSP(string spName);
int executeSP(string spName, SPParamCollection spParamCollection, bool addExtraParmas);
int executeSP(string spName, SPParamCollection spParamCollection);
DataTable getDataUsingSqlQuery(string strSqlQuery);
int executeSqlQuery(string strSqlQuery);
SPParamReturnCollection executeSPReturnParam(string spName, SPParamReturnCollection spParamReturnCollection);
SPParamReturnCollection executeSPReturnParam(string spName, SPParamCollection spParamCollection, SPParamReturnCollection spParamReturnCollection);
SPParamReturnCollection executeSPReturnParam(string spName, SPParamCollection spParamCollection, SPParamReturnCollection spParamReturnCollection, bool addExtraParmas);
int executeSPReturnParam(string spName, SPParamCollection spParamCollection, ref SPParamReturnCollection spParamReturnCollection);
object getScalarUsingSP(string spName);
object getScalarUsingSP(string spName, SPParamCollection spParamCollection);
}
}
using IDataaccess;
namespace Dataaccess
{
/// <summary>
/// Class DataAccess Layer implimentation New version
/// </summary>
public class DataAccess : IDataaccess.IDataAccess
{
#region Public variables
static string Strcon;
DataSet dts = new DataSet();
public DataAccess()
{
Strcon = sReadConnectionString();
}
private string sReadConnectionString()
{
try
{
//dts.ReadXml("C:\\cnn.config");
//Strcon = dts.Tables[0].Rows[0][0].ToString();
//System.Configuration.Configuration config = ConfigurationManager.OpenExeConfiguration(ConfigurationUserLevel.None);
//Strcon = config.ConnectionStrings.ConnectionStrings["connectionString"].ConnectionString;
// Add an Application Setting.
//Strcon = "Data Source=192.168.50.103;Initial Catalog=erpDB;User ID=ipixerp1;Password=NogoXVc3";
Strcon = System.Configuration.ConfigurationManager.AppSettings["connection"];
//Strcon = System.Configuration.ConfigurationSettings.AppSettings[0].ToString();
}
catch (Exception)
{
}
return Strcon;
}
public SqlConnection connection;
public SqlCommand cmd;
public SqlDataAdapter adpt;
public DataTable dt;
public int intresult;
public SqlDataReader sqdr;
#endregion
#region Public Methods
public DataTable getDataUsingSP(string spName)
{
return getDataUsingSP(spName, null);
}
public DataTable getDataUsingSP(string spName, SPParamCollection spParamCollection)
{
try
{
using (connection = new SqlConnection(Strcon))
{
connection.Open();
using (cmd = new SqlCommand(spName, connection))
{
int count, param = 0;
if (spParamCollection == null)
{ param = -1; }
else
{
param = spParamCollection.Count;
}
for (count = 0; count < param; count++)
{
cmd.Parameters.AddWithValue(spParamCollection[count].Name, spParamCollection[count].Value);
}
cmd.CommandType = CommandType.StoredProcedure;
cmd.CommandTimeout = 60;
adpt = new SqlDataAdapter(cmd);
dt = new DataTable();
adpt.Fill(dt);
return (dt);
}
}
}
finally
{
connection.Close();
}
}
public DataSet getDataSetUsingSP(string spName)
{
return getDataSetUsingSP(spName, null);
}
public DataSet getDataSetUsingSP(string spName, SPParamCollection spParamCollection)
{
try
{
using (connection = new SqlConnection(Strcon))
{
connection.Open();
using (cmd = new SqlCommand(spName, connection))
{
int count, param = 0;
if (spParamCollection == null)
{
param = -1;
}
else
{
param = spParamCollection.Count;
}
for (count = 0; count < param; count++)
{
cmd.Parameters.AddWithValue(spParamCollection[count].Name, spParamCollection[count].Value);
}
cmd.CommandType = CommandType.StoredProcedure;
cmd.CommandTimeout = 60;
adpt = new SqlDataAdapter(cmd);
DataSet ds = new DataSet();
adpt.Fill(ds);
return ds;
}
}
}
finally
{
connection.Close();
}
}
public SqlDataReader getDataReaderUsingSP(string spName)
{
return getDataReaderUsingSP(spName, null);
}
public SqlDataReader getDataReaderUsingSP(string spName, SPParamCollection spParamCollection)
{
try
{
using (connection = new SqlConnection(Strcon))
{
connection.Open();
using (cmd = new SqlCommand(spName, connection))
{
int count, param = 0;
if (spParamCollection == null)
{ param = -1; }
else
{
param = spParamCollection.Count;
}
for (count = 0; count < param; count++)
{
cmd.Parameters.AddWithValue(spParamCollection[count].Name, spParamCollection[count].Value);
}
cmd.CommandType = CommandType.StoredProcedure;
cmd.CommandTimeout = 60;
sqdr = cmd.ExecuteReader();
return (sqdr);
}
}
}
finally
{
connection.Close();
}
}
public int executeSP(string spName)
{
return executeSP(spName, null);
}
public int executeSP(string spName, SPParamCollection spParamCollection, bool addExtraParmas)
{
try
{
using (connection = new SqlConnection(Strcon))
{
connection.Open();
using (cmd = new SqlCommand(spName, connection))
{
int count, param = 0;
if (spParamCollection == null)
{ param = -1; }
else
{
param = spParamCollection.Count;
}
for (count = 0; count < param; count++)
{
SqlParameter par = new SqlParameter(spParamCollection[count].Name, spParamCollection[count].Value);
if (addExtraParmas)
{
par.TypeName = spParamCollection[count].TypeName;
par.SqlDbType = spParamCollection[count].Type;
}
cmd.Parameters.Add(par);
}
cmd.CommandType = CommandType.StoredProcedure;
cmd.CommandTimeout = 60;
return (cmd.ExecuteNonQuery());
}
}
}
finally
{
connection.Close();
}
}
public int executeSP(string spName, SPParamCollection spParamCollection)
{
return executeSP(spName, spParamCollection, false);
}
public DataTable getDataUsingSqlQuery(string strSqlQuery)
{
try
{
using (connection = new SqlConnection(Strcon))
connection.Open();
{
using (cmd = new SqlCommand(strSqlQuery, connection))
{
cmd.CommandType = CommandType.Text;
cmd.CommandTimeout = 60;
adpt = new SqlDataAdapter(cmd);
dt = new DataTable();
adpt.Fill(dt);
return (dt);
}
}
}
finally
{
connection.Close();
}
}
public int executeSqlQuery(string strSqlQuery)
{
try
{
using (connection = new SqlConnection(Strcon))
{
connection.Open();
using (cmd = new SqlCommand(strSqlQuery, connection))
{
cmd.CommandType = CommandType.Text;
cmd.CommandTimeout = 60;
intresult = cmd.ExecuteNonQuery();
return (intresult);
}
}
}
finally
{
connection.Close();
}
}
public SPParamReturnCollection executeSPReturnParam(string spName, SPParamReturnCollection spParamReturnCollection)
{
return executeSPReturnParam(spName, null, spParamReturnCollection);
}
public int executeSPReturnParam()
{
return 0;
}
public int executeSPReturnParam(string spName, SPParamCollection spParamCollection, ref SPParamReturnCollection spParamReturnCollection)
{
try
{
SPParamReturnCollection spParamReturned = new SPParamReturnCollection();
using (connection = new SqlConnection(Strcon))
{
connection.Open();
using (cmd = new SqlCommand(spName, connection))
{
int count, param = 0;
if (spParamCollection == null)
{ param = -1; }
else
{
param = spParamCollection.Count;
}
for (count = 0; count < param; count++)
{
cmd.Parameters.AddWithValue(spParamCollection[count].Name, spParamCollection[count].Value);
}
cmd.CommandType = CommandType.StoredProcedure;
foreach (SPParams paramReturn in spParamReturnCollection)
{
SqlParameter _parmReturn = new SqlParameter(paramReturn.Name, paramReturn.Size);
_parmReturn.Direction = paramReturn.ParamDirection;
if (paramReturn.Size > 0) _parmReturn.Size = paramReturn.Size;
else _parmReturn.Size = 32;
_parmReturn.SqlDbType = paramReturn.Type;
cmd.Parameters.Add(_parmReturn);
}
cmd.CommandTimeout = 60;
intresult = cmd.ExecuteNonQuery();
connection.Close();
//for (int i = 0; i < spParamReturnCollection.Count; i++)
//{
// spParamReturned.Add(new SPParams
// {
// Name = spParamReturnCollection[i].Name,
// Value = cmd.Parameters[spParamReturnCollection[i].Name].Value
// });
//}
}
}
return intresult;
}
finally
{
connection.Close();
}
}
public SPParamReturnCollection executeSPReturnParam(string spName, SPParamCollection spParamCollection, SPParamReturnCollection spParamReturnCollection)
{
return executeSPReturnParam(spName, spParamCollection, spParamReturnCollection, false);
}
public SPParamReturnCollection executeSPReturnParam(string spName, SPParamCollection spParamCollection, SPParamReturnCollection spParamReturnCollection, bool addExtraParmas)
{
try
{
SPParamReturnCollection spParamReturned = new SPParamReturnCollection();
using (connection = new SqlConnection(Strcon))
{
connection.Open();
using (cmd = new SqlCommand(spName, connection))
{
int count, param = 0;
if (spParamCollection == null)
{ param = -1; }
else
{
param = spParamCollection.Count;
}
for (count = 0; count < param; count++)
{
//cmd.Parameters.AddWithValue(spParamCollection[count].Name, spParamCollection[count].Value);
SqlParameter par = new SqlParameter(spParamCollection[count].Name, spParamCollection[count].Value);
if (addExtraParmas)
{
par.TypeName = spParamCollection[count].TypeName;
par.SqlDbType = spParamCollection[count].Type;
}
cmd.Parameters.Add(par);
}
cmd.CommandType = CommandType.StoredProcedure;
foreach (SPParams paramReturn in spParamReturnCollection)
{
SqlParameter _parmReturn = new SqlParameter(paramReturn.Name, paramReturn.Value);
_parmReturn.Direction = paramReturn.ParamDirection;
if (paramReturn.Size > 0) _parmReturn.Size = paramReturn.Size;
else _parmReturn.Size = 32;
_parmReturn.SqlDbType = paramReturn.Type;
cmd.Parameters.Add(_parmReturn);
}
cmd.CommandTimeout = 60;
cmd.ExecuteNonQuery();
connection.Close();
for (int i = 0; i < spParamReturnCollection.Count; i++)
{
spParamReturned.Add(new SPParams
{
Name = spParamReturnCollection[i].Name,
Value = cmd.Parameters[spParamReturnCollection[i].Name].Value
});
}
}
}
return spParamReturned;
}
catch (Exception ex)
{
return null;
}
finally
{
connection.Close();
}
}
public object getScalarUsingSP(string spName)
{
return getScalarUsingSP(spName, null);
}
public object getScalarUsingSP(string spName, SPParamCollection spParamCollection)
{
try
{
using (connection = new SqlConnection(Strcon))
{
connection.Open();
using (cmd = new SqlCommand(spName, connection))
{
int count, param = 0;
if (spParamCollection == null)
{ param = -1; }
else
{
param = spParamCollection.Count;
}
for (count = 0; count < param; count++)
{
cmd.Parameters.AddWithValue(spParamCollection[count].Name, spParamCollection[count].Value);
cmd.CommandTimeout = 60;
}
cmd.CommandType = CommandType.StoredProcedure;
return cmd.ExecuteScalar();
}
}
}
finally
{
connection.Close();
cmd.Dispose();
}
}
#endregion
}
}
497
As I can see your current approach doesn't care about transactional environment, This days no one care about how to create DAL, when EF, nhibernate, ... are available, I suggest check Entity Framework and use it for startup check code first. it is available for 2008 and 2010.
56
If you love us? You can donate to us via Paypal or buy me a coffee so we can maintain and grow! Thank you!
Donate Us With
