System.Nullreferenceexception: 'Undefined object reference for an instance of an object

Question

I was trying to run my database but this error appears. System.Nullreferenceexception: 'Undefined object reference for an instance of an object.

con era null

    using System;
using System.Collections.Generic;
using System.Configuration;
using System.Data;
using System.Data.SqlClient;
using System.Linq;
using System.Web;

namespace Roger_Silva_DR2_AT.DAL
{
    public class DataAccessLayer
    {

        public string InserirDados(Usuario objuser)

        {
            SqlConnection con = null;

            string result = "";

            try

            {

                con = new SqlConnection(ConfigurationManager.ConnectionStrings["mycon"].ToString());

                SqlCommand cmd = new SqlCommand("Usp_InserirAtulizarDeletar_Usuario", con)
                {
                    CommandType = CommandType.StoredProcedure
                };

                cmd.Parameters.AddWithValue("@ID", 0);

                cmd.Parameters.AddWithValue("@Nome", objuser.Nome);

                cmd.Parameters.AddWithValue("@Sobrenome", objuser.Sobrenome);

                cmd.Parameters.AddWithValue("@Nascimento", objuser.Nascimento);

                cmd.Parameters.AddWithValue("@Query", 1);

                con.Open();

                result = cmd.ExecuteScalar().ToString();

                return result;

            }

            catch

            {

                return result = "";

            }

            finally

            {

                con.Close();

            }

        }

        public string AtualizarDados(Usuario objuser)

        {

            SqlConnection con = null;

            string result = "";

            try

            {

                con = new SqlConnection(ConfigurationManager.ConnectionStrings["mycon"].ToString());

                SqlCommand cmd = new SqlCommand("Usp_InserirAtualizarDeletar_Usuario", con)
                {
                    CommandType = CommandType.StoredProcedure
                };

                cmd.Parameters.AddWithValue("@ID", objuser.ID);

                cmd.Parameters.AddWithValue("@Nome", objuser.Nome);

                cmd.Parameters.AddWithValue("@Sobrenome", objuser.Sobrenome);

                cmd.Parameters.AddWithValue("@Nascimento", objuser.Nascimento);

                cmd.Parameters.AddWithValue("@Query", 2);

                con.Open();

                result = cmd.ExecuteScalar().ToString();

                return result;

            }

            catch

            {

                return result = "";

            }

            finally

            {

                con.Close();

            }

        }

        public string DeletarDados(Usuario objuser)

        {

            SqlConnection con = new SqlConnection("LocalDB)\\MSSQLLocalDB");

            string result = "";

            try

            {

                con = new SqlConnection(ConfigurationManager.ConnectionStrings["mycon"].ToString());

                SqlCommand cmd = new SqlCommand("Usp_InserirAtualizarDeletar_Usuario", con)
                {
                    CommandType = CommandType.StoredProcedure
                };

                cmd.Parameters.AddWithValue("@ID", objuser.ID);

                cmd.Parameters.AddWithValue("@Nome", null);

                cmd.Parameters.AddWithValue("@Sobrenome", null);

                cmd.Parameters.AddWithValue("@Nascimento", null);

                cmd.Parameters.AddWithValue("@Query", 3);

                con.Open();

                result = cmd.ExecuteScalar().ToString();

                return result;

            }

            catch

            {

                return result = "";

            }

            finally

            {

                con.Close();

            }

        }

        public List<Usuario> SelecionarTodosDados()

        {

            SqlConnection con = null;

            DataSet ds = null;

            List<Usuario> userlist = null;

            try

            {

                con = new SqlConnection(ConfigurationManager.ConnectionStrings["mycon"].ToString());

                SqlCommand cmd = new SqlCommand("Usp_InserirAtualizarDeletar_Usuario", con)
                {
                    CommandType = CommandType.StoredProcedure
                };

                cmd.Parameters.AddWithValue("@ID", null);

                cmd.Parameters.AddWithValue("@Nome", null);

                cmd.Parameters.AddWithValue("@Sobrenome", null);

                cmd.Parameters.AddWithValue("@Nascimento", null);

                cmd.Parameters.AddWithValue("@Query", 4);

                con.Open();

                SqlDataAdapter da = new SqlDataAdapter
                {
                    SelectCommand = cmd
                };

                ds = new DataSet();

                da.Fill(ds);

                userlist = new List<Usuario>();

                for (int i = 0; i < ds.Tables[0].Rows.Count; i++)

                {
                    Usuario cobj = new Usuario
                    {
                        ID = Convert.ToInt32(ds.Tables[0].Rows[i]["ID"].ToString()),

                        Nome = ds.Tables[0].Rows[i]["Nome"].ToString(),

                        Sobrenome = ds.Tables[0].Rows[i]["Sobrenome"].ToString(),

                        Nascimento = Convert.ToDateTime(ds.Tables[0].Rows[i]["Nascimento"].ToString())
                    };



                    userlist.Add(cobj);

                }

                return userlist;

            }

            catch

            {

                return userlist;

            }

            finally

            {

                con.Close();

            }

        }

        public Usuario SelecionarDadosbyID(string ID)

        {

            SqlConnection con = null;

            DataSet ds = null;

            Usuario cobj = null;

            try

            {

                con = new SqlConnection(ConfigurationManager.ConnectionStrings["mycon"].ToString());

                SqlCommand cmd = new SqlCommand("Usp_InserirAtualizarDeletar_Usuario", con)
                {
                    CommandType = CommandType.StoredProcedure
                };

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

                cmd.Parameters.AddWithValue("@Nome", null);

                cmd.Parameters.AddWithValue("@Sobrenome", null);

                cmd.Parameters.AddWithValue("@Nascimento", null);

                cmd.Parameters.AddWithValue("@Query", 5);

                SqlDataAdapter da = new SqlDataAdapter
                {
                    SelectCommand = cmd
                };

                ds = new DataSet();

                da.Fill(ds);



                for (int i = 0; i < ds.Tables[0].Rows.Count; i++)

                {

                    cobj = new Usuario
                    {
                        ID = Convert.ToInt32(ds.Tables[0].Rows[i]["ID"].ToString()),

                        Nome = ds.Tables[0].Rows[i]["Nome"].ToString(),

                        Sobrenome = ds.Tables[0].Rows[i]["Sobrenome"].ToString(),

                        Nascimento = Convert.ToDateTime(ds.Tables[0].Rows[i]["Nascimento"].ToString())
                    };

                }

                return cobj;

            }

            catch

            {

                return cobj;

            }

            finally

            {

                con.Close();

            }

        }

    }
}

Answer 1

I usually use a Static class that always returns a new Instance of SqlConnection for each operation.

didn’t understand the SqlConnection con = null. Another thing is that you can return the direct result without having to instantiate one string result.

I set up an example below that only has different the type of return(because I used bool).

public bool InserirDados(Usuario objuser){

    try{    

        using (SqlCommand Cmd = new SqlCommand()){

            SqlConnection Con = new SqlConnection(ConfigurationManager.ConnectionStrings["mycon"].ConnectionString);

            Cmd.Connection = Con;
            Cmd.CommandType = CommandType.StoredProcedure;
            Cmd.CommandText = "Usp_InserirAtulizarDeletar_Usuario";

            Cmd.Parameters.AddWithValue("@ID", 0);
            Cmd.Parameters.AddWithValue("@Nome", objuser.Nome);
            Cmd.Parameters.AddWithValue("@Sobrenome", objuser.Sobrenome);
            Cmd.Parameters.AddWithValue("@Nascimento", objuser.Nascimento);
            Cmd.Parameters.AddWithValue("@Query", 1);

            Con.Open();
            if (Cmd.ExecuteNonQuery() > 0){
                Con.Close();
                return true;
            }
            else
                Con.Close();

        }
        return false
    }
    catch (SqlException ex){
        MessageBox.Show($"Ocorreu um erro: {ex.message}");
    }
}

I didn’t use the Finally block to check if the connection is still open because instâncie Con within the Using() and within it I treated its closing. Try it that way.

I hope I’ve helped.