Reading a SAS Dataset with .NET OLEDB

If you have had to read in a SAS Dataset into a .NET Dataset here is some code I found that works to do it with:


Make sure you add these lines to the start of the page

Imports SAS
Imports SASWorkspaceManager
Imports System.Text
Imports System.Data.OleDb


In the Subroutine do this:
 (note: BoldIalics means you should type in your information not what I wrote)
  
   Try  'NEVER FORGET TRY CATCH!!

            Dim sasDs As System.Data.DataSet = New System.Data.DataSet
            Dim sas As OleDbConnection = New OleDbConnection("Provider=sas.LocalProvider; Data Source=pathToDirectoryWhereSASFILEisLocated")
            sas.Open()
            Dim sasCommand As OleDbCommand = sas.CreateCommand
            sasCommand.CommandType = CommandType.TableDirect
            sasCommand.CommandText = "nameOfSASFilewithoutExtension"
            Dim da As OleDbDataAdapter = New OleDbDataAdapter(sasCommand)
            da.Fill(sasDs, "SasData")
            sas.Close()
            DataGridView1.DataSource = sasDs.Tables("SasData")
        Catch ex As Exception
            MessageBox.Show("Unable to load SAS dataset. Error seen was: " + ex.ToString)
        End Try

    End Sub


DOING A JOIN INSIDE AN UPDATE STATEMENT

This example is meant only to illustrate how to do an update statement that involves multiple tables.

dbo.CustomersMasterList Table

CML_ID, Name, Address, City, State, Zip, Phone, CustomerEmailAddress (newly added)

dbo.EmailAddresses Table

E_ID, emailaddress

Sample data from dbo.CustomersMasterList
1,John Doe,12345, Elm Street,Walawala WA, 90085,952-555-1212, NULL

2,Jane Doe,12345, Elm Street,Walawala WA, 90085,952-555-1212, NULL

...

Sample data from dbo.EmailAddresses

1,JohnDoe@aol.com

2,JaneDoe@aol.com

Say you need to take Table A Which is dbo.CustomersMasterList

and you needed to add a column to it called CustomerEmailAddress. So you go modify the table and add CustomerEmailAddress to the table.

But the email addresses are in a seperate table called dbo.EmailAddresses and its a big table so you dont want to retype all that data.

You could do this:

UPDATE dbo.CustomersMasterList

SET CustomerEmailAddress = EmailAddress

FROM dbo.CustomersMasterList CML

JOIN dbo.EmailAddresses ON

CML_ID = E_ID

WHERE any criteria can go here...

Let me know if you have any issues.... Happy programming!