Difference between revisions of "DD Class9"

esse quam videri
Jump to: navigation, search
(→‎DataReader)
(→‎DataReader)
Line 1: Line 1:
 
[[Category:Data Design]]
 
[[Category:Data Design]]
  
−
==DataReader==
+
==Primary Keys==
−
Data Reader is a fast forward data reader that can save memory and resources on large datasets
+
The candidate key selected as being most important for identifying a body of information (an entity, object or record).
  
 +
==Normalization==
 +
[http://en.wikipedia.org/wiki/Database_normalization Normal Forms]
  
−
*DataReader http://iam.colum.edu/dd/classsource/ado/dataReader.aspx [http://iam.colum.edu/dd/gbrowser.php?file=/classsource/ado/dataReader.aspx source]
+
'''First Normal'''
 +
:Form eliminates repeating groups by putting each into a separate table and connecting them with a one-to-many relationship.
 +
:
  
 +
[[Data Relationships]]
  
 +
Not Following First Normal Form Repeating Groups
  
−
1 Connection Object
+
Blog1
−
<csharp>
+
{| class="wikitable" cellpadding="5" cellspacing="0"
−
//Connection string comes from web config
+
!BlogID !!  BlogText !! recCreationDate !! Mood
−
SqlConnection objConnection = new SqlConnection(ConfigurationManager.ConnectionStrings["cheeseConnectionString"].ConnectionString);           
+
|-
−
</csharp>
+
|1 || Blog1 || 03/30/03 || Happy
 +
|-
 +
|2 || Blog2 || 03/30/03 || Happy
 +
|-
 +
|3 || Blog3 || 03/30/03 || Sad
 +
|-
 +
|4 || Blog4 || 03/30/03 || Happy
 +
|-
 +
|5 || Blog4 || 03/30/03 || Mad
 +
|}
  
−
web.config connection strings
+
Tables that Follow First normal form
  
−
<xml>
+
Blog2
−
<configuration>
+
{| class="wikitable" cellpadding="5" cellspacing="0"
−
+
! BlogID !! BlogText !! recCreationDate !! MoodID
−
              <connectionStrings>
+
|-
−
+
|1 || Blog1 || 03/30/03 || 1
−
  <add name="cheeseConnectionString" connectionString="Data Source=iamdb;Initial Catalog=cheese;User ID=student;Password=Student" providerName="System.Data.SqlClient"/>
+
|-
−
      </connectionStrings>
+
|2 || Blog2 || 03/30/03 || 1
−
</xml>
+
|-
 +
|3 || Blog3 || 03/30/03 || 2
 +
|-
 +
|4 || Blog4 || 03/30/03 || 1
 +
|-
 +
|5 || Blog4 || 03/30/03 || 3
 +
|}
 +
Mood
 +
{| class="wikitable" cellpadding="5" cellspacing="0"
 +
!MoodID !! MoodName
 +
|-
 +
|1 || Happy
 +
|-
 +
|2 || Sad
 +
|-
 +
|3 || Mad
 +
|-
 +
|4 || Afraid
 +
|}
  
−
2 Command Object
+
1NF also
−
<csharp>string strSQL = "SELECT CheeseName,CheeseDescription FROM cheese";
+
:Removes multiple column with the same type of data
−
   
 
−
    string strResultsHolder = "";
 
−
   
 
−
    SqlCommand objCommand = new SqlCommand(strSQL, objConnection);
 
−
</csharp>
 
  
−
3 Reader and Reading
+
Books Not Normal
−
<csharp>
 
−
SqlDataReader objDataReader = null;                            //Reader to read through the result
 
−
    try
 
−
    {
 
−
        objConnection.Open();                                      //Open Connection
 
−
        objDataReader = objCommand.ExecuteReader();
 
  
−
        while (objDataReader.Read() == true)
+
'''Books'''
−
        {
+
{| class="wikitable" cellpadding="5" cellspacing="0"
−
            strResultsHolder += String.Format("<b>{0}</b>:{1}<br /><br />",
+
!Author !! Title_01 !! Pages_01 !! Title_02 !! Pages_02 !! Title_03 !! Pages_03
−
                objDataReader["CheeseName"], objDataReader["CheeseDescription"]);
+
|-
−
        }
+
| Michael Allen Dymmoch || The Man Who Understood Cats || 256 || White Tiger || 320 || ||
−
    }
+
|-
−
    catch (Exception e)
+
| Joseph Cancellaro || Exploring Sound Design for Interactive Media || 272 || || || ||
−
    {
+
|}
−
        lblErr.Text = "Connection failed to open successfully.<br/>";
 
−
        lblErr.Text += e.ToString();
 
−
    }
 
−
    finally
 
−
    {
 
−
        objDataReader.Close();      //Close Reader
 
−
        objConnection.Close();      //Close Connection
 
  
−
    }
+
In Class Build Blogs Table and Normalize Books Table
−
    //Write results
 
−
    divListCheese.InnerHtml = strResultsHolder;
 
−
}
 
  
−
</csharp>
+
 
 +
 
 +
http://en.wikipedia.org/wiki/First_normal_form
 +
 
 +
==ERD==
 +
 
 +
http://en.wikipedia.org/wiki/Entity-relationship_model
 +
 
 +
tools
 +
*http://staruml.sourceforge.net/en/ free and open source
 +
*http://www.visual-paradigm.com/product/vpuml/ proprietary free community edition
  
 
==Review First Normal Form==
 
==Review First Normal Form==

Revision as of 16:19, 24 October 2011


Primary Keys

The candidate key selected as being most important for identifying a body of information (an entity, object or record).

Normalization

Normal Forms

First Normal

Form eliminates repeating groups by putting each into a separate table and connecting them with a one-to-many relationship.

Data Relationships

Not Following First Normal Form Repeating Groups

Blog1

BlogID BlogText recCreationDate Mood
1 Blog1 03/30/03 Happy
2 Blog2 03/30/03 Happy
3 Blog3 03/30/03 Sad
4 Blog4 03/30/03 Happy
5 Blog4 03/30/03 Mad

Tables that Follow First normal form

Blog2

BlogID BlogText recCreationDate MoodID
1 Blog1 03/30/03 1
2 Blog2 03/30/03 1
3 Blog3 03/30/03 2
4 Blog4 03/30/03 1
5 Blog4 03/30/03 3

Mood

MoodID MoodName
1 Happy
2 Sad
3 Mad
4 Afraid

1NF also

Removes multiple column with the same type of data

Books Not Normal

Books

Author Title_01 Pages_01 Title_02 Pages_02 Title_03 Pages_03
Michael Allen Dymmoch The Man Who Understood Cats 256 White Tiger 320
Joseph Cancellaro Exploring Sound Design for Interactive Media 272 
In Class Build Blogs Table and Normalize Books Table


http://en.wikipedia.org/wiki/First_normal_form

ERD

http://en.wikipedia.org/wiki/Entity-relationship_model

tools

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..


Examples

http://iam.colum.edu/dd/classsource/Data/GridAndDetails.aspx

http://iam.colum.edu/dd/classsource/Data/GridDataBound.aspx

http://iam.colum.edu/dd/classsource/Data/GridDetails.aspx

http://iam.colum.edu/dd/classsource/Data/GridEdit.aspx

http://iam.colum.edu/dd/classsource/Data/GridSelect.aspx

http://iam.colum.edu/dd/classsource/Data/GridSelectTheme.aspx

http://iam.colum.edu/dd/classsource/Data/GridTemplate.aspx

Retrieved from "http://imamp.colum.edu/mediawiki/index.php?title=DD_Class9&oldid=19942"