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