Returning multiple sqlcommands

Question

I get that we can use using to have multiple commands in a sqlconnection.

Like this:

using (SqlConnection connection = new SqlConnection(connectionString))
{
    connection.Open();
    using (SqlCommand command1 = new SqlCommand(commandText1, connection))
    {
    }
    using (SqlCommand command2 = new SqlCommand(commandText2, connection))
    {
    }
    // etc
}

However, what if the using is in a method that returns a reader cast?

Like this:

public IEnumerable<LocationInfo> GetData()
{           
    using (var connection = new SqlConnection(ConfigurationManager.ConnectionStrings["Database"].ConnectionString))
    {
        //connection.Close();
        connection.Open();
        using (SqlCommand command = new SqlCommand(@"SELECT .... ", connection))
        {           
            command.Notification = null;
            SqlDependency dependency = new SqlDependency(command);
            dependency.OnChange += new OnChangeEventHandler(dependency_OnChange);

            if (connection.State == ConnectionState.Closed)
            { connection.Open(); }
            using (var reader = command.ExecuteReader())
            {
                return reader.Cast<IDataRecord>().Select(x => new LocationInfo()                           
                {
                    Names = x.GetString(2),
                    Values = Math.Round(x.GetDouble(7), 2).ToString("#,##0.00"),
                    ValuesDouble = x.GetDouble(7),
                    Values2 = Math.Round(x.GetDecimal(9), 2).ToString("#,##0.00"),
                    ValuesDouble2 = x.GetDecimal(9),
                    truckDelivery=x.GetDecimal(3),
                    truckIdle = x.GetDecimal(4),
                    truckRepair = x.GetDecimal(5),
                    truckReady = x.GetDecimal(6),
                    presentEmp=x.GetInt32(11),
                    absentEmp = x.GetInt32(12),
                    ondutyEmp = x.GetInt32(13),
                }).ToList();
            }
            /* I tried this but it just got ignored
            using (var reader2 = command.ExecuteReader())
            {
                reader2.NextResult();
                return reader2.Cast<IDataRecord>().Select(x => new LocationInfo()
                {
                    SumVol = x.GetString(0)       
                }).ToList();
            }*/
        }
    }
}

Help me please. My second using keeps getting ignored and don't assume that I know anything because I'm new to this. Thank you in advance.

Answer 1

OK so here is what you can do in your situation to merge results from two result sets into one object.

public IEnumerable<LocationInfo> GetData()
{
        List<LocationInfo> locations = new List<LocationInfo>();
        using (var connection = new SqlConnection(ConfigurationManager.ConnectionStrings["Database"].ConnectionString))
        using (SqlCommand command = new SqlCommand(@"SELECT .... ", connection))
        {
            connection.Open();

            using (var reader = command.ExecuteReader())
            {
                LocationInfo x = new LocationInfo();
                while(reader.Read())
                {

                    {
                        x.Names = reader.GetString(2),
                        x.Values=Math.Round(reader.GetDouble(7),2).ToString("#,##0.00"),
                        x.ValuesDouble = reader.GetDouble(7),
                        Values2 = Math.Round(reader.GetDecimal(9), 2).ToString("#,##0.00"),
                        x.ValuesDouble2 = reader.GetDecimal(9),
                        x.truckDelivery=reader.GetDecimal(3),
                        x.truckIdle = reader.GetDecimal(4),
                        x.truckRepair = reader.GetDecimal(5),
                        x.truckReady = reader.GetDecimal(6),
                        x.presentEmp=reader.GetInt32(11),
                        x.absentEmp = reader.GetInt32(12),
                        x.ondutyEmp = reader.GetInt32(13),
                    };

                }
                if(reader.NextResult())
                {
                 while (reader.Read())
                 {
                  x.SumVol=reader.GetString(0);
                 }
                }
                locations.Add(x);
            }
        }
        return locations;
}

Answer 2

You need to Read the record pointed by the SqlDataReader obtained by the ExecuteReader. Accumulate your LocationInfo in a List and return them when you have finished to loop over the reader.

public IEnumerable<LocationInfo> GetData()
{
    List<LocationInfo> locations = new List<LocationInfo>();
    using (var connection = new SqlConnection(ConfigurationManager.ConnectionStrings["Database"].ConnectionString))
    using (SqlCommand command = new SqlCommand(@"SELECT .... ", connection))
    {
        connection.Open();
        using (var reader = command.ExecuteReader())
        {
            while(reader.Read())
            {
                LocationInfo x = new LocationInfo()
                {
                    Names = x.GetString(2),
                    Values = Math.Round(x.GetDouble(7), 2).ToString("#,##0.00"),
                    ValuesDouble = x.GetDouble(7),
                    Values2 = Math.Round(x.GetDecimal(9), 2).ToString("#,##0.00"),
                    ValuesDouble2 = x.GetDecimal(9),
                    truckDelivery=x.GetDecimal(3),
                    truckIdle = x.GetDecimal(4),
                    truckRepair = x.GetDecimal(5),
                    truckReady = x.GetDecimal(6),
                    presentEmp=x.GetInt32(11),
                    absentEmp = x.GetInt32(12),
                    ondutyEmp = x.GetInt32(13),
                };
                locations.Add(x);
            }
        }
    }
    return locations;

}