How to store SqlDataReader in variable or properties!!

Question

Hi .
I'm trying to store SqlDataReader retrieved data in variable like this

String name;
SqlDataReader db = Cmd.ExecuteReader();
db.Read();
name = db[2].ToString();
NameTextBox.Text=name;

---

When i do like that it throw a Null reference exception
object reference not set to an instance of an object.

---

And Know that when i break with the debugger the variable [name] after [db[2].ToString()] Has the value
from the database..

Please help
Thank you so much 💖

Answer 1

I found the reason of the current problem.

 mainForm = new RadTabbedForm1(Type, Number);  

You created a new instance of MainForm in SearchForm, so there are three forms currently.

MainForm => SrarchForm =>MainForm

It's just that you didn't call the Show() method when you created the MainForm, so we couldn't see the third one.

The code you wrote in MainForm to query the database and modify the textBox value will only be executed in the third form (the second mainForm), and what we see is the first mainForm.

So, in any case, the value of textBox in this mainForm will not change.

Try to set Number and Type as Property in SearchForm, and then call it in MainForm.

MainForm:

       public Int64 NUMNER { get; set; }  
        public string TYPE { get; set; }  
        Pations pation;  
        public async void _Load(string typ, Int64 num)  
        {  
            var result = Task<Pations>.Run(() =>  
            {  
                Pations p = Loadpations(typ, num);  
                return p;  
            });  
            Pations PationsResult = await result;  
        }  
  
        private Pations Loadpations(string typ, Int64 num)  
        {  
            // ......  
            this.Invoke((MethodInvoker)delegate ()  
            {  
                textBox1.Text = pation.LastName;  
            });  
            return pation;  
        }  
  
        private void button1_Click(object sender, EventArgs e)  
        {  
            textBox2.Text = "tesst";  
            using (SearchForm searchForm = new SearchForm())  
            {  
                searchForm.ShowDialog();  
                NUMNER = searchForm.Number;  
                TYPE = searchForm.Type;  
            }  
            _Load(TYPE, NUMNER);  
        }  

SearchForm:

       public Int64 Number { get; set; }  
        public string Type { get; set; }  
        private void button1_Click(object sender, EventArgs e)  
        {  
            RadTabbedForm1 mainForm;  
  
            Number = Convert.ToInt64(textBox1.Text);  
            if (checkedListBox1.CheckedItems.Count>0)  
            {  
                if (checkedListBox1.GetItemChecked(0)==true)  
                {  
                    Type = "Phone";  
                    this.Close();  
                }  
                else if (checkedListBox1.GetItemChecked(1) == true)  
                {  
                    Type = "Code";  
                    this.Close();  
                }  
            }  
            else  
            {  
                MessageBox.Show("error");  
            }  
        }  

---

If the response is helpful, please click "Accept Answer" and upvote it.
Note: Please follow the steps in our documentation to enable e-mail notifications if you want to receive the related email notification for this thread.

Answer 2

The following does not check for null values, written using C#9, .NET Framework 5.0. If using say .NET Framework 4.8, several minor changes are needed like each using statement needs {} and is null will be == null

Notes

• Never use SELECT *, always specify the columns like I have
• AddWithValue 90 percent of the time is okay but using Add is always best for parameters.
• Return data and an exception object as a value named tuple so you know if it's safe to use the data
• Asynchronous methods are optional but when used with a slow server keeps the app responsive
• Always best to run SQL statement outside of code to know they work like in SSMS
• And if ambitious write unit test to validate code.
• Write data operations in a separate class project

Table query

SELECT Id,   
       Code,   
       FirstName,   
       LastName,   
       SUBSTRING(phone, 1, 3) + '-' + SUBSTRING(phone, 4, 3) + '-' + SUBSTRING(phone, 7, 4)  
FROM dbo.Patient;  

Then

Code

Concrete class for returning data

public class Patient  
{  
    public int Id { get; set; }  
  
    public string Code { get; set; }  
  
    public string FirstName { get; set; }  
  
    public string LastName { get; set; }  
  
    public string FullName => $"{FirstName} {LastName}";  
  
    public string Phone { get; set; }  
  
}  

Code to read from database

public class PatientOperations  
{  
    public static string ConnectionString = "Server=.\\SQLEXPRESS;Database=ForumExample;Integrated Security=true";  
  
    public static async Task<(Patient patient, Exception exception)> PatientByPhoneNumber(string phoneNumber)  
    {  
  
        Patient patient = new ();  
          
        return await Task.Run(async () =>  
        {  
  
            await using var cn = new SqlConnection(ConnectionString);  
            await using var cmd = new SqlCommand  
            {  
                Connection = cn,   
                CommandText = "SELECT Id, Code, FirstName, LastName, Phone FROM dbo.Patient WHERE Phone = @Phone;"  
            };  
  
            cmd.Parameters.Add("@Phone", SqlDbType.NVarChar).Value = phoneNumber;  
              
            try  
            {  
                  
                await cn.OpenAsync();  
                var reader = await cmd.ExecuteReaderAsync();  
  
                if (!reader.HasRows) return (patient, null);  
                  
                reader.Read();  
                  
                patient.Id = reader.GetInt32(0);  
                patient.Code = reader.GetString(1);  
                patient.FirstName = reader.GetString(2);  
                patient.LastName = reader.GetString(3);  
                patient.Phone = phoneNumber;  
                  
                return (patient, null);  
            }  
            catch (Exception exception)  
            {  
                return (patient, exception);  
            }  
  
        });  
    }  
}  

Code in a say a button click with mocked phone number. Mark the button click with async.

var (patient, exception) = await PatientOperations.PatientByPhoneNumber("8881231234");  
if (exception is null)  
{  
    FullNameTextBox.Text = patient.FullName;  
}  
else  
{  
    MessageBox.Show(exception.Message);  
}  

TextBox populated with full name, of course you can have a separate TextBox for each property.

Answer 3

Hi hamed,

First of all, the code you posted can't be correct! Cmd.ExecuteReader() returns a SqlDataReader, not a SqlDataAdapter. The code you posted shouldn't even compile.

You probably want something like this:

string name = "";
SqlDataReader rdr = Cmd.ExecuteReader();
while (rdr.Read())
{
    name = rdr[2].ToString());
}
rdr.Close()
NameTextBox.Text=name;