How to: Access an External Data Source from a UDF
Applies to: SharePoint Server 2010
This example shows how to access an external database from a user-defined function (UDF).
Example
using System;
using System.Collections.Generic;
using System.Text;
using Microsoft.Office.Excel.Server.Udf;
using System.Data.SqlClient;
using System.Web;
using System.Security.Principal;
namespace DatabaseAccessUdfTest1
{
[UdfClass]
public class
{
[UdfMethod(IsVolatile=true)]
public string GetRowCount()
{
try
{
SqlConnection sqlConnection = new SqlConnection
("Data Source=myDatabaseServer002;Initial
Catalog=northwind;Integrated Security=SSPI;");
SqlCommand sqlCommand = new SqlCommand("SELECT COUNT(*)
FROM Customers", sqlConnection);
sqlConnection.Open();
string rowCount = (string)sqlCommand.ExecuteScalar();
sqlConnection.Close();
return (rowCount);
}
catch (Exception e)
{
return (e.ToString());
}
}
[UdfMethod(IsVolatile=true)]
public string GetSqlUserName()
{
try
{
SqlConnection sqlConnection = new SqlConnection("Data
Source= myDatabaseServer003;Initial
Catalog=northwind;Integrated Security=SSPI;");
SqlCommand sqlCommand = new SqlCommand("SELECT
CURRENT_USER", sqlConnection);
sqlConnection.Open();
string userName = (string)sqlCommand.ExecuteScalar();
sqlConnection.Close();
return (userName);
}
catch (Exception e)
{
return (e.ToString());
}
}
[UdfMethod(ReturnsPersonalInformation=true)]
public string GetUserName()
{
return
(System.Threading.Thread.CurrentPrincipal.Identity.Name);
}
[UdfMethod(ReturnsPersonalInformation=true)]
public string GetUserAuthenticationType()
{
return
(System.Threading.Thread.CurrentPrincipal.Identity.AuthenticationType);
}
}
}
Imports System
Imports System.Collections.Generic
Imports System.Text
Imports Microsoft.Office.Excel.Server.Udf
Imports System.Data.SqlClient
Imports System.Web
Imports System.Security.Principal
Namespace DatabaseAccessUdfTest1
<UdfClass> _
Public Class
<UdfMethod(IsVolatile:=True)> _
Public Function GetRowCount() As String
Try
Dim sqlConnection As New SqlConnection("Data Source=myDatabaseServer002;Initial Catalog=northwind;Integrated Security=SSPI;")
Dim sqlCommand As New SqlCommand("SELECT COUNT(*) FROM Customers", sqlConnection)
sqlConnection.Open()
Dim rowCount As String = CStr(sqlCommand.ExecuteScalar())
sqlConnection.Close()
Return (rowCount)
Catch e As Exception
Return (e.ToString())
End Try
End Function
<UdfMethod(IsVolatile:=True)> _
Public Function GetSqlUserName() As String
Try
Dim sqlConnection As New SqlConnection("Data Source= myDatabaseServer003;Initial Catalog=northwind;Integrated Security=SSPI;")
Dim sqlCommand As New SqlCommand("SELECT CURRENT_USER", sqlConnection)
sqlConnection.Open()
Dim userName As String = CStr(sqlCommand.ExecuteScalar())
sqlConnection.Close()
Return (userName)
Catch e As Exception
Return (e.ToString())
End Try
End Function
<UdfMethod(ReturnsPersonalInformation:=True)> _
Public Function GetUserName() As String
Return (System.Threading.Thread.CurrentPrincipal.Identity.Name)
End Function
<UdfMethod(ReturnsPersonalInformation:=True)> _
Public Function GetUserAuthenticationType() As String
Return (System.Threading.Thread.CurrentPrincipal.Identity.AuthenticationType)
End Function
End Class
End Namespace
See Also
Tasks
How to: Create a UDF That Calls a Web Service
Concepts
Walkthrough: Developing a Managed-Code UDF
Frequently Asked Questions About Excel Services UDFs