使用 SQL Server 2005中的 CLR 集成

将 C# 版本的代码复制到下面的代码中，以说明这种可以从 CLR 集成中大大获益的情况：

using System;
using System.Data;
using System.Data.Sql;
using System.Data.SqlServer;
using System.Data.SqlTypes;

public class ProductionSchedule
{
//4-year limit on scheduling
   public const int MAXPRODUCTS = 101;
   public const int MAXWEEKS = 210;
   public const int MAXNAME = 256;

   public ProductionSchedule()
   {
         
   }

   public static int Schedule(SqlDateTime startDate, int numWeeks)
   {
      SqlDateTime[] week = new SqlDateTime[MAXWEEKS];
      int[] quantity;
      int[][] Cij;
      int[] Fk;
      int[] minK = new int[MAXWEEKS];
      int product_id, current_product, product_count = 0;
      int startPeriod;

      // We'll use arrays to keep state about products and forecasts 
in memory. This is only viable given that we know we have a small number 
of products and weeks. 
      // For larger data sets, we would have to consider cursors or 
temporary tables.

      // stored as CLR types since we know they can't be null
      int[] h = new int[MAXPRODUCTS];
int[] K = new int[MAXPRODUCTS];

// stored as nullable SqlChars since the table schema allows for null names
      SqlChars[] productNames = new SqlChars[MAXPRODUCTS];
      bool moreProducts = true;
      int optimal_j;
      int period;
      int sum;
      SqlPipe pipe = SqlContext.GetPipe();
      SqlDataRecord record;
      object[] values = new object[3];
      SqlMetaData[] metadata = new SqlMetaData[3];

      //Initialize algorithm arrays
      Cij = new int[MAXWEEKS][];
      for( int l=0;l<MAXWEEKS;l++)
         Cij[l] = new int[MAXWEEKS];
      Fk = new int[MAXWEEKS];

      //Look up K and h for all products
      SqlCommand cmd = SqlContext.GetCommand();
      cmd.CommandText = @"SELECT pname, InventoryCost, StartupCost from dbo.t_Products ORDER BY PID";
      SqlDataReader reader = cmd.ExecuteReader();
      while(reader.Read())
      {
         productNames[product_count] = reader.GetSqlChars(0); //product name
         h[product_count] = reader.GetInt32(1); //holding cost 
         K[product_count] = reader.GetInt32(2); //startup cost
         product_count++;

         
         // if we exceeded number of expected products then bail out with an exception
         if (product_count >= MAXPRODUCTS)
         {
            throw new Exception("Too many products");
         }
      }
      reader.Close();
      product_count = 0;

      //Get the list of product ids;
      cmd = SqlContext.GetCommand();
      cmd.CommandText = @"select PID, weekdate, DemandQty from dbo.t_SalesForecast ORDER BY PID, WeekDate";
      reader = cmd.ExecuteReader();
      moreProducts=reader.Read();
         
      //Set up the record for returning results
      metadata[0] = new SqlMetaData( "Product", 
SqlDbType.NVarChar,MAXNAME );
      metadata[1] = new SqlMetaData( "Period", SqlDbType.DateTime );
      metadata[2] = new SqlMetaData( "Quantity", SqlDbType.Int );
      record = new SqlDataRecord( metadata );


      while( moreProducts )
      {
         product_id = current_product = reader.GetInt32(0);
         int index = 1;
         quantity = new int[MAXWEEKS];
         while( current_product == product_id )
         {
            week[index] = reader.GetSqlDateTime(1);
            quantity[index] = reader.GetInt32(2);
            index++;

            moreProducts = reader.Read();
            if( !moreProducts )
               break;
            current_product = reader.GetInt32(0);
         }

         //Determine the ordinal start week
         startPeriod = 1;

         //For each product ID calculate Cij
         for( int i = startPeriod; i < (startPeriod + numWeeks); i++ )
         {
            for( int j = i+1; j <= (startPeriod + numWeeks+1); j++ )
            {
               Cij[i][j] = GetCij(quantity,i,j,K [product_count],h[product_count]);
            }
         }

         //Calculate Fk
         for( int k = startPeriod + numWeeks + 1; k >= startPeriod; k--)
         {
            minK[k] = GetFk_SO(k,startPeriod + numWeeks,Cij,Fk);
         }

         //Send the results
         record.SetSqlChars(0,productNames[product_count]);
         pipe.SendResultsStart(record,false);

         for( int k = startPeriod; k < startPeriod + numWeeks; )
         {
            period = k;
            optimal_j = minK[k];
            sum = 0;
            while( k < optimal_j )
            {
               sum = sum + quantity[k++];
            }
            values[1] = week[period];
            record.SetValue(1,values[1]);
            values[2] = sum;
            record.SetValue(2,values[2]);
            pipe.SendResultsRow(record);
         }
         pipe.SendResultsEnd();
         product_count++;
      }
      reader.Close();
      return 0;
   }

   private static int GetCij(int[] quantities, int i, int j, int K, int h)
   {
      if( j == i+1 )
         return K;
      else
         return (j-1-i) * h * quantities[j-1] + GetCij(quantities, i, j-1,K,h);

   }
   private static int GetFk_SO(int k,int n,int[][] Cij, int[] Fk)
   {
      int j,min;
      j = k+1;
      min = j;

      if ( k == n+1 )
      {
         Fk[k] = 0;
         return j;
      }

      Fk[k] = Cij[k][j] + Fk[j];
      for(; k <= n ;k++)
      {
         j = k + 1;
         while( j <= n+1 )
         {
            if( Cij[k][j] + Fk[j] < Fk[k] )
            {
               min = j;
               Fk[k] = Cij[k][j] + Fk[j];
            }

            j++;
         }
      }
      return min;
   }

}

[1] [2] [3] [4] [5] [6] [7] [8] [9] [10] [11] [12] [13]