public Int32 Update(InbAttribMapValDto pData)
{
Int32 rowsUpdated = 0;
string sql = "update " + DBUtils.SCHEMA_NAME + "inb_attrib_map_val " +
"set inb_attrib_code = @inb_attrib_code, mapped_value = @mapped_value, " +
" descr = @descr, active_flag = @active_flag " +
" where id = @id";
using (SqlConnection conn = new SqlConnection(sqlConnStr))
{
using (SqlCommand cmd = new SqlCommand(sql, conn))
{
cmd.CommandType = System.Data.CommandType.Text;
cmd.Parameters.Add("@id", System.Data.SqlDbType.Int).Value = pData.Id;
cmd.Parameters.Add("@inb_attrib_code", System.Data.SqlDbType.VarChar).Value = DBUtils.ValueStringOrDBNull(pData.InbAttribCode);
cmd.Parameters.Add("@mapped_value", System.Data.SqlDbType.VarChar).Value = DBUtils.ValueStringOrDBNull(pData.MappedValue);
cmd.Parameters.Add("@descr", System.Data.SqlDbType.VarChar).Value = DBUtils.ValueStringOrDBNull(pData.Descr);
cmd.Parameters.Add("@active_flag", System.Data.SqlDbType.VarChar).Value = DBUtils.ValueStringOrDBNull(pData.ActiveFlag);
conn.Open();
rowsUpdated = cmd.ExecuteNonQuery();
}
}
return rowsUpdated;
}
public Int32 Insert(InbAttribMapValDto pData)
{
Int32 newId = DBUtils.GetNextSequence(sqlConnStr, SEQ_NAME);
string sql = "Insert into " + DBUtils.SCHEMA_NAME + "inb_attrib_map_val " +
" (id, inb_attrib_code, mapped_value, descr, active_flag) " +
" Values " +
" (@id, @inb_attrib_code, @mapped_value, @descr, @active_flag) ";
using (SqlConnection conn = new SqlConnection(sqlConnStr))
{
using (SqlCommand cmd = new SqlCommand(sql, conn))
{
cmd.CommandType = System.Data.CommandType.Text;
cmd.Parameters.Add("@id", System.Data.SqlDbType.Int).Value = newId;
cmd.Parameters.Add("@inb_attrib_code", System.Data.SqlDbType.VarChar).Value = DBUtils.ValueStringOrDBNull(pData.InbAttribCode);
cmd.Parameters.Add("@mapped_value", System.Data.SqlDbType.VarChar).Value = DBUtils.ValueStringOrDBNull(pData.MappedValue);
cmd.Parameters.Add("@descr", System.Data.SqlDbType.VarChar).Value = DBUtils.ValueStringOrDBNull(pData.Descr);
cmd.Parameters.Add("@active_flag", System.Data.SqlDbType.VarChar).Value = DBUtils.ValueStringOrDBNull(pData.ActiveFlag);
conn.Open();
cmd.ExecuteNonQuery();
}
}
return newId;
}
public void Test_InbAttribMapPhraseDal()
{
const string BASE_MAPPED_VALUE = "TEST_IF";
messageSeqNo = 0;
// Pre-Requisite -- INB_ATTRIB row, CODE = "CPTY_SN"
// INB_ATTRIB_MAP_VAL: ID = 1, MAPPED_VALUE = "TEST_IF"
InbAttribMapValDal inbAttribMapValDal = new InbAttribMapValDal(sqlConnectionString);
List<InbAttribMapValDto> resultMapValList = new List<InbAttribMapValDto>();
InbAttribMapValDto mapValData = new InbAttribMapValDto();
mapValData.InbAttribCode = "CPTY_SN";
mapValData.MappedValue = BASE_MAPPED_VALUE;
mapValData.Descr = BASE_MAPPED_VALUE + "- DESCR";
mapValData.ActiveFlag = "Y";
//Make sure TEST_IF exists in INB_ATTRB table
resultMapValList = inbAttribMapValDal.GetMapValues(mapValData.InbAttribCode);
bool foundRow = false;
foreach (InbAttribMapValDto mapVal in resultMapValList)
{
if (mapVal.MappedValue.Equals(BASE_MAPPED_VALUE))
{
mapValData.Id = mapVal.Id;
foundRow = true;
break;
}
}
if (!foundRow)
{
mapValData.Id = inbAttribMapValDal.Insert(mapValData);
}
Assert.IsTrue(mapValData.Id > 0, getMessage("Attrib Map Val row not found."));
InbAttribMapPhraseDal inbAttribMapPhraseDal = new InbAttribMapPhraseDal(sqlConnectionString);
List<InbAttribMapPhraseDto> resultDataList = new List<InbAttribMapPhraseDto>();
List<InbAttribMapComboDto> resultComboList = new List<InbAttribMapComboDto>();
InbAttribMapPhraseDto parmData = new InbAttribMapPhraseDto();
//Make sure the data we are about to insert doesn't already exist.
resultDataList = inbAttribMapPhraseDal.GetPhrases(mapValData.Id);
int oldTestDataRowsDeleted = 0;
foreach (InbAttribMapPhraseDto mapPhrase in resultDataList)
{
inbAttribMapPhraseDal.Delete(mapPhrase.Id);
oldTestDataRowsDeleted++;
}
bool oldTestDataExists = resultDataList.Count != oldTestDataRowsDeleted;
Assert.IsFalse(oldTestDataExists, getMessage("Old test data exists and was not deleted."));
//Main test routine
const string INPUT_A = "TEST_IF_01";
const string INPUT_B = "TEST_IF_02";
const string INPUT_C = "TEST_IF_03";
const string INPUT_D = "TEST_IF_04";
Int32 testIdInsert1 = 0;
Int32 testIdInsert2 = 0;
//Insert -- test null value parms
parmData.InbAttribMapValId = mapValData.Id;
parmData.Phrase = INPUT_A;
parmData.ActiveFlag = "Y";
testIdInsert1 = inbAttribMapPhraseDal.Insert(parmData);
Assert.AreNotEqual(0, testIdInsert1, getMessage("Row inserted - non-Zero Id returned."));
expectedValue = INPUT_A;
resultDataList = inbAttribMapPhraseDal.GetPhrases(parmData.InbAttribMapValId);
Assert.IsTrue(resultDataList.Count > 0, getMessage("No rows returned after insert."));
foundRow = false;
foreach (InbAttribMapPhraseDto mapPhrase in resultDataList)
{
if (mapPhrase.Id.Equals(testIdInsert1))
{
Assert.AreEqual(INPUT_A, mapPhrase.Phrase, getMessage("MappedPhrase not found."));
foundRow = true;
break;
}
}
Assert.IsTrue(foundRow, getMessage("Row not found after insert."));
//Insert -- test non-null value parms
parmData.InbAttribMapValId = mapValData.Id;
parmData.Phrase = INPUT_B;
parmData.ActiveFlag = "Y";
testIdInsert2 = inbAttribMapPhraseDal.Insert(parmData);
Assert.AreNotEqual(0, testIdInsert2, getMessage("Row inserted - non-Zero Id returned."));
expectedValue = INPUT_B;
resultComboList = inbAttribMapPhraseDal.GetPhrases("TEST_IF");
Assert.IsTrue(resultDataList.Count > 0, getMessage("No rows returned after insert."));
foundRow = false;
foreach (InbAttribMapComboDto mapPhrase in resultComboList)
{
if (mapPhrase.PhraseId.Equals(testIdInsert2))
{
Assert.AreEqual(INPUT_B, mapPhrase.Phrase, getMessage("MappedPhrase not found."));
foundRow = true;
break;
}
}
Assert.IsTrue(foundRow, getMessage("Row not found after insert."));
//Update
parmData.InbAttribMapValId = mapValData.Id;
parmData.Id = testIdInsert1;
parmData.Phrase = INPUT_C;
parmData.ActiveFlag = "Y";
int rowsUpdated = inbAttribMapPhraseDal.Update(parmData);
resultDataList = inbAttribMapPhraseDal.GetPhrases(parmData.InbAttribMapValId);
Assert.IsTrue(resultDataList.Count > 0, getMessage("No rows returned after update."));
foundRow = false;
foreach (InbAttribMapPhraseDto mapPhrase in resultDataList)
{
if (mapPhrase.Id.Equals(testIdInsert1))
{
Assert.AreEqual(INPUT_C, mapPhrase.Phrase, getMessage("MappedPhrase not found."));
foundRow = true;
break;
}
}
Assert.IsTrue(foundRow, getMessage("Row not found after update."));
//Update
parmData.InbAttribMapValId = mapValData.Id;
parmData.Id = testIdInsert2;
parmData.Phrase = INPUT_D;
parmData.ActiveFlag = "Y";
rowsUpdated = inbAttribMapPhraseDal.Update(parmData);
resultDataList = inbAttribMapPhraseDal.GetPhrases(parmData.InbAttribMapValId);
Assert.IsTrue(resultDataList.Count > 0, getMessage("No rows returned after update."));
foundRow = false;
foreach (InbAttribMapPhraseDto mapPhrase in resultDataList)
{
if (mapPhrase.Id.Equals(testIdInsert2))
{
Assert.AreEqual(INPUT_D, mapPhrase.Phrase, getMessage("MappedPhrase not found."));
foundRow = true;
break;
}
}
Assert.IsTrue(foundRow, getMessage("Row not found after update."));
//Delete
rowsUpdated = inbAttribMapPhraseDal.Delete(testIdInsert1);
Assert.IsTrue(rowsUpdated == 1, getMessage("Row not deleted."));
rowsUpdated = inbAttribMapPhraseDal.Delete(testIdInsert2);
Assert.IsTrue(rowsUpdated == 1, getMessage("Row not deleted."));
}
public void Test_InbAttribMapValDal()
{
// Pre-Requisite -- INB_ATTRIB row, CODE = "CPTY_SN"
//Setup
const string INPUT_A = "TEST_IF_01";
const string INPUT_B = "TEST_IF_02";
const string INPUT_C = "TEST_IF_03";
const string INPUT_D = "TEST_IF_04";
const string ATTRIB_CODE = "CPTY_SN";
messageSeqNo = 0;
InbAttribMapValDal inbAttribMapValDal = new InbAttribMapValDal(sqlConnectionIntegratedSecurityString);
List<InbAttribMapValDto> resultDataList = new List<InbAttribMapValDto>();
//Make sure the data we are about to insert doesn't already exist.
resultDataList = inbAttribMapValDal.GetMapValues(ATTRIB_CODE);
foreach (InbAttribMapValDto mapVal in resultDataList)
{
if (mapVal.MappedValue.Equals(INPUT_A) ||
mapVal.MappedValue.Equals(INPUT_B) ||
mapVal.MappedValue.Equals(INPUT_C) ||
mapVal.MappedValue.Equals(INPUT_D))
inbAttribMapValDal.Delete(mapVal.Id);
}
resultDataList = inbAttribMapValDal.GetMapValues(ATTRIB_CODE);
bool oldTestDataExists = false;
foreach (InbAttribMapValDto mapVal in resultDataList)
{
if (mapVal.MappedValue.Equals(INPUT_A) ||
mapVal.MappedValue.Equals(INPUT_B) ||
mapVal.MappedValue.Equals(INPUT_C) ||
mapVal.MappedValue.Equals(INPUT_D))
{
oldTestDataExists = true;
break;
}
}
Assert.IsFalse(oldTestDataExists, getMessage("Old test data exists and was not deleted."));
Int32 testIdInsert1 = 0;
Int32 testIdInsert2 = 0;
InbAttribMapValDto parmData = new InbAttribMapValDto();
parmData.InbAttribCode = ATTRIB_CODE;
resultDataList = inbAttribMapValDal.GetMapValues(parmData.InbAttribCode);
//Insert -- test null value parms
parmData.MappedValue = INPUT_A;
//data.Descr = "DESCR";
parmData.ActiveFlag = "Y";
testIdInsert1 = inbAttribMapValDal.Insert(parmData);
Assert.AreNotEqual(0, testIdInsert1, getMessage("Row inserted - non-Zero Id returned."));
expectedValue = INPUT_A;
resultDataList = inbAttribMapValDal.GetMapValues("CPTY_SN");
Assert.IsTrue(resultDataList.Count > 0, getMessage("No rows returned after insert."));
bool foundRow = false;
foreach (InbAttribMapValDto mapVal in resultDataList)
{
if (mapVal.Id.Equals(testIdInsert1))
{
Assert.AreEqual(INPUT_A, mapVal.MappedValue, getMessage("MappedValue not found."));
foundRow = true;
break;
}
}
Assert.IsTrue(foundRow, getMessage("Row not found after insert."));
//Insert -- test non-null value parms
parmData.MappedValue = INPUT_B;
parmData.Descr = INPUT_B + "- DESCR";
parmData.ActiveFlag = "Y";
testIdInsert2 = inbAttribMapValDal.Insert(parmData);
Assert.AreNotEqual(0, testIdInsert2, getMessage("Row inserted - non-Zero Id returned."));
expectedValue = INPUT_B;
resultDataList = inbAttribMapValDal.GetMapValues("CPTY_SN");
Assert.IsTrue(resultDataList.Count > 0, getMessage("No rows returned after insert."));
foundRow = false;
foreach (InbAttribMapValDto mapVal in resultDataList)
{
if (mapVal.Id.Equals(testIdInsert2))
{
Assert.AreEqual(INPUT_B, mapVal.MappedValue, getMessage("MappedValue not found."));
foundRow = true;
break;
}
}
Assert.IsTrue(foundRow, getMessage("Row not found after insert."));
//Update
parmData.Id = testIdInsert1;
parmData.MappedValue = INPUT_C;
parmData.Descr = null;
parmData.ActiveFlag = "Y";
int rowsUpdated = inbAttribMapValDal.Update(parmData);
resultDataList = inbAttribMapValDal.GetMapValues("CPTY_SN");
Assert.IsTrue(resultDataList.Count > 0, getMessage("No rows returned after update."));
foundRow = false;
foreach (InbAttribMapValDto mapVal in resultDataList)
{
if (mapVal.Id.Equals(testIdInsert1))
{
Assert.AreEqual(INPUT_C, mapVal.MappedValue, getMessage("MappedValue not found."));
foundRow = true;
break;
}
}
Assert.IsTrue(foundRow, getMessage("Row not found after update."));
//Update
parmData.Id = testIdInsert2;
parmData.MappedValue = INPUT_D;
parmData.Descr = "Test data: " + INPUT_D;
parmData.ActiveFlag = "Y";
rowsUpdated = inbAttribMapValDal.Update(parmData);
resultDataList = inbAttribMapValDal.GetMapValues("CPTY_SN");
Assert.IsTrue(resultDataList.Count > 0, getMessage("No rows returned after update."));
foundRow = false;
foreach (InbAttribMapValDto mapVal in resultDataList)
{
if (mapVal.Id.Equals(testIdInsert2))
{
Assert.AreEqual(INPUT_D, mapVal.MappedValue, getMessage("MappedValue not found."));
foundRow = true;
break;
}
}
Assert.IsTrue(foundRow, getMessage("Row not found after update."));
//Delete
rowsUpdated = inbAttribMapValDal.Delete(testIdInsert1);
Assert.IsTrue(rowsUpdated == 1, getMessage("Row not deleted."));
rowsUpdated = inbAttribMapValDal.Delete(testIdInsert2);
Assert.IsTrue(rowsUpdated == 1, getMessage("Row not deleted."));
}
