/*This method is invoked when you first start the application ,
* and through a messagebox asking you if you want to create a database file. sdf
* If the user decides to create the file will create an instance of the class ContactDataContext
* with the corresponding parameter of the path given by the FileName property of the control SaveFileDialog
* for connecting and using the method CreateDatabase will create the Database with related tables mapped i
* nside of the file Contact.dbml of LinqToSql.
* Will be valued the variable path and settings saved and passed as a parameter each
* time and request connection to the Database Created
*/
public static void CreateFirstDataBase()
{
var dialog = new SaveFileDialog();
dialog.Filter = "File sdf|*.sdf";
if (System.Windows.Forms.MessageBox.Show(string.Format("{0}{1}{2}{3}{4}", "File",
" ", Properties.Settings.Default.path, " ", "not found ,want to create the file?"),
System.Windows.Forms.Application.ProductName.ToString(), MessageBoxButtons.YesNo,
MessageBoxIcon.Question).Equals(System.Windows.Forms.DialogResult.Yes) &&
dialog.ShowDialog().Equals(System.Windows.Forms.DialogResult.OK))
{
using (var ctx = new ContactDataContext(dialog.FileName))
{
ctx.CreateDatabase();
Properties.Settings.Default.path = dialog.FileName;
Properties.Settings.Default.Save();
}
}
}
/*This method and is nearly similar to the previous method, except that the user can decide when to create a new database,
* this method is not called the first time.
*/
public static void CreateNewDataBase()
{
var dialog = new SaveFileDialog();
dialog.Filter = "File sdf|*.sdf";
var dialogresult = dialog.ShowDialog();
if (dialogresult.Equals(System.Windows.Forms.DialogResult.Cancel)) return;
if (dialogresult.Equals(System.Windows.Forms.DialogResult.OK) && (!System.IO.File.Exists(dialog.FileName)))
{
using (var ctx = new ContactDataContext(dialog.FileName))
{
ctx.CreateDatabase();
Properties.Settings.Default.path = dialog.FileName;
Properties.Settings.Default.Save();
}
return;
}
System.Windows.Forms.MessageBox.Show(string.Format("{0}{1}{2}{3}{4}{5}", "File", " "
, dialog.FileName, " ", "already exsist", " ", "choose a different name"));
}
//Click event of btnFind
private void btnFind_Click(object sender, RoutedEventArgs e)
{
/*This Linq query performs a search based on what the user selects the Search form,
* the form includes three RadioButton and CheckBox controls sixteen,
* first of all check if the user has selected at least one RadioButton and one or more checkboxes,
* otherwise it will be perceived from a messagebox.
* Next check out what has been selected, this is by LinqToObjects query,
* first retrieves the name of the selected RadioButton control and the second the name or names of the selected checkboxes,
* which is becoming the operator and Where the predicate inside.
*This query and the search for or interested in the tasks assigned to each person.
var result = ctx.GetTable <Job> ()
. Where (w => w.ACTIVITY.Equals (_activity.Content.ToString (). ToUpper ()) && w.STATE.Equals (_state.ToUpper ()))
. Join (ctx.PERSON, job => job.ID, cust => cust.ID, (job, name) => new {name.NAME, name.SURNAME, name.ADDRESS, name.ZIPCODE, name.CITY} )
. OrderBy (a => a.SURNAME)
. Distinct ()
. ToList ();
*A check is performed on the Job table if there are people who work a year and a corresponding duty,
*will join the two tables placed in the Job and person using the ID field will be extracted and all the information
*of the person or persons who fulfill the conditions specified in Where previous predicate operator,
*then using the OrderBy operator will be sorted in ascending according to the specified predicate,
*in this case by name, will be eliminated any double occurrences, and this task Distinct operator,
*he will remove all occurrences double and the method ToList convert the result of the query
*that will anominustype type in a list and we'll look at why
*/
if (Validations.ChechBoxAndRadioButtonChecked(dgvSearch) < 2)
{
return;
}
var print = new PreviewDialog();
var _state = string.Empty;
using (var ctx = new ContactDataContext(Properties.Settings.Default.path))
{
foreach (var myRadioButton in dgvSearch.Children.OfType<RadioButton>().Where(myRadioButton => myRadioButton.IsChecked.Equals(true)))
{
_state = myRadioButton.Content.ToString();
}
foreach (var myCheckBox in dgvSearch.Children.OfType<CheckBox>().Where(myCheckBox => myCheckBox.IsChecked.Equals(true)))
{
var _activity = myCheckBox;
var result = ctx.GetTable<JOB>()
.Where(w =>w.ACTIVITY.Equals(_activity.Content.ToString().ToUpper()) && w.STATE.Equals(_state.ToUpper()))
.Join( ctx.PERSON, job => job.ID, cust => cust.ID,(job, name) => new {name.NAME, name.SURNAME, name.ADDRESS, name.ZIPCODE, name.CITY })
.OrderBy(a => a.SURNAME)
.Distinct()
.ToList();
foreach (var item in result)
{
/*The method of the class LoadData PreviewDialog populates a list of such Persons passing all the data found by the search query,
* which is why we converted the result of the query in a list, in the class PreviewDialog will find the list of
* such person and the method LoadData
*/
print.LoadData(item.NAME, item.SURNAME, item.ADDRESS, item.ZIPCODE, item.CITY);
}
}
print.ShowDialog();
}
}
/*This method of type DataGrid, using code ctx.PERSON; select all data from the DataBase of the Person table
* and returns a datagrid with its property ItemSource.
*/
public static System.Windows.Controls.DataGrid LoadData()
{
var dgvDati = new System.Windows.Controls.DataGrid();
using (var ctx = new ContactDataContext(Properties.Settings.Default.path))
{
var selectperson = ctx.PERSON;
dgvDati.ItemsSource = selectperson;
}
return dgvDati;
}
/*This method means the method SubmitChanges performs the necessary changes within the database,
* this method requires the necessary arguments for the update data, all the while by ContactDataContext class that exposes all the methods needed in the *iteration with the data, runs a foreach loop on the variable idupdateperson and updated all the data that meets the conditions
* of the predicate in the Where operator.
*/
public static void UpdateData(string name, string surname, string address, string zipcode, string city, string state, string activity)
{
using (var ctx = new ContactDataContext(Properties.Settings.Default.path))
{
var idperson = ctx.PERSON.Where(w => w.NAME.Equals(name) && w.SURNAME.Equals(surname));
foreach (var itemperson in idperson.ToList())
{
var updateperson = ctx.PERSON.Where(w => w.NAME.Equals(name) && w.SURNAME.Equals(surname));
itemperson.NAME = name.ToUpper();
itemperson.SURNAME = surname.ToUpper();
itemperson.ADDRESS = address.ToUpper();
itemperson.ZIPCODE = zipcode.ToUpper();
itemperson.CITY = city.ToUpper();
var idjob = ctx.JOB.Where(w => w.IDPERSON.Equals(itemperson.ID));
foreach (var itemjob in idjob.ToList())
{
var updatejob = ctx.JOB.Where(w => w.ID.Equals(itemperson.ID));
itemjob.STATE = state;
itemjob.ACTIVITY = activity;
}
ctx.SubmitChanges();
}
}
}
/*This method means the method DeleteOnSubmit status delete data into a pending state,
* then the method SubmitChanges performs the necessary changes within the database, this method requires the necessary arguments for deleting data,
* all the while using the class that exposes all ContactDataContext methods
* needed in the iteration with the data as we shall see in subsequent methods.
* Is then performed using a foreach loop iteration variable iddeletejob and will be eliminated on all data that
* satisfy the predicate in the Where operator, for both the table job for the person table.
*/
public static void DeleteData(string name, string surname)
{
using (var ctx = new ContactDataContext(Properties.Settings.Default.path))
{
try
{
var ideletejob = ctx.PERSON.Where(w => w.NAME.Equals(name) && w.SURNAME.Equals(surname));
foreach (var item in ideletejob.ToList())
{
var deletejob = ctx.JOB.Where(w => w.IDPERSON.Equals(item.ID));
if (!deletejob.Any()) return;
ctx.JOB.DeleteOnSubmit(deletejob.First());
var deleteperson = ctx.PERSON
.Where(w => w.NAME.Equals(name) && w.SURNAME.Equals(surname));
if (!deleteperson.Any()) return;
ctx.PERSON.DeleteOnSubmit(deleteperson.First());
ctx.SubmitChanges();
}
}
catch (System.Exception ex)
{
System.Windows.Forms.MessageBox.Show(ex.Message);
}
}
}
/*This method means the method InsertOnSubmit status insert data into a pending state,
* then the method SubmitChanges performs the necessary changes within the database, this method requires the necessary arguments for entering data,
* all the while using the class that exposes all ContactDataContext methods
* needed in the iteration with the data as we shall see in subsequent methods.
*/
public static void InsertData(string name, string surname, string address, string zipcode, string city, string state, string activity)
{
using (var ctx = new ContactDataContext(Properties.Settings.Default.path))
{
var newperson = new PERSON
{
NAME = name.ToUpper(),
SURNAME = surname.ToUpper(),
ADDRESS = address.ToUpper(),
ZIPCODE = zipcode.ToUpper(),
CITY = city.ToUpper()
};
ctx.PERSON.InsertOnSubmit(newperson);
ctx.SubmitChanges();
var newjob = new JOB
{
IDPERSON = newperson.ID,
STATE = state,
ACTIVITY = activity
};
ctx.JOB.InsertOnSubmit(newjob);
ctx.SubmitChanges();
}
}
