//例子
static DataSourceView CreateDataSourceView(Database db, string strDataSourceName)
{
// Create the data source view
DataSourceView dsv = db.DataSourceViews.FindByName(strDataSourceName);
if (dsv != null)
{
dsv.Drop();
}
dsv = db.DataSourceViews.Add(strDataSourceName, strDataSourceName);
dsv.DataSourceID = strDataSourceName;
dsv.Schema = new DataSet();
dsv.Schema.Locale = CultureInfo.CurrentCulture;
// Open a connection to the data source
OleDbConnection connection = new OleDbConnection(dsv.DataSource.ConnectionString);
connection.Open();
#region Create tables
// Add the DimTime table
AddTable(dsv, connection, "DimTime");
AddComputedColumn(dsv, connection, "DimTime", "SimpleDate", "DATENAME(mm, FullDateAlternateKey) + ' ' + DATENAME(dd, FullDateAlternateKey) + ',' + ' ' + DATENAME(yy, FullDateAlternateKey)");
AddComputedColumn(dsv, connection, "DimTime", "CalendarYearDesc", "'CY' + ' ' + CalendarYear");
AddComputedColumn(dsv, connection, "DimTime", "CalendarSemesterDesc", "CASE WHEN CalendarSemester = 1 THEN 'H1'+' '+ 'CY' +' '+ CONVERT(CHAR (4), CalendarYear) ELSE 'H2'+' '+ 'CY' +' '+ CONVERT(CHAR (4), CalendarYear) END");
AddComputedColumn(dsv, connection, "DimTime", "CalendarQuarterDesc", "'Q' + CONVERT(CHAR (1), CalendarQuarter) +' '+ 'CY' +' '+ CONVERT(CHAR (4), CalendarYear)");
AddComputedColumn(dsv, connection, "DimTime", "MonthName", "EnglishMonthName+' '+ CONVERT(CHAR (4), CalendarYear)");
AddComputedColumn(dsv, connection, "DimTime", "FiscalYearDesc", "'FY' + ' ' + FiscalYear");
AddComputedColumn(dsv, connection, "DimTime", "FiscalSemesterDesc", "CASE WHEN FiscalSemester = 1 THEN 'H1'+' '+ 'FY' +' '+ CONVERT(CHAR (4), FiscalYear) ELSE 'H2'+' '+ 'FY' +' '+ CONVERT(CHAR (4), FiscalYear) END");
AddComputedColumn(dsv, connection, "DimTime", "FiscalQuarterDesc", "'Q' + CONVERT(CHAR (1), FiscalQuarter) +' '+ 'FY' +' '+ CONVERT(CHAR (4), FiscalYear)");
AddComputedColumn(dsv, connection, "DimTime", "FiscalMonthNumberOfYear", "CASE WHEN MonthNumberOfYear = '1' THEN CONVERT(int,'7') WHEN MonthNumberOfYear = '2' THEN CONVERT(int,'8') WHEN MonthNumberOfYear = '3' THEN CONVERT(int,'9') WHEN MonthNumberOfYear = '4' THEN CONVERT(int,'10') WHEN MonthNumberOfYear = '5' THEN CONVERT(int,'11') WHEN MonthNumberOfYear = '6' THEN CONVERT(int,'12') WHEN MonthNumberOfYear = '7' THEN CONVERT(int,'1') WHEN MonthNumberOfYear = '8' THEN CONVERT(int,'2') WHEN MonthNumberOfYear = '9' THEN CONVERT(int,'3') WHEN MonthNumberOfYear = '10' THEN CONVERT(int,'4') WHEN MonthNumberOfYear = '11' THEN CONVERT(int,'5') WHEN MonthNumberOfYear = '12' THEN CONVERT(int,'6') END");
dsv.Update();
// Add the DimGeography table
AddTable(dsv, connection, "DimGeography");
// Add the DimProductCategory table
AddTable(dsv, connection, "DimProductCategory");
// Add the DimProductSubcategory table
AddTable(dsv, connection, "DimProductSubcategory");
AddRelation(dsv, "DimProductSubcategory", "ProductCategoryKey", "DimProductCategory", "ProductCategoryKey");
// Add the DimProduct table
AddTable(dsv, connection, "DimProduct");
AddComputedColumn(dsv, connection, "DimProduct", "ProductLineName", "CASE ProductLine WHEN 'M' THEN 'Mountain' WHEN 'R' THEN 'Road' WHEN 'S' THEN 'Accessory' WHEN 'T' THEN 'Touring' ELSE 'Components' END");
AddRelation(dsv, "DimProduct", "ProductSubcategoryKey", "DimProductSubcategory", "ProductSubcategoryKey");
dsv.Update();
// Add the DimCustomer table
AddTable(dsv, connection, "DimCustomer");
AddComputedColumn(dsv, connection, "DimCustomer", "FullName", "CASE WHEN MiddleName IS NULL THEN FirstName + ' ' + LastName ELSE FirstName + ' ' + MiddleName + ' ' + LastName END");
AddComputedColumn(dsv, connection, "DimCustomer", "GenderDesc", "CASE WHEN Gender = 'M' THEN 'Male' ELSE 'Female' END");
AddComputedColumn(dsv, connection, "DimCustomer", "MaritalStatusDesc", "CASE WHEN MaritalStatus = 'S' THEN 'Single' ELSE 'Married' END");
AddRelation(dsv, "DimCustomer", "GeographyKey", "DimGeography", "GeographyKey");
// Add the DimReseller table
AddTable(dsv, connection, "DimReseller");
AddComputedColumn(dsv, connection, "DimReseller", "OrderFrequencyDesc", "CASE WHEN OrderFrequency = 'A' THEN 'Annual' WHEN OrderFrequency = 'S' THEN 'Bi-Annual' ELSE 'Quarterly' END");
AddComputedColumn(dsv, connection, "DimReseller", "OrderMonthDesc", "CASE WHEN OrderMonth = '1' THEN 'January' WHEN OrderMonth = '2' THEN 'February' WHEN OrderMonth = '3' THEN 'March' WHEN OrderMonth = '4' THEN 'April' WHEN OrderMonth = '5' THEN 'May' WHEN OrderMonth = '6' THEN 'June' WHEN OrderMonth = '7' THEN 'July' WHEN OrderMonth = '8' THEN 'August' WHEN OrderMonth = '9' THEN 'September' WHEN OrderMonth = '10' THEN 'October' WHEN OrderMonth = '11' THEN 'November' WHEN OrderMonth = '12' THEN 'December' ELSE 'Never Ordered' END");
// Add the DimCurrency table
AddTable(dsv, connection, "DimCurrency");
dsv.Update();
// Add the DimSalesReason table
AddTable(dsv, connection, "DimSalesReason");
// Add the FactInternetSales table
AddTable(dsv, connection, "FactInternetSales");
AddRelation(dsv, "FactInternetSales", "ProductKey", "DimProduct", "ProductKey");
AddRelation(dsv, "FactInternetSales", "CustomerKey", "DimCustomer", "CustomerKey");
AddRelation(dsv, "FactInternetSales", "OrderDateKey", "DimTime", "TimeKey");
AddRelation(dsv, "FactInternetSales", "ShipDateKey", "DimTime", "TimeKey");
AddRelation(dsv, "FactInternetSales", "DueDateKey", "DimTime", "TimeKey");
AddRelation(dsv, "FactInternetSales", "CurrencyKey", "DimCurrency", "CurrencyKey");
dsv.Update();
// Add the FactResellerSales table
AddTable(dsv, connection, "FactResellerSales");
AddRelation(dsv, "FactResellerSales", "ProductKey", "DimProduct", "ProductKey");
AddRelation(dsv, "FactResellerSales", "ResellerKey", "DimReseller", "ResellerKey");
AddRelation(dsv, "FactResellerSales", "OrderDateKey", "DimTime", "TimeKey");
AddRelation(dsv, "FactResellerSales", "ShipDateKey", "DimTime", "TimeKey");
AddRelation(dsv, "FactResellerSales", "DueDateKey", "DimTime", "TimeKey");
AddRelation(dsv, "FactResellerSales", "CurrencyKey", "DimCurrency", "CurrencyKey");
// Add the FactInternetSalesReason table
AddTable(dsv, connection, "FactInternetSalesReason");
AddCompositeRelation(dsv, "FactInternetSalesReason", "FactInternetSales", "SalesOrderNumber", "SalesOrderLineNumber");
dsv.Update();
// Add the FactCurrencyRate table
AddTable(dsv, connection, "FactCurrencyRate");
AddRelation(dsv, "FactCurrencyRate", "CurrencyKey", "DimCurrency", "CurrencyKey");
AddRelation(dsv, "FactCurrencyRate", "TimeKey", "DimTime", "TimeKey");
#endregion
// Send the data source view definition to the server
dsv.Update();
return(dsv);
}
