Visual InterDev

  • Article

      

Microsoft® Visual InterDev™ allows you great flexibility in designing Web applications with a database component. You can use any database supported by ActiveX® Data Objects (ADO) for which you have drivers, including Microsoft® SQL Server™, Microsoft® Access, Oracle, and others.

You can interact directly with the database or use views, stored procedures, and other database entities to manage the database. The database can be physically located on the same computer as your Web server or on a different computer. You can do all your database access using the Web server, or you can access the database directly from a client computer.

To prepare for the data access features of Visual InterDev, consider these concepts first:

  • Database Integration in Visual InterDev

  • Web Servers and Databases

  • Data Connections

  • Database Management

Database Integration in Visual InterDev

Visual InterDev integrates a wide array of features to help you create Web applications with a database component:

  • Database projects   A type of project you can add to your Visual InterDev solution that includes tools required to build and manage your databases as a separate component from Web pages.

  • Data View window   A window that provides a live view of the data to which your database or Web project is currently connected. From the , you can launch tools to manage your database, whether you are in a database project or Web project.

  • Microsoft® Visual Database Tools   A set of tools for managing and querying your database graphically. The Database Designer allows you to create and modify table definitions, column definitions, and relationships between tables in Microsoft SQL Server and Oracle databases. Using the Query Designer, you can visually create and run SQL statements. A View Designer enables you to create views.

  • Data Environment   A repository in your Web project for information required to connect to and access data in databases. The data environment stores reusable connection strings that allow you to access databases from your Web pages. In addition, the data environment stores data commands that represent recordsets, based on database objects or SQL commands.

  • Data-bound controls   Controls such as text boxes, buttons, and so on, that you can put on a Web page and that are automatically bound to specific fields in a database record. Data-bound controls already include script required to make data connections, extract data, and update the database so you can build database access into your Web pages with little or no scripting.

  • **Source control for database objects   **A link between databases and your source control system so you can put SQL scripts and compiled stored procedures under source control. This makes development simpler and more secure in companies with more than one database programmer.

Web Servers and Databases

In Web applications that access databases, two server-like functions are occurring: the Web server handles requests for pages and a database server or equivalent software handles database access. Although these two server functions are part of the same application, each functions separately.

You can configure Web servers and database servers in various ways, depending on how you want people to use the database server, what the target audience is for your Web application, and how the application relates to other applications in your business. The following configuration options are possible:

  • The database server can run on the same computer as the Web server.

  • The database server can be on a separate computer from the Web server. You might do this if you wanted to optimize each computer for its respective task, if you want to share a database server among several Web servers, or if the database server is used for applications other than the Web application.

  • The database server and Web server can be entirely separate processes that do not communicate. This model is practical if client computers can access the database directly, which can increase performance.

  • The database can be on a local (client) computer. You might do this in special cases, such as testing.

Using the Web server as a gateway to the database is the most common strategy, because it gives Web applications the widest reach — the server does the database work, so it doesn't matter what type of browser the user has. Server-side database access is therefore a good choice for public Internet sites, where users might access the site with any browser.

Client-side access, on the other hand, can provide a richer user experience that emphasizes performance, because the browser can manage data sets independently from the Web server. However, client-side access requires Internet Explorer DHTML as a browser, and also that the database be accessible via specific database drivers. Client-side database access is therefore most practical in intranet sites, such as a corporate Web site, where users access the site using a standard browser with predictable features.

For information about Web server and database server security, see Security.

Data Connections

To use a database, you add a data connection to your Visual InterDev project, which tells it how to access the database. Typically, a data connection includes information such as:

  • The type of database you are accessing (for example, Microsoft SQL Server) and the server name (if appropriate).

  • The name of the database (for example, pubs).

  • A user name.

  • A password.

You can add as many data connections to your project as you need. For example, if your application requires access to two different databases, you would add two data connections. For details about how to add a data connection, see Connecting to a Database.

Creating Connections

Visual InterDev allows you to connect to databases using two protocols. The preferred protocol is OLE DB, an efficient, full-featured protocol that allows access to a wide variety of data sources. When you install Visual InterDev, the OLE DB data providers for several commonly-used databases, including Microsoft Jet (Access), Microsoft SQL Server, and Oracle, are installed on your computer.

Alternatively, you can use ODBC, a widely-used standard for database access. Use an ODBC driver if you do not have an OLE DB provider for the database you want to access.

Using ODBC Drivers

If you are creating a data connection using an ODBC driver, Visual InterDev reads connection information from a DSN (data source name) on your computer. Your computer can provide the DSN information in either a file DSN (stored in a .dsn file) or a machine or system DSN (stored in the Windows® registry of your computer).

In the case of a file DSN, Visual InterDev reads the connection information from the DSN and extracts the connection string. It then adds a connection node to your project's data environment.

The connection string extracted from the DSN is stored in your Web project in a binary file called DataEnvironment.asa. This is referred to as a "DSN-less" connection, because the Web project no longer needs the DSN to establish the connection. From then on, Visual InterDev can simply read the connection information from the binary file as needed.

In the case of using a machine or system DSN, a connection string is not used. Instead, you will have to recreate the DSN on each development machine as well as the Web server.

At run time, the server must have the appropriate ODBC driver to make the connection to the data source. By default, ODBC drivers are installed on both your development computer and your server when you install Visual InterDev. However, if you deploy your applications to other servers, you must make sure that those servers also have the correct ODBC drivers.

Security with Data Connections

Generally, when you are creating an application, you want to have the widest possible range of privileges so you can manipulate the database and data as needed. However, when users access the data at run time, you want to limit them to the minimum privileges required to run the application.

Your first task is to create user profiles on the database. For example, you might create an administrator-level user profile for yourself to use during development. You can also create a user profile with privileges appropriate for users of your application. If the database server is on a different computer than the Web server, you must also make sure that the correct user profiles have been defined at the operating system level. For more information, see Security.

Later, when you create a data connection, you can specify both design-time and run-time authorization. Design-time authorization is the user name and password you use when developing the application. Run-time authorization is the user name and password that Visual InterDev will use when connecting to the database while the application is running.

When specifying design-time authorization, you choose how secure your authorization is. For maximum security, you can choose to be prompted for a password each time you connect to the database. If you are not as concerned about security, you can choose not to be prompted. In that case, Visual InterDev encrypts your password and stores it in the project.

When you specify run-time authorization, you do not have this choice: you cannot prompt users for a password, because the prompting would necessarily have to occur on the Web server. Therefore, you must include the password with the user name. The password is encrypted and stored in the project so it can be passed to the database each time a user connects to the database when the application is running.

Database Management

In addition to helping you create Web pages that are linked to databases, Visual InterDev allows you to manage databases while directly connected to them at design time. Depending on the features of your database and your access privileges, you can use Visual InterDev tools to add, remove, or modify:

  • Databases

  • Tables or columns

  • Views and synonyms

  • Relationships between tables

  • Indexes

  • Constraints and triggers

  • Stored procedures, functions, and packages

  • Queries that return sets of data, or queries that modify a database by adding, updating, copying, or deleting records

Note   Features that allow you to make structural edits to databases are available only in the Enterprise Edition of Microsoft® Visual Studio™.

Managing databases is a separate task from adding database functionality to a Web application. Therefore, to manage databases in Visual InterDev, you create a database project. To help you perform various database management tasks, a database project in Visual InterDev provides the following tools:

  • **Data View window   **A window that displays all the database objects that you can currently work with. From the Data View window, you can edit objects such as tables, views, stored procedures, and triggers.

    Data View window showing available database objects

  • **Database Designer   **A tool that displays your Microsoft SQL Server or Oracle database as a , which you can edit to add or modify table and column definitions, define relationships, create indexes, and add constraints.

    Database diagram representing a database visually

  • Query Designer   A designer that allows you to visually create SQL statements to query or modify a database.

    Query Designer being used to create an SQL statement

  • View Designer   A version of the Query Designer that allows you to visually create the SQL Statement that defines a view.

  • Stored Procedure editor   A window for creating stored procedure that includes a link to the Query Designer to construct SQL statements.

  • Trigger editor   A window for creating triggers.

  • Script editor   A window for creating SQL scripts, which are SQL statements that are independent of any particular database. You can put SQL scripts under source control as well.

For more information about the tools available for managing a database, see .