DBCode (See related posts)

// insert code here..

 The corresponding C# code is very easy to follow.  We perform a select into a DataTable using a DataAdapter.  We then loop the results and calculate the Running Sales on the fly and update the current row in the DataTable at the same time.  Then we perform a Select() on the DataTable to apply the data range to get our final results.

   1:  using System;

   2:  using System.Collections.Generic;

   3:  using System.Data;

   4:  using System.Data.SqlClient;

   5:  using System.Data.SqlTypes;

   6:  using System.Text;

   7:  using Microsoft.SqlServer;

   8:  using Microsoft.SqlServer.Server;

   9:   

  10:   

  11:  public partial class StoredProcedures

  12:  {

  13:    const string SELECT =

  14:  @"SELECT  

  15:    ShipName, ShipAddress, ShipCity, 

  16:    ShipRegion, ShipPostalCode, ShipCountry, 

  17:    CustomerID, CustomerName, Address, City,

  18:    Region, PostalCode, Country, Salesperson, 

  19:    OrderID, OrderDate, RequiredDate, ShippedDate, 

  20:    ShipperName, ProductID, ProductName, UnitPrice, 

  21:    Quantity, Discount, ExtendedPrice, Freight, 

  22:    ExtendedPrice + Freight AS Total

  23:  FROM   Invoices

  24:  WHERE CustomerID = @CustomerID

  25:  AND OrderDate <= @EndDate";

  26:   

  27:    [Microsoft.SqlServer.Server.SqlProcedure]

  28:    public static void RunningSalesCS(

  29:      string customerID, 

  30:      DateTime beginDate, 

  31:      DateTime endDate)

  32:    {

  33:      decimal runningSales = 0.0M;

  34:      DataTable table = new DataTable();

  35:      SqlDataAdapter adapter = null;

  36:      SqlCommand command = null;

  37:   

  38:      using (SqlConnection conn = 

  39:        new SqlConnection("context connection=true"))

  40:      {

  41:        command = new SqlCommand(SELECT, conn);

  42:   

  43:        command.Parameters.AddWithValue("@CustomerID", customerID);

  44:        command.Parameters.AddWithValue("@EndDate", endDate);

  45:   

  46:        adapter = new SqlDataAdapter(command);

  47:   

  48:        conn.Open();

  49:   

  50:        int rows = 0;

  51:   

  52:        try

  53:        {

  54:          rows = adapter.Fill(table);

  55:        }

  56:        finally

  57:        {

  58:          conn.Close();

  59:        }

  60:   

  61:        if (rows == 0) throw new 

  62:          ApplicationException("No rows returned.");

  63:   

  64:        table.Columns.Add(new 

  65:          DataColumn("RunningBalance", 

  66:          typeof(decimal)));

  67:   

  68:        foreach(DataRow row in table.Rows)

  69:        {

  70:          row["RunningBalance"] = 

  71:            (runningSales += (decimal)row["Total"]);

  72:        }

  73:   

  74:        List<SqlMetaData> metaData = 

  75:          new List<SqlMetaData>(rows);

  76:   

  77:        foreach(DataColumn column in table.Columns)

  78:        {

  79:          SqlMetaData newMetaData = null;

  80:   

  81:          if(column.DataType.FullName == "System.String")

  82:          {

  83:              newMetaData = new SqlMetaData(

  84:                column.ColumnName, SqlDbType.NVarChar, 4000);

  85:          }

  86:          else if(column.DataType.FullName == "System.Int32")

  87:          {

  88:              newMetaData = new SqlMetaData(

  89:                column.ColumnName, SqlDbType.Int);

  90:          }

  91:          else if (column.DataType.FullName == "System.Int16")

  92:          {

  93:            newMetaData = new SqlMetaData(

  94:              column.ColumnName, SqlDbType.SmallInt);

  95:          }

  96:          else if (column.DataType.FullName == "System.Int64")

  97:          {

  98:            newMetaData = new SqlMetaData(

  99:              column.ColumnName, SqlDbType.BigInt);

 100:          }

 101:          else if (column.DataType.FullName == "System.DateTime")

 102:          {

 103:            newMetaData = new SqlMetaData(

 104:              column.ColumnName, SqlDbType.DateTime);

 105:          }

 106:          else if (column.DataType.FullName == "System.Boolean")

 107:          {

 108:            newMetaData = new SqlMetaData(

 109:              column.ColumnName, SqlDbType.Bit);

 110:          }

 111:          else if (column.DataType.FullName == "System.Decimal")

 112:          {

 113:            newMetaData = new SqlMetaData(

 114:              column.ColumnName, SqlDbType.Money);

 115:          }

 116:          else if (column.DataType.FullName == "System.Single")

 117:          {

 118:            newMetaData = new SqlMetaData(

 119:              column.ColumnName, SqlDbType.Real);

 120:          }

 121:          else

 122:          {

 123:            throw new ApplicationException(

 124:              "Could not map " + column.DataType.FullName);

 125:          }

 126:   

 127:          metaData.Add(newMetaData);

 128:        }

 129:   

 130:        if (metaData.Count != table.Columns.Count)

 131:        {

 132:          throw new ApplicationException(

 133:            "Not all columns were mapped to SqlMetaData.");

 134:        }

 135:   

 136:        SqlMetaData[] array = (SqlMetaData[])(metaData.ToArray());

 137:   

 138:        if (array == null) throw new ApplicationException(

 139:          "SqlMetaData array is null.");

 140:   

 141:        if (array.Length == 0) throw new ApplicationException(

 142:          "No columns in SqlMetaData array.");

 143:   

 144:        bool isFirst = true;

 145:   

 146:        DataRow[] results = table.Select(

 147:          "[OrderDate]>=#" + beginDate + "#");

 148:   

 149:        SqlContext.Pipe.Send(string.Format(

 150:          "Returning {0} rows.", results.Length));

 151:   

 152:        foreach (DataRow row in results)

 153:        {

 154:          SqlDataRecord record = new SqlDataRecord(array);

 155:   

 156:          for(int i=0; i<record.FieldCount; i++)

 157:          {

 158:            record.SetValue(i, row[i]);

 159:          }

 160:   

 161:          if(!isFirst)

 162:          {

 163:            SqlContext.Pipe.SendResultsRow(record);

 164:          }

 165:          else

 166:          {

 167:            SqlContext.Pipe.SendResultsStart(record);

 168:            SqlContext.Pipe.SendResultsRow(record);

 169:            isFirst = false;

 170:          }

 171:        }

 172:   

 173:        if(!isFirst)

 174:        {

 175:          SqlContext.Pipe.SendResultsEnd();

 176:        }

 177:      }

 178:    }

 179:  };