Library tutorials & articles
Replicating GetRows in .NET
- Introduction
- Old vs New
- Let's Explain
Let's Explain
Let's Explain
The first thing in our .NET code above is importing our Data namespaces to allow us to call our objects so we can connect to the data. Next we declare all our variables. We then set-up our database connection and call the DataAdapter to open and query our data source. Now within our called DatAdapter we expose our DataSet which is a disconnected storage container of the tables holding our data, and this can hold a lot of tables.
By means of our DataSet we are offered much control over our data presentation. The DataSet upon creation stores and exposes a collection of rows and columns or a DataTable object (which stores one in-memory table), with which we can manipulate and view our results, even updating, deleting, and inserting.
Now after I queried the database and filled the DataSet with the help of the DataAdapter, I exposed our DataTable object with GetRows - a DataTable declared variable object, and got some precise information on what it contains.
I wanted to know how many columns and rows there are and with the DataTable.Rows.Count and DataTable.Columns.Count I get that. Next, I use .NET's cool new string building method - the StringBuilder to build my table. It was filled with two loops - one loop for telling us the columns name by GetRows.Columns(c).ToString() method and another loop for our row information like so GetRows.Rows(x)(y).ToString() , whereby x indicates row number and y is column number.
Then I send out my results as a string to the tag below and that's it.
Conclusion
Here I have demonstrated a very simple and easy way of migrating your old GetRows code exactly over to .NET. You may at times simply employ many of the native .NET controls to display your data if you feel that'll be enough, but you may at times run into snags like I have and this was the only satisfactory way of displaying my data in the way I needed to.
So when it comes to migrating legacy code, .NET does it and more, and with so much more power, performance and class, and without compromising any flexibility whatsoever.
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.