VB.NET Application with SQL Anywhere 10 database: Part 2

An ideal book and eBook for trainers who want to teach an introductory course in SQL Server Integration Services or, to those who want to study and learn SSIS in a little over two weeks.

[Read the first part of this article here]

Now you can click on the Preview Data… hyperlink which opens up the Preview Data window as shown in Figure 19. The Select an object to preview field gets populated automatically to run the getData () method. When you click on the Preview button, the grey area is populated by a table showing the retrieved rows of data from the Customers table as shown in Figure 19. 

Figure 19

VB.NET Application with SQL Anywhere 10 database: Part 2

Click on the Close button in the above window. In the various tasks of the DataGridView many options are chosen by default. You may also check reordering of the column by placing a check mark in the Edit Column Reordering in Figure 18 which opens Edit Columns window as shown in Figure 20, another useful control to manipulate the columns so that the columns you want to see are the first few columns.

Figure 20

VB.NET Application with SQL Anywhere 10 database: Part 2

You may want to edit the columns and change some of the items such as reordering of the columns, column name, column width, etc. All this can be done from this screen. For this tutorial only the columns width was changed. A label was added and its text was changed to, "Demo 10 Database Customers" as shown in Figure 21.

Figure 21

VB.NET Application with SQL Anywhere 10 database: Part 2

Build the project from the main menu item, Build. Now when the program is run by clicking the Debug --> Start without debugging, or by pressing Ctrl+F5, the program runs and Form1 is displayed as shown in Figure 22. 

Figure 22

VB.NET Application with SQL Anywhere 10 database: Part 2

Figure 23 shows the properties of the dataset DsAny that is created when the data source was created.

Figure 23

VB.NET Application with SQL Anywhere 10 database: Part 2

The schema of the above dataset is shown in Figure 24. This gets added to the project files in the Solution Explorer.

Figure 24

VB.NET Application with SQL Anywhere 10 database: Part 2

Using the smart tasks on the CustomerBindingSource you can carry out few of the indicated tasks shown in Figure 25.

Figure 25

VB.NET Application with SQL Anywhere 10 database: Part 2

The properties of the CustomerBindingSource are shown in Figure 26.

Figure 26

VB.NET Application with SQL Anywhere 10 database: Part 2

The CustomerTableAdapter directly connects to the database and it has its own properties window as shown in Figure 27. You will be able to edit queries in the dataset designer, add query, etc using the hyperlinks at the bottom of the properties window.

Figure 27

VB.NET Application with SQL Anywhere 10 database: Part 2

Alternatively you will be able to carry out similar tasks from the smart tasks on the CustomerTableAdapter component in the component tray as shown in Figure 28. In Microsoft applications, you have more than one way of carrying out a task.

Figure 28

VB.NET Application with SQL Anywhere 10 database: Part 2

The Object Browser shown in Figure 29 for this project shows the various data related classes that are used in the application working behind scenes as not a single line of code was explicitly used for this form to display the data.

Figure 29

VB.NET Application with SQL Anywhere 10 database: Part 2


Windows form displaying retrieved data from a Stored Procedure

In this section you will be using a stored procedure on the SQL Anywhere 10 Server called ShowProductInfo. This procedure takes a parameter and retrieves related product information which is displayed in a DataGridView on a Form.

Configuring the data Source

Add a BindingSource1 to a new form, Form2. In the DataSource field of the BindingSource1 property window click and from the drop-down choose Add Project Data Source as shown in Figure 30. 

Figure 30

VB.NET Application with SQL Anywhere 10 database: Part 2

This opens the window of the Data Source Configuration Wizard. The next two steps are similar to what was done in the previous example. Here you choose a stored procedure such as ShowProductInfo as shown in Figure 31. Rename the DataSet name at the bottom of the screen to DsProductInfo as shown. Click on the button Finish on this screen.

Figure 31

VB.NET Application with SQL Anywhere 10 database: Part 2


Reviewing the Stored Proc in Interactive SQL

We will look at this stored proc using the interactive SQL. You can activate the Interactive SQL tool from the Tools menu as seen earlier. This wakes up the Server for which you have to provide the same set of authentication information as in the earlier example. After accepting the credentials the Interactive SQL opens in its own window as shown Figure 32, where you can get a list of Stored Procs on the server.

Figure 32

VB.NET Application with SQL Anywhere 10 database: Part 2

You will display the list of stored procedures on the server by clicking on, Lookup Procedure Name. You may need to scroll down to get to your stored procedure as shown in Figure 33.

Figure 33

VB.NET Application with SQL Anywhere 10 database: Part 2

When you click on this, the Stored Procedure statement will be added to the query pane of Interactive SQL. From this screen you can execute the Stored Procedure from the SQL menu's drop-down menu item, Execute. The result of executing the above Stored Procedure is shown in Figure 34. Click out of Interactive SQL.

Figure 34

VB.NET Application with SQL Anywhere 10 database: Part 2


Configuring the BindingSource

The BindingSource1's Data source is DsProductInfo. When you click on the DataMember field in the BindingSource1 property window, the Stored Procedure – ShowProductInfo is displayed as shown in Figure 35.

Figure 35

VB.NET Application with SQL Anywhere 10 database: Part 2


When this is accepted by clicking on it, the ShowProductInfoDataAdpater gets added to Form2's Component Tray as shown in Figure 36.

Figure 36

VB.NET Application with SQL Anywhere 10 database: Part 2


Displaying data from the Stored Procedure

Similar to the previous example, drag and drop a DataGridView on to the Form (Form2) and click on the smart task on the DataGridView. For the data source use the BindingSource1 from the drop-down as shown in Figure 37. This immediately displays the DataGridView by adding all the column headers from the Stored Procedure. You may also edit the columns individually, if needed, as described in the earlier example in more detail.

Figure 37

VB.NET Application with SQL Anywhere 10 database: Part 2


Passing the parameter to the stored procedure

This Stored Procedure retrieves the column values for a given value of ID as seen in the Interactive SQL which showed the retrieved column values for the ID=300.

You can pass the value of the ID in the click event of the button, "Show Product" using the following code. You need to provide the ID in the Textbox control at the bottom of this screen.

Listing 1:

Private Sub Button1_Click (ByVal sender As System.Object, _

    ByVal e As System.EventArgs) Handles Button1.Click

        ShowProductInfoTableAdapter.Fill (DsProductInfo. _

        ShowProductInfo, TextBox1.Text)

    End Sub

You can now build the project and display Form2 using the 'run' button in the main menu. When the form shows up, type in the value of the ID parameter and click the 'Show product' button. As shown in Figure 38, the product information for the product with ID in the textbox is displayed (The textbox is showing a value in it after it is clicked).

Figure 38

VB.NET Application with SQL Anywhere 10 database: Part 2


Summary

SQL Anywhere 10 is well integrated with Visual Studio.NET 2.0. SQL Anywhere Explorer gives immediate access to the database objects which makes it extremely easy to integrate the data into a VB.Net application. Interactive SQL and Sybase Central are also accessible from VS 2005. Activating a form bound to the data also starts the server if it is stopped.


If you have read this article you may be interested to view :


Books to Consider

comments powered by Disqus