ASP.NET Example – Populating a DataSet Using SQLDataAdapter Fill DataSet

In the example functions below I’m illustrating how to set up a basic query to a stored procedure in your database that takes a single string parameter. The examples involve using the SQLDataAdapter object to Fill a DataSet. The resulting DataTable can then be queried as desired. Specifically:

1. The first example shows how to read the first row of the results after your Datatable has been populated. I provide the same example in VB.NET and C#.
2. The second example shows how to loop through the results if you anticipate multiple returned rows. As before,  I provide the same example in VB.NET and C#.

As part of this code, remember to include the correct .NET Namespaces. If you are writing your code inline (in either VB.NET or C#), use:

<%@ Import Namespace="System.Data" %>
<%@ Import Namespace="System.Data.SqlClient" %>

and if you are writing your code in codebehind, use:

VB Codebehind:

Imports System.Data
Imports System.Data.SqlClient

C# Codebehind:

using System.Data;
using System.Data.SqlClient;

Also, navigate here if you are interested in further articles I have written about ADO in .NET.

Read the First Row of the Results – VB.NET

Public Function GetUserShoeSize(ByVal strFromId As String) As String
 Dim returnValue As String = String.Empty
 Dim ds As New DataSet()
 Dim dt As New DataTable()
 Dim conn As SqlConnection = Nothing
 Dim da As SqlDataAdapter = Nothing
 Dim cmd As SqlCommand = Nothing
 Try
 Dim paramOne As New SqlParameter()
 paramOne.ParameterName = "ClientId"
 paramOne.DbType = DbType.String
 paramOne.Value = strFromId
 conn = New SqlConnection(ConfigurationManager.ConnectionStrings("ConnectionStringFromConfig").ConnectionString)
 conn.Open()
 cmd = New SqlCommand()
 cmd.Connection = conn
 cmd.CommandType = CommandType.StoredProcedure
 cmd.CommandText = "myStoredProcedure"
 cmd.Parameters.Add(paramOne)
 da = New SqlDataAdapter()
 da.SelectCommand = cmd
 da.Fill(ds)
 dt = ds.Tables(0)
 returnValue = dt.Rows(0).Item("ShoeSize")
 Catch ex As Exception
 returnValue = "Unknown Size"
 Finally
 If Not ds Is Nothing Then ds.Dispose()
 ds = Nothing
 If Not da Is Nothing Then da.Dispose()
 da = Nothing
 If Not cmd Is Nothing Then cmd.Dispose()
 conn.Close()
 conn.Dispose()
 conn = Nothing
 End Try
 Return returnValue
 End Function

Read the First Row of the Results – C#

public string GetUserShoeSize(string strFromId)
 {
 string returnValue = string.Empty;
 DataSet ds = new DataSet();
 DataTable dt = new DataTable();
 SqlConnection conn = null;
 SqlDataAdapter da = null;
 SqlCommand cmd = null;
 try
 {
 SqlParameter paramOne = new SqlParameter();
 paramOne.ParameterName = "ClientId";
 paramOne.DbType = DbType.String;
 paramOne.Value = strFromId;
 conn = new SqlConnection(ConfigurationManager.ConnectionStrings["ConnectionStringFromConfig"].ConnectionString);
 conn.Open();
 cmd = new SqlCommand();
 cmd.Connection = conn;
 cmd.CommandType = CommandType.StoredProcedure;
 cmd.CommandText = "myStoredProcedure";
 cmd.Parameters.Add(paramOne);
 da = new SqlDataAdapter();
 da.SelectCommand = cmd;
 da.Fill(ds);
 dt = ds.Tables[0];
 returnValue = (string)dt.Rows[0]["ShoeSize"];
 }
 catch (Exception ex)
 {
 returnValue = "Unknown Size";
 }
 finally
 {
 if ((ds != null))
 ds.Dispose();
 ds = null;
 if ((da != null))
 da.Dispose();
 da = null;
 if ((cmd != null))
 cmd.Dispose();
 conn.Close();
 conn.Dispose();
 conn = null;
 }
 return returnValue;
 }

Loop Through Multiple Rows of Results – VB.NET

Public Function GetUserShoeSize(ByVal strFromId As String) As String
 Dim returnValue As String = String.Empty
 Dim ds As New DataSet()
 Dim dt As New DataTable()
 Dim conn As SqlConnection = Nothing
 Dim da As SqlDataAdapter = Nothing
 Dim cmd As SqlCommand = Nothing
 Try
 Dim paramOne As New SqlParameter()
 paramOne.ParameterName = "ClientId"
 paramOne.DbType = DbType.String
 paramOne.Value = strFromId
 conn = New SqlConnection(ConfigurationManager.ConnectionStrings("ConnectionStringFromConfig").ConnectionString)
 conn.Open()
 cmd = New SqlCommand()
 cmd.Connection = conn
 cmd.CommandType = CommandType.StoredProcedure
 cmd.CommandText = "myStoredProcedure"
 cmd.Parameters.Add(paramOne)
 da = New SqlDataAdapter()
 da.SelectCommand = cmd
 da.Fill(ds)
 dt = ds.Tables(0)
  For Each dr As DataRow In dt.Rows
 Dim tmpShoeSize = dr.Item("ShoeSize")
 Next
 Catch ex As Exception
 returnValue = "Unknown Size"
 Finally
 If Not ds Is Nothing Then ds.Dispose()
 ds = Nothing
 If Not da Is Nothing Then da.Dispose()
 da = Nothing
 If Not cmd Is Nothing Then cmd.Dispose()
 conn.Close()
 conn.Dispose()
 conn = Nothing
 End Try
 Return returnValue
 End Function

Loop Through Multiple Rows of Results – C#

public string GetUserShoeSize(string strFromId)
 {
 string returnValue = string.Empty;
 DataSet ds = new DataSet();
 DataTable dt = new DataTable();
 SqlConnection conn = null;
 SqlDataAdapter da = null;
 SqlCommand cmd = null;
 try
 {
 SqlParameter paramOne = new SqlParameter();
 paramOne.ParameterName = "ClientId";
 paramOne.DbType = DbType.String;
 paramOne.Value = strFromId;
 conn = new SqlConnection(ConfigurationManager.ConnectionStrings["ConnectionStringFromConfig"].ConnectionString);
 conn.Open();
 cmd = new SqlCommand();
 cmd.Connection = conn;
 cmd.CommandType = CommandType.StoredProcedure;
 cmd.CommandText = "myStoredProcedure";
 cmd.Parameters.Add(paramOne);
 da = new SqlDataAdapter();
 da.SelectCommand = cmd;
 da.Fill(ds);
 dt = ds.Tables[0];
 foreach (DataRow dr in dt.Rows)
 {
 object tmpShoeSize = dr["ShoeSize"];
 }
 }
 catch (Exception ex)
 {
 returnValue = "Unknown Size";
 }
 finally
 {
 if ((ds != null))
 ds.Dispose();
 ds = null;
 if ((da != null))
 da.Dispose();
 da = null;
 if ((cmd != null))
 cmd.Dispose();
 conn.Close();
 conn.Dispose();
 conn = null;
 }
 return returnValue;
 }

Further Reading

Have a look at:

• The Microsoft documentation for .NET 4.5 for populating a DataSet from a DataAdapter
• Coverage on MSDN of ASP.NET Data Access in Framework 1.1