Retrieving Information about the Tables in a Database

To provide your user with a list of options your application will probably need to get information about the tables in your database. There are several ways to get that information. The most important method is to use the INFORMATION_SCHEMA schema. This schema is standard in every database.

Using INFORMATION_SCHEMA

INFORMATION_SCHEMA is a special schema contained in every database. It contains the definition for several objects inside the database.

INFORMATION_SCHEMA conforms to an ANSI standard that provides for retrieving information from any ANSI-compatible database engine. In the case of SQL Server, INFORMATION_SCHEMA consists of a set of views that query the database's sys* tables, which contain information about the database structure. You can query these tables directly, just as you can query any database table. In most cases, however, it is better to use the INFORMATION_SCHEMA views to retrieve information from the sys* tables.

Note

INFORMATION_SCHEMA sometimes queries tables that you do not really need, which adversely affects performance. In the examples in this chapter, this isn't especially important because the application also has to wait for user input. You should keep this in mind, however, if speed is an important concern for your application.

The basic T-SQL code used to get information about the columns belonging to a table is:

SELECT TABLE_SCHEMA,     TABLE_NAME,     COLUMN_NAME,     ORDINAL_POSITION,     DATA_TYPE FROM INFORMATION_SCHEMA.COLUMNS WHERE TABLE_NAME = '<TABLE_NAME>')

Notice that you select the TABLE_SCHEMA field to obtain the schema for the table. This will be important for creating the appropriate query later. To experiment with the techniques in this chapter, start a new project in Visual Studio.

Starting a New Visual Studio Project

1.	Click Start | Programs | Microsoft Visual Studio 2005 | Microsoft Visual Studio 2005.
2.	Choose File | New | Project from the Visual Studio menu.
3.	Select the Visual Basic node in the Project Types pane and select the Windows Application template in the Templates pane. Name the project Chapter7 and click the OK button.
4.	The application for this example is included in the sample files in the \Chapter7\DynQuery folder. You can cut and paste the code for the procedures that follow from the file Form1.vb.

Obtaining a List of Tables and Views

Typically, you will want to let the user choose the table in addition to the columns for a dynamic query, so you will have to give the user a list of tables from which to choose. Using the following query you can get the necessary information:

SELECT TABLE_SCHEMA, TABLE_NAME, TABLE_TYPE     FROM INFORMATION_SCHEMA.TABLES

You can use this query in your application as follows.

Obtaining the Table List

1.	Double-click the Form1 form that Visual Studio created for you. You will see the Form1_Load procedure. Declare two global variables and add a call to the RetrieveTables procedure in the Form1_Load procedure, so it looks like this: Public Class Form1 Dim SchemaName As String = "", TableName As String = "" Private Sub Form1_Load(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles MyBase.Load RetrieveTables() End Sub
2.	Now add the RetrieveTables procedure shown here beneath the Form1_Load procedure. For this code to work, you must have the AdventureWorks database attached to your SQLExpress Server. To learn about attaching databases to servers, see Chapter 4. Sub RetrieveTables() Dim FieldName As String Dim MyConnection As New SqlClient.SqlConnection( _ "Data Source=.\SQLExpress;" & _ "Initial Catalog=AdventureWorks;Trusted_Connection=Yes;") Dim com As New SqlClient.SqlCommand( _ "SELECT TABLE_SCHEMA, TABLE_NAME, TABLE_TYPE " & _ "FROM INFORMATION_SCHEMA.TABLES", _ MyConnection) MyConnection.Open() Dim dr As SqlClient.SqlDataReader = com.ExecuteReader With dr Do While .Read 'You must preserve the information to use it in a Form or Page SchemaName = .GetString(0) TableName = .GetString(1) FieldName = .GetString(2) Console.WriteLine("{0} {1} {2}", _ SchemaName, TableName, FieldName) Loop .Close() End With 'Assume user has chosen the following schema and table: SchemaName = "Sales" TableName = "Customer" End Sub Note In a real application, the connection string and the SQL string would be maintained in application resources or in the application config file.
3.	Choose Start Debugging from the Debug menu to build and run your project. A blank form will appear and the schema information will be written to the Output pane in the Visual Studio window.
4.	Close the form to end the application.

The preceding Visual Basic code initializes a SqlCommand object named com with the SQL string you want to execute, then executes the SqlCommand object. This is the simplest way to execute a T-SQL sentence from an application.

As an exercise, you can place the schemas and tables retrieved during the form's Load procedure into a user interface on the form so that the user can choose the schema and table to work with. For the example in this chapter, we'll assume the user has selected the Sales schema and the Customer table.

Once the user chooses the table, you can retrieve the list of columns for that table using the same method but with the user's input as the name of the table in the query. To do that, you can code a placeholder in the query string, then replace the placeholder through a call to String.Format. In the code below, the placeholder in the query string is {0}.

Obtaining the Column List

1.	Add the following RetrieveColumns procedure beneath the RetrieveTables procedure. Sub RetrieveColumns(ByVal TableName As String) Dim MyConnection As New SqlClient.SqlConnection( _ "Data Source=.\SQLExpress;" & _ "Initial Catalog=AdventureWorks;Trusted_Connection=Yes;") Dim sqlStr As String sqlStr = "SELECT TABLE_SCHEMA, TABLE_NAME, COLUMN_NAME, " + _ "ORDINAL_POSITION, DATA_TYPE " + _ "FROM INFORMATION_SCHEMA.COLUMNS " + _ "WHERE (TABLE_NAME = '{0}')" Dim tableColumns As New DataTable Dim da As New SqlClient.SqlDataAdapter( _ String.Format(sqlStr, TableName), MyConnection) da.Fill(tableColumns) For i As Integer = 0 To tableColumns.Rows.Count - 1 With tableColumns.Rows.Item(i) Console.WriteLine("{0} {1} {2}", _ .Item(1), .Item(2), .Item(3)) End With Next End Sub
2.	In the Form1_Load procedure, add the following call to the RetrieveColumns procedure after the RetrieveTables procedure. RetrieveColumns(TableName)
3.	Choose Start Debugging from the Debug menu to build and run the project. A blank form appears and the table and column information is written to the Output pane in the Visual Studio window.
4.	Close the form to end the application.

The DataTable in the RetrieveColumns procedure can be used to fill a CheckListBox control or a ListView control with CheckBoxes enabled to let the user select the desired fields.

Add a ListView Control to the Form

1.	Select the Form1.vb [Design] tab in the Visual Studio window.
2.	Choose Toolbox from the View menu.
3.	Drag and drop a Label control from the Toolbox onto your form. Right-click the label and select Properties from the context menu. In the Properties window, change the Label's text to Columns.
4.	Now drag and drop a ListView control from the Toolbox onto your form. Your screen should look similar to this:
5.	Drag the right border of the ListView to make the list wider. Right-click the ListView and select Properties from the context menu. In the Properties window, set the CheckBoxes property to True and set the View property to List.
6.	Right-click the form and choose View Code from the context menu to return to your code. In the RetrieveColumns procedure, replace the Console.WriteLine statement with the following code: ListView1.Items.Add(.Item(2))
7.	Build and run your project. You will see a list of columns with checkboxes in the list on your form, as shown here:
8.	Close the form to end the application.

Once the user selects the table and columns they want to view, your application can create the query. The basic structure of a selection query includes a comma-separated list of columns, like this:

SELECT <Column1>, <Column2>, <Column3>    FROM <Table_Name>

You can easily create the list of columns from your ListView control.

Format User Input for the Dynamic Query

1.	Select the Form1.vb [Design] tab in the Visual Studio window.
2.	Drag and drop a Button control below your ListView control. In the Properties window change the text to Run Query.
3.	Double-click the button. Visual Studio creates a procedure called Button1_Click. Add the following code to this procedure: Private Sub Button1_Click(ByVal sender As System.Object, _ ByVal e As System.EventArgs) Handles Button1.Click Dim baseSQL As String = "SELECT {0} FROM {1}" Dim sbFields As New System.Text.StringBuilder Dim numChecked As Integer = 0 With sbFields For Each el As ListViewItem In ListView1.Items If el.Checked Then numChecked = numChecked + 1 If .Length <> 0 Then .Append(",") End If Console.WriteLine(el.Text) .AppendFormat("[{0}]", el.Text) End If Next End With Console.WriteLine(sbFields) End Sub Note When you have to manipulate a string more than two times, it is better to use a StringBuilder object to achieve your result than to use string concatenation.
4.	Build and run the project. Select some columns in the ListView control, click the Run Query button, and examine the comma-delimited list of columns displayed in the Output pane.
5.	Close the form to end the application.
6.	Finally, you can create the complete dynamic query statement by adding the column list and the name of the table. Because the tables can belong to a schema, it is a good idea to use the fully qualified name of the table in the query. Tip In your application, if security is not an issue, you might want to display the query string in a TextBox control and let the user modify it before executing it. See the section "Parameters and Security in Dynamic Queries" for information about the security risks involved in allowing a user to directly modify query strings.

Build and Execute the Dynamic Query

1.	Replace the Console.WriteLine statement at the end of the Button1_Click procedure with the following code, which builds the dynamic query string: Dim tblsql As String Dim txtsql As String tblsql = String.Format("{0}.{1}", SchemaName, TableName) txtsql = String.Format(baseSQL, _ sbFields.ToString, tblsql)
2.	Now add the following code at the end of the Button1_Click procedure to execute the dynamic query. For this example, the first 100 rows of the results are displayed in the Output pane. In a real application, you would use the DataTable as a DataSource for a DataGridView control. DataGridView controls are ideal for displaying query results to the user. Dim MyConnection As New SqlClient.SqlConnection( _ "Data Source=.\SQLExpress;" & _ "Initial Catalog=AdventureWorks;Trusted_Connection=Yes;") Dim com As New SqlClient.SqlCommand(txtsql, MyConnection) Dim tableResults As New DataTable tableResults.Clear() Dim da As New SqlClient.SqlDataAdapter(com) da.Fill(tableResults) For i As Integer = 0 To tableResults.Rows.Count - 1 With tableResults.Rows.Item(i) Dim rowstr As New System.Text.StringBuilder For j As Integer = 0 To numChecked - 1 rowstr.Append(.Item(j)) rowstr.Append(" ") Next Console.WriteLine(rowstr) End With If i > 100 Then Exit For Next
3.	Build and run the project. Select some columns in the ListView control and click the Run Query button. The results of the dynamic query are displayed in the Output pane. You can select different columns and click the button again to build and execute a different dynamic query.
4.	Close the form to end the application.

Executing a Dynamic Query on Behalf of Another Account

One of the best security practices is to deny READ permission on tables to avoid unauthorized access, so sometimes an application needs to execute a query dynamically generated by the user but using other credentials.

In SQL Server 2005, you can use the EXECUTE command to act as another user for a particular command. The syntax for the EXECUTE command is:

EXECUTE ('<dynamic query>') as USER='<User Name>'

Caution

This is a very dangerous idea. As you will see later in the chapter, it leads to a potential security risk. To avoid security breaches, your application has to be sure what kind of query it is executing.