William (Bill) Vaughn's Musings

I tried to perform this fairly simple (sounding) operation this afternoon and here it is after dinner and I'm just now done. The problem is, that until you find the right web site with the right example you'll get errors like "Cannot find Installable ISAM" or "Invalid file format" or other red-herring errors that just waste your time.

Here is an example I'm building for my new ADO.NET 3.5 "What's New For SQL Server Developers" session to be given for the first time at VSLive in December. This example reads a simple comma-delimited file that contains two columns "City" and "State". It's used to illustrate what happens when you use a Table-Valued Function (a new SQL Server 2008 feature) and pass a large rowset as the parameter (now supported in ADO.NET 3.5).

To start with notice the OleDb Connection object's ConnectString.

         cnOleDb.ConnectionString = "Provider=Microsoft.Jet.OLEDB.4.0;" _
            & "Data Source=D:\;Extended Properties=""text;HDR=Yes;FMT=Delimited(,)"";"

There are a couple of syntax gotchas here that you need to watch out for.

1. The Data Source references a path, not a filename. The filename you'll be reading is passed in the SELECT.
2. The Extended Properties key must be passed as a double-quoted string. Without the double quotes, I got an "Cannot find Installable ISAM" exceptions. Note that the delimiter used in your file should be mentioned here. Note that my file is comma delimited. I also included the column names as the first row--this is the "header".
3. I also installed the 2007 Office System Driver : Data Connectivity from http://www.microsoft.com/downloads/details.aspx?familyid=7554F536-8C28-4598-9B72-EF94E038C891&displaylang=en but this might not be necessary.

Once the OleDb connection is open, you can create a simple SELECT to pull rows from the specified file. Note that my file has no imbedded spaces. I suspect you'll need to add brackets to the filename if it does (have spaces).

The rest of this example sucks the rows out of the DataReader and passes them to another ADO.NET Command that's connected to a SQL Server 2008 instance via a Structured parameter. On the other end, there's a stored procedure (AuthorsByStateList) that's programmed to accept a Table-type User Defined Type as a Parameter (set to READONLY). I return the rowset generated by the SP and display it in a grid.

hth

Bill

--------------------------------- Code Follows ------------------------------------------------------------------------------------------------------------

Private Sub btnDataReader_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles btnDataReader.Click
        Dim cnOleDb As New OleDb.OleDbConnection
        Try
            cnOleDb.ConnectionString = "Provider=Microsoft.Jet.OLEDB.4.0;" _
            & "Data Source=D:\;Extended Properties=""text;HDR=Yes;FMT=Delimited(,)"";"

            Dim cmdOleDb As New OleDb.OleDbCommand("SELECT City, State FROM AllPublishersStateList.csv", cnOleDb)
            cnOleDb.Open()
            Dim drOleDb As OleDb.OleDbDataReader
            drOleDb = cmdOleDb.ExecuteReader

            Dim cmdResults As SqlCommand
            cmdResults = New SqlCommand("AuthorsByStateList", cn2)
            With cmdResults
                .CommandType = CommandType.StoredProcedure
                .Parameters.Add("@StateList", SqlDbType.Structured).Value = drOleDb
                .Parameters("@StateList").TypeName = "dbo.StateList"
                Dim dtResults As New DataTable, drResults As SqlDataReader
                cn2.Open()
                ' Pass the DataReader to the query.
                drResults = .ExecuteReader
                dtResults.Load(drResults)
                DataGridView1.DataSource = dtResults
            End With
        Catch exsql As SqlException
            MessageBox.Show(exsql.Message)
        Catch ex As Exception
            Debug.Assert(False, ex.Message)
        Finally
            cnOleDb.Close()
            cn2.Close()
        End Try
    End Sub