/// <summary>
/// method for selecting a database to use.
/// </summary>
/// <param name="database">the name of the database</param>
/// <returns>returns the name of the database set.</returns>
public string ChooseDb(string database)
{
//set the global variable of the database name
if (database.Trim() != "")
{
(App.Current as App).Db_name = database;
this.database = (App.Current as App).Db_name;
}
else
{
PopUp p = new PopUp("Please choose a database");
this.database = (App.Current as App).Db_name;
}
return(this.database);
}
/// <summary>
/// OPENS THE DB CONNECTION
/// </summary>
public void Open()
{
//open the connection
try
{
if (this.con.State.ToString().ToLower() == "closed") //ONLY OPEN THE CONNECTION WHEN IT'S CLOSED, TO AVOID OPENING WHILE IT'S STILL OPENED.
{
this.con.Open();
}
}
catch (Exception ex)
{
PopUp p = new PopUp("Couldn't connect to the server. \n" + ex.ToString());
}
}
/// <summary>
/// method for deleting a record in a table
/// </summary>
/// <param name="table">the name of the table</param>
/// <param name="condition">the condition,e.g, where bla =' bla'</param>
public void Remove(string table, string condition)
{
this.table = table;
this.condition = (condition != null ? condition : "");
//this.CheckTable("");
// string q = "DELETE FROM " + this.table + " " + this.condition;
if (this.condition.Trim() != "")
{
string q = "UPDATE " + this.table + " SET deleted = '1' " + this.condition + "";
this.Quering(delete, q);
}
else
{
box = new PopUp("Couldn't delete the record.");
}
}
/// <summary>
/// Helps to open the MysqlDataReader, IT'S USEFUL BECAUSE ONCE THE READER HAS USED THE .READ(), IT GETS EMPTY
/// SO TO REFILL IT, THIS METHOD HELPS TO DO THAT :).
/// LIFE'S GOOD :-).
/// </summary>
private void OpenReader()
{
try
{
if (this.reader != null)
{
this.reader.Close();
//reopen the connection so that the reader will be good as new.
this.Close();
this.Open();
}
this.reader = this.cmd.ExecuteReader();
}
catch (Exception ex)
{
PopUp p = new PopUp("pp pp " + ex.ToString());
}
}
/// <summary>
/// for getting the single result of the query
/// stores the values in string[column]
/// with variable name this.Result
/// e.g. this.Result[column].
/// </summary>
public void Record()
{
if (this.CountRows() > 0)
{//if there are any rows returned, do something.
this.OpenReader();
if (!(this.reader == null))
{//there's a valid query, the .get() method has been called, so carry on
int rows = 0;
int field_count = 0;
//get the number of rows, although it's just a single row.
this.result = new string[this.reader.FieldCount];
while (this.Reading())
{
// Get the number of columns returned since it's only one record
while (field_count < this.reader.FieldCount)
{ //count the number of the fields
if (this.reader[field_count] != null) //TO AVOID OBJECT REFERENCE SET TO NULL ERROR, WE MAKE SURE
{ //THE reader doesn't have a null value, so that we can convert it to string
this.result[field_count] = this.reader[field_count].ToString();
}
else
{//SINCE IT'S NULL, JUST PUT IN AN EMPTY QUOTE. THE SMARTEST WAY I'VE FOUND SO FAR, TO SOLVE THIS PROBLEM.
this.result[field_count] = "";
}
field_count++;
}
rows++;
}
}
else
{//it means the get method hasnt executed any query
PopUp p = new PopUp("No query was done, a query has to be done with the aid of the Get() method before the Records() method can be functional. :(");
}
this.CLoseReader();
}
else
{
//empty result.
}
}
/// <summary>
/// checks for phone validity
/// </summary>
/// <param name="value">the phone number to be checked</param>
/// <param name="msg">the message to display to the frontend when it returns false,optional,if empty, nothing will be displayed</param>
/// <returns>True if valid, and false if not.</returns>
public bool CheckPhone(string value, string msg = "")
{
//regular expression for phone number
string regexPattern = @"^(\+{1}|00)\s{0,1}([0-9]{3}|[0-9]{2})\s{0,1}\-{0,1}\s{0,1}([0-9]{2}|[1-9]{1})\s{0,1}\-{0,1}\s{0,1}([0-9]{8}|[0-9]{7})";
if (System.Text.RegularExpressions.Regex.IsMatch(value, regexPattern))
{
// this.Token = 1;
return(true);
}
else
{//check if the msg is empty
if (this.Trim(msg) != "")
{
PopUp p = new PopUp(msg);
}
else
{
//do nothing.
}
return(false);
// this.Token = 0;
}
}
/// <summary>
/// for getting the records of the query
/// stores the values in string[row,column]
/// with variable name this.Results
/// e.g. this.Results[row,column].
/// </summary>
public void Records()
{
if (this.CountRows() > 0)
{//if there are any rows returned, do something.
this.OpenReader();
if (!(this.reader == null))
{//theres a valid query, the .get() method has been called, so carry on
int rows = 0;
int field_count = 0;
//get the number of rows
while (this.Reading())
{
//WE'RE GOING TO MAKE SOMETHING LIKE A MULTI DIMENSIONAL ARRAY
// Get the number of columns returned
while (field_count < this.reader.FieldCount)
{//count the number of the fields
field_count++;
}
rows++;
}
//BASED ON HOW MANY ROWS WERE READ, set the values of the dimensional array
this.Results = new string[rows, field_count];//so the number of rows returned is the maximum value the [rows,] part can take
//and the number of fields is the maximum value the [,field_count] part can take
int i = 0;
int j = 0;
//OPEN THE READER again, so that the reader.Read() will be refreshed and have something to read even if
//it has already been read above.
this.OpenReader();
/*
* THIS TIME, WE'RE READING THE VALUE TO STORE IT IN THE MULTI DIMENSIONAL ARRAY
* THIS WAY, WE CAN GET VALUES OF MORE THAN ONE ROW. ;-)
*/
while (this.reader.Read())
{
//FOR THE COLUMNS
while (j < this.reader.FieldCount)
{
if (this.reader[j] != null) //TO AVOID OBJECT REFERENCE SET TO NULL ERROR, WE MAKE SURE
{ //THE reader doesn't have a null value, so that we can convert it to string
this.Results[i, j] = this.reader[j].ToString();
}
else
{//SINCE IT'S NULL, JUST PUT IN AN EMPTY QUOTE. THE SMARTEST WAY I'VE FOUND SO FAR, TO SOLVE THIS PROBLEM.
this.Results[i, j] = "";
}
j++;
}
//reset j, to get column values again for the next row level, to make the loop perfect.
j = 0;
i++;
}
}
else
{//it means the get method hasnt executed any query
PopUp p = new PopUp("No query was done, a query has to be done with the aid of the Get() method before the Records() method can be functional. :(");
}
this.CLoseReader();
}
else
{
//empty result.
}
}
/// <summary>
/// method for updating a record in a table
/// </summary>
/// <param name="table">the name of the table</param>
/// <param name="columns">the column(s) to be edited.</param>
/// <param name="values">the value of the column(s) listed</param>
/// <param name="condition">the conditon, e.g set bla = 'bla' where bla_id = 'bla'</param>
public void Change(string table, string columns, string[] values, string condition)
{
//filter values
Filter filter = new Filter();
this.table = table;
this.condition = condition;
//CHECK IF THE column is empty
string col = string.Empty;
if (filter.Trim(columns) == "" || filter.Trim(table) == "" || filter.Trim(condition) == "")
{
//IT CAN'T BE EMPTY
if (columns == "")
{
box = new PopUp("Columns can't be empty in the current Change() method.");
}
else if (table == "")
{
box = new PopUp("table can't be empty in the current Change() method.");
}
else if (condition == "")
{
box = new PopUp("condition can't be empty in the current Change() method.");
}
else
{
box = new PopUp("A value is empty, can't work. I don't even know what is wrong sef, oh, find the problem yourself, i cannot come and be killing myself.");
}
}
else
{ //COLUMN HAS SOME VALUES,make sure the number of columns are equal to the number of values
#region handling the columns stuff
Split split = new Split(); //divide
split.Divide(columns, ',');
//set the start first, we'll accomplish this at the end, under the looping ish.
//col = bla = 'bla',bla = 'bla';
//make sure before counting the array, they must not be empty.
string comma = "";
//CHECK IF THE COLUMNS LENGTH AND VALUE LENGTH ARE THE SAME.
if (split.Length == values.Length)
{
//now store
for (int i = 0; i < split.Value_array.Length; i++) //keep adding to the col variable with comma
{ //actually this is unecessary work, but it's just to make sure
int j = 0;
if (filter.Trim(split.Value_array[i]) != "")
{//NOW THIS IS WHAT MAKES THE CODE USEFUL
/*
* SOMEONE MIGHT PUT COMMA AT THE END OR IN BETWEEN BY MISTAKE,
* AND THAT COULD DESTROY EVERYTHING, THEN THAT
* STUPID ERROR STARTS SHOWING AGAIN.
* THIS PREVENTS THAT :)
*/
//ADD THE VALUE
//FILTER THE VALUES ISH PROPERLY TO INSERT INTO THE QUERY
/*
* THERE COULD BE SOME CONDITIONS.
* the string[] values variable holds values in string, so even if you input a sql functional value like "NOW()"
* it will be passed as a string, which would not turn out good, if we don't handle it properly.
* so we're going to set up an array that holds functional values to determine when we would remove string nature
* what i mean is we'll create an array that holds values of functions in sql command, so when we are looking through
* the string[] value values, we can check if any functional sql value is part of the values, by comparing the values
* with the values inside the other array that stores functional values
*/
//THE ARRAY THAT STORES THE FUNCTIONAL VALUES, any sql functional values used in queries should be added here. :)
// string[] func_val = { "NOW()" };
//THE VARIABLE THAT WILL STORE UP string[] value values
//NOW WHILE CHECKING THROUGH THE VALUES, ALSO CHECK FOR FUNCTIONAL VALUES TO REMOVE THE QUOTES
for (j = 0; j < func_val.Length; j++)
{//STORE THE VALUE
//CHECK IF THE VALUE ISN'T THE LAST ARRAY SO THAT WE DON'T PUT COMMA IN THE LAST VALUE
int calc = values.Length - i;
if (calc == 1)//its the last value, remove the comma
{
comma = "";
}
else
{
comma = ",";
}
if (filter.Trim(values[i]).ToUpper() == filter.Trim(func_val[j].ToUpper()) /*making sure they're all uppercase to perfect comparising*/)
{//it's a functional value, remove the quotes
col += filter.Trim(split.Value_array[i]) + " = " + filter.Trim(values[i]) + "" + comma;
}
else
{//ITS NOT A FUNCTIONAL value, leave the quotes
col += filter.Trim(split.Value_array[i]) + " = '" + filter.Trim(values[i]) + "'" + comma;
}
}
//reset j to keep checking if the values match the functional values.
}
else
{//do nothing
}
}
#endregion
//update
string q = "UPDATE " + this.table + " SET " + col + " " + this.condition;
//
this.Quering(update, q);
}
else
{//THE COLUMN AND VALUE ARE NOT THE SAME, THAT WILL BE AN ISSUE
box = new PopUp("Internal Error: the number of columns and the number of values aren't equivalent in the recent Change() Method.");
}
}
}
/// <summary>
/// Gets the value of the selected index and returns the respective primary key value.
/// mostly used for combobox item stuff.
/// </summary>
/// <param name="index">the selected index value from the combo box item. e.g combobox1.SelectedIndex</param>
/// <param name="table">the table you want to get the record from,if empty or null, it will use the previous</param>
/// <param name="condition">in case there was a condition in the other query that listed the records in the combobox, e.g"ORDER BY ASC", you have to
/// specify it here too, so that it works well.
/// </param>
/// <param name="start_at">determines where to start the searching in combobox.</param>
/// <returns>returns the exact record value</returns>
public string[] Selected(int index, string table, string condition = "", int start_at = 0)
{
string[] result = new string[1];
/*
* if it starts at zero, run normally
* else increement by the value it starts with.
* or do some maths.
*/
if (table != null)
{
if (!(this.Trim(table) == ""))
{
//CALL THE QUERY
int selected_value = index - start_at;//removing the irrelevant values,we're starting from where the main items start from the combobox the record
//index must be greater than the start_at value
if (index >= start_at)
{//correct
Query qu = new Query();
qu.Get(table, condition);
int columns = qu.CountFields();
result = new string[columns];
this.SelectedResult = new string[columns];
int rows = qu.CountRows();
qu.Records();
//now loop and get the value, then break out like a prisoner, i think. or like a financial stuff.
for (int i = 0; i < rows; i++)
{
if (i == selected_value)
{
//it's the one, let's do what we do best and gerrarahea.
for (int j = 0; j < columns; j++)
{
//put in the values of the column field into result
result[j] = qu.Results[i, j];
}
//break out :), we don't need you anymore, yae, just like F boys.
break;
}
else
{
//continue
}
}
}
else
{//wrong
PopUp p = new PopUp("The index has to be greater than or equal to the start_at value,");
//break;
}
}
else
{
PopUp p = new PopUp("no argument should be left empty in Selected() Method.");
}
}
else
{
//it's null.
}
result.CopyTo(this.SelectedResult, 0);
return(this.SelectedResult);
}