Table Valued Function - getting the code for it to work

Question

Table Valued Function - getting the code for it to work

kayuca

Old Hand

Points: 356
More actions
May 14, 2008 at 3:43 pm

#187687

On page 82 of the book is code to define a table valued function. I cannot get it to work using VS 2008 Professional. Any one help? I have tried going to the Wrox Site with previous questions, but have given up on them because neither the authors or anyone else offered any help.
Specifically: here are the lines on page 82 that I am having trouble with.
1.The line: public static IEnumerable ... needs something to work, but I don't know what to do with it. At build time I get the following error message:
Error1Using the generic type 'System.Collections.Generic.IEnumerable ' requires '1' type arguments
2.The line: if (File.Exists(FilePath)) There is something wrong because "File.Exists" never is offered by intellisense. I'm probably missing something but don't know what.
Help on either of the above please.
---------------------------------------------------------
Additionally, I have found after a lot of head scratching the following fixes to this code.
1.First line: namespace SQL CLRRoutines.FileIO - I'm guessing that there should be a period between SQL and CLR... but that is a guess. This is screwy because SQL.CLRRoutines.FileIO is never used.
2.There is a missing using statement for System.Collections.Generic, othewise you cant even reference IEnumerable.
3.The decoraton statement [Microsoft.SqlServer.Server.SqlFunction... ] doesn't work typed exactly as shown. Type it all on one line and omit the plus sign.
Victor Victor (kayuca)
After submitting above, I took a guess at what IEnumerable wants and changed that line to
IEnumerable which I believe it wants to enumerate thru -- strings. Yeah - It compiles. That's the good news. The bad news is I get the orignal error when I try to deploy it. HELP!!
Following is my complete code for this tvf:
using System;
using System.Data;
using System.Data.SqlClient;
using System.Data.SqlTypes;
using Microsoft.SqlServer.Server;
using System.Collections.Generic;
namespace SQL.CLRRoutines.FileIO
{
sealed public partial class TableValuedFunctions //Sealed means the class cannot be inherited.
{
[Microsoft.SqlServer.Server.SqlFunction(FillRowMethodName="tvf_ParseFile_FillRow",TableDefinition="Item NVARCHAR(4000)")]
public static IEnumerable tvf_ParseFile(String FilePath, char Delimiter)
{
//Declare local variables
String[] sItems;
System.IO.StreamReader oSR;
//if (FilePath.Exists(FilePath))
if(FilePath.Length > 0)
{
oSR = System.IO.File.OpenText(FilePath);
//parse file
sItems = oSR.ReadToEnd().Split(Delimiter);
//close file.
oSR.Close();
//return items.
return (sItems);
}
else
{
throw new System.Exception("File not found");
}
}//end of method.
private static void tvf_ParseFile_FillRow(Object obj, out SqlString sItem)
{
String sTemp = (string)obj;//convert object.
sItem = sTemp; //assign value to field.
}//end of method.
}//end of class.
}//end of namespace.

Viewing 4 posts - 1 through 3 (of 3 total)

You must be logged in to reply to this topic. Login to reply

Jonathan Kehayias One Orange Chip Points: 26778 More actions · Answer 1

I know this is an old post, but I can see it was never replied to. That book is full of syntax errors, and typos. I know because I own it. There are also examples in it that don't follow best practice implementations. The correct code for the example that you asked about can be found in the code download for the book:

using System;

using System.Data;

using System.Data.SqlClient;

using System.Data.SqlTypes;

using Microsoft.SqlServer.Server;

using System.Collections;

using System.IO;

namespace SQLCLRRoutines.FileIO

{

sealed public partial class TableValuedFunctions

{

[Microsoft.SqlServer.Server.SqlFunction(FillRowMethodName="tvf_ParseFile_FillRow",

TableDefinition="Item NVARCHAR(4000)")]

public static IEnumerable tvf_ParseFile(String FilePath, char Delimiter)

{

//declare local variables

String[] sItems;

System.IO.StreamReader oSR;

//does file exist?

if (File.Exists(FilePath))

{

//open file

oSR = System.IO.File.OpenText(FilePath);

//parse file

sItems = oSR.ReadToEnd().Split(Delimiter);

//close file

oSR.Close();

//return items

return(sItems);

}

else

{

throw new System.Exception("FILE NOT FOUND");

}

private static void tvf_ParseFile_FillRow(Object obj, out SqlString sItem)

{

//convert object

String sTemp = (string)obj;

//assign value to field

sItem = sTemp;

}

};

The link to the code is:

http://www.wrox.com/WileyCDA/WroxTitle/productCd-0470054034,descCd-DOWNLOAD.html

Hope that helps you or anyone else looking at this later.

kayuca Old Hand Points: 356 More actions · Answer 2

Jonathon: Very glad to finally get some help on this. I set the book aside and am in the MCTS Training Kit. The kit kind of sidesteps CLR programming and I will have to go along with that. But in the real world CLR is going to see more and more activity so I will store your code suggestion until then when I will want to get up to speed on the latest techniques. Thanks very much. Victor Victor

Jonathan Kehayias One Orange Chip Points: 26778 More actions · Answer 3

CLR is already getting more interest, but from experience answering questions, most of it is misplaced. If you can do it in TSQL, you shouldn't use CLR. There are exceptions, but the vast majority of things I see attempted in CLR, can be done faster with TSQL. Unless you would have written an unmanaged Extended Procedure to do it in SQL 2000, you probably shouldn't use CLR for it either. That is my opinion at least.