SQL CLR function showing error when i am fetching

Question

Hi am fetching data from sharepoint 2010 list by asmx webservices but when i there is no data in any column it says below error on sql: Lets says "ows_ClientContactPerson" column has not any record then it will give me below error if has then it will not give me error..

Msg 6260, Level 16, State 1, Line 2
An error occurred while getting new row from user defined Table Valued Function : 
System.ArgumentException: Column 'ows_ClientContactPerson' does not belong to table  row.
System.ArgumentException: 
 at System.Data.DataRow.GetDataColumn(String columnName)
at System.Data.DataRow.get_Item(String columnName)
at SharePointClients.GetClientsItemInfo(Object obj, SqlString& ID, SqlString& Title,     SqlString& ParentClientVertical, SqlString& NoOfStores, SqlString& Complexity, SqlString&  Location, SqlString& ClientContactPerson, SqlString& IsActive).

Please see my C# code from when i am creating my assemblies.

using System;
using System.Collections;
using System.Text;
using System.Data;
using System.Data.SqlTypes;
using Microsoft.SqlServer.Server;
using System.Security.Principal;
using System.Net;
using System.Xml;

public class SharePointClients
{
[SqlFunction(SystemDataAccess = SystemDataAccessKind.Read, FillRowMethodName = "GetClientsItemInfo")]
public static IEnumerable GetClientsItems(SqlString url, SqlString listName, SqlString viewName)
{
    DataTable t = new DataTable();
    WindowsImpersonationContext ctx = null;

    WindowsIdentity id = SqlContext.WindowsIdentity;

    try
    {
        ctx = id.Impersonate();

        WSS.Lists svc = new WSS.Lists();
        svc.Url = url.ToString();

        //svc.Credentials = new NetworkCredential("barley", "pass@word1", "VS");
        svc.Credentials = CredentialCache.DefaultNetworkCredentials;
        XmlNode node = svc.GetListItems(listName.ToString(), viewName.ToString(), null, null,"150000", null, null);
        XmlTextReader rdr = new XmlTextReader(node.OuterXml,
                                    XmlNodeType.Element, null);
        DataSet ds = new DataSet();
        ds.ReadXml(rdr);
        t = ds.Tables[1];
    }
    finally
    {
        if (ctx != null)
            ctx.Undo();
    }

    return t.Rows;
}

public static void GetClientsItemInfo(
                    object obj,
                    out SqlString ID,
                    out SqlString Title,
                    out SqlString ParentClientVertical,
                    out SqlString NoOfStores,
                    out SqlString Complexity,
                    out SqlString Location,
                    out SqlString ClientContactPerson,
                    //out SqlString ClientContactEmailID,
                    //out SqlString PhoneNumber,
                    out SqlString IsActive)

{
    DataRow r = (DataRow)obj;
    ID = new SqlString(Convert.ToString(r["ows_ID"]));
    Title = new SqlString(Convert.ToString(r["ows_Title"]));
    ParentClientVertical = new SqlString(Convert.ToString(r["ows_ParentClientVertical"]));
    NoOfStores = new SqlString(Convert.ToString(r["ows_NoOfStores"]));
    Complexity = new SqlString(Convert.ToString(r["ows_Complexity"]));
    Location = new SqlString(Convert.ToString(r["ows_Location"]));
    ClientContactPerson = new SqlString(Convert.ToString(r["ows_ClientContactPerson"]));
    //ClientContactEmailID = new SqlString(Convert.ToString(r["ows_ClientContactEmailID"]));
    //PhoneNumber = new SqlString(Convert.ToString(r["ows_PhoneNumber"]));
    IsActive = new SqlString(Convert.ToString(r["ows_IsActive"]));
}
}

Please see below my SQL function creation code

/****** Object:  UserDefinedFunction [dbo].[fn_GetListItemsClients]    Script Date: 10/19/2012 05:45:43 ******/
CREATE FUNCTION [dbo].[fn_GetListItemsClients](@SiteUrl [nvarchar](256), @ListName [nvarchar](256), @viewName [nvarchar](256))
RETURNS  TABLE (    
[ID] [nvarchar](50) NULL,
[Title] [nvarchar](256) NULL,
[ParentClientVertical] [nvarchar](256) NULL,
[NoOfStores] [nvarchar](256) NULL,
[Complexity] [nvarchar](256) NULL,
[Location] [nvarchar](256) NULL,
[ClientContactPerson] [nvarchar](256) NULL,
--[ClientContactEmailID] [nvarchar](70) NULL,
--[PhoneNumber] [nvarchar](70) NULL,
[IsActive] [nvarchar](256) NULL

) WITH EXECUTE AS CALLER
AS 
EXTERNAL NAME [PMTFunction].[SharePointClients].[GetClientsItems]
GO

and please find below where i am using select command

 select * from    dbo.fn_GetListItemsClients('http://wks10953:1000/_vti_bin/Lists.asmx','Clients','')

This is really important, Please help me..

Answer 1

Check if DataRow contains required column as follows, use follow code base for each column to avoid future errors.

if (r.Table.Columns.Contains("ows_ClientContactPerson"))

ClientContactPerson = new SqlString(Convert.ToString(r["ows_ClientContactPerson"]));

else

ClientContactPerson = new SqlString(string.Empty);