• Print |
• E-mail  |
• Bookmark

Overview

This sample presents various techniques and patterns that you can use to access SAS through SAS Integration Technologies using the Microsoft .NET Framework and ASP.NET. The functionality in this sample is very similar to the ASP Sample Library.

This sample demonstrates the following SAS technologies:

• SAS Integration Technologies Client (for Windows)
• Stored Processes
• Streaming ODS HTML
• ActiveX Graphs
• ObjectManager
• SAS Object Pooling

This sample demonstrates the following Microsoft .NET concepts and controls:

• ASP.NET
• C#
• Web Forms
• ADO.NET
• ADODB
• DataGrid
• DataList
• Repeater
• Various HTML Controls

Explain the Sample Code

The following sections describe specific tasks that relate to accessing SAS data and analytics from ASP.NET. For a more detailed understanding of the sample, read the code and the comments in the code.

• SAS Connection Pooling
• Accessing SAS Data from ADO.NET
• Accessing Formatted SAS Data using ADODB and ADO.NET
• Using a DataView with DataBound HTML Server Controls
• Displaying ODS and Graphics using Response.Write
• Displaying ODS and Graphics using HTML Server Controls

At the bottom of this page, a list of references is provided so that you can read more about these topics.

SAS Connection Pooling

SAS connection pooling enables you to create a pool of connections to SAS Workspace Servers.  Connection pooling is not a new technology, but starting with the SAS 9 Integration Technologies Client (for Windows) there is a new option to manage the connection pool with the SASObjectManager instead of the WorkspaceManager.  For .NET Framework development, the SASObjectManager should always be used for connection pooling.  For more information about SAS connection pooling, see Using Connection Pooling in the SAS Integration Technologies Developer's Guide.

The following code, which is included in the method CreateDefaultPool() in the file Global.asax.cs, demonstrates the technique that this sample uses in order to create the default ObjectPool. The default ObjectPool creates SAS Workspaces using local COM.  This code can easily be modified to point to an IOM Bridge based workspace server.

//Initialize a default pool
SASObjectManager.ObjectFactory sasOman = 
    new SASObjectManager.ObjectFactory();

ServerDef sasServerDef = new ServerDef();
LoginDef sasLoginDef = new LoginDef();

ObjectPool sasObjectPool;		

string strPoolName = "DefaultPool";

try
{
    //check to be sure the pool isn't already running
    sasObjectPool = sasOman.ObjectPools.Item(strPoolName);
	
    if(sasObjectPool != null)
        return;
}
catch(Exception ex)
{   
    Trace.Write(
       "Pool not created yet, creating pool.  Exception message:  " + 
       ex.Message);
}

try
{
    sasServerDef.MachineDNSName        = "localhost";
    sasServerDef.Protocol = SASObjectManager.Protocols.ProtocolCom;
    sasServerDef.Port                  = 0;
    sasServerDef.MaxPerObjectPool      = 5;
    sasServerDef.RunForever            = false;
    sasServerDef.ShutdownAfter         = 30;
    sasServerDef.RecycleActivationLimit= 30;
	
    sasLoginDef.LoginName              = "";
    sasLoginDef.Password               = "";
    sasLoginDef.MinSize                = 0;
    sasLoginDef.MinAvail               = 0;
    
    sasObjectPool = sasOman.ObjectPools.CreatePoolByServer(strPoolName, 
                                                           sasServerDef, 
                                                           sasLoginDef);

    Trace.Write("ObjectPool created.");
}
catch(Exception ex)
{
    Trace.Write("Exception while creating object pool:  " + ex.Message);
}

After an ObjectPool is created, you must get a PooledObject from the ObjectPool to actually use the workspace.  The method GetSasData() in file SampleUtils.cs contains the following code, which demonstrates how to retrieve a workspace from a pool.  First, an instance of the ObjectPool is created. A PooledObject is then retrieved from the ObjectPool using the GetPooledObject() method. This method returns a generic PooledObject.  The SASObject property of the PooledObject is then cast into a SAS.Workspace object. The workspace is now available for use in your application to access SAS analytics.

SASObjectManager.PooledObject pooledObject = null;
SAS.Workspace sasWorkspace = null;

//Get ObjectPool
ObjectPool sasObjectPool = sasOman.ObjectPools.Item(poolName);

//Get a PooledObject
pooledObject = sasObjectPool.GetPooledObject("", "", 10000);

//Get a SAS Workspace
sasWorkspace = (SAS.Workspace) pooledObject.SASObject;

In order to enable ObjectPooling to work effectively in the Microsoft .NET Framework, the ReturnToPool() method of the PooledObject must be called. This also ensures that the PooledObject is returned to the ObjectPool immediately. Relying on garbage collection to free your PooledObject is inefficient and will result in poor performance of the ObjectPool.  In this sample, ReturnToPool() is always included in a finally clause to ensure that the code is executed even if an exception is encountered.

finally
{
    if((pooledObject != null) && (sasWorkspace != null))
        pooledObject.ReturnToPool()
}

Accessing SAS Data from ADO.NET

It is possible to access SAS Data directly from ADO.NET using the IOM Data Provider.  The following code demonstrates how to retrieve data from SAS using only ADO.NET; ADODB is not required.  The method GetSasData() in SampleUtils.cs retrieves data from SAS based on the input parameters.  This method enables you to re-use code and also provides the flexibility to specify a customized SQL command.

First, a workspace is created and initialized.  In order to access the data from SAS, an OleDbDataAdapter object is created using the standard connection string for the IOM Provider and the SQL command that was passed into CreateSasData().  Calling OleDbDataAdapter.Fill() will execute the SQL command using the IOM Provider, and the results will be loaded into the specified DataSet object, which in this case is sasData.

After the data is successfully retrieved from SAS, or after an exception is thrown, the workspace resources must be cleaned up.  In the finally clause, the PooledObject is returned to the ObjectPool.

ObjectFactory sasOman = new ObjectFactory();

SASObjectManager.PooledObject pooledObject = null;
SAS.Workspace sasWorkspace = null;

try
{
    //Create a SAS Workspace and initialize it. 
    //  --this code was omitted, see SampleUtils.cs for complete code.

    //Get the requested data using ADO.NET
    System.Data.OleDb.OleDbDataAdapter sasDataAdapter = 
        new System.Data.OleDb.OleDbDataAdapter(
            selectCommandText, 
            "provider=sas.iomprovider.1; SAS Workspace ID=" + 
            sasWorkspace.UniqueIdentifier);

    System.Data.DataSet sasData = new System.Data.DataSet();
    sasDataAdapter.Fill(sasData, "sasdata");

    return sasData;
}
catch(Exception ex)
{	
    throw ex;
}
finally
{
    if((pooledObject != null) && (sasWorkspace != null))
    {
        pooledObject.ReturnToPool();
    }
}

Accessing Formatted SAS Data using ADODB and ADO.NET

It isn't possible to access formatted SAS data using the OleDbDataAdapter as described in the previous section. You must use ADODB in order to retrieve formatted SAS data. This is achieved by using the built-in COM Interop assembly for ADODB. This technique for accessing SAS data is demonstrated in the method GetFormattedSasData() in the file SampleUtils.cs.

The code to retrieve a workspace from the ObjectPool is the same as in the previous section.  The differences here are in the way the data is retrieved using ADODB instead of using the OleDbDataAdapter.

First, the required ADODB objects are created.  The adoConnection object is opened using the default connection string for the IOM Provider.  The adoCommand object is then configured to use the new connection and the SQL command that was passed into GetFormattedSasData().  The custom Recordset property "SAS Formats" is set to "_ALL_" to enable formatting of all columns with their default SAS format.  The adoRecordset is then opened using the adoCommand object.

In order to use this data for data binding in an ASP.NET application, the data must be in an ADO.NET DataSet.  In order to move data from an ADODB.Recordset object into an ADO.NET DataSet, the OleDbDataAdapter provides another Fill method which will fill a DataSet with the data contained in a Recordset.

This technique is a bit slower than using ADO.NET party because the SAS server has to apply the formats to the data.  Also, more speed and memory are consumed because two objects have to be created in memory to represent the same data.

For this reason, it is recommended that you avoid using ADODB unless SAS formats are necessary.  If you are dealing with formatted SAS data that takes some time to retrieve, then you might also need to look into caching this data in order to enhance performance of your application.  For an example of using the built in Cache object, see the method GetTables() in ViewSasData.aspx.cs.

//Get the formatted data using ADODB...
//  Then put the ADODB RecordSet into ADO.NET
ADODB.Connection adoConnection = new ADODB.ConnectionClass();
ADODB.Recordset adoRecordset = new ADODB.Recordset();
ADODB.Command adoCommand = new ADODB.CommandClass();

adoConnection.Open("provider=sas.iomprovider.1; SAS Workspace ID=" + 
    sasWorkspace.UniqueIdentifier, "", "", 0);

adoCommand.ActiveConnection = adoConnection;
adoCommand.CommandText = selectCommandText;
adoCommand.Properties["SAS Formats"].Value = "_ALL_";
adoCommand.CommandType = ADODB.CommandTypeEnum.adCmdText;

adoRecordset.Open(adoCommand, System.Reflection.Missing.Value, 
    ADODB.CursorTypeEnum.adOpenDynamic, ADODB.LockTypeEnum.adLockReadOnly, 1);

//Fill an ADO.NET DataSet using the ADODB Recordset
System.Data.OleDb.OleDbDataAdapter sasDataAdapter = 
    new System.Data.OleDb.OleDbDataAdapter();
    
System.Data.DataSet sasData = new System.Data.DataSet();
sasDataAdapter.Fill(sasData, adoRecordset, "sasdata");

Using a DataView with DataBound HTML Server Controls

In several of the Web Forms in this sample, an ADO.NET DataView is used as the DataSource for an HTML server control.  Using a DataView enables filtering and ordering of the data that is returned by SAS.  Using a DataView also makes it easier to bind the control to the DataSource.  Because there is only one DataTable associated with a DataView, the DataMember property of the control does not need to be set when using a DataView as the DataSource.

The method refreshTickerList() in the file ManageStockData.aspx.cs uses SAS data to populate the list of available stock tickers in an HtmlSelect control.  You convert the <select> element to an HTML server control by adding the runat="server" attribute to the select tag.

In the following code that populates the HtmlSelect controls with SAS data, a DataSet is created using the helper method SampleUtils.GetSasData().  This method returns a DataSet containing the data that was generated by the specified SQL query.  In this case it returns the list of tables in the STOCKS library, which for the purpose of this sample is the list of stock tickers.  Then a DataView of this data is created using the DataTable named sasdata.

For each control, the DataSource is set to the new DataView and the DataTextField is set to the column containing the name of the stock ticker.  Finally, the control is bound to the data by calling the DataBind() method.  At this point, each HtmlSelect control is now populated with new values that were retrieved from SAS.

private void refreshTickerList()
{
    try
    {
        // Get the list of stocks using an SQL query
        System.Data.DataSet sasData = SampleUtils.GetSasData(
            "select * from sashelp.vmember " + 
            "where libname='STOCKS' and memtype='DATA'",
            (string) Session["CurrentPool"],
            (string)Application[(string)Session["CurrentPool"]]);

        // Create a DataView of the list of stocks to simplify binding.
        System.Data.DataView stockList = 
            new DataView(sasData.Tables["sasdata"]);

        // Bind each control to the DataView.
        this.cmbProcCorr.DataSource = stockList;
        this.cmbProcCorr.DataTextField = "memname";
        this.cmbProcCorr.DataBind();

        this.cmbStockHistory.DataSource = stockList;
        this.cmbStockHistory.DataTextField = "memname";
        this.cmbStockHistory.DataBind();

        this.cmbUpdateStockData.DataSource = stockList;
        this.cmbUpdateStockData.DataTextField = "memname";
        this.cmbUpdateStockData.DataBind();
    }
    catch(Exception ex)
    {
        Trace.Write("Exception getting list of stocks to display: " + 
		    ex.Message);
    }
}

It is also possible to create a custom DataTable at runtime.  This is useful if you would like to bind a control to data that isn't retrieved using ADODB or ADO.NET.  In ASL.NET this technique is used to create a DataTable containing a list of:

• librefs that are assigned on the server (GetLibrefs() in SampleUtils.cs),
• column names for a specified SAS table (GetColumns() in SamleUtils.cs),
• connection pools that are currently available (CreatePoolListDataSource() in SampleUtils.cs),
• server processes that are currently available for a selected connection pool (DisplayPoolInfo() in PoolDetail.aspx.cs)

For more details on the implementation of a custom DataTable, read the code in the source files as previously specified.

Displaying ODS and Graphics using Response.Write

SAS/Graph is a product that can create ActiveX, Java Applet, GIF, and JPEG based charts and graphs.  Combined with ODS, it is possible for SAS to generate HTML with embedded interactive charts and graphs, which can then be displayed in your Web application.  The actual implementation is very similar to what was done in the ASP Sample Library .  The main difference is the way the HTML created by SAS is rendered in ASP.NET.

Given that the Response object is available to a Web Form, it is possible to call Response.Write() to write out the HTML to the Response object.  This will render the HTML to the user in much the same way it was done in the ASP Sample Library using VBScript.  The method Page_Load() in DisplayStockHistory.aspx.cs demonstrates this technique.

In the following code, the SAS Stored Process graphStockHistory.sas is executed with the generated parameter string.  A SAS.BinaryStream object is then created to stream the ODS back from SAS using the Fileref object sasHtmlRef.  The binary data is streamed into a byte array using the Scripto StreamHelper class.  In order to convert the byte array into a string object the method System.Text.Encoding.UTF8.GetString() is called.  This method takes the UTF8 encoded byte array and converts it into a string object.  This step works because the HTML generated by SAS is encoded as UTF8, which is specified in the ODS statement option ENCODING="utf-8."  The HTML is then rendered to the Web browser by calling Response.Write().

For more details on the implementation of this Web Form, consult the actual source files, which are DisplayStockHistory.aspx and DisplayStockHistory.aspx.cs.

// Execute the stored process
sasWorkspace.LanguageService.StoredProcessService.Execute(
    "graphStockHistory.sas", strParams.ToString());

//now we will stream back the html file containing 
//  ActiveX control and write it to the response
SCRIPTOLib.StreamHelper sasStreamHelper = new SCRIPTOLib.StreamHelper();
SAS.BinaryStream sasBinaryStream;
sasBinaryStream = sasHtmlRef.OpenBinaryStream(SAS.StreamOpenMode.StreamOpenModeForReading);
Response.WriteFile("header.html");
byte[] byteGraph = (byte[]) sasStreamHelper.ReadBinaryArray(sasBinaryStream, 0);
sasBinaryStream.Close();

string strGraph = System.Text.Encoding.UTF8.GetString(byteGraph);
Response.Write(strGraph);

Displaying ODS and Graphics using HTML Server Controls

Using the Response object to write out HTML does not adhere to the Web Forms programming paradigm.  When working with Web Forms, using Reponse.Write is discouraged.  In order to take full advantage of Web Forms, you need to display the ODS HTML by using the built-in HTML and Web controls provided by ASP.NET.

One way to do this is to write your ODS to a <span> element that is configured to run as a server control.  This enables you use your code in order to programmatically set the HTML contained in the span element.  This gives you better control over the placement of the ODS in your Web Form than you would get using Response.Write().

The Web Form BuildActiveXChart.aspx provides a good example of using HTML controls to display ODS output.  In BuildActiveXChart.aspx, a span element is added with the runat="server" attribute.  The attribute id="spnActiveXChart" indicates the ID that will be used to access this server control programatically.

The majority of the code that is used to display the ODS in an HTML server control is very similar to using Response.Write.  The data is streamed back into a byte array and converted into a string object.  Instead of writing the HTML to the Response object, the InnerHtml property of the span element, spnActiveXChart, is set to the downloaded ODS HTML string.  If no data is returned, then an error message is displayed.

// Execute the stored process
sasWorkspace.LanguageService.StoredProcessService.Execute(
    "buildActivexPieChart.sas", strParams.ToString());

// Stream back the html file containing ActiveX control and write it to the response
SCRIPTOLib.StreamHelper sasStreamHelper = new SCRIPTOLib.StreamHelper();
SAS.BinaryStream sasBinaryStream;
sasBinaryStream = sasHtmlRef.OpenBinaryStream(SAS.StreamOpenMode.StreamOpenModeForReading);
byte[] pieChart = (byte[]) sasStreamHelper.ReadBinaryArray(sasBinaryStream, 0);
sasBinaryStream.Close();

//The encoding of the HTML is specified as UTF8 in the ODS code
string strPieChart = System.Text.Encoding.UTF8.GetString(pieChart);

if(strPieChart.Length == 0)
    spnActiveXChart.InnerHtml = 
        "<h5>There was an error creating the ActiveX Pie Chart.  " +
        "No data was returned by the stored process.</h5>";
else
    spnActiveXChart.InnerHtml = strPieChart;

Further Reading

• Developer's Guide: Windows Client
• SAS 9.1 Data Providers ADO/OLE DB Cookbook
• Microsoft Developer Network
• Official Microsoft ASP.NET Site

Install and Run the Sample

This document explains:

• software requirements for running this sample
• the procedures for downloading and installing the sample
• procedures for running the sample.

Software Requirements

To run this sample, you must have the following software installed on your computer:

• SAS Integration Technologies Client (for Windows). You can download this software from the SAS Integration Technologies site. Select SAS Integration Technologies Client for Windows from the list of components.

• Internet Explorer 5 or later is recommended in order to view the ActiveX Graph controls.

• Windows 2000, Windows XP, or Windows Server 2003 with IIS Installed.

• Microsoft .NET Framework SDK 1.1.
  
  See Using .NET Framework for more information about downloading, installing, and using the .NET Framework SDK.

• Visual Studio .NET 2003 (optional).
  
  If you plan to modify or recompile the sample, the you must have Visual Studio .NET 2003 installed. This sample was written with Visual Studio .NET 2003, so the project file will not work with earlier versions of Visual Studio .NET.

To understand the content of this sample it will help to have a working knowledge of HTML, C#, ASP.NET, SAS Integrated Object Model, ADO.NET, and ADODB.

Using the Microsoft .NET Framework

Downloading and Installing the Microsoft .NET Framework SDK

To install and run ASP.NET Sample Library, which is an ASP.NET application, you will need to install the Microsoft .NET Framework Software Development Kit (SDK). The .NET Framework SDK includes the .NET Framework, as well as everything you need to write, build, test, and deploy .NET Framework applications.

To learn more about the .NET Framework and how to download and install the SDK, see the Microsoft .NET Development site.

The .NET Framework SDK is a free download and can be installed on Windows 2000, Windows Server 2003, and Windows XP. In order to run ASP.NET applications, you must have Internet Information Services (IIS) installed on your server before you install the .NET Framework.

Configuration Steps Necessary

After you have the .NET Framework installed and you are able to run ASP.NET applications, you will need to configure SAS so that you can create workspace pools and use them from ASP.NET.

In the Windows 2000 and Windows XP environments, ASP.NET applications run as the local user account ASPNET. In the Windows Server 2003 environment, ASP.NET applications run under the NETWORK SERVICE user account. In order for you to create a workspace on your local COM IOM server, you will first need to configure DCOM to grant the appropriate users permission to launch and access the server.

If you are unfamiliar with configuring DCOM applications, then you can consult the SAS Integration Technologies Library for more information.

In order to configure access and launch permissions for a SAS 9 server, see Setting SAS Permissions on the Server (COM/DCOM). You will need to grant access to the user account that is appropriate for your platform, as mentioned previously. If you are configuring access and launch permissions for a SAS server in release 8.2, then you can follow the directions for SAS 9. The name of the DCOM server for release 8.2 will be SAS: IOM DCOM Servers instead of SAS.Workspace (SAS Version 9.1).

You should also ensure that the ASPNET or NETWORK SERVICE user has the appropriate file permissions for the SAS installation directory in addition to the temporary and work directories. If the user does not have required file permissions, then you will likely see "Server Execution Failed" error messages.

If you will be accessing a remote IOM server, it is recommended that you use the IOM Bridge protocol instead of DCOM. With the IOM Bridge protocol, you will not have to deal with the additional configuration steps and security issues related to DCOM. You will have to setup and configure an ObjectSpawner on your remote server. For more information, see Setting up an IOM Bridge Connection.

Install the Sample

To install this sample:

1. Download the ASL.NET package to your computer. Extract the contents of this ZIP file to a temporary location on your system. Go to the location where you extracted asl.net.zip, and execute setup.exe. The file ASL.NETSetup.msi is a Windows Installer Package that contains all the required files and DLL's for the ASP.NET Sample Library.

   The installer will create a new ASP.NET application on your IIS server. If you select the defaults that the installer gives, then the new application will be called ASL.NET. You can access the new application at the following default URL: http://localhost/ASL.NET/.

   After installation, you will have the following files on your computer in the virtual directory that you specified during installation:

   Web Forms to Administer Pooling

   • MakePool.aspx — creates a new pool
   • Pool.aspx — displays information about the current pool
   • PoolDetail.aspx — displays detailed information about a pool
   • PoolParams.aspx — creates a new pool
   • UsePool.aspx — enables selection of the active pool when executing Web Forms

   Web Forms and Classes that Utilize SAS

   • BuildActiveXChart.aspx — displays a dynamic ActiveX chart
   • DisplayProcCorr.aspx — displays ODS output from PROC CORR
   • DisplayStockHistory.aspx — displays an ActiveX chart of stock history
   • GetStockData.aspx — downloads historical stock data
   • ManageStockData.aspx — provides an interface to run various stock related Web Forms
   • RunMeans.aspx — displays SAS list output
   • SampleUtils.cs — defines a utility class with various data access methods
   • UpdateStockData.aspx — updates data for multiple stock tickers
   • ViewSasData.aspx — displays SAS tables from any library
   • Global.asax — configures application and session objects

   SAS Stored Processes

   • buildActivexPieChart.sas — creates an ActiveX pie chart
   • corrStocks.sas — runs PROC CORR against stock data and writes output to ODS
   • getStockData.sas — downloads new stock data from the Web
   • graphStockHistory.sas — creates an ActiveX graph of historical stock prices
   • init.sas — initializes the stock library

   Additional Files

   • ASL.NET.csproj — specifies the Visual Studio.NET 2003 project file
   • ASLDoc.html — specifies the ASL.NET documentation
   • Default.aspx — specifies the default entry page to this sample
   • header.html — specifies the default header for all pages
   • license.txt — specifies the license information for this sample
   • pool.css — specifies the style sheet for this sample
   • Web.config — configures the ASP.NET application



2. To enable downloading stock data from the internet, you will need to modify the file getStockData.sas. This file can be found in the AspSampleFiles\Repository folder in the virtual directory that was created by the installer.

   The FILENAME statement needs to be updated with the correct proxy server name. If you do not have a proxy server, then just delete the proxy= option completely.

   You will also need to enable write permission for the Data folder for the user IDs that the SAS Workspace Server will run under. The Data folder can be found in the AspSampleFiles\Repository folder of the virtual directory.

3. When the ASP.NET application initializes, a default pool of local COM SAS Workspace Servers is created. If your system isn't configured properly, then these workspace servers will not launch for ASP.NET. See Using the Microsoft .NET Framework for more information about configuring local COM for ASP.NET.

4. If you will be running against a the workspace server on a different machine than where you installed the sample, then you will need to copy the folder AspSampleFiles\Repository and all its contents to the local file system of your Workspace server. When you create the pool, you must specify the location of the Repository folder on the workspace server file system.

5. If you want to modify the default workspace pool that is created during application startup, then you will need to open up the project ASL.NET.csproj in VisualStudio .NET 2003. Modify the CreateDefaultPool method in the file Global.asax.cs to create a pool according to the specificationss of your workspace server. Be sure to rebuild your project after you make these necessary changes.

   In a Web based distributed environment, it is recommended that you use an Object Spawner to launch IOM Bridge based workspace servers.

Run the Sample

To begin using the sample, open a Web browser and navigate to the URL http://localhost/ASL.NET. If you are accessing the sample remotely, then replace localhost with the machine name of your Web server. You should be greeted by the default page for the sample with the available tasks listed on the left.

• Select Create a New Pool in order to create a new pool of workspaces.

• Select View All Pools in order to view the list of pools currently available. This should include the default pool created during application startup. When you view the details of a workspace pool, you will also be able to shutdown or destroy the pool.

• Select Use Pools to start using a workspace pool to access SAS analytics and data from ASP.NET.

For additional information about the implementation of this sample, see the Explain the Code section on the Details tab.

Operating System and Release Information