sas data viewer custom task format issue

[ShenQicheng]

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

[Reeza]

Is this useful at all?

https://gist.github.com/FriedEgg/55ec275ae1e1c8fcbe6a

[ShenQicheng]

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. 

[ChrisHemedinger · Accepted solution]

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

[ShenQicheng]

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

[ChrisHemedinger]

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.

[ShenQicheng]

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.