I am trying to create a Data Access Layer using ADO.Net. I have created a generic method for ExecuteNonQuery. The stored procedure I am calling will return two parameters. The output parameters from the stored procedure should be assigned to DTO object and the DTO should need to be returned to BLL.
I am a bit concerned about two things: 1) It is working only when we initialize the output parameters before calling the stored procedure 2) Converting the results to DTO object. Any performance issues?
I am not sure if it's the right approach to return the DTO object to BLL. DBHelper and DBAccess class seems ok to me. I am concerned about the GetRefNo method for the above two questions.
Could anyone please review and let me know if any changes can be made to the existing process.
public SubmissionHeaderDTO GetRefNo()
{
var inPrms = new Dictionary<string, object>()
{
{"FormId", Guid.NewGuid()},
{"FormCode", "TST"},
{"FormTitle", "Test form"},
{"User", "test"},
{"Host", "iisserver"}
};
var outPrms = new Dictionary<string, object>()
{
{ "SubmissionId", Guid.NewGuid() },
{ "SubmitSequence", "one"}
};
var refNo = DBHelper.ExecSP(Constants.SPNames.SubmissionHeaderInsert, inPrms, outPrms);
SubmissionHeaderDTO result = DictionaryToObject<SubmissionHeaderDTO>(refNo);
return result;
}
private static T DictionaryToObject<T>(IDictionary<string, object> dict) where T : new()
{
var t = new T();
PropertyInfo[] properties = t.GetType().GetProperties();
foreach (PropertyInfo property in properties)
{
if (!dict.Any(x => x.Key.Equals(property.Name, StringComparison.InvariantCultureIgnoreCase)))
continue;
KeyValuePair<string, object> item = dict.First(x => x.Key.Equals(property.Name, StringComparison.InvariantCultureIgnoreCase));
Type tPropertyType = t.GetType().GetProperty(property.Name).PropertyType;
Type newT = Nullable.GetUnderlyingType(tPropertyType) ?? tPropertyType;
object newA = Convert.ChangeType(item.Value, newT);
t.GetType().GetProperty(property.Name).SetValue(t, newA, null);
}
return t;
}
DBHelper.cs
public static Dictionary<string, object> ExecSP(string sp, Dictionary<string, object> paramIn, Dictionary<string, object> paramOut)
{
var dbAccess = new DBAccess();
var results = dbAccess.ExecuteQuery(sp, paramIn, paramOut);
return results;
}
DBAccess.cs
public Dictionary<string, object> ExecuteQuery(string storedProcedure, Dictionary<string, object> parameters,
Dictionary<string, object> outParameters)
{
SqlConnection sqlConn = null;
SqlCommand sqlcmd = null;
SqlTransaction transaction = null;
SqlParameter sqlPar = null;
Dictionary<string, object> returnParameters = null;
try
{
sqlConn = new SqlConnection(Configuration.DBConnection);
sqlConn.Open();
transaction = sqlConn.BeginTransaction("Results");
sqlcmd = new SqlCommand(storedProcedure, sqlConn, transaction);
sqlcmd.CommandType = CommandType.StoredProcedure;
foreach (KeyValuePair<string, object> kv in parameters)
{
sqlcmd.Parameters.AddWithValue(kv.Key, kv.Value);
}
foreach (KeyValuePair<string, object> kv in outParameters)
{
sqlPar = sqlcmd.Parameters.AddWithValue(kv.Key, kv.Value);
sqlPar.Direction = ParameterDirection.Output;
}
sqlcmd.ExecuteNonQuery();
returnParameters = GetOutputParameters(sqlcmd.Parameters);
transaction.Commit();
}
catch (System.Exception e)
{
var tt = e.Message;
try
{
if (transaction != null)
transaction.Rollback();
}
catch { }
throw;
}
finally
{
if (sqlConn != null)
{
if (sqlConn.State != ConnectionState.Closed)
{
sqlConn.Close();
}
}
if (transaction != null)
{
IDisposable dispose = transaction as IDisposable;
if (dispose != null) { dispose.Dispose(); }
}
if (sqlcmd != null)
{
IDisposable dispose = sqlcmd as IDisposable;
if (dispose != null) { dispose.Dispose(); }
}
if (sqlConn != null)
{
IDisposable dispose = sqlConn as IDisposable;
if (dispose != null) { dispose.Dispose(); }
}
}
return returnParameters;
}
private Dictionary<string, object> GetOutputParameters(SqlParameterCollection paramCollection)
{
var returnParameters = new Dictionary<string, object>();
foreach (SqlParameter par in paramCollection)
{
if ((par.Direction == ParameterDirection.Output) || (par.Direction == ParameterDirection.ReturnValue))
{
returnParameters.Add(par.ParameterName, par.Value);
}
}
return returnParameters;
}