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

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.

£14.99    £7.50
by Jayaram Krishnaswamy | July 2007 | .NET Microsoft

This article by Jayaram Krishnaswamy 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.

SQL Anywhere 10

SQL Anywhere 10 is the latest version of Sybase's feature rich SQL Anywhere database technology. It is highly scalable from the small foot-print UltraLite database all the way to its enterprise server with gigabytes of data. It is a comprehensive database package with built-in support for a wide range of applications, including session based synchronization; data exchange with both relational and non-relational data bases; secure store and forward messaging; messaging with FTP and email; and asynchronous access to mobile web services. You may download an evaluation version of the software and take it for a test drive. Sybase Central is a graphical database management interface to the database and its various supporting applications. The integration features are used in this article to create a Windows application retrieving data from the SQL Anywhere 10’s demonstration database, a database which is a part of the default installation of the developer edition.

Overview of SQL Anywhere 10

From Sybase Central you can connect to the demo database quite easily by clicking on the Connections menu item and choosing Connect with SQL Anywhere 10. Figure 1 shows the SQL Anywhere management interface, Sybase Central. Using this interface you may also create an ODBC DSN by following the trail; Tools --> SQL Anywhere 10 --> open ODBC Administrator.

Figure 1

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

It is very easy to connect to the database using the ODBC driver which is provided with the default installation of this product. The Figure 2 shows the User DSN installed with the default installation in the ODBC Data Source Administrator window.

Figure 2

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

The Username is DBA and the Password is sql (case sensitive) for the demo database, demo.db. Please refer to the article, "Migrating from Oracle 10G XE to SQL Anywhere 10" which describes connecting to the demo database in detail. Figure 3 shows the demo database and its objects.

Figure 3

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

VB.NET Windows Application

We will create an ASP.NET 2.0 Windows application called SqlAny. We will create forms which display retrieved data from a table on the database as well as from a stored procedure after accepting a parameter passed to the stored procedure interactively. The Figure 4 shows the details of the project in the Solution Explorer as well as the Object Browser.

Figure 4

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


Accessing SQL Anywhere Explorer

SQL Anywhere Explorer is a component of SQL Anywhere that lets you connect to SQL Anywhere and UltraLite  databases from Visual Studio .NET. From the View menu of Visual Studio, you can access the SQL Anywhere Explorer as shown in Figure 5 - SQL Anywhere 10 is integrated with Visual Studio (both 1.1 and 2.0 versions).

Figure 5

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

Alternatively, you can access SQL Anywhere Explorer from the Tools menu item as shown in Figure 6. In this case the Sybase Central management interface opens in a separate window.

Interactive SQL is another of SQL Anywhere 10's tools for working with SQL queries on this database.

Figure 6

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

When you click on SQL Anywhere Explorer from the View menu, you will be lead to the following window shown in Figure 7 which allows you to establish a data connection.

Figure 7

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

Click on the drop-down, Add Connection, which opens the window shown in Figure 8 where you will be given a choice of two connections that you may connect to, SQL Anywhere or UltraLite. These are both databases. Both can run on mobile devices, but UltraLite has a smaller footprint.

Figure 8

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

By choosing to connect to SQL Anywhere you invoke the authentication window for making the connection, as shown in Figure 9. The Username is DBA and the Password is sql. After entering these values you can get to the ODBC DSN mentioned earlier, from the drop-down. You may also test the connectivity which you see as being a success, for the entered values of Username, Password, and ODBC DSN.

Figure 9

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

Visual Studio makes a data connection as shown in Figure 10. The nodes for Tables, Views, and Procedures are all expanded in this figure showing all the objects that can be accessed on this database. Since we logged in as DBA, all permissions are in place.

Figure 10

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

Before the connection is made, SQL Anywhere starts up as shown in Figure 11. This message console gets minimized and stays up in the system tray of the desktop. This can be restored and closed by activating the icon in the tray.

Figure 11

VB.NET Application with SQL Anywhere 10 database: Part 1
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: £14.99
Book Price: £24.99
See more
Select your format and quantity:

Creating a Windows Form to display table data from the Server

A DataGridView will be added to the form and it will be configured to bring in the data from a table in the demo database.

Configuring a DataGridView on the form

In the Tools menu locate the DataGridView in the Data group as shown in Figure 12 and drag it to the form. The DataGridView when fully configured will display the data from the database.

Figure 12

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

Smart tasks to the rescue

Click on the Smart task (right tip of the DataGridView) and follow from top-to-bottom the listed tasks. The first thing is of course, the connection. Click on the Add Project Data Source hyperlink at the very bottom as shown in Figure 13. 

Figure 13

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

This opens up the Data Source Configuration Wizard window with several Data Source types as shown in Figure 14. Read the instructions. You will be using a database for obtaining the data.

Figure 14

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

Click on the Database icon and then click on the Next button in the above window. SQL Anywhere.demo10 appears in the first box showing the data source this window will use as shown in Figure 15. You can also see the connection string in this window. For this exercise you will use the option to include the sensitive data in the connection string as shown.

Figure 15

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

Clicking on the Next button you pop-up a window as shown in Figure 16 where you can choose to save the string to a configuration file. Click on the Next button. 

Figure 16

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

After this screen you will display all the data source objects from which you can make a choice. You may choose more than one object. Here the Customers table is chosen as shown in Figure 17. For this choice the program provides a default dataset, DataSet1 which has been replaced with DsAny as shown.

Figure 17

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

When you click on the Finish button in the above screen the program adds this dataset to the Component Tray below the design pane. It also adds two other objects, the CustomerBindingSource and the CustomersTableAdapter as shown in Figure 18. The DataGridView now develops columns and rows with the column headings from the customers table as shown. The Data source for the DataGridView is the CustomerBindingSource.

Figure 18

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

Continue reading the second part of this article >>


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: £14.99
Book Price: £24.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