public Int32 Insert(InbAttribMapPhraseDto pData)
{
Int32 newId = DBUtils.GetNextSequence(sqlConnStr, SEQ_NAME);
string sql = "Insert into " + DBUtils.SCHEMA_NAME + "inb_attrib_map_phrase " +
" (id, inb_attrib_map_val_id, phrase, active_flag) " +
" Values " +
" (@id, @inb_attrib_map_val_id, @phrase, @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_map_val_id", System.Data.SqlDbType.Int).Value = pData.InbAttribMapValId;
cmd.Parameters.Add("@phrase", System.Data.SqlDbType.VarChar).Value = DBUtils.ValueStringOrDBNull(pData.Phrase);
cmd.Parameters.Add("@active_flag", System.Data.SqlDbType.VarChar).Value = DBUtils.ValueStringOrDBNull(pData.ActiveFlag);
conn.Open();
cmd.ExecuteNonQuery();
}
}
return(newId);
}
public Int32 Update(InbAttribMapPhraseDto pData)
{
Int32 rowsUpdated = 0;
string sql = "update " + DBUtils.SCHEMA_NAME + "inb_attrib_map_phrase " +
"set inb_attrib_map_val_id = @inb_attrib_map_val_id, phrase = @phrase, 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_map_val_id", System.Data.SqlDbType.Int).Value = pData.InbAttribMapValId;
cmd.Parameters.Add("@phrase", System.Data.SqlDbType.VarChar).Value = DBUtils.ValueStringOrDBNull(pData.Phrase);
cmd.Parameters.Add("@active_flag", System.Data.SqlDbType.VarChar).Value = DBUtils.ValueStringOrDBNull(pData.ActiveFlag);
conn.Open();
SqlTransaction trans = conn.BeginTransaction();
try
{
cmd.Transaction = trans;
rowsUpdated = cmd.ExecuteNonQuery();
trans.Commit();
}
catch (Exception e)
{
trans.Rollback();
//throw;
}
}
}
return(rowsUpdated);
}
private void UpdateMappedPhrase(InbAttribMapPhraseDto pData)
{
bool isUpdate = pData.Id > 0;
DataRow row = null;
try
{
InbAttribMapPhraseDal inbAttribMapPhraseDal = new InbAttribMapPhraseDal(sqlConnectionString);
int methodResult = 0;
if (isUpdate)
{
methodResult = inbAttribMapPhraseDal.Update(pData);
}
else if (pData.ActiveFlag.Equals("N"))
{
methodResult = inbAttribMapPhraseDal.Delete(pData.Id);
}
else
{
methodResult = inbAttribMapPhraseDal.Insert(pData);
}
if (methodResult > 0)
{
// update grid view with new row.
if (tblPhrases == null)
{
InitPhraseTable();
}
if (isUpdate)
{
row = tblPhrases.Rows.Find(pData.Id);
if (row != null)
{
tblPhrases.Rows[tblPhrases.Rows.IndexOf(row)].Delete();
}
}
else
{
row = tblPhrases.NewRow();
row["ID"] = methodResult;
row["Mapped Attribute ID"] = pData.InbAttribMapValId;
row["Phrase"] = pData.Phrase;
row["Active Flag"] = pData.ActiveFlag;
tblPhrases.Rows.Add(row);
}
}
else
{
throw new Exception("No Attribute changes were made to the database.");
}
}
catch (Exception err)
{
XtraMessageBox.Show("An error occurred while attempting to perform the requested update." + Environment.NewLine +
"Error CNF-301 in " + FORM_NAME + ".UpdateMappedPhrase(): " + err.Message,
FORM_ERROR_CAPTION, MessageBoxButtons.OK, MessageBoxIcon.Error);
}
}
private void btnMapPhraseSave_Click(object sender, EventArgs e)
{
if (lkupMappedValue.Text.Equals(""))
{
return;
}
DataRow row = ((System.Data.DataRowView)(lkupMappedValue.EditValue)).Row;
string phrase = tedPhrase.Text.Trim();
InbAttribMapPhraseDto mapPhraseData = new InbAttribMapPhraseDto();
mapPhraseData.ActiveFlag = "Y";
mapPhraseData.InbAttribMapValId = Convert.ToInt32(row["ID"].ToString());
mapPhraseData.Phrase = phrase;
mapPhraseData.Id = 0;
if (row == null || phrase.Trim().Equals(""))
{
return;
}
UpdateMappedPhrase(mapPhraseData);
}
public Int32 Insert(InbAttribMapPhraseDto pData)
{
Int32 newId = DBUtils.GetNextSequence(sqlConnStr, SEQ_NAME);
string sql = "Insert into " + DBUtils.SCHEMA_NAME + "inb_attrib_map_phrase " +
" (id, inb_attrib_map_val_id, phrase, active_flag) " +
" Values " +
" (@id, @inb_attrib_map_val_id, @phrase, @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_map_val_id", System.Data.SqlDbType.Int).Value = pData.InbAttribMapValId;
cmd.Parameters.Add("@phrase", System.Data.SqlDbType.VarChar).Value = DBUtils.ValueStringOrDBNull(pData.Phrase);
cmd.Parameters.Add("@active_flag", System.Data.SqlDbType.VarChar).Value = DBUtils.ValueStringOrDBNull(pData.ActiveFlag);
conn.Open();
cmd.ExecuteNonQuery();
}
}
return newId;
}
public Int32 Update(InbAttribMapPhraseDto pData)
{
Int32 rowsUpdated = 0;
string sql = "update " + DBUtils.SCHEMA_NAME + "inb_attrib_map_phrase " +
"set inb_attrib_map_val_id = @inb_attrib_map_val_id, phrase = @phrase, 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_map_val_id", System.Data.SqlDbType.Int).Value = pData.InbAttribMapValId;
cmd.Parameters.Add("@phrase", System.Data.SqlDbType.VarChar).Value = DBUtils.ValueStringOrDBNull(pData.Phrase);
cmd.Parameters.Add("@active_flag", System.Data.SqlDbType.VarChar).Value = DBUtils.ValueStringOrDBNull(pData.ActiveFlag);
conn.Open();
SqlTransaction trans = conn.BeginTransaction();
try
{
cmd.Transaction = trans;
rowsUpdated = cmd.ExecuteNonQuery();
trans.Commit();
}
catch (Exception e)
{
trans.Rollback();
//throw;
}
}
}
return rowsUpdated;
}
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."));
}
