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

Exclusive offer: get 50% off this eBook here
Beginners Guide to SQL Server Integration Services Using Visual Studio 2005

Beginners Guide to SQL Server Integration Services Using Visual Studio 2005 — Save 50%

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.

$23.99    $12.00
by Jayaram Krishnaswamy | July 2007 | .NET Microsoft

This article by Jayaram Krishnswamy shows how you can develop a VB.NET 2.0 application using the integration features provided by the SQL Anywhere database. The SQL Anywhere tools are directly accessible without going out of the Visual Studio 2.O IDE. The article describes a window application with two examples developed with this easy to use integration interface. SQL Anywhere also provides a DataWindow specifically for .NET but this is not included in this article.

[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


Beginners Guide to SQL Server Integration Services Using Visual Studio 2005 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.
Published: December 2007
eBook Price: $23.99
Book Price: $39.99
See more
Select your format and quantity:

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 :


Beginners Guide to SQL Server Integration Services Using Visual Studio 2005 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.
Published: December 2007
eBook Price: $23.99
Book Price: $39.99
See more
Select your format and quantity:

About the Author :


Jayaram Krishnaswamy

Jayaram Krishnaswamy studied at the Indian Institute of Science in Bangalore India and Madras University in India and taught at the Indian Institute of Technology in Madras. He went to Japan on a Japanese Ministry of Education Research scholarship to complete his PhD in Electrical Engineering from Nagoya University. He was a Post-Doctoral Fellow at Sydney University in Australia; a Government of India Senior Scientific Officer at the Indian Institute of Science in Bangalore and Indian Institute of Technology at Kanpur; a Visiting Scientist at the Eindhoven Institute of Technology in the Netherlands; a visiting Professor of Physics at the Federal University in Brazil; an Associate Research Scientist at a government laboratory in São Jose dos Campos in São Paulo, Brazil; a visiting scientist at the National Research Council in Ottawa, Canada before coming to USA in 1985. He has also taught and worked at the Colorado State University in Fort Collins and North Carolina State University in Raleigh, North Carolina. He worked with Northrop Grumman Corporation on a number of projects related to high energy electron accelerators and Free Electron Lasers. These projects were undertaken at the Brookhaven National Laboratory in Long Island and in the Physics Department at Princeton University. He has over 80 publications in refereed and non-refereed publications and 8 issued patents. He is fluent in Japanese and Portuguese and lives in Honolulu, Hawaii, USA.

He has been working in IT-related fields since 1997. He was once a Microsoft Certified Trainer in Networking and a Siebel Certified developer. He has worked with several IT related companies, such as the Butler International in their Siebel practice, with several IBM sub-contractors and smaller companies. Presently he is active in writing technical articles in the IT field to many online sites such as http://CodeProject.com, http://APSFree.com, http://DevShed.com, http://DevArticles.com, http://OfficeUsers.org, http://ASPAlliance.com, Egghead Café, http://SSWUG.org, Packt Article Network, http://databasedev.co.uk, http://cimaware.com, and many others. Between 2006 and 2010 he wrote more than 400 articles mostly related to database and web related technologies covering Microsoft, Oracle, Sybase, ColdFusion, Sun, and other vendor products.

He has written four books all published by Packt related to Microsoft Database and Application Development: SQL Server Integration Services Using Visual Studio 2005, Learning SQL Server Reporting Services 2008, Microsoft SQL Azure; Enterprise Application Development, and Microsoft Visual Studio Lightswitch Business Application Development. He regularly writes for his four blogs on Blogger; http://Hodentek.blogspot.com, http://HodentekHelp.blogspot.com, http://HodentekMobile.blogspot.com, and http://HodentekMSSS.blogspot.com. He received the 2011 Microsoft Community Contributor award.

Contact Jayaram Krishnaswamy

Books From Packt

Implementing Microsoft Dynamics NAV 2009
Implementing Microsoft Dynamics NAV 2009

ASP.NET 3.5 Social Networking
ASP.NET 3.5 Social Networking

Entity Framework Tutorial
Entity Framework Tutorial

VSTO 3.0 for Office 2007 Programming
VSTO 3.0 for Office 2007 Programming

Learning SQL Server 2008 Reporting Services
Learning SQL Server 2008 Reporting Services

ASP.NET 3.5 Application Architecture and Design
ASP.NET 3.5 Application Architecture and Design

Software Testing with Visual Studio Team System 2008
Software Testing with Visual Studio Team System 2008

C# 2008 and 2005 Threaded Programming: Beginner's Guide
C# 2008 and 2005 Threaded Programming: Beginner's Guide

 


 

 

Code Download and Errata
Packt Anytime, Anywhere
Register Books
Print Upgrades
eBook Downloads
Video Support
Contact Us
Awards Voting Nominations Previous Winners
Judges Open Source CMS Hall Of Fame CMS Most Promising Open Source Project Open Source E-Commerce Applications Open Source JavaScript Library Open Source Graphics Software
Resources
Open Source CMS Hall Of Fame CMS Most Promising Open Source Project Open Source E-Commerce Applications Open Source JavaScript Library Open Source Graphics Software