storedprocedures.cs

< SQL Server2005程序设计>

using System;
using System.Data;
using Microsoft.AnalysisServices.AdomdServer;

namespace Chapter20ServerAssembly
{
    public class StoredProcedures
    {
        public DataTable GetData()
        {
            DataTable dt = new System.Data.DataTable();
            dt.Columns.Add("FirstName", typeof(string));
            dt.Columns.Add("LastName", typeof(string));
            dt.Columns.Add("Phone", typeof(string));
            dt.Columns.Add("ProbNotCarOwner", typeof(double));

            object[] row = new object[4];

            AdomdCommand comContext = new AdomdCommand("SELECT TOP 250 t.FirstName, t.LastName, t.Phone, PredictProbability([Is Car Owner],'N') AS ProbNotCarOwner FROM	CarOwner_DT PREDICTION JOIN OPENQUERY([Adventure Works DW], 'SELECT c.FirstName,c.LastName,tc.* FROM vCustomerProfitability tc INNER JOIN dimCustomer c ON tc.CustomerKey = c.CustomerKey') AS t ON t.Gender = CarOwner_DT.Gender AND t.IncomeGroup = CarOwner_dt.[Income Group] AND t.MaritalStatus = CarOwner_dt.[Marital Status] AND t.Region = CarOwner_dt.Region ORDER BY PredictProbability([Is Car Owner],'N') DESC");
            try
            {
                AdomdDataReader dr = comContext.ExecuteReader();
                while (dr.Read())
                {
                    row[0] = dr[0];
                    row[1] = dr[1];
                    row[2] = dr[2];
                    row[3] = dr[3];
                    dt.Rows.Add(row);
                }
                dr.Close();
                return dt;
            }
            catch
            {
                return null;
            }
        }
    }
}