Contents tagged with DataReader

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

    6 Comments

    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.

    Read more...

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

    1 Comment

    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

    Read more...

  • Retrieving Values from a DataReader

    15 Comments

    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://msdn.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

    Read more...