Documents
Resources
Learning Center
Upload
Plans & pricing Sign in
Sign Out
Get this document free

Lists of Stores Going Out of Business in Illinois

VIEWS: 42 PAGES: 29

Lists of Stores Going Out of Business in Illinois document sample

More Info
									Reading From Data Sources

   Database Background
    –   Data Models
    –   Normalization
   ADO.NET
    –   Managed Providers
    –   Connection Object
    –   Command and DataReader Object
    –   ExecuteReader Object
Understanding Modern Databases
   Most important question: How is the application going to store
    data?
     –   Simple file for just a few records
     –   Large database containing thousands of records
   Database: collection of data that‟s been organized in a way that
    allows its contents to be easily accessed and manipulated
   Relational Database Management System (RDMS): software
    that‟s used to store, retrieve and modify data in a relational
    database
   Metadata: “data for data”
     –   part of RDMS that describes organization and nature of data
     –   describes a model of how the data will be structured
Data Models

   Simplest form
    –   Database arranges data into a table, which consists
        of rows and columns.
            Row is synonymous with record
            Column is synonymous with field

User ID Owner      Car Type State    Phone No.   VIN Number
1        Scott     Corvette   IL     328-5555    A34DC982
2        Sam       Cavalier   WI     333-5555    DS23J89K
1        Scott     Stealth    IL     328-5555    23FGH4JK
2        Sam       Stealth    CA     333-5555    198GYUIET
3        Steve     Cavalier   WI     244-5555    POIJ257890
Normalization and Relational DB’s
   The previous database contains redundancy, which
    makes a very inefficient database
    –   If Scott moved to another state, his state value would have to
        change for every record
    –   Whereas, if we had another table with just Scott‟s name and
        state, we‟d only change it once.
   Normalization
    –   Process that reduces data redundancy to its lowest level
    –   Occurs by splitting larger, complex table, into smaller, non-
        redundant tables (known an entity tables).
    –   Each entity table contains one unique field (primary key) and
        normally group one kind of object (person, place, thing or idea)
Car Database Normalized

     User ID   Owner       Phone No.       State
     1         Scott       328-5555        IL
     2         Sam         333-5555        CA
     3         Steve       244-5555        WI

                       User ID   Car Type          VIN Number
                       1         Corvette          A34DC982
                       2         Cavalier          DS23J89K
                       1         Stealth           23FGH4JK
                       2         Stealth           198GYUIET
                       3         Cavalier          POIJ257890
Normalization
   Three basic rules
    –   Minimize redundant data in individual tables
    –   Create a separate entity table for each set of related data
    –   Specify a unique field in each table to act as a primary key
   RDMS Products
    –   Microsoft SQL Server, Access, Oracle, Informix and MySQL
   Benefits
    –   Minimize the number of records that need to be modified
    –   Creates relational databases, so data can be easily shared
        and joined through queries.
ADO.NET
   Group of object classes provided by .NET framework
    for interacting with data from data stores.
   Data store: broader meaning of database that includes
    non-database containers.
    –   E-mail servers, text files, Excel, XML, etc.
    –   The list of connections is not fixed. ADO.NET gives us the
        ability to write providers, drivers and adapters so we can
        connect to data stores that aren‟t even imagined yet.
   ASP.NET offers objects that make it easier to display
    the data.
ADO.NET
   ADO.NET works with disconnected data
    –   The user requests the page
    –   The connection is opened, the data is gathered, the
        connection is closed and the data is sent to the user.
    –   This allows for more scalability and reduces our costs
   Summary
    –   Provides us the power to pull in data from a variety of sources,
        while reducing the amount of code.
    –   It will make our system more scalable and flexible
    –   For the most benefit we must fully understand how to use the
        ADO.NET objects and SQL
Using Web Matrix to Create a Data
Page

   Open the database file (MS Access file) from
    the data tab
   Drag and drop table to the empty page to
    create a grid
Managed Providers

   We need to be able to talk to the data sources in order
    to extract data from them
   ADO.NET uses “Managed” Data Providers
    –   Managed means that they conform to the .NET standards for
        memory and isolation from other processes
    –   Managed Provider for SQL Server: only speaks with Microsoft
        SQL Server 7 or higher. Thus, it‟s extremely fast.
    –   Managed Provider for OLEDB, allowing us to talk to any data
        store with a OLEDB Provider. (Almost every other data source
        other than Microsoft SQL Server)
Managed Providers Structure
         ASP.NET Application

                                                                ADO.NET
Managed Provider for   Managed Provider for                     Managed
SQL Server             OLEDB                                    Providers


                                                                OLEDB
                                     Other OLEDB Providers      Providers



             Microsoft SQL
                                              Other Databases
                 Server
ADO.NET Objects
   Six elements needed to work with data
    –   Data Source: need to understand the basic structure (table
        and field names)
    –   Managed Data Provider: conversing language
    –   ADO.NET Connection Object: allows the ASP.NET page to
        speak with Provider or Driver
    –   ADO.NET Command Object: tool that has instructions about
        what to read from or say to data
    –   ADO.NET DataReader or DataSet Object: place that holds
        data that has been read or is to be written.
    –   ASP.NET controls: objects run on the server that display the
        data. <asp:DataGrid>
ADO.NET Data Flow

            ASP.NET Application


       Data Set              Data Reader


              Command Object


              Connection Object

           Managed Data Provider



                  Database
Connection Object
   Used to connect to the data source
   Connection String: contains information needed to
    connect to the actual store of data
    –   First part specifies kind of Provider or Driver used
    –   Second part specifies the database to use
    –   Last part security information such as the user name and
        password.
   The three most common connection strings
    –   Access
    –   SQL Server
    –   Managed SQL Server
Connection Object

   After creating the connection string, we begin
    to use the object
    –   Connection.open()
    –   Connection.close()
   The following database examples reference
    the car database. (created by me in Access)
   Save the database in
    C:\BegASPNET\Database\Car.mdb
<%@ import Namespace=“System.Data” %>
<%@ import Namespace=“System.Data.Oledb” %>                          Connection Object
                                                                         Example
<script language=“vb” runat=“server”>
    Sub Page_Load()
       „Set up out connection string and display on the page
       Dim strConnection as String = “Provider=Microsoft.Jet.OLEDB.4.0;”
          strConnection += “Data Source=C:\BegASPNET\Database\Car.mdb”
          data_src.text = strConnection

      Dim objConnection as New OledbConnection(strConnection)

       „Now we try to open the connection
       try
            objConnection.Open()
            con_open.text=“Connection opened successfully. <br/>”
            objConnection.Close()
            con_close.text=“Connection closed successfully. <br/>”
       catch e as Exception
            con_open.text=“Connection failed to open. <br/>”
            con_close.text=e.ToString()
       end try
    End Sub
</script>
<html>                                          Connection Object
   <body>                                            Example
     <h4>Testing the data connection
     <asp:label id=“data_src” runat=“server” /></h4>
     <asp:label id=“con_open” runat=“server” /><br/>
     <asp:label id=“con_close” runat=“server” /><br/>
   </body>
</html>
Access Example Display

Testing the data connection
  Provider=Microsoft.Jet.OLEDB.4.0;Data
  Source=C:\BegASPNET\Database\Car.mdb

Connection opened successfully.

Connection closed successfully.
Structured Query Language
   SQL Queries
    –   SQL statements: commands that are sent to the database
        engine and tell it to perform certain actions
            SELECT: retrieve data from one or more tables
            DELETE: delete data from a table
            INSERT: add a new record to a table
            CHANGE: change existing data in a record of a table
   Typical SQL Statement
    –   SELECT CarType, CarYear FROM User WHERE CarType =
        „Lebaron‟
            This returns any record from the User table where the value of the
             CarType field is “Lebaron”.
<%@ import Namespace=“System.Data” %>
<%@ import Namespace=“System.Data.Oledb” %>
                                                                           Command and
                                                                            DataReader
<script language=“vb” runat=“server”>
    Sub Page_Load()                                                          Example
       „Set up out connection string and display on the page
       Dim strConnection as String = “Provider=Microsoft.Jet.OLEDB.4.0;”
          strConnection += “Data Source=C:\BegASPNET\Database\Car.mdb”
          data_src.text = strConnection
       Dim strSQL as String = “SELECT CarType, CarYear FROM Cars”
       Dim strResultsHolder as string

      Dim objConnection as New OledbConnection(strConnection)
      Dim objCommand as New OledbCommand(strSQL, objConnection)
      Dim objDataReader as OledbDataReader

      „Now we try to open the connection
      try
          objConnection.Open()
          con_open.text=“Connection opened successfully. <br/>”
      objDataReader = objCommand.ExecuteReader()

      Do While objDataReader.Read()=True
          strResultsHolder +=objDataReader(“CarType”)
          strResultsHolder +=“&nbsp;”
          strResultsHolder +=objDataReader(“CarYear”)
          strResultsHolder +=“<br>”
      Loop
       objDataReader.Close()                                            Command and
           objConnection.Close()
           con_close.text=“Connection closed successfully. <br/>”
                                                                          DataReader
       divListCars.innerHTML = strResultsHolder                            Example
       catch e as Exception
           con_open.text=“Connection failed to open. <br/>”
           con_close.text=e.ToString()
       end try
    End Sub
</script>
<html>
    <body>
       <h4>Testing the data connection
       <asp:label id=“data_src” runat=“server” />With the data reader object</h4>
       <asp:label id=“con_open” runat=“server” /><br/>
       <div:label id=“divListCars” runat=“server”>list will go here</div>
       <asp:label id=“con_close” runat=“server” /><br/>
    </body>
</html>
Data Reader Output

   The program lists of all of the car types and
    their year:
    Corvette 2003
    Cavalier 1997
    Stealth 1996
    Stealth 1997
    Cavalier 2000
<%@ Import Namespace=“System.Data %>
<%@ Import Namespace=“System.Data.Oledb %>
                                                              Execute Reader
                                                                 Example
<script language=“vb” runat=“server”>
   Sub Page_Load
      Dim strConnection as String = “Provider=Microsoft.Jet.OLEDB.4.0;”
           strConnection += “Data source= C:\BegASPNET\Database\Car.mdb”
      Dim strSQL as String = “SELECT CarType, CarYear, CarVinNum FROM Cars”

     Dim objConnection as New OledbConnection(strConnection)
     Dim objCommand as New OledbCommand(strSQL, objConnection)

       objConnection.Open()
       dgEmps.DataSource =
    objCommand.ExecuteReader(CommandBehavior.CloseConnection)
       dgEmps.DataBind()
    End Sub
</script>
                                                      Execute Reader
<html>                                                   Example
   <body>
     <h2>Using ExecuteReader to create a Table</h2>
     <asp:datagrid id=“dgEmps” runat=“server”
        CellPadding=“3”
        Font-Name=“arial”
        Font-Size=“8pt”
     />
   </body>
</html>
ExecuteReader Example

Using ExecuteReader to create Table
  CarType     CarYear      CarVinNum
  Corvette    2003         A34DC982

  Cavalier    1997         DS23J89K

  Stealth     1996         23FGH4JK

  Stealth     1997         198GYUIET

  Cavalier    2000         POIJ257890
DataSet and DataTable Objects
   Since ADO.NET works with a disconnected database, it
    stores the database information in the DataSet.
    –   It can hold several table and the table relationships
    –   It can navigate among the records
    –   It can change data located in the data store
   ADO.NET provides four DataSet objects
    –   DataSet: holds tables and their relationships
    –   DataTable: holds data in table
    –   DataAdapter: modifies and passes results from Connection
        into DataSet
            DataAdapter.Fill: copies data into DataSet
            DataAdapter.Update: copies DataSet data back to data source
    –   DataView: displays data in particular manner
DataSet Data Flow
              Data View


                         Data Set
            Data Table
            Data Table
            Data Table


             Data Adapter

          Connection Object
<%@ Import Namespace=“System.Data %>
<%@ Import Namespace=“System.Data.Oledb %>                                   DataSet Example
<script language=“vb” runat=“server”>
    Sub Page_Load
       Dim strConnection as String = “Provider=Microsoft.Jet.OLEDB.4.0;”
            strConnection += “Data source= C:\BegASPNET\Database\Car.mdb”
            data_src.text = strConnection
       Dim strSQL as String = “SELECT CarType, CarYear, CarVinNum FROM Cars;”
       Dim objDataSet as New DataSet()

      Dim objConnection as New OledbConnection(strConnection)
      Dim objAdapter as New OledbDataAdapter(strSQL, objConnection)

      objAdapter.Fill(objDataSet, “Cars”)

      Dim objDataView as New DataView(objDataSet.Tables(“Cars”))

       dgNameList.DataSource=objDataView
       dgNameList.DataBind()
    End sub
</script>
<html>
    <body>
       <h4>Reading data from the connection
       <asp:label id=“data_src” runat=“server”/> to the DataGrid control.</h4>
       <asp:datagrid id=“dgNameList” runat=“server” /><br/>
    </body>
</html>
DataSet Output

Reading data from the connection
 Provider=Microsoft.Jet.OLEDB.4.0;Data
 source=C:\BegASPNET\Database\Car.mdb to
 the DataGrid control.
  CarType    CarYear    CarVinNum
  Corvette   2003       A34DC982
  Cavalier   1997       DS23J89K
  Stealth    1996       23FGH4JK
  Stealth    1997       198GYUIET
  Cavalier   2000       POIJ257890

								
To top