Often when working with a database I will create a class to mimic each of the tables in my database (creating a class is beyond the scope of this tutorial). After these classes are created I create helper classes that perform the database interaction for these classes. So if I have a User class then I might create a static UserDB class to perform the database work for this class. The shell of the Database class looks something like this.
public static class UserDB
{
}
The reason I choose to make these classes static is because I don't really need to create individual instances of the class. Now the first thing we'll want to do is create a class property that gets the connection string for our database. For this, you will need to know the names of the server and database as well as the user and password.
public static class UserDB
{
// Private Connection string
private static string Connection { get { return "Server=server_name;Database=database_name;Uid=username;Pwd=password;"; } }
}
Now that we have a way to easily get our connection string we can begin creating the methods our class will use to perform database actions. The first method we'll create is a way to retrieve all of the Users from the database. I like to put my query string at the top of my method to make it easier for me if I have to go back and change the query in the future. The MySqlHelper class comes with several static methods to make database operations simpler. Using the MysqlHelper class saves you from having to open and close the connection to the database and from having to create the command object as it takes care of all of that in the background for you.
public static IEnumerable getAllTestUsers()
{
// Create the query string
string query = "SELECT Username, FirstName, LastName FROM Users";
// Create a list to hold the users
List<User> users = new List<User>();
// Create an instance of the User class
User user;
// Create a MySqlDataReader using the MySqlHelper class
// We pass in the connection string and the query to the
// ExecuteReader method of the MySqlHelper class and it
// does all the heavy lifting for us
using (MySqlDataReader reader = MySqlHelper.ExecuteReader(Connection, query))
{
// Check if the reader returned any rows
if (reader.HasRows)
{
// While the reader has rows we loop through them,
// create new users, and insert them into our list
while (reader.Read())
{
user = new User();
user.Username = reader.GetString(0);
user.FirstName = reader.GetString(1);
user.LastName = reader.GetString(2);
users.Add(user);
}
}
}
// After we've read all of the rows the MySqlHelp automatically closes the connection
// Finally, we return our list of users
return users;
}
Next I want to just quickly cover how to insert data into the database using the MySqlHelper class. Once again, I'll begin by creating my query.
public static int InsertUser(User user)
{
// Query for inserting a User
string query = "INSERT INTO Users (Username, Password, FirstName, LastName) "
+ "VALUES (@user, @pass, @first, @last)";
// Create the parameters
List<MySqlParameter> parms = new List<MySqlParameter>();
parms.Add(new MySqlParameter("user", user.Username));
parms.Add(new MySqlParameter("pass", user.Password));
parms.Add(new MySqlParameter("first", user.FirstName));
parms.Add(new MySqlParameter("last", user.LastName));
// Pass the List Connection string, query, and an array of SqlParameters
// to the ExecuteNonQuery method of the MySqlHelper class. For Update,
// Insert, and Delete commands this should return the number of rows
// affected.
return MySqlHelper.ExecuteNonQuery(Connection, query, parms.ToArray());
}
Hopefully if you are wanting to try out Mono on linux, or just want to make use of MySQL databases, then this tutorial will help you get started. Here is the completed class we created.
public static class UserDB
{
// Private Connection string
private static string Connection { get { return "Server=server_name;Database=database_name;Uid=username;Pwd=password;"; } }
// Get all Users from Database
public static IEnumerable getAllTestUsers()
{
// Create the query string
string query = "SELECT Username, FirstName, LastName FROM Users";
// Create a list to hold the users
List<User> users = new List<User>();
// Create an instance of the User class
User user;
// Create a MySqlDataReader using the MySqlHelper class
// We pass in the connection string and the query to the
// ExecuteReader method of the MySqlHelper class and it
// does all the heavy lifting for us
using (MySqlDataReader reader = MySqlHelper.ExecuteReader(Connection, query))
{
// Check if the reader returned any rows
if (reader.HasRows)
{
// While the reader has rows we loop through them,
// create new users, and insert them into our list
while (reader.Read())
{
user = new User();
user.Username = reader.GetString(0);
user.FirstName = reader.GetString(1);
user.LastName = reader.GetString(2);
users.Add(user);
}
}
}
// After we've read all of the rows the MySqlHelp automatically closes the connection
// Finally, we return our list of users
return users;
}
// Insert a user into the database
public static int InsertUser(User user)
{
// Query for inserting a User
string query = "INSERT INTO Users (Username, Password, FirstName, LastName) "
+ "VALUES (@user, @pass, @first, @last)";
// Create the parameters
List<MySqlParameter> parms = new List<MySqlParameter>();
parms.Add(new MySqlParameter("user", user.Username));
parms.Add(new MySqlParameter("pass", user.Password));
parms.Add(new MySqlParameter("first", user.FirstName));
parms.Add(new MySqlParameter("last", user.LastName));
// Pass the List Connection string, query, and an array of SqlParameters
// to the ExecuteNonQuery method of the MySqlHelper class. For Update,
// Insert, and Delete commands this should return the number of rows
// affected.
return MySqlHelper.ExecuteNonQuery(Connection, query, parms.ToArray());
}
}
Using this class is pretty simple. To retrieve all of the users you could do this.
List<User> users = (List<User>)UserDB.getAllTestUsers();
foreach (User item in users) {
Console.WriteLine (item.FirstName);
}
And to insert a new user into the database we'd do this.
User user = new User
{
Username = "TestUser",
FirstName = "TestName",
LastName = "LastName",
Password = "Password"
};
int result = UserDB.InsertUser(user);
Resources:
Using MySQL with .Net
Mono Website
MySQL Connector
Mono MySQL Page
MultiQuote
| 