Access results of SQL Server stored functions in C#

I have created a few stored functions in SQL Server that return a t开发者_运维百科able via a select statement. Like so:

CREATE FUNCTION [dbo].[mFunSelectStudents] ()
RETURNS @result TABLE
    (IDStudent int, 
     Name nchar(50),
     Password nchar(50))
AS 
BEGIN
    INSERT INTO @result select * from School.dbo.Student
    RETURN    
END

I tried to assign the function to an SqlDataAdapter in c# like this:

SqlCommand cmd = new SqlCommand("mFunSelectStudents", con);
SqlDataAdapter adpStudents = new SqlDataAdapter();
adpStudents.SelectCommand = cmd;

But this doesn't work..

Where @result is a return parameter of the stored function. Now, how do I call the function in C# and assign the data to a grid ?

Any help is appreciated..

The command cannot be just the name of the function. You are supposed to put a SQL command there, and in SQL one retrieves data from a TVF by SELECTing from it, like this:

SELECT * FROM dbo.mFunSelectStudents()

Consequently, the first line of your C# code snippet should be:

SqlCommand cmd = new SqlCommand("SELECT * FROM dbo.mFunSelectStudents()", con);

Wrap the function in a stored procedure, or do the work itself in a SP. The results of a single select statement will be accessible as a DataTable in the C# client.

      create proc  selectSomeData
       .
       .
       .

In the client, your commandType would be StoredProcedure and the CommandText would be the name of the sp.

Your first line :

SqlCommand cmd = new SqlCommand("mFunSelectStudents", con);

Is correct, however this one you should check it

SqlDataAdapter adpStudents.SelectCommand = cmd;

First you need to use new with the SqlDataAdapter before you can assign the selectCommand, as follows:

SqlDataAdapter adpStudents = new SqlDataAdapter(cmd);

Assign the command to the DataAdapter

adpStudents.SelectCommand = cmd;

And then you prepare a Dataset

DataSet ds = new DataSet();

Fill it with your DataAdapter

adpStudents.Fill(ds);

Assign it to your grid

gridName.DataSource = ds;

And call DataBind to update the info on the grid

gridName.DataBind();