//Register a New User
public static void RegisterNow(RegisterInfo registerInfo)
{
using (MySqlConnection connection = DbInfo.Connection())
{
using (MySqlCommand cmd = new MySqlCommand())
{
cmd.Connection = connection;
cmd.CommandType = CommandType.Text;
cmd.CommandText =
"INSERT INTO `users`(`ID`, `Firstname`, `Middlename`, `Lastname`, `Email`, `Password`, `Pin`) " +
"VALUES (@ID,@Firstname,@Middlename,@Lastname,@Email,@Password,@Pin)";
cmd.Parameters.AddWithValue("@ID", "");
cmd.Parameters.AddWithValue("@Firstname", registerInfo.firstname);
cmd.Parameters.AddWithValue("@Middlename", registerInfo.middlename);
cmd.Parameters.AddWithValue("@Lastname", registerInfo.lastname);
cmd.Parameters.AddWithValue("@Email", registerInfo.email);
cmd.Parameters.AddWithValue("@Password", registerInfo.Password);
cmd.Parameters.AddWithValue("@Pin", int.Parse("0"));
try
{
//connection.Open();
int recordsAffected = cmd.ExecuteNonQuery();
//MessageBox.Show("person has added");
}
finally
{
connection.Close();
}
}
}
}
internal static void SetUserInfo(int ID)
{
//create connection and open it
MySqlConnection connection = DbInfo.Connection();
//try to connect to database
try
{
//Build Mysql command
MySqlCommand cmd = connection.CreateCommand();
cmd.CommandText =
"SELECT `ID`, `Firstname`, `Middlename`, `Lastname`, `Email`, `Password`, `Pin` FROM `users` WHERE `ID`=" + ID;
MySqlDataReader reader = cmd.ExecuteReader();
//if match is found
if (reader.Read())
{
//MessageBox.Show(reader["Firstname"] + " "+ reader["Middlename"].ToString());
Userinformation = new userinf(ID, reader["Firstname"].ToString(), reader["Middlename"].ToString(), reader["Lastname"].ToString(), reader["Email"].ToString());
}
}
//finally
finally
{
//check state and clone
if (connection.State == ConnectionState.Open)
{
connection.Clone();
}
}
}
//Get all Databases | Using DB name
public static List <string> GetAllDatabases()
{
//Temp list
List <string> temp = new List <string>();
//create connection and open it
MySqlConnection connection = DbInfo.Connection();
//Create command
MySqlCommand id_cmd = connection.CreateCommand();
id_cmd.CommandText =
"SELECT `ID`, `Datasource`, `Username`, `Password`, `Databasename` FROM `databases`";
//Create reader
MySqlDataReader reader = id_cmd.ExecuteReader();
//While reading
while (reader.Read())
{
temp.Add(reader["Databasename"].ToString());
}
return(temp);
}
//Create Database Connection | Using DB name
public static MySqlConnection CreateDBConnection(string DBName)
{
string constring = "";
//create connection and open it
MySqlConnection connection = DbInfo.Connection();
MySqlCommand id_cmd = connection.CreateCommand();
id_cmd.CommandText =
"SELECT `ID`, `Datasource`, `Username`, `Password`, `Databasename` FROM `databases` WHERE Databasename = @Databasename";
id_cmd.Parameters.AddWithValue("@Databasename", DBName);
MySqlDataReader reader = id_cmd.ExecuteReader();
//if match is found
if (reader.Read())
{
constring = "datasource = " + reader["Datasource"] + "; username = "******"Username"] + "; password="******"Password"] + "; database = " + reader["Databasename"].ToString();
//Create mysqlconnection
MySqlConnection newcon = new MySqlConnection(constring);
newcon.Open();
return(newcon);
}
else
{
//Return connection
MySqlConnection nl = null;
return(nl);
}
}
//Delete new Database
public static void DeleteDatabase(int ID)
{
//Create Connection
using (MySqlConnection connection = DbInfo.Connection())
{
//Create Cmd
using (MySqlCommand cmd = new MySqlCommand())
{
cmd.Connection = connection;
cmd.CommandType = CommandType.Text;
//Set CommandText
cmd.CommandText = "DELETE FROM `databases` WHERE `ID` = @ID";
//Add Parameters
cmd.Parameters.AddWithValue("@ID", ID);
try
{
int recordsAffected = cmd.ExecuteNonQuery();
}
finally
{
connection.Close();
}
}
}
}
//-------------------------CRUD--------------------------------
//Add new Database
public static void AddNewDatabase(DatabaseInfo Databaseinfo)
{
//Create Connection
using (MySqlConnection connection = DbInfo.Connection())
{
//Create cmd
using (MySqlCommand cmd = new MySqlCommand())
{
cmd.Connection = connection;
cmd.CommandType = CommandType.Text;
//Create CommandText
cmd.CommandText = "INSERT INTO `databases`(`ID`, `Datasource`, `Username`, `Password`, `Databasename`) " +
"VALUES (@ID,@Datasource,@Username,@Password,@Databasename)";
//Set Parameters
cmd.Parameters.AddWithValue("@ID", "");
cmd.Parameters.AddWithValue("@Datasource", Databaseinfo.DataSource);
cmd.Parameters.AddWithValue("@Username", Databaseinfo.Username);
cmd.Parameters.AddWithValue("@Password", Databaseinfo.Password);
cmd.Parameters.AddWithValue("@Databasename", Databaseinfo.DatabaseName);
try
{
int recordsAffected = cmd.ExecuteNonQuery();
}
finally
{
connection.Close();
}
}
}
}
//Write to database
private void RegisterNow()
{
//All Contorls
List <Control_> controls = new List <Control_>
{
new Control_(FirstName_txt, Visuals.Colored),
new Control_(MiddleName_txt, Visuals.Colored),
new Control_(LastName_txt, Visuals.Colored),
new Control_(Email_txt, Visuals.Colored),
new Control_(Password_txt, Visuals.Colored),
new Control_(PasswordSecond_txt, Visuals.Colored)
};
//If all fields are filled in
if (ControlState.Execute(controls, Visuals.Colored))
{
using (MySqlConnection connection = DbInfo.Connection())
{
using (MySqlCommand command = new MySqlCommand())
{
command.Connection = connection;
command.CommandType = CommandType.Text;
command.CommandText =
"INSERT INTO `users`(`ID`, `Firstname`, `Middlename`, `Lastname`, `Email`, `Password`, `Pin`) " +
"VALUES (@ID,@Firstname,@Middlename,@Lastname,@Email,@Password,@Pin)";
command.Parameters.AddWithValue("@ID", "");
command.Parameters.AddWithValue("@Firstname", FirstName_txt.Text);
command.Parameters.AddWithValue("@Middlename", MiddleName_txt.Text);
command.Parameters.AddWithValue("@Lastname", LastName_txt.Text);
command.Parameters.AddWithValue("@Email", Email_txt.Text);
command.Parameters.AddWithValue("@Password", Password_txt.Text);
command.Parameters.AddWithValue("@Pin", int.Parse("0"));
try
{
//connection.Open();
int recordsAffected = command.ExecuteNonQuery();
MessageBox.Show("person has added");
}
catch (SqlException)
{
throw;
}
finally
{
connection.Close();
}
}
}
}
}
//Write to database
private void WriteToDB()
{
//All Contorls
List <Control_> controls = new List <Control_>
{
new Control_(DatabasName_txt, Visuals.Colored),
new Control_(DataSource_txt, Visuals.Colored),
};
//If all fields are filled in
if (ControlState.Execute(controls, Visuals.Colored))
{
using (MySqlConnection connection = DbInfo.Connection())
{
using (MySqlCommand command = new MySqlCommand())
{
command.Connection = connection;
command.CommandType = CommandType.Text;
command.CommandText =
"INSERT INTO `databases`(`ID`, `Datasource`, `Username`, `Password`, `Databasename`) " +
"VALUES (@ID,@DataSource,@Username,@Password,@DatabasName)";
command.Parameters.AddWithValue("@ID", "");
command.Parameters.AddWithValue("@DatabasName", DatabasName_txt.Text);
command.Parameters.AddWithValue("@DataSource", DataSource_txt.Text);
command.Parameters.AddWithValue("@Username", Username_txt.Text);
command.Parameters.AddWithValue("@Password", Password_txt.Text);
try
{
//connection.Open();
int recordsAffected = command.ExecuteNonQuery();
MessageBox.Show("Database has been added!");
this.Close();
}
catch (SqlException)
{
throw;
}
finally
{
connection.Close();
}
}
}
}
}
//Update new Database
public static void UpdateDatabase(DatabaseInfo Databaseinfo)
{
//Create Connection
using (MySqlConnection connection = DbInfo.Connection())
{
//Create Cmd
using (MySqlCommand cmd = new MySqlCommand())
{
cmd.Connection = connection;
cmd.CommandType = CommandType.Text;
//Create CommandText
cmd.CommandText = "UPDATE `databases` SET " +
"`Datasource`=[value-2]," +
"`Username`=[value-3]," +
"`Password`=[value-4]," +
"`Databasename`=[value-5]" +
" WHERE `ID`=@ID";
//Set Parameters
cmd.Parameters.AddWithValue("@ID", Databaseinfo.ID);
cmd.Parameters.AddWithValue("@Datasource", Databaseinfo.DataSource);
cmd.Parameters.AddWithValue("@Username", Databaseinfo.Username);
cmd.Parameters.AddWithValue("@Password", Databaseinfo.Password);
cmd.Parameters.AddWithValue("@Databasename", Databaseinfo.DatabaseName);
try
{
int recordsAffected = cmd.ExecuteNonQuery();
}
finally
{
connection.Close();
}
}
}
}
internal static void LoginNow(string Username, string Password, Window loginform)
{
//Loading tables
LoadTables();
bool match = false;
int MatchID = 0;
for (int i = 0; i < DB_Info.Count; i++)
{
if (!match)
{
//Build relations
List <DBrelation> Relations = new List <DBrelation>
{
new DBrelation(Username, DB_Info[i].UsernameField),
new DBrelation(Password, DB_Info[i].PasswordField)
};
//create connection and open it
MySqlConnection connection = DbInfo.Connection();
//try to connect to database
try
{
//Build Mysql command
MySqlCommand cmd = new MySqlCommand("SELECT * FROM " + (DB_Info[i].TableName) + " WHERE " + (WHERE_builder(Relations)), connection);
//Get ID
MySqlCommand id_cmd = connection.CreateCommand();
id_cmd.CommandText =
"SELECT `" + DB_Info[i].IDfieldname +
"` FROM `" + DB_Info[i].TableName +
"` WHERE `" + DB_Info[i].UsernameField + "` = '" + Username +
"' AND `" + DB_Info[i].PasswordField + "` = '" + Password + "'";
MySqlDataReader reader = id_cmd.ExecuteReader();
//if match is found
if (reader.Read())
{
//Set match info
match = true;
MatchID = int.Parse(reader[DB_Info[i].IDfieldname].ToString());
//MessageBox.Show("Table: " + DB_Info[i].TableName + "\n Id: " + MatchID);
DB_Info[i].MatchID = MatchID;
//set userinfo
UserInfo.SetUserInfo(MatchID);
//open form
OpenForm(DB_Info[i]);
break;
}
}
//finally
finally
{
//check state and clone
if (connection.State == ConnectionState.Open)
{
connection.Clone();
}
}
}
else
{
MessageBox.Show("Table: " + DB_Info[i].TableName + "\n Id: " + MatchID);
}
}
//If there is no match found in any of the loaded tables
if (!match)
{
MessageBox.Show("There was no match found in the following tables: \n" + LoadedTables_ToString());
}
}
