Datareader Error Data is Null

Question

Good night!

I am deadlocked in bringing data from a Datareader with Null values. It simply does not let bring the results.

I wanted it to even contain null values, so users can update in the database through Forms

error that appears: System.Data.Sqltypes.Sqlnullvalueexception: 'Data is Null. Cannot call this method or this property at Null values.'

SqlConnection con = ConectDAO.abrir();
                        SqlCommand conexao = new SqlCommand("SEARCH_DADOS_INFO_PREST_TEXTBOX", con);
                        conexao.Parameters.AddWithValue("@CNPJ", SqlDbType.Int);
                        conexao.Parameters["@CNPJ"].Value = toolStripTXT_Pesq_Cnpj.Text;
                        conexao.CommandType = CommandType.StoredProcedure;
                        SqlDataReader dr;
                        dr = conexao.ExecuteReader();
                        dr.Read();
                       //txt_CODPASTA.Text = dr.GetString(0);
                       txt_razao.Text = dr.GetString(1);
                       txt_nomeFant.Text = dr.GetString(2);
                       txt_CNPJgroup.Text = dr.GetString(3);
                       txt_cep.Text = dr.GetString(4);
                       txt_end.Text = dr.GetString(5);
                       txt_Compl.Text = dr.GetString(6);
                       txt_Num.Text = dr.GetString(7);
                       txt_Bair.Text = dr.GetString(8);
                       txt_Cid.Text = dr.GetString(9);
                       txt_UF.Text = dr.GetString(10);

Answer 1

This happens because the returns of DataReader are not of the same type as .NET, then when it executes a GetString or any other type (GetInt for example), the value is converted, and a value null make a mistake.

In this case just check if the value is DBNull before:

txt_razao.Text = dr.IsDBNull(1) ? null : dr.GetString(1);

That is, it validates first if it is DBNull (null type coming from the database), if it is, leaves the value as null, or read the value with GetString