Checking if record/object already exists.

Last time I said it would be nice to know if the record already exists in the database. When you want a one-to-many relationship, you don’t want to accidentally add the same record more than once. If an object is made that has the exact same values as another record, we shouldn’t add it, but simply change the index to point to the correct record. Then again, you might want to allow such duplicates (like a history log.)

So today I created a new method for DBObject called doesExist. It simply returns true or false if the object already exists in the database. One thing you have to watch out for in MySQL is checking for NULL. You can’t simply do WHERE column=NULL It won’t work. You have to call it as WHERE column IS NULL

Here is the complete method.

Something to keep in mind, it will only check columns that have been defined as ‘column_’. This could cause false results if you want it to check all the columns but you haven’t use SetProperty to set all of the columns. Then again this might be the behavior you want (as in my case.) Just make sure any column that you want checked has been set with SetProperty

/// <summary>
/// Checks if all the data except the index already exists as a record in the database.
/// </summary>
/// <param name="table"></param>
/// <param name="index_column"></param>
/// <param name="connection"></param>
/// <returns></returns>
public bool doesExist(string table, string index_column, string connection)
{
	//If there are no properties then it can't be exist
	if (this._properties.Count <= 0) { return false; }
	//Our Command
	MySqlCommand cmd = new MySqlCommand();
	//Build the Query
	StringBuilder sb_query = new StringBuilder();
	sb_query.Append("SELECT COUNT(*) FROM ");
	sb_query.Append(table);
	sb_query.Append(" WHERE ");

	//Check that all the columns match (ignore the index_column if it exists)
	foreach (string key in this._properties.Keys)
	{
		//If it's a column and it's not the index
		if (key.StartsWith("column_") && key != "column_" + index_column)
		{
			//Add the Key
			sb_query.Append(key.Substring(key.IndexOf("_") + 1));

			//Check for null entries
			if (DBNull.Value != this._properties[key])
			{
				//Cool, normal check
				sb_query.Append("=");
				sb_query.Append("?" + key);
				cmd.Parameters.AddWithValue("?" + key, this._properties[key]);
			}
			else
			{
				//Null is special
				sb_query.Append(" IS NULL ");
			}
			sb_query.Append(" AND ");
			//Now add the value

		}
	}
	//Now remove the  trailing comma
	sb_query.Remove(sb_query.Length - 5, 5);

	//Start the Database stuff
	using (MySqlConnection conn = new MySqlConnection(connection))
	{
		//Put the Command together
		cmd.CommandText = sb_query.ToString();
		cmd.Connection = conn;

		//Now run it!
		try
		{
			conn.Open();
			int count = Convert.ToInt32(cmd.ExecuteScalar());
			if (count > 0)
			{
				//it exists
				return true;
			}
		}
		catch (Exception ex)
		{
			Console.WriteLine("\nError:\n" + ex.Message);
		}
	}

	//Cool, it passes
	return false;
}