How to execute a table-valued function in ado.net?

Question

I am using ado.net.

I have a function jsp in my database that takes 2 parameters and returns a table. I need to prompt the user for the two parameters, then execute the jsp function and print the table to the screen. Here is what I currently have:

jspCmd = new SqlCommand(jspStmt, conn);
jspCmd.CommandType = CommandType.StoredProcedure;

jspCmd.Parameters.Add("@snum", SqlDbType.VarChar, 5);
jspCmd.Parameters.Add("@pnum", SqlDbType.VarChar, 5);
jspCmd.Prepare();

Console.WriteLine();
Console.WriteLine(@"Please enter S# and P# separated by blanks, or exit to terminate");
string line = Console.ReadLine();
Regex r = new Regex("[ ]+");
string[] fields = r.Split(line);

if (fields[0] == "exit") break;
jspCmd.Parameters[0].Value = fields[0];
jspCmd.Parameters[1].Value = fields[1];

jspCmd.ExecuteNonQuery();//<---I BELIEVE ERROR COMING FROM HERE

reader = jspCmd.ExecuteReader();//PRINT TABLE TO SCREEN
while (reader.Read())
{
    Console.WriteLine(reader[0].ToString() + "  "
                      + reader[1].ToString()
                      + "  " + reader[2].ToString());
}
reader.Close();

When I run this, I enter the two params and an exception is raised:

Program aborted: System.Data.SqlClient.SqlException (0x80131904): The request
for procedure 'jsp' failed because 'jsp' is a table valued function object.

Can anyone show me the correct way to do this?

Indigotin

Answer 1

Make sure your jspStmt is a SELECT with regular parameter binding, eg:

var jspStmt = "SELECT * FROM myfunction(@snum, @pnum)";
// this is how table-valued functions are invoked normally in SQL.

Omit the following:

jspCmd.CommandType = CommandType.StoredProcedure; 
// WRONG TYPE, leave it as CommandType.Text;

Omit the following:

jspCmd.ExecuteNonQuery();//<---I BELIEVE ERROR COMING FROM HERE
// WRONG KIND OF RESULT, it **IS** a query.  Further, let your
// later jspCmd.ExecuteReader() invoke it and get the actual data.

Plethora

Answer 2

To execute a table-valued function use SELECT as a text command:

jspCmd = new SqlCommand("SELECT * FROM " + jspStmt + "()", conn);
jspCmd.CommandType = CommandType.Text;

And to get the results use ExecuteReader - which you already do but after you use ExecuteNonQuery, which is for INSERTs, UPDATEs, etc.

Comedo

I tried this and I am getting a new exception. My jspStmt = "jsp" because that is the name of the function. And the Exception is: Program aborted: System.Data.SqlClient.SqlException (0x80131904): Invalid column name 'jsp'. Statement(s) could not be prepared. – Indigotin

@user3345200 forgot to add parentheses as well - try now. – Comedo

I added the ( ) and it now changed to: Program aborted: System.Data.SqlClient.SqlException (0x80131904): 'jsp' is not a recognized built-in function name. Statement(s) could not be prepared. ----jsp is in my procedures folder for my database. Any idea why it would give me this? – Indigotin

I strongly suggest that you simplify things. Use SQL Server Management studio to produce a valid SELECT query for this function. Something like SELECT * FROM dbo.jsp() or SELECT * FROM dbo.jsp(1, 'string', '2014-03-29'). Then work on creating that same query in your code. – Partain

Answer 3

To add to D Stanley's answer, it looks like the new exceptions you are getting are due to incorrectly calling the function. Try the following (corrected the select statement and added parameters to the function):

jspCmd = new SqlCommand("SELECT * FROM jsp('" + fields[0] + "', '" + fields[1] + "')", conn);

Then proceed to use ExecuteReader like you did.

Humic

This is unsafe. By not parameterizing the SQL statement properly, you risk a SQL injection attack. See David Bullock's answer for a proper example. – Nehru