Member Avatar for Nfurman

Hello!

I've got trivial problem and have no time to re-read manuals. I need to construct a function in SQL2005 which selects from DB and returns datatable. Something like this:

create function GetAllStudents()
returns Table
as
begin
(
select StudentID,StudentName,StudentFamilyName,Birthday,HisClass
from Students
)
return DataTable

I need it to bind then to bind to DataView Control on my ASP.Net 2.0 page.

Any help and advice?

  • 2 Contributors
  • 1 Reply
  • 123 Views
  • 8 Months Discussion Span
  • Latest Post Latest Post by noamwegner

Dani AI

Generated

Short answer: either a stored procedure or a table-valued function (TVF) will give you a result set that you can turn into a .NET DataTable and then a DataView. is right that a stored procedure is the simplest route from ASP.NET, but an inline TVF is useful when you want to join or compose the result inside other SQL. See Microsoft documentation for syntax and limitations: CREATE PROCEDURE (Transact-SQL) and .

Key distinctions and practical notes:

  • SQL functions return a table result set on the server; they do not produce a .NET DataTable object. The client code must fetch the result set (for example, with ADO.NET) and populate a DataTable.
  • Inline TVFs are "inlined" by the optimizer and are generally cheaper than multi-statement TVFs in SQL Server 2005. Functions cannot perform side effects (no permanent DML or EXEC of stored procs, and limited support for dynamic SQL).
  • Stored procedures are easy to call from ADO.NET (CommandType = StoredProcedure) and work well for straightforward selects returned to a DataTable.

Consumption and binding tips for ASP.NET 2.0:

  • Use schema-qualified object names (dbo.GetAllStudents). Parameterize inputs; avoid SELECT *; return only needed columns.
  • Fill a DataTable with a SqlDataAdapter or use a SqlDataSource control to avoid manual plumbing. Convert to a DataView via DataTable.DefaultView and assign it to the control’s DataSource, then call DataBind().
  • Use using blocks (or otherwise dispose connections/commands/adapters) and catch exceptions for connection/permission issues.

Troubleshooting checklist:

  • If nothing is returned, verify object name, schema, and permissions. TVFs must be referenced as SELECT * FROM dbo.YourTvf(...), not EXEC.
  • If performance is poor with a TVF, try rewriting as an inline TVF or stored proc and profile with execution plans.
  • For WebForms binding issues, ensure DataBind is called and DataSourceID conflicts aren’t present.

For the ADO.NET fill/bind pattern and the SqlDataAdapter.Fill behavior, see .

Member Avatar for noamwegner

why dont you use a stored proc
CREATE PROC blahProcName ()
{
AS
select StudentID,StudentName,StudentFamilyName,Birthday,HisClass
from Students
}
and on the server side
using system.data.sqlclinet;

sqlconnection conn = new sqlconnection(connectionstring);
conn.open;
sqlcommand cmd = new sqlcommand("blahProcName",conn);
cmd.commandType = commandType.storedproc;
dateTable dt = new dataTable("mytable");
sqlAdapter sa = new sqlAdapter (cmd);
sa.fill(dt);
conn.close();
cmd.dispose();
sa.dipose();
then you can bind to the datatable
there is no need to use a function here and its very easy to use
hope it helped

Edited by noamwegner because: n/a
Be a part of the DaniWeb community

We're a friendly, industry-focused community of developers, IT pros, digital marketers, and technology enthusiasts meeting, networking, learning, and sharing knowledge.