Programmatic MSSQL data source in ASP.NET (C#)

I keep having to search through code to find it, so, since writing about it makes it easier for me to find ... here's how I've been programmatically making calls to Microsoft SQL Server.

If I'm doing something wrong, please comment below or send me an email. Some times have been changed to dummy values.


SqlDataSource dataSource = new SqlDataSource();
dataSource.ConnectionString = ConfigurationManager.ConnectionStrings["ConnectionString"].ConnectionString;
dataSource.SelectCommand = "stored_proc";
dataSource.SelectCommandType = SqlDataSourceCommandType.StoredProcedure;
dataSource.SelectParameters.Add("id", value);
dataSource.CacheDuration = 3600;

DataView viewData = (DataView)dataSource.Select(DataSourceSelectArguments.Empty);
DataTable tableData = viewData.ToTable();

//Stuff

tableData = null;
viewData = null;
dataSource = null;

From what I can tell, there's no close by doing a select this way; the connection must close after it's performed?

Update June 6 2009:

To use this code you would also need to include the following, if they were not already included.

// Need to add for SqlDataSource
using System.Web.UI.WebControls;
// Need for ConfigurationManager
using System.Configuration;
// Need for DataView
using System.Data;
// For DataSourceSelectArguments
using System.Web.UI;

Because of this, there may be another way to do this, that doesn't require these additions.