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