C# Classe pour utiliser une DB

Exemple à adapter en fonction de la DB (ci-dessous MySql) et à indenter (car pas beau comme cela ^^ )

Et surtout à configurer comme l’on souhaite 🙂

using MySql.Data.MySqlClient;
using System;
using System.Web.UI;
using System.Data;

/// <summary>
/// Description résumée de DbConnect
/// </summary>
public class DbConnect
{
 
 private MySqlConnection connection;
 private string server;
 private string database;
 private string uid;
 private string password;
 Page Page;

//Constructor
 public DbConnect()
 {
 Initialize();
 }

//Initialize values
 private void Initialize()
 {
 server = "MON SERVEUR DE DB";
 database = "DB";
 uid = "USERDB";
 password = "PASSWORDDB";
 string connectionString;
 connectionString = "SERVER=" + server + ";" + "DATABASE=" +
 database + ";" + "UID=" + uid + ";" + "PASSWORD=" + password + ";";

connection = new MySqlConnection(connectionString);
 }

//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(Page,ex.Message);
 return false;
 }
 }

//Insert statement
 public void Insert(string table, string columTable, string valueTable)
 {
 string query = "INSERT INTO "+table+" (" + columTable + ") VALUES("+ valueTable+")";

//open connection
 if (this.OpenConnection() == true)
 {
 //create command and assign the query and connection from the constructor
 MySqlCommand cmd = new MySqlCommand(query, connection);

//Execute command
 cmd.ExecuteNonQuery();

//close connection
 this.CloseConnection();
 }
 }

//Update statement
 public void Update(string table, string columTable, string valueTable ,string WHERE)
 {
 string query = "UPDATE " + table + " SET " + columTable + "='" + valueTable + "' "+ WHERE + "";

//Open connection
 if (this.OpenConnection() == true)
 {
 //create mysql command
 MySqlCommand cmd = new MySqlCommand();
 //Assign the query using CommandText
 cmd.CommandText = query;
 //Assign the connection using Connection
 cmd.Connection = connection;

//Execute query
 cmd.ExecuteNonQuery();

//close connection
 this.CloseConnection();
 }
 }

//Delete statement
 public void Delete()
 {
 string query = "DELETE FROM tableinfo WHERE name='John Smith'";

if (this.OpenConnection() == true)
 {
 MySqlCommand cmd = new MySqlCommand(query, connection);
 cmd.ExecuteNonQuery();
 this.CloseConnection();
 }
 }

//Select statement 
 public DataTable Select (string colum ,string table )
 {
 string query = "SELECT "+ colum+" FROM "+ table+"";
 DataTable select = new DataTable();
 //Open connection
 if (this.OpenConnection() == true)
 {
 //Create Command
 MySqlCommand cmd = new MySqlCommand(query, connection);
 //Create a data reader and Execute the command
 MySqlDataReader dataReader = cmd.ExecuteReader();
 select.Load(dataReader);
 //close Data Reader
 dataReader.Close();
 
 //close Connection
 this.CloseConnection();

//return list to be displayed
 return select;
 }
 else
 {
 return select;
 }
 }

//Count statement
 public int Count()
 {
 string query = "SELECT Count(*) FROM tableinfo";
 int Count = -1;

//Open Connection
 if (this.OpenConnection() == true)
 {
 //Create Mysql Command
 MySqlCommand cmd = new MySqlCommand(query, connection);

//ExecuteScalar will return one value
 Count = int.Parse(cmd.ExecuteScalar() + "");

//close Connection
 this.CloseConnection();

return Count;
 }
 else
 {
 return Count;
 }
 }


}