Create a Point-and-Click SQL Server Query Tool for Users Using a Windows Form

8,3 Tạo một điểm-và-Click SQL Server Query Công cụ cho người dùng sử dụng một khách hàng Mẫu Windows thường muốn có một phương tiện truy vấn các bảng, nhưng họ không nhất thiết phải biết làm thế nào để tạo ra câu lệnh SQL.
Nội dung trích xuất từ tài liệu:
Create a Point-and-Click SQL Server Query Tool for Users Using a Windows Form 8.3 Create a Point-and-Click SQL Server Query Tool for Users Using a WindowsFormClients usually want a means of querying the tables, but they do not necessarily knowhow to create SQL statements. This example describes how to create a point-and-clickquery interface using a Windows Form and display fields from individual tables as theyare chosen.In just about every application you create, your clients need a means to view the data andwant to be able to create their own lists. However, most dont want to have to learn howto create SQL statements. In this How-To, you will see a method for not only creating apoint-and-click query tool that will allow the users to examine all tables in the database,but also for using the Windows Form in an application without modification.TechniqueTo accomplish the task just presented, you will be using the OleDbCommand andDataReader object. Along with these objects, you will be using some stored proceduresthat SQL Server supplies. These stored procedures list the various objects within a SQLServer database-in this case, Northwinds tables and columns.You will take the elements returned in the DataReader object and load the Add method ofthe ListBox object.StepsOpen and run the VB.NET -Chapter 8 solution. From the main Windows Form, click onthe command button with the caption How-To 8.3. The first list box you see to the left ispopulated with the tables from Northwind. Click on the Customer table, and you will seethe columns in the next list box labeled Columns. Click on the CompanyName andContactName, and you will see the SQL String text box filled in. After clicking on theView button, the form will look like the one displayed in Figure 8.5. 1. Create a Windows Form. Then place the controls shown in Figure 8.5 with the properties set forth in Table 8.4. Table 8.4. Labels, ListBoxes, DataGrid, TextBox, and Command Button Controls Property Settings Object Property Setting Label Name Label1 Text TablesLabel Name Label2 Text ColumnsLabel Name Label3 Text SQL StringLabel Name Label4 Text Data DisplayListBox Name lstTablesListBox Name lstColumns SelectionMode MultiSimpleTextBox Name txtSQLString MultiLine TrueButton Name btnViewDataGrid Name dgDisplay2. Tip Notice that the lstTables list box only allows the user to pick one table at a time, whereas lstColumns allows you to choose multiple columns. A great enhancement to this tool would be to allow the user to select multiple tables and have the application figure out the relation between tables.3. In the class module for the form, add the following Private declaration just below the line of code that reads Windows Form Designer generated code:4. Dim mcnn As New OleDb.OleDbConnection(BuildCnnStr((local), Northwind)) This line of code declares and assigns an OleDBConnection object that will be used throughout the form.5. On the form, add the code in Listing 8.12 to the Load event. The first thing this code routine does is create a new OleDbCommand called ocmdTables and assign the built-in SQL Server stored procedure called sp_Tables. 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.12 frmHowTo8_3.vb: Executing a SQL Server-Supplied Stored Procedure That Lists the Tables in the Database Private Sub frmHowTo3_8_Load(ByVal sender As System.Object, _ ByVal e As System.EventArgs) Handles MyBase.Load -- Create the connection and specify the stored procedure to use. Dim ocmdTables As New OleDb.OleDbCommand(sp_Tables, mcnn) Dim odrTables As OleDb.OleDbDataReader Try -- Specify the type of command being performed ocmdTables.CommandType = CommandType.StoredProcedure mcnn.Open() -- Create the DataReader object odrTables = ocmdTab ...