User2018110980 posted
Good Day All!!!
I need experts openion and guidence on this...I'm developing a web application using 3-tier model architecture (like: Application, Business, and Data Layers). I'm not sure the code I wrote is up to the mark and generic to other web applications too. For
example: In Data Access Layer I wrote a method ExecuteProcedure(...) which accespts 3-parameters, ProcName, InParam (optional), OutParam(optional). The method looks like:
namespace ns1
{
...
public struct ParamType
{
public string Name;
public SqlDbType Type;
public int Size;
public string Value;
}
....
}
....
namespace ns2
{
....
public DataSet ExecuteProcedure(string procName, List<ParamType> inParams = null, List<ParamType> outParams = null)
{
DataSet resultSet = null;
connection = GetConnection();
try
{
if (connection.State == ConnectionState.Closed)
connection.Open();
SqlCommand cmd = connection.CreateCommand();
cmd.CommandText = procName;
cmd.CommandType = CommandType.StoredProcedure;
if (inParams != null)
{
SqlParameter param = null;
foreach (ParamType pt in inParams)
{
param = new SqlParameter(pt.Name, pt.Type);
param.Value = pt.Value;
cmd.Parameters.Add(param);
}
}
if (outParams != null)
{
foreach (ParamType pt in outParams)
{
SqlParameter param = new SqlParameter(pt.Name, pt.Type);
param.Value = pt.Value;
cmd.Parameters.Add(param);
}
}
SqlDataAdapter da = new SqlDataAdapter(cmd);
resultSet = new DataSet();
da.Fill(resultSet);
}
catch (Exception ex)
{
throw ex;
}
finally
{
if (connection.State == ConnectionState.Open)
connection.Close();
connection.Dispose();
connection = null;
}
return resultSet;
}
....
}
In the method signature I used a struc type, ParamType, which has 4-attributes (like: Name, Type, Size, Value) which describes the basic parameter properties. I have placed this type in the main namespace (ns1) which can be accessable to all levels of classes,
but here to make Type parameter as generic I took the actual type as
SqlDbType and refering in BusinessLayer -> Application Layer, but as per the layered model functionality Application layer shouldn't directly interacts with anyof the DB related functionality without the help of BusinessLayer. So, here I'm
requesting you to provide me a generic approach of writing / implementing both Business + Data Access layers. Please provide me any links or samples where I can go thorugh it. Hope you understand my problem. Thanks for your help, in advance.
Regards,
-Kiran.
