Library tutorials & articles
Replicating GetRows in .NET
- Introduction
- Old vs New
- Let's Explain
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)"
.Open
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>"
Next
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>"
Next
Response.Write "</TABLE>"
'Close and clear our connections
dbConn.Close
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>")
Next
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>")
Next
grTable.Append ("</TABLE>")
objDataAdapter = Nothing : objDS = Nothing
End Sub
</script>
<html>
<body>
<%=grTable.ToString()%>
</body>
</html>
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 deskto...
Related articles
Related discussion
-
Profile Class does not work after Translation
by converter2009 (1 replies)
-
what is the SQL Server Provider
by hayperaktib (1 replies)
-
Very Urgent regarding deleting the images from a folder
by Nanosteps (6 replies)
-
Java Script, File uploading on ftp server using java script code
by h_c_a_andersen (2 replies)
-
sharepoint calendar web part with events from sql table
by converter2009 (2 replies)
Related podcasts
-
StackOverflow uses ASP.NET MVC - Jeff Atwood and his technical team
Scott chats with Jeff Atwood of CodingHorror.com and most recently, StackOverflow.com. Jeff and Joel Spolsky and their technical team have created a new class of application using ASP.NET MVC. What works, what doesn't, and how did it all go down?
Events coming up
-
Mar
15
DevWeek 2010
London, United Kingdom
DevWeek is Europe’s leading independent conference for software developers, database professionals and IT architects, and features expert speakers on a wide range of topics, including .NET 4.0, Silverlight 3, WCF 4, Visual Studio 2010, REST, Windows Workflow 4, Thread Synchronization, ASP.NET 4.0, SQL Server 2008 R2, LINQ, Unit Testing, CLR & C# 4.0, .NET Patterns, WPF 4, F#, Windows Azure, ADO.NET, Entity Framework, Debugging, T-SQL Tips & Tricks, and more.
<%@ Page Language="C#" Debug="False" Strict="True" Explicit="True" Buffer="True"%>
<%@ Import Namespace="System.Data" %>
<%@ Import Namespace="System.Data.SqlClient" %>
<script language="C#" runat="server">
StringBuilder grTable;
string sqlStr;
int TotalRows, TotalFlds, c, r;
void Page_Load(Object Source, EventArgs E) {
sqlStr = "Select lname As LastName, fname As FirstName, empid As ID, hiredate As [Hired On] from Employee";
SqlConnection objConnect = new SqlConnection ("server=(local);uid=sa;pwd=;database=Pubs;");
SqlDataAdapter objDataAdapter = new SqlDataAdapter (sqlStr.ToString(), objConnect);
DataSet objDS = new DataSet();
//Create and Fill Info Datatable with results
objDataAdapter.Fill (objDS,"Info");
//Close and clear our connections
objConnect.Close();
objConnect = null;
//Declare name variable as a DataTable
DataTable GetRows = 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; c <= TotalFlds-1; c++) {
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; r <= TotalRows-1; r++) {
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>" + GetRows.Rows[r][3].ToString() + "</TD>");
grTable.Append ("</TR>");
}
grTable.Append ("</TABLE>");
objDataAdapter = null;
objDS = null;
}
</script>
<html>
<body>
<%
Response.Write (grTable.ToString());
grTable = null;
%>
</body>
</html>
-Jimmy Markatos
Do you have an example of this same concept using C#?
No, it's not pathetic and Yes, it is a technique that clearly your lack of insight allowed you to totally miss. Sure GetRows is what it is. Nevertheless, one project app fully benefited from this technique in a way any other technique would simply not do.
This has nothing to do with portraying .NET's magnificent features or coding the “.NET way” All this shows is a unique methodology in "Replicating GetRows in .NET" in light of a particular need, when migrating a specific functionality from old ASP, and replicating as is to .NET, period!
So if you don't agree or YOU missed the point, then it's an oversight on your part. Not considering, Developer Fusion deemed it worthy to be added to this site.
It sure can - I've just corrected the article to reflect this
The author has indeed closed the connection at the end of the code (I've moved this further up now to make this clearer) - but the actual recordset is automatically closed the moment the call to the adapters "Fill" method is called - the recursive part of the code is on the "disconnected" DataSet.
The whole point of GetRows is to open a conn, get the data and close the conn.
not leave it open while you loop all data as you would exactly like a recordset.
Pathetic.
Forget about oops buddy, when ur migrating tons of code on a deadline.
But do we need to close the connection in the end, cant the datatable sustain data when the collection is closed.
Hi!
I'm trying to divide data from a table in different excel worksheets files.
Nombre Dato Nombre Dato Nombre Dato
a 1 a 1 b 45
a 10 a 10 b 38
b 45
b 38
As far as "control over display" you can really accomplish almost anything by using the format properties for each column and/or formatting the data in the SQL or Stored Proc. If you needed something really crazy, you could even add some code to the ItemDataBound event. That's a useful discussion in and of itself since you can also add formatting based on data elements - such as highlighting a row where a product is on sale or something like that.
This thread is for discussions of Replicating GetRows in .NET.