0

Background: I have a asp.net web pages (razor) application on a 2008R2 web server that has a page with an excel upload button which allows the user to post excel data to a SQL Server 2014 table. In order to get it to work on the dev server I had to install the Microsoft Access Database Engine 2010 as outlined in this MikesDotNetting blog post. However, I have been encouraged to find a way to import data to the production server that doesn't require installing Microsoft Access Database Engine on the server. I have a C# Windows Forms app that does the same thing but I am not good at converting Windows Forms app code to my razor web sites.

I actually didn't expect this code below to work since I am posting data to SQL Server 2014 and according to MS OleDB has been deprecated and has not been supported since SQL Server 2012.

Q1 - Does deprecated just mean 'use at your own risk' or does it mean it shouldn't work on 2014 at all?

The code below (which occurs right after my if...ISPOST) works great for getting an excel file uploaded to my web server then inserted into a SQL Server 2014 table. I just need to find a way to make it work without installing Microsoft Access Database Engine to the production server 2008 R2 IIS7.

Q2 - Is there some Nuget Package or other workaround that might help?

Q3 - Is there a quick fix to convert that OLE DB connection string to ODBC?

Q4 - Is there a namespace / assembly I could add to the project that would not require the access database engine install on the server?

Code:

try
                {
                    // import code
                    var excel = Request.Files[0];
                    var file = Path.Combine(Server.MapPath("~/Upload_Folder/"), excel.FileName);
                    excel.SaveAs(file);
                    var excelConnectionString = "Provider=Microsoft.ACE.OLEDB.12.0;Data Source={0};Extended Properties=\"Excel 12.0 Xml;HDR=Yes;IMEX=1\"";
                    var sqlConnectionString = ConfigurationManager.ConnectionStrings["MYCONNECTIONSTRING"].ToString();
                    var excelData = new DataTable();
                    using (var myConnection = new OleDbConnection(string.Format(excelConnectionString, file)))
                    {
                        var myCommand = new OleDbCommand("SELECT * FROM [Sheet1$]", myConnection);
                        var myAdapter = new OleDbDataAdapter(myCommand);
                        myAdapter.Fill(excelData);
                        using (var destinationConnection = new SqlConnection(sqlConnectionString))
                        {
                            destinationConnection.Open();
                            using (var bulkCopy = new SqlBulkCopy(destinationConnection))
                            {
                                bulkCopy.DestinationTableName = "MYTABLE";
                                bulkCopy.ColumnMappings.Add("CODE", "code");
                                bulkCopy.ColumnMappings.Add("TITLE", "title");                                   
                                bulkCopy.ColumnMappings.Add("LAST_NAME", "last_name");
                                bulkCopy.ColumnMappings.Add("FIRST_NAME", "first_name");
                            }
                        }
                        if (Directory.Exists(Path.GetDirectoryName(file)))
                        {
                            File.Delete(file);
                        }
                    }
                }
                catch (Exception ex)
                {
                    throw ex;
                }
TimK
  • 115
  • 2
  • 2
  • 12
  • Q1 (Answered). I found another [S.O. post with a nice explanation](http://stackoverflow.com/questions/9208091/the-difference-between-deprecated-depreciated-and-obsolete) and a [blog post from late 2011](http://hal2020.com/2011/09/25/ole-db-and-sql-server-history-end-game-and-some-microsoft-dirt/) which has some narrative suggesting while deprecated, OLE DB will be supported for another 7 years (i.e. through 2018). – TimK Feb 06 '16 at 16:41
  • Q2 (Answered). there are several Nuget package options available to work with Excel files [ClosedXML](http://blog.bajajsumit.com/2014/02/uploading-excel-and-importing-data-in.html), [ExcelDataReader](https://www.nuget.org/packages/ExcelDataReader/), [EPPlus](https://www.nuget.org/packages/EPPlus/), etc. However, I decided to just create a dll from my c# windows forms application (note: I called it a console app in the OP originally). This was very simple as described in [this SO post](http://stackoverflow.com/questions/11536140/convert-a-completed-project-to-a-dll) – TimK Feb 18 '16 at 18:00
  • Q3 (Answered). I haven't done this yet but I found this nice blog post demonstrating how to convert your OLE DB connections to [ODBC connections](https://www.simple-talk.com/sql/database-administration/getting-data-between-excel-and-sql-server-using-odbc--/). – TimK Feb 18 '16 at 18:05
  • Q4 I tried this (see link in first comment above). I first created a dll from my windows forms application. to do this just...1) change output type to class library then rebuild the c# win forms app., 2) the dll file gets added to the bin folder, 3) then just copy the dll over to the web app bin folder, 4) right-click and add the reference, 5) then copy the code from the win forms app into your web app. I realized it still required a connection string because the file was stored on the web server first. so it didn't work. – TimK Feb 20 '16 at 20:20
  • Q2 I also wanted to mention that the first paragraph of this post explains the issue I am running into. [Reading Excel Files Without Saving To Disk In ASP.NET ](http://www.mikesdotnetting.com/article/277/reading-excel-files-without-saving-to-disk-in-asp-net) Basically you need to use a library like EPPlus to stream the data from excel without storing the file on your web server. Since the Excel file does not touch the web server there is no reason for those OLEDB or ODBC connection strings or installing MS Access Db Engine or other drivers on the server. – TimK Feb 20 '16 at 20:25

1 Answers1

1

I didn't get any responses to this question after nearly 50 views which made me think it was too general a question with many different solutions. However to make it as useful a post as possible I just wanted to document my own solutions (with links) to the 4 original questions proposed (see comments 1-4 above). --Tim

TimK
  • 115
  • 2
  • 2
  • 12