private void _updateOrAddOrDeleteDBFromStoreProc(Query query)
{
List <SqlParameter> parameters = new List <SqlParameter>();
string storeProcName;
if (StoreProcMap != null && StoreProcMap.ContainsKey(query.Procedure))
{
Query.StoreProcType storeproc = this.StoreProcMap[query.Procedure];
storeProcName = storeproc.Name;
foreach (string key in storeproc.Parameters.Keys)
{
// use parameter type from storeproc definition
SqlParameter parameter = new SqlParameter(key, storeproc.Parameters[key]);
parameter.Value = query.Parameters[key].Value;
parameters.Add(parameter);
}
}
else
{
storeProcName = query.Procedure;
// generic, no mapping
// rely on input parameter type
foreach (string key in query.Parameters.Keys)
{
SqlParameter parameter = new SqlParameter(key, query.Parameters[key].Type);
parameter.Value = query.Parameters[key].Value;
parameters.Add(parameter);
}
}
SqlTransConn transConn = GetConnection();
try
{
SqlHelper.ExecuteNonQuery(transConn, CommandType.StoredProcedure, storeProcName, parameters);
}
catch (Exception ex)
{
throw new Exception("Failed to execute store proc:" + storeProcName, ex);
}
finally
{
transConn.Close();
}
}
private List <T> _findAllFromStoreProc(Query query)
{
List <T> list = new List <T>();
List <SqlParameter> parameters = new List <SqlParameter>();
string storeProcName;
if (StoreProcMap != null && StoreProcMap.ContainsKey(query.Procedure))
{
Query.StoreProcType storeproc = this.StoreProcMap[query.Procedure];
storeProcName = storeproc.Name;
foreach (string key in storeproc.Parameters.Keys)
{
// use parameter type from storeproc definition
SqlParameter parameter = new SqlParameter(key, storeproc.Parameters[key]);
parameter.Value = query.Parameters[key].Value;
parameters.Add(parameter);
}
}
else
{
storeProcName = query.Procedure;
// generic, no mapping
// rely on input parameter type
foreach (string key in query.Parameters.Keys)
{
SqlParameter parameter = new SqlParameter(key, query.Parameters[key].Type);
parameter.Value = query.Parameters[key].Value;
parameters.Add(parameter);
}
}
SqlTransConn transConn = GetConnection(_connectionName);
try
{
using (SqlDataReader reader = SqlHelper.ExecuteReader(transConn, CommandType.StoredProcedure, storeProcName, parameters))
{
if (reader.HasRows)
{
while (reader.Read())
{
T to = new T();
to.Initialize(reader);
LateBinding(ref to, query);
list.Add(to);
}
}
}
}
catch (Exception ex)
{
throw new Exception("Failed to execute store proc:" + storeProcName, ex);
}
finally
{
if (!transConn.IsInTransaction())
{
transConn.Close();
}
}
return(list);
}