How is the usign (MySqlConnection) C # used?

3

Hi, I want to know the correct way to implement the using (MySqlConnection cn = new MySqlConnection()
It is assumed that when creating a connection within this using when I finish executing what is inside the block I will release the resources used in my case to disconnect from my server, my questions are Do you go inside a class?
How do I get the information from my query? Thanks

    
asked by WilsonGtZ 28.11.2016 в 21:06
source

3 answers

2

A use is intended to release the resources used in your declaration.

using (MySqlConnection cn = new MySqlConnection())
    //abrir conexion
    cn.Open();
    //lógica de programación  encargada de realizar la consulta.
}

You can implement them in the following way:

Consider that you have a relational table called Prueba_Usuario .

Consider that you have the following Entity (Class where you will store the data)

public class Usuario
{
    public Usuario()
    {    
    }
    public String Nombre { get; set; }

    public String ApellidoPaterno { get; set; }

    public String ApellidoMaterno { get; set; }
}

You can implement your Using in the following way, using a pattern called Data Accesss Object (DAO) .

public class DaoUsuario
{
  //Almacenamos temporalmente la cadena de conexión
  private string CadenaConexion;

  public  DaoUsuario(string cadenaConexion)
  {
    this.CadenaConexion = cadenaConexion;
  }
  public IEnumerable<Usuario> ConsultarUsuariosPorNombre(string nombre)
  {
    List<Usuario> ListaUsuarios = new List<Usuario>(); //Creamos una lista para agregar acada usuario que leemos de base de datos
    using (MySqlConnection conexion = new MySqlConnection(CadenaConexion)) //configuramos la conexión
    {
      try
      {
        //iniciamos la conexion a la base de datos
        conexion.Open();
        MySqlCommand comando = conexion.CreateCommand();
        //configuramos el comando
        comando.CommandType = CommandType.Text; //Indicamos que el comando será una setencia de texto.
        //Considerando que tenemos unta tabla llamada Prueba_Usuario
        comando.CommandText = string.Format(" SELECT nombre, apellidoPaterno, apellidoMaterno FROM Pueba_Usuario where nombre [email protected]");

        comando.Parameters.Add(new MySqlParameter("@parametroNombre",nombre)); //le indicamos que remplace @parametroNombre con el valor de nombre

        MySqlDataReader resultado = comando.ExecuteReader(); //Ejecutamos el comando para decirle que nos triga la información de nombre,apellidoPaterno y apellidoMaterno
        //Leeremos cada renglos de la tabla mientras existan datos en ella
        while (resultado.Read())
        {
          Usuario actual = new Usuario();

          actual.Nombre = (string)resultado["nombre"]; //le indicamos el tipo de datos que debe ser con (string)
          actual.ApellidoPaterno = (string)resultado["apellidoPaterno"];
          actual.ApellidoMaterno = (string)resultado["apellidoMaterno"];
          //agregamos el usuario a a lista de usuarios.
          ListaUsuarios.Add(actual);
        }
      }
      catch (Exception)
      {

      throw;
      }
    }
    return ListaUsuarios;
  }
  public IEnumerable<Usuario> ConsultarUsuarios()
  {
    List<Usuario> ListaUsuarios = new List<Usuario>(); //Creamos una lista para agregar acada usuario que leemos de base de datos
    using (MySqlConnection conexion = new MySqlConnection(CadenaConexion)) //configuramos la conexión
    {
      //iniciamos la conexion a la base de datos
      conexion.Open();
      MySqlCommand comando = conexion.CreateCommand();
      //configuramos el comando
      comando.CommandType = CommandType.Text; //Indicamos que el comando será una setencia de texto.
      //Considerando que tenemos unta tabla llamada Prueba_Usuario
      comando.CommandText = string.Format(" SELECT nombre, apellidoPaterno, apellidoMaterno FROM Pueba_Usuario");
      MySqlDataReader resultado = comando.ExecuteReader(); //Ejecutamos el comando para decirle que nos triga la información de nombre,apellidoPaterno y apellidoMaterno
      //Leeremos cada renglos de la tabla mientras existan datos en ella
      while (resultado.Read())
      {
        Usuario actual = new Usuario();

        actual.Nombre = (string)resultado["nombre"] ; //le indicamos el tipo de datos que debe ser con (string)
        actual.ApellidoPaterno = (string)resultado["apellidoPaterno"];
        actual.ApellidoMaterno = (string)resultado["apellidoMaterno"];
        //agregamos el usuario a a lista de usuarios.
        ListaUsuarios.Add(actual);
      }
    }
    return ListaUsuarios;
  }
}

... and you can instantiate it in the following way:

DaoUsuario dao = new DaoUsuario("CadenaConexion");
IEnumerable<Usuario> usuarios = dao.ConsultarUsuarios()   ;
IEnumerable<Usuario> usuarioPorNombre = dao.ConsultarUsuariosPorNombre("nombrePrueba");
    
answered by 28.11.2016 / 22:30
source
5

This is an example how to use it:

public static void Create(Cliente entity)
{
    using (var cn = new MySqlConnection(ConfigurationManager.ConnectionStrings["default"].ToString()) )
    {
        cn.Open();
        using (var cmd = cn.CreateCommand())
        {
            cmd.CommandText =
                "INSERT INTO Clientes(RazonSocial, DocumentoIdentidad, NumeroDocumento, Direccion, Telefono)" +
                " VALUES(?RazonSocial, ?DocumentoIdentidad, ?NumeroDocumento, ?Direccion, ?Telefono)";

            cmd.Parameters.AddWithValue("?RazonSocial", entity.RazonSocial);
            cmd.Parameters.AddWithValue("?DocumentoIdentidad", entity.DocumentoIdentidad);
            cmd.Parameters.AddWithValue("?NumeroDocumento", entity.NumeroDocumento);
            cmd.Parameters.AddWithValue("?Direccion", entity.Direccion);
            cmd.Parameters.AddWithValue("?Telefono", entity.Telefono);
            cmd.ExecuteNonQuery();
        }       
    }
 }

Answering your question:

By enclosing the declaration of the object Connection between a block Using ... End Using , we will be sure to close the connection and destroy the resources used by the object, at the end of that block (when it is executed End Using ). With this we do not have to be aware of calling the method Close (to close the connection), nor the method Dispose (to destroy the object), therefore, it is not necessary to have an external procedure for such tasks.

If you go inside a class? If you can put it inside a class to avoid mixing the code. It is advisable to work in layers: Presence, Logic, Persistence, Entities. Each layer has a responsibility.

  • Presentation: Responsible for interacting with the user
  • Logic: Responsible for calling methods and business logic
  • Persistence: Persist against the database
  • Entities: Mappings to data

Each layer has a responsibility and can not be contaminated with responsibilities from other layers.

    
answered by 28.11.2016 в 21:27
2

Responding to your question, all blocks of code must be executed within a class.

In this case, when using using (MySqlConnection cn = new MySqlConnection() you will not have to close connections manually since the class MySqlConnection inherits from IDisposable, which at the end of the using block the connection closes automatically.

In other words, using serves to specify that a block of code will be executed and a series of resources will be associated to the specific context, just as it could be connections to the database. It is one of the best practices to use the using context because if the type used in the context inherits from the IDisposable interface, the object is freed from memory upon completion of the code block of using without having to dispose of the resources manually.

This is an example code so you can handle the results:

using (var conn = new MySqlConnection(connectionString))
{
    using (var cmd = new MySqlCommand())
    {
        cmd.Connection = conn;
        cmd.CommandText = "SELECT Columna1, Columna2 FROM [Table]";
        conn.Open();
        using (var reader = cmd.ExecuteReader())
        {
            while (reader.Read())
            {
                try
                {
                    //Manejo de los resultados
                    //string variable1 = reaader["Columna1"].ToString();
                    //string variable2 = reaader["Columna2"].ToString();
                }
                catch (Exception ex) {
                    //Manejo de la excepción
                }
            }
        }
    }
}
    
answered by 28.11.2016 в 21:52