public UserFiltersOpsmgrDto Get(Int32 pId)
{
var result = new UserFiltersOpsmgrDto();
string sql = "select id, user_id, descr, filter_expr " +
"from " + DBUtils.SCHEMA_NAME + "v_user_filters " +
"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 = pId;
conn.Open();
using (SqlDataReader dataReader = cmd.ExecuteReader())
{
if (dataReader.HasRows)
{
while (dataReader.Read())
{
result.Id = DBUtils.HandleInt32IfNull(dataReader["id"].ToString());
result.UserId = dataReader["user_id"].ToString();
result.Descr = dataReader["descr"].ToString();
result.FilterExpr = dataReader["filter_expr"].ToString();
}
}
}
}
}
return(result);
}
public Int32 Update(UserFiltersOpsmgrDto pData)
{
Int32 rowsUpdated = 0;
string sql = "update " + DBUtils.SCHEMA_NAME + "user_filters_opsmgr " +
"set user_id = @user_id, descr = @descr, filter_expr = @filter_expr " +
" 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("@user_id", System.Data.SqlDbType.VarChar).Value = DBUtils.ValueStringOrDBNull(pData.UserId);
cmd.Parameters.Add("@descr", System.Data.SqlDbType.VarChar).Value = DBUtils.ValueStringOrDBNull(pData.Descr);
cmd.Parameters.Add("@filter_expr", System.Data.SqlDbType.VarChar).Value = DBUtils.ValueStringOrDBNull(pData.FilterExpr);
conn.Open();
rowsUpdated = cmd.ExecuteNonQuery();
}
}
return(rowsUpdated);
}
public Int32 Insert(UserFiltersOpsmgrDto pData)
{
Int32 newId = DBUtils.GetNextSequence(sqlConnStr, SEQ_NAME);
string sql = "Insert into " + DBUtils.SCHEMA_NAME + "user_filters_opsmgr " +
" (id, user_id, descr, filter_expr) " +
" Values " +
" (@id, @user_id, @descr, @filter_expr) ";
using (SqlConnection conn = new SqlConnection(sqlConnStr))
{
using (SqlCommand cmd = new SqlCommand(sql, conn))
{
cmd.Parameters.Add("@id", System.Data.SqlDbType.Int).Value = newId;
cmd.Parameters.Add("@user_id", System.Data.SqlDbType.VarChar).Value = DBUtils.ValueStringOrDBNull(pData.UserId);
cmd.Parameters.Add("@descr", System.Data.SqlDbType.VarChar).Value = DBUtils.ValueStringOrDBNull(pData.Descr);
cmd.Parameters.Add("@filter_expr", System.Data.SqlDbType.VarChar).Value = DBUtils.ValueStringOrDBNull(pData.FilterExpr);
conn.Open();
cmd.ExecuteNonQuery();
}
}
return(newId);
}
public Int32 Update(UserFiltersOpsmgrDto pData)
{
Int32 rowsUpdated = 0;
string sql = "update " + DBUtils.SCHEMA_NAME + "user_filters_opsmgr " +
"set user_id = @user_id, descr = @descr, filter_expr = @filter_expr " +
" 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("@user_id", System.Data.SqlDbType.VarChar).Value = DBUtils.ValueStringOrDBNull(pData.UserId);
cmd.Parameters.Add("@descr", System.Data.SqlDbType.VarChar).Value = DBUtils.ValueStringOrDBNull(pData.Descr);
cmd.Parameters.Add("@filter_expr", System.Data.SqlDbType.VarChar).Value = DBUtils.ValueStringOrDBNull(pData.FilterExpr);
conn.Open();
rowsUpdated = cmd.ExecuteNonQuery();
}
}
return rowsUpdated;
}
public Int32 Insert(UserFiltersOpsmgrDto pData)
{
Int32 newId = DBUtils.GetNextSequence(sqlConnStr, SEQ_NAME);
string sql = "Insert into " + DBUtils.SCHEMA_NAME + "user_filters_opsmgr " +
" (id, user_id, descr, filter_expr) " +
" Values " +
" (@id, @user_id, @descr, @filter_expr) ";
using (SqlConnection conn = new SqlConnection(sqlConnStr))
{
using (SqlCommand cmd = new SqlCommand(sql, conn))
{
cmd.Parameters.Add("@id", System.Data.SqlDbType.Int).Value = newId;
cmd.Parameters.Add("@user_id", System.Data.SqlDbType.VarChar).Value = DBUtils.ValueStringOrDBNull(pData.UserId);
cmd.Parameters.Add("@descr", System.Data.SqlDbType.VarChar).Value = DBUtils.ValueStringOrDBNull(pData.Descr);
cmd.Parameters.Add("@filter_expr", System.Data.SqlDbType.VarChar).Value = DBUtils.ValueStringOrDBNull(pData.FilterExpr);
conn.Open();
cmd.ExecuteNonQuery();
}
}
return newId;
}
public UserFiltersOpsmgrDto Get(Int32 pId)
{
var result = new UserFiltersOpsmgrDto();
string sql = "select id, user_id, descr, filter_expr " +
"from " + DBUtils.SCHEMA_NAME + "v_user_filters " +
"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 = pId;
conn.Open();
using (SqlDataReader dataReader = cmd.ExecuteReader())
{
if (dataReader.HasRows)
{
while (dataReader.Read())
{
result.Id = DBUtils.HandleInt32IfNull(dataReader["id"].ToString());
result.UserId = dataReader["user_id"].ToString();
result.Descr = dataReader["descr"].ToString();
result.FilterExpr = dataReader["filter_expr"].ToString();
}
}
}
}
}
return result;
}
public void Test_UserFiltersOpsmgrDal()
{
messageSeqNo = 0;
UserFiltersOpsmgrDal userFiltersDal = new UserFiltersOpsmgrDal(sqlConnectionString);
List<UserFiltersOpsmgrDto> userFiltersList = new List<UserFiltersOpsmgrDto>();
userFiltersList = userFiltersDal.GetAll("IFRANKEL");
//expectedCount = 6;
//actualCount = userFiltersList.Count;
Assert.AreEqual(expectedCount, actualCount, "Selected list count.");
expectedValue = "Need Contracts Assigned";
foreach (UserFiltersOpsmgrDto filterData in userFiltersList)
{
if (filterData.Id == 9002)
{
actualValue = filterData.Descr;
}
}
Assert.AreEqual(expectedValue, actualValue, "Selected Filter Description.");
UserFiltersOpsmgrDto userFiltersData = new UserFiltersOpsmgrDto();
userFiltersData.UserId = "IFRANKEL";
userFiltersData.Descr = "TEST UNIT 01";
userFiltersData.FilterExpr = "[SetcMeth] = 'Our Paper' And [SetcStatus] = 'OK_TO_SEND'";
Int32 filterId = 0;
filterId = userFiltersDal.Insert(userFiltersData);
Assert.AreNotEqual(0, filterId, "Row was inserted");
userFiltersData = userFiltersDal.Get(filterId);
expectedValue = "TEST UNIT 01";
actualValue = userFiltersData.Descr;
Assert.AreEqual(expectedValue, actualValue, "Inserted Filter Row.");
userFiltersData.Id = filterId;
userFiltersData.UserId = "IFRANKEL";
userFiltersData.Descr = "TEST UNIT 99";
userFiltersData.FilterExpr = "[BkrMeth] = 'Broker Paper'";
int rowsUpdated = 0;
rowsUpdated = userFiltersDal.Update(userFiltersData);
Assert.AreEqual(1, rowsUpdated, "User Filter Row Updated.");
userFiltersData = userFiltersDal.Get(filterId);
expectedValue = "TEST UNIT 99";
actualValue = userFiltersData.Descr;
Assert.AreEqual(expectedValue, actualValue, "User Filter Descr Updated.");
expectedValue = "[BkrMeth] = 'Broker Paper'";
actualValue = userFiltersData.FilterExpr;
Assert.AreEqual(expectedValue, actualValue, "User Filter FilterExpr Updated.");
int rowsDeleted = 0;
rowsDeleted = userFiltersDal.Delete(filterId);
Assert.AreEqual(1, rowsDeleted, "User Filter Row Deleted.");
userFiltersData = userFiltersDal.Get(10);
Assert.AreEqual(0, userFiltersData.Id, "User Filter Row Successfully Deleted.");
}
