Replicating GetRows in .NET

Old vs New

The Old Fashioned Way

The code here shows how we can query a database and write out the columns and rows into a nicely formatted table.

The Code: Below I open a database connection, run my query and loop through the records with GetRows and display the results. To run the examples below as written you'll need to have the Pubs database available to you.

<%@ Language=VBScript %>
Dim sqlStr
sqlStr = "Select lname As LastName, fname As FirstName, emp_id As ID, hire_date As [Hired On] from Employee"

'Open up the database connection
Set dbConn = Server.CreateObject("ADODB.Connection")

With dbConn
  .Provider = "SQLOLEDB"
  .ConnectionString = "Provider=SQLOLEDB;USER ID=sa;PASSWORD=;INITIAL CATALOG=Pubs;Data Source=(local)"
End With

Set rsDbConn = dbConn.Execute(sqlStr, adExecuteNoRecords)

If Not rsDbConn.EOF Then
  GetrsConn = rsDbConn.GetRows()
End if

Response.Write "<TABLE border=1 Width=""60%"">"
Response.Write "<TR>"

'Columns name loop
For c = 0 to UBound(GetrsConn,1)
  Response.Write "<TD><B>" & rsDbConn.Fields(c).Name & "</B></TD>"

Response.Write "</TR>"

'Rows loop
For r = 0 to UBound(GetrsConn,2)
  Response.Write "<TR>"
  Response.Write "<TD>" & GetrsConn(0,r) & "</TD>"
  Response.Write "<TD>" & GetrsConn(1,r) & "</TD>"
  Response.Write "<TD>" & GetrsConn(2,r) & "</TD>"
  Response.Write "<TD>" & GetrsConn(3,r) & "</TD>"
  Response.Write "</TR>"

Response.Write "</TABLE>"
'Close and clear our connections
Set rsDbConn = Nothing

Again, this is standard to any ASP developer. Now as you'll see, you can do the exact same thing in .NET and explain that after.

Let's Do the Same Thing in .NET

<%@ Import Namespace="System.Data" %>
<%@ Import Namespace="System.Data.SqlClient" %>
<script language="VB" runat="server">

Dim grTable As StringBuilder
Dim sqlStr As String
Dim TotalRows, TotalFlds, c, r As Integer

Sub Page_Load(Source As Object, E As EventArgs)

  sqlStr = "Select lname As LastName, fname As FirstName, emp_id As ID, hire_date As [Hired On] from Employee"
  Dim objConnect As New SqlConnection ("server=(local);uid=sa;pwd=;database=Pubs;")
  Dim objDataAdapter As New SqlDataAdapter (sqlStr.ToString(), objConnect)
  Dim objDS As New DataSet()
  'Create and Fill Info Datatable with results
  objDataAdapter.Fill (objDS, "Info")
  'Close and clear our connections
  objConnect.Close : objConnect = Nothing
  'Declare name variable as a DataTable
  Dim GetRows As DataTable = objDS.Tables ("Info")
  'Get Table Info
  TotalRows = GetRows.Rows.Count
  TotalFlds = GetRows.Columns.Count
  grTable = New StringBuilder ()
  grTable.Append ("<TABLE border=1 Width=60%>")
  grTable.Append ("<TR>")
  'Loop through data
  'Loop through the Columns Fields
  For c = 0 To TotalFlds-1
    grTable.Append ("<TD><B>" & GetRows.Columns(c).ToString() & "</B></TD>")
  grTable.Append ("</TR>")
  'First header row is now closed and we loop through our database rows
  For r = 0 To TotalRows-1
    grTable.Append ("<TR>")
    grTable.Append ("<TD>" & GetRows.Rows(r)(0).ToString() & "</TD>")
    grTable.Append ("<TD>" & GetRows.Rows(r)(1).ToString() & "</TD>")
    grTable.Append ("<TD>" & GetRows.Rows(r)(2).ToString() & "</TD>")
    grTable.Append ("<TD>" & FormatDateTime(GetRows.Rows(r)(3).ToString(),2) & "</TD>")
    grTable.Append ("</TR>")
  grTable.Append ("</TABLE>")
  objDataAdapter = Nothing : objDS = Nothing
End Sub

You might also like...


About the author

Dimitrios Markatos

Dimitrios Markatos United States

Dimitrios, or Jimmy as his friends call him, is a .NET developer/architect who specializes in Microsoft Technologies for creating high-performance and scalable data-driven enterprise Web and des...

Interested in writing for us? Find out more.


Why not write for us? Or you could submit an event or a user group in your area. Alternatively just tell us what you think!

Our tools

We've got automatic conversion tools to convert C# to VB.NET, VB.NET to C#. Also you can compress javascript and compress css and generate sql connection strings.

“Perfection is achieved, not when there is nothing more to add, but when there is nothing left to take away.” - Antoine de Saint Exupéry