DD Class9

DataReader

• DataReader http://iam.colum.edu/dd/classsource/ado/dataReader.aspx source

1 Connection Object <csharp> //Connection string comes from web config SqlConnection objConnection = new SqlConnection(ConfigurationManager.ConnectionStrings["cheeseConnectionString"].ConnectionString); </csharp>

web.config connection strings

<xml> <configuration>

              <connectionStrings>

<add name="cheeseConnectionString" connectionString="Data Source=iamdb;Initial Catalog=cheese;User ID=student;Password=Student" providerName="System.Data.SqlClient"/> </connectionStrings> </xml>

2 Command Object <csharp>string strSQL = "SELECT CheeseName,CheeseDescription FROM cheese";

   string strResultsHolder = "";
   
   SqlCommand objCommand = new SqlCommand(strSQL, objConnection);

</csharp>

3 Reader and Reading <csharp> SqlDataReader objDataReader = null; //Reader to read through the result

   try
   {
       objConnection.Open();                                       //Open Connection
       objDataReader = objCommand.ExecuteReader();

       while (objDataReader.Read() == true)
       {
           strResultsHolder += String.Format("{0}:{1}

",
               objDataReader["CheeseName"], objDataReader["CheeseDescription"]);
       }
   }
   catch (Exception e)
   {
       lblErr.Text = "Connection failed to open successfully.
";
       lblErr.Text += e.ToString();
   }
   finally
   {
       objDataReader.Close();      //Close Reader
       objConnection.Close();      //Close Connection

   }
   //Write results
   divListCheese.InnerHtml = strResultsHolder;

}

</csharp>

Review First Normal Form

Let build the example for the homework.

Build Interface to GameDB

Games

GameTitle	GameGenre	DeveloperName	Platform(s)	Year	DeveloperWebsite	GameWebsite
Quake1	FPS	id	Dos	1996	http://www.idsoftware.com/	http://www.idsoftware.com/games/quake/quake/
Diablo	RPG	Blizzard	Windows 95	1996	http://www.blizzard.com/	http://www.blizzard.com/diablo/
SimCity	Sim	Interplay	Dos	1993	http://www.interplay.com/	http://www.maxis.com/

Tables Games, Developers, Websites, Platforms?

Build Views

Nesting Data Bound Controls

nesting controls

Datasource Controls can probably handle up to 70% of your data binding needs but they do have drawbacks. Consider the cheese database which uses an SQL view to join Cheese with Region, Consistency, and MilkType. Of course SQL view don't support updating and can lead to some bad data binding. we can fix a few of these problem with stored procedures.

Simple filter Demo in class

GridView Details View Master Child Relationship

http://iam.colum.edu/dd/classsource/data/CheeseDataBindingFull.aspx source

Stored Procedures

http://iam.colum.edu/dd/classsource/data/sproc/sprocADO.aspx

Cheese browser assignment

Make a creative cheese browser from that tables in you db. Use a view to join that data from multiple tables..

Here's and example of a simple cheese shop browser app.

http://iam.colum.edu/dd/classsource/class8/CheeseShop/CheeseShop1.aspx

Homework

Make a creative cheese browser from that tables in you db. Use a view to join that data from multiple tables..