SQL-Server export to Excel, Access and XML
Introduction
This article will demonstrate how to export a table within SQL-Server database to Microsoft Excel, Microsoft Access or a standard XML file utilizing Transact-SQL OPENROWSET. Using this method means no dealing with reading table data from a database into a client container such as a DataTable as an intermediate step to saving the table data to Microsoft Excel, Microsoft Access or an XML file where the intermediate step can be simple or complex dependent on the chosen logic which could be using Open XML, Excel automation, a third party library or using a StreamWriter. What follows are static code samples and dynamic code samples to build upon for your solution.
All base code resides in a class project while execution is performed in a windows form project. This by no means that this code is solely for windows form projects as the code to export data can run in any C# project type be it WPF, web solution etc.
Description
Before writing code there is configuration to be performed, otherwise the following error will be raised when executing the code presented:
The requested operation could not be performed because OLE DB provider "Microsoft.ACE.OLEDB.12.0" for linked server "(null)" does not support the required transaction interface.
The configuration can be done in SQL-Server Management Studio (SSMS) as demonstrated here, or SQL Operations Studio (SOS) for example.
Open SSMS, open Object Explorer, open to the following tree node.
Right click on the node above and select properties.
Select and check the following properties.
Select the Database node, right-click, select Refresh for validation. This concludes setting up SQL-Server for exporting data from tables in databases.
Setting up to run the code samples, unzip the attached Visual Studio solution, find CreateDatabase.sql, run this script in SSMS following by running the script SQL_Scripts.sql to create and populating a table. Both scripts are located in the root folder of the attached solution.
All code samples are executed from a Windows form project as presented below:
Before getting into the code for exporting there are several classes that need to be discussed. In the main class for performing export operations, there is a base class, BaseSqlServerConnections which is responsible for centralizing properties for connecting to the intended database. There are two properties, IsKarenMachine and IsKarensDatabaseServer. Both properties are only needed for these code samples. They are responsible for determining if you the reader have changed the server and catalog from the defaults if they are not changed your connection will fail.
BaseSqlServerConnection inherits BaseExceptionsHandler which provides properties which are used in each method of the main data class SqlServerOperations to assist dealing with runtime exceptions.
On each build of the main project several commands run which are setup under project properties, Build Events in Pre-Build which will delete any Excel, Access or XML file. Each code sample copies a fresh Excel or Access file from a folder one level below the application folder to use for the current code sample.
Pre-build command
Base Excel export
The following utilizes a SqlClient connection and command to export, in this case the SQL-Server Customers table in the catalog ExcelExporting on the server KARENS-PC (remember to change this to your server name or to .\SQLEXPRESS or localdb).
Inspecting the CommandText for the command object note the only variable is the Database set to the file name passed in. The actual table name and fields are static.
public bool ExportAllCustomersToExcel(string pFileName, ref int pRowsExported)
{
using (SqlConnection cn = new SqlConnection { ConnectionString = ConnectionString })
{
using (SqlCommand cmd = new SqlCommand { Connection = cn })
{
cmd.CommandText =
$"INSERT INTO OPENROWSET('Microsoft.ACE.OLEDB.12.0', 'Excel 12.0;Database={pFileName}'," +
"'SELECT * from [Customers$]') " +
"SELECT CustomerIdentifier,CompanyName,ContactName,ContactTitle," +
"[Address],City,Region,PostalCode,Country,Phone " +
"FROM Customers";
cn.Open();
try
{
pRowsExported = cmd.ExecuteNonQuery();
return pRowsExported > 0;
}
catch (Exception e)
{
mHasException = true;
mLastException = e;
}
}
}
return IsSuccessFul;
}
The data provider in the above example is exactly the same as for the code sample for exporting to Microsoft Access as shown in the partial code sample.
cmd.CommandText = $"INSERT INTO OPENROWSET('Microsoft.ACE.OLEDB.12.0','{pFileName}';'Admin';''," +
For the base SQL INSERT INTO, this is a standard SQL command up to OPENROWSET. OPENROWSET is the critical aspect of the entire command in combination with the initial setup at the start of this document.
Within OPENROWSET the provided is indicated along with extended properties for Excel which is not required for working with Microsoft Access.
In the second SELECT statement list field names, this could also be SELECT * too which is fine if all fields are to be exported yet it's better to list each field and consider wrapping each field with brackets e.g. CustomerName bracketed would be [CustomerName]. This is to safeguard against fields with spaces in their name such as Customer Name which would cause an exception when executing the intended statement so wrapping all fields in brackets is a safety net.
The line, pRowsExported = cmd.ExecuteNonQuery executes the command to export and on success returns how many rows were exported.
Base Access export
In this case, the code below is almost the same as the Excel export.
For the first two arguments to OPENROWSET the provider is exactly the same as the Excel OPENROWSET but with Microsoft Access the extended property is not required. Database is missing, instead, the file name is next followed by specifying 'Admin' which indicates this is an admin. This could be an issue with a customer running this operation where their machine may be locked down by company security policies.
Fields are dynamic rather than hardcoded as with the Excel code sample. Excel can also have dynamic field names.
The WHERE condition is similar to another Excel method included in the attached solution which permits some or all records to be exported.
public bool ExportAllCustomersToAccess(string pFileName, string pCountry, ref int pRowsExported)
{
string fields = "CompanyName,ContactName,ContactTitle,Phone";
using (SqlConnection cn = new SqlConnection { ConnectionString = ConnectionString })
{
using (SqlCommand cmd = new SqlCommand { Connection = cn })
{
/*
* If using .mdb use Microsoft.Jet.OLEDB.4.0 rather than Microsoft.ACE.OLEDB.12.0 as the provider name
*/
cmd.CommandText = $"INSERT INTO OPENROWSET('Microsoft.ACE.OLEDB.12.0','{pFileName}';'Admin';''," +
$"'SELECT {fields} FROM Customers') " +
$" SELECT {fields} " +
" FROM Customers";
if (pCountry != "*")
{
cmd.CommandText = cmd.CommandText + " WHERE Country = @Country";
cmd.Parameters.AddWithValue("@Country", pCountry);
}
try
{
cn.Open();
pRowsExported = cmd.ExecuteNonQuery();
return pRowsExported > 0;
}
catch (Exception e)
{
mHasException = true;
mLastException = e;
}
}
}
return IsSuccessFul;
}
Base XML export
The main focus on exporting to XML is working with FOR XML clause in a SELECT statement. If there is a large data set to export the common method of reading data into a DataSet then writing to XML may truncate the data which does not happen when using XmlReader in tanget with an instance of XmlDocument as shown below.
public bool WriteCustomerTableToXmlFile(string pFileName)
{
// Using SQL here or in a stored procedure write data to xml
// table is stored in this variable TempTable
using (SqlConnection cn = new SqlConnection() { ConnectionString = ConnectionString })
{
using (SqlCommand cmd = new SqlCommand() { Connection = cn })
{
// replace sql to match the temp table structure
string selectStatement = @"
SELECT ( SELECT Cust.CustomerIdentifier ,
Cust.CompanyName ,
Cust.ContactName ,
Cust.ContactTitle
FROM dbo.Customers AS Cust
FOR
XML PATH('Customer') ,
TYPE
)
FOR XML PATH('') ,
ROOT('Customers');";
cmd.CommandText = selectStatement;
try
{
cn.Open();
XmlReader reader = cmd.ExecuteXmlReader();
var doc = new XmlDocument();
doc.Load(reader);
doc.Save(pFileName);
}
catch (Exception e)
{
mHasException = true;
mLastException = e;
}
}
}
return IsSuccessFul;
}
Summary
In this article you have learned the basics for exporting SQL-Server table data to Microsoft Excel, Microsoft Access and XML along with using base classes to handle data connections and a base class for handling runtime exceptions.
From here consider using stored procedures for exporting data to Microsoft Excel, Microsoft Access and XML if dealing with large datasets.
See also
Import and Export Excel Files into Varbinary (Max) in SQL Server
Source code
The full source code is provided in the following Github repository.