/*
* 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";
}
}
