Database Connectivity

Powerful database connectivity options and visual database tools are integral to Visual InterDev. Its database connectivity features are based on the industry database access standard ODBC, and its visual tools work with any database supporting ODBC, including Oracle, SQL Server, Microsoft Access, Visual FoxPro, Borland dBASE, Borland Paradox, Informix, Sybase, IBM DB/2, and many others. In addition, Visual InterDev lets you create highly scalable database solutions, even for Internet applications with thousands of concurrent users, because it leverages the Active Server framework. The core database components of Visual InterDev include:

• Data Environment
• Data View
• Visual database tools (including the Query Designer and the Database Designer)
• Database design-time controls

The integrated database access tools are also based on ADO, which provides scalable, object-oriented database connectivity to any ODBC data source. In addition, Visual InterDev can be used with alternate data access components developed by third parties.

The Data Environment

Visual InterDev 6 changes the way we work with databases and attach them to Web pages. Just as Visual InterDev 1 took a significant step in changing how developers approach this issue, Visual InterDev 6 makes an even more drastic step by adding the Data Environment as well as many other database automation features. The Data Environment collects your data connection and data manipulation code in one place. It provides a standard interface for creating data-related objects and for placing them on Web pages. And it also provides a layer of abstraction, allowing you to create and modify objects that represent your data independently of the database.

The Data Environment is an object layer around ADO. ADO is still used to access the back-end database, and you can script either ADO or Data Environment commands. ADO is used to provide world-class database connectivity within Visual InterDev applications. Specifically designed for Web-based data access, ADO provides an object-based approach to data access over the Web. Via ActiveX scripting, connections to databases can be easily established to any ODBC data source, and a variety of methods within the component provide you with a powerful set of database commands for manipulating data and creating data-driven Web pages.

ADO lets you manipulate database-defined data types, including binary large objects (BLOBs), such as GIF and JPG images, that are retrieved from databases and dynamically written into Web pages. In addition, ADO provides a rich set of properties for setting locking levels, cursor options, query and login time-outs, transaction support, result set scrolling, error handling, and more. ADO provides an easy-to-use, object-based approach to database programming. Using VBScript or JScript, ADO gives you maximum flexibility to develop powerful database functionality within your Web applications.

Data View

Visual InterDev gives you an integrated view into any ODBC data source being used within a Web site. Data View provides a visual interface to all the databases being used. Figure 1-2 shows the browsing of a database within a Web site directly in Visual InterDev.

Figure 1-2. The Data View pane at the bottom right shows an open data source to a SQL Server database, including its tables. (You can also see the Recordset1 design-time control near the center of the editor page.)

Besides depicting each database connection being used in the site, Data View provides a live connection to each database, letting you work directly with each database within the IDE during Web site development. For example, you can open any database to view tables, defined views, and stored procedures.

Data View can provide detailed information about objects and properties within each database, including table definitions and field types, key structures, and stored procedures. Data View works in conjunction with the Query Designer and Database Designer features; it provides a sophisticated database development, administration, and maintenance system that is tightly integrated with the Visual InterDev IDE. Data View works against any OLE DB or ODBC-compliant data source and can show multiple connections against heterogeneous databases.

Query Designer

Visual InterDev provides a sophisticated SQL Query Designer that works against any ODBC data source. The integrated Query Designer provides an extremely easy-to-use interface for visually constructing even the most complex SQL statements. You can open live views of data sources, and you can drag tables directly into a design pane of the Query Designer window to build their queries. As the user selects fields from tables, the SQL pane shows the dynamically constructed SQL statement. You can directly modify the SQL statement, and the changes are reflected in the design pane. In addition, the query builder lets you execute any SQL statement to test it, displaying the results in a results pane. The Query Designer also lets you easily create complex queries across multiple tables and databases by automatically creating SQL joins and visually depicting these relationships in the design pane. Figure 1-3 below shows a view of the Query Designer.

The SQL pane is a live pane and can be used to create stored procedures, execute arbitrary data definition language (DDL) commands directly against any ODBC data source, or perform ad hoc SQL queries. Visual InterDev thus provides a complete, tightly integrated database development and administration tool for Web developers. The Query Designer works in conjunction with the Query control (a design-time control) so that once developed and tested in the Query Designer, the HTML and server scripting necessary to execute the query is generated and embedded in the appropriate ASP Web page.

Figure 1-3. The Query Designer lets you visually construct complex SQL queries against any ODBC data source.

The Database Designer

In addition to providing the visual Query Designer, which works against any ODBC-compliant database, Visual InterDev also provides a complete Database Designer for users of SQL Server, Microsoft Access, and Oracle databases. The Database Designer is based on an extensible architecture, so support for other database systems can later be added. Using the Database Designer, you can modify the structure and properties of existing databases. Plus, database administration operations that used to take hours can now be done with a couple of mouse clicks.

For example, you can use the Database Designer to change the data type on a field (for instance, from a type CHAR to a type INT) with a simple drop-down selection. Visual InterDev then simply changes the field type, which ordinarily requires manual DDL operations to export the entire table, drop the table, create a new table with the new data type, and import the data into the new table. The Database Designer can generate DDL scripts that can be reviewed and submitted to database administrators for review and execution in controlled database environments.

The Database Designer can also be used to set up database designs (such as key structures and properties) and logical relationships between tables (such as foreign key relationships with automatic triggers and cascading updates, inserts, and deletes). This tool provides a powerful and flexible database environment that simplifies the most complex SQL Server administration tasks.

Scalability

Some database tools for Web applications offer visual aids to ease the development of data-driven Web sites, but often they do not provide the scalability required for commercial Internet sites or large-scale production intranet sites that host mission-critical applications. Visual InterDev provides both ease of use with extensive visual tools and the scalability required for large-scale, data-driven Web sites. For example, global database connections can be established for an entire site, and the Active Server framework pools these database connections across users. Pooling, connection caching, and time-out values are all established automatically based on default properties, but you can easily customize them within Visual InterDev.

Visual InterDev also makes it easy to connect to multiple heterogeneous databases within a Web site, returning or updating data that has been visually integrated within a single HTML page for the user. For example, Visual InterDev can be used to return data from an Oracle database running on a UNIX server, as well as data from a DB/2 database running on an IBM mainframe, all integrated within a single HTML page for the user.