public bool createConfigFile()
{
try
{
connectionFile = new XmlDocument();
XmlDeclaration dec = connectionFile.CreateXmlDeclaration("1.0", "UTF-8", null);
connectionFile.AppendChild(dec);
XmlElement root = connectionFile.CreateElement("Main");
XmlElement defaultCo = connectionFile.CreateElement("Server");
XmlElement version = connectionFile.CreateElement("Version");
XmlElement dllVersion = connectionFile.CreateElement("TiersFileVersion");
XmlElement path = connectionFile.CreateElement("IP");
XmlElement port = connectionFile.CreateElement("Port");
path.SetAttribute("IP", "127.0.0.1");
port.SetAttribute("Port", "11347");
version.SetAttribute("CurrentVersion", "1.1.0");
dllVersion.SetAttribute("CurrentVersion", "1.0.1");
defaultCo.AppendChild(path);
defaultCo.AppendChild(port);
root.AppendChild(defaultCo);
root.AppendChild(version);
connectionFile.AppendChild(root);
connectionFile.Save(SR2DURLs.configXmlFile.getUrlFromRoot());
SR2DLogger.log("Config file created.", true);
connectionFile.Load(SR2DURLs.configXmlFile.getUrlFromRoot());
return(true);
}
catch (Exception e)
{
SR2DLogger.error(e, true);
return(false);
}
}
public bool createFolder(string path)
{
try
{
Directory.CreateDirectory(path);
SR2DLogger.log("Main folder has been created.", false);
return(true);
}
catch (Exception e)
{
SR2DLogger.error(e, true);
return(false);
}
}
//Clode the connection
public bool close()
{
try
{
databaseConnection.Close();
return(true);
}
catch (Exception e)
{
SR2DLogger.error(e, false);
return(false);
}
}
//Change the database
public bool changeDatabase(string dbName)
{
try
{
databaseConnection.ChangeDatabase(dbName);
SR2DLogger.log("Database changed", false);
return(true);
}
catch (Exception e)
{
SR2DLogger.error(e, false);
statut = "closed";
return(false);
}
}
public bool createConnectionFile()
{
try
{
connectionFile = new XmlDocument();
XmlDeclaration dec = connectionFile.CreateXmlDeclaration("1.0", "UTF-8", null);
connectionFile.AppendChild(dec);
XmlElement root = connectionFile.CreateElement("Connections");
XmlElement defaultCo = connectionFile.CreateElement("Default");
XmlElement path = connectionFile.CreateElement("DatabaseName");
XmlElement username = connectionFile.CreateElement("Username");
XmlElement password = connectionFile.CreateElement("Password");
XmlElement port = connectionFile.CreateElement("Port");
XmlElement source = connectionFile.CreateElement("Source");
defaultCo.SetAttribute("Name", "default");
defaultCo.SetAttribute("DefaultCo", "No");
path.SetAttribute("DatabaseName", "default");
username.SetAttribute("Username", "default");
password.SetAttribute("Password", "default");
port.SetAttribute("Port", "3050");
source.SetAttribute("Source", "default");
defaultCo.AppendChild(path);
defaultCo.AppendChild(username);
defaultCo.AppendChild(password);
defaultCo.AppendChild(port);
defaultCo.AppendChild(source);
root.AppendChild(defaultCo);
connectionFile.AppendChild(root);
connectionFile.Save(SR2DURLs.connectionXmlFile.getUrlFromRoot());
SR2DLogger.log("Connection file created.", true);
connectionFile.Load(SR2DURLs.connectionXmlFile.getUrlFromRoot());
return(true);
}
catch (Exception e)
{
SR2DLogger.error(e, true);
return(false);
}
}
//*********** END CONSTRUCTEUR **********//
//Creation d'une connection
public SR2DMessages create(string user, string password, string databaseName, string datasource, string port)
{
string connectionString = "server=" + datasource + ";port=" + port + ";uid=" + user + ";pwd=" + password + ";database=" + databaseName + ";";
databaseConnection = new MySqlConnection(connectionString);
try
{
databaseConnection.Open();
SR2DLogger.log("The database connection has been opened", false);
statut = "opened";
return(SR2DMessages.SUC_100);
}
catch (Exception e)
{
SR2DLogger.error(e, false);
statut = "closed";
return(SR2DMessages.ERR_100);
}
}
//********** END CONSTRUCTEUR **********//
//Initialisation du dataset
public bool init()
{
//***** Déclaration des tables *****//
DataTable userTable = new DataTable();
DataTable rankTable = new DataTable();
userTable.TableName = "user";
rankTable.TableName = "ranks";
//***** End Déclaration des tables *****//
//Création des adapter et remplissage du dataset
try
{
//User table adapter
userDataAdapter = new MySqlDataAdapter();
userDataAdapter.SelectCommand = new MySqlCommand("select * from user");
userDataAdapter.SelectCommand.Connection = connection.DatabaseConnection;
userDataAdapter.InsertCommand = new MySqlCommand("insert into user(name, password, rank) values (@u, @p, @r)", connection.DatabaseConnection);
userDataAdapter.InsertCommand.Connection = connection.DatabaseConnection;
userDataAdapter.InsertCommand.Parameters.Add(new MySqlParameter("u", MySqlDbType.Text));
userDataAdapter.InsertCommand.Parameters.Add(new MySqlParameter("p", MySqlDbType.Text));
userDataAdapter.InsertCommand.Parameters.Add(new MySqlParameter("r", MySqlDbType.Int16));
userDataAdapter.InsertCommand.Parameters[0].Direction = ParameterDirection.Input;
userDataAdapter.InsertCommand.Parameters[1].Direction = ParameterDirection.Input;
userDataAdapter.InsertCommand.Parameters[2].Direction = ParameterDirection.Input;
userDataAdapter.InsertCommand.Parameters[0].SourceColumn = "name";
userDataAdapter.InsertCommand.Parameters[1].SourceColumn = "password";
userDataAdapter.InsertCommand.Parameters[2].SourceColumn = "rank";
userDataAdapter.UpdateCommand = new MySqlCommand("update user set(name, password, rank) = (@u, @p, @r) where id = @idU", connection.DatabaseConnection);
userDataAdapter.UpdateCommand.Connection = connection.DatabaseConnection;
userDataAdapter.UpdateCommand.Parameters.Add(new MySqlParameter("idU", MySqlDbType.Int16));
userDataAdapter.UpdateCommand.Parameters.Add(new MySqlParameter("u", MySqlDbType.Text));
userDataAdapter.UpdateCommand.Parameters.Add(new MySqlParameter("p", MySqlDbType.Text));
userDataAdapter.UpdateCommand.Parameters.Add(new MySqlParameter("r", MySqlDbType.Int16));
userDataAdapter.UpdateCommand.Parameters[0].Direction = ParameterDirection.Input;
userDataAdapter.UpdateCommand.Parameters[1].Direction = ParameterDirection.Input;
userDataAdapter.UpdateCommand.Parameters[2].Direction = ParameterDirection.Input;
userDataAdapter.UpdateCommand.Parameters[3].Direction = ParameterDirection.Input;
userDataAdapter.UpdateCommand.Parameters[0].SourceColumn = "id";
userDataAdapter.UpdateCommand.Parameters[1].SourceColumn = "name";
userDataAdapter.UpdateCommand.Parameters[2].SourceColumn = "password";
userDataAdapter.UpdateCommand.Parameters[3].SourceColumn = "rank";
userDataAdapter.DeleteCommand = new MySqlCommand("delete from user where id = @id", connection.DatabaseConnection);
userDataAdapter.DeleteCommand.Connection = connection.DatabaseConnection;
userDataAdapter.DeleteCommand.Parameters.Add(new MySqlParameter("id", MySqlDbType.Int16));
userDataAdapter.DeleteCommand.Parameters[0].Direction = ParameterDirection.Input;
userDataAdapter.DeleteCommand.Parameters[0].SourceColumn = "id";
//Ranks table adapter
rankDataAdapter = new MySqlDataAdapter();
rankDataAdapter.SelectCommand = new MySqlCommand("select * from ranks");
rankDataAdapter.SelectCommand.Connection = connection.DatabaseConnection;
rankDataAdapter.InsertCommand = new MySqlCommand("insert into ranks(rank_number, name) values (@rN, @n)", connection.DatabaseConnection);
rankDataAdapter.InsertCommand.Connection = connection.DatabaseConnection;
rankDataAdapter.InsertCommand.Parameters.Add(new MySqlParameter("rN", MySqlDbType.Int16));
rankDataAdapter.InsertCommand.Parameters.Add(new MySqlParameter("n", MySqlDbType.Text));
rankDataAdapter.InsertCommand.Parameters[0].Direction = ParameterDirection.Input;
rankDataAdapter.InsertCommand.Parameters[1].Direction = ParameterDirection.Input;
rankDataAdapter.InsertCommand.Parameters[0].SourceColumn = "rank_number";
rankDataAdapter.InsertCommand.Parameters[1].SourceColumn = "name";
rankDataAdapter.UpdateCommand = new MySqlCommand("update ranks set(rank_number, name) = (@rN, @n) where id = @id", connection.DatabaseConnection);
rankDataAdapter.UpdateCommand.Connection = connection.DatabaseConnection;
rankDataAdapter.UpdateCommand.Parameters.Add(new MySqlParameter("rN", MySqlDbType.Int16));
rankDataAdapter.UpdateCommand.Parameters.Add(new MySqlParameter("n", MySqlDbType.Text));
rankDataAdapter.UpdateCommand.Parameters.Add(new MySqlParameter("id", MySqlDbType.Int16));
rankDataAdapter.UpdateCommand.Parameters[0].Direction = ParameterDirection.Input;
rankDataAdapter.UpdateCommand.Parameters[1].Direction = ParameterDirection.Input;
rankDataAdapter.UpdateCommand.Parameters[2].Direction = ParameterDirection.Input;
rankDataAdapter.UpdateCommand.Parameters[0].SourceColumn = "rank_number";
rankDataAdapter.UpdateCommand.Parameters[1].SourceColumn = "name";
rankDataAdapter.UpdateCommand.Parameters[2].SourceColumn = "id";
rankDataAdapter.DeleteCommand = new MySqlCommand("delete from ranks where id = @id", connection.DatabaseConnection);
rankDataAdapter.DeleteCommand.Connection = connection.DatabaseConnection;
rankDataAdapter.DeleteCommand.Parameters.Add(new MySqlParameter("id", MySqlDbType.Int16));
rankDataAdapter.DeleteCommand.Parameters[0].Direction = ParameterDirection.Input;
rankDataAdapter.DeleteCommand.Parameters[0].SourceColumn = "id";
//Remplissage des tables
userDataAdapter.Fill(userTable);
rankDataAdapter.Fill(rankTable);
//Modifiaction des tables
DataColumn[] userDC = { null };
DataColumn[] rankDC = { null };
userDC[0] = userTable.Columns["id"];
rankDC[0] = rankTable.Columns["id"];
userTable.PrimaryKey = userDC;
rankTable.PrimaryKey = rankDC;
//Remplissage du dataset
ds.Tables.Add(userTable);
ds.Tables.Add(rankTable);
return(true);
}
catch (Exception e)
{
SR2DLogger.error(e, false);
return(false);
}
}
//Init all database adapters
public void init()
{
/*Dataset tables*/
DataTable badgesTable = new DataTable();
DataTable foodTable = new DataTable();
DataTable foodTypesTable = new DataTable();
DataTable bookingTable = new DataTable();
DataTable varsTable = new DataTable();
DataTable excludedFoodTypesTable = new DataTable();
/*Tables name*/
badgesTable.TableName = "badges";
foodTable.TableName = "food";
foodTypesTable.TableName = "food_types";
bookingTable.TableName = "booking";
varsTable.TableName = "vars";
excludedFoodTypesTable.TableName = "excluded_food_types_join";
try
{
//Badges table adapter
badgesDataAdapter = new MySqlDataAdapter();
//Select command
badgesDataAdapter.SelectCommand = new MySqlCommand("select * from badges");
badgesDataAdapter.SelectCommand.Connection = connection.DatabaseConnection;
//Insert command
badgesDataAdapter.InsertCommand = new MySqlCommand("insert into badges(first_name, name, code_id, class_code, class_fullname, passed, retired) values (@fn, @n, @cID, @clC, @clFn, @p, @r)", connection.DatabaseConnection);
badgesDataAdapter.InsertCommand.Connection = connection.DatabaseConnection;
badgesDataAdapter.InsertCommand.Parameters.Add(new MySqlParameter("fn", MySqlDbType.Text));
badgesDataAdapter.InsertCommand.Parameters.Add(new MySqlParameter("n", MySqlDbType.Text));
badgesDataAdapter.InsertCommand.Parameters.Add(new MySqlParameter("cID", MySqlDbType.Text));
badgesDataAdapter.InsertCommand.Parameters.Add(new MySqlParameter("clC", MySqlDbType.Text));
badgesDataAdapter.InsertCommand.Parameters.Add(new MySqlParameter("clFn", MySqlDbType.Text));
badgesDataAdapter.InsertCommand.Parameters.Add(new MySqlParameter("p", MySqlDbType.Int16));
badgesDataAdapter.InsertCommand.Parameters.Add(new MySqlParameter("r", MySqlDbType.Int16));
badgesDataAdapter.InsertCommand.Parameters[0].Direction = ParameterDirection.Input;
badgesDataAdapter.InsertCommand.Parameters[1].Direction = ParameterDirection.Input;
badgesDataAdapter.InsertCommand.Parameters[2].Direction = ParameterDirection.Input;
badgesDataAdapter.InsertCommand.Parameters[3].Direction = ParameterDirection.Input;
badgesDataAdapter.InsertCommand.Parameters[4].Direction = ParameterDirection.Input;
badgesDataAdapter.InsertCommand.Parameters[5].Direction = ParameterDirection.Input;
badgesDataAdapter.InsertCommand.Parameters[6].Direction = ParameterDirection.Input;
badgesDataAdapter.InsertCommand.Parameters[0].SourceColumn = "first_name";
badgesDataAdapter.InsertCommand.Parameters[1].SourceColumn = "name";
badgesDataAdapter.InsertCommand.Parameters[2].SourceColumn = "code_id";
badgesDataAdapter.InsertCommand.Parameters[3].SourceColumn = "class_code";
badgesDataAdapter.InsertCommand.Parameters[4].SourceColumn = "class_fullname";
badgesDataAdapter.InsertCommand.Parameters[5].SourceColumn = "passed";
badgesDataAdapter.InsertCommand.Parameters[6].SourceColumn = "retired";
//Update command
badgesDataAdapter.UpdateCommand = new MySqlCommand("update badges set first_name=@fn, name=@n, code_id=@cID, class_code=@clC, class_fullname=@clFn, passed=@p, retired=@r where id = @id", connection.DatabaseConnection);
badgesDataAdapter.UpdateCommand.Connection = connection.DatabaseConnection;
badgesDataAdapter.UpdateCommand.Parameters.Add(new MySqlParameter("fn", MySqlDbType.Text));
badgesDataAdapter.UpdateCommand.Parameters.Add(new MySqlParameter("n", MySqlDbType.Text));
badgesDataAdapter.UpdateCommand.Parameters.Add(new MySqlParameter("cID", MySqlDbType.Text));
badgesDataAdapter.UpdateCommand.Parameters.Add(new MySqlParameter("clC", MySqlDbType.Text));
badgesDataAdapter.UpdateCommand.Parameters.Add(new MySqlParameter("clFn", MySqlDbType.Text));
badgesDataAdapter.UpdateCommand.Parameters.Add(new MySqlParameter("p", MySqlDbType.Int16));
badgesDataAdapter.UpdateCommand.Parameters.Add(new MySqlParameter("r", MySqlDbType.Int16));
badgesDataAdapter.UpdateCommand.Parameters.Add(new MySqlParameter("id", MySqlDbType.Int16));
badgesDataAdapter.UpdateCommand.Parameters[0].Direction = ParameterDirection.Input;
badgesDataAdapter.UpdateCommand.Parameters[1].Direction = ParameterDirection.Input;
badgesDataAdapter.UpdateCommand.Parameters[2].Direction = ParameterDirection.Input;
badgesDataAdapter.UpdateCommand.Parameters[3].Direction = ParameterDirection.Input;
badgesDataAdapter.UpdateCommand.Parameters[4].Direction = ParameterDirection.Input;
badgesDataAdapter.UpdateCommand.Parameters[5].Direction = ParameterDirection.Input;
badgesDataAdapter.UpdateCommand.Parameters[6].Direction = ParameterDirection.Input;
badgesDataAdapter.UpdateCommand.Parameters[7].Direction = ParameterDirection.Input;
badgesDataAdapter.UpdateCommand.Parameters[0].SourceColumn = "first_name";
badgesDataAdapter.UpdateCommand.Parameters[1].SourceColumn = "name";
badgesDataAdapter.UpdateCommand.Parameters[2].SourceColumn = "code_id";
badgesDataAdapter.UpdateCommand.Parameters[3].SourceColumn = "class_code";
badgesDataAdapter.UpdateCommand.Parameters[4].SourceColumn = "class_fullname";
badgesDataAdapter.UpdateCommand.Parameters[5].SourceColumn = "passed";
badgesDataAdapter.UpdateCommand.Parameters[6].SourceColumn = "retired";
badgesDataAdapter.UpdateCommand.Parameters[7].SourceColumn = "id";
//Delete command
badgesDataAdapter.DeleteCommand = new MySqlCommand("delete from badges where id = @id", connection.DatabaseConnection);
badgesDataAdapter.DeleteCommand.Connection = connection.DatabaseConnection;
badgesDataAdapter.DeleteCommand.Parameters.Add(new MySqlParameter("id", MySqlDbType.Int16));
badgesDataAdapter.DeleteCommand.Parameters[0].Direction = ParameterDirection.Input;
badgesDataAdapter.DeleteCommand.Parameters[0].SourceColumn = "id";
/*DEBUG ONLY*/
badgesDataAdapter.RowUpdated += new MySqlRowUpdatedEventHandler(rowUpdatedEvent);
/*DEBUG ONLY*/
//Fill tables thanks to select command
badgesDataAdapter.Fill(badgesTable);
//Food table adapter
foodDataAdapter = new MySqlDataAdapter();
foodDataAdapter.SelectCommand = new MySqlCommand("select * from food");
foodDataAdapter.SelectCommand.Connection = connection.DatabaseConnection;
foodDataAdapter.InsertCommand = new MySqlCommand("insert into food(name, quantity, type_id, price) values (@n, @q, @tID, @p)", connection.DatabaseConnection);
foodDataAdapter.InsertCommand.Connection = connection.DatabaseConnection;
foodDataAdapter.InsertCommand.Parameters.Add(new MySqlParameter("n", MySqlDbType.Text));
foodDataAdapter.InsertCommand.Parameters.Add(new MySqlParameter("q", MySqlDbType.Int16));
foodDataAdapter.InsertCommand.Parameters.Add(new MySqlParameter("tID", MySqlDbType.Int16));
foodDataAdapter.InsertCommand.Parameters.Add(new MySqlParameter("p", MySqlDbType.Decimal));
foodDataAdapter.InsertCommand.Parameters[0].Direction = ParameterDirection.Input;
foodDataAdapter.InsertCommand.Parameters[1].Direction = ParameterDirection.Input;
foodDataAdapter.InsertCommand.Parameters[2].Direction = ParameterDirection.Input;
foodDataAdapter.InsertCommand.Parameters[3].Direction = ParameterDirection.Input;
foodDataAdapter.InsertCommand.Parameters[0].SourceColumn = "name";
foodDataAdapter.InsertCommand.Parameters[1].SourceColumn = "quantity";
foodDataAdapter.InsertCommand.Parameters[2].SourceColumn = "type_id";
foodDataAdapter.InsertCommand.Parameters[3].SourceColumn = "price";
foodDataAdapter.UpdateCommand = new MySqlCommand("update food set name=@n, quantity=@q, type_id=@tID, price=@p where id = @id", connection.DatabaseConnection);
foodDataAdapter.UpdateCommand.Connection = connection.DatabaseConnection;
foodDataAdapter.UpdateCommand.Parameters.Add(new MySqlParameter("n", MySqlDbType.Text));
foodDataAdapter.UpdateCommand.Parameters.Add(new MySqlParameter("q", MySqlDbType.Int16));
foodDataAdapter.UpdateCommand.Parameters.Add(new MySqlParameter("tID", MySqlDbType.Int16));
foodDataAdapter.UpdateCommand.Parameters.Add(new MySqlParameter("p", MySqlDbType.Int16));
foodDataAdapter.UpdateCommand.Parameters.Add(new MySqlParameter("id", MySqlDbType.Int16));
foodDataAdapter.UpdateCommand.Parameters[0].Direction = ParameterDirection.Input;
foodDataAdapter.UpdateCommand.Parameters[1].Direction = ParameterDirection.Input;
foodDataAdapter.UpdateCommand.Parameters[2].Direction = ParameterDirection.Input;
foodDataAdapter.UpdateCommand.Parameters[3].Direction = ParameterDirection.Input;
foodDataAdapter.UpdateCommand.Parameters[4].Direction = ParameterDirection.Input;
foodDataAdapter.UpdateCommand.Parameters[0].SourceColumn = "name";
foodDataAdapter.UpdateCommand.Parameters[1].SourceColumn = "quantity";
foodDataAdapter.UpdateCommand.Parameters[2].SourceColumn = "type_id";
foodDataAdapter.UpdateCommand.Parameters[3].SourceColumn = "price";
foodDataAdapter.UpdateCommand.Parameters[4].SourceColumn = "id";
foodDataAdapter.DeleteCommand = new MySqlCommand("delete from food where id = @id", connection.DatabaseConnection);
foodDataAdapter.DeleteCommand.Connection = connection.DatabaseConnection;
foodDataAdapter.DeleteCommand.Parameters.Add(new MySqlParameter("id", MySqlDbType.Int16));
foodDataAdapter.DeleteCommand.Parameters[0].Direction = ParameterDirection.Input;
foodDataAdapter.DeleteCommand.Parameters[0].SourceColumn = "id";
foodDataAdapter.RowUpdated += new MySqlRowUpdatedEventHandler(rowUpdatedEvent);
foodDataAdapter.Fill(foodTable);
//Food types table adapter
foodTypesDataAdapter = new MySqlDataAdapter();
foodTypesDataAdapter.SelectCommand = new MySqlCommand("select * from food_types");
foodTypesDataAdapter.SelectCommand.Connection = connection.DatabaseConnection;
foodTypesDataAdapter.InsertCommand = new MySqlCommand("insert into food_types(id, name) values (@id, @n)", connection.DatabaseConnection);
foodTypesDataAdapter.InsertCommand.Connection = connection.DatabaseConnection;
foodTypesDataAdapter.InsertCommand.Parameters.Add(new MySqlParameter("id", MySqlDbType.Int16));
foodTypesDataAdapter.InsertCommand.Parameters.Add(new MySqlParameter("n", MySqlDbType.Text));
foodTypesDataAdapter.InsertCommand.Parameters[0].Direction = ParameterDirection.Input;
foodTypesDataAdapter.InsertCommand.Parameters[1].Direction = ParameterDirection.Input;
foodTypesDataAdapter.InsertCommand.Parameters[0].SourceColumn = "id";
foodTypesDataAdapter.InsertCommand.Parameters[1].SourceColumn = "name";
foodTypesDataAdapter.UpdateCommand = new MySqlCommand("update food_types set name=@n where id = @id", connection.DatabaseConnection);
foodTypesDataAdapter.UpdateCommand.Connection = connection.DatabaseConnection;
foodTypesDataAdapter.UpdateCommand.Parameters.Add(new MySqlParameter("id", MySqlDbType.Int16));
foodTypesDataAdapter.UpdateCommand.Parameters.Add(new MySqlParameter("n", MySqlDbType.Text));
foodTypesDataAdapter.UpdateCommand.Parameters[0].Direction = ParameterDirection.Input;
foodTypesDataAdapter.UpdateCommand.Parameters[1].Direction = ParameterDirection.Input;
foodTypesDataAdapter.UpdateCommand.Parameters[0].SourceColumn = "id";
foodTypesDataAdapter.UpdateCommand.Parameters[1].SourceColumn = "name";
foodTypesDataAdapter.DeleteCommand = new MySqlCommand("delete from food_types where id = @id", connection.DatabaseConnection);
foodTypesDataAdapter.DeleteCommand.Connection = connection.DatabaseConnection;
foodTypesDataAdapter.DeleteCommand.Parameters.Add(new MySqlParameter("id", MySqlDbType.Int16));
foodTypesDataAdapter.DeleteCommand.Parameters[0].Direction = ParameterDirection.Input;
foodTypesDataAdapter.DeleteCommand.Parameters[0].SourceColumn = "id";
foodTypesDataAdapter.Fill(foodTypesTable);
//Booking table adapter
bookingDataAdapter = new MySqlDataAdapter();
bookingDataAdapter.SelectCommand = new MySqlCommand("select * from booking");
bookingDataAdapter.SelectCommand.Connection = connection.DatabaseConnection;
bookingDataAdapter.InsertCommand = new MySqlCommand("insert into booking(badge_id, food_id) values (@idB, @idF)", connection.DatabaseConnection);
bookingDataAdapter.InsertCommand.Connection = connection.DatabaseConnection;
bookingDataAdapter.InsertCommand.Parameters.Add(new MySqlParameter("idB", MySqlDbType.Int16));
bookingDataAdapter.InsertCommand.Parameters.Add(new MySqlParameter("idF", MySqlDbType.Int16));
bookingDataAdapter.InsertCommand.Parameters[0].Direction = ParameterDirection.Input;
bookingDataAdapter.InsertCommand.Parameters[1].Direction = ParameterDirection.Input;
bookingDataAdapter.InsertCommand.Parameters[0].SourceColumn = "badge_id";
bookingDataAdapter.InsertCommand.Parameters[1].SourceColumn = "food_id";
bookingDataAdapter.UpdateCommand = new MySqlCommand("update booking set badge_id=@idB, food_id=@idF where id = @id", connection.DatabaseConnection);
bookingDataAdapter.UpdateCommand.Connection = connection.DatabaseConnection;
bookingDataAdapter.UpdateCommand.Parameters.Add(new MySqlParameter("id", MySqlDbType.Int16));
bookingDataAdapter.UpdateCommand.Parameters.Add(new MySqlParameter("idB", MySqlDbType.Int16));
bookingDataAdapter.UpdateCommand.Parameters.Add(new MySqlParameter("idF", MySqlDbType.Int16));
bookingDataAdapter.UpdateCommand.Parameters[0].Direction = ParameterDirection.Input;
bookingDataAdapter.UpdateCommand.Parameters[1].Direction = ParameterDirection.Input;
bookingDataAdapter.UpdateCommand.Parameters[2].Direction = ParameterDirection.Input;
bookingDataAdapter.UpdateCommand.Parameters[0].SourceColumn = "id";
bookingDataAdapter.UpdateCommand.Parameters[1].SourceColumn = "badge_id";
bookingDataAdapter.UpdateCommand.Parameters[2].SourceColumn = "food_id";
bookingDataAdapter.DeleteCommand = new MySqlCommand("delete from booking where id = @id", connection.DatabaseConnection);
bookingDataAdapter.DeleteCommand.Connection = connection.DatabaseConnection;
bookingDataAdapter.DeleteCommand.Parameters.Add(new MySqlParameter("id", MySqlDbType.Int16));
bookingDataAdapter.DeleteCommand.Parameters[0].Direction = ParameterDirection.Input;
bookingDataAdapter.DeleteCommand.Parameters[0].SourceColumn = "id";
bookingDataAdapter.Fill(bookingTable);
//Vars table adapter
varsDataAdapter = new MySqlDataAdapter();
varsDataAdapter.SelectCommand = new MySqlCommand("select * from vars");
varsDataAdapter.SelectCommand.Connection = connection.DatabaseConnection;
varsDataAdapter.InsertCommand = new MySqlCommand("insert into vars(k, value) values (@k, @v)", connection.DatabaseConnection);
varsDataAdapter.InsertCommand.Connection = connection.DatabaseConnection;
varsDataAdapter.InsertCommand.Parameters.Add(new MySqlParameter("k", MySqlDbType.Text));
varsDataAdapter.InsertCommand.Parameters.Add(new MySqlParameter("v", MySqlDbType.Text));
varsDataAdapter.InsertCommand.Parameters[0].Direction = ParameterDirection.Input;
varsDataAdapter.InsertCommand.Parameters[1].Direction = ParameterDirection.Input;
varsDataAdapter.InsertCommand.Parameters[0].SourceColumn = "k";
varsDataAdapter.InsertCommand.Parameters[1].SourceColumn = "value";
varsDataAdapter.UpdateCommand = new MySqlCommand("update vars set k=@k, value=@v where id=@id", connection.DatabaseConnection);
varsDataAdapter.UpdateCommand.Connection = connection.DatabaseConnection;
varsDataAdapter.UpdateCommand.Parameters.Add(new MySqlParameter("k", MySqlDbType.Text));
varsDataAdapter.UpdateCommand.Parameters.Add(new MySqlParameter("v", MySqlDbType.Text));
varsDataAdapter.UpdateCommand.Parameters.Add(new MySqlParameter("id", MySqlDbType.Int16));
varsDataAdapter.UpdateCommand.Parameters[0].Direction = ParameterDirection.Input;
varsDataAdapter.UpdateCommand.Parameters[1].Direction = ParameterDirection.Input;
varsDataAdapter.UpdateCommand.Parameters[2].Direction = ParameterDirection.Input;
varsDataAdapter.UpdateCommand.Parameters[0].SourceColumn = "k";
varsDataAdapter.UpdateCommand.Parameters[1].SourceColumn = "value";
varsDataAdapter.UpdateCommand.Parameters[2].SourceColumn = "id";
varsDataAdapter.DeleteCommand = new MySqlCommand("delete from vars where id = @id", connection.DatabaseConnection);
varsDataAdapter.DeleteCommand.Connection = connection.DatabaseConnection;
varsDataAdapter.DeleteCommand.Parameters.Add(new MySqlParameter("id", MySqlDbType.Int16));
varsDataAdapter.DeleteCommand.Parameters[0].Direction = ParameterDirection.Input;
varsDataAdapter.DeleteCommand.Parameters[0].SourceColumn = "id";
varsDataAdapter.Fill(varsTable);
//Excluded food types table adapter
excludedFoodTypesDataAdapter = new MySqlDataAdapter();
excludedFoodTypesDataAdapter.SelectCommand = new MySqlCommand("select * from excluded_food_types_join");
excludedFoodTypesDataAdapter.SelectCommand.Connection = connection.DatabaseConnection;
excludedFoodTypesDataAdapter.InsertCommand = new MySqlCommand("insert into excluded_food_types_join(master_food_types, slave_food_types) values (@mFT, @sFT)", connection.DatabaseConnection);
excludedFoodTypesDataAdapter.InsertCommand.Connection = connection.DatabaseConnection;
excludedFoodTypesDataAdapter.InsertCommand.Parameters.Add(new MySqlParameter("mFT", MySqlDbType.Int16));
excludedFoodTypesDataAdapter.InsertCommand.Parameters.Add(new MySqlParameter("sFT", MySqlDbType.Int16));
excludedFoodTypesDataAdapter.InsertCommand.Parameters[0].Direction = ParameterDirection.Input;
excludedFoodTypesDataAdapter.InsertCommand.Parameters[1].Direction = ParameterDirection.Input;
excludedFoodTypesDataAdapter.InsertCommand.Parameters[0].SourceColumn = "master_food_types";
excludedFoodTypesDataAdapter.InsertCommand.Parameters[1].SourceColumn = "slave_food_types";
excludedFoodTypesDataAdapter.UpdateCommand = new MySqlCommand("update excluded_food_types_join set master_food_types=@mFT, slave_food_types=@sFT where id = @id", connection.DatabaseConnection);
excludedFoodTypesDataAdapter.UpdateCommand.Connection = connection.DatabaseConnection;
excludedFoodTypesDataAdapter.UpdateCommand.Parameters.Add(new MySqlParameter("mFT", MySqlDbType.Int16));
excludedFoodTypesDataAdapter.UpdateCommand.Parameters.Add(new MySqlParameter("sFT", MySqlDbType.Int16));
excludedFoodTypesDataAdapter.UpdateCommand.Parameters.Add(new MySqlParameter("id", MySqlDbType.Int16));
excludedFoodTypesDataAdapter.UpdateCommand.Parameters[0].Direction = ParameterDirection.Input;
excludedFoodTypesDataAdapter.UpdateCommand.Parameters[1].Direction = ParameterDirection.Input;
excludedFoodTypesDataAdapter.UpdateCommand.Parameters[2].Direction = ParameterDirection.Input;
excludedFoodTypesDataAdapter.UpdateCommand.Parameters[0].SourceColumn = "master_food_types";
excludedFoodTypesDataAdapter.UpdateCommand.Parameters[1].SourceColumn = "slave_food_types";
excludedFoodTypesDataAdapter.UpdateCommand.Parameters[2].SourceColumn = "id";
excludedFoodTypesDataAdapter.DeleteCommand = new MySqlCommand("delete from excluded_food_types_join where id = @id", connection.DatabaseConnection);
excludedFoodTypesDataAdapter.DeleteCommand.Connection = connection.DatabaseConnection;
excludedFoodTypesDataAdapter.DeleteCommand.Parameters.Add(new MySqlParameter("id", MySqlDbType.Int16));
excludedFoodTypesDataAdapter.DeleteCommand.Parameters[0].Direction = ParameterDirection.Input;
excludedFoodTypesDataAdapter.DeleteCommand.Parameters[0].SourceColumn = "id";
excludedFoodTypesDataAdapter.Fill(excludedFoodTypesTable);
//Create datacolumn
DataColumn[] badgesDC = { null };
DataColumn[] foodDC = { null };
DataColumn[] foodTypesDC = { null };
DataColumn[] bookingDC = { null };
DataColumn[] varsDC = { null };
DataColumn[] excludedFoodTypesDC = { null };
//Set column to be the id column for table
badgesDC[0] = badgesTable.Columns["id"];
foodDC[0] = foodTable.Columns["id"];
foodTypesDC[0] = foodTypesTable.Columns["id"];
bookingDC[0] = bookingTable.Columns["id"];
varsDC[0] = varsTable.Columns["id"];
excludedFoodTypesDC[0] = excludedFoodTypesTable.Columns["id"];
//Set table primary key (id) column
badgesTable.PrimaryKey = badgesDC;
foodTable.PrimaryKey = foodDC;
foodTypesTable.PrimaryKey = foodTypesDC;
bookingTable.PrimaryKey = bookingDC;
varsTable.PrimaryKey = varsDC;
excludedFoodTypesTable.PrimaryKey = excludedFoodTypesDC;
//Add table to the dataset
ds.Tables.Add(badgesTable);
ds.Tables.Add(foodTable);
ds.Tables.Add(foodTypesTable);
ds.Tables.Add(bookingTable);
ds.Tables.Add(varsTable);
ds.Tables.Add(excludedFoodTypesTable);
}
catch (Exception e)
{
SR2DLogger.error(e, false);
}
}