Sybase Business Intelligence Solutions - Database Management, Data Warehousing Software, Mobile Enterprise Applications and Messaging
Sybase Brand Color Bar
delete

Search for    in all of Sybase.com
view all search results right arrow
  blank
 
 
 
 
 
 
 
 
 
 

 
 
CLICK TO EXPAND NAVIGATION
CLICK TO EXPAND NAVIGATION
 
 
 
 
Support > Technical Documents > Document Types > White Paper-Technical > Tutorial: Deploying a SQL Anywhere .NET Applicatio...

Tutorial: Deploying a SQL Anywhere .NET Application Using ClickOnce
 
RSS Feed
 
 
 

Contents

Introduction
Preparation
Setting Up the Project
Deploying the Application
Updating the Application
Conclusion

Download paper

Introduction

SQL Anywhere is a versatile database capable of integrating with a wide variety of development environments, runs on various platforms, and is easily deployable using different methods. One way of distributing a SQL Anywhere database application is by using Microsofts ClickOnce technology to deploy Windows-based solutions.

While going through this tutorial, we will be deploying and installing a sample ADO.NET application using the ClickOnce deployment method. We will then update the database runtime files and redeploy the application.

If you are interested in learning about other methods of deploying a SQL Anywhere database application (InstallShield, silent install, etc.), refer to the document Tutorial:Embedding SQLAnywhere in Commercial Applications Requiring a Database available here:
http://www.sybase.com/detail?id=1093518.

Why Deploy Using ClickOnce?

ClickOnce is an application deployment method developed by Microsoft that is integrated in Visual Studio. It provides a quick and easy way to create an installation package to distribute your application via the web, network, or removable media. Due to SQL Anywheres small footprint and ease of deployment capabilities, you can rapidly generate a distribution package that includes your application and all necessary database runtime files.

ClickOnce applications can also be self-updating, with control over when and how the application updates. This provides you with an efficient method to not only update your application files, but also update the database runtime files when new versions of SQL Anywhere are released. Moreover, an applications installation package can be published to a web site or can be made available locally, thus allowing both online and offline distribution.

Prerequisites

  • SQL Anywhere 12 or higher a free Developer Edition of SQL Anywhere is available here:
    http://www.sybase.com/detail?id=1016644
  • Samples included in the SQL Anywhere installation (Default location is C:\Documents and Settings\All Users\Documents\SQL Anywhere 12\Samples)
  • Latest SQL Anywhere 12 (or higher) Emergency Bug Fix (EBF) available here:
    http://downloads.sybase.com/swd/summary.do?baseprod=144&client=ianywhere
  • Visual Studio 2010
  • Microsoft Internet Information Services (IIS) 6 or higher (only if you want to deploy the application via web browser)
  • .NET Framework 3.5 or higher
  • OPTIONAL: Another computer or virtual machine image to deploy the application

This paper was written for the Windows x86 platform, but is applicable to 64-bit systems as well.

Preparation

In this demonstration, we will be deploying the SQL Anywhere ADO.NET sample application SimpleViewer that is included in the SQL Anywhere installation. We will also define a batch file to run the Data Source utility (dbdsn.exe) to create an ODBC data source name for the demo database. We will later modify the project to run the batch file before launching the application to eliminate end-user setup procedures. Moreover, we will need to have Internet Information Services (IIS) installed (with the default website) so that we can distribute the application via the localhost web address.

Gathering the Database Runtime Files

This demonstration requires the following files. Copy them to an accessible folder.

The following files are located (default) in: C:\Program Files\SQL Anywhere 12\Bin32.

  • dbctrs12.dll
  • dbdsn.exe
  • dbelevate12.exe
  • dbeng12.exe
  • dbeng12.lic
  • dbicu12.dll
  • dbicudt12.dll
  • dblgen12.dll
  • dbserv12.dll
  • sqlany.cvf

Note: If you want to distribute the 64-bit database server, the above files will be located in the Bin64 folder.

The sample database file (demo.db) is found at the location below. It will also need to be copied to the above folder:
C:\Documents and Settings\All Users\Documents\SQL Anywhere 12\Samples.

Creating the ODBC Data Source Name

Create a file called dsn.bat using a text editor such as Notepad. Add the following command and save it to the above folder:

dbdsn -pe -w "SQL Anywhere 12 Demo" -c "uid=dba;pwd=sql;DBF=demo.db;ServerName=SimpleViewerDB;START=dbeng12"

This command runs the dbdsn.exe utility to create an ODBC data source called SQL Anywhere 12 Demo that starts the sample database (demo.db). To find out more about the dbdsn utility, refer to the documentation:
http://dcx.sybase.com/1001/en/dbdaen10/da-dbdsn-data-dbutilities.html.

Setting Up the Project

Before we begin deploying, we will have to alter the project. We will first add the database runtime files and then alter the source code to run the dsn.bat batch file before launching the application.

  1. Open the SimpleViewer solution. Click Start -> All Programs -> SQL Anywhere 12 -> Sample Applications and Projects -> SQLAnywhere -> ADO.NET -> SimpleViewer -> SimpleViewer.sln.

    Note: You'll need to convert the project if you are using a newer version of Visual Studio. Allow Visual Studio to do this automatically.


  2. Add the database runtime files to the project (see Preparation step above for list of files). This can be done by right-clicking the SimpleViewer project in the Solution Explorer and selecting Add -> Existing Item... These files need to be added to the solution so that they are available to the ClickOnce publishing options.
  3. For all the newly added files, make sure that their build action property is set to Content and their copy to output directory property is copy if newer. This is required so that these files are included in the deployment without compiling or building them.

  4. We have added dsn.bat to the solution. Now we need to be sure the program runs it before the application starts. We can do this by creating a method that will run dsn.bat before the application starts. To do this, create a C# class by right clicking the SimpleViewer project and selecting Add -> New Item... -> Class. Name this class PreDeploymentEvents.cs and click add.

  5. In the newly created C# class called PreDeploymentEvents.cs, add the following namespaces:

    using System.Diagnostics;
    using System.Deployment.Application;

    And add the following method inside the body of class PreDeploymentEvents:

    public static void RunBatch()
    {
        if (ApplicationDeployment.IsNetworkDeployed)
        {
            if (ApplicationDeployment.CurrentDeployment.IsFirstRun)
            {
                try
                {
                    Process proc = null;
                    string targetDir = string.Format(@"");
    
                    // Direct the process to the current folder
                    proc = new Process();
                    proc.StartInfo.WorkingDirectory = targetDir;
                    proc.StartInfo.FileName = "dsn.bat";
                    proc.StartInfo.Arguments = string.Format("Y");
    
                    // Affirm that the DSN will be replaced
                    proc.StartInfo.CreateNoWindow = true;
                    proc.StartInfo.WindowStyle = ProcessWindowStyle.Hidden;
                    proc.StartInfo.RedirectStandardError = true;
                    proc.StartInfo.RedirectStandardOutput = true;
                    proc.StartInfo.UseShellExecute = false;
                    proc.Start();
                }
                catch (Exception ex)
                {
                    Console.WriteLine("Exception Occurred :{0},{1}",ex.Message, ex.StackTrace.ToString());
                }
            }
        }
    }
    
  6. Now we need to make sure to call the RunBatch function before the program starts so that the ODBC data source is defined before we run the application. Do this by opening Program.cs and adding the following line of code after the static void Main() function:

    PreDeploymentEvents.RunBatch();

    You can now build the project by selecting Build Solution from the Build menu. You may need to change the target framework of the project to be .NET Framework 3.5 (see next section on how to do this). The solution should build without any errors.

Deploying the Application

We will be going through the steps required to deploy the application. This will install the application so that it runs like any other application installed on the computer. We will then delete the application.

  1. Right-click the project folder and select properties

  2. On the left column click on Application. Make sure the target Framework is set to .NET Framework 3.5. If prompted to change the framework, accept it. Click the Publish tab on the left.

  3. Click on Prerequisites.... This is used to select the programs the end-user will need to have before running the application. Select .NET Framework 3.5 SP1 and press OK.

  4. Click on Application Files and make sure that for all the files being included, the Publish Status is set to Include, the Download Group is set to (Required) and make sure the Hash property is set to Include. Click OK.

  5. Set the location of the Publishing folder location. In this demonstration, it will be located at - C:\SimpleViewer\. This is where the application files will be stored after the application is published.

  6. Click on Updates.. and make sure that The application should check for updates is NOT checked so that the application is not self-updating. Later, we will be going over how to update the application.

  7. Click on Options... and then open the Deployment tab on the left. Make sure that Use .deploy file extension is NOT checked so that the files deployed do not have a .deploy extension in them.

  8. Press 'Publish Now'

  9. The application is published to the folder C:\SimpleViewer and it should have the following:

    "Application Files" A folder with the content of the different versions of the application. The SQL Anywhere database and runtime files are located there.
    Publish.htm An HTML page with a description and a link to install the application
    setup.exe A file to assemble and install the application
    SimpleViewer.application A manifest file created by ClickOnce that runs the latest version of the application

    The next step is to run the application. We recommend that you transfer the contents of the folder C:\SimpleViewer to another computer or virtual machine image that does not have SQL Anywhere installed. This will more accurately simulate a typical deployment environment.

    Run setup.exe to install the application (you can safely ignore the publisher warning).

  10. The application should now be running with a Windows based form. You can then close the application.

  11. Right-Click on the SQL Anywhere icon in the system track (lightning bolt) and click Restore from the popup menu. Take note of the current version number at the top.

    The application can now be removed using "Add or Remove Programs" in Control Panel

    Note that the uninstall will NOT remove the ODBC data source name created when the application first ran. To remove this DSN, open a Command Prompt and change to the directory C:\SimpleViewer\Application Files\SimpleViewer_1_0_0_1 and execute the following:

    dbdsn.exe -du SQL Anywhere 12 Demo -y

    Go ahead and completely remove the SimpleViewer application.

Updating the Application

Here, we will go over how to update an application. We start by deploying and installing the SimpleViewer application, and then we update some of the database runtime files and redeploy the application with the changes. Finally, we update the application that has already been installed.

  1. Open the SimpleViewer project, right-click SimpleViewer in the Solution Explorer and select Properties.

  2. Open the Publish tab

  3. Set the Publishing Folder Location to http://<Your-Host-Name>/SimpleViewer (default is http://localhost/SimpleViewer)

    Note: If you do not want to publish using a web browser, you can leave the publishing folder location to C:\SimpleViewer. However, every time you update the application, you will need to copy the necessary installation files to your deployment computer or virtual machine image.

  4. Click on "Updates..." select "The application should check for updates" and "Before the application starts" and press OK.

  5. Click on "Publish Now"

  6. A web page will open up explaining the application to be installed. In your deployment computer or virtual machine image, launch a web browser and open this same page. Click Install and run the application.

  7. Close the application. Back in your development machine, close Visual Studio and copy the SimpleViewer folder to a safe location. It is good practice to keep a backup of the project in case anything goes wrong during the update.

  8. We will now be applying an Express Bug Fix (EBF). EBFs are fixes to reported software defects and are released periodically. To find EBFs for SQL Anywhere, navigate to http://downloads.sybase.com/swd/summary.do?baseprod=144&client=ianywhere. Register for a free MySybase account if you havent done so already. Click on SQL Anywhere and scroll down to Platform:Windows x86/Windows x64. Select the newest SQL Anywhere Express Bug Fix. Agree to the terms and conditions and click continue.

  9. Press on the executable to download it.

  10. Run the EBF executable. When the setup wizard appears, click Update and then click Finish. The EBF will modify a few files in your current SQL Anywhere installation. The files that were updated will have an updated version stamp.

  11. Reassemble the updated database runtime files required into a new folder. These will have newer version numbers.

  12. Launch Visual Studio, open the SimpleViewer project and delete all the old runtime files that were added earlier, excluding demo.db, dsn.bat and PreDeploymentEvents.cs.

  13. In the Solution Explorer, open References and delete iAnywhere.Data.SQLAnywhere.v3.5. The EBF above updated the SQL Anywhere ADO.NET data provider, so we need to re-compile the project with the updated version.

  14. Add the updated iAnywhere.Data.SQLAnywhere.v3.5 by right-clicking References and selecting Add Reference... -> Browse to the location of the new iAnywhere.Data.SQLAnywhere.v3.5 . The default location is C:\Program Files\SQL Anywhere 12\Assembly\V3.5.

  15. Add the new database runtime files and be sure that for these files, the Build Action is set to Content and their Copy to Output Directory to Copy if newer.

  16. Open the project properties and go to the Publish tab. Click on Application Files and make sure iAnywhere.Data.SQLAnywhere.v3.5.dll has its Publish Status set to Include, Download Group set to (Required) and Hash set to Include.

  17. Publish the application by right-clicking the project and selecting Publish... and Finish.

  18. Now in your deployment computer, open the SimpleViewer application from the start menu.

  19. A prompt will open up asking if you want to update the application. Click OK.

  20. Right-click on the SQL Anywhere icon in the system tray and click Restore. You will see that the database engine has been updated to the newest version.

    The application is now running and updated to the latest version.

Conclusion

This tutorial showed you how to generate a ClickOnce deployment package to distribute a SQL Anywhere-powered .NET database application. SQL Anywheres integration with leading development environments, such as Visual Studio, allows developers to quickly implement world-class solutions and bring them to market as fast as possible.

For more information about SQL Anywhere and .NET technologies, please visit the SQL Anywhere .NET Developer Center:
http://www.sybase.com/developer/library/sql-anywhere-techcorner/microsoft-net.


 

SQL Anywhere

The industry-leading mobile and embedded database that provides data management and synchronization technologies for applications outside the data center.
Related Links

DOCUMENT ATTRIBUTES
Last Revised: Jun 28, 2012
Product: SQL Anywhere
Technical Topics: SQL Anywhere, Microsoft.NET, Stored Procedures, Functions & Database Development
  
Business or Technical: Technical
Content Id: 1098727
Infotype: White Paper-Technical
 
 
 

 
© Copyright 2014, Sybase Inc. - v 7.6 Home / Contact Us / Help / Jobs / Legal / Privacy / Code of Ethics