storedprocedures.cs

来自「< SQL Server2005程序设计>」· CS 代码 · 共 300 行

#region Using directives

using System;
using System.Collections.Generic;
using System.Text;
using System.Data.SqlClient;
using Microsoft.AnalysisServices;
using Microsoft.AnalysisServices.AdomdServer;

#endregion

namespace Chapter19AdomdServer
{
    public class StoredProcedures
    {
        public void CreateNewDB()
        {
            Server s = new Server();
            s.Connect("Data Source=" + Context.CurrentServerID + ";Initial Catalog=" + Context.CurrentDatabaseName);
            //s.Connect("*");
            Database dbNew = new Database("NewDatabase");
            s.Databases.Add(dbNew);
            dbNew.Update();
            s.Disconnect();
        }

        public AdomdDataReader GetData() // Only DMX (not MDX) queries are permitted; Will always flow to catch block
        {
            AdomdCommand comContext = new AdomdCommand("SELECT Shippers.[Shipper Name].MEMBERS ON COLUMNS, Time.Year.MEMBERS ON ROWS FROM	Sales WHERE	Measures.[Total Sales]");
            try
            {
                return comContext.ExecuteReader();
            }
            catch
            {
                return null;
            }
        }

        public System.Data.DataTable GetDataTable()
        {
            System.Data.DataTable dt = new System.Data.DataTable();
            dt.Columns.Add("ID", typeof(int));
            dt.Columns.Add("Name", typeof(string));

            object[] row = new object[2];
            
            row[0] = 1;
            row[1] = "Andrew";
            dt.Rows.Add(row);
            
            row[0] = 2;
            row[1] = "Steve";
            dt.Rows.Add(row);
            
            return dt;
        }

        public Microsoft.AnalysisServices.AdomdClient.AdomdDataReader GetDataClient()
        {
            Microsoft.AnalysisServices.AdomdClient.AdomdConnection conContext = new Microsoft.AnalysisServices.AdomdClient.AdomdConnection("data source=" + Context.CurrentServerID + ";initial catalog=" + Context.CurrentDatabaseName);
            Microsoft.AnalysisServices.AdomdClient.AdomdCommand comContext = new Microsoft.AnalysisServices.AdomdClient.AdomdCommand("SELECT Shippers.[Shipper Name].MEMBERS ON COLUMNS, Time.Year.MEMBERS ON ROWS FROM Sales WHERE Measures.[Total Sales]", conContext);
            conContext.Open();
            try
            {
	            return comContext.ExecuteReader();
            }
            catch
            {
		        return null;
	        }
        }

        public Set RemoveLast(Set set)
		{
			SetBuilder sb = new SetBuilder();

			for (int i = 0; i < set.Tuples.Count - 1; i++)
			{
			        sb.Add(set.Tuples[i]);
			}
			
			return sb.ToSet();
		}

        // Taken from Mosha Posumansky's blog: http://sqljunkies.com/WebLog/mosha
        // Permalink: http://sqljunkies.com/WebLog/mosha/archive/2005/03/31/9920.aspx
        public Set Sample(Set set, int k)
        {
            int n = set.Tuples.Count;
            if (k >= n)
                return set;

            System.Random r = new System.Random();

            SetBuilder sb = new SetBuilder();
            int i = n;
            for (int ii = 0; ii < set.Tuples.Count; ii++)
            {
                int rnd = r.Next(i);
                if (rnd < k)
                {
                    k--;
                    sb.Add(set.Tuples[ii]);
                }
                i--;
            }
            return sb.ToSet();
        }
        
        /// <summary>
        /// Return the minimum of two numbers
        /// </summary>
        /// <param name="x"></param>
        /// <param name="y"></param>
        /// <returns></returns>
        public float Minimum(float x, float y)
        {
            if (x < y)
                return x;
            else
                return y;
        }

        /// <summary>
        /// Return the maximum of two numbers
        /// </summary>
        /// <param name="x"></param>
        /// <param name="y"></param>
        /// <returns></returns>
        public float Maximum(float x, float y)
        {
            if (x > y)
                return x;
            else
                return y;
        }

        /// <summary>
        /// Filter a set of members by an expression
        /// </summary>
        /// <param name="set"></param>
        /// <param name="filterExpression"></param>
        /// <returns></returns>
        public Set FilterSet(Set set, string filterExpression)
        {
            Expression expr = new Expression(filterExpression);

            SetBuilder resultSetBuilder = new SetBuilder();
            

            foreach (Tuple tuple in set)
            {
                if ((bool)(expr.Calculate(tuple)))
                    resultSetBuilder.Add(tuple);
            }

            return resultSetBuilder.ToSet();
        }

        /// <summary>
        /// Calculate the weighted average of a set
        /// </summary>
        /// <param name="set"></param>
        /// <param name="weightExpression"></param>
        /// <param name="inputExpression"></param>
        /// <returns></returns>
        public float WeightedAverage(Set set, string weightExpression, string inputExpression)
        {
            Expression weightExpr = new Expression(weightExpression);
            Expression inputExpr = new Expression(inputExpression);

            float total = 0;
            float totalWeight = 0;

            foreach (Tuple tuple in set)
            {
                float expression = (float)inputExpr.Calculate(tuple);
                float weight = (float)weightExpr.Calculate(tuple);

                total += expression * weight;
                totalWeight += weight;
            }

            if (totalWeight > 0)
                return total / totalWeight;
            else
                return 0;
        }

        /// <summary>
        /// Check the current state of the data warehouse and create partitions if necessary
        /// </summary>
        public void CreatePartitions()
        {
            #region Retrieve order date of last sales transaction

            // Open a connection to the data warehouse
            SqlConnection conn = new SqlConnection("data source=localhost\\yukon;initial catalog=AdventureWorksDW;Integrated Security=SSPI"); //TODO: Properties.Settings.Default.AdventureWorksDW
            conn.Open();

            // Create a command
            SqlCommand cmd = new SqlCommand();
            cmd.Connection = conn;

            // Get the order date key of the last internet sale
            int lastInternetSaleDateKey = 0;
            cmd.CommandText = "select max(OrderDateKey) from FactInternetSales";
            lastInternetSaleDateKey = (int)cmd.ExecuteScalar();

            // Get the order date key of the last reseller sale
            int lastResellerSaleDateKey = 0;
            cmd.CommandText = "select max(OrderDateKey) from FactResellerSales";
            lastResellerSaleDateKey = (int)cmd.ExecuteScalar();

            #endregion

            #region Create partitions

            // Connect to the calling session
            Server svr = new Server();
            svr.Connect("*");

            // Get the Adventure Works cube
            Database db = svr.Databases.GetByName(Context.CurrentDatabaseName);
            Cube cube = db.Cubes[0];

            MeasureGroup mg;
            int maxOrderDateKey;

            #region Internet Sales measure group

            mg = cube.MeasureGroups.GetByName("Internet Sales");
            maxOrderDateKey = 0;
            foreach (Partition part in mg.Partitions)
            {
                maxOrderDateKey = Math.Max(maxOrderDateKey, Convert.ToInt32(part.Annotations.Find("LastOrderDateKey").Value.Value));
            }

            if (maxOrderDateKey < lastInternetSaleDateKey)
            {
                Partition part = mg.Partitions.Add("Internet_Sales_" + lastInternetSaleDateKey);
                part.StorageMode = StorageMode.Molap;
                part.Source = new QueryBinding(db.DataSources[0].ID, "SELECT * FROM [dbo].[FactInternetSales] WHERE OrderDateKey > '" + maxOrderDateKey + "' and OrderDateKey <= '" + lastInternetSaleDateKey + "'");
                part.Annotations.Add("LastOrderDateKey", Convert.ToString(lastInternetSaleDateKey));
                part.Update();
                part.Process();
            }

            #endregion

            #region Reseller Sales measure group

            mg = cube.MeasureGroups.GetByName("Reseller Sales");
            maxOrderDateKey = 0;
            foreach (Partition part in mg.Partitions)
            {
                maxOrderDateKey = Math.Max(maxOrderDateKey, Convert.ToInt32(part.Annotations.Find("LastOrderDateKey").Value.Value));
            }

            if (maxOrderDateKey < lastResellerSaleDateKey)
            {
                Partition part = mg.Partitions.Add("Reseller_Sales_" + lastResellerSaleDateKey);
                part.StorageMode = StorageMode.Molap;
                part.Source = new QueryBinding(db.DataSources[0].ID, "SELECT * FROM [dbo].[FactResellerSales] WHERE OrderDateKey > '" + maxOrderDateKey + "' and OrderDateKey <= '" + lastResellerSaleDateKey + "'");
                part.Annotations.Add("LastOrderDateKey", Convert.ToString(lastResellerSaleDateKey));
                part.Update();
                part.Process();
            }

            #endregion

            #region Customers measure group

            mg = cube.MeasureGroups.GetByName("Customers");
            maxOrderDateKey = 0;
            foreach (Partition part in mg.Partitions)
            {
                maxOrderDateKey = Math.Max(maxOrderDateKey, Convert.ToInt32(part.Annotations.Find("LastOrderDateKey").Value.Value));
            }

            if (maxOrderDateKey < lastInternetSaleDateKey)
            {
                Partition part = mg.Partitions.Add("Customers_" + lastInternetSaleDateKey);
                part.StorageMode = StorageMode.Molap;
                part.Source = new QueryBinding(db.DataSources[0].ID, "SELECT * FROM [dbo].[FactInternetSales] WHERE OrderDateKey > '" + maxOrderDateKey + "' and OrderDateKey <= '" + lastInternetSaleDateKey + "'");
                part.Annotations.Add("LastOrderDateKey", Convert.ToString(lastInternetSaleDateKey));
                part.Update();
                part.Process();
            }

            #endregion

            svr.Disconnect();

            #endregion
        }
    }
}