DAT Blog Post 5

Week Five Sessions 7-8

What Have I Learned?

This week I learned about DELETE statements, ON DELETE CASCADE, and connecting our databases with c#.

We use delete statements to delete records within our database. This can be done or a single row, multiple rows, or all of the rows on a table.

Here are some examples

DELETE FROM `tbl_player` WHERE`playerID` > 5;
DELETE FROM `tbl_user` WHERE`userID` > 5;
DELETE FROM `tbl_inventory` WHERE`inventoryID` < 3;

We use the “on delete cascade” parameter on a foreign key so that when we delete the parent record, the child records also delete from the database. We can look at this as cutting a branch off of a tree. When we remove the branch, the leaves are removed as well since they are children of the branch.

Here is an example:

DROP TABLE IF EXISTS `tbl_inventory`;
CREATE TABLE `tbl_inventory` (
	`inventoryID` INTEGER NOT NULL AUTO_INCREMENT,
	`quantity` INTEGER DEFAULT 0 NOT NULL,
	`assetID` INTEGER NOT NULL,
	`playerID` INTEGER NOT NULL,
	PRIMARY KEY (`inventoryID`),
	FOREIGN KEY (`assetID`) REFERENCES `tbl_asset`(`assetID`) ON DELETE CASCADE  ,
	FOREIGN KEY (`playerID`) REFERENCES `tbl_player`(`playerID`) ON DELETE CASCADE  
);

When we want to connect our application to the server and the database, we must first establish a connection so that we can perform actions such as insert statements and update statements. I decided I would make a class that I can call called “Connect” rather than establishing the connection on each form.

Here is what it looks like:

public class Connect
 {
 MySqlConnection connectDB = new MySqlConnection(@"server=localhost;password=password;user id=root;persistsecurityinfo=True;database=dat602_assessmentdb_m2");
 public void openConnection()
 {
 if (connectDB.State == System.Data.ConnectionState.Closed)
  {
 connectDB.Open();
 }
 }
 public void closeConnection()
 {
 if (connectDB.State == System.Data.ConnectionState.Open)
 {
 connectDB.Close();
 }
 }
  public MySqlConnection getConnection()
 {
 return connectDB;
 }
 }

Why Have I learned This?

Delete statements are very useful if we want to remove records from tables within our database. This is especially useful for our project as we are required to delete users accounts from our game. We can use a WHERE statement to define what user we want to delete.

I believe that we learned about “on delete cascade” as it is a useful way of deleting related records across multiple tables. This will come in handy when we want to delete users from our games as they have a relationship with other tables.

I believe that we have learned about connecting our database with c# as it is a very useful skill to have. We need to make sure that our database can connect before we progress as there is no real point in trying to perform certain tasks if we can not access our database.

How Have I learned This?

I have learned this by researching what a DELETE statement is and how I implement it, followed by practising it to ensure that I get the general structure of the statement is. I also looked into how we implement the cascade parameter and ran some tests to see if it worked as expected.

Learning how to connect my database was not too much of a struggle as there is not too much to it. All I needed to do was make sure that my connection string was correct and displayed the correct information. Once this was done, there was no need to go back and practice.