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.

Comments

• Anonymous
  November 08, 2006
  People should take GREAT care when using such methods of UDFs. Luis alludes to some of the potential problems (for example, by using a Stored procedure instead of a straight select statement). Creating generic versions of this sort of UDF can cause great harm because anybody can author a workbook that makes malicious use of the UDF and upload it to the server, then make the administrator go to the workbook and click on it to run the code. Using parameterized, well known stored procedures instead of generic stored procedures or plain SQL will go a long way to making your solutions more robust.

• Anonymous
  November 09, 2006
  Shahar is completely correct... it's always dangerous to take user input and stuff them into SQL statements, however in some scenarios this is required. I think I mitigated most of the common-place security issues in my code above, but take it with a grain of salt :o) If you can do without writing generic UDFs for SQL writing than take that route, you can always use the above code as a framework and modify as needed (that's the whole point actually).

• Anonymous
  April 17, 2007
  And how would you call this in the workbook?