Get full column from dataset in time format (HH: MM) using C # without using Linq

Question

Is there a way to get the total number of columns from a dataset without using a LINQ query?

CREATE procedure St_Proc_GetUserReportforCurrentDayTask @userID int as Begin set NoCount on; DECLARE @TODAY DATE SET @TODAY = CONVERT(VARCHAR(10), GETDATE(), 111) select CONVERT(VARCHAR,production.CalendarDate,101) + RIGHT (CONVERT(VARCHAR,production.CalendarDate , 100 ) ,7) as Date, RegionAndProjectInfo.RegionProjectName as Region , County.CountyName as County, WorkType.WorkTypeName as WorkType, Task.TaskName as Task, Production.VolumeProcessed as 'Volumes Processed', Production.TimeSpent as 'Duration' from Production inner join RegionAndProjectInfo on RegionAndProjectInfo.RegionProjectID=Production.RegionProjectID inner join County on County.CountyID=Production.CountyID inner join WorkType on WorkType.WorkTypeID=Production.WorkTypeID inner join Task on Task.TaskID=Production.TaskID where Production.UserID=@userID and CalendarDate >= @TODAY End 

From the above stored procedure, I populate the data set. After that, I bind this dataset to the grid view. In the data set, the Duration column contains data in the format HH: MM (example - 01:00, 12:45, 02:59, etc.). Is there a way so that I can get the total Duration in HH:MM format from the dataset itself? I do not want to query the database again to get SUM from Duration . I already posted this question here , but the solution was to use a LINQ query, which I don't want to use.

Answer 1

I'm curious why you don't want to use Linq, but I'm distracted ...

Here is the un-linq-y version using loops:

 double total = 0; foreach(DataRow r in dataSet.Tables[0].Rows) { var DurHour = r["Duration"].ToString().Split(':')[0]; var DurMinute = r["Duration"].ToString().Split(':')[1]; TimeSpan ts = new TimeSpan(int.Parse(DurHour), int.Parse(DurMinute), 0); total += ts.TotalMinutes; } Console.WriteLine("Total time in minutes: {0}", total); 

Answer 2

You can use the DataTable.Compute method to execute the sums, however it will not know how to handle the duration like HH: MM, you will need to provide another colum in your SELECT, which converts HH: MM to a numeric type and then converts it back to HH: MM in your code after summing.

If you can change the database schema, I would save the duration as ticks in the int or bigint column. This makes it easy to convert to TimeSpan on the .NET side. It also allows you to more easily perform sum and range calculations in SQL. If you need to be able to convert TimeSpans to and from ticks into SQL, you can use the following functions that I wrote. I had to insert some random underscores (_) in order to get my response for sending. (For some reason, saving time without them.) They will need to be removed in order for the code to work. Excuse me.

To get ticks from a string representation of a time string:

 /* [-][d.]hh:mm:ss[.fffffff] "-" A minus sign, which indicates a negative time interval. No sign is included for a positive time span. "d" The number of days in the time interval. This element is omitted if the time interval is less than one day. "hh" The number of hours in the time interval, ranging from 0 to 23. "mm" The number of minutes in the time interval, ranging from 0 to 59. "ss" The number of seconds in the time interval, ranging from 0 to 59. "fffffff" Fractional seconds in the time interval. This element is omitted if the time interval does not include fractional seconds. If present, fractional seconds are always expressed using seven decimal digits. */ CREATE FUNCTION [dbo].[ParseTimeSpanString] ( @timespan varchar(26) ) RETURNS INT AS BEGIN DECLARE @hourStart int DECLARE @minuteStart int DECLARE @secondStart int DECLARE @ticks bigint SET @hourStart = CHAR INDEX('.', @timeSpan) + 1 SET @minuteStart = CHAR INDEX(':', @timeSpan) + 1 SET @secondStart = CHAR INDEX(':', @timespan, @minuteStart) + 1 SET @ticks = 0 IF (@hourStart < @minuteStart) BEGIN SET @ticks = CON_VERT(int, LEFT(@timespan, @hourstart - 2)) * 864000000000 END ELSE BEGIN SET @hourStart = 1 END SET @ticks = @ticks + CON_VERT(int, SUBSTRING(@timespan, @hourStart, @minuteStart - @hourStart - 1)) * 36000000000 SET @ticks = @ticks + CON_VERT(int, SUBSTRING(@timespan, @minuteStart, @secondStart - @minuteStart - 1)) * 600000000 SET @ticks = @ticks + CON_VERT(decimal(9,7), SUBSTRING(@timespan, @secondStart, LEN(@timeSpan) - @secondStart)) * 10000000.0 RETURN @ticks END GO 

To get a string representation of TimeSpan from ticks:

 /* [-][d.]hh:mm:ss[.fffffff] "-" A minus sign, which indicates a negative time interval. No sign is included for a positive time span. "d" The number of days in the time interval. This element is omitted if the time interval is less than one day. "hh" The number of hours in the time interval, ranging from 0 to 23. "mm" The number of minutes in the time interval, ranging from 0 to 59. "ss" The number of seconds in the time interval, ranging from 0 to 59. "fffffff" Fractional seconds in the time interval. This element is omitted if the time interval does not include fractional seconds. If present, fractional seconds are always expressed using seven decimal digits. */ CREATE FUNCTION [dbo].[GetTimeSpanString] ( @ticks bigint ) RETURNS varchar(26) AS BEGIN DECL_ARE @timeSpanString varchar(26) IF @ticks < 0 BEGIN SET @timeSpanString = '-' END ELSE BEGIN SET @timeSpanString = '' END SET @ticks = ABS(@ticks) -- Days SET @timeSpanString = @timeSpanString + CON_VERT(varchar(26), FLOOR(@ticks / 864000000000.0)) + '.' SET @ticks = @ticks % 864000000000 -- Hours SET @timeSpanString = @timeSpanString + CON_VERT(varchar(26), FLOOR(@ticks / 36000000000.0)) + ':' SET @ticks = @ticks % 36000000000 -- Minutes SET @timeSpanString = @timeSpanString + CON_VERT(varchar(26), FLOOR(@ticks / 600000000.0)) + ':' SET @ticks = @ticks % 600000000 --Seconds DECLARE @seconds decimal(9,7) SET @seconds = @ticks / 10000000.0 SET @timeSpanString = @timeSpanString + CON_VERT(varchar(26), @seconds) RETURN @timeSpanString END GO