DBCode

jdmays () — Tue, 05 Sep 2006 00:22:36 GMT
// description of your code here


// 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 metaData = 



  75:          new List(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

 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:  };
DZone Snippets: dbcode code

DBCode
