As the first step in the decommissioning of sasCommunity.org the site has been converted to read-only mode.


Here are some tips for How to share your SAS knowledge with your professional network.


User:Savian/BlogEntry: 2007 May 06 13:41:32 EDT

From sasCommunity
Jump to: navigation, search


Using OleDb to Get SAS Metadata/Schema information

Ok, this took me a bit of time to figure out. I was looking at ISASColumnInfo, IGetColumnInfo, and other things trying to determine how to retrieve the schema from a SAS dataset. The relevant line of code is GetOleDbSchemaTable() but the rest is included for a complete example. The object array is used as a filter. The schema table has a number of columns and the object array filters IN ORDER OF THE OBJECTS. Hence, the object array, in this case says to ignore values in column 1, column2, and only return values in column 3 that match the dataset name.

I hope this helps someone else in a bind.

       internal static DataTable GetColumnInfo(string library, string dataset)
       {
           DataTable dt = new DataTable();
           try
           {
               OleDbConnection sas = new OleDbConnection(@"Provider=sas.LocalProvider; Data Source=" + library);
               sas.Open();
               object[] obj = new object[] {null, null, dataset} ;
               dt =sas.GetOleDbSchemaTable(OleDbSchemaGuid.Columns, obj);
           }
           catch (Exception ex)
           {
               MessageBox.Show("Unable to load SAS dataset. Error seen was " + ex.Message);
           }
           return dt;
       }
               //The following code will generate OleDb specific schema information but SAS metadata is not included.
               //Commented out but left in in case it is needed.
               //OleDbCommand sasCommand = sas.CreateCommand();
               //sasCommand.CommandType = CommandType.TableDirect;
               //sasCommand.CommandText = dataset;
               //OleDbDataReader sasRead = sasCommand.ExecuteReader();
               //ds.Tables.Add(sasRead.GetSchemaTable());