SQL CLR UDF returning result set


Note: I have moved to blog at my own domain, so kindly visit this post over there for recent updates or comments. http://www.imranbalouch.com/blog/index.php/2010/05/sql-clr-udf-returning-result-set/

SQL CLR Integration is a very nice feature provided by Microsoft especially if you need to do some complex calculations or deal with objects.

Today we will be creating a User Defined Function,UDF, using CLR, which will return a result set/table and hence will be a Table Value Function, TVF.

Our UDF will be accepting a query parameter, which will hit database and get some XML values and later on deserialize them and return the values in table.

First of all in VS create a SQL Project and add a User Defined Function in it.

public partial class UserDefinedFunctions
{
[Microsoft.SqlServer.Server.SqlFunction(DataAccess = DataAccessKind.Read,
FillRowMethodName = “getDeserializedData_Fill”,
// define columns returned
TableDefinition = “ResquestId bigint, RequestDetailId bigint, Name nvarchar(50), Address nvarchar(50 “
)]
public static IEnumerable getDeserializedData(String sqlQuery)
{
ArrayList resultCollection = new ArrayList();
if (sqlQuery == null || sqlQuery == “”)
{
sqlQuery = “SELECT TOP (10) R.REQUESTID AS REQUESTID, R.REQUESTTYPEID_FK AS REQUESTTYPEID, REQUESTDETAILID, RD.XMLPACKET FROM REQUESTS1 R WITH (NOLOCK)JOIN REQUESTDETAILS1 RD WITH (NOLOCK) ON RD.REQUESTID_FK = R.REQUESTID WHERE RD.REQUESTSTATUSIDINTERNAL_FK = 1”;
}
using (SqlConnection conection = new SqlConnection(“context connection = true;”))
using (SqlCommand command = new SqlCommand(sqlQuery, conection))
{
conection.Open();
SqlDataReader dReader = command.ExecuteReader();
if (dReader != null && dReader.HasRows)
{
DataTable dTable = new DataTable();
dTable.Load(dReader);
foreach(DataRow dRow in dTable.Rows)
{
resultCollection.Add(GetGenericClass(dRow));
}
}
}
return resultCollection;
}

In above code we created a UDF with DataAccessKind.Read to tell that our function will be accessing data only to read it, Fill row method tells the name of method which will be populating the rows of our table and Table Definition tells that which columns will be present in the returning table.

getDeserializedData is our main function that will accept a String type parameter named query, they query which will get data from database. Any UDF that is a TVF must have return type as IEnumerable. Laterally we opened a connection and fetched data from database, and pass one by one row to our GetGenericClass method which return an object of our generic class which we than add in to an ArrayLIst. Our GenricClass is as

using System;
using System.Collections.Generic;
using System.Text;
namespace CLRSQLInregration
{
[Serializable]
public class GenericClass
{
public Int64 ResquestId
{
get;
set;
}
public Int64 RequestDetailId
{
get;
set;
}
public String Name
{
get;
set;
}
public String Address
{
get;
set;
}
}
}

Another Class that we will use is CustomerClass like:

using System;
using System.Collections.Generic;
using System.Text;
namespace CLRSQLInregration
{
[Serializable]
public class CustomerClass
{
public String Name
{
get;
set;
}
public String Address
{
get;
set;
}
}
}

We have marked the class as Serializable, so that we can serialize and deserialize the objects of this class.

Our GetGenericClass method is like:

public static GenericClass GetGenericClass(DataRow dRow)
{
GenericClass objGeneric = new GenericClass();
objGeneric.ResquestId = Convert.ToInt64(dRow[“REQUESTID”]);
objGeneric.RequestDetailId = Convert.ToInt64(dRow[“REQUESTDETAILID”]);
InternalResponse objResponse = null;
if (dRow[“XMLPACKET “] != DBNull.Value)
objResponse = (CustomerClass)Serializer.DeSerializeObject(Convert.ToString(dRow[“XMLPACKET “]), typeof(CustomerClass));
if (objResponse != null)
{
objGeneric.Name = objResponse.Name;
objGeneric.Address = objResponse.Address;
}
}

This method simply deserializes the XML Packet in the row and take values from the object and assign values to our GenericClass object.

Our datafill method is be like:

public static void getDeserializedData_Fill(object obj, out Int64 ResquestId, out Int64 RequestDetailId, out String Name, out String Address)
{
GenericClass objResponse = (GenericClass)obj;
if (objResponse != null)
{
ResquestId = objResponse.ResquestId;
RequestDetailId = objResponse.RequestDetailId;
Name = objResponse.Name;
Address = objResponse.Address;
}
}

This fill method will execute when return resultCollection; code of getDeserializedData method is called.

After all this u need to deploy the CLR assembly and call the UDF and here you go.

Whenever Serialization is involved in CLR, we need to create a Serializer class for our project as well, which can be created by using sgen.exe file of VS or in project properties, go to build events and in post build event command line specify the following line:

“D:Program FilesMicrosoft Visual Studio 8SDKv2.0Binsgen.exe” /force “$(TargetPath)”

It will create the serializer class for your project which you have to add in your CLR assemblies of SQL.

Advertisements

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

%d bloggers like this: