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