//check if the user exsit or not
public static IList <string> CheckInUser(string userName, String password)
{
Contact_managerDataContext dc = new Contact_managerDataContext();
var check = dc.UserDetails.Where(e1 => e1.UserName == userName).Where(e2 => e2.Password == password).Select(e3 => e3.Password).ToList();
return(check);
}
//to get filtered list
public static IList <Country> FilteredResultCountries(bool status)
{
Contact_managerDataContext dc = new Contact_managerDataContext();
var ds = dc.Countries.Where(e1 => e1.IsActive == status).ToList();
return(ds);
}
//to get a filter list
public static object FilterReseultUser(bool status)
{
Contact_managerDataContext dc = new Contact_managerDataContext();
var ds = dc.UserDetails.Where(e1 => e1.IsActive == status).ToList();
return(ds);
}
//to get all the details from the database
public static IList <UserDetail> GetUserDetails()
{
Contact_managerDataContext dc = new Contact_managerDataContext();
var ds = dc.UserDetails.ToList();
return(ds);
}
//to get all the country
public static IList <Country> GetAllCountry()
{
Contact_managerDataContext dc = new Contact_managerDataContext();
var ds = dc.Countries.ToList();
return(ds);
}
//for get the details from the address
public static object GetAddress()
{
Contact_managerDataContext dc = new Contact_managerDataContext();
var q = (from a in dc.Addressbooks
join s in dc.States on a.FKStateId equals s.PKStateId
join u in dc.UserDetails on a.FKUserId equals u.PKUserId
select new
{
a.PKAddressId,
s.PKStateId,
s.StateName,
u.PKUserId,
u.UserName,
a.FirstName,
a.LastName,
a.EmailId,
a.PhoneNo,
a.Address1,
a.Address2,
a.Street,
a.City,
a.ZipCode,
a.IsActive
});
return(q);
}
//for insert in the address
public static void InsertInAddress(AddressData objAddress)
{
Contact_managerDataContext dc = new Contact_managerDataContext();
Addressbook addressbook = new Addressbook();
if (dc.States.First(e1 => e1.PKStateId == objAddress.FKStateId) != null && dc.UserDetails.First(e1 => e1.PKUserId == objAddress.FKUserId) != null)
{
addressbook.FKStateId = objAddress.FKStateId;
addressbook.FKUserId = objAddress.FKUserId;
addressbook.FirstName = objAddress.FirstName;
addressbook.LastName = objAddress.LastName;
addressbook.EmailId = objAddress.EmailId;
addressbook.PhoneNo = objAddress.PhoneNo;
addressbook.Address1 = objAddress.Address1;
addressbook.Address2 = objAddress.Address2;
addressbook.Street = objAddress.Street;
addressbook.City = objAddress.City;
addressbook.ZipCode = objAddress.ZipCode;
addressbook.IsActive = objAddress.IsActive;
dc.Addressbooks.InsertOnSubmit(addressbook);
dc.SubmitChanges();
}
else
{
throw new Exception("Plese Enter the valid Statename and UserName");
}
}
//list of state on country
public static object ListOfState(int id)
{
Contact_managerDataContext dc = new Contact_managerDataContext();
var ds = dc.States.Where(e1 => e1.FKCountryId == id).ToList();
return(ds);
}
// filtered value of the Addressbook
public static object FilteredAddress(int id, bool status)
{
Contact_managerDataContext dc = new Contact_managerDataContext();
var da = dc.Addressbooks.Where(e1 => e1.FKStateId == id).Where(e1 => e1.IsActive == status).ToList();
var du = UserDB.GetUserDetails();
var ds = dc.States.ToList();
var qry = da.Join(du, a1 => a1.FKUserId, u1 => u1.PKUserId, (a1, u1) => new
{
Addressbook = a1,
UserDetail = u1
}).Join(ds, a1 => a1.Addressbook.FKStateId, s1 => s1.PKStateId, (a1, s1) => new
{
PKAddressId = a1.Addressbook.PKAddressId,
FKStateId = s1.PKStateId,
StateName = s1.StateName,
FKUserId = a1.UserDetail.PKUserId,
UserName = a1.UserDetail.UserName,
FirstName = a1.Addressbook.FirstName,
LastName = a1.Addressbook.LastName,
EmailId = a1.Addressbook.EmailId,
PhoneNo = a1.Addressbook.PhoneNo,
Address1 = a1.Addressbook.Address1,
Address2 = a1.Addressbook.Address2,
Street = a1.Addressbook.Street,
City = a1.Addressbook.City,
ZipCode = a1.Addressbook.ZipCode,
IsActive = a1.Addressbook.IsActive
}).ToList();
return(qry);
}
public static void UpdateInAddress(AddressData objAddress)
{
Contact_managerDataContext dc = new Contact_managerDataContext();
Addressbook addressbook = dc.Addressbooks.First(e1 => e1.PKAddressId == objAddress.PKAddressId);
if (dc.States.First(e1 => e1.PKStateId == objAddress.FKStateId) != null && dc.UserDetails.First(e1 => e1.PKUserId == objAddress.FKUserId) != null)
{
addressbook.FKStateId = objAddress.FKStateId;
addressbook.FKUserId = objAddress.FKUserId;
addressbook.FirstName = objAddress.FirstName;
addressbook.LastName = objAddress.LastName;
addressbook.EmailId = objAddress.EmailId;
addressbook.PhoneNo = objAddress.PhoneNo;
addressbook.Address1 = objAddress.Address1;
addressbook.Address2 = objAddress.Address2;
addressbook.Street = objAddress.Street;
addressbook.City = objAddress.City;
addressbook.ZipCode = objAddress.ZipCode;
addressbook.IsActive = objAddress.IsActive;
dc.SubmitChanges();
}
else
{
throw new Exception("Please check the details ");
}
}
//get table from the database with country Name
public static object GetCountryAndState()
{
Contact_managerDataContext dc = new Contact_managerDataContext();
// IQueryable<State> qry = dc.States;
var q = (from s in dc.States join c in dc.Countries on s.FKCountryId equals c.PKCountryId select new { s.PKStateId, s.FKCountryId, c.CountryName, s.StateName, s.IsActive }).ToList();
return(q);
}
//for deleting
public static void DeleteInAddress(int addreessId)
{
Contact_managerDataContext dc = new Contact_managerDataContext();
Addressbook addressbook = dc.Addressbooks.First(e1 => e1.PKAddressId == addreessId);
dc.Addressbooks.DeleteOnSubmit(addressbook);
dc.SubmitChanges();
}
//for deleting
public static void DeleteCountry(int countryId)
{
Contact_managerDataContext dc = new Contact_managerDataContext();
Country country = dc.Countries.First(e1 => e1.PKCountryId == countryId);
dc.Countries.DeleteOnSubmit(country);
dc.SubmitChanges();
}
//delete from the user detail
public static void DeleteInUserDetail(int userId)
{
Contact_managerDataContext dc = new Contact_managerDataContext();
UserDetail us = dc.UserDetails.First(e1 => e1.PKUserId == userId);
dc.UserDetails.DeleteOnSubmit(us);
dc.SubmitChanges();
}
//for delete the state
public static void DeleteInSatte(int stateId)
{
Contact_managerDataContext dc = new Contact_managerDataContext();
State state = dc.States.First(e1 => e1.PKStateId == stateId);
dc.States.DeleteOnSubmit(state);
dc.SubmitChanges();
}
//checking the user for the login form
public static int IsUserExist(string userName)
{
int userId = 0;
Contact_managerDataContext dc = new Contact_managerDataContext();
var qry = dc.UserDetails.Where(e1 => e1.UserName == userName).Select(e1 => e1.PKUserId).ToList();
foreach (var s in qry)
{
userId = (int)s;
}
return(userId);
}
//for inserting
public static void InsertNewCountry(Countrydata objcountryData)
{
Contact_managerDataContext dc = new Contact_managerDataContext();
Country country = new Country();
country.CountryName = objcountryData.CountryName;
country.ZipCodeStart = objcountryData.ZipCodeStart;
country.ZipCodeEnd = objcountryData.ZipCodeEnd;
country.IsActive = objcountryData.IsActive;
dc.Countries.InsertOnSubmit(country);
dc.SubmitChanges();
}
//for updating
public static void UpdateCountry(Countrydata objCountryData)
{
int countryId = objCountryData.PKCountry;
Contact_managerDataContext dc = new Contact_managerDataContext();
Country country = dc.Countries.First(e1 => e1.PKCountryId == countryId);
country.CountryName = objCountryData.CountryName;
country.ZipCodeStart = objCountryData.ZipCodeStart;
country.ZipCodeEnd = objCountryData.ZipCodeEnd;
country.IsActive = objCountryData.IsActive;
//dc.Countries.InsertOnSubmit(country);
dc.SubmitChanges();
}
//to get insert data
public static void InsertInUserDetail(UserData objUserData)
{
Contact_managerDataContext dc = new Contact_managerDataContext();
UserDetail us = new UserDetail();
us.UserName = objUserData.UserName;
us.Password = objUserData.Password;
us.FirstName = objUserData.FirstName;
us.LastName = objUserData.LastName;
us.EmailId = objUserData.EmailId;
us.PhoneNo = objUserData.PhoneNo;
us.IsActive = objUserData.IsActive;
dc.UserDetails.InsertOnSubmit(us);
dc.SubmitChanges();
}
//update the user details
public static void UpdateInUserDetail(UserData objUserData)
{
int userId = objUserData.UserId;
Contact_managerDataContext dc = new Contact_managerDataContext();
UserDetail us = dc.UserDetails.First(e1 => e1.PKUserId == userId);
us.UserName = objUserData.UserName;
us.Password = objUserData.Password;
us.FirstName = objUserData.FirstName;
us.LastName = objUserData.LastName;
us.EmailId = objUserData.EmailId;
us.PhoneNo = objUserData.PhoneNo;
us.IsActive = objUserData.IsActive;
dc.SubmitChanges();
}
//get the list of all the state
//public static IList<State> GetState()
//{
// Contact_managerDataContext dc = new Contact_managerDataContext();
// // var ds = dc.States.ToList(dc.States.Select(s=>s.FKCountryId==);
// var ds =dc.States.ToList();
// return ds;
//}
//insert in the state if the coiuntry id exist
//for filtering the states
public static object FilteredResultState(int id, bool status)
{
Contact_managerDataContext dc = new Contact_managerDataContext();
var ds = dc.States.Where(e1 => e1.FKCountryId == id).Where(e2 => e2.IsActive == status).ToList();
var dco = dc.Countries.ToList();
var qry = ds.Join(dco, e1 => e1.FKCountryId, c1 => c1.PKCountryId, (State, Country) => new
{
PKStateId = State.PKStateId,
FKCountry = Country.PKCountryId,
CountryName = Country.CountryName,
StateName = State.StateName,
IsActive = State.IsActive
});
return(qry.ToList());
}
//Update the value in state table
public static void UpdateInState(StateData objStateDate)
{
Contact_managerDataContext dc = new Contact_managerDataContext();
State state = dc.States.First(e1 => e1.PKStateId == objStateDate.StateId);
if (dc.Countries.First(e1 => e1.PKCountryId == objStateDate.FKCountryId) != null)
{
state.FKCountryId = objStateDate.FKCountryId;
state.StateName = objStateDate.StateName;
state.IsActive = objStateDate.IsActive;
dc.SubmitChanges();
}
else
{
throw new ApplicationException("please provide a valid country");
}
}
public static void InsertInState(StateData objStateData)
{
int fKCountryId = objStateData.FKCountryId;
Contact_managerDataContext dc = new Contact_managerDataContext();
State state = new State();
if (dc.Countries.First(e1 => e1.PKCountryId == fKCountryId) != null)
{
state.FKCountryId = fKCountryId;
state.StateName = objStateData.StateName;
state.IsActive = objStateData.IsActive;
dc.States.InsertOnSubmit(state);
dc.SubmitChanges();
}
else
{
throw new ApplicationException("PLease inter the valid countryid");
}
}
