by Redge Shepherd
Bookmark

C#, Excel, and SQL Server

Using Excel to work with SQL Server data sets is often more convenient than having to write a complete solution using C#. A variety of solutions exist to work with Excel from your C# code and Microsoft.Office.Interop.Excel is just one of them. EPPlus is also a popular solution for working with Excel. EPPlus is free to use for development purposes, however, a license is required for production applications.

Add Reference to work with Excel

Right-click on the “References” section in your project and click on Add Reference. Use the Search feature to find the “Microsoft.Office.Interop.Excel” assembly and click OK.

Click OK to add the Reference to project. We can now start working with Excel from our C# code.

Using C# With Excel and SQL Server

MSSQLTips.com features three well-written articles complete with code examples that demonstrate how to use C# with SQL Server and Excel:

I highly encourage you to visit MSSQLTips.com and sign up for their newsletter. Interesting tips are sent to my email inbox every day. There’s always a better way and more than one solution. Your applications may benefit from new features and changes especially with .NET.

Direct Connections from Excel to SQL Server

I work with C#, SQL Server, and Excel on a daily basis. Many times it is just as convenient to create a database connection directly from Excel and read the data into a Table or Pivot Table. The tools available in Excel and a little VBA are just as easy to integrate with a SQL Server database to extract and manipulate data. While this may be true, users can create “orphans” by saving the workbook to a different name.

Orphan workbooks make it difficult to impose version control on your application. Changes or “bug fixes” will never find their way into an orphan file and their use is highly discouraged.

The content presented by the reference articles serves to provide a method for your application to export data to an external tool such as Excel. This makes it easier for the user to manage what could be a large number of independently created and easily forgotten file names.

I encourage you to visit Mircea Dragan’s page to see other articles that may be of interest to you.

Related Articles and Resources

Leave a Reply