Microsoft.data.sqlclient issue with executeReader()

[JordHendriks]

Describe the bug

When debugging, i receive an error on line: SqlDataReader reader = command.ExecuteReader(). Using microsoft.data.sqlclient with the provided code, i try to connect with my sql database. All details are correctly filled but this exception keeps happening.

Exception message: System.NullReferenceException: 'Object reference not set to an instance of an object.'
Stack trace: SQLTest.dll!test.script.Update() Line 43

To reproduce

Include a complete code listing (or project/solution) that we can run to reproduce the issue.

Partial code listings, or multiple fragments of code, will slow down our response or cause us to push the issue back to you to provide code to reproduce the issue.

using System;
using System.Collections;
using System.Collections.Generic;
using System.ComponentModel;
using System.Linq;
using System.Drawing;
using System.Runtime.InteropServices;
using System.Data;
using Microsoft.Data.SqlClient;
//using System.Data.SqlClient;
using System.ComponentModel.DataAnnotations;
using System.Reflection;

namespace test
{
    public class script : Object
    {
        //private int lastValue;
        //private DateTime lastUpdateTime;
        //private SqlConnection sqlConnection;
        //private SqlCommand sqlCommand;

        static void Main()
        {
            Console.WriteLine("started");
            script script = new script();                   
            script.Update();

        }
        public void Update()
        {
            DateTime lastPrintTime = DateTime.MinValue;
            int lastValue = 0;
            // Connect to the database and retrieve the values
            string connectionString = "Data Source=172.16.41.11;Initial Catalog=SQLFromPLC;User ID=SQL_S71500;Password=SQL_S71500;";
            string queryString = "SELECT TOP 100 [S1_Value], [Timestamp] FROM [SQLFromPLC].[dbo].[PLC_CL0100010] ORDER BY [Timestamp] ASC"; // retrieve 10 values ordered by timestamp
            //string queryString = "SELECT TOP 10 [S1_Value], [S2_Value], [S3_Value], [S4_Value], [M1_Value], [M2_Value], [M3_Value], [M4_Value], [Dir1_Value], [Dir2_Value], [Dir3_Value], [Dir4_Value], [Timestamp] FROM [SQLFromPLC].[dbo].[PLC_CL+sender.Parent.FullName()+] ORDER BY [Timestamp] ASC"; // retrieve 10 values ordered by timestamp
            using (SqlConnection connection = new SqlConnection(connectionString))
            {
                using (SqlCommand command = new SqlCommand(queryString, connection))
                {
                    connection.Open();
                    using (SqlDataReader reader = command.ExecuteReader())
                    {
                        if (reader.Read())
                        {
                            lastPrintTime = Convert.ToDateTime(reader["Timestamp"]);
                            int value = Convert.ToInt32(reader["S1_Value"]);
                            Console.WriteLine("S1_Value: {0} Timestamp: {1}", value, lastPrintTime);
                            if (value == 1)
                            {
                                //sender.sensorCleared = true;
                                //UpdateVal();
                                Console.WriteLine("Sensor now clear // green");
                            }
                            else if (value == 0)
                            {
                                //sender.sensorCleared = false;
                                //UpdateVal();
                                Console.WriteLine("Sensor now not clear // red");
                            }
                            lastValue = value;

                        }
                        while (reader.Read())
                        {
                            DateTime timestamp = Convert.ToDateTime(reader["Timestamp"]);
                            int value = Convert.ToInt32(reader["S1_Value"]);
                            TimeSpan timeSinceLastPrint = timestamp - lastPrintTime;
                            lastPrintTime = timestamp;
                            if (timeSinceLastPrint.TotalMilliseconds >= 0)
                            {
                                System.Threading.Thread.Sleep(timeSinceLastPrint);
                                if (value != lastValue)
                                {
                                    if (value == 1)
                                    {
                                        //sender.sensorCleared = true;
                                        //UpdateVal();
                                        Console.WriteLine("S1_Value: {0} Timestamp: {1}", value, timestamp);
                                        Console.WriteLine("Sensor now clear // green");
                                    }
                                    else if (value == 0)
                                    {
                                        //sender.sensorCleared = false;
                                        //UpdateVal();
                                        Console.WriteLine("S1_Value: {0} Timestamp: {1}", value, timestamp);
                                        Console.WriteLine("Sensor now not clear // red");
                                    }
                                    lastValue = value;
                                }

                            }
                        }
                        reader.Close();
                    }
                }
            }
        }        
    }   
}

Expected behavior

When using System.data.SqlClient everything works as it should but I need to use the microsoft one because it is not outdated. When debugging, I expect the code to read from this database and show me all the values in the tables.

Further technical details

Microsoft.Data.SqlClient version: (found on the nuget or Microsoft.Data.SqlClient.dll) both options do not work for me. .NET target: (Framework 6.0) SQL Server version: (SQL Server 2022) Operating system: (Windows 10 2023)

Additional context SQLTest.zip

[JRahnama]

@JordHendriks thanks for opening the issue. we will look into this.

[JRahnama]

Does it fail only for the highlighted query or any other query fails as well? If only fails for this query can you provide the create command for the database please?

[JordHendriks]

It is only the highlighted query. The others do not fail. I added a print message in between every line and it works up until the line mentioned above.

[JordHendriks]

@JRahnama I am using SQL server 2022 configuration manager and SQL server management studio 2019. Might not have been clear in my description.

[JordHendriks]

I apologize, you asked me to send the create command of the database. CREATE TABLE [SQLFromPLC].[dbo].[PLC_CL0100010] (S1_Value bit, S2_Value bit, S3_Value bit, S4_Value bit, M1_Value bit, M2_Value bit, M3_Value bit, M4_Value bit, Dir1_Value bit, Dir2_Value bit, Dir3_Value bit, Dir4_Value bit, Timestamp datetime);

This is the table I created. I have tried several things but the error in C# looks like it occurs at the SqlDataReader reader = command.ExecuteReader() where command is getting the System.NullReferenceException.

[JRahnama]

I will test and will update you later.