| |
Installing the Database
- Download file onto the desktop. Since it is a zip file you will have to unzip it (possibly with winzip; instructions follow).
- Double click on the file. Click on I Agree.
- Click on Extract which is a button on the menu bar. It will bring up a new box called Extract.
- Click on New Folder…
- Type in DB and hit enter. It will return you to the Extract box.
- Click Extract(located near Cancel) and close Winzip.
- You will see a folder called DB on the desktop. (If you don’t then do a search for it).
- Open DB. DB contains a lot of files one of them being SETUP.EXE
- Double click on SETUP.EXE
- InstallShield will run and finally a Welcome message box will be displayed on a green background.
- Click Next and it will take you to the Information box.
- Click Next again. Now you should be in the Choose Destination Location.
- The Destination Folder shown on the bottom of the box should read C:\mysql; If it doesn’t make sure it does.
- Click next and it will take you to the Setup Type box.
- Choose Custom and click next. It will take you to the Select Components box.
- Click Next in the Select Components box.
- You should see a progress bar showing the install going to 100%.
- When that is done it will bring up a box called Setup Complete.
- Click Finish in Setup Complete box.
- The program will close down sending you back to the desktop.
- At this point you can safely delete the folder DB which should be on the desktop or to the location that you created it.
INSTALLATION COMPLETE!!!
- To check if the installation worked do the following.
- Open My Computer which should be on the desktop.
- Open Local Disk (C:)
- There should be a folder under C: called mysql.
- Open folder mysql.
- You should see 9 folders and 6 files as soon as you open it.
- One of the folders is called bin
- Open bin
- You will see a lot of executable files under bin.
- One of them is called winmysqladmin.exe
- Double click on winmysqladmin.exe.
- You will see the above first. THEN YOU WILL SEE A SCREEN POP UP THAT ASKS YOU FOR YOUR USERNAME AND PASSWORD.
Username = group3
Password = group3
- Once that is done the above screen will minimize into the Status bar on the bottom right of your screen You will know it because it is a Traffic light.
- Right click on it.
- Move the mouse over WinNT. This will bring up a new submenu.
- Click on Start the Server Stand Alone. This will pop up a message
- Click on Yes.
- You will notice that the Traffic light has gone from Red To Green.
- Everything is working as it should be now.
- Right click on the Traffic Icon again and choose Show Me. That will bring up the initial screen that you saw. (It has the MySQL logo on it).
- You will see a lot of tabs on there. One of them is called Database. You can check what databases are currently registered with MySQL by clicking on there. More on that later.
- For now Right Click where it tells you to in Blue letters at the top and click Hide Me. The Dialog box disapperars.
-----------------------------------------------------------------------------------------------------------------------
Using a mysql Database with C#
- Your installation of .Net SDK may need MDAC (Microsoft Data Access Components) 2.7, which can be downloaded from
- You must download ‘ODBC .Net Provider’ from http://msdn.microsoft.com/downloads/. You can find it under the section ‘.Net Framework>ODBC .Net Data Provider’, in the left HTML frame. Run the downloaded file named odbc_net.msi. ODBC .Net Provider is a set of .Net classes, which provide methods (functions) to connect and query an ODBC compliant database.
- Now download MyODBC Unzip and run Setup.exe
-
An example
As an example database, we create a database called ‘mp3collection’, which stores information—title, artist, album, and filename—about MP3 files. A table within ‘mp3collection’ named ‘mp3files’ will be used to store all this information in its corresponding columns. We also have a column named ‘sno’ (serial number) in ‘mp3files’. We have deliberately used this column to explain the concept of ‘auto_ increment’ facility in MySQL.
- Issue the following at the Command Prompt of Windows:
c:\mysql\bin\mysql -u root –p
|
- When prompted, enter the password corresponding to the ‘root’—that you had set before. Now enter the following command at the mysql prompt:
create database mp3collection;
use mp3collection;
create table mp3files (sno smallint unsigned auto_increment not
null,title char(50),artist char(20),album char(50),filename char(50), primary key (sno));
grant all on mp3col lection.* to csharp@127.0.0.1 identified by ‘csharp’;
|
- The last statement creates a new MySQL user named ‘csharp’ and assigns him the password ‘csharp’. It also gives this user ‘all’ access privileges on the database ‘mp3collection’ when he connects from the same machine—the one that runs MySQL.
- Set up a DSN To connect to the MySQL database through C#, we need to setup what is called a DSN (Data Source Name) on the Windows machine. Navigate to Control Panel>Administrative Tools>Data Sources (ODBC). Click on the tab User DSN and then click on Add. Select MySQL from the list of presented drivers and click on Finish. The MySQL Driver configuration window will pop up. Fill in the following:
Windows DSN name: mysql_csharp
MySQL host: 127.0.01
MySQL database name: mp3collection
user : csharp
password : pcquest
|
Leave the remaining text fields blank.
-
Understanding the code
As said before we installed ODBC .Net provider for methods, which allows connecting and querying ODBC databases. When installed, it provides a Namespace (collection of classes) called Microsoft.Data. Odbc. The classes within this Namespace provide the required methods. Hence with the line:
using Microsoft.Data.Odbc
- We declare that we will be using classes in this Namespace. So where is the actual code for these classes located? It’s all bundled in a dll file called Microsoft.odbc.dll. We know this but the C# compiler doesn’t. Hence when we compile the programs we specified this using the ‘/r’ option. Such DLL files are called assemblies in C# lingo. The following is what we should do in the main method:
- Specify the Data Source Name, which is required to connect to the ODBC database.
- Connect to the database
- Construct an SQL Query.
- Submit the query to the database
- Get the result of the SELECT Query. We iterate through this result and display it.
- Close the connection to the database
Now we explain how we perform each of the above steps in C#.
- For the first step, we need to create an object of the class OdbcConnection by giving the DSN name as a parameter to its constructor. The syntax is:
OdbcConnection_object = new OdbcConnection(“DSN=<DSN-name>”);
|
- We connect to the database server as:
where connection is the OdbcConnection object.
- We construct an SQL query by creating an object of OdbcCommand class. We give the SQL query itself and the OdbcConnection object as a parameter to its constructor. The syntax is:
OdbcCommand_object = new OdbcCommand(<SQL Query>,<OdbcConnection_object>);
|
- In case of SQL queries that don’t return any data like INSERT, DELETE, UPDATE etc. we call ExecuteNonQuery( ) method. In case of SQL queries which return data like the SELECT query, we call ExecuteReader( ) method. Both these methods belong to the OdbcCommand class. The latter method returns an OdbcDataReader object, which contains the returned data rows.
- When we call the method Read( ) of the OdbcDataReader class, it reads a data row and returns true. It will return false when it has read all the data rows. Hence we use a while loop as follows:
while(reader.Read( ))
{
code to display columns’ data in the data row
}
|
Here ‘reader’ is the OdbcDataReader object. Within this loop we can use the GetString( ) method of the OdbcDataReader class to get data in a particular column in the returned data rows. This method accepts the column number as a parameter and returns the data in that column. The column numbers start from zero (0). For example if we provide ‘2’ as parameter, GetString( ) will return the artist name. However, since we know the name of the columns, it would make more sense to retrieve the data using the column name. Another method of the OdbcData Reader class named GetOrdinal( ), returns the column number when given the column name as a parameter. Hence we use this method as a parameter to the GetString( ) method as you can see in code of display.cs. The syntax of GetString( ) and GetOrdinal( ) methods are as follows:
GetString(<column number>); (note that the column number starts from zero)
GetOrdinal(“<column-name>);
|
- Finally we close the connection using connection.Close( ).You can also connect to a MySQL database server running on a remote Linux machine or a remote Windows machine. You only need to substitute the IP address 127.0.0.1 with the IP address of the machine on which you would be running the C# programs in the GRANT statement. Secondly you must specify the IP address of the remote machine for the ‘MySQL host’ in place of 127.0.0.1 while setting up the DSN.
You can use the following code to check if you can open a connection to mysql through OLE DB
using System;
using System.Drawing;
using System.Collections;
using System.ComponentModel;
using System.Windows.Forms;
using System.Data;
using System.Data.OleDb;
namespace MySQLTest
{
public class frmMain : System.Windows.Forms.Form
{
private System.Windows.Forms.Button btnExit;
private System.Windows.Forms.Button btnOpenOLEDB;
private System.ComponentModel.Container components = null;
public frmMain()
{
InitializeComponent();
}
protected override void Dispose( bool disposing )
{
if( disposing )
{
if (components != null)
{
components.Dispose();
}
}
base.Dispose( disposing );
}
#region Windows Form Designer generated code
private void InitializeComponent()
{
this.btnOpenOLEDB = new System.Windows.Forms.Button();
this.btnExit = new System.Windows.Forms.Button();
this.SuspendLayout();
this.btnOpenOLEDB.Location = new System.Drawing.Point(24, 32);
this.btnOpenOLEDB.Name = "btnOpenOLEDB";
this.btnOpenOLEDB.Size = new System.Drawing.Size(296, 40);
this.btnOpenOLEDB.TabIndex = 0;
this.btnOpenOLEDB.Text = "Open connection to MySQL through OLE DB";
this.btnOpenOLEDB.Click += new System.EventHandler(this.btnOpenOLEDB_Click);
this.btnExit.Location = new System.Drawing.Point(320, 240);
this.btnExit.Name = "btnExit";
this.btnExit.Size = new System.Drawing.Size(80, 32);
this.btnExit.TabIndex = 1;
this.btnExit.Text = "Exit";
this.btnExit.Click += new System.EventHandler(this.btnExit_Click);
this.AutoScaleBaseSize = new System.Drawing.Size(5, 13);
this.ClientSize = new System.Drawing.Size(416, 283);
this.Controls.AddRange(new System.Windows.Forms.Control[] {
this.btnExit,
this.btnOpenOLEDB});
this.Name = "frmMain";
this.Text = "MySQLTest";
this.ResumeLayout(false);
}
#endregion
[STAThread]
static void Main()
{
Application.Run(new frmMain());
}
private void btnExit_Click(object sender, System.EventArgs e)
{
this.Close();
}
private void btnOpenOLEDB_Click(object sender, System.EventArgs e)
{
System.Data.OleDb.OleDbConnection con;
con=new System.Data.OleDb.OleDbConnection("");
con.ConnectionString="Provider=MySQLProv;Data Source=mysql;";
try
{
con.Open();
if (con.State==ConnectionState.Open) MessageBox.Show("Connection to MySQL opened through OLE DB Provider");
con.Close();
}
catch(Exception ex)
{
MessageBox.Show(ex.Message);
}
}
}
}
|
|
|