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:
[...] 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 SQLData… [...]