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

In a similar article I detailed how I was doing programmatic access of Microsoft SQL Server. However, on another project I was creating a class in App_Code. Using my method required the use of a couple additional namespaces from System.Web. That seemed a bit excessive.

So I did some digging around and came up with what I believe is a better solution, for use in classes/non-Web code.

First, the following must be included in.

// For DataTable
using System.Data;
// For SqlConnection, SqlCommand, SqlParameter, SqlDataAdapter
using System.Data.SqlClient;
// For ConfigurationManager
using System.Configuration;

Next we have the code, with some dummy data.

public DataTable returnDataTable(int Id) {
	DataTable returnValue = new DataTable();

	SqlConnection connection = new SqlConnection();
	connection.ConnectionString = ConfigurationManager.ConnectionStrings["connectionString"].ConnectionString;
	SqlCommand command = new SqlCommand();
	command.Connection = connection;
	command.CommandType = CommandType.StoredProcedure;
	command.CommandText = "stored_proc";
	command.Parameters.Add(new SqlParameter("@id", Id));
	SqlDataAdapter adapter = new SqlDataAdapter(command);
	adapter.Fill(returnValue);

	adapter = null;
	command = null;
	connection.Close();
	connection = null;

	return returnValue;
}

This seems to be a bit better than what I had.

Or not? Let me know.

Search
Links of Note
Recent Posts
categories
Support This Site

If my blog was helpful to you, then please consider visiting my Amazon Wishlist.