Utpa fall 2011
Download
1 / 35

UTPA – Fall 2011 - PowerPoint PPT Presentation


  • 67 Views
  • Uploaded on

CSCI 3327 Visual Basic Chapter 13: Databases and LINQ. UTPA – Fall 2011. Objectives. In this chapter, you will Learn the relational database model Get familiar with using LINQ to retrieve and manipulate data from a database

loader
I am the owner, or an agent authorized to act on behalf of the owner, of the copyrighted work described.
capcha
Download Presentation

PowerPoint Slideshow about ' UTPA – Fall 2011' - selima


An Image/Link below is provided (as is) to download presentation

Download Policy: Content on the Website is provided to you AS IS for your information and personal use and may not be sold / licensed / shared on other websites without getting consent from its author.While downloading, if for some reason you are not able to download a presentation, the publisher may have deleted the file from their server.


- - - - - - - - - - - - - - - - - - - - - - - - - - E N D - - - - - - - - - - - - - - - - - - - - - - - - - -
Presentation Transcript
Utpa fall 2011

CSCI 3327 Visual Basic Chapter 13: Databases and LINQ

UTPA – Fall 2011


Objectives
Objectives

  • In this chapter, you will

    • Learn the relational database model

    • Get familiar with using LINQ to retrieve and manipulate data from a database

    • Know how to add data sources to project, create LINQ to SQL classes, and use data binding to move data between GUI controls and databases

    • Learn how to create Master/Detail views to select a record and display its details


Introduction
Introduction

  • A database is an organized collection of data

  • A database management system (DBMS) provides mechanisms for storing, organizing, retrieving and modifying data

  • Existing DBMSs

    • Microsoft SQL Server

    • Oracle

    • Sybase

    • IBM DB2


Introduction cont d
Introduction (cont'd)

  • PostgreSQL and MySQL are popular open-source DBMSs that can be downloaded and used freely by anyone

  • In this class, we use Microsoft’s free SQL Server Express, which is installed with Visual Basic Express and Visual Studio

  • It can also be downloaded separately from Microsoft (www.microsoft.com/express/sql)


Relational database
Relational Database

  • A relational database organizes data simply in tables

    • rows (also called records)

    • columns (also called fields, attributes)

      • Primary key: a column (or group of columns) requiring a unique value that cannot be duplicated in other rows

      • A primary key composed of two or more columns is known as a composite key

      • Foreign key—a column in this table that matches the primary-key column in another table



SQL

  • A program might select data from the table to create a query result

    • E.g., to retrieve the location of each department, in increasing order by Department number

    • SQL:

      • Select distinct Department, Location

        From Employees

        Order By Department



Schema
Schema

  • A database’s tables, their fields and the relationships among them are collectively known as a database schema

  • In SQL Server, database files have the .mdf (“master data file”) file-name extension

    • Books.mdf


Books database
Books Database

  • Books database consists of 3 tables

    • Authors

    • Titles

    • AuthorISBN





Entity relationship model
Entity-Relationship Model

  • Entity-Relationship (ER) model

    • Entity

      • Authors

      • Titles

    • Relationship

      • There is a one-to-many relationship between a primary key and a corresponding foreign key

        • E.g., one author can write many books and one book can be written by many authors

      • Others: many-to-many or one-to-one relationship

      • E.g., AuthorISBN


Example of er diagram
Example of ER Diagram

Books database


Querying a database with linq
Querying a Database with LINQ

  • Connect to the Books database

  • Create the LINQ to SQL classes required to use the database

  • Add the Authors table as a data source

  • Drag the Authors table data source onto the Design view to create a GUI for displaying the table’s data

  • Add a few statements to the program to allow it to interact with the database



Creating linq to sql classes
Creating LINQ to SQL Classes

  • Step 1: Creating the Project

    • Create a new Windows Forms Application named DisplayTable

    • Change the name of the source file to DisplayAuthorsTable.vb

    • The IDE updates the Form’s class name to match the source file

    • Set the Form’s Text property to Display Authors Table


Creating linq to sql classes cont d
Creating LINQ to SQL Classes (cont'd)

  • Step 2: Adding a Database to the Project and Connecting to the Database

    • To interact with a database, you must create a connection to the database.

    • In Visual Basic 2010 Express, select View > Other Windows > Database Explorer to display the Database Explorer window

      • For a full version of Visual Studio, select View > Server Explorer to display the Server Explorer

    • Click the Connect to Database icon at the top of the Database Explorer

      • In Choose Data Source dialog, select Microsoft SQL Server Database File from the Data source: list

      • Always use this selection CheckBox, the IDE will use this type of database file by default when you connect to databases Click Continue to display the Add Connection dialog



Creating linq to sql classes cont d1
Creating LINQ to SQL Classes (cont'd)

  • In the Add Connection dialog

    • click the Change… Button to select a database

    • click Browse… to locate and select the Books.mdf file in the Databases directory

    • click Test Connection to verify that the IDE can connect to the database through SQL Server Express

    • click OK to create the connection



Creating linq to sql classes cont d2
Creating LINQ to SQL Classes (cont'd)

  • Step 3: Generating the LINQ to SQL classes

    • Select the database from which the LINQ to SQL classes will be created

    • LINQ to SQL uses the database’s schema to help define the classes

    • Right click the project name in the Solution Explorer and select Add > New Item… to display the Add New Item dialog

      • Select the LINQ to SQL Classes template, name the new item Books.dbml and click the Add button

        • The Object Relational Designer window will appear

      • Double click the Books.dbml file in the Solution Explorer to open the Object Relational Designer



Creating linq to sql classes cont d3
Creating LINQ to SQL Classes (cont'd)

  • Expand the Books.mdf database node in the Database Explorer

  • Expand the Tables node

  • Drag the Authors, Titles and AuthorISBN tables onto the Object Relational Designer

  • The IDE prompts whether you want to copy the database to the project directory

  • Select Yes

    • The Object Relational Designer will display the tables that you dragged from the Database Explorer

  • Save the Books.dbmlfile


Data bindings between controls and the linq to sql classes
Data Bindings Between Controls and the LINQ to SQL Classes

  • Step 1: Adding the Author LINQ to SQL Class as a Data Source

  • To use the LINQ to SQL classes for data binding, you must first add them as a data source.

    • Select Data > Add New Data Source… to display the Data Source Configuration Wizard.

    • The LINQ to SQL classes are used to create objects representing the tables in the database, so we’ll use an Object data source. In the dialog, select Object and click Next >. Expand the tree view and ensure that Author is checked. An object of this class will be used as the data source

    • Click Finish


Data bindings between controls and the linq to sql classes cont d
Data Bindings Between Controls and the LINQ to SQL Classes (cont'd)

  • The Authors table in the database is now a data source that can be used by the bindings

  • Open the Data Sources window by selecting Data > Show Data Sources.


Data bindings between controls and the linq to sql classes cont d1
Data Bindings Between Controls and the LINQ to SQL Classes (cont'd)

  • Step 2: Creating GUI Elements

    • Use the Design view to create a GUI control that can display the Authors table’s data

      • Switch to Design view for the DisplayAuthorsTable class

      • Click the Author node in the Data Sources window—it should change to a drop-down list.

        • Open the drop-down by clicking the down arrow

        • Select DataGridView option—this is the GUI control that will be used to display and interact with the data.

      • Drag the Author node from the Data Sources window onto the Form in Design view


Data bindings between controls and the linq to sql classes cont d2
Data Bindings Between Controls and the LINQ to SQL Classes (cont'd)

  • To make the DataGridViewoccupy the entire window

    • select the DataGridView

    • use the Properties window to set the Dock property to Fill


Data bindings between controls and the linq to sql classes cont d3
Data Bindings Between Controls and the LINQ to SQL Classes (cont'd)

  • Step 3: Connecting the BooksDataContext to the AuthorBindingSource

    • The final step is to connect the BooksDataContext to the AuthorBindingSource, so that the application can interact with the database

    • Obtain data from the database and to save any changes that the user makes to the data back into the database (see next slide)


Data bindings between controls and the linq to sql classes cont d4
Data Bindings Between Controls and the LINQ to SQL Classes (cont'd)

  • Step 4: Saving Modifications Back to the Database

    • If the user modifies the data in the DataGridView, we’d also like to save the modifications in the database

    • By default, the BindingNavigator’s Save Data Button is disabled

    • To enable it, right click this Button’s icon and select Enabled

    • Then, double click the icon to create its Click event handler

    • Saving the data entered into the DataGridView back to the database is a three-step process (lines 22–24)


ad