/// <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 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 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");
}