I See Sharp

Here are the types I used. Make sure usr_Name (the username) and usr_Pass are maked as "Binary". Else, the username and passwords wouldn't be case-sensitive at all.

We don't need no transactions! Use MyISAM storage engine.

Right. Now that our tables are set up, we can open up our C# editor. Create a new console application. Add a reference to the MySQL .net Connector (which I'm sure you already downloaded and installed from this location). The MySQL connector for the .Net framework 1.0, after installation, can normally be found at [MySQLInstallPath]\MySQL Connector Net 1.0.4\bin\.NET 1.0

Here's the wrapper that I wrote for this example. It's pretty straightforward. There's a class called "DbWrapper" which does all the database work for us. The DbWrapper has methods to add a user, verify whether a username exists or not, and validate a username and password.

using System;
using MySql.Data.MySqlClient;

namespace CSConnector
{
    /// <summary>
    /// Wraps calls to the users database
    /// </summary>
    public class DbWrapper
    {
        private MySqlConnection sqlConn;
        private string connStr;
        private bool isConnected;

        /// <summary>
        /// Creates a new database wrapper object that wraps around
        /// the users table.
        /// </summary>
        /// <param name="svr">The name of the server</param>
        /// <param name="db">The database catalog to use</param>
        /// <param name="user">The user name</param>
        /// <param name="pass">The user password</param>
        public DbWrapper(string svr, string db, string user, string pass)
        {
            this.connStr = "Server="+svr+";Database="+db+";Uid="+user+";Pwd="+pass+";";
            
            try
            {
                sqlConn = new MySqlConnection(this.connStr);
            }
            catch(Exception excp)
            {
                Exception myExcp = new Exception("Error connecting you to " +
                    "the my sql server. Internal error message: " + excp.Message, excp);
                throw myExcp;
            }

            this.isConnected = false;
        }

        /// <summary>
        /// Creates a new database wrapper object that wraps around
        /// the users table.
        /// </summary>
        /// <param name="connStr">A connection string to provide to connect
        /// to the database</param>
        public DbWrapper(string connStr)
        {
            this.connStr = connStr;

            try
            {
                sqlConn = new MySqlConnection(this.connStr);
            }
            catch(Exception excp)
            {
                Exception myExcp = new Exception("Error connecting you to " +
                    "the my sql server. Error: " + excp.Message, excp);
                
                throw myExcp;
            }

            this.isConnected = false;
        }

        /// <summary>
        /// Opens the connection to the SQL database.
        /// </summary>
        public void Connect()
        {
            bool success = true;

            if (this.isConnected == false)
            {
                try
                {
                    this.sqlConn.Open();
                }
                catch(Exception excp)
                {
                    this.isConnected = false;
                    success = false;
                    Exception myException = new Exception("Error opening connection" +
                        " to the sql server. Error: " + excp.Message, excp);
                    
                    throw myException;
                }

                if (success)
                {
                    this.isConnected = true;
                }
            }
        }
        
        /// <summary>
        /// Closes the connection to the sql connection.
        /// </summary>
        public void Disconnect()
        {
            if (this.isConnected)
            {
                this.sqlConn.Close();
            }
        }

        /// <summary>
        /// Gets the current state (boolean) of the connection.
        /// True for open, false for closed.
        /// </summary>
        public bool IsConnected
        {
            get
            {
                return this.isConnected;
            }
        }

        /// <summary>
        /// Adds a user into the database
        /// </summary>
        /// <param name="username">The user login</param>
        /// <param name="password">The user password</param>
        public void AddUser(string username, string password)
        {
            string Query = "INSERT INTO users(usr_name, usr_pass) values" +
                "('"+username+"','"+password+"')";

            MySqlCommand addUser = new MySqlCommand(Query, this.sqlConn);

            try
            {
                addUser.ExecuteNonQuery();
            }
            catch(Exception excp)
            {
                Exception myExcp = new Exception("Could not add user. Error: " +
                    excp.Message, excp);
                throw(myExcp);
            }
        }

        /// <summary>
        /// Verifies whether a user with the supplied user
        /// credentials exists in the database or not. User
        /// credentials are case-sensitive.
        /// </summary>
        /// <param name="username">The user login</param>
        /// <param name="password">The user password</param>
        /// <returns>A boolean value. True if the user exists
        /// in the database, false if the user does not exist
        /// in the database.</returns>
        public bool VerifyUser(string username, string password)
        {
            int returnValue = 0;

            string Query = "SELECT COUNT(*) FROM users where (usr_Name=" +
                "'"+username+"' and usr_Pass='"+password+"') LIMIT 1";

            MySqlCommand verifyUser = new MySqlCommand(Query, this.sqlConn);

            try
            {
                verifyUser.ExecuteNonQuery();

                MySqlDataReader myReader = verifyUser.ExecuteReader();

                while(myReader.Read() != false)
                {
                    returnValue = myReader.GetInt32(0);
                }

                myReader.Close();
            }
            catch(Exception excp)
            {
                Exception myExcp = new Exception("Could not verify user. Error: " +
                    excp.Message, excp);
                throw(myExcp);
            }

            if (returnValue == 0)
            {
                return false;
            }
            else
            {
                return true;
            }
        }

        /// <summary>
        /// Checks whether a supplied user name exists or not
        /// </summary>
        /// <param name="username">The user name</param>
        /// <returns>True if the username is already in the table,
        /// false if the username is not in the table</returns>
        public bool UserExists(string username)
        {
            int returnValue = 0;

            string Query = "SELECT COUNT(*) FROM users where (usr_Name=" +
                "'"+username+"') LIMIT 1";

            MySqlCommand verifyUser = new MySqlCommand(Query, this.sqlConn);

            try
            {
                verifyUser.ExecuteNonQuery();

                MySqlDataReader myReader = verifyUser.ExecuteReader();

                while(myReader.Read() != false)
                {
                    returnValue = myReader.GetInt32(0);
                }

                myReader.Close();
            }
            catch(Exception excp)
            {
                Exception myExcp = new Exception("Could not verify user. Error: " +
                    excp.Message, excp);
                throw(myExcp);
            }

            if (returnValue == 0)
            {
                return false;
            }
            else
            {
                return true;
            }
        }


    }
}

SELECT COUNT(*) FROM users WHERE (usr_Name='Rowan' AND usr_Pass='ISeeSharp') LIMIT 1;


There's no black magic in there. I'm just inserting users and counting records. Here's how you can use the wrapper - I used only the Add user and UserExists method. You can try the rest out yourself :)

            DbWrapper myWrapper = new DbWrapper("localhost", "CS",
                "iseesharp", "seesharper");
            myWrapper.Connect();

            myWrapper.AddUser("Rowan", "ISeeSharp");
            if (myWrapper.UserExists("rowan"))
            {
                Console.WriteLine("Something's weird here");
            }
            else
                if (myWrapper.UserExists("Rowan"))
            {
                Console.WriteLine("I exist, therefore I think!");
            }

            myWrapper.Disconnect();

This short piece of code instantiates a new DbWrapper, and passes it the required parameters to connect to my database. It adds a new user "Rowan" with the password "ISeeSharp" into the SQL Database. In between the Connect() and Disconnect() methods, you'll be able to use any of the provided methods in the wrapper. Make sure you disconnect after using the wrapper!

Cheers,
Rowy

# posted by Rowy @ Wednesday, September 07, 2005 14 comments