System.NullReferenceException: 'Object reference not set to an instance of an object

0

I was trying to run my database but this error appears. System.NullReferenceException: 'Object reference not set to an instance of an object.

with 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();

            }

        }

    }
}
    
asked by anonymous 07.02.2018 / 03:21

1 answer

1

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

I did not understand SqlConnection con = null . Another thing is that you can return the direct result without having to instantiate a string result .

I've set up an example below that just has different type of return (since 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 did not use the finally block to check if the connection is still open, since I instantiated Con within Using() and within it I handled its closing. Try it that way.

I hope I have helped.

    
07.02.2018 / 04:51