Introduction
When I was looking for a method to upload data from the SQL Server CE database of my Web Pages site, I found a possible answer in the “Solution 1 – OLEDB” proposal of the “Exporting Data to Excel” article.
During the development I came across some small problems and finally I obtained an acceptable implementation that I will share in this article.
My solution uses the System.Data.SqlServerCe.dll
, that must be referenced in Web.config
file, and three functions, which I outline in the following.
The code in depth
Web.config file
="1.0"
<configuration>
<system.web>
<compilation debug="true">
<assemblies>
<add assembly="System.Data.SqlServerCe, Version=4.0.0.0, Culture=neutral, PublicKeyToken=89845DCD8080CC91"/>
</assemblies>
</compilation>
</system.web>
</configuration>
The add assembly
setting references the System.Data.SqlServerCe.dll
assembly that is needed during the compilation of the web page (see CreateDataTable function).
CreateDataTable functions
public static DataTable CreateDataTable(string sqlCeDb, string sqlCmd)
{
DataSet dataSet = new DataSet();
DataTable dt = new DataTable();
try {
SqlCeConnection sqlConn= new SqlCeConnection();
sqlConn.ConnectionString = "Data Source = " + sqlCeDb;
SqlCeCommand cmd = new SqlCeCommand();
cmd.CommandType = CommandType.Text;
cmd.CommandText = sqlCmd;
cmd.Connection = sqlConn;
sqlConn.Open();
SqlCeDataAdapter sda = new SqlCeDataAdapter(cmd);
sda.Fill(dataSet);
sqlConn.Close();
dt = dataSet.Tables[0];
return dt;
}
catch (Exception ex)
{
return dt;
}
}
The CreateDataTable function opens a connection with the SQL Server CE database and exports data as DataTable from the database.
It accepts the physical path of the SQL Server CE .sdf file as first parameter and the SQL query extracting data from the database as second parameter.
The function instantiates classes from the System.Data.SqlServerCe
namespace, that must be referenced in Web.Config
.
ExportToExcel function
public static int ExportToExcel(DataTable dt, string excelFile, string sheetName)
{
string connString = "Provider=Microsoft.ACE.OLEDB.12.0;Data Source=" +
excelFile + ";Extended Properties=Excel 12.0 Xml;";
int rNumb = 0;
try
{
using (OleDbConnection con = new OleDbConnection(connString))
{
con.Open();
StringBuilder strField = new StringBuilder();
for (int i = 0; i < dt.Columns.Count; i++)
{
strField.Append("[" + dt.Columns[i].ColumnName + "],");
}
strField = strField.Remove(strField.Length - 1, 1);
var sqlCmd = "CREATE TABLE [" + sheetName + "] (" + strField.ToString().Replace("]", "] text") + ")";
OleDbCommand cmd = new OleDbCommand(sqlCmd, con);
cmd.ExecuteNonQuery();
for (int i = 0; i < dt.Rows.Count; i++)
{
StringBuilder strValue = new StringBuilder();
for (int j = 0; j < dt.Columns.Count; j++)
{
strValue.Append("'" + AddSingleQuotes(dt.Rows[i][j].ToString()) + "',");
}
strValue = strValue.Remove(strValue.Length - 1, 1);
cmd.CommandText = "INSERT INTO [" + sheetName + "] (" + strField.ToString() + ") VALUES (" +
strValue.ToString() + ")";
cmd.ExecuteNonQuery();
rNumb = i + 1;
}
con.Close();
}
return rNumb;
}
catch (Exception ex)
{
return -1;
}
}
The ExportToExcel function receives a DataTable as first parameter and transfers its content into a sheet (with name from the third parameter) of a new Excel file created at the path passed with the second parameter.
If the function is successful, it returns the number of exported records, otherwise it returns -1.
It derives with some modifications from the Export function from Exporting Data to Excel; in the following I highlight its points of interest.
The connection string
I have chosen to produce an Excel file in the new .xlsx file format introduced by Excel 2007, and so I have used a connection string to the Access Database Engine 2010, which exists in 32-bit and 64-bit versions and that must be downloaded from Microsoft Access Database Engine 2010 Redistributable, if it’s not present on your system yet.
Even if you want to create a traditional .xls file, you have to know that the old Microsoft OLE DB Provider for Jet is available in 32-bit version only.
So, the only solution for the web sites running on 64-bit environments is to use the Access Database Engine 2010 with a slightly different connection string:
string connString = "Provider=Microsoft.ACE.OLEDB.12.0;Data Source=" +
excelFile + ";Extended Properties=Excel 8.0;";
The field names string
Two different SQL statements require a list of field name as string in the format
[field1],[field2],[field3],…,[fieldn]
The string is created by a for
loop that appends to a StringBuilder
object all the column names of the DataTable.
Create Excel sheet
The major simplification used by the function is that all the data from database are transferred to the Excel file as text.
This approach avoids to examine one by one the column data types of DataTable and create an Excel sheet with columns of the same source type.
So, all the columns of the Excel sheet are generated as text fields with a SQL statement that uses the field names string seen before adding to any field name “text” as data type with the use of a Replace("]", "] text")
method.
Copy records from DataTable to Excel sheet
For each DataTable row a string is created appending all the row values and then the string is used together with the field names string to assemble a SQL statement that inserts the row values into the Excel sheet.
Note that the process of creating a field values string involves a call to the AddSingleQuote
function to escape possible single quotes in the values.
AddSingleQuote function
public static string AddSingleQuotes(string origText)
{
string s = origText;
int i = 0;
while ((i = s.IndexOf("'", i)) != -1)
{
s = s.Substring(0, i) + "'" + s.Substring(i);
i += 2;
}
return s;
}
If the text passed as value to the Insert SQL statement includes a single quote, SQL Server throws an error (Error: 105 Unclosed quotation mark after the character string ‘).
The function fixes this occurrence by escaping any single quote with the addition of a second single quote.
The sample application
To illustrate the use of my functions, I propose a simple site that extract data as Excel file from a sample database with a quite complex query.
The sample database I used is the TestDB.sdf file downloadable from the link SQL SERVER – CE – Samples Database for SQL CE 4.0.
It must be copied into the App_Data
directory of the sample site and then Default.cshtml
page can be launched in browser.
Obviously, the query I used could be replaced with a simpler one like
SELECT * FROM Customers