1 / 52

Chapter 12

JavaScript, Third Edition. Chapter 12. Database Connectivity with ASP.NET. Objectives. Study databases Connect to databases with ASP.NET Execute SQL commands through ASP.NET. Introduction. Web pages commonly used to: Gather information stored on a Web server database

hadassah-lawrence
Download Presentation

Chapter 12

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. Content is provided to you AS IS for your information and personal use only. Download presentation by click this link. While downloading, if for some reason you are not able to download a presentation, the publisher may have deleted the file from their server. During download, if you can't get a presentation, the file might be deleted by the publisher.

E N D

Presentation Transcript

  1. JavaScript, Third Edition Chapter 12 Database Connectivity with ASP.NET

  2. Objectives • Study databases • Connect to databases with ASP.NET • Execute SQL commands through ASP.NET JavaScript, Third Edition

  3. Introduction • Web pages commonly used to: • Gather information stored on a Web server database • Most server-side scripting languages, including ASP.NET: • Have the ability to create Web pages that can read and write data to and from databases JavaScript, Third Edition

  4. Understanding Databases • Database: • Ordered collection of information from which a computer program can quickly access information • The information stored in computer databases is stored in tables JavaScript, Third Edition

  5. Understanding Databases (Cont.) • Each row in a database table is called a record: • Single complete set of related information • Each column in a database table is called a field: • Individual categories of information stored in a record JavaScript, Third Edition

  6. Understanding Databases (Cont.) JavaScript, Third Edition

  7. Understanding Databases (Cont.) • A better solution for large and complex databases is a relational database • A relational databasestores information across multiple related tables JavaScript, Third Edition

  8. Understanding Databases (Cont.) • Relational databases consist of one or more related tables • You create relationships within the database: • By working with two tables at a time • One table in a relationship is always considered to be the primary table • The other table is considered to be the related table JavaScript, Third Edition

  9. Understanding Databases (Cont.) • Primary table: • Main table in a relationship referenced by another table • Related table (child table): • References a primary table in a relational database • Tables in a relationship are connected using primary and foreign keys JavaScript, Third Edition

  10. Understanding Databases (Cont.) • Primary key: • Field that contains a unique identifier for each record in a primary table • Foreign key: • Field in a related table that refers to the primary key in a primary table • Primary and foreign keys link records across multiple tables in a relational database JavaScript, Third Edition

  11. Understanding Databases (Cont.) JavaScript, Third Edition

  12. Understanding Databases (Cont.) • Three basic types of relationships within a relational database: • one-to-one • one-to-many • many-to-many • A one-to-one relationship: • Exists between two tables when a related table contains exactly one record for each record in the primary table JavaScript, Third Edition

  13. Understanding Databases (Cont.) • A one-to-many relationship: • Exists in a relational database when one record in a primary table has many related records in a related table • You create a one-to-many relationship in order to eliminate redundant information in a single table JavaScript, Third Edition

  14. Understanding Databases (Cont.) JavaScript, Third Edition

  15. Understanding Databases (Cont.) JavaScript, Third Edition

  16. Understanding Databases (Cont.) • Primary and foreign keys: • The only pieces of information in a relational database table that should be duplicated • Normalization: • Breaking tables into multiple related tables in order to reduce redundant and duplicate information • Reduces the size of a database • Makes the data easier to work with JavaScript, Third Edition

  17. Understanding Databases (Cont.) • A many-to-many relationship: • Exists in a relational database when many records in one table are related to many records in another table • To create a many-to-many relationship: • You must use a junction table • Most relational database systems cannot work directly with many-to-many relationships JavaScript, Third Edition

  18. Understanding Databases (Cont.) • A junction table: • Creates a one-to-many relationship for each of the two tables in a many-to-many relationship • Contains foreign keys from the two tables in a many-to-many relationship, along with any other fields that correspond to a many-to-many relationship JavaScript, Third Edition

  19. Understanding Databases (Cont.) JavaScript, Third Edition

  20. Database Management Systems • A database management system (DBMS): • An application or collection of applications used to create, access, and manage a database • Runs on many different platforms, ranging from personal computers, to client-server systems, to mainframes JavaScript, Third Edition

  21. Database Management Systems (Cont.) • Different database management systems exist: • A flat-file database management system • A relational database management system, or RDBMS • Hierarchical and network database management systems • Oracle, Sybase, and Informix • Access, FoxPro, and Paradox JavaScript, Third Edition

  22. Database Management Systems (Cont.) • Two other important aspects of database management systems are: • Their querying and reporting capabilities • A query: • A structured set of instructions and criteria for retrieving, adding, modifying, and deleting database information JavaScript, Third Edition

  23. Database Management Systems (Cont.) • A report: • The formatted, printed output of a database table • OR • The results of a query • Most database management systems use a data manipulation language, or DML: • ex: structured query language, or SQL (pronounced sequel) JavaScript, Third Edition

  24. Database Management Systems (Cont.) JavaScript, Third Edition

  25. Database Management Systems (Cont.) JavaScript, Third Edition

  26. Structured Query Language • SQL uses fairly easy-to-understand statements to execute database commands • SQL statements are composed of keywords that perform actions on a database JavaScript, Third Edition

  27. Structured Query Language (Cont.) JavaScript, Third Edition

  28. Connecting to Databases with ASP.NET • With Active Server Pages, you use ActiveX Data Objects to access databases • ActiveX Data Objects, or ADO: • A Microsoft database connectivity technology • Allows ASP and other Web development tools to access ODBC- and OLE DB-compliant databases • OLE DB: • A data source connectivity standard promoted by Microsoft as a successor to ODBC JavaScript, Third Edition

  29. Connecting to Databases with ASP.NET (Cont) • One of the primary differences between OLE DB and ODBC is: • ODBC supports access only to relational databases • OLE DB provides access to both relational databases and non-relational data sources • ADO.NET: • Most recent version of ADO • Allows you to access OLE DB-compliant data sources and XML • Use to directly access Microsoft SQL Server databases, without having to go through OLE DB JavaScript, Third Edition

  30. Connecting to Databases with ASP.NET (Cont) • ADO and OLE DB: • Part of Microsoft Universal Data Access strategy for providing access to data, regardless of its storage format • MDAC Or Microsoft Data Access Components: • Make up the Universal Data Access technology • Installed with numerous Microsoft products, including Internet Explorer, Internet Information Server, Microsoft Visual Studio, and the Microsoft .NET Framework SDK JavaScript, Third Edition

  31. ADO.NET Object Model • ADO.NET technology: • Based on an object model used for accessing and manipulating data sources JavaScript, Third Edition

  32. ADO.NET Object Model (Cont) • Import processing directive • Used to import a namespace in order for the ASP.NET documents to access the ADO.NET object model • Namespaces: • Used for managing the various classes and other elements in the .NET Framework JavaScript, Third Edition

  33. ADO.NET Object Model (Cont) • There are numerous namespaces available to the .NET Framework: • Many are imported automatically into your ASP.NET programs • Others, including namespaces that give ASP.NET access to database connectivity classes, must be explicitly imported JavaScript, Third Edition

  34. ADO.NET Object Model (Cont) • To access the OLE DB namespace: • You must import the System.Data.OleDb namespace • This is done by adding the following statement to your ASP.NET documents: <%@ Importƒnamespace=”System.Data.OleDb” %> JavaScript, Third Edition

  35. The ADO.NET Connection Object • Connection object: • Used to access databases from ASP.NET • ADO.NET includes two Connection objects: • The SqlConnection object • Connects to Microsoft SQL Server version 7.0 or later • The OleDbConnection object • Connects to OLE DB data sources JavaScript, Third Edition

  36. The ADO.NET Connection Object (Cont) JavaScript, Third Edition

  37. The ADO.NET Connection Object (Cont) JavaScript, Third Edition

  38. The ADO.NET Connection Object (Cont) • First step in working with a database in ASP.NET: • Create an instance of the OleDbConnection object using the following syntax: • Var object = new OleDbConnection("connection string”); • Connection string must include the Provider and DataSource name=value pairs • You assign to Provider the name of the .NET data provider: • Identifies the relational database system you want to access JavaScript, Third Edition

  39. The ADO.NET Connection Object (Cont) JavaScript, Third Edition

  40. Opening and Closing a Data Source • You must use the Open() method to open a specific data source • Use the Close() method to disconnect the database connection • Necessary because database connections do not close automatically when an ASP.NET program ends JavaScript, Third Edition

  41. Checking the Database Connection • The State property contains a string indicating the current status of the database connection JavaScript, Third Edition

  42. Executing SQL Commands Through ASP.NET • ADO.NET provides four primary objects for accessing and manipulating data sources: • Command object • DataReader object • DataSet object • DataAdapter object JavaScript, Third Edition

  43. The Command Object • The Command object: • Executes a command, such as an SQL command, against a data source • ADO.NET includes two Connection objects: • SqlCommand object: executes commands against Microsoft SQL Server version 7.0 or later • OleDbCommand object: executes commands against an OLE DB data source JavaScript, Third Edition

  44. The DataReader Object • A DataReader object: • Retrieves read-only, forward-only data from a data source • ADO.NET includes two DataReader objects: • SqlDataReader object: retrieves data from Microsoft SQL Server version 7.0 or later • OleDbDataReader object: retrieves data from OLE DB data sources • You use the ExecuteReader() methodof the OleDbCommand object to create an OleDbDataReader object JavaScript, Third Edition

  45. The DataReader Object (Cont) • The OleDbDataReader object contains various properties and methods for reading the returned data • Read() method: • Advances the leDbDataReader object to the next record • Cursor: • The position within the record set JavaScript, Third Edition

  46. Chapter Summary • A database: • An ordered collection of information from which a computer program can quickly access information • A record in a database: • Contains a single, complete set of related information • Fields: • The individual categories of information stored in a record JavaScript, Third Edition

  47. Chapter Summary (cont.) • A flat-file database: • Stores information in a single table • A relational database: • Stores information across multiple related tables • A primary table: • The main table in a relationship referenced by another table • A related table (also called a child table): • References a primary table in a relational database JavaScript, Third Edition

  48. Chapter Summary (cont.) • A primary key: • A field that contains a unique identifier for each record in a primary table • A foreign key: • A field in a related table that refers to the primary key in a primary table • A one-to-one relationship: • Exists between two tables when a related table contains exactly one record for each record in the primary table JavaScript, Third Edition

  49. Chapter Summary (cont.) • Normalization: • Breaking tables into multiple related tables in order to reduce redundant and duplicate information • A many-to-many relationship: • Exists in a relational database when many records in one table are related to many records in another table • A database management system (DBMS): • An application or collection of applications used to create, access, and manage a database JavaScript, Third Edition

  50. Chapter Summary (cont.) • ActiveX Data Objects, or ADO: • A Microsoft database connectivity technology that allows ASP and other Web development tools to access ODBC- and OLE DB-compliant databases • A one-to-many relationship: • Exists in a relational database when one record in a primary table has many related records in a related table JavaScript, Third Edition

More Related