BookmarkSubscribeRSS Feed
🔒 This topic is solved and locked. Need further help from the community? Please sign in and ask a new question.
ShenQicheng
Obsidian | Level 7

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

1 ACCEPTED SOLUTION

Accepted Solutions
ChrisHemedinger
Community Manager

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

It's time to register for SAS Innovate! Join your SAS user peers in Las Vegas on April 16-19 2024.

View solution in original post

6 REPLIES 6
ShenQicheng
Obsidian | Level 7

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
Community Manager

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

It's time to register for SAS Innovate! Join your SAS user peers in Las Vegas on April 16-19 2024.
ShenQicheng
Obsidian | Level 7

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
Community Manager

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.

It's time to register for SAS Innovate! Join your SAS user peers in Las Vegas on April 16-19 2024.
ShenQicheng
Obsidian | Level 7

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.

sas-innovate-2024.png

Join us for SAS Innovate April 16-19 at the Aria in Las Vegas. Bring the team and save big with our group pricing for a limited time only.

Pre-conference courses and tutorials are filling up fast and are always a sellout. Register today to reserve your seat.

 

Register now!

SAS Enterprise Guide vs. SAS Studio

What’s the difference between SAS Enterprise Guide and SAS Studio? How are they similar? Just ask SAS’ Danny Modlin.

Find more tutorials on the SAS Users YouTube channel.

Click image to register for webinarClick image to register for webinar

Classroom Training Available!

Select SAS Training centers are offering in-person courses. View upcoming courses for:

View all other training opportunities.

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