SQL-C# ~ Add Student if not in class

Asked

Viewed 122 times

1

Good morning, I have the following code below:

public void AdicionarAlunoTurma(Adicionar_Alunos aluno)
    {
        List<string> parametros = new List<string>();
        StringBuilder query = new StringBuilder();
        parametros.Add(aluno.id.ToString());
        parametros.Add(aluno.nome);
        parametros.Add(aluno.turmaID.ToString());
        query.Append("insert into persons_turma (personsID, persons_turma, turmaID) values (@param1,@param2,@param3) ");
        SqlDynamic.Execute(db, query.ToString(), parametros.ToArray());

and the table fields are these.

inserir a descrição da imagem aqui

I defined people as unique because they were having problems with duplication. How could I be adding the same student to another class? I tried to develop an IF before the query, causing SQL to ignore that it was unique and add anyway. but I couldn’t finish this logic.

Thanks in advance for the help

c# sql oop
  • 3

    Adding the column turmaID in the key composition?

  • i would select previously, to check if personId already exists when it comes to the class, if the result is greater than 1 then the user has already been registered in that class!

  • The personId cannot be unique, the table persons_turma is to make the relation of students with the class, the table persons_turma must have only the fields Personid (not single, foreign key referencing a Personid of the table Person) , Turmaid (not single, foreign key referencing Class table Turmaid) is a primary key Personturmaid, so you ensure that you make the list of existing people with existing classes and can reference anyone with any class

  • There is probably a problem of poor table definition and relationships as said by @Joãopauloamorim in the comment above. Post the create command of the table persons_turma so we can analyze better, I would even answer something, plus it seems that the definition of the keys and relationships of the table is in trouble.

  • I didn’t save the codes used to create the table (I created it about 5 days ago)

  • I removed that command from him to be ONLY. I would only need him to do a scan to see if the student is already in that class

  • @Matheusmuniz make a select in the table before passing personId and Turmaid, if return records you do not save.

  • @Joãopauloamorim I made the change and made the following code:

Show 3 more comments

2 answers

0

In the clause WHERE you define in your assembly the ID of the class you want to list.

Example: 

SELECT * 
  FROM persons_turma 
 WHERE personsID = pessoaId 
   AND turmaID = turmaId

0

Make a SELECT to check if there is the student who wants to include the class already exists:

public int ExisteAluno(int idAluno) 
{
    string strQuery = "SELECT id FROM tabela WHERE personsID = @p_id";
    return db.Database.SqlQuery<int>(strQuery, new SqlParameter("@p_id", idAluno)).FirstOrDefault();
}

if(ExisteAluno(idAluno) == 0)
{
    // Insere o aluno na turma aqui!
}

ATTENTION: Plural of person in English is people!

Browser other questions tagged c# sql oop

You are not signed in. Login or sign up in order to post.