.comment-link {margin-left:.6em;}
My Photo
Name:
Location: Unspecified, Mauritius

I too, am a bug within Māyā.

My Other Blog(s) and Site(s)
Friend sites and blogs
My Recent Posts
Tutorials
Best Articles
My C-Sharp Ideas
Archives
Blog Directories

Wednesday, September 07, 2005

 

MySQL with C#

A quick intro
Is MySQL really mine?

Hello guys. I'm back from uni, and in full health. I've got so many new things to tell you, so many new things to teach you... hehe! :)

MySql is one of the most popular (and free) database management software around. You can have a look and freely download MySQL at www.mysql.com - make sure to download the .Net connector and also the MySQL administrator if you can. It's pretty useful if you don't know much of MySQL.

Now, let's get started. After downloading MySQL, MySQL's Administrator and MySQL Connector, make sure you're not a silly dick with a condom stuck up in your ass. Already checked? Okay, we're set to go.

We'll open up MySQL Administrator. The MySQL Administrator provides a nice user interface for people like me and you (if you're reading this, then I guess you don't know much about SQL) who aren't quite fluent in SQL. Well, I wasn't a few months ago, but now I'm some kind of advanced noob. Anyways, open up MySQL Administrator. You'll see something like this:




I remember being stuck on this fucking screen for quite some time the first time I opened up MySQL. Well duh, of course, the server host is localhost, and the username is "root" (and not "sa"). The password's the password that you typed in during installation. Don't change the port unless you specified something else during install. Click on OK. You should now be into MySQL Administrator.

At the top left, you'll see a menu with pretty icons on it. There's mainly "Server Information" which gives you information about your SQL server, Service Control - which allows you to start / stop / restart the MySQL service on your machine, Startup Variables - some useful variables and paths for MySQL, User Administration - where you can create / manage / delete / update users, etc... I'm not going to explain in detail what each of the menu items do, they're pretty much straightforward I think :)

Let's get straight to the point. What interests us for the moment is the Catalogs menu item, found at the bottom of the top-left menu. Click on the catalog, and create a new catalog by right-clicking on some empty space on the list box right beneath the top-right menu. Choose "Create New Schema". By default, mysql already has a few schema's created, but never mind about them. Create a new one, called CS. CS, of course, doesn't stand for Counter-Strike (as many dick-heads might have thought), but rather for C-Sharp. This is going to be our test catalog.

Right, after creating that schema, we'll need to create a new table for it. Right click on the big space on the right of the menu's (where obviously the tables should be, but aren't) and click on Create Table. Well, heh, you can also click on the little "Create Table" button, but that's all up to you.

Create a table named users. Make sure the table is of type "MyISAM" and not "InnoDB". We don't need to use transactions here :) Here are screenshots of my users table:.




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;
}
}


}
}


This is not really a tutorial on SQL statements. But still, I'll give you a quick overview on what exactly I did in the statements, and how you can use this code.

First of all, it's up to you to make sure that the users don't include single inverted commas into the username or password fields. You must absolutely make sure the user can only enter strings containing only characters a..z, A..Z and numbers. If you don't, you might run the risk of having SQL injection attacks on your code, especially if you're making an ASP.net Web Application.


To insert a new user:
INSERT INTO users(usr_Name, usr_Pass) VALUES('Rowan', 'ISeeSharp');

To verify whether a user exists:
SELECT COUNT(*) FROM users WHERE (usr_Name='Rowan') LIMIT 1;

To verify user credentials:

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


Comments:
Hi Blogger, I thought I'd just leave this message on your blog. I hope you don't mind. I've been trying to find blogs where people are talking about sql programming and when I was looking, I found this one on this post. this post, thought I would say hi, before I go off to find some more sql programming
 
I don't like console programs. I tried your code in a ASPX page without a code behind. I had to copy the MySql.Data.dll file into my web applications bin directory and it also required the <%@ assembly name="MySql.Data, Version=1.0.7.30072, Culture=neutral, PublicKeyToken=c5687fc88969c44d" %> page directive although that may be because I didn't restart the web application.
 
Thanks for the example... I know its an old article, but this is exactly what Im doing now, and I find it very useful. Thanks alot :-D
 
This was precisely what I was looking for. Thank you for posting this. Clear and concise.
 
I had to go to Project->Add Reference->MySql.Data in order to make it work properly. Before it couldn't use the MySql namespace...
 
Great stuff. I can't figure out how to read data from the database, as opposed to just getting counts.
 
You're an angry little person who cusses alot, besides that...THANKS A TON FOR SOME REAL COOL MYSQL INFO!
 
"SELECT COUNT(*) FROM users where (usr_Name=" +
"'"+username+"') LIMIT 1";

May be unsecure from mysql injection.
Recommend to use parameter instead.
 
i think this wrapper stinks.
 
Who knows where to download XRumer 5.0 Palladium?
Help, please. All recommend this program to effectively advertise on the Internet, this is the best program!
 
Excellent :)
Thanks
 
I'm homosexual and what is this?
 
Hi, if it wasn't for the swearing and bizarre reference to a condom, this is a useful page.
 
I know this post is quite old. But yet I stumbled across it searching for MySQL and C#.
I just want to add that people should be aware of connection pooling. Otherwise there is a chance of clients not closing the connection properly, even if they call connection.Close(). The physical connection will remain.
 
Despite the fact that this is 6 years later, I'm still going to leave this: you need to parameterize your shit. People will read this and think that a SQL parameter that simply connects a string variable with a query will be fine to use. This is not the case, and is very insecure. Just a thought: it may be a good idea to update this tutorial of yours with that aspect in mind.
 
First C# tutorial I actually understands ever! thank you very much.
 
Post a Comment

Links to this post:

Create a Link



<< Home