/* * This function the get all business by zipcode * */ protected void btnTopThree_Click(object sender, EventArgs e) { lblMessage.Text = ""; try { using (LicenseEntities le = new LicenseEntities()) { GridView1.DataSource = null; GridView1.DataBind(); if (!String.IsNullOrWhiteSpace(ddlCity.SelectedValue)) { var licenses = le.LicenseDBs; //get result by zipcode var result = licenses.Where(n => n.City == ddlCity.SelectedValue).Take(3); // lvTopThree.DataSource = result.ToList(); GridView1.DataSource = result.ToList(); GridView1.DataBind(); } } } catch (Exception) { lblMessage.Text = "DataBase Connection Failed!"; } }
//This function will update user modified data protected void btnSubmit_Click(object sender, EventArgs e) { if (!String.IsNullOrWhiteSpace(txtFID.Text)) { lblMessage.Text = ""; using (LicenseEntities le = new LicenseEntities()) { try { int fid = Convert.ToInt32(txtFID.Text); var updataeLicense = le.LicenseDBs.FirstOrDefault(n => n.FID == fid); if (updataeLicense != null) { updataeLicense.Business_Name = txtBusiness_Name.Text; updataeLicense.Business_Phone_Number = txtBusiness_Phone_Number.Text; updataeLicense.City = txtCity.Text; updataeLicense.State = txtState.Text; updataeLicense.Zip = txtZip.Text; updataeLicense.License_Status = txtLicense_Status.Text; updataeLicense.Classification_Code = txtClassficationCode.Text; updataeLicense.Classification_Description = txtClassficationDes.Text; le.Entry(updataeLicense).State = System.Data.EntityState.Modified; le.SaveChanges(); lblMessage.Text = "Update Success!"; } } catch (Exception) { lblMessage.Text = "Update failed!"; } } } }
/** * This funciton will pull out the record based on the zipcode */ protected void btnSearchByZip_Click(object sender, EventArgs e) { lblMessage.Text = ""; try { //using entity framework to connect to database using (LicenseEntities le = new LicenseEntities()) { //reset the Grid View gvSearchByZip.DataSource = null; gvSearchByZip.DataBind(); //check any exist input by user if (!String.IsNullOrWhiteSpace(ddlZip.SelectedValue)) { var licenses = le.LicenseDBs; //get the result from the entites search var result = licenses.Where(n => n.Zip == ddlZip.SelectedValue); if (result.Count() == 0) { lblMessage.Text = "No Result Found!"; } // lvTopThree.DataSource = result.ToList(); gvSearchByZip.DataSource = result.ToList(); gvSearchByZip.DataBind(); //show number of the result lblMessage.Text = "There are " + result.Count() + " results has been found!"; } } } catch (Exception) { lblMessage.Text = "DataBase Connection Failed!"; } }
/** * Check existence of the FID */ private bool Existed(int fid) { using (LicenseEntities le = new LicenseEntities()){ var exist = le.LicenseDBs.Any(n => n.FID == fid); if (exist) { return(true); } else { return(false); } } }
/** * This function will remvoe all the content of database * */ protected void btnRemove_Click(object sender, EventArgs e) { using (LicenseEntities le = new LicenseEntities()) { //remove records one by one foreach (var entity in le.LicenseDBs) { le.LicenseDBs.Remove(entity); } le.SaveChanges(); } //show information to user lblMessage.Text = "All the data has been removed from database!"; }
public static List <string> GetFID(string pre) { List <string> allFID = new List <string>(); using (LicenseEntities dc = new LicenseEntities()) { //get the fid start will the certain number // allFID = dc.LicenseDBs.Where(n => n.FID.StartsWith(pre)).ToList(); allFID = (from a in dc.LicenseDBs where (SqlFunctions.StringConvert((double)a.FID)).Trim().StartsWith(pre) select SqlFunctions.StringConvert((double)a.FID).Trim()).ToList(); } return(allFID); }
//Check is the user input FID exist private bool CheckFID(int fid) { //create entity frame work object using (LicenseEntities le = new LicenseEntities()) { //check any result in the database of the fid var exist = le.LicenseDBs.Any(n => n.FID == fid); if (exist) { return(true); } else { return(false); } } }
/*This funtion will get the record from database, the load to the textbox * for user to modify * */ protected void btnModify_Click(object sender, EventArgs e) { //Check the fid has inputted or not if (!String.IsNullOrWhiteSpace(txtFID.Text)) { //clean existed value of textboxes CleanText(); using (LicenseEntities le = new LicenseEntities()) { try { int fid = Convert.ToInt32(txtFID.Text); //check the FID exist or not if (!Existed(fid)) { throw new Exception(); } var lic = le.LicenseDBs.SingleOrDefault(n => n.FID == fid); if (lic != null) { txtBusiness_Name.Text = lic.Business_Name.Trim(); txtBusiness_Phone_Number.Text = lic.Business_Phone_Number.Trim(); txtCity.Text = lic.City.Trim(); txtClassficationCode.Text = lic.Classification_Code.Trim(); txtLicense_Status.Text = lic.License_Status.Trim(); txtState.Text = lic.State.Trim(); txtStreet_Address.Text = lic.Street_Address.Trim(); txtZip.Text = lic.Zip.Trim(); txtClassficationDes.Text = lic.Classification_Description.Trim(); } } catch (Exception) { lblMessage.Text = "FID Not Exist!"; } } } }
/*This function will show the reuslt similar business in certain area * */ protected void btnSearchByZipAndCategory_Click(object sender, EventArgs e) { lblMessage.Text = ""; try { //check user input any thing if (String.IsNullOrWhiteSpace(ddlCategory.SelectedValue) || String.IsNullOrWhiteSpace(ddlZip.SelectedValue)) { //no input get from user lblMessage.Text = "Please select search conditions"; return; } using (LicenseEntities le = new LicenseEntities()) { gvSearchByZip.DataSource = null; gvSearchByZip.DataBind(); var licenses = le.LicenseDBs; //get the result fit for the both condition var result = licenses.Where(n => n.Zip == ddlZip.SelectedValue && n.Classification_Code == ddlCategory.SelectedValue); if (result.Count() == 0) { lblMessage.Text = "No Result Found!"; } //put search result to Grid View gvSearchByZip.DataSource = result.ToList(); gvSearchByZip.DataBind(); //show user total number of results have been found lblMessage.Text = "There are " + result.Count() + " results has been found!"; } } catch (Exception) { lblMessage.Text = "DataBase Connection Failed!"; } }
/* * The Submit function will pull the data from webpage, put into a LicenseDB object * Then store it inot the database */ protected void btnSubmit_Click(object sender, EventArgs e) { try { //check the fid field is exist or not, empty throw a excetion to stop //the process if (CheckFID(Convert.ToInt32(txtFID.Text))) { throw new Exception(); } lblSuccess.Text = ""; //create a entity framework object using (LicenseEntities le = new LicenseEntities()) { LicenseDB lDB = new LicenseDB(); lDB.Business_Name = txtBusiness_Name.Text; lDB.Business_Phone_Number = txtBusiness_Phone_Number.Text; lDB.City = txtCity.Text; lDB.State = txtState.Text; lDB.Zip = txtZip.Text; lDB.License_Status = txtLicense_Status.Text; lDB.FID = Convert.ToInt32(txtFID.Text); lDB.Classification_Code = txtClassficationCode.Text; lDB.Classification_Description = txtClassficationDes.Text; //add new record to entity framework le.LicenseDBs.Add(lDB); le.SaveChanges(); lblSuccess.Text = "Adding Success!!"; } } catch (Exception) { lblSuccess.Text = "FID is already Existed!!"; } }
//This Funtion will pull the information from page //and search from the Database protected void btnSearch_Click(object sender, EventArgs e) { //this flag to make sure user inputed or selected something bool flag = false; try { using (LicenseEntities le = new LicenseEntities()) { //get all the records in the database IQueryable <LicenseDB> result = le.LicenseDBs; //check user input contents if (!String.IsNullOrWhiteSpace(txtFID.Text)) { //FID is integer value in the database int fid = Convert.ToInt32(txtFID.Text); result = result.Where(n => n.FID == fid); flag = true; } //check user input contents if (!String.IsNullOrWhiteSpace(txtBusinessName.Text)) { flag = true; result = result.Where(n => n.Business_Name.Contains(txtBusinessName.Text)); } //check user input contents if (!String.IsNullOrWhiteSpace(txtAddress.Text)) { flag = true; result = result.Where(n => n.Street_Address.Contains(txtAddress.Text)); } //check user input contents if (!String.IsNullOrWhiteSpace(ddlCity.SelectedValue)) { flag = true; result = result.Where(n => n.City.Contains(ddlCity.SelectedValue)); } //check user input contents if (!String.IsNullOrWhiteSpace(ddlStates.SelectedValue)) { flag = true; result = result.Where(n => n.State.Contains(ddlStates.SelectedValue)); } //check user input contents if (!String.IsNullOrWhiteSpace(ddlZip.SelectedValue)) { flag = true; result = result.Where(n => n.Zip.Contains(ddlZip.SelectedValue)); } //check user input contents if (!String.IsNullOrWhiteSpace(txtPhoneNumber.Text)) { flag = true; result = result.Where(n => n.Business_Phone_Number.Contains(txtPhoneNumber.Text)); } //check user input contents if (!String.IsNullOrWhiteSpace(ddlStatus.SelectedValue)) { flag = true; result = result.Where(n => n.License_Status.Contains(ddlStatus.SelectedValue)); } //check user input contents if (!String.IsNullOrWhiteSpace(txtClassificationCode.Text)) { flag = true; result = result.Where(n => n.Classification_Code.Contains(txtClassificationCode.Text)); } //check user input contents if (!String.IsNullOrWhiteSpace(txtClassficationDes.Text)) { flag = true; result = result.Where(n => n.Classification_Description.Contains(txtClassficationDes.Text)); } //check any result exists in the final if (result.Count() > 0 && flag) { gvSearch.DataSource = result.ToList(); } else { //no result find the reset the GridView gvSearch.DataSource = null; } //update the Grid View gvSearch.DataBind(); } } catch (Exception) { lblMessage.Text = "Database connection error!"; } }
/** * This function will take the path of user select csv file, * then create a license list to store csv file information * then store the license into entity framework */ protected void btnSubmit_Click(object sender, EventArgs e) { string path = ""; lblMessage.Text = ""; try { //check user selection of textbox empty or not if (!String.IsNullOrWhiteSpace(fuCSV.FileName)) { path = fuCSV.FileName; //get the full path of user select file path = Convert.ToString(fuCSV.PostedFile.FileName); //print out the full path of file lblMessage.Text = path; //read the file from LicenseCollection lc = new LicenseCollection(path); //create entity frame work LicenseEntities en = new LicenseEntities(); //store licene once a time to the entiry framework foreach (var l in lc) { //check fid existence if (Existed(l.Fid)) { continue; } LicenseDB lDB = new LicenseDB(); lDB.Business_Name = l.Business_Name; lDB.Street_Address = l.Street_Address; lDB.Business_Phone_Number = l.Business_Phone_Number; lDB.City = l.City; lDB.State = l.State; lDB.Zip = l.Zip; lDB.License_Status = l.License_Status; lDB.FID = l.Fid; lDB.Classification_Code = l.ClassficationCode; //this part is from the phase 4 , revese back to orginal if (l.GetType() == typeof(Hotel)) { lDB.Classification_Description = ((Hotel)l).ClassficationDis; } else if (l.GetType() == typeof(Auto)) { lDB.Classification_Description = ((Auto)l).ClassficationDis; } else if (l.GetType() == typeof(Restaurant)) { lDB.Classification_Description = ((Restaurant)l).ClassficationDis; } else if (l.GetType() == typeof(OtherBusiness)) { OtherBusiness newOther = (OtherBusiness)l; lDB.Classification_Description = newOther.ClassficationDis; } //lDB.Classification_Description = l.ClassficationDis; //save data to database en.LicenseDBs.Add(lDB); en.SaveChanges(); } } //show the final information to user lblMessage.Text = "Your data has been update Database!"; }catch (Exception) { lblMessage.Text = "The CSV file format is not right"; } }