MySQL Connection
Enviado por suero • 6 de Febrero de 2014 • 1.734 Palabras (7 Páginas) • 247 Visitas
Creating the Class
It's always a better idea to create a new class for connecting to the database and to separate the actual code from the code that will access the database. This will help keep our code neat, easier to read and more efficient.
We will start by adding the MySql Connector library:
//Add MySql Library
using MySql.Data.MySqlClient;
Then declaring and initializing the variables that we will use:
• connection: will be used to open a connection to the database.
• server: indicates where our server is hosted, in our case, it's localhost.
• database: is the name of the database we will use, in our case it's the database we already created earlier which is connectcsharptomysql.
• uid: is our MySQL username.
• password: is our MySQL password.
• connectionString: contains the connection string to connect to the database, and will be assigned to the connection variable.
Our class will look as follows:
class DBConnect
{
private MySqlConnection connection;
private string server;
private string database;
private string uid;
private string password;
//Constructor
public DBConnect()
{
Initialize();
}
//Initialize values
private void Initialize()
{
server = "localhost";
database = "connectcsharptomysql";
uid = "username";
password = "password";
string connectionString;
connectionString = "SERVER=" + server + ";" + "DATABASE=" +
database + ";" + "UID=" + uid + ";" + "PASSWORD=" + password + ";";
connection = new MySqlConnection(connectionString);
}
//open connection to database
private bool OpenConnection()
{
}
//Close connection
private bool CloseConnection()
{
}
//Insert statement
public void Insert()
{
}
//Update statement
public void Update()
{
}
//Delete statement
public void Delete()
{
}
//Select statement
public List <string> [] Select()
{
}
//Count statement
public int Count()
{
}
//Backup
public void Backup()
{
}
//Restore
public void Restore()
{
}
}
Opening and Closing the Connection
We should always open a connection before querying our table(s), and close it right after we're done, to release the resources and indicate that this connection is no longer needed.
Opening and closing a connection to the database is very simple, however, it's always better to use exception handling before opening a connection or closing it, to catch the errors and deal with them.
//open connection to database
private bool OpenConnection()
{
try
{
connection.Open();
return true;
}
catch (MySqlException ex)
{
//When handling errors, you can your application's response based
//on the error number.
//The two most common error numbers when connecting are as follows:
//0: Cannot connect to server.
//1045: Invalid user name and/or password.
switch (ex.Number)
{
case 0:
MessageBox.Show("Cannot connect to server. Contact administrator");
break;
case 1045:
MessageBox.Show("Invalid username/password, please try again");
break;
}
return false;
}
}
//Close connection
private bool CloseConnection()
{
try
{
connection.Close();
return true;
}
catch (MySqlException ex)
{
MessageBox.Show(ex.Message);
return false;
}
}
Working with DML (Insert, Update, Select, Delete)
Usually, Insert, update and delete are used to write or change data in the database, while Select is used to read data.
For this reason, we have different types of methods to execute those queries.
The methods are
...