Desktop productivity for business analysts and programmers

sas data viewer custom task format issue

Accepted Solution Solved
Reply
Contributor
Posts: 26
Accepted Solution

sas data viewer custom task format issue

I'm making my owner sas data viewer in a custom task(C#), and the format need to be applied.

I refered to "SAS® 9.4 Providers for OLE DB Cookbook" and "Sample 26145: Visual Studio 2005 Visual Basic Code Snippets".

Here is my code.

 

            var adoConnection = new ADODB.Connection();
            var adoRecordset = new ADODB.Recordset();
            var adoCommand = new ADODB.Command();

            var server = new SasServer(Consumer.AssignedServer);
            var connectString = "provider=SAS.IOMProvider.1; SAS Workspace ID={0}";
            adoConnection.Open(string.Format(connectString, server.GetWorkspaceIdentifier()), "", "", 0);
            adoCommand.ActiveConnection = adoConnection;
            adoCommand.CommandText = "select * from sashelp.class";
            adoCommand.CommandType = ADODB.CommandTypeEnum.adCmdText;
            adoCommand.Properties["SAS Formats"].Value = "_ALL_";
            //adoRecordset.Properties["SAS Formats"].Value = "_ALL_";
            adoRecordset.Open(adoCommand, System.Reflection.Missing.Value, ADODB.CursorTypeEnum.adOpenDynamic, ADODB.LockTypeEnum.adLockReadOnly, 1);

            //Fill an ADO.NET DataSet using the ADODB Recordset
            var adapter = new OleDbDataAdapter();
            var dataTable = new DataTable();
            var count = adapter.Fill(dataTable, adoRecordset);

            sasDataGridView1.DataSource = dataTable.DefaultView;

            adoRecordset.Close();
            Marshal.ReleaseComObject(adoRecordset);
            adoConnection.Close();
            Marshal.ReleaseComObject(adoConnection);

 

But it doesn't work. The DataGridView shows nothing.  If I change sashelp.class to sashelp.cars, I got a "At least one of the IDs values (element 2 which is -1) is invalid." exception.

 

Am I missing something?

 

Shen


Accepted Solutions
Solution
‎01-26-2016 01:54 AM
Community Manager
Posts: 2,692

Re: sas data viewer custom task format issue

I included a simple data set viewer in this example:
 
Build your own SAS client app with Microsoft .NET
 
Here's a screenshot:
 

 

If building a custom task, you might also look at this SasDataExample.  It's described in Object-oriented access to SAS data in a custom task.

 

Chris

View solution in original post


All Replies
Grand Advisor
Posts: 17,325

Re: sas data viewer custom task format issue

Contributor
Posts: 26

Re: sas data viewer custom task format issue

Thanks, Reeza.

I haven't tried the power shell version, though I do refered it and it helped me on implementing in c#, because I want to find a solution on c# developing.

(I know that Chris posted a similar blog at Build your own SAS data set viewer using PowerShell)

 

Anyway, I will try the power shell sample. 

 

Solution
‎01-26-2016 01:54 AM
Community Manager
Posts: 2,692

Re: sas data viewer custom task format issue

I included a simple data set viewer in this example:
 
Build your own SAS client app with Microsoft .NET
 
Here's a screenshot:
 

 

If building a custom task, you might also look at this SasDataExample.  It's described in Object-oriented access to SAS data in a custom task.

 

Chris

Contributor
Posts: 26

Re: sas data viewer custom task format issue

Thanks, Chris.

 

I have learned the SasDataExample custom task sample before, but missed the "Apply Format" textbox.

But this approach uses SAS.Tasks.Toolkit.Helpers.TaskDataHelpers.GetDistinctValues to get formatted values of a *single* column.

 

I compared the working powershell version with my .NET program on ADODB usage and found that "SAS Formats=_ALL_" recordset property only works when ADODB.CommandTypeEnum.adCmdTableDirect is set, that means if I want to filter the table, I have to create a filtered data in work library and then fetch data from that work table. Am I right?

 

Shen

 

Community Manager
Posts: 2,692

Re: sas data viewer custom task format issue

You might be correct about that, when using the OLE DB method.

 

If you have a handle to the SAS Workspace, you can use the Utilities interface to get a FormatService handle, and use the FormatService to apply formatting to values for you.  I'll warn you though: the methods are a little onerous to navigate.  You'll find them documented in the sas.chm file within your Integration Technologies client installation directory.

Contributor
Posts: 26

Re: sas data viewer custom task format issue

Hi, Chris,

 

I'll read sas.chm and try the FormatService later.

For now, the SAS Formats=_ALL_ is good for me.

 

Really appreciate your help.

Shen.

☑ This topic is SOLVED.

Need further help from the community? Please ask a new question.

Discussion stats
  • 6 replies
  • 410 views
  • 2 likes
  • 3 in conversation