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); }