MySQL Connection
Enviado por suero • 6 de Febrero de 2014 • 1.734 Palabras (7 Páginas) • 227 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;
...