Why do I get "System resources exceeded" C # Access?

1

I'm working with c # and access,

this is my code:

foreach (var item in alumnos)
                    {
                    OleDbDataReader reader = Connection.Read("SELECT * FROM Estudiante WHERE cod=" + item.cod);
                    if (reader.Read())
                        {
                        }
                    else
                        {
                        MSAConnection.execute("INSERT INTO Estudiante (cod,tipoEstudiante,nombre,Paterno,Paterno,nombreCompleto,grado,paralelo,cel,CelT,codColegio) " +
                        "values (\""
                        + Convert.ToInt32(item.cod) + "\",\""
                        + tipoEstudiante + "\",\""
                        + item.nombres.ToUpper() + "\",\""
                        + item.Paterno.ToUpper() + "\",\""
                        + item.Paterno.ToUpper() + "\",\""
                        + item.nombrecompleto.ToUpper() + "\",\""
                        + getGrado(item.grado) + "\",\""
                        + getParalelo(item.paralelo).ToUpper() + "\",\""
                        + Convert.ToInt32(getNumero(item.cel)) + "\",\""
                        + Convert.ToInt32(getNumeroTutor(item.CelT)) + "\",\""
                        + Convert.ToInt32(item.colegio) + "\")");
                        }
                    }

    
asked by Rodrigo Rodriguez 09.10.2017 в 16:44
source

2 answers

0

If you open a read () do not forget to close it.

foreach (var item in alumnos)
                    {
                    OleDbDataReader reader = Connection.Read("SELECT * FROM Estudiante WHERE cod=" + item.cod);
                    if (reader.Read())
                        {
                        }
                    else
                        {
                        MSAConnection.execute("INSERT INTO Estudiante (cod,tipoEstudiante,nombre,Paterno,Paterno,nombreCompleto,grado,paralelo,cel,CelT,codColegio) " +
                        "values (\""
                        + Convert.ToInt32(item.cod) + "\",\""
                        + tipoEstudiante + "\",\""
                        + item.nombres.ToUpper() + "\",\""
                        + item.Paterno.ToUpper() + "\",\""
                        + item.Paterno.ToUpper() + "\",\""
                        + item.nombrecompleto.ToUpper() + "\",\""
                        + getGrado(item.grado) + "\",\""
                        + getParalelo(item.paralelo).ToUpper() + "\",\""
                        + Convert.ToInt32(getNumero(item.cel)) + "\",\""
                        + Convert.ToInt32(getNumeroTutor(item.CelT)) + "\",\""
                        + Convert.ToInt32(item.colegio) + "\")");
                        }
                        if(reader != null) reader.Close();
                    }
    
answered by 09.10.2017 / 17:13
source
2

I do not know how to implement Connection.Read() but it is clear that if you do not allow parameters, you should stop using it.

You could use something like:

using (OleDbConnection conn = new OleDbConnection("connection string"))   
{   
    conn.Open();  

    string sql = @"INSERT INTO Estudiante (cod,tipoEstudiante,nombre,Paterno,Paterno,nombreCompleto,grado,paralelo,cel,CelT,codColegio)
                    VALUES (@cod, @TipoEstudiante, @nombre, ...)"
    OleDbCommand cmd = new OleDbCommand (sql, conn);

    foreach (var item in alumnos)
    {
        if (!ExisteEstudiente(item.cod))
        {
            cmd.Parameters.Clear();
            cmd.Parameters.AddWithValue("@cod", Convert.ToInt32(item.cod));
            cmd.Parameters.AddWithValue("@TipoEstudiante", tipoEstudiante);
            cmd.Parameters.AddWithValue("@nombre", item.nombres.ToUpper());

            //resto parametros

            cmd.ExecuteNonQuery();
        }
    }
}

You will see that the command is defined only once and then parameters are assigned during each iteration of foreach

In addition, a method is used to validate if it exists

public bool ExisteEstudiente(string cod)  
{  

    string sql = @"SELECT * FROM Estudiante WHERE cod = @cod";   

    using (OleDbConnection conn = new OleDbConnection("connection string"))   
    {   
        conn.Open();  

        OleDbCommand cmd = new OleDbCommand (sql, conn);   

        cmd.Parameters.AddWithValue("@cod", cod);   

        int count = Convert.ToInt32(cmd.ExecuteScalar());   

        return count == 0;   

    }   

} 

In this way the code is neat and maintainable, it is important to define the connection within the using

    
answered by 09.10.2017 в 17:16