Nannette Thacker ShiningStar.net : DataReader

Section 9: C# and VB Project: Using our TableAdapters, DataTables and Intellisense

nannette — Tue, 09 Sep 2008 13:19:00 GMT

In our final Section 9 of this tutorial by Nannette Thacker, we learn how to use our TableAdapters within our code. We also look at how Intellisense makes it easy to set up your TableAdapter code.

Download the ZIP files:
C#: ShiningStarCExcel.zip
VB: ShiningStarVBExcel.zip

Do everything without complaining or arguing, so that you may become blameless and pure. Philippians 2:14

May your dreams be in ASP.NET!
Nannette Thacker

Section 8: C# and VB Project: Using an OleDbDataReader to Retrieve Our Data

nannette — Mon, 08 Sep 2008 20:16:00 GMT

In Section 8 of this tutorial by Nannette Thacker, we take a look at adding a OleDbDataReader and the ExecuteReader method to retrieve data from an Excel Spreadsheet.

Download the ZIP files:
C#: ShiningStarCExcel.zip
VB: ShiningStarVBExcel.zip

"Taste and see that the LORD is good; blessed is the man who takes refuge in him." Psalm 34:8

May your dreams be in ASP.NET!
Nannette Thacker

C# and VB Project: A Tutorial Using Data Sets, Table Adapters, WebForms, Controls, File Upload, Excel Import

nannette — Wed, 13 Aug 2008 15:35:00 GMT

In this tutorial by Nannette Thacker, we will create a VB or C# project from scratch. We will create DataSets, TableAdapters, a WebForm and Controls, a File Upload, and import and view an Excel Spreadsheet, and more! We'll also learn about intellisense and other features of Visual Studio or Visual Web Developer. This project may be used in both VS or VWD 2005 or 2008. Zip files with the complete source code in either C# or VB, Excel Spreadsheet, and .mdf SQL Server Database is available for download. You may easily modify this tutorial to use an existing database as well.

I have written this tutorial with step by step instructions and screen shots on how to do each point. If you are learning ASP.NET, please don't simply download the source code and plunk it into your project, the best way to learn is to walk through the tutorial from scratch and do each step yourself.

Introduction: C# and VB Project: A Tutorial Using Data Sets, Table Adapters, WebForms, Controls, File Upload, Excel Import

Download the ZIP files:
C#: ShiningStarCExcel.zip
VB: ShiningStarVBExcel.zip

Learning a new technology can be intimidating when first starting out. You want to learn it in 5 minutes, and in some cases, you feel pressured that you must learn it in 5 minutes! I remember first learning C programming in college years ago, and lying on the bedroom floor in tears, crying and pleading with God to teach me. I felt I was struggling, but through His grace, He helped me earn one of only two A's in the course which had over 100 students.

I encourage you, don't give up, and pray for God's help. He will be there for you, as long as your project is one that is not contrary to His will.

I can do all things through Christ who strengthens me. Philippians 4:13

May your dreams be in ASP.NET!
Nannette Thacker

Importing an Excel Spreadsheet Using Typed DataSets and TableAdapters: Importing the Excel Spreadsheet

nannette — Mon, 11 Aug 2008 16:07:00 GMT

By Nannette Thacker

In the final installment of this tutorial, we look at importing the uploaded Excel spreadsheet into the application database tables using the DAL.

The article series hosted on 4guysfromrolla.com continues with Importing an Excel Spreadsheet Using Typed DataSets and TableAdapters: Importing the Excel Spreadsheet.

Download the Application in ZIP Format

May your dreams be in ASP.NET!

Nannette Thacker

Retrieving Values from a DataReader

nannette — Thu, 14 Feb 2008 17:57:00 GMT

By Nannette Thacker

There are several ways to retrieve values from a DataReader.

The first method is by using the index and looping through the fields in order as retrieved within the Select statement. Starting with the 0 index, you may use the GetValue() Function.

The following code snippets are in VB. A C# snippet is provided at the bottom.

reader = objCommand.ExecuteReader()
While reader.Read()

If Not reader.GetValue(0) Is DBNull.Value Then _
    lastname = reader.GetValue(0)
If Not reader.GetValue(1) Is DBNull.Value Then _
    firstname = reader.GetValue(1)

End While
reader.Close()

However, this makes the code harder to read, and if a field is added or removed from the query, it could break your code. So let's look at how to retrieve the values by field names.

If your reader is using an OleDbDataReader, we use the reader.Item function.

If Not reader.Item("lastname") Is DBNull.Value Then _
    lastname = reader.Item("lastname")
If Not reader.Item("firstname") Is DBNull.Value Then _
    firstname = reader.Item("firstname")

If your reader is using a SqlDataReader, we use the reader.GetItem function.

If Not reader.GetItem("lastname") Is DBNull.Value Then _
    lastname = reader.GetItem("lastname")
If Not reader.GetItem("firstname") Is DBNull.Value Then _
    firstname = reader.GetItem("firstname")

Here is a C# example:

if (reader["lastname"] != DBNull.Value)
    lastname = reader["lastname"].ToString();

A reader of my column, Zac, suggests that reader("lastname") instead of reader.Item("lastname") will also work.

He is correct. For the DataReader class implementation, Item is the default property and may be left out. You may access an indexer by using the () operator on the base class object or you may access an indexer by using the Item property.

Another reader of my column, Marneus, pointed out the GetOrdinal option as well. He said "there is a performance hit, each time you use the reader("lastname"), it has to check the meta datas to get the column number."

Readers may refer to this document on the GetOrdinal()

http://msdn2.microsoft.com/en-us/library/system.data.sqlclient.sqldatareader.getordinal.aspx

Please note this comment on efficiency:

"Because ordinal-based lookups are more efficient than named lookups, it is inefficient to call GetOrdinal within a loop. Save time by calling GetOrdinal once and assigning the results to an integer variable for use within the loop."

From the article, here is the usage:

' Call GetOrdinal and assign value to variable.
Dim customerID As Integer = reader.GetOrdinal("CustomerID")

' Use variable with GetString inside of loop.
While reader.Read()
    Console.WriteLine("CustomerID={0}", reader.GetString(customerID))
End While

Another reader, AndrewSeven, pointed out the cost for hitting the reader twice for each field. He suggests:

object val = reader["lastname"];
if (val != DBNull.Value)
{
   lastname = val;
}

And also comments "If your concern is readability, you should probably encapsulate the null test so you can write:

lastname = GetValueFromReader(reader,"lastname");"

Travis responded with this suggestion: "Just do this, it handles nulls nicely unlike .ToString():"

lastname = Convert.ToString(reader["lastname"]);

So I tested his theory by creating columns that would not allow null values, and imported columns with null values. In VB it is written like so:

lastname = Convert.ToString(reader("lastname"))
firstname = Convert.ToString(reader("firstname"))

That is much cleaner! Thanks everyone for your great input!

May your dreams be in ASP.net!

Nannette Thacker