How to make an Insert in two distinct tables in C#

Question

I need to enter values in an X table, then recover the id of it, so that I can send in the second Y table the id of X and other values, but I do not know how to do this. Follow the code below what I’m trying to do without success.

class DCadastro
{

private string _nome;
private string _displina;

    public string Nome { get => _nome; set => _nome = value; }
    public string Displina { get => _displina; set => _displina = value; }

    public string Inserir(DCadastro dCadastro)
    {
        string resp = "";
        SqlCommand cmd = new SqlCommand();
        SqlConnection con = new SqlConnection();

        try
        {
            con.ConnectionString = Conexao.Cn;
            con.Open();
            cmd.Connection = con;

            cmd.CommandText = "INSERT INTO PROFESSOR(nome) VALUES (@nome);  SELECT SCOPE_IDENTIY(); INSERT INTO DISCIPLINA(disciplina, idProfessor) VALUES (@disciplina, @idProfessor);";
            cmd.Parameters.AddWithValue("@nome", Nome);
            Int32 idRetorno = Convert.ToInt32(cmd.ExecuteScalar());
            cmd.Parameters.AddWithValue("@disciplina", Displina);
            cmd.Parameters.AddWithValue("@idProfessor", idRetorno);
            resp = cmd.ExecuteNonQuery() == 1 ? "OK" : "Registro não foi inserido";

        }
        catch(Exception erro)
        {
            throw erro;
        }
        finally
        {
            if (con.State == System.Data.ConnectionState.Open) con.Close();
        }
        return resp;
    }

Answer 1

The problem is that there are three consultations:

    INSERT INTO PROFESSOR(nome) VALUES (@nome);  
    SELECT SCOPE_IDENTIY(); 
    INSERT INTO DISCIPLINA(disciplina, idProfessor) VALUES (@disciplina, @idProfessor);

Two return NULL:

    INSERT INTO PROFESSOR(nome) VALUES (@nome);  
    INSERT INTO DISCIPLINA(disciplina, idProfessor) VALUES (@disciplina, @idProfessor);

And only this returns a scalar value:

    SELECT SCOPE_IDENTIY(); 

An alternative to solving your problem is to divide these operations within a transaction scope. Starts by making the first two queries, because the second query returns a value for Executescalar() and concludes with the last query.

    public string Nome { get => _nome; set => _nome = value; }
    public string Displina { get => _displina; set => _displina = value; }

    public string Inserir(DCadastro dCadastro)
    {
        string resp = "";
        SqlCommand cmd = new SqlCommand();
        SqlConnection con = new SqlConnection();

        try
        {
            con.ConnectionString = Conexao.Cn;
            con.Open();
            cmd.Connection = con;

            // Inicia o escopo de transação
            SqlTransaction  transaction = connection.BeginTransaction("SampleTransaction");

             // Informa o SqlCommand em qual escopo que se passará a transação de dados
             cmd.Transaction = transaction;

            // Passa para o SqlCommand a primeira e segunda consultas.           
            cmd.CommandText = "INSERT INTO PROFESSOR(nome) VALUES (@nome); SELECT CAST(SCOPE_IDENTITY() AS int);";
            cmd.Parameters.AddWithValue("@nome", Nome);
            Int32 idRetorno = Convert.ToInt32(cmd.ExecuteScalar());


            //Terceira consulta.
            cmd.CommandText = "INSERT INTO DISCIPLINA(disciplina, idProfessor) VALUES (@disciplina, @idProfessor);";
            cmd.Parameters.AddWithValue("@disciplina", Displina);
            cmd.Parameters.AddWithValue("@idProfessor", idRetorno);
            resp = cmd.ExecuteNonQuery() == 1 ? "OK" : "Registro não foi inserido";

            // Encerra o escopo de transação passando as operações para Banco de Dados.
            transaction.Commit();

        }
        catch(Exception erro)
        {
            throw erro;
        }
        finally
        {
            if (con.State == System.Data.ConnectionState.Open) con.Close();
        }
        return resp;
    }