C# DataAccess.CloseDatabase方法代码示例

    public void LoadIndicator(string Program, DropDownList dropdownIndicator)
    {
        data = new DataAccess();
        data.ConnectToDatabase();;

        try
        {
            SqlCommand cmdTxt = new SqlCommand("SELECT IndicatorData FROM Indicator WHERE ProgramCategoryID ="+
                "(SELECT ProgramCategoryID FROM ProgramCategory WHERE ProgramData = @Program)",data.Connection);
            cmdTxt.Parameters.Add("@Program", SqlDbType.Char).Value = Program;

            SqlDataReader dr = cmdTxt.ExecuteReader();
            while (dr.Read())
            {
                dropdownIndicator.Items.Add(dr.GetString(0).Trim());
            }
            dr.Close();

        }
        catch (Exception)
        {
        }
        finally
        {
            data.CloseDatabase();
        }
    }

 public string GetBarangayName(int BarangayID)
 {
     data = new DataAccess();
     data.ConnectToDatabase();;
     SqlCommand cmdTxt = new SqlCommand("SELECT BarangayName FROM Barangays WHERE BarangayID = @BarangayID",data.Connection);
     cmdTxt.Parameters.Add("@BarangayID", SqlDbType.VarChar).Value = BarangayID;
     SqlDataReader dr = cmdTxt.ExecuteReader();
     dr.Read();
     string BarangayName = dr.GetString(0);
     dr.Close();
     data.CloseDatabase();
     return BarangayName;
 }

 //Check Birthdate Also
 public bool HasSamePhilhealth(string PhilhealthNumber)
 {
     data = new DataAccess();
     data.ConnectToDatabase(); ;
     SqlCommand cmdTxt = new SqlCommand("SELECT COUNT(*) FROM Patients WHERE PtFaxNumber = @philhealthNum", data.Connection);
     cmdTxt.Parameters.Add("@philhealthNum", SqlDbType.VarChar).Value = PhilhealthNumber;
     int check = (int)cmdTxt.ExecuteScalar();
     data.CloseDatabase();
     if (check > 0)
         return true;
     else
         return false;
 }

 public int GetBarangayID(string BarangayName)
 {
     data = new DataAccess();
     data.ConnectToDatabase();;
     SqlCommand cmdTxt = new SqlCommand("SELECT BarangayID FROM Barangays WHERE BarangayName = @BarangayName",data.Connection);
     cmdTxt.Parameters.Add("@BarangayName", SqlDbType.VarChar).Value = BarangayName;
     SqlDataReader dr = cmdTxt.ExecuteReader();
     dr.Read();
     int id = dr.GetInt32(0);
     dr.Close();
     data.CloseDatabase();
     return id;
 }

    public int CountIndicatorPerProgram(string Program)
    {
        int count = 0;
        data = new DataAccess();
        data.ConnectToDatabase();;

        SqlCommand cmdTxt = new SqlCommand("SELECT COUNT(*) FROM Indicator WHERE ProgramCategoryID = " +
            "(SELECT ProgramCategoryID FROM ProgramCategory WHERE ProgramData = @data)",data.Connection);
        cmdTxt.Parameters.Add("@data", SqlDbType.VarChar).Value = Program;
        count = (int)cmdTxt.ExecuteScalar();
        data.CloseDatabase();
        return count;
    }

 public int GetMedicineQuantity(int MedicineId)
 {
     data = new DataAccess();
     data.ConnectToDatabase();
     
     SqlCommand cmdTxt = new SqlCommand("SELECT Quantity FROM Medicine WHERE MedicineId = @aa",data.Connection);
     cmdTxt.Parameters.Add("@aa", SqlDbType.Int).Value = MedicineId;
     SqlDataReader id = cmdTxt.ExecuteReader();
     id.Read();
     int quantity = Int32.Parse(id["Quantity"].ToString());
     id.Close();
     data.CloseDatabase();
     return quantity;
 }

 public int GetMedicineId(string MedicineName)
 {
     data = new DataAccess();
     data.ConnectToDatabase();
     
     SqlCommand cmdTxt = new SqlCommand("SELECT MedicineId FROM Medicine WHERE MedicineName = @aa",data.Connection);
     cmdTxt.Parameters.Add("@aa", SqlDbType.Char).Value = MedicineName;
     SqlDataReader id = cmdTxt.ExecuteReader();
     id.Read();
     int retId = id.GetInt32(0);
     id.Close();
     data.CloseDatabase();
     return retId;
 }

 public List<string> GetDiseaseCategory()
 {
     List<string> category;
     data = new DataAccess();
     data.ConnectToDatabase();
     category = new List<string>();
     SqlCommand cmdTxt = new SqlCommand("Select DiseaseCategoryName From DiseaseCategory",data.Connection);
     SqlDataReader dr = cmdTxt.ExecuteReader();
     while (dr.Read())
     {
         category.Add(dr.GetString(0).ToString().Trim());
     }
     dr.Close();
     data.CloseDatabase();
     return category;
 }

 public bool SavePatientDisease(string PatientID, string DiseaseName, DateTime encTimeSaved)
 {
     data = new DataAccess();
     data.ConnectToDatabase();;
     SqlCommand cmdTxt = new SqlCommand("SaveDiseases",data.Connection);
     cmdTxt.CommandType = CommandType.StoredProcedure;
     cmdTxt.Parameters.Add("@encDateTime", SqlDbType.DateTime).Value = encTimeSaved;
     cmdTxt.Parameters.Add("@DiseaseName", SqlDbType.VarChar).Value = DiseaseName;
     cmdTxt.Parameters.Add("@PatientID", SqlDbType.VarChar).Value = PatientID;
     int checker = cmdTxt.ExecuteNonQuery();
     data.CloseDatabase();
     if (checker > 0)
         return true;
     else
         return false;
 }

 public bool HasPopulationData(int Year, string Month, int BarangayID)
 {
     int count = 0;
     mc = new MonthConverter();
     data = new DataAccess();
     data.ConnectToDatabase();
     SqlCommand cmdTxt = new SqlCommand("SELECT COUNT(*) FROM Population WHERE Year = " +
         "@year AND BarangayID = @barangayID AND Month = @month", data.Connection);
     cmdTxt.Parameters.Add("@year", SqlDbType.Int).Value = Year;
     cmdTxt.Parameters.Add("@month", SqlDbType.Int).Value = mc.MonthNameToIndex(Month);
     cmdTxt.Parameters.Add("@barangayID", SqlDbType.Int).Value = BarangayID;
     count = (int)cmdTxt.ExecuteScalar();
     data.CloseDatabase();
     if (count > 0)
         return true;
     else
         return false;
 }

    /*Add Medicine Finished - Lakhi 10/10/2010*/

    public bool AddMedicine(string MedicineName, string CategoryName, int Quantity)
    {
        data = new DataAccess();
        data.ConnectToDatabase();
        
        SqlCommand cmdTxt = new SqlCommand("AddMedicine",data.Connection);
        
        cmdTxt.CommandType = CommandType.StoredProcedure;
        cmdTxt.Parameters.Add("@MedicineName", SqlDbType.Char).Value = MedicineName;
        cmdTxt.Parameters.Add("@Quantity", SqlDbType.Int).Value = Quantity;
        cmdTxt.Parameters.Add("@CategoryName", SqlDbType.Char).Value = CategoryName;
        
        int checker = cmdTxt.ExecuteNonQuery();
        data.CloseDatabase();
        if (checker > 0)
            return true;
        else
            return false;
    }

    public DataTable GetValuesConsultation(string Patient_Id)
    {
        data = new DataAccess();
        SqlDataReader dtrPatient;
        DataTable patientData = new DataTable();
        patientData.Columns.Add("PatientID");
        patientData.Columns.Add("PatientLName");
        patientData.Columns.Add("PatientFName");
        patientData.Columns.Add("PatientMName");
        patientData.Columns.Add("PatientBirthdate");
        patientData.Columns.Add("PatientAddress");
        patientData.Columns.Add("PatientFaxNumber");
        patientData.Columns.Add("PatientBarangay");
        data.ConnectToDatabase();;
        try
        {
            SqlCommand cmdTxt = new SqlCommand("GetPatientData2",data.Connection);
            cmdTxt.CommandType = CommandType.StoredProcedure;
            cmdTxt.Parameters.Add("@Patient_Id", SqlDbType.Char).Value = Patient_Id;
            dtrPatient = cmdTxt.ExecuteReader();
            dtrPatient.Read();

            patientData.Rows.Add(dtrPatient["PatientID"].ToString(),
                dtrPatient["PtLname"].ToString().Trim(),
                dtrPatient["PtFname"].ToString().Trim(),
                dtrPatient["PtMname"].ToString().Trim(),
                dtrPatient["PtBdate"].ToString().Trim(),

                dtrPatient["PtAddress"].ToString().Trim(),

                dtrPatient["PtFaxNumber"].ToString().Trim(),
                dtrPatient["BarangayName"].ToString().Trim());
        }
        catch (Exception)
        {
        }
        finally
        {
            data.CloseDatabase();
        }
        return patientData;
    }

    //Methods
    public bool AddPatient(string PatientFirstName, string PatientMiddleName, string PatientLastName,
        string PatientContactNumber, string PatientEmailAddress, string PatientSuffix, string PatientBirthdate, string PatientBirthplace, string PatientAddress,
        string PatientFaxNumber, string PatientDoctor, string PatientNationality, string PatientCity,
        string PatientSex, string PatientMaritalStatus, string PatientSpouseName, string PatientCompany, string DateRegistered, string PatientBarangay)
    {
        data = new DataAccess();
        data.ConnectToDatabase();
        SqlCommand cmdTxt = new SqlCommand("AddPatient",data.Connection);
        
        cmdTxt.CommandType = CommandType.StoredProcedure;
        cmdTxt.Parameters.Add("@PatientFirstName", SqlDbType.Char).Value = PatientFirstName;
        cmdTxt.Parameters.Add("@PatientMiddleName", SqlDbType.Char).Value = PatientMiddleName;
        cmdTxt.Parameters.Add("@PatientLastName", SqlDbType.Char).Value = PatientLastName;
        cmdTxt.Parameters.Add("@PatientContactNumber", SqlDbType.Char).Value = PatientContactNumber;
        cmdTxt.Parameters.Add("@PatientEmailAddress", SqlDbType.Char).Value = PatientEmailAddress;
        cmdTxt.Parameters.Add("@PatientNationality", SqlDbType.Char).Value = PatientNationality;
        cmdTxt.Parameters.Add("@PatientCity", SqlDbType.Char).Value = PatientCity;
        cmdTxt.Parameters.Add("@PatientBirthdate", SqlDbType.Char).Value = PatientBirthdate;
        cmdTxt.Parameters.Add("@PatientBirthPlace", SqlDbType.Char).Value = PatientBirthplace;
        cmdTxt.Parameters.Add("@PatientAddress", SqlDbType.Char).Value = PatientAddress;
        cmdTxt.Parameters.Add("@PatientFaxNumber", SqlDbType.Char).Value = PatientFaxNumber;
        cmdTxt.Parameters.Add("@PatientDoctor", SqlDbType.Char).Value = PatientDoctor;
        cmdTxt.Parameters.Add("@PatientSex", SqlDbType.Char).Value = PatientSex;
        cmdTxt.Parameters.Add("@PatientCompany", SqlDbType.Char).Value = PatientCompany;
        cmdTxt.Parameters.Add("@PatientMaritalStatus", SqlDbType.Char).Value = PatientMaritalStatus;
        cmdTxt.Parameters.Add("@PatientSpouseName", SqlDbType.Char).Value = PatientSpouseName;
        cmdTxt.Parameters.Add("@DateRegistered", SqlDbType.Char).Value = DateRegistered;
        cmdTxt.Parameters.Add("@PatientSuffix", SqlDbType.Char).Value = PatientSuffix;
        cmdTxt.Parameters.Add("@PatientBarangay", SqlDbType.Char).Value = PatientBarangay;
        cmdTxt.Parameters.Add("@DateYear", SqlDbType.Char).Value = DateTime.Now.Year.ToString();

        int checker = cmdTxt.ExecuteNonQuery();
        data.CloseDatabase();
        if (checker > 0)
            return true;
        else
            return false;
    }

    public void LoadCheckBoxList(CheckBoxList checkBoxDisease, string DiseaseCategory)
    {
        data = new DataAccess();
        data.ConnectToDatabase();
        try
        {
            SqlCommand cmdTxt = new SqlCommand("Select DiseaseName From Diseases Where DiseaseCategoryName = @DiseaseCategory",data.Connection);
            cmdTxt.Parameters.Add("@DiseaseCategory", SqlDbType.Char).Value = DiseaseCategory;

            SqlDataReader dr = cmdTxt.ExecuteReader();
            while (dr.Read())
            {
                checkBoxDisease.Items.Add(dr.GetString(0).ToString().Trim());
            }
            dr.Close();
        }
        catch (Exception)
        {
        }
        finally
        {
            data.CloseDatabase();
        }
    }

 public List<string> GetPatientsDisease(string PatientID, string encID)
 {
     List<string> patientDisease = new List<string>();
     data = new DataAccess();
     data.ConnectToDatabase();;
     SqlCommand cmdTxt = new SqlCommand("SELECT d.DiseaseName FROM PatientsDiseases AS pd INNER JOIN " +
                      "Diseases AS d ON pd.DiseaseID = d.DiseaseID WHERE (pd.PatientID = @PatientID) " +
                      "AND (pd.EncounterID = @encID)",data.Connection);
     cmdTxt.Parameters.Add("@encID", SqlDbType.Int).Value = Int32.Parse(encID);
     cmdTxt.Parameters.Add("@PatientID", SqlDbType.Int).Value = Int32.Parse(PatientID);
     SqlDataReader dr = cmdTxt.ExecuteReader();
     while (dr.Read())
     {
         patientDisease.Add(dr.GetString(0));
     }
     dr.Close();
     data.CloseDatabase();
     return patientDisease;
 }