Hey there, I'm Rowan. I'm currently studying Software Engineering at UTM, and I'm also an IT Entrepreneur. This is my programming blog - you'll find loads of C# stuff in here, as well as MySQL and C tutorials with source. Comments are most welcome.
I too, am a bug within Māyā.
My Other Blog(s) and Site(s)
Friend sites and blogs
My Recent Posts
My C-Sharp Ideas
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.
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 :)
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!
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=18.104.22.168072, 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
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.
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!
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.Post a Comment
Links to this post: