Create a Point-and-Click Query Tool for Users Using a Web Form

8,7 Tạo một điểm-và-Click Query Công cụ cho người dùng sử dụng một mẫu Web Như hữu ích-nếu không như vậy-hơn ví dụ hiển thị trong 8,3 Làm thế nào để, bài tập này sẽ cho bạn thấy làm thế nào để thêm một công cụ truy vấn dữ liệu hướng đến ứng dụng ASP.NET của bạn. Khi các ứng dụng cơ sở dữ liệu tạo, thậm chí trên Web,
Nội dung trích xuất từ tài liệu:
Create a Point-and-Click Query Tool for Users Using a Web Form 8.7 Create a Point-and-Click Query Tool for Users Using a Web Form As useful-if not more so-than the example shown in the 8.3 How-to, this exercise will show you how to add a data-driven query tool to your ASP.NET application. When creating database applications, even on the Web, one of your clients inevitably wants to be able to examine the data in his database, and not necessarily in edit pages. The client wants to be able to list his data out and examine the data at his leisure. Giving the user the flexibility to do this via the Web is not as big of a hassle as it sounds. This How-To will show you how to create a page to view the tables in your database, using a nice point-and-click interface. Technique To accomplish the task just presented, you will be using the OleDbCommand and DataReader objects. Along with these objects, you will be using some stored procedures that SQL Server supplies. Those stored procedures list the various objects within a SQL Server database-in this case, Northwind's tables and columns. You will take the elements returned in the DataReader object and load the Add method of the ListBox object. You will also use the Session object as you did in the previous How-To to save a DataTable object for use over trips to the server. Finally, the ViewState object will be used to store a string variable when going to the server and back. The ViewState object is a good .NET state object to use for small pieces of data, such as strings. Steps Open and run the VB.NET -Chapter 8 solution. From the main Web Form, click on the hyperlink with the caption How-To 8.7: Create a Point-and-Click SQL Server Query Tool for Users Using a Web Form. When the new page opens, the first list box you see to the left is populated with the tables from Northwind. Click on the Customer table, and you will see the columns listed in the list box labeled Columns. Click on the CompanyName and ContactName, and you will see the SQL String text box filled in. After clicking on the View button, the Web page will look like the one displayed in Figure 8.14. 1. Create a Web Form. Then place the controls shown in Figure 8.14 with the properties set forth in Table 8.9. Table 8.9. Property Settings for Controls on the Point-and-Click Web Form Object Property Setting DOCUMENT bgColor buttonface Label Name Label1 Text Tables Label Name Label2 Text Columns Label Name Label3 Text SQL String Label Name Label4 Text Data Display ListBox Name lstTables AutoPostback True ListBox Name lstColumns SelectionMode Multiple AutoPostback True DropDown Name ddSortBy AutoPostback True TextBox Name txtSQLString MultiLine True Button Name btnView DataGrid Name dgDisplay AllowPaging True Hyperlink Name hplBackToMain Text Return To Main NavigateURL wfrmMain.aspx 2. Tip Notice that the lstTables list box allows the user to choose only one table at a time, whereas lstColumns allows the user to choose multiple columns. A great enhancement to this tool would be to allow the user to choose multiple tables and have the application figure out the relationship between tables and create joins automatically. 3. In the class module for the form, add the following Private declaration just below the line of code that reads Web Form Designer Generated Code: 4. Private mdtDisplay As DataTable 5. On the Web Form, add the code in Listing 8.40 to the Load event. The first task is to load the tables list box, performed by the subroutine LoadTables(), which is also in this listing. The form only calls this routine if it is the first time into the page, by checking for Not Me.IsPostBack. The form then tests to see whether the Session object has an entry called MyDisplayDataTable. If the entry exists, then mdtDisplay is referenced to it, meaning that this time through the Load event is probably occurring on a trip back from the server. The entry exists, and the code needs to set a reference to it. In LoadTables, the routine first creates a new OleDbConnection object called ocnn, an OleDbCommand object called ocmdTables. It then assigns the built-in SQL Server stored procedure called sp_Tables when instantiating ocmdTables. After establishing the CommandType as being CommandType.StoredProcedure and then opening the connection, the data reader called odrTables is created by calling the ExecuteReader method off ocmdTables. Listing 8.40 frmHowTo8_7.vb: Executing a SQL Server-Supplied Stored Procedure That Lists the Tables in the Database Private Sub Page_Load(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles MyBase.Load 'Put user code to initialize the page here If Not Me.IsPostBack Then LoadTables() End If If Not (Session ...