CODEWITHSUNDEEP
c#sql-server
dcoffin
dcoffin

Reputation: 33

Saving results from SELECT query to another table

I have a SQL query generates the results found below it using Visual Studio query window:

SELECT SUM(TaskLength) AS TaskLength 
FROM myTable 
WHERE EventStartTime BETWEEN '2019/8/17' AND '2019/8/19' 
GROUP BY TaskName 
ORDER BY TaskLength

The query results window produces the following:

TaskName        TaskLength
--------------------------
Idle Time        20
Start Shift      31
Downtime         85
Engineering     120
Part Prep       141
Maintenance     172
Production      417

My C# code below only returns one string representing one column from one row. 

using (SqlConnection con = new SqlConnection(_connectionString))
{
     string query = @"SELECT SUM(TaskLength) AS TaskLength 
                      FROM myTable 
                      WHERE EventStartTime BETWEEN '2019/8/17' AND '2019/8/19' 
                      GROUP BY TaskName 
                      ORDER BY TaskLength";

     using (SqlCommand cmd = new SqlCommand(query, con))
     {
          con.Open();
          object result = cmd.ExecuteScalar();
          string totalMinutes = Convert.ToString(result);
     }
}

Does the returned cmd.ExecuteScalar object represent the entire resulting table?

If not, is there another way to do this. My thoughts are to save the results to an intermediate table (if it does not already do this) and save that to a .csv file there unless there is a better way.

Upvotes: 0

Views: 455

Answers (1)

Steve
Steve

Reputation: 216291

ExecuteScalar returns only the first column from the first row.
If you have many rows to read then you need to call ExecuteReader and loop on the using the value returned by the SqlDataReader.Read until it returns false.
While looping you store the results of your query in some kind of List creating objects matching the info returned by the reader.

Then, this list can be easily used as DataSource for some kind of User Interface object like a DataGridView or similar or used to write your data into a file or other storage.

// The model that describes the data returned by the query
public class TaskData
{
    public string Name {get;set;}
    public int Length {get;set;}
} 

..... 
// Where you store each record retrieved by the query
List<TaskData> results = new List<TaskData>();

using (SqlConnection con = new SqlConnection(_connectionString))
{
   // Added the taskname to the query
   string query = @"SELECT TaskName, SUM(TaskLength) as TaskLength 
                  FROM myTable 
                  WHERE EventStartTime 
                  BETWEEN '2019/8/17' AND '2019/8/19' 
                  GROUP BY TaskName ORDER BY TaskLength";

   using (SqlCommand cmd = new SqlCommand(query, con))
   {
      con.Open();

      // Get a reader to loop over the results
      using(SqlDataReader reader = cmd.ExecuteReader())
      {
          // Start reading data (until there are no more records to read)
          while(reader.Read())
          {
               // From the current record build the TaskData info
               TaskData data = new TaskData
               {
                   Name = reader["TaskName"].ToString(),
                   Length = Convert.ToInt32(reader["TaskLength"]);
               }

               // Add this info the the collection of your results
               results.Add(data);
          }
      }
   }
}

And now, if you want to store the result to a CSV file, the code is simply

File.WriteAllLines("yourFile.csv", 
      string.Join(Environment.NewLine, 
             results.Select(x => x.Name +","+x.Length))

Upvotes: 5

Related Questions