Data entry in C#database

Asked

Viewed 1,192 times

2

My class Cliente:

using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Threading.Tasks;

namespace Objetos_de_Transferencia
{
    public class Cliente
    {
        public int id_cli { get; set; }
        public string nome { get; set; }
        public int telefone { get; set; }
        public int telemovel { get; set; }
        public string morada { get; set; }
        public string sexo { get; set; }
        public DateTime data_nasc { get; set; }
        public string email { get; set; }
    }
}

My class ClienteDAO:

using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Threading.Tasks;

using System.Data;
using System.Data.SqlClient;
using System.Data.Sql;
using Objetos_de_Transferencia;

namespace AcessoBancoDados
{
    public class ClienteDAO
    {

        public ConnectionBD connectionBD = new ConnectionBD();
        public SqlCommand cmd = null;
        public SqlDataReader dr = null;

        SqlConnection con = null;

        Cliente cliente = null;

        //// metodo para inserir um novo cliente
        public void gravar(Cliente cliente) {

            try
            {
                SqlConnection con = connectionBD.criarConexao();

                cmd = new SqlCommand("insert into Cliente2(nome, telefone, telemovel, morada, sexo, data_nasc, email)values(@nome, @telefone, @telemovel, @morada, @sexo, @data_nasc, @email)", con);

                cmd.Parameters.AddWithValue("@nome", cliente.nome);
                cmd.Parameters.AddWithValue("@telefone", cliente.telefone);
                cmd.Parameters.AddWithValue("@telemovel", cliente.telemovel);
                cmd.Parameters.AddWithValue("@morada", cliente.morada);
                cmd.Parameters.AddWithValue("@sexo", cliente.sexo);
                cmd.Parameters.AddWithValue("@data_nasc", cliente.data_nasc);
                cmd.Parameters.AddWithValue("@email", cliente.email);

                cmd.ExecuteNonQuery(); 

            }
            catch (Exception ex)
            {

                throw new Exception(ex.Message);
            }
            finally
            {

                con.Close();
            }


        }

        //// metodo para atualizar os dados
        public void atualizar(Cliente cliente) {

            try
            {
                SqlConnection con = connectionBD.criarConexao();

                cmd = new SqlCommand("update Cliente2 set nome = @nome, telefone = @telefone, telemovel = @telemovel, morada = @morada, sexo = @sexo, data_nasc = @data_nasc, email = @email where id_cli = @id_cli", con);

                cmd.Parameters.AddWithValue("@id_cli", cliente.id_cli);
                cmd.Parameters.AddWithValue("@nome", cliente.nome);
                cmd.Parameters.AddWithValue("@telefone", cliente.telefone);
                cmd.Parameters.AddWithValue("@telemovel", cliente.telemovel);
                cmd.Parameters.AddWithValue("@morada", cliente.morada);
                cmd.Parameters.AddWithValue("@sexo", cliente.sexo);
                cmd.Parameters.AddWithValue("@data_nasc", cliente.data_nasc);
                cmd.Parameters.AddWithValue("@email", cliente.email);

                cmd.ExecuteNonQuery();

            }
            catch (Exception ex)
            {

                throw new Exception(ex.Message);
            }
            finally
            {

                con.Close();
            }
        }

        //// metodo para excluir um cliente
        public void ecluir(int codigo) {

            try
            {
                SqlConnection con = connectionBD.criarConexao();

                cmd = new SqlCommand("delete from Cliente2 where id_cli = @id_cli", con);

                cmd.Parameters.AddWithValue("@id_cli", codigo);

                cmd.ExecuteNonQuery();

            }
            catch (Exception ex)
            {

                throw new Exception(ex.Message);
            }
            finally
            {

                con.Close();
            }
        }

        //// metodo para obter um cliente pelo id
        public Cliente pesquizarPorCodigo(int codigo) {



            try
            {
                SqlConnection con = connectionBD.criarConexao();

                cmd = new SqlCommand("select * from Cliente2 where id_cli = @id_cli", con);

                cmd.Parameters.AddWithValue("@id_cli", codigo);

                dr = cmd.ExecuteReader();

                /// vai ter que varrer tudo e procurar o que o usuario digitou
                while (dr.Read()) {

                    cliente = new Cliente();  ///// assim consigo regatar tudo que esta dentro dessa classe Cliente

                    cliente.id_cli      = Convert.ToInt32(dr["id_cli"]);
                    cliente.nome        = Convert.ToString(dr["nome"]);
                    cliente.telefone    = Convert.ToInt32(dr["telefone"]);
                    cliente.telemovel   = Convert.ToInt32(dr["telemovel"]);
                    cliente.morada      = Convert.ToString(dr["id_cli"]);
                    cliente.sexo        = Convert.ToString(dr["id_cli"]);
                    cliente.data_nasc   = Convert.ToDateTime(dr["id_cli"]);
                    cliente.email       = Convert.ToString(dr["id_cli"]);
                }

                return cliente;
            }
            catch (Exception ex)
            {

                throw new Exception(ex.Message);
            }
            finally
            {

                con.Close();
            }
        }

        //// metodo para listar todos os clientes cadastrados
        public List<Cliente> listar() {

            try
            {
                SqlConnection con = connectionBD.criarConexao();

                cmd = new SqlCommand("select * from Cliente2", con);

                //////  muda um pouco
                dr = cmd.ExecuteReader();

                List<Cliente> lista = new List<Cliente>();

                while(dr.Read()){

                    cliente.id_cli = Convert.ToInt32(dr["id_cli"]);
                    cliente.nome = Convert.ToString(dr["nome"]);
                    cliente.telefone = Convert.ToInt32(dr["telefone"]);
                    cliente.telemovel = Convert.ToInt32(dr["telemovel"]);
                    cliente.morada = Convert.ToString(dr["id_cli"]);
                    cliente.sexo = Convert.ToString(dr["id_cli"]);
                    cliente.data_nasc = Convert.ToDateTime(dr["id_cli"]);
                    cliente.email = Convert.ToString(dr["id_cli"]);

                    lista.Add(cliente);
                }

                return lista;

            }
            catch (Exception ex)
            {

                throw new Exception(ex.Message);
            }
            finally {

                con.Close();
            }
        }

    }
}

Action on the Sign Up Behind button:

using System;
using System.Collections.Generic;
using System.ComponentModel;
using System.Data;
using System.Drawing;
using System.Linq;
using System.Text;
using System.Threading.Tasks;
using System.Windows.Forms;

using AcessoBancoDados;
using Objetos_de_Transferencia;

namespace Apresentacao
{
    public partial class fmCliente : Form
    {
        Cliente cliente = null;
        ClienteDAO clienteDAO = null;

        public fmCliente()
        {
            InitializeComponent();
        }

        private void tbnCadastrarCliente_Click(object sender, EventArgs e)
        {
            try
            {
                cliente = new Cliente();

                cliente.nome        = tbNome.Text;
                cliente.telefone    = Convert.ToInt32(tbTelefone.Text);
                cliente.telemovel   = Convert.ToInt32(tbTelemovel.Text);
                cliente.morada      = tbMorada.Text;
                cliente.sexo        = tbSexo.Text;
                cliente.data_nasc   = dtpData_Nasc.Value;
                cliente.email       = tbEmail.Text;

                clienteDAO = new ClienteDAO();

                clienteDAO.gravar(cliente);  //// acessei a minha classa ClienteDAO

                //// DataTable dt = new DataTable();

                MessageBox.Show("Cliente "+cliente.nome+" cadastrado com sucesso!!!!!");

            }
            catch (Exception ex)
            {

                throw new Exception("Erro ao efetuar o cadastro:.... detalhes" + ex.Message);
            }
        }
    }
}

The error occurs here: 

throw new Exception("Erro ao efetuar o cadastro:.... detalhes" + ex.Message);

This is the error message:

Erro ao efetuar o cadastro:.... detalhesA referência de objecto não foi definida como uma instância de um objecto

The error occurs here in the record method call:

private void tbnCadastrarCliente_Click(object sender, EventArgs e)
    {
        try
        {
            cliente = new Cliente();

            cliente.nome        = tbNome.Text;
            cliente.telefone    = Convert.ToInt32(tbTelefone.Text);
            cliente.telemovel   = Convert.ToInt32(tbTelemovel.Text);
            cliente.morada      = tbMorada.Text;
            cliente.sexo        = tbSexo.Text;
            cliente.data_nasc   = dtpData_Nasc.Value;
            cliente.email       = tbEmail.Text;

            clienteDAO = new ClienteDAO();

            clienteDAO.gravar(cliente);  //// acessei a minha classa ClienteDAO

            //// DataTable dt = new DataTable();

            MessageBox.Show("Cliente "+cliente.nome+" cadastrado com sucesso!!!!!");

        }
        catch (Exception ex)
        {

            throw new Exception("Erro ao efetuar o cadastro:.... detalhes" + ex.Message);
        }
    }

using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Threading.Tasks;

using System.Data;
using System.Data.SqlClient;
using AcessoBancoDados.Properties;   //// para poder pegar a minha string de conexao

namespace AcessoBancoDados
{
    public class ConnectionBD
    {
        //criar a minha conexao e rtorna a minha string de conexao
        public SqlConnection criarConexao()
        {

            return new SqlConnection(Settings.Default.connectionString);  //pegando a minha string de conexao com a minha classe setting para trazer a string Conexao
        }
    }
}
c#

  • What is your doubt?

  • when I try to insert a new client, it returns the following error: Error when registering:.... Object reference is not defined as an instance of an object.

  • If you can help, I really appreciate

  • The mistake is in that part:

  • throw new Exception("Error when registering:.... details" + ex.Message);

  • What error appears in?

  • This error: Error when registering:.... detailsThe object reference was not defined as an instance of an object

  • Yes. Something is void. It returns a Stack Trace?

  • Enter the code of this method "connectionBD.creatConexao()" in the question, please.

  • 1

    Debugging at which time the error pops up. You put the catch, but the error popped inside Try. If it’s the moment you call record(), debugging record, when it gives the error or another method. You can post this?

  • Error inside the catch

  • And say the following:

  • Error while registering:.... detailsThe object reference was not defined as an instance of an object

  • When he enters the gravar and creates an instance is returning con other than nil?

  • 1

    @Antoniogoncalves, I’d like to point out two anti-pattern in your code, 1 - you’re re-throwing the wrong way new Exception(ex.Message), you are stripping all information about the error before it is dealt with, do new Exception("Sua Mensagem de Erro", ex), the second point is not to use the using for objects implementing Idisposable, in this case the SqlConnection and SqlCommand. Finally, I advise you to implement UnitOfWork Pattern to avoid creating a connection to each call.

Show 10 more comments

2 answers

1

Make sure the connection string arrives correctly by the class Setting. If it’s okay, the problem is that in your class ClienteDAO you have two instances of SqlConnection:

public class ClienteDAO
    {

        public ConnectionBD connectionBD = new ConnectionBD();
        public SqlCommand cmd = null;
        public SqlDataReader dr = null;

        SqlConnection con = null; // <--- Uma instância

        Cliente cliente = null;

        //// metodo para inserir um novo cliente
        public void gravar(Cliente cliente) {

            try
            {
                SqlConnection con = connectionBD.criarConexao(); // <--- Outra instância

                cmd = new SqlCommand("insert into Cliente2(nome, telefone, telemovel, morada, sexo, data_nasc, email)values(@nome, @telefone, @telemovel, @morada, @sexo, @data_nasc, @email)", con);
                //etc

I advise you to leave the instantiation of the connection within each method that will be responsible for some connection with the database.

Then I’d stay that way:

public class ClienteDAO
    {

        public ConnectionBD connectionBD = new ConnectionBD();
        public SqlCommand cmd = null;
        public SqlDataReader dr = null;

        Cliente cliente = null;

        //// metodo para inserir um novo cliente
        public void gravar(Cliente cliente) {

            try
            {
                SqlConnection con = connectionBD.criarConexao();

                cmd = new SqlCommand("insert into Cliente2(nome, telefone, telemovel, morada, sexo, data_nasc, email)values(@nome, @telefone, @telemovel, @morada, @sexo, @data_nasc, @email)", con);
                //etc

  • I honestly saw no change in your code

  • You have to do two tests...one with the duplicity change I mentioned and check the Settings class. Is there any way to put the code of the Settings class? I want to see how you do to get this Settings.default.connectionString

  • Well, thank you for your help, until finally I was able to solve the problem, the real problem was the opening of the connection, just put the con. Close() before making the query, thank you anyway

0

Antonio, I advise you to use the method in your routines 

using (sqlconnection con = New connectionDb.abreConexao());
{
 Rotina de interação com o banco aqui.
}

Browser other questions tagged c#

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