/// <summary> /// constructor for loading an existing recipie /// </summary> /// <param name="current"></param> public FrmRecipie(Recipie current) { //setup basic form InitializeComponent(); updateLbItemSearch(Program.items.Values); setProfessionAndLevel(); //load data for current recipie curRecipie = current; this.Text += current.id; cbProfession.Text = current.profession; cbCrafterLevel.Text = current.crafterLevel; result = current.result; txtResultName.Text = result.name; nudNumProduced.Value = current.resultQty; nudHours.Value = current.hours; lvIngredients.SuspendLayout(); foreach (InventoryItem iq in current.ingredients) { addIngredient(iq.item, iq.qty); } lvIngredients.ResumeLayout(); }
/// <summary> /// Loads all the items and recipies from the database /// </summary> public static void loadAllFromDB() { items = new Dictionary <int, Item>(); recipies = new List <Recipie>(); FrmLoading.setText("Connecting to DB..."); using (SqlConnection dataConnection = new SqlConnection(Program.connectionString)) { dataConnection.Open(); FrmLoading.setText("Loading Items from DB..."); //load loot DataSet dsItems = new DataSet(); string selectLoot = @"SELECT * FROM items ORDER BY id" ; using (SqlDataAdapter da = new SqlDataAdapter(selectLoot, dataConnection)) { da.Fill(dsItems); } foreach (DataRow row in dsItems.Tables[0].Rows) { Item item = new Item(); item.id = (int)row["id"]; item.name = (string)row["name"]; item.cost = (int)row["cost"]; item.description = (string)row["description"]; item.type = (string)row["type"]; item.subType = (string)row["subType"]; items.Add(item.id, item); } FrmLoading.setText("Loading Recipies from DB..."); //load recipies DataSet dsRecipies = new DataSet(); string selectRecipies = @"SELECT * FROM recipies ORDER BY id" ; using (SqlDataAdapter da = new SqlDataAdapter(selectRecipies, dataConnection)) { da.Fill(dsRecipies); } DataSet dsRecipieIngredients = new DataSet(); string selectIngredients = @"SELECT * FROM recipieIngredients ORDER BY recipieID"; using (SqlDataAdapter da = new SqlDataAdapter(selectIngredients, dataConnection)) { da.Fill(dsRecipieIngredients); } int iLine = 0; foreach (DataRow row in dsRecipies.Tables[0].Rows) { Recipie r = new Recipie(); r.id = (int)row["id"]; r.result = Program.items[(int)row["result"]]; r.resultQty = (int)row["resultQty"]; r.profession = (string)row["profession"]; r.crafterLevel = (string)row["crafterLevel"]; r.hours = (byte)row["hours"]; while (iLine < dsRecipieIngredients.Tables[0].Rows.Count) { if (((int)dsRecipieIngredients.Tables[0].Rows[iLine]["recipieID"]) == r.id) { InventoryItem iq = new InventoryItem(); iq.item = Program.items[(int)dsRecipieIngredients.Tables[0].Rows[iLine]["ingredient"]]; iq.qty = (int)dsRecipieIngredients.Tables[0].Rows[iLine]["qty"]; r.ingredients.Add(iq); } else if (((int)dsRecipieIngredients.Tables[0].Rows[iLine]["recipieID"]) > r.id) { break; } iLine++; } recipies.Add(r); } FrmLoading.setText("Generating Local Inventory Lists..."); inventories = new Dictionary <string, Inventory>(); inventories.Add("On Hand", new Inventory("On Hand")); string selectLootLocations = @"select distinct location from inventory where location != 'On Hand'"; using (SqlCommand commIngredients = new SqlCommand(selectLootLocations, dataConnection)) { using (SqlDataReader reader = commIngredients.ExecuteReader()) { while (reader.Read()) { inventories.Add(reader.GetString(0), new Inventory(reader.GetString(0))); } } } } }
/// <summary> /// event handler for the save and close button /// </summary> /// <param name="sender"></param> /// <param name="e"></param> private void btnSave_Click(object sender, EventArgs e) { if (cbProfession.Text.Replace("'", "''").Length == 0) { System.Windows.Forms.MessageBox.Show("Profession is a required field", "Error", System.Windows.Forms.MessageBoxButtons.OK, System.Windows.Forms.MessageBoxIcon.Error); return; } if (cbCrafterLevel.Text.Replace("'", "''").Length == 0) { System.Windows.Forms.MessageBox.Show("Crafter level is a required field", "Error", System.Windows.Forms.MessageBoxButtons.OK, System.Windows.Forms.MessageBoxIcon.Error); return; } if (result == null) { System.Windows.Forms.MessageBox.Show("Result is a required field", "Error", System.Windows.Forms.MessageBoxButtons.OK, System.Windows.Forms.MessageBoxIcon.Error); return; } if (lvIngredients.Items.Count == 0) { System.Windows.Forms.MessageBox.Show("Ingedients must be specified", "Error", System.Windows.Forms.MessageBoxButtons.OK, System.Windows.Forms.MessageBoxIcon.Error); return; } //setup the update to the main sql table string sql; if (this.curRecipie == null) { sql = @"INSERT INTO recipies (result, resultQty, profession, crafterLevel, hours) VALUES ('-" + result.id + @"', '" + nudNumProduced.Value + @"', '" + cbProfession.Text.Replace("'", "''") + @"', '" + cbCrafterLevel.Text.Replace("'", "''") + @"', '" + nudHours.Value + @"')"; curRecipie = new Recipie(); } else { sql = @"UPDATE recipies SET result = '" + result.id + @"', resultQty = '" + nudNumProduced.Value + @"', profession = '" + cbProfession.Text.Replace("'", "''") + @"', crafterLevel = '" + cbCrafterLevel.Text.Replace("'", "''") + @"', hours = '" + nudHours.Value + @"' WHERE id = '" + curRecipie.id + @"'"; } //update the memory copy of the recipie curRecipie.result = this.result; curRecipie.resultQty = (int)nudNumProduced.Value; curRecipie.profession = cbProfession.Text; curRecipie.crafterLevel = cbCrafterLevel.Text; curRecipie.hours = (byte)nudHours.Value; using (SqlConnection dataConnection = new SqlConnection(Program.connectionString)) { dataConnection.Open(); using (SqlCommand comm = new SqlCommand(sql, dataConnection)) { comm.ExecuteNonQuery(); } if (curRecipie.id == -1) { //find the new recipie sql = @"SELECT id FROM recipies WHERE result < 0"; using (SqlCommand comm = new SqlCommand(sql, dataConnection)) { curRecipie.id = (int)comm.ExecuteScalar(); } //mark the recipie as not new sql = @"UPDATE recipies SET result = -1*result WHERE id = '" + curRecipie.id + "'"; using (SqlCommand comm = new SqlCommand(sql, dataConnection)) { comm.ExecuteNonQuery(); } //add the link to this recipie to the rest of the program, //(since it's done by reference we can do this before the shared add ingredients code) Program.recipies.Add(curRecipie); } else { //if it's an existing recipie we already have the ids set, //but need to clear out the old ingredients before adding the new ones curRecipie.ingredients.Clear(); sql = @"DELETE FROM recipieIngredients WHERE recipieID = '" + curRecipie.id + "'"; using (SqlCommand comm = new SqlCommand(sql, dataConnection)) { comm.ExecuteNonQuery(); } } //add the ingredients to the recipie foreach (ListViewItem lvi in lvIngredients.Items) { InventoryItem iq = (InventoryItem)lvi.Tag; curRecipie.ingredients.Add(iq); sql = @"INSERT INTO recipieIngredients (recipieID, ingredient, qty) VALUES ('" + curRecipie.id + @"', '" + iq.item.id + @"', '" + iq.qty + @"')"; using (SqlCommand comm = new SqlCommand(sql, dataConnection)) { comm.ExecuteNonQuery(); } } //update the main window with the recipie Program.mainForm.lvItems_SelectedIndexChanged(null, null); } this.Close(); }