Databases: DBMS, Tables, Rows, and Columns

Working with Databases

• Traditional files are suitable for storing small amounts of data
• Traditional files become impractical when large amounts of data need to be stored and manipulated
• Simple operations on traditional files with large data become cumbersome and inefficient
• Database Management Systems (DBMS) are designed to store, retrieve, and manipulate large amounts of data efficiently
• Applications in C# or other languages can communicate with the DBMS after the data has been stored
• Applications send instructions to the DBMS instead of directly manipulating the data
• The DBMS executes the instructions and returns the results to the application
• Examples of popular DBMSs include Microsoft SQL Server, Oracle, DB2, MySQL, and Microsoft Access
• Microsoft SQL Server Express Edition is used in this course because it is automatically installed with Visual Studio

Tables, Rows, and Columns

• A DBMS stores data in a database
• Data in a database is organized into one or more tables
• Data in a table is organized into rows and columns
• A row represents a complete set of information about a single item
• Data within a row is divided into columns
• Each column holds an individual piece of information about the item

Column Data Types

• When creating a database table, specifying a data type for each column is necessary
• The available data types are provided by the DBMS, not C#
• The nchar(n) and nvarchar(n) data types are used to store strings
• An nchar(n) column has a fixed length and uses n characters of space in the database
• An nvarchar(n) column has a variable length, using only the space needed to accommodate the data, up to n characters

Primary Keys

• Most database tables have a primary key column to identify a specific row
• The primary key column must hold a unique value for each row
• Examples of primary keys include:
  • Employee ID numbers
  • Product numbers
  • Invoice numbers
• An identity column to serve as the primary key needs to be specifically created
• Identity columns are typically integers
• When a new row adds to the table, the DBMS automatically assigns a unique value to the row's identity column

Creating a Database in Visual Studio

• A .NET application uses layered special components
• Data Source: A source of data the application can work with, such as databases, Excel spreadsheets, XML data, text files, or Web services
• Table Adapter: Connects to a data source, retrieves data from a table, and updates the table in the data source
• Dataset: Gets a copy of a table from the table adapter, keeps it in memory, and allows the application to work with the data by modifying, adding, or deleting rows
• Binding Source: A component to connect user interface controls directly to a dataset

Creating an Example Application

• Steps
  • Create a new Windows Forms Application project named "Phone Book"
  • Change the Form1 form's Text property to "Phone Book"
  • Create a new database and add it to the project by clicking Project -> Add New Item...
  • In the Add New Item window, select Service-based Database and name it "Phonelist.mdf" (ensure the .mdf extension is kept)
  • Click the Add button in the Add New Item window, which adds the Phonelist.mdf database to the project
  • In the Solution Explorer, note the Phonelist.mdf database entry
  • Double-click the Phonelist.mdf database entry to see the Server Explorer window
  • Click on the created database, then go to the properties window
  • Change the "Copy to Output Directory" property to "Copy if newer" to prevent data loss when editing the database at runtime
  • In the Server Explorer, add a table to the Phonelist.mdf database by expanding the Phonelist.mdf entry, right-clicking on Tables, and selecting Add New Table
  • Upon creating a table, the Table Designer automatically adds a column named Id
    • The key icon next to the column name identifies it as the table's primary key
    • The column name is Id with the int data type
    • The "Allow Nulls" checkbox is unchecked, meaning the column cannot be null
  • Change the name of the Id column to PersonID
  • In the Properties window, scroll down to see the Identity Specification
  • Double-click Identity Specification to reveal (Is Identity), Identity Increment, and Identity Seed properties
  • Change the (Is Identity) property to True to designate the column as an identity column, with the Identity Increment and Identity Seed properties both set to 1
  • Add another column named Name, select nvarchar(50) as the data type, and remove the check under Allow Nulls
  • Add another column named Phone, select nvarchar(50) as the data type, and remove the check under Allow Nulls
  • At the bottom of the screen, find the area labeled T-SQL and change the first statement so it reads: CREATE TABLE [dbo].[Person]
  • Click the Update button
  • Click the Update Database button to confirm the table's name change
  • Enter data into the table by expanding the Tables entry in the Server Explorer and right-clicking the Person entry, then click Show Table Data
    • The system automatically generates a value for the PersonID column as each new row adds
    • values must be entered for the Name and Phone columns
    • Exclamation points appear next to the columns to indicates as the data is not saved to the database
  • Save all data by clicking File -> Save All.
  • Close the table so the Form1 form is displayed in the Designer
• The database file locates in the bin/Debug directory in your project folder - It is populated after executing the project

Data-Bound Control

• A data-bound control is a user interface control connected to a data source
• It can connect a column to a database table
• Automatically displays data from the data source and can be used to change the data to which they connect
• A DataGridView control display an entire database table in a displayable grid on an application's form

Adding a DataGridView Control

• Steps:
  • Make the Phone Book project is open in Visual Studio
  • Go to Project -> Add New Data Source
  • Make sure Database is selected and and click Next >
  • Ensure the Dataset is selected
  • Click to choose the data connection, and the Phonelist.mdf database should be selected and click Next >
  • The dialog asks if you want to save a connection string
    • Check the Yes box and click next
  • Expand the Tables entry, expand the Tables entry, and place a check next to Person
  • View -> Other Windows -> click Data Sources and the Data Sources window should displayed
  • Select DataGridView as the default data-bound control for the Person table -> click DataGridView
  • Drag the Person table from the Data Sources window onto the form to create a DataGridView control
  • Save and run the application
  • To change the value of a cell, click the cell with the mouse and then change its data as necessary
  • To select a row, click the button that appears next to it, along the left side of the control
  • To delete a row, select it; then either click the Delete button on the navigation bar or press delete on the keyboard