I have stored Procedure for retriving counts of old or new patients.My stored Procedure works correctly.Now I've to write DAL file,BAL file using this stored procedure.But i'm in stage just i fully confused now.can you help me guys?I have to use MySqlDataReader for return counts of patients.
CREATE DEFINER=`root`@`%` PROCEDURE `GetPatientsCount`(IN PDID INT, IN PDATE DATE , IN UTYPE VARCHAR(20))
BEGIN
IF PDID > 0
THEN
SELECT
if(DATE_FORMAT(P.firstVisitDate, '%Y-%m-%d') = PDATE,
'New Patient',
'Old Patient')
AS UserType,
count(
if(
DATE_FORMAT(P.firstVisitDate, '%Y-%m-%d') = PDATE,
'New Patient',
'Old Patient')) as Cnt
FROM commonpersoninfo AS C,
appointment_has_doctor AS AD,
appointment AS A,
symptoms AS S,
personinfo P
WHERE A.pkey = AD.Appointment_pkey
AND C.PKEY = A.PatientInfo_PKey
AND AD.DoctorsInfo_pkey = PDID
AND S.CommonPersonInfo_pkey = C.pkey
AND DATE_FORMAT(a.STARTDATETIME, '%Y-%m-%d') = PDATE
AND P.CommonPersonInfo_pkey = C.pkey
AND if(
DATE_FORMAT(P.firstVisitDate, '%Y-%m-%d') = PDATE,
'New Patient',
'Old Patient') = UTYPE
GROUP BY UserType;
ELSE
SELECT if(DATE_FORMAT(P.firstVisitDate, '%Y-%m-%d') = PDATE,
'New Patient',
'Old Patient')
AS UserType,
count(
if(
DATE_FORMAT(P.firstVisitDate, '%Y-%m-%d') = PDATE,
'New Patient',
'Old Patient')) as Cnt
FROM commonpersoninfo AS C,
appointment_has_doctor AS AD,
appointment AS A,
symptoms AS S,
personinfo P
WHERE A.pkey = AD.Appointment_pkey
AND C.PKEY = A.PatientInfo_PKey
AND S.CommonPersonInfo_pkey = C.pkey
AND DATE_FORMAT(a.STARTDATETIME, '%Y-%m-%d') = PDATE
AND P.CommonPersonInfo_pkey = C.pkey
AND if(
DATE_FORMAT(P.firstVisitDate, '%Y-%m-%d') = PDATE,
'New Patient',
'Old Patient') = UTYPE
GROUP BY UserType;
END IF;
END
Chandradev PrasadPosted Jun 8, 2013, 7:56 AM
Do you want the suntax for fetching data from SP in DAL ? You can do using datatable or using datareader and also like this ,
If return value is more than one than fetch 2 count value in datatable. if you are returning one value then u can also do like this
using (SqlConnection con = new SqlConnection(Helper.GetConnectionString()))
{
using (SqlCommand cmd = new SqlCommand("StoredProcedure1", con))
{
con.Open();
cmd.CommandType = CommandType.StoredProcedure;
var flag = cmd.ExecuteScalar();
}
}
You also check this post. You will get the syntax here
http://chandradev819.wordpress.com/2012/06/17/how-to-create-4-tier-archecture-application-in-asp-net-with-c/