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.