ADO.NET SQL Server - DataTable And DataSet

Here are a couple of quick methods in C# for creating a DataSet and a DataTable from a SQL Server stored procedure. Use a data table when you don't need the full flexibility of a dataset. Much better performance.

Here are a couple of quick methods in C# for creating a DataSet and a DataTable from a SQL Server stored procedure. Use a data table when you don't need the full flexibility of a dataset. Much better performance. These methods and others like them will give you the power you need while closing the database connection as soon as possible. Add in your own personal error handling of course...Also, it is good practice to include the owner of the procedure in your calls. This way, SQL Server doesn't have to do it for you each time prior to running the procedure. A minor performance gain.

sample:

DataTable oTable = this.GetDataTable("dbo.myprocname '" + sStringVariable + "'," + nInteger.ToString() + ")");

foreach(DataRow oRow in oTable.Rows)
{
Response.Write(oRow["mycolumnname"].ToString());
}


public DataSet GetDataSet(string sQuery,string sConnectionString)
{

SqlConnection oConn = new SqlConnection();
DataSet oRS = new DataSet();

try
{
oConn.ConnectionString = sConnectionString;
oConn.Open();
SqlDataAdapter oDA = new SqlDataAdapter("EXEC " + sQuery,oConn);
oDA.Fill(oRS);
oConn.Close();
}
catch (Exception e) { }
finally { if (oConn.State == ConnectionState.Open) { oConn.Close(); } }

return oRS;
}

public DataTable GetDataTable(string sQuery,string sConnectionString)
{

SqlConnection oConn = new SqlConnection();
DataTable oRS = new DataTable();

try
{
oConn.ConnectionString = sConnectionString;
oConn.Open();
SqlDataAdapter oDA = new SqlDataAdapter("EXEC " + sQuery,oConn);
oDA.Fill(oRS);
oConn.Close();
}
catch (Exception e) { }
finally{ if (oConn.State == ConnectionState.Open) { oConn.Close(); } }

return oRS;
}


Submission Date:  9/23/2005 2:59:15 PM
Submitted By:  Robbe Morris
My Home Page:  http://www.robbemorris.com

By Robbe Morris   Popularity  (942 Views)
Picture
Biography - Robbe Morris
Robbe has been a Microsoft MVP in C# since 2004. He is also the co-founder of NullSkull.com which provides .NET articles, book reviews, software reviews, and software download and purchase advice.  Robbe also loves to scuba dive and go deep sea fishing in the Florida Keys or off the coast of Daytona Beach. Microsoft MVP