Lecture 14 A new Introduction to Databases DB

Lecture 14 A new Introduction to Databases: DB Review & ADO. NET Models – Connected and Disconnected By Chris Pascucci and FLF

Introduction to Databases n A database is a collection of data related to a particular problem domain. n n n n Databases can range from a few kilobytes to several terabytes and beyond. The Integrated Student Information System (ISIS) once used at Temple University (100 years ago) is a large database. So is Banner. Google’s database is an example of a massive database. NSA has a massive database called a data warehouse that collects information from a variety of other databases. Social Security and the IRS have massive data bases – imagine what is stored there And then … there is the Affordable Care Act data base … A Database Management System (DBMS) is software that is used to create, access and maintain a database. n n Microsoft Access Microsoft SQL Server

Introduction to Databases n A relational database stores data and the connections (relationships) within the data. n n A relational database uses tables to store and manipulate data. n n A table can be visualized as a 2 -dimensional grid that contains rows and columns. Each table contains one or more records (rows) that contain information about a single entry. n n It uses a relational data model. Records contain a set of related fields that are used to store data. Each record contains one or more fields (columns) that contain a single piece of data.

Introduction to Databases n n More TERMINOLOGY … A table represents an entity in a problem domain. n n For example, in the integrated system information system ISIS, a table may represent a Student entity or a Course entity A record (row) is an instance of an entity. n n n In ISIS, a specific student record (you, for example) would be an instance of the student entity (table). Also, a specific course “CIS 3309 Section 001” would be an instance of the course entity. Database Tables are models of application domain entities – those “things” that need to be manipulated in our system n n These things are no different in concept from the entities we model in C# Only in C# we use classes for our models rather than database Tables

Introduction to Databases n n Tables are made up of rows (instances of an entity) and columns (fields, or attributes of each instance/ table) Most tables include a primary key, which is a field that uniquely identifies each record in a table. n n Storing student records by name in a large university will make it difficult to find students with the same name. Giving each student a Student. ID uniquely identifies each student. SSN uniquely identify each American in the U. S. What uniquely identifies each Temple student (you) The tables in a relational database are related to each other through their key columns. A column that identifies a related row in another table is called a foreign key.

Intro to Databases – Naming Conventions n n These are some suggested conventions when coding in C#. NET Given a database named Employees with one table named tbl. Employees containing the following fields: fld. Employee. ID – (number) unique employee id number fld. First. Name and fld. Last. Name – (strings) employee's first and last name, respectively fld. Telephone – (string or number) the employee's telephone number fld. Date. Hired – (date value) stores date that the employee was hired fld. Wage – (number) contains the hourly wage for the employee fld. Deductions – (number) contains the number of tax deductions claimed by the employee fld. Notes is a string containing notes pertaining to the employee fld. Status – (Boolean) If True the employee is still employed. If False the employee is a former employee. fld. Type – (string) employee (valid values: fulltime and parttime)

Populated Database Table

Naming Database Tables and Fields n Standard (Hungarian) prefixes are commonly used to name database tables and fields n n The prefix "tbl" denotes a table The prefix "fld" denotes a field

Relational Databases n Data stored in one table can be related to data stored in another table in a few ways: n n One-To-One Relationship One-To-Many Relationship. Many-To-Many Relationship. One-To-Many Relationship means a record in one table is related to many records in another table via primary and foreign keys. n n For example, a course record in the Course table for Temple university can relate to many students in the Student table. A student record in a Student table can relate to many courses in the Course Table (perhaps 4 or 5 courses)

Relational Databases n Many-To-Many Relationship means a record in Table A can have many records in Table B, and vice versa. n n n It can be thought of as two separate one-to-many relationships. For example, a writer can write many books, and a book can have many writers. Thus, a Book table can contain a record that relates to an Author table by having multiple authors for a book, and an author record in the Author table can relate to the Book table by having a author that wrote many books. Student-Course example? What do we do to many-many relationships in an RDB? One-To-One Relationship means a record in one table is related to one and only one record in another table. n For example, an employee has a single resume, and each resume only belongs to one employee. (Well – maybe)

Introduction to SQL n n Structured Query language (SQL) is an English – like language that is used to manage and manipulate data in the database. There are 4 basic SQL statements that are needed to work with data in a database (along with CREATE). n n SELECT – used to query a table and find specific information based on some criteria. It selects one or more rows (records) from a table. INSERT – used to add one or more rows (records) to a table. UPDATE – used for modifying data in one or more rows (records) in a table. DELETE – used to delete one or more rows (records) in a table.

SQL: Select Statement n n The result of a Select statement is called a result set, or result table, which is a logical set of rows that consists of all of the columns and rows requested by the Select statement. Syntax: SELECT column_name [, column_name 2]. . . FROM table_name [WHERE selection-criteria] [ORDER BY column_name [ASC|DESC] [, column_name 2[ASC|DESC]]. . . ]

SQL: Select Statement Example n Example: SELECT Product. ID, Name, Unit. Price FROM Products WHERE Category. ID = “props” ORDER BY Unit. Price DESC n The asterisk (*) can be used to select all fields of a table. n Example: SELECT * FROM Products

SQL: Insert Statement n n The Insert statement is used to add a new row (record) to a table. It can be used to set fields to specified values as it adds the new record. Syntax: INSERT [INTO] table_name [(columns_list)] VALUES (values_list) n The order of the items in columns list must match the order of the items in the values list.

SQL: Insert Statement Example n Example: INSERT INTO Products (Product. ID, Short. Description, Unit. Price) VALUES (“p 101”, “Toy”, “ 19. 99”)

SQL: Update Statement n n The Update statement is used to modify/edit existing data within a table. Syntax: UPDATE table_name SET column_name = value [, column_name 2 = value 2]. . . WHERE selection-criteria n Syntax: UPDATE Products SET Unit. Price = “ 25. 79”, On. Hand = “ 3” WHERE Product. ID=“arm 01”

SQL: Delete Statement n n The Delete statement is used to remove a row or rows from a table. Syntax: DELETE FROM table_name WHERE selection-criteria n Example: DELETE FROM Products WHERE Product. ID = “arm 01”

SELECT Example n Table used in SQL Examples: Return to last viewed slide

Introduction to DB Access in VS - ADO. NET n n The Active. X Data Objects (ADO. NET) is part of the. NET framework that provides a multi-layered set of data access namespaces (set of classes), which allow us to access and modify data stored in a database. ADO. NET is the primary data access API (Application Program Interface) for the. NET framework. n n It is a framework inside a framework. This framework’s responsibility is to allow our programs to work with databases by providing the abstraction against which we can program ADO. NET supports many different database platforms such as Oracle, SQL Server, Microsoft Access ADO. NET supports both a connected and disconnected data architecture.

ADO. NET Libraries n ADO. NET supports multiple database platforms n n Sql. Client provides support for Microsoft SQL Server 7. 0 and later. n System. Data. Sql. Client Oracle. Client provides support for Oracle 8. 1. 7 and later. n System. Data. Oracle. Client Ole. Db (Object Linking & Embedding) provides support for conecting to Ole. Db sources like SQL server, Oracle, and Microsoft. ACE. OLEDB. 12. 0 (Microsoft Access). n System. Data. Ole. Db Note that these are all sub Namespaces under System. Data namespace which is provided automatically for us in all our components

Database Providers n Object Linking & Embedding Database (OLE DB) connectivity system based on Active X technology. n n In. NET we use Data Providers to connect to our DBs n n n Active. X is Microsoft technology used for developing reusable objectoriented software components. Libraries of code provide all necessary communication between your code and database systems. Providers are an integral part of ADO. NET. Providers exist for the main database systems such as SQL Server, Access, and Oracle. For this course, we will be working in the provider world mostly with SQL Server or Access providers. It does not matter which you use although all our examples and code illustrations use Access.

The ADO. Net Platforms n Microsoft ACE Database Engine is now the main Accessbased component for storing, parsing, optimizing, and processing queries. n n ACE (Access Connectivity Engine -- now called the Access Database Engine) Newest database engine for working with Access 2007 (and later) “. accdb” files. It is a File-Server System DBMS where the work is done on the individual server where the Access DBMS exists. Microsoft SQL Server is a client-server DBMS in which the client requests information from the database and the server processes the request.

The ADO. NET Platforms - 2 n n n Oracle is another client-server DBMS used for large databases. Visual Studio &. NET have tools for working with all these database management systems. Reminder: these tools are part of the sub-namespaces of the System. Data namespace

More on ADO. NET has three main abstractions: n n The ADO. NET Data. Providers are components (namespaces containing classes) specifically designed for working with a particular database platform. n n There is a namespace (a set of classes) for working with Access, SQL Server, one set specifically for Oracle, etc… The ADO. NET Data. Set is a component used for working with data separate from the data source. n n Data. Providers Data. Sets Data. Readers An integral part of the disconnected data architecture. The ADO. NET Data. Reader is an abstraction that is the main feature for working in the connected mode of DB processing

ADO. NET Components n The Data. Adapter is a component that is used to load data into the Data. Table of a Data. Set so it can be used in a disconnected mode n n Data Adapter’s major function is to manage the exchange of data between the database and the Data. Set. The Command component is used to issue SQL commands for the Data. Adapter to execute while connected to a database. The Connection component is used to establish a connection to a database for the Data. Adapter to use. The Data. Reader component is used for read-only, forward-only stream of data coming from a database. n This component is part of a connected data architecture.

Still More on ADO. NET n n We can consider the Data Adapters and Data Readers as providing two distinct models of access to data bases provided in. NET For an introduction to some of the distinctions between these two models, visit … http: //msdn. microsoft. com/enus/library/ms 254931%28 v=vs. 110%29. aspx Will use the Data Reader (Connected) model in our work

Disconnected vs. Connected n n The major advantage of the disconnected approach is that it improves system performance due to the fact that it uses less resources for maintaining connections when working with a data source. However, there is a disadvantage to this approach that occurs when two or more users try to update the same row of a table, which is called a concurrency problem.

Disconnected vs. Connected n n ADO. NET contains two different approaches to work with data in a database: Connected Data Architecture and the Disconnected Data Architecture. Connected Data Architecture: n n n Represents the objects that require an open connection to work and interact with the data source. ADO. NET provides classes to communicate directly with a data source. Disconnected Data Architecture: n n Represents the objects that open and close a connection to the data source as needed. Your application works with data that was copied from the data source. Any changes made to the “copied” data in the Dataset will be later reconciled with the data source. Disconnected applications alone don't fulfill the demands of today’s distributed applications. Improved performance, more efficient and more complex than the connected approach.

Steps to Working with ADO. NET (Connected) n n n First, a database connection is established Then the database is opened (use the Open command) SQL commands are sent over the open connection ADO. NET builds an in-memory representation of the returned data from any SELECT command changes are made to this representation When processing is complete, the database connection is closed n n n It is suggested that you use the Open and Close methods to explicitly open and close your connections. In this way, you keep your connections open only as long as needed to complete the required processing This is true for both the connected and disconnected models But the definition of when “processing is complete” will differ

Processing Database Elements n n All elements of a data base, tables (entities), rows (sets of values), columns (database fields), and individual cells (database values) are represented as abstractions in. NET In other words, every table, row, and column are represented as instances of the abstractions (classes) that are part of the ADO. NET support structure n n But the abstractions you “program against” using Data. Readers are different from those involved when using Data. Sets In CIS 3309 we will use Data. Readers only 3342

An Underlying Framework – n n The Connection Keeping a mental picture of this process in your head is important It all begins with the connection Already visited this idea for text and binary files and (perhaps) graphics. Now we revisit it again, for databases Remember … n n n In your programs, you manipulate objects using methods belonging to the class that defines the type of the object The class (an abstract data type) is an abstraction (model) of the object being manipulated The are several objects involved here

The Object Framework – The Connection The database abstraction (methods and data stores) that you program against (DB commands, internal datasets and data tables) The connection – via creation of a database connection object The actual, physical database to be manipulated ‘Example - Connection to a Microsoft Access Database Ole. Db. Connection my. Acc. Connection. Obj = new Ole. Db. Connection(connect. String); ‘Example - Connection to SQL Server Database Sql. Connection my. Sql. Connection. Obj = new Sql. Connection(connect. String);

Connection Strings – more details 1 n We need to connect the program’s ADO. NET database object to the underlying database to be used // SQL Server Connection string connect. String = "server=dwarf. cis. temple. edu; Database=sp 19_c? ? ? ; ” + "User id=fa 06_c 342132; Password=jimbo“ ; Sql. Connection my. Connection. Sql = new Sql. Connection(Connect. String); // Access Connection string connect. String = " string str. Connection = "provider=Microsoft. ACE. OLEDB. 12. 0; " + "Data Source=. . /Debug/Resources/Emp. Man. DB. accdb"; “; Ole. Db. Connection my. Connection = new Ole. Db. Connection(Connect. String); n n n Programmer layer – build commands (SELECT, INSERT, DELETE, and UPDATE) for execution against the database Logical layer – uses a Data. Adapter to send commands from your software (across the data adapter) to the database getting back tables to be processed Physical layer – manages the actual (physical) execution of the commands on the database

Connection Strings – more detail 2 n n A connection string … n A connection string consists of key-value pairs n An equals sign separates a key and value n A semicolon separates a key-value pair Connection strings differ for each database provider Documentation for Visual Studio contains includes an article entitled “Working with Connection Strings” which you can find by using on-line help index “connection strings [ADO. NET]” You can also go to http: //www. connectionstrings. com to locate lots more information about connection strings

The Ole. Db. Connection Class n A connection is represented by the System. Data. Ole. Db. Connection class n Properties The Connection. String property contains a string that determines how ADO. NET will establish the database connection. One of the benefits of ADO. NET is that if a program is migrated to use a different database, all of the other code remains intact. Only the Connection. String needs to be changed. n The Connection. Timeout specifies the number of seconds that can elapse to establish a connection. n The Data. Source property gets the location and file name of the database. This file name is embedded into the Connection. String. n The Provider property defines the database provider. This information is also embedded into the Connection. String property. n The State property gets the state of the connection. A Few Methods n The Open and Close methods open and close the connection, respectively. An Event n The State. Changed event fires when the connection state changes. n n n

Database Processing Sequence n n Establish a connection to your data source using the Connection object. Create an SQL command statement either using a string or the Command object. Execute the Command object within the context of the connected database. Process the results of the commands by retrieving/storing the data when necessary. n n n Use the Data. Reader to read records. (in CIS 3342) Use the Data. Adapter and Data. Set for storage of data retrieved from the database, and for use in your program. Close the connection and release resources utilized by all the objects.

ADO. NET Terms Summary n Data. Set: n n Connection: n n Executes commands (SQL statements) against the data source. Data Providers: n n Connects to the data source. Command: n n Data in the Dataset is independent of the database that was used to retrieve it. The ADO. NET classes responsible for working directly with a specific database. Data Reader n Retrieves query results in a read-only, forward-only connected result set.

ADO. NET Resources n Connection Strings: n n Ole. Db. Connection Class: n n http: //msdn. microsoft. com/en-us/library/system. data. oledbcommand. aspx Ole. Db. Data. Adapter Class: n n http: //msdn. microsoft. com/en-us/library/system. data. oledbconnection. aspx Ole. Db. Command Class: n n http: //www. connectionstrings. com http: //msdn. microsoft. com/enus/library/system. data. oledbdataadapter. aspx Ole. Db. Data. Reader Class: n http: //msdn. microsoft. com/en-us/library/system. data. oledbdatareader. aspx