1 / 39

ASP.NET 2.0

ASP.NET 2.0. Chapter 7 Managing Data Sources. Objectives. Working with Database Applications. Data can be stored and formatted using a variety of tools and technologies A relational database management system (RDBMS) is a system that stores data in related tables

lilly
Télécharger la présentation

ASP.NET 2.0

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. ASP.NET 2.0 Chapter 7 Managing Data Sources

  2. Objectives ASP.NET 2.0, Third Edition

  3. Working with Database Applications • Data can be stored and formatted using a variety of tools and technologies • A relational database management system (RDBMS) is a system that stores data in related tables • An RDBMS may contain additional tools to help create and manage tables, and provide an interface for programmers to develop applications that can communicate with the database ASP.NET 2.0, Third Edition

  4. Characteristics of a Relational Database ASP.NET 2.0, Third Edition

  5. Relationships Between Tables ASP.NET 2.0, Third Edition

  6. Relationships Between Tables (continued) ASP.NET 2.0, Third Edition

  7. Applications That Store Relational Data • There are three examples of popular relational databases • Microsoft Access • Microsoft Access stores the information in a single file and can only handle a few concurrent connections through the web, and is less secure than SQL Server • Microsoft SQL Server • Microsoft SQL Server is a robust database application that also allows you to create stored procedures and supports transactions • Oracle • Oracle databases commonly are used with web applications built with Java and PHP technologies ASP.NET 2.0, Third Edition

  8. Visual Studio .NET Built-in Database Tools • A variety of Visual Studio .NET tools allow you to create and manipulate a database • With these tools, you can create the database from scratch or convert the data from an existing format into a relational database • You can also import data from a spreadsheet into a Microsoft Access database • In addition, you can convert a Microsoft Access database to a SQL Server database ASP.NET 2.0, Third Edition

  9. Creating a SQL Server Database in Visual Studio .NET • Visual Studio .NET provides a graphical user interface that you can use to create a connection to a database • When you create the database, you need to know the type of authentication required for access to the SQL Server • You can use the authentication built within Windows NT, or SQL Server authentication • You can create the database in either the Database Explorer window or the Solution Explorer window • If you create the database in the App_Data folder in the Solution Explorer window, then both the SQL Server data file and the transaction log are stored in the App_Data folder • This database can be easily moved by simply detaching the database from this instance of SQL Server and reattach it to another instance of SQL Server ASP.NET 2.0, Third Edition

  10. Using the Table Designer ASP.NET 2.0, Third Edition

  11. Using the Table Designer (continued) ASP.NET 2.0, Third Edition

  12. Using the Table Designer (continued) ASP.NET 2.0, Third Edition

  13. Creating a Relationship with the Database Diagram ASP.NET 2.0, Third Edition

  14. Views and Queries • TheVisual Studio .NET SQL Editor is used to create and edit views, SQL scripts, and stored procedures • Views and queries allow you to retrieve information from the database • Queries are SQL commands that retrieve data from the database • SQL scripts and stored procedures may contain the same SQL commands used in queries and views ASP.NET 2.0, Third Edition

  15. Using Views and Queries • Queries and views can both be used to retrieve data • A view is an SQL statement that is saved with the database as part of a database design • Views can contain only select statements, which means that views can retrieve data; they cannot be used to create, update, or delete data • View Designer is used to create and manage views and works only with SQL Server • A query is an SQL statement that is saved with a Visual Studio database project and not with the database • The Query Designer can be used to create, update, and delete data ASP.NET 2.0, Third Edition

  16. Building a View with the Query and View Designer • The Query and View Designer allows you to create a query or view within Visual Studio .NET • To aid you in creating views and queries, there are four windows called panes displayed within the Query and View Designer • Diagram Pane • Grid Pane • SQL Pane • Results Pane ASP.NET 2.0, Third Edition

  17. SQL Statements • You can build and modify the SQL commands to retrieve a select group of records • To retrieve all the records in the Products table, you can use this SQL command: • SELECT * FROM Products • The keyword FROM is used to indicate the name of the table where the data is retrieved • You can retrieve the list of manufacturers by using the Select statement: • SELECT DISTINCT Manufacturers FROM Products • The view can use the keyword TOP to retrieve a subset of the records in the table: • SELECT TOP (50) PERCENT * FROM Products ASP.NET 2.0, Third Edition

  18. Creating Search Conditions • You can use keywords to qualify a subset of records and columns to retrieve • The WHERE clause of a search condition consists of the keyword WHERE, an expression, a comparison operator, and another expression • Valid comparison operators include +, <, >, <>, IS, and ISNOT • Example: WHERE Member='Staff' AND Salary>55000 • The GROUP BY clause allows you to aggregate the records based on the values of one of the columns • Example: SELECT *FROM Products GROUP BY CategoryID • The JOIN clause allows you to combine one or more tables into a single set of records ASP.NET 2.0, Third Edition

  19. Mondial Database • “of whole world: relating to or involving the entire world” (Encarta) • SQL Server file on web site in recordings directory • http://www.dbis.informatik.uni-goettingen.de/Mondial/ • Various sources of country data ASP.NET 2.0, Third Edition

  20. Some Views Of Mondial • Country-City • Country-Ethnic • Country-Language ASP.NET 2.0, Third Edition

  21. SQL Stored Procedures and SQL Server Scripts • Stored procedures can be used to create an SQL command that is stored within the database • The stored procedure is more efficient than an SQL statement because it is precompiled by the server • Stored procedures are created within Visual Studio .NET • SQL Server scripts provide you with the ability to store SQL commands in an external text file • SQL Server scripts are often used to create and back up your SQL Server databases ASP.NET 2.0, Third Edition

  22. Input and Output Parameters • A stored procedure can be used to run a SQL statement • A good technique is to pass values to the stored procedure that can be used in the SQL statement • The name of any parameter within a stored procedure always begins with the @ symbol • An input parameter is a value that is passed to the stored procedure when it is called • The data type and length of this value must match the data type and length that is specified within the stored procedure • Output parameters can send values back to the object that called the stored procedure ASP.NET 2.0, Third Edition

  23. The Return Code • You can have a return value passed back to the stored procedure call • The return code is indicated with the keyword RETURN and is often used to indicate the status of the query • By default, the return value is set to 0, which means the query execution was successful • When the value is 1, the required parameter value was not passed to the query • When the value is 2, the parameter passed was not valid ASP.NET 2.0, Third Edition

  24. Modifying a Stored Procedure with the SQL Editor • The SQL Query Builder has the same user interface as the Query and View Editor • The code to create the query, however, is stored in the stored procedure • Within the stored procedure, you can edit the blocks of code that are enclosed within a blue line, via the SQL Query Builder • You only need to right-click the block and select Design SQL Block ASP.NET 2.0, Third Edition

  25. Using Built-In Views and Stored Procedures • Built-in views and stored procedures allow you to retrieve information about your database • The sp_tables built-in stored procedure would return a list of tables in the database • The sp_columns built-in stored procedure would return the list of columns for the name of the table that is passed as a parameter to the stored procedure • You can access the stored system views and stored procedures in the Database Explorer • It is important to provide strong security for the SQL server, because some of the built-in stored procedures can be used maliciously if your SQL Server security has been breached ASP.NET 2.0, Third Edition

  26. Database Connections • In order to create a connection to databases, one must know: • The parts of a connection string • How to store a connection string in the web configuration file • How to create a connection to the database from a web page • How to move data from Access to SQL Server ASP.NET 2.0, Third Edition

  27. Understanding the Connection String • The connection string is a piece of text that contains the location of your database and any additional information required to access the database • The connection string format is different for each database because each database has different connection string requirements • When you use Visual Studio .NET to create a connection to a database, you will fill out the connection information using a dialog box, and the software will create the connection string for you • The following code is a sample of a connection string to a SQL Server database: • Data Source=.\SQLEXPRESS; AttachDbFilename="C:\[Yourfolder]\Chapter7\App_Data\Chapter7.mdf”; Integrated Security=True; ASP.NET 2.0, Third Edition

  28. Storing Connection Strings in the Web Configuration File ASP.NET 2.0, Third Edition

  29. Storing Connection Strings in the Web Configuration File (continued) ASP.NET 2.0, Third Edition

  30. Storing Connection Strings in the Web Configuration File (continued) ASP.NET 2.0, Third Edition

  31. Creating a Connection to a Database from a Web Page (continued) ASP.NET 2.0, Third Edition

  32. Creating a Connection to a Database from a Web Page (continued) ASP.NET 2.0, Third Edition

  33. Creating a Connection to a Microsoft Access Database ASP.NET 2.0, Third Edition

  34. Creating a Connection to a Microsoft Access Database (continued) ASP.NET 2.0, Third Edition

  35. Creating a Connection to XML Data ASP.NET 2.0, Third Edition

  36. Moving Data to SQL Server ASP.NET 2.0, Third Edition

  37. Summary • Relational databases store data related tables. Tables consist of rows and columns. When you create the column, you must identify the data type, the file size, and if the column allows, null values. • Relationships between tables are defined in the Database Diagram. The primary key column contains a unique value for each row of data and is used to create the relationship in other tables. When values of primary keys are stored in other tables, they are called foreign keys. • Normalization is the process used to design databases, which will reduce redundancy, promote data integrity, and improve database performance. • You can use the Visual Database Tools within Visual Studio .NET to create and maintain your databases. ASP.NET 2.0, Third Edition

  38. Summary (continued) • The Table Designer allows you to create tables and enter data. • The Query and View Designer allows you to create queries called views. Views are stored in the database and only are used with select statements. Queries must be executed manually but can contain INSERT, UPDATE, and DELETE statements. • The SQL Editor allows you to create stored procedures. Stored procedures are SQL commands that are stored with the SQL Server, and then compiled. Therefore, stored procedures run faster than SQL commands that are stored on a web page. • The SQL Query Builder allows you to use the visual tools to build your stored procedures. SQL Server scripts are files that contain SQL statements and are often used to create new databases and backup existing databases. ASP.NET 2.0, Third Edition

  39. Summary (continued) • You can store the connection strings in the root level Web.config application configuration file making them available from any web page in the application using global application variables or the connectionStrings element. • The connection string syntax varies with each type of database. The connection string contains the information needed to identify the type of database, the location of the database, the software provider required to communicate with the database, and user permissions required to access the data in the database. • You can convert a Microsoft Access database to SQL Server using the upsizing wizard. ASP.NET allows you to create connections to non-Microsoft data sources such as XML files. ASP.NET 2.0, Third Edition

More Related