SQL-DMO provides a variety of ways to return SQLObject-List objects. One of the most versatile is the ListObjects method. With this method, you can return lists for user-defined tables, views, and stored procedures, as well as all database objects including user-defined and system-defined objects. The items in a SQLObjectList object need not all have the same type, as the items in a collection must. Fourteen SQL-DMO methods return SQLObjectList objects for SQL Server elements such as columns, keys, and permissions. Refer to BOL for information about all the ways to return SQLObjectList objects.
Listing 2 displays two procedures that apply the ListObjects method to return a SQLObjectList object. The procedures use the object to enumerate all of a database's user-defined tables. The first procedure specifies the database name and prints the list that the second procedure, which is a function procedure, returns. Listing 2 works with the cabxli server. You must change both the server and database name specifications so that they're appropriate for your environment. In this example, the ListObjects() method applies to the database collection member you designated in the first procedure. The method's argument instructs it to return all user-defined tables. The function procedure uses the For...Each loop to iterate through the list items as objects, but the function appends the items to a string variable, str1. The line immediately after the loop strips the "; " delimiter from str1 for the next list item name. (You could use a list in this format to designate the elements in a list box or combo box on an Access form.)
Creating a Database Objects Explorer
Figure 1 shows an Access form serving as an explorer for SQL Server tables and their columns. (To download this form, see the More on the Web box.) The combo box shows a list of tables within a server's database. After the user chooses a table, the application populates the list box with the selected table's column names. If the user chooses another table from the combo box, the form automatically updates the list box's contents to the newly selected table's column names.
The form contains a label control along with two other unbound controls: a combo box and a list box. Procedures in a programming module behind the form control the behavior and contents of the combo box and list box controls. Listing 3, which shows the code that produces Figure 1's Access form, contains three main components: a general area and two event procedures. Callout A in Listing 3 marks the general area, which precedes the Form_Load() and Combo0_AfterUpdate() event procedures. The general area includes some constant and object declarations. You need to change the constant declarations so that they contain the names of a server and a database in your environment. Users can specify the database they want to use, but I kept the code simple for tutorial objectives.
The object declarations work in any environment. By declaring objects in the general area, the application makes them available to all the module's procedures. The OpenConnection() subprocedure doesn't need to reside in the general area. Placing the subprocedure's code outside both event procedures reinforces the idea that the procedure assigns object references that any procedure in the module can use.
The Form_Load() event procedure in Callout B in Listing 3 runs before the form loads. This event procedure displays a message that says the procedure is preparing the explorer for use. This message serves as a subtle warning that the form takes several moments to open. Connecting the srv1 object reference to the database server and assigning the dbs1 and cols1 object references take up most of the wait. These activities occur in the OpenConnection() subprocedure.
The OpenConnection() subprocedure assigns a value to the cols1 object reference, in the same way as the Combo0_AfterUpdate() event procedure in Callout C in Listing 3 performs that function. Making the object reference assignment in the OpenConnection() subprocedure substantially reduces the time required to make the assignment initially in the Combo0_AfterUpdate() event procedure. The design assumes that waiting for a form to open is better than waiting after a form is opened and supposedly ready for use. Besides, you must update the cols1 collection in the Combo0_AfterUpdate() event procedure to reflect the table that the user selected.
After calling the OpenConnection() subprocedure, the remainder of the Form_Load() event procedure populates the combo box with the target database's table names. The target database's name is in DbsName. The code assigns a SQLObjectList object with all the user-defined table names in the obj1 object reference. Then, the code iterates through the list items and creates a string in a format suitable for the Combo0 RowSource property. The event procedure concludes by making two property assignments for the combo box. The first property assignment tells the control how to interpret the table- name list, and the second assignment is the list of table names.
The Combo0_AfterUpdate() event procedure automatically runs immediately after a user makes a selection from the combo box. This event procedure performs two tasks. First, it compiles a list of column names for the table that the user selected. Second, it assigns that list as the source for the list box. Then, the procedure iterates through the Columns collection members for the selected table because a SQLObjectList object has no way to return only column names.
Basic Skills
These examples explain the basic skills for using SQL-DMO in custom SQL Server solutions and give you a simple explorer for the table and column names in a database. Understanding SQL-DMO programming is particularly important for MSDE developers, who can't expect that their custom-application clients have access to Enterprise Manager or other client-management tools.
End of Article
Prev. page
1
[2]
next page -->
