Part IV: MySQL and .NET: Classes

We are going to create two classes, Bookmark and Category, which represent the two tables.

First, If you don't have the “App_Code” folder in your project already, right click on the project > Add ASP.NET Folder > App_Code.

Now add two classes to App_Code, Bookmark and Category, and add the following lines to use the DataTable and MySQL classes:

using System.Data;
using MySql.Data.MySqlClient;

The classes are somewhat similar, every function connects to the database server, sets the stored procedure parameter, performs the operation and returns data in the case of a select statement.

Let's look at one common piece of code line by line:

// create a MySQL connection object and
// set the connection string
MySqlConnection conn = new MySqlConnection(ConfigurationManager.ConnectionStrings["DBMonkey"].ToString());

// create a MySQL command to perform the SQL operations

MySqlCommand cmd = new MySqlCommand();

try
{ 
    // set the command's connection object
    cmd.Connection = conn; 

    // set the name of the stored procedure
    cmd.CommandText = "categoryDelete"; 

   
   // tell the command that it will execute a stored procedure
    cmd.CommandType = CommandType.StoredProcedure; 

    // add a MySQL parameter object to the command and
    // set the parameter name and value
    cmd.Parameters.Add(new MySqlParameter("in_categoryId", categoryId));

    // open the connection
    conn.Open();

    // execute the command
    cmd.ExecuteNonQuery();
}
catch (Exception ex)
{
    throw new Exception(ex.Message);
}
finally
{ 
    // close the connection
    conn.Close();
}

That was the delete function from the Category class. Simple, right!

Now let’s look at the Category class:

using System;
using System.Data;
using System.Configuration;
using MySql.Data.MySqlClient;

// A category has many bookmarks
public class Category
{
    // Get all categories.
    public DataTable Get()
    { ....   }

    // Add a new category.
    // Call the Update function and pass 0 for categoryId,
    // the stored procedure will add a new record.
    public void Insert(string title)
    {
        Update(0, title);
    }

    // Update existing category if categroyId > 0,
    // or add a new one if categoryId = 0.
    public void Update(int categoryId, string title)
    {    ...    }

 

    // Delete category by category id.
    public void Delete(int categoryId)
    { ...    }

}

And the Bookmark Class:

using System;
using System.Data;
using System.Configuration;
using MySql.Data.MySqlClient;

// A bookmark has only one category
public class Bookmark
{
    // Get all bookmarks.
   // Pass zero to the stored procedure and it will return all bookmarks.

    public DataTable Get()
    {
        return GetByCategory(0);
    }

    // Get bookmarks by category id.
    public DataTable GetByCategory(int categoryId)
    {    ...    }

 

    // Add a new bookmark.
    // Call the Update function and pass 0 for bookmarkId,
    // the stored procedure will add a new record.
    public void Insert(string title, string url, int categoryId)
    {
        Update(0, title, url, categoryId);
    }

    // Update existing bookmark if bookmarkId > 0,
    // or add a new one if bookmarkId = 0.
    public void Update(int bookmarkId, string title, string url, int categoryId)
    {   ...    }

 

    // Delete bookmark by bookmark id.
    public void Delete(int bookmarkId)
    { ...     }
}

You can view the complete code here:
Category Class
Bookmark Class

In Part V we'll create the web pages and put everything to together.