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); } }