public static decimal GetSum() { List <Total> _Total = new List <Total>(); List <Total> _Total2 = new List <Total>(); decimal TotalRevenue = 0; SqlDataReader dataReader = null; SqlConnection sqlConnection = new SqlConnection("context connection = true"); try { String Query; Query = "SELECT * FROM [SqlCLR].[dbo].[Total]"; SqlCommand sqlCommand = new SqlCommand(Query, sqlConnection); sqlCommand.CommandType = CommandType.Text; sqlConnection.Open(); // establish the connection //dataReader = sqlCommand.ExecuteReader(); // run the query using (dataReader = sqlCommand.ExecuteReader()) { while (dataReader.Read()) { Total _TotalItem = new Total(); //TotalRevenue = (decimal)dataReader["TotalRevenue"] + 1; _TotalItem.TotalRevenue = Convert.ToDecimal(dataReader["TotalRevenue"]); _TotalItem.DatetimeStamp = Convert.ToDateTime(dataReader["DatetimeStamp"]); _Total.Add(_TotalItem); } } foreach (Total _TotalItem in _Total) { Total _TotalItem2 = new Total(); _TotalItem2.TotalRevenue = _TotalItem.TotalRevenue; _TotalItem2.DatetimeStamp = _TotalItem.DatetimeStamp; _Total2.Add(_TotalItem2); } foreach (Total _TotalItem3 in _Total2) { TotalRevenue = _Total2.Count + Convert.ToDecimal(0.01); } } catch (Exception ex) { SqlContext.Pipe.Send("Error caught!"); throw; } finally { // make sure to clean up external resources! SqlContext.Pipe.Send("Cleaning up..."); if (dataReader != null && !dataReader.IsClosed) { dataReader.Close(); } if (sqlConnection.State != ConnectionState.Closed) { sqlConnection.Close(); } } SqlContext.Pipe.Send("Ending..."); return(TotalRevenue); }
public static void GetTotalAmountIntoList() { List <Total> _Total = new List <Total>(); List <Total> _Total2 = new List <Total>(); SqlDataReader dataReader = null; SqlConnection sqlConnection = new SqlConnection("context connection = true"); try { String Query; Query = "SELECT * FROM [SqlCLR].[dbo].[Total]"; SqlCommand sqlCommand = new SqlCommand(Query, sqlConnection); sqlCommand.CommandType = CommandType.Text; sqlConnection.Open(); // establish the connection //dataReader = sqlCommand.ExecuteReader(); // run the query using (dataReader = sqlCommand.ExecuteReader()) { while (dataReader.Read()) { Total _TotalItem = new Total(); _TotalItem.Id = Convert.ToInt32(dataReader["id"]); _TotalItem.TotalRevenue = Convert.ToDecimal(dataReader["TotalRevenue"]); _TotalItem.DatetimeStamp = Convert.ToDateTime(dataReader["DatetimeStamp"]); _Total.Add(_TotalItem); } } int _previousId = -1; foreach (Total _TotalItem in _Total) { Total _TotalItem2 = new Total(); _TotalItem2.Id = _TotalItem.Id; _TotalItem2.TotalRevenue = _TotalItem.TotalRevenue; _TotalItem2.DatetimeStamp = _TotalItem.DatetimeStamp; if (_previousId != -1) { Total _TotalItemPrevious = new Total(); _TotalItemPrevious = _Total.FindLast(x => x.Id == _previousId); _TotalItem2.TotalRevenueNew = _TotalItemPrevious.TotalRevenue; } else { _TotalItem2.TotalRevenueNew = 0; } _previousId = _TotalItem.Id; _Total2.Add(_TotalItem2); } TotalRevenueList = _Total2; } catch (Exception ex) { SqlContext.Pipe.Send("Error caught!"); throw; } finally { // make sure to clean up external resources! SqlContext.Pipe.Send("Cleaning up..."); if (dataReader != null && !dataReader.IsClosed) { dataReader.Close(); } if (sqlConnection.State != ConnectionState.Closed) { sqlConnection.Close(); } } SqlContext.Pipe.Send("Ending..."); }