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:
- 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#.
- 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:
1 thought on “ASP.NET Example – Populating a DataSet Using SQLDataAdapter Fill DataSet”