Store excel file in SQL Server

Question

I'm looking for a good solution for this scenario:

• I have an asp.net application with SQL Server 2008 R2 backend.
• I need to allow user to upload a file and save it for later retrieval.
• Also I need to parse this file for querying purposes on db side. So I need both data from this file as datatable and from sql to combine results.

So here's what I thought of:

1. Store excel file in SQL Server as varbinary(max) - this will allow to retrieve file later through .net pretty easy.
2. Parsing: one way to do it is upon file uploading by user parse it with openxml or some 3rd party library into datatable and pass it to stored procedure which will return resultset.

I don't like this approach, because it means that we will have to pass significant amount of data to the database two times (First when passing the whole file, and 2nd when passing a dataset.

Any suggestions on how to do this more efficiently? Is it possible to read data from varbinary max using something like this:

SELECT *
FROM OPENDATASOURCE('Microsoft.ACE.OLEDB.12.0', 
                    'Data Source=D:\TestJET.xls;
                    Extended Properties=''Excel 12.0;HDR=NO;IMEX=1;ImportMixedTypes=Text''')...[Sheet1$]

but without actually creating a file?

Or maybe it is possible not to pass the whole file to the db, but construct excel file back from datatable?

Or maybe varbinary(max) is not the best solution and better use different datatype like XML?

Excel file is pretty simple. One styled table with no macros or formulas. (Cannot be converted to CSV though) file size is 200-800kb.

Answer 1

You can upload the file to your server using a FileUpload control then the file can be retrieved everytime its needed.

From there you can read the excel file using a OleDbConnection adding each fetched record to a list to iterate one by one and insert them into your database afterwards.

This is a little example using three classes: CConexion(handles the OleDbConnection process), ExcelParser(reads and inserts the fetched records to your database so you can query on them when needed) and ExcelRecord which represents a container for the data on each registry of your ExcelFile.

These OleDb libraries get installed when you have MS Office Excel 2010 on your computer. I have tested the code and works well.

The excel file is called Hoja1.xlsx on the example and is stored on folder called Files on the root of your TestApp, when you open it there are three records with two fields each that go (A1,B1),(A2,B2),(A3,B3) on the first sheet of the file called also Hoja1.xlsx

    ''CConexion Class:

    Imports System.Data.SqlClient
    Imports System.Data.OleDb
    Imports System.Data

    Public Class CConexion

    #Region "Private Variables"

        Dim sOleDbConnectionString As String = String.Empty
        Dim conexionOleDb As New OleDbConnection()

    #End Region

    #Region "Con_Ole"

        Public Interface IConexionOleDb

            Property retConexionOleDb() As OleDbConnection
            Sub retOpenOleDb()
            Sub retCloseOleDb()

        End Interface

        Public Property retConexionOleDb() As OleDbConnection

            Get
                Return conexionOleDb
            End Get
            Set(ByVal value As OleDbConnection)

            End Set

        End Property

        Public Sub retOpenOleDb()

            If Not conexionOleDb.State = System.Data.ConnectionState.Open Then
                conexionOleDb.Open()
            End If

        End Sub

        Public Sub retCloseOleDb()

            If Not conexionOleDb.State = ConnectionState.Closed Then
                conexionOleDb.Close()
            End If

        End Sub

    #End Region

    #Region "Constructors"

        Public Sub New()

        End Sub

        Public Sub New(ByVal rutaOleDb As String)

            sOleDbConnectionString = "Provider=Microsoft.ACE.OLEDB.12.0;" _
                                                        & "Data Source=" & rutaOleDb _
                                                        & ";" & "Extended Properties=Excel 12.0;"
            conexionOleDb.ConnectionString = sOleDbConnectionString

        End Sub

    #End Region

    End Class

    ''ExcelRecord Class:

    Public Class ExcelRecord

        Private _RId As Short = 0
        Public Property RId() As Short
            Get
                Return _RId
            End Get
            Set(ByVal value As Short)
                _RId = value
            End Set
        End Property

        Private _RText As String = String.Empty
        Public Property RText() As String
            Get
                Return _RText
            End Get
            Set(ByVal value As String)
                _RText = value
            End Set
        End Property

        Public Sub New()

        End Sub

        Public Sub New(ByVal Rid As Short, ByVal RText As String)
            Me.RId = Rid
            Me.RText = RText
        End Sub

    End Class

    ''ExcelParser Class:

    Imports System.Data.OleDb
    Imports System.Collections.Generic

    Public Class ExcelParser

        Private Function InsertRecords(ByVal objExcelRecords As List(Of ExcelRecord)) As Boolean
        ''Your code for insertion here
            Return True
        End Function

        Public Function ReadExcel(ByVal filePath As String) As Short

            Dim cn As New CConexion(filePath)
            Dim dr As OleDbDataReader
            Dim OperationState As Boolean = False
            Dim objExcelRecords As New List(Of ExcelRecord)

            Try

                Dim cmd As New OleDbCommand("Select * from [Hoja1$]", cn.retConexionOleDb)

                cn.retOpenOleDb()
                dr = cmd.ExecuteReader

                While dr.Read    

                    Dim objExcelRecord As New ExcelRecord(CShort(dr(0)), CStr(dr(1)))
                    objExcelRecords.Add(objExcelRecord)

                End While

                OperationState = InsertRecords(objExcelRecords)
                CType(dr, IDisposable).Dispose()

            Catch ex As Exception

            Finally

                cn.retCloseOleDb()
                cn.retConexionOleDb.Dispose()

            End Try

            Return OperationState

        End Function

    End Class


''Test Page ExcelReader.aspx.vb
Protected Sub Page_Load(ByVal sender As Object, ByVal e As System.EventArgs) Handles Me.Load

        If Not Page.IsPostBack Then

            Dim filePath As String = Server.MapPath("~\Files\Hoja1.xlsx")
            Dim objExcelParser As New ExcelParser()

            If objExcelParser.ReadExcel(filePath) Then
                Response.Write("Read!")
            Else
                Response.Write("No Read!")
            End If

        End If

    End Sub

Let me know if this works for you. Hope it helps.

Answer 2

Your idea of constructing the excel file from the DB is interesting - this suggests that it's not important to get the same file back that was uploaded, just one that contains the same information. Is that correct?

If so, I would parse the file on upload (I like ClosedXML for C# excel work) on the server, store the relevant data in a query-friendly form in your database and then throw the original file away. When the user asks for the file back, create a new one for them with the right contents.

Be careful with XML Data type columns - they have restrictions on what you can do with them.

Answer 3

You might want to look at the FILESTREAM feature of SQL 2008 r2 to store the file in the server. Here is an overview and a good blog post. I am not sure what you file type or what do you need to get out of it but you have options of using bulk inserts or as you already pointed out OPENDATASOURCE or executing process at later time then loading data using the ID from the FILESTREAM to link the data.