Using UDFs to Write to SQL Databases from Excel Services
There are many reasons why writing straight to a SQL database is a desirable feature on a server. For example, one could imagine a solution where users would load up a workbook on Excel Services, change some parameters, and have that new data saved to a SQL database, where it can be queried or updated by other users simultaneously.
In this post I will show one way to write a fairly simple UDF that can be called from a workbook, in order to store data on a SQL database. The data to be stored can be anything residing on a cell in the workbook, calculated in real time, or passed in through parameters.
There's not much else to say here, except that this ability opens up a multitude of scenarios, varying from collaboration to making Excel Services the front-end to powerful SQL databases.
With no further ado, here's the code (I apologize for the formatting I had to use to try and make this readable without resorting to attachments):
CODE:
[UdfMethod(IsVolatile=true, ReturnsPersonalInformation=true)]
public string writeToSql(
string serverName,
string databaseName,
string tableName,
string columnNames, // Comma separated!!!
string values) // Comma separated!!!
{
String[] restricted = { ";", "--", "/*", "*/", "xp_" };
String[] checkRestriction = { tableName, columnNames, values };
// Being overly restrictive about accepted inputs.
foreach (String currCheck in checkRestriction)
{
foreach (String currRestricted in restricted)
{
if (currCheck.Contains(currRestricted))
{
return "An error has occurred";
}
}
}
// Escape single quotes.
tableName = tableName.Replace("\'", "\'\'");
columnNames = columnNames.Replace("\'", "\'\'");
columnNames = columnNames.Replace("\"", "\"\"");
values = values.Replace("\'", "\'\'");
// Impersonate current user
//(see earlier blog post on user impersonation for the code)
using (WindowsImpersonationContext wiContext = impersonateUser())
{
// Build connection string
string connectionString =
"Integrated Security=SSPI;" +
"Persist Security Info=True;Initial Catalog=" +
databaseName +
";Data Source=" +
serverName +
";";
// Connect to SQL DB
SqlConnection connection =
new SqlConnection(connectionString);
connection.Open();
// use sp_executesql to kinda thwart SQL injection attacks
SqlCommand command =
new SqlCommand("sp_executesql", connection);
command.CommandType =
System.Data.CommandType.StoredProcedure;
// Comma separated string of values from parameter
string rowValues = "\'" +
values.Replace(",", "\',\'") +
"\'";
// Comma separated string of values from parameter
string columnValues = "\"" +
columns.Replace(",", "\",\"") +
"\"";
// Prepare statement to insert row in SQL
string parameter =
"insert into " +
tableName +
" (" +
columnValues +
")" +
" values (" +
rowValues +
")";
// execute command
command.Parameters.AddWithValue("@statement", parameter);
command.ExecuteNonQuery();
// Close connection
connection.Close();
} // end impersonation
return "Success!";
}
I have used this particular UDF numerous times. Most of the time I use it to send parameters chosen by a user through Sharepoint Filters straight into a SQL database. I have also used PivotTable Report filters as pseudo-drop downs where users can pick a particular value, and the UDF call can use that value as it's parameter to store in the SQL DB.
There are many more uses for it though, and many ways in which the code can be improved, but this framework should get you going.