C# DataLayer.Execute方法代码示例

        public MediaGroupItem GetMediaGroupItem(string AssetID)
        {
            DataLayer dal = new DataLayer();
            dal.ConnectionString = ConnectionString;
            dal.Connect();

            SqlCommand cmd = new SqlCommand();
            cmd.CommandType = CommandType.StoredProcedure;
            cmd.CommandText = "GetMediaGroupItem";
            cmd.Parameters.AddWithValue("@AssetID", AssetID);

            DataTable dt = dal.Execute(cmd);

            foreach (DataRow dr in dt.Rows)
            {
                MediaGroupItem mediagroupitem = new MediaGroupItem
                {
                    MediaID = Int32.Parse(dr["MediaID"].ToString()),
                    AssetID = dr["AssetID"].ToString(),
                    FileName = dr["FileName"].ToString(),
                    PreviewImageURL = dr["PreviewImage"].ToString(),
                    FilePath = dr["FilePath"].ToString(),
                    PermanentLinkURL = dr["PermanentLink"].ToString(),
                    Sort = Int32.Parse(dr["Sort"].ToString())
                };

                return mediagroupitem;
            }

            return null;
        }

    public List<Insurance_DashBoard> GetDashBoardInsurance(long PatientID)
    {
        dl = new DataLayer();
        SqlCommand cmd = new SqlCommand();
        cmd.CommandType = CommandType.StoredProcedure;
        cmd.CommandText = "Portal_GetDashBoardInsuranceProviders";

        cmd.Parameters.AddWithValue("PatientID", PatientID);

        List<Insurance_DashBoard> Insurances = new List<Insurance_DashBoard>();
        DataTable dt = dl.Execute(cmd);

        foreach (DataRow dr in dt.Rows)
        {
            Insurance_DashBoard Insurance = new Insurance_DashBoard
            {
                ID = Int64.Parse(dr["ID"].ToString()),
                ProviderName = dr["ProviderName"].ToString(),
                InsuranceType = dr["InsuranceType"].ToString(),
                CreatedBy = dr["CreatedBy"].ToString(),
                GroupNumber = dr["GroupNumber"].ToString(),
                PolicyNumber = dr["PolicyNumber"].ToString(),
                DateCreated = Convert.ToDateTime(dr["DateCreated"].ToString()),

            };

            Insurances.Add(Insurance);

        }

        return Insurances;
    }

        public List<MediaGroup> GetMediaGroups()
        {
            DataLayer dal = new DataLayer();
            dal.ConnectionString = ConectionString;
            dal.Connect();

            SqlCommand cmd = new SqlCommand();
            cmd.CommandType = CommandType.StoredProcedure;
            cmd.CommandText = "GetMediaGroups";

            DataTable dt = dal.Execute(cmd);
            List<MediaGroup> MediaGroups = new List<MediaGroup>();

            foreach (DataRow dr in dt.Rows)
            {
                MediaGroup mediagroup = new MediaGroup
                {
                    MediaID = Int32.Parse(dr["MediaID"].ToString()),
                    MediaTitle = dr["MediaTitle"].ToString(),
                    MediaType = dr["MediaType"].ToString(),
                    DateCreated = DateTime.Parse(dr["DateCreated"].ToString())
                };

                MediaGroups.Add(mediagroup);

            }
            return MediaGroups;
        }

    public List<EventLink> GetEventLinks(Int64 EventID)
    {
        dl = new DataLayer(pConnectionString);
        SqlCommand cmd = new SqlCommand();
        cmd.CommandType = CommandType.StoredProcedure;
        cmd.CommandText = "[GetEventsLinks]";
        cmd.Parameters.AddWithValue("@EventID", EventID);

        List<EventLink> Links = new List<EventLink>();
        DataTable dt = dl.Execute(cmd);

        foreach (DataRow dr in dt.Rows)
        {
            EventLink link = new EventLink
            {
                LinkID = Int64.Parse(dr["LinkID"].ToString()),

                LinkURL = dr["LinkURL"].ToString(),

                LinkDescription = dr["LinkDescription"].ToString(),

            };

            Links.Add(link);

        }

        return Links;
    }

    public HopeEvent GetEvent(Int64 EventID )
    {
        dl = new DataLayer(pConnectionString);
        SqlCommand cmd = new SqlCommand();
        cmd.CommandType = CommandType.StoredProcedure;
        cmd.CommandText = "GetEvent";
        cmd.Parameters.AddWithValue("@EventID", EventID);

         DataTable dt = dl.Execute(cmd);

        foreach (DataRow dr in dt.Rows)
        {
            HopeEvent the_event = new HopeEvent
            {
                ID = Int64.Parse(dr["ID"].ToString()),

                EventName = dr["EventName"].ToString(),
                ShortDescription = dr["ShortDescription"].ToString(),
                LongDescription = dr["LongDescription"].ToString(),
                AdditionalInformation = dr["AdditionalInformation"].ToString(),
                RegistrationInformation = dr["RegistrationInformation"].ToString(),
                Sort = Int32.Parse(dr["Sort"].ToString()),
                DateCreated = Convert.ToDateTime(dr["DateCreated"].ToString())

            };

            return the_event;

        }

        return null;
    }

    public List<Patient_Contact> GetDashBoardContacts(long PatientID)
    {
        dl = new DataLayer();
        SqlCommand cmd = new SqlCommand();
        cmd.CommandType = CommandType.StoredProcedure;
        cmd.CommandText = "Portal_GetDashBoardContacts";
        cmd.Parameters.AddWithValue("PatientID", PatientID);

        List<Patient_Contact> contacts = new List<Patient_Contact>();
        DataTable dt = dl.Execute(cmd);

        foreach (DataRow dr in dt.Rows)
        {
            Patient_Contact contact = new Patient_Contact
            {
                ID = Int64.Parse(dr["ID"].ToString()),
                ContactName = dr["ContactName"].ToString(),
                ContactType = dr["ContactType"].ToString(),
                ContactPhoneNumber = dr["PhoneNumber"].ToString(),
                Emergency = bool.Parse(dr["Emergency"].ToString())

            };

            contacts.Add(contact);

        }

        return contacts;
    }

    public List<Patient_ContactPhone> GetDashBoardContactPhones(long PatientID)
    {
        dl = new DataLayer();
        SqlCommand cmd = new SqlCommand();
        cmd.CommandType = CommandType.StoredProcedure;
        cmd.CommandText = "Portal_GetDashBoardPatientContactNumbers";
        cmd.Parameters.AddWithValue("PatientID", PatientID);

        List<Patient_ContactPhone> contacts = new List<Patient_ContactPhone>();
        DataTable dt = dl.Execute(cmd);

        foreach (DataRow dr in dt.Rows)
        {
            Patient_ContactPhone phone = new Patient_ContactPhone
            {
                PhoneNumber = dr["PhoneNumber"].ToString(),
                PhoneType = dr["PhoneType"].ToString(),
                DateCreated = DateTime.Parse(dr["DateCreated"].ToString()),

            };

            contacts.Add(phone);

        }

        return contacts;
    }

        public MediaGroup GetMediaGroup(int MediaID)
        {
            DataLayer dal = new DataLayer();
            dal.ConnectionString = ConnectionString;
            dal.Connect();

            SqlCommand cmd = new SqlCommand();
            cmd.CommandType = CommandType.StoredProcedure;
            cmd.CommandText = "GetMediaGroup";

            cmd.Parameters.AddWithValue("@MediaID", MediaID);

            DataTable dt = dal.Execute(cmd);

            DataRow dr = dt.Rows[0];
            return new MediaGroup
            {
                MediaID = Int32.Parse(dr["MediaID"].ToString()),
                MediaTitle = dr["MediaTitle"].ToString(),
                MediaType = dr["MediaType"].ToString(),
                ProductID = Int32.Parse(dr["ProductID"].ToString()),
                Active = bool.Parse(dr["Active"].ToString()),
                DateCreated = DateTime.Parse(dr["DateCreated"].ToString())
            };
        }

        public static DateTime GetMaxCustomerLastUpdateDate()
        {
            SqlCommand cmd = new SqlCommand();
            cmd.CommandType = CommandType.StoredProcedure;
            cmd.CommandText = "GetMaxCustomerLastUpdateDate";

            DataLayer dal = new DataLayer();
            dal.ConnectionString = Properties.Settings.Default.ConnectionString;
            dal.Connect();

            DataRow row = dal.Execute(cmd).Rows[0];

            return  DateTime.Parse(row["LastUpdated"].ToString());
        }

        public static int GetMaxOrderNo()
        {
            SqlCommand cmd = new SqlCommand();
            cmd.CommandType = CommandType.StoredProcedure;
            cmd.CommandText = "GetMaxOrderNo";

            DataLayer dal = new DataLayer();
            dal.ConnectionString = Properties.Settings.Default.ConnectionString;
            dal.Connect();

            DataRow row = dal.Execute(cmd).Rows[0];

            return Int32.Parse(row["OrderNo"].ToString());
        }

        public bool Save()
        {
            SqlCommand cmd = new SqlCommand();
            cmd.CommandType = CommandType.StoredProcedure;
            cmd.CommandText = "AddOrderPayment";

            cmd.Parameters.AddWithValue("@OrderNo", OrderNo);
            cmd.Parameters.AddWithValue("@CreditCardType", CreditCardType);
            cmd.Parameters.AddWithValue("@CreditCardNumber", CreditCardNumber);
            cmd.Parameters.AddWithValue("@CreditCardExpDate", CreditCardExpDate);
            cmd.Parameters.AddWithValue("@PaymentMethod", PaymentMethod);

            DataLayer dal = new DataLayer();
            dal.ConnectionString = Properties.Settings.Default.ConnectionString;
            dal.Connect();

            dal.Execute(cmd);

            return true;
        }

        public static bool EmailExists(string email)
        {
            SqlCommand cmd = new SqlCommand();
            cmd.CommandType = CommandType.StoredProcedure;
            cmd.CommandText = "IsExistingEmail";

            cmd.Parameters.AddWithValue("@Email", email);

            DataLayer dal = new DataLayer();
            dal.ConnectionString = ConectionString;
            dal.Connect();

            DataTable dt = dal.Execute(cmd);

            if (dt.Rows.Count > 0)
            {
                return true;
            }

            return false;
        }

    public void Portal_SavePatient(Patient patient, Referrer referrer)
    {
        dl = new DataLayer(pConnectionString);
        SqlCommand cmd = new SqlCommand();
        cmd.CommandType = CommandType.StoredProcedure;
        cmd.CommandText = "Portal_SavePatient";

        cmd.Parameters.AddWithValue("@ID", patient.ID);
        cmd.Parameters.AddWithValue("@FirstName", patient.FirstName);
        cmd.Parameters.AddWithValue("@LastName", patient.LastName);
        cmd.Parameters.AddWithValue("@DateOfBirth", patient.DateOfBirth);
        cmd.Parameters.AddWithValue("@Gender", patient.Gender);
        cmd.Parameters.AddWithValue("@Ethnicity", patient.Ethnicity);
        cmd.Parameters.AddWithValue("@MedicaidNumber", patient.MedicaidNumber);
        cmd.Parameters.AddWithValue("@SocialSecurityNumber", patient.MedicaidNumber);
        cmd.Parameters.AddWithValue("@ResidingWithName", patient.ResidingWithName);
        cmd.Parameters.AddWithValue("@ResidingWithRelationship", patient.ResidingWithRelationship);
        cmd.Parameters.AddWithValue("@SchoolName", patient.SchoolName);
        cmd.Parameters.AddWithValue("@SchoolGrade", patient.SchoolName);
        cmd.Parameters.AddWithValue("@SchoolPhone", patient.SchoolPhone);
        cmd.Parameters.AddWithValue("@EmployerName", patient.EmployerName);
        cmd.Parameters.AddWithValue("@Occupation", patient.Occupation);
        cmd.Parameters.AddWithValue("@ClientOnProbation", patient.ClientOnProbation);
        cmd.Parameters.AddWithValue("@EmailAddress", patient.EmailAddress);
        cmd.Parameters.AddWithValue("@Diagnosis", patient.Diagnosis);
        cmd.Parameters.AddWithValue("@ServiceRequested", patient.ServiceRequested);
        cmd.Parameters.AddWithValue("@LocationofServices", patient.LocationofServices);
        cmd.Parameters.AddWithValue("@FinanciallyResponsiblePerson", patient.FinanciallyResponsiblePerson);
        cmd.Parameters.AddWithValue("@PaymentMethod", patient.PaymentMethod);
        cmd.Parameters.AddWithValue("@Email", patient.EmailAddress);
        cmd.Parameters.AddWithValue("@ReferralSourceName", referrer.ReferralSourceName);
        cmd.Parameters.AddWithValue("@ReferralSourceAgency", referrer.ReferralSourceAgency);
        cmd.Parameters.AddWithValue("@ReferrerPhone", referrer.ReferrerPhone);
        cmd.Parameters.AddWithValue("@ReferralFax", referrer.ReferralFax);
        cmd.Parameters.AddWithValue("@ReferralStreet", referrer.Street);
        cmd.Parameters.AddWithValue("@ReferralSuite", referrer.Suite);
        cmd.Parameters.AddWithValue("@ReferralCity", referrer.City);
        cmd.Parameters.AddWithValue("@ReferralState", referrer.State);
        cmd.Parameters.AddWithValue("@ReferralZipCode", referrer.ZipCode);
        cmd.Parameters.AddWithValue("@ReferralCountry", referrer.Country);
        cmd.Parameters.AddWithValue("@PatientStreet", patient.PatientStreet);
        cmd.Parameters.AddWithValue("@PatientSuite", patient.PatientSuite);
        cmd.Parameters.AddWithValue("@PatientCity", patient.PatientCity);
        cmd.Parameters.AddWithValue("@PatientState", patient.PatientState);
        cmd.Parameters.AddWithValue("@PatientZipCode", patient.PatientZipCode);
        cmd.Parameters.AddWithValue("@PatientCountry", patient.PatientCountry);

        dl.Execute(cmd);

        return;
    }

    public int Portal_SaveTherapist(Therapist therapist, string LoggedInUserEmail)
    {
        dl = new DataLayer();
        SqlCommand cmd = new SqlCommand();
        cmd.CommandType = CommandType.StoredProcedure;
        cmd.CommandText = "Portal_SaveTherapist";

        cmd.Parameters.AddWithValue("@FirstName", therapist.FirstName);
        cmd.Parameters.AddWithValue("@LastName", therapist.LastName);
        cmd.Parameters.AddWithValue("@Gender", therapist.Gender);
        cmd.Parameters.AddWithValue("@TaxNumber", therapist.TaxNumber);
        cmd.Parameters.AddWithValue("@Qualification", therapist.Qualification);
        cmd.Parameters.AddWithValue("@LicenseNumber", therapist.LicenceNumber);
        cmd.Parameters.AddWithValue("@HiringDate", therapist.HiringDate);
        cmd.Parameters.AddWithValue("@TerminationDate", therapist.TerminationDate);
        cmd.Parameters.AddWithValue("@EmailAddress", therapist.EmailAddress);
        cmd.Parameters.AddWithValue("@Title", therapist.Title);
        cmd.Parameters.AddWithValue("@Bio", therapist.Bio);
        cmd.Parameters.AddWithValue("@MiddleInitial", therapist.MiddleInitial);
        cmd.Parameters.AddWithValue("@PictureURL", therapist.PictureURL);
        cmd.Parameters.AddWithValue("@NPINumber", therapist.NPINumber);
        cmd.Parameters.AddWithValue("@SpecialisedTraining", therapist.SpecialisedTraining);
        cmd.Parameters.AddWithValue("@Password", therapist.Password);
        cmd.Parameters.AddWithValue("@PhoneNUmber", therapist.PhoneNumber);
        cmd.Parameters.AddWithValue("@PhoneType", therapist.PhoneType);
        cmd.Parameters.AddWithValue("@DateofBirth", therapist.DateOfBirth);
        cmd.Parameters.AddWithValue("@Active", therapist.Active);
        cmd.Parameters.AddWithValue("@SavedBy", LoggedInUserEmail);

        DataTable dt = dl.Execute(cmd);

        DataRow dr = dt.Rows[0];

        return Int32.Parse(dr["RET"].ToString());
    }

    public bool Login(string email, string password)
    {
        dl = new DataLayer(pConnectionString);
        SqlCommand cmd = new SqlCommand();
        cmd.CommandType = CommandType.StoredProcedure;
        cmd.CommandText = "Portal_UserLogin";
        cmd.Parameters.AddWithValue("@email", email);
        cmd.Parameters.AddWithValue("@password", password);

        DataTable dt = dl.Execute(cmd);

        if (dt.Rows.Count > 0)
        {
            return true;
        }

        return false;
    }