Page 1 of 1

Using MySqlHelper Using the MySqlHelper class with .Net or Mono

#1 Nakor  Icon User is offline

  • Professional Lurker
  • member icon

Reputation: 446
  • View blog
  • Posts: 1,501
  • Joined: 28-April 09

Posted 10 November 2010 - 06:11 AM

One of the roadblocks for me when I first started using Mono on linux was that I had no idea how to get my applications connected to a database. After a little researching on the Mono Website I found that MySQL has already provided a library to allow .Net (and Mono) applications to interface with the MySQL database using the MySQL Connector library. This useful library mimics the methods of the .Net System.Data library fairly well and if you are familiar with connecting to SQL Server using a SqlClient then using the MySQL Connector should be pretty straightforward. Today I wanted to go over using the MySqlHelper class to connect to a MySQL database.

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

Is This A Good Question/Topic? 0
  • +

Replies To: Using MySqlHelper

#2 Sergio Tapia  Icon User is offline

  • D.I.C Lover
  • member icon

Reputation: 1253
  • View blog
  • Posts: 4,168
  • Joined: 27-January 10

Posted 10 November 2010 - 06:25 PM

Definitely a useful tutorial. No cruft and to the point. Well done!
Was This Post Helpful? 0
  • +
  • -

#3 shadursky  Icon User is offline

  • New D.I.C Head

Reputation: 0
  • View blog
  • Posts: 1
  • Joined: 16-November 10

Posted 16 November 2010 - 02:15 PM

Nakor,
in case you have SQL query typo, how can you handle SQL server response?

This post has been edited by shadursky: 16 November 2010 - 02:16 PM

Was This Post Helpful? 0
  • +
  • -

#4 Nakor  Icon User is offline

  • Professional Lurker
  • member icon

Reputation: 446
  • View blog
  • Posts: 1,501
  • Joined: 28-April 09

Posted 21 November 2010 - 11:08 PM

enclose your request in a try/catch like so

try {
    List<User> users = (List<User>)UserDB.getAllTestUsers();
   
    foreach (User item in users) {
	Console.WriteLine (item.FirstName);
    }
} catch (Exception e) {
    Console.WriteLine(e.Message);
}



Then if you accidentally did something like
string query = "SELEC Username, FirstName, LastName FROM Users";


you would receive a message saying

Quote

You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'SELEC Username, FirstName, LastName FROM Users' at line 1


This could as easily be printed to a textbox or label rather than to the Console.

This post has been edited by Nakor: 21 November 2010 - 11:09 PM

Was This Post Helpful? 0
  • +
  • -

#5 skarlet  Icon User is offline

  • New D.I.C Head

Reputation: 0
  • View blog
  • Posts: 1
  • Joined: 05-April 11

Posted 05 April 2011 - 07:43 PM

it works when i use the ExecuteDataset method. but when i try to use the ExecuteScalar method like this:

String query = "SELECT name FROM employee WHERE id=1";
String a = MySqlHelper.ExecuteScalar(connection,query).ToString();
return a;



it doesn't work until i write codes to open the database connection, so the codes will look like this:

connection.Open();
String query = "SELECT name FROM employee WHERE id=1";
String a = MySqlHelper.ExecuteScalar(connection,query).ToString();
return a;



could you explain to me why do i have to open the connection?
thank you..
Was This Post Helpful? 0
  • +
  • -

#6 Nakor  Icon User is offline

  • Professional Lurker
  • member icon

Reputation: 446
  • View blog
  • Posts: 1,501
  • Joined: 28-April 09

Posted 06 April 2011 - 04:42 AM

From what I can tell when you pass the ExecuteScalar method a MySqlConnection then you need to open the connection before you can call it. However if you pass it the connection string instead of a MySqlConnection object then you can execute it without opening a connection seperately.

For example, this works fine for me without needing to open a connection before calling ExecuteScalar

    private static string ConString { get {     return "Server=server;Database=dbname;Uid=user;Pwd=password;"; } }


    public static string GetCD()
    {
        string query = "select interpret from cds where titel = 'Glee'";
        return MySqlHelper.ExecuteScalar(ConString, query).ToString();
    }


Was This Post Helpful? 0
  • +
  • -

Page 1 of 1