< Day Day Up > |
The preceding Visual Basic .NET example included column sorting, which is built into the DataGrid control. In FoxPro, adding column sorting turned out to be a little involved, but the data was easy to get. In Visual Basic .NET, you have to think about data retrieval efficiency. For small numbers of rows, DataView s are okay. But unlike Visual FoxPro, where opening a table of a few hundred thousands of records has no performance implications to speak of, returning a few hundred thousand records into a grid in order to filter the records is not in the cards in the ADO.NET world. Instead, you have to get your data sparingly. That's how we'll design the lookup form. Create a form with a grid, three command buttons , a text box, and a label, as shown in Figure 9.7. Figure 9.7. A generic search form in Visual Basic .NET.The code in this form creates a SELECT statement ending in a WHERE clause that matches any string beginning with the letter(s) typed in by the user : Cmd = "SELECT " + FieldList + " FROM " + TableName + " WHERE " + SearchField + " LIKE '" + TextBox1.Text + "%'" The resulting command might look like this: SELECT CustomerID, CompanyName, ContactName FROM CUSTOMERS WHERE CompanyName LIKE 'A%' The code to load the table is trivial, as shown in Listing 9.6. Listing 9.6. The Code to Build a SELECT StringDim cn As New SqlClient.SqlConnection cn.ConnectionString = ConnStr cn.Open() Dim cmd As New SqlClient.SqlCommand cmd.Connection = cn cmd.CommandText = "SELECT * FROM " + TableName _ + " WHERE " + SearchField + " LIKE '" _ + MyText1.Text.ToUpper.Trim + "%'" Dim da As New SqlClient.SqlDataAdapter da.SelectCommand = cmd ds.Clear() da.Fill(ds, TableName) dv = ds.Tables(0).DefaultView DataGrid1.DataSource = dv This will return only the records that match the filter expression. If you want to match a substring anywhere within the target string, you can include a percent sign at the end of the string " LIKE '" , that is, " LIKE '%" . However, users who have been spoiled by using FoxPro may still not be prepared for the consequences of issuing a command like this that returns 25,000 records from SQL, never mind returning records from a Web server. So you can avoid dangerous liaisons by returning a count of the records to be returned, and if it passes some threshold (stored in an integer property called MaxRecords ), warn the user (see Listing 9.7). Listing 9.7. Visual Basic .NET Code to Count Records and Warn the User If the Records Exceed Some Limitcmd.CommandText = "SELECT Count(*) FROM " + TableName _ + " WHERE " + SearchField + " LIKE '" + MyText1.Text.ToUpper.Trim + "%'" Dim HowMany As Integer HowMany = cmd.ExecuteScalar If HowMany > MaxRecords Then Dim s as string = "This query will return " ; + HowMany.ToString + " records. Proceed?" If MsgBox(s, _ MsgBoxStyle.Exclamation, Application.ProductName) _ = MsgBoxResult.No Then Return End If End If The same thing is possible in FoxPro, but not needed for DBFs. For SQL Server, it's easy: ConnStr = "Driver={SQL Server};server=(local);database=Northwind;UID=sa;PWD=;" handle = SQLSTRINGCONNECT( ConnStr) SQLEXEC( Handle, "SELECT COUNT(*) AS RecCount FROM Customers" ) The command returns a cursor named SQLRESULT . If you don't use an AS clause to rename the result of the COUNT(*) expression, the column is named Exp . So the result of the SQLEXEC call in the preceding code line is SQLResult.Reccount , and SQLEXEC( Handle, "SELECT COUNT(*) FROM Customers" ) returns SQLResult.Exp . So you can write the generic code like that shown in Listing 9.8 (again, assume that the form has a property called MaxRecords that's been set to the maximum number of records you want to return without the user's approval). Listing 9.8. Visual FoxPro SQL Code to Count Records and Warn the User If the Records Exceed Some LimitWITH THISFORM FilterExpr = " WHERE " + SearchField + " LIKE '" + .Textbox1.Value + "%'" Cmd = "SELECT COUNT(*) FROM " + .TableName + " WHERE " + FilterExpr SQLExec ( Handle, Cmd ) IF SQLResult.Exp > .MaxRecords S = "This will return " + TRANSFORM(Exp) + " records; continue?" IF MessageBox ( s, 292 ) <> 6 RETURN ENDIF ENDIF Cmd = "SELECT " + FieldList + " FROM " + TableName + FilterExpr SQLExec ( Handle, Cmd ) .Grid1.RecordSource = "SQLResult" ENDWITH |
< Day Day Up > |