public List <RegulationMap> FindAllRegulations(int speciesId)
{
String commandText = "usp_GetSpeciesRegulations";
List <RegulationMap> regulationMappings = new List <RegulationMap>();
try
{
using (SqlConnection conn = DataContext.GetConnection(this.GetConnectionStringKey(_context)))
{
using (SqlCommand cmd = new SqlCommand(commandText, conn))
{
cmd.CommandType = CommandType.StoredProcedure;
cmd.Parameters.AddWithValue("@taxonomy_species_id", speciesId);
SqlDataReader reader = cmd.ExecuteReader(CommandBehavior.CloseConnection);
if (reader.HasRows)
{
while (reader.Read())
{
RegulationMap regulationMapping = new RegulationMap();
regulationMapping.ID = Int32.Parse(reader["taxonomy_regulation_map_id"].ToString());
regulationMapping.RegulationTypeCode = reader["regulation_type_code"].ToString();
regulationMapping.RegulationLevel = reader["regulation_level"].ToString();
regulationMapping.URL1 = reader["url_1"].ToString();
regulationMapping.URL1 = reader["url_2"].ToString();
regulationMapping.ModifiedDate = DateTime.Parse(reader["modified_date"].ToString());
regulationMappings.Add(regulationMapping);
}
}
}
}
}
catch (Exception ex)
{
throw ex;
}
return(regulationMappings);
}
public IQueryable <RegulationMap> GetRegulationMapFolderItems(string searchString)
{
const string COMMAND_TEXT = "usp_TaxonomyRegulationMaps_Search";
List <RegulationMap> regulationMapList = new List <RegulationMap>();
try
{
using (SqlConnection cn = DataContext.GetConnection(this.GetConnectionStringKey(_context)))
{
using (SqlCommand cmd = new SqlCommand())
{
cmd.Connection = cn;
cmd.CommandType = CommandType.StoredProcedure;
cmd.CommandText = COMMAND_TEXT;
cmd.Parameters.AddWithValue("@search_text", searchString);
using (SqlDataReader reader = cmd.ExecuteReader())
{
while (reader.Read())
{
RegulationMap regulationMap = new RegulationMap();
regulationMap.FamilyID = GetInt(reader["taxonomy_family_id"].ToString());
regulationMap.GenusID = GetInt(reader["taxonomy_genus_id"].ToString());
regulationMap.SpeciesID = GetInt(reader["taxonomy_species_id"].ToString());
regulationMap.RegulationID = GetInt(reader["taxonomy_regulation_id"].ToString());
regulationMapList.Add(regulationMap);
}
}
}
}
}
catch (Exception ex)
{
throw ex;
}
return(regulationMapList.AsQueryable());
}
public ResultContainer UpdateRegulationMap(RegulationMap regulationMap)
{
const string COMMAND_TEXT = "usp_TaxonomyRegulationMap_Insert";
ResultContainer resultContainer = new ResultContainer();
try
{
using (SqlConnection cn = DataContext.GetConnection(this.GetConnectionStringKey(_context)))
{
using (SqlCommand cmd = new SqlCommand())
{
cmd.Connection = cn;
cmd.CommandType = CommandType.StoredProcedure;
cmd.CommandText = COMMAND_TEXT;
if (regulationMap.FamilyID > 0)
{
cmd.Parameters.AddWithValue("@taxonomy_family_id", regulationMap.FamilyID);
}
else
{
cmd.Parameters.AddWithValue("@taxonomy_family_id", DBNull.Value);
}
if (regulationMap.GenusID > 0)
{
cmd.Parameters.AddWithValue("@taxonomy_genus_id", regulationMap.GenusID);
}
else
{
cmd.Parameters.AddWithValue("@taxonomy_genus_id", DBNull.Value);
}
if (regulationMap.SpeciesID > 0)
{
cmd.Parameters.AddWithValue("@taxonomy_species_id", regulationMap.SpeciesID);
}
else
{
cmd.Parameters.AddWithValue("@taxonomy_species_id", DBNull.Value);
}
cmd.Parameters.AddWithValue("@taxonomy_regulation_id", regulationMap.RegulationID);
if (!String.IsNullOrEmpty(regulationMap.Note))
{
cmd.Parameters.AddWithValue("@note", regulationMap.Note);
}
else
{
cmd.Parameters.AddWithValue("@note", DBNull.Value);
}
cmd.Parameters.AddWithValue("@created_by", regulationMap.CreatedByCooperatorID);
SqlParameter errorParam = new SqlParameter();
errorParam.SqlDbType = System.Data.SqlDbType.Int;
errorParam.ParameterName = "@out_error_number";
errorParam.Direction = System.Data.ParameterDirection.Output;
errorParam.Value = 0;
cmd.Parameters.Add(errorParam);
SqlParameter newIdParam = new SqlParameter();
newIdParam.SqlDbType = System.Data.SqlDbType.Int;
newIdParam.ParameterName = "@out_taxonomy_regulation_map_id";
newIdParam.Direction = System.Data.ParameterDirection.Output;
newIdParam.Value = 0;
cmd.Parameters.Add(newIdParam);
cmd.ExecuteNonQuery();
resultContainer.ResultCode = cmd.Parameters["@out_error_number"].Value.ToString();
if (!String.IsNullOrEmpty(resultContainer.ResultCode))
{
if (Int32.Parse(resultContainer.ResultCode) > 0)
{
throw new Exception(resultContainer.ResultCode + resultContainer.ResultMessage);
}
}
}
}
}
catch (SqlException ex)
{
switch (ex.Errors[0].Number)
{
case 547: // Foreign Key violation
string s = ex.Message;
s = s.Substring(s.IndexOf("column "));
string[] array = s.Split('.');
s = array[0].Substring(array[0].IndexOf('\''));
break;
}
throw ex;
}
catch (Exception ex)
{
throw ex;
}
return(resultContainer);
}
