// GHCDataAccessLayer dataLayer = null;
////CentralLogger log = null;
//// DATABASE = "CLARITY";
//public UtilityDataLayer()
//{
// string database = ConfigurationManager.AppSettings["database"];
// dataLayer = GHCDataAccessLayerFactory.GetDataAccessLayer(DataProviderType.Sql, database);
// // dataLayer = GHCDataAccessLayerFactory.GetDataAccessLayer(DBEnvironment.PROD, DataProviderType.Sql, DATABASE);
//}
public static List <MemberPlan> GetMemberPlanID(string PAT_MRN_ID)
{
GHCDataAccessLayer dataLayer = GHCDataAccessLayerFactory.GetDataAccessLayer(DataProviderType.Sql, DATABASE);
string sql = "fw.dbo.proc_GetMemberPlanID";
SqlParameter[] sqlParams =
{
new SqlParameter("@PAT_MRN_ID", SqlDbType.VarChar)
{
Value = PAT_MRN_ID
}
};
DataTable dt = dataLayer.ExecuteDataSet(sql, CommandType.StoredProcedure, 0, sqlParams).Tables[0];
var members = dt.AsEnumerable().Select(dr => new MemberPlan
{
MemberNumber = dr.Field <string>("MEMBER_NUMBER"),
PlanID = dr.Field <decimal>("PLAN_ID"),
PlanName = dr.Field <string>("PLAN_NAME")
}).ToList();
return(members);
}
public void GetDataAccessLayer_ByConnectionStringAndType()
{
// arrange
string connectionString = "PERSIST SECURITY INFO=FALSE;INTEGRATED SECURITY=SSPI;DATA SOURCE=ASCLARITYDEV;INITIAL CATALOG=CLARITY;CONNECT TIMEOUT=6000";
// act
GHCDataAccessLayer dataAccess = GHCDataAccessLayerFactory.GetDataAccessLayer(connectionString, DataProviderType.Sql);
// assert
Assert.IsInstanceOfType(dataAccess, typeof(GHCDataAccessLayer));
}
public void ExecuteDataReader_ByTextAndType()
{
// arrange
string sql = "SELECT TOP 1 * FROM clarity.dbo.PATIENT";
string database = "CLARITY";
GHCDataAccessLayer dataAccess = GHCDataAccessLayerFactory.GetDataAccessLayer(DataProviderType.Sql, database);
// act
DbDataReader dr = (DbDataReader)dataAccess.ExecuteDataReader(sql, CommandType.Text);
// assert
Assert.IsTrue(dr.HasRows, "0 rows returned, 1 expected");
}
public void ExecuteScalar_ByTextAndType()
{
// arrange
string sql = "SELECT TOP 1 city FROM clarity.dbo.PATIENT WHERE city IS NOT NULL and LEN(city) > 0";
string database = "CLARITY";
GHCDataAccessLayer dataAccess = GHCDataAccessLayerFactory.GetDataAccessLayer(DataProviderType.Sql, database);
// act
string value = dataAccess.ExecuteScalar(sql, CommandType.Text).ToString();
// assert
Assert.IsTrue(value.Length > 0, "Returned value is below expected length");
}
public void ExecuteDataSet_ByTextAndType()
{
// arrange
string sql = "SELECT TOP 1 * FROM clarity.dbo.PATIENT";
string database = "CLARITY";
GHCDataAccessLayer dataAccess = GHCDataAccessLayerFactory.GetDataAccessLayer(DataProviderType.Sql, database);
// act
DataSet ds = dataAccess.ExecuteDataSet(sql, CommandType.Text);
// assert
Assert.IsTrue(ds.Tables[0].Rows.Count > 0, "0 rows returned, 1 expected");
}
public void ExecuteQuery_ByTextAndType()
{
// arrange
string sql = "UPDATE fw.dbo.DIMENSION SET ATTR_DESCRIPTION = ATTR_DESCRIPTION WHERE ATTR_NAME = 'UTGDA'";
string database = "CLARITY";
int expected = 1;
GHCDataAccessLayer dataAccess = GHCDataAccessLayerFactory.GetDataAccessLayer(DataProviderType.Sql, database);
// act
int actual = dataAccess.ExecuteQuery(sql, CommandType.Text);
// assert
Assert.AreEqual(actual, expected, "Unexpected count of affected records");
}
public void GetDataAccessLayer_ByTypeAndDatabase()
{
// arrange
string database = "CLARITY";
string expected = "PERSIST SECURITY INFO=FALSE;INTEGRATED SECURITY=SSPI;DATA SOURCE=ASCLARITYDEV;INITIAL CATALOG=CLARITY;CONNECT TIMEOUT=6000";
// act
GHCDataAccessLayer dataAccess = GHCDataAccessLayerFactory.GetDataAccessLayer(DataProviderType.Sql, database);
// assert
string actual = dataAccess.ConnectionString;
Assert.AreEqual(expected, actual, "ConnectionString does not match expected");
}
public void BadgerCareProd_ConnectionString()
{
// arrange
string database = "BADGERCARE";
DBEnvironment environment = DBEnvironment.PROD;
string expected = "PERSIST SECURITY INFO=FALSE;INTEGRATED SECURITY=SSPI;DATA SOURCE=ASCLARITY;INITIAL CATALOG=BADGERCARE;CONNECT TIMEOUT=6000";
// act
GHCDataAccessLayer dataAccess = GHCDataAccessLayerFactory.GetDataAccessLayer(environment, DataProviderType.Sql, database);
// assert
string actual = dataAccess.ConnectionString;
Assert.AreEqual(expected, actual, "BADGERCAREPROD connection string does not match");
}
public void RxClaimDev_ConnectionString()
{
// arrange
string database = "RXCLAIM";
DBEnvironment environment = DBEnvironment.DEV;
string expected = "PERSIST SECURITY INFO=FALSE;INTEGRATED SECURITY=SSPI;DATA SOURCE=SQLDEV1;INITIAL CATALOG=RXCLAIM;CONNECT TIMEOUT=6000";
// act
GHCDataAccessLayer dataAccess = GHCDataAccessLayerFactory.GetDataAccessLayer(environment, DataProviderType.Sql, database);
// assert
string actual = dataAccess.ConnectionString;
Assert.AreEqual(expected, actual, "RXCLAIMDEV connection string does not match");
}
public static string GetRxGroup(string PAT_MRN_ID)
{
GHCDataAccessLayer dataLayer = GHCDataAccessLayerFactory.GetDataAccessLayer(DataProviderType.Sql, DATABASE);
string sql = "fw.dbo.proc_GetRxGroup";
SqlParameter[] sqlParams =
{
new SqlParameter("@PAT_MRN_ID", SqlDbType.VarChar)
{
Value = PAT_MRN_ID
}
};
return(dataLayer.ExecuteDataSet(sql, CommandType.StoredProcedure, 0, sqlParams).Tables[0].AsEnumerable().Select(dr => dr.Field <string>("RX_GROUP")).First());
}
public void ExecuteDataReader_ByTextAndParams()
{
// arrange
string sql = "SELECT TOP 1 * FROM clarity.dbo.PATIENT WHERE City = @City";
string database = "CLARITY";
SqlParameter[] sqlParams =
{
new SqlParameter("@City", SqlDbType.VarChar)
{
Value = "MADISON"
}
};
GHCDataAccessLayer dataAccess = GHCDataAccessLayerFactory.GetDataAccessLayer(DataProviderType.Sql, database);
// act
DbDataReader dr = (DbDataReader)dataAccess.ExecuteDataReader(sql, sqlParams);
// assert
Assert.IsTrue(dr.HasRows, "0 rows returned, 1 expected");
}
public void ExecuteScalar_ByTextAndParams()
{
// arrange
string sql = "SELECT TOP 1 city FROM clarity.dbo.PATIENT WHERE city IS NOT NULL AND LEN(city) > 0 AND city = @City";
string database = "CLARITY";
GHCDataAccessLayer dataAccess = GHCDataAccessLayerFactory.GetDataAccessLayer(DataProviderType.Sql, database);
SqlParameter[] sqlParams =
{
new SqlParameter("@City", SqlDbType.VarChar)
{
Value = "MADISON"
}
};
// act
string value = dataAccess.ExecuteScalar(sql, sqlParams).ToString();
// assert
Assert.IsTrue(value.Length > 0, "Returned value is below expected length");
}
public void ExecuteQuery_ByTextAndParams()
{
// arrange
string sql = "UPDATE fw.dbo.DIMENSION SET ATTR_DESCRIPTION = ATTR_DESCRIPTION WHERE ATTR_NAME = @Name";
string database = "CLARITY";
int expected = 1;
SqlParameter[] sqlParams =
{
new SqlParameter("@Name", SqlDbType.VarChar)
{
Value = "UTGDA"
}
};
GHCDataAccessLayer dataAccess = GHCDataAccessLayerFactory.GetDataAccessLayer(DataProviderType.Sql, database);
// act
int actual = dataAccess.ExecuteQuery(sql, sqlParams);
// assert
Assert.AreEqual(actual, expected, "Unexpected count of affected records");
}
public void ExecuteDataSet_All()
{
// arrange
string sql = "SELECT TOP 1 * FROM clarity.dbo.PATIENT WHERE City = @City";
string database = "CLARITY";
int timeout = 0;
SqlParameter[] sqlParams =
{
new SqlParameter("@City", SqlDbType.VarChar)
{
Value = "MADISON"
}
};
GHCDataAccessLayer dataAccess = GHCDataAccessLayerFactory.GetDataAccessLayer(DataProviderType.Sql, database);
// act
DataSet ds = dataAccess.ExecuteDataSet(sql, CommandType.Text, timeout, sqlParams);
// assert
Assert.IsTrue(ds.Tables[0].Rows.Count > 0, "0 rows returned, 1 expected");
}
public DataLoader()
{
dataLayer = GHCDataAccessLayerFactory.GetDataAccessLayer(DataProviderType.Sql, "CLARITY");
}
public ConcurrentConnections()
{
// establish data layer
dataLayer = GHCDataAccessLayerFactory.GetDataAccessLayer(DataProviderType.Sql, "CLARITY");
}