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.
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.
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.
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.
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.
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 23 shows the properties of the dataset DsAny that is created when the data source was created.
The schema of the above dataset is shown in Figure 24. This gets added to the project files in the Solution Explorer.
Using the smart tasks on the CustomerBindingSource you can carry out few of the indicated tasks shown in Figure 25.
The properties of the CustomerBindingSource are shown in Figure 26.
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.
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.
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.
eBook Price: $23.99
Book Price: $39.99
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.
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.
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.
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.
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.
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.
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.
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.
Private Sub Button1_Click (ByVal sender As System.Object, _
ByVal e As System.EventArgs) Handles Button1.Click
ShowProductInfoTableAdapter.Fill (DsProductInfo. _
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).
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 :
eBook Price: $23.99
Book Price: $39.99
About the Author :
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.
Books From Packt