private DataRow GetClaimByClaimNumber(string claimNumber, string claimStatus)
{
DataRow record = null;
string sql = "SELECT ClaimID, ClaimNumber FROM fw.dbo.AccumulatorImport WHERE ClaimNumber = @CLAIM_NUMBER AND ClaimStatus = @CLAIM_STATUS";
SqlParameter[] sqlParams =
{
new SqlParameter("@CLAIM_NUMBER", SqlDbType.VarChar)
{
Value = claimNumber
},
new SqlParameter("@CLAIM_STATUS", SqlDbType.VarChar)
{
Value = claimStatus
}
};
DataTable dt = dataLayer.ExecuteDataSet(sql, CommandType.Text, 0, sqlParams).Tables[0];
if (dt.Rows.Count > 0)
{
record = dt.Rows[0];
}
return(record);
}
// 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 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");
}
/// <summary>
/// There is a bug in GHCDataAccess where parameters passed into a execute method remain
/// even if the next execute does not use parameters. This method will simulate this.
/// </summary>
/// <returns></returns>
public bool TestConsecutiveStatements()
{
string sql = "";
Console.WriteLine("Executing query without parameters...");
sql = "fw.dbo.GetAccumulatorImportBatchID";
Console.WriteLine(dataLayer.ExecuteScalar(sql, CommandType.StoredProcedure, 0, null).ToString());
Console.WriteLine("Executing query with parameters...");
sql = "SELECT TOP 10 MemberNumber, ClaimNumber " +
"FROM fw.dbo.AccumulatorImportRemediation " +
"WHERE Disposition = @Disposition AND PlanType = @PlanType";
SqlParameter[] sqlParams =
{
new SqlParameter("@Disposition", SqlDbType.VarChar)
{
Value = "VALID"
},
new SqlParameter("@PlanType", SqlDbType.VarChar)
{
Value = "HSA"
}
};
DataTable firstTable = dataLayer.ExecuteDataSet(sql, CommandType.Text, 0, sqlParams).Tables[0];
Console.WriteLine("Retrieved " + firstTable.Rows.Count + " records!");
Console.WriteLine("Executing query without parameters...");
//sql = "SELECT TOP 10 MemberNumber, ClaimNumber " +
// "FROM fw.dbo.AccumulatorImportRemediation " +
// "WHERE Disposition = 'INELIGIBLE' AND ClaimStatus = 'PAID'";
sql = "fw.dbo.GetAccumulatorImportBatchID";
//Console.WriteLine(dataLayer.ExecuteScalar(sql).ToString()); // WORKED!
// Throws error: {"Procedure GetAccumulatorImportBatchID has no parameters and arguments were supplied."}
Console.WriteLine(dataLayer.ExecuteScalar(sql, CommandType.StoredProcedure, 0, null).ToString());
//DataTable secondTable = dataLayer.ExecuteDataSet(sql).Tables[0];
//Console.WriteLine("Retrieved " + secondTable.Rows.Count + " records!");
return(true);
}
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 ExecuteDataSet_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
DataSet ds = dataAccess.ExecuteDataSet(sql, sqlParams);
// assert
Assert.IsTrue(ds.Tables[0].Rows.Count > 0, "0 rows returned, 1 expected");
}