PowerBuilder Tips, Tricks, and Techniques

Berndt Hamboeck

Subscribe to Berndt Hamboeck: eMailAlertsEmail Alerts
Get Berndt Hamboeck: homepageHomepage mobileMobile rssRSS facebookFacebook twitterTwitter linkedinLinkedIn


PowerBuilder: Article

SQL Anywhere 10 & DataWindow .NET 2.0 in an ASP Environment

Speeding up database development

Tips and Tricks
In this section I'll describe some questions that were raised when we used Visual Studio, SQL Anywhere, and DataWindow .NET together. This list isn't complete but should help you get started.

Common Problems Using SQL Anywhere & DataWindow .NET
How to connect to SQL Anywhere from .NET with or without using DataWindow .NET?
There are several options available:

1.  .NET Framework Data Provider for OLE

using System.Data.OleDb;
using Sybase.DataWindow;

OleDbConnection oleDbConn = new
    System.Data.OleDb.OleDbConnection();
oleDbConn.ConnectionString = @"Provider=SAOLEDB.10;User ID=dba;Password=sql;DBF=<fullpath>.db;";
oleDbConn.Open();
SQLCA = new AdoTransaction(oleDbConn);
SQLCA.BindConnection();

2.  SQL Anywhere .NET Data Provider - doesn't work when using DataWindow.NET

SAConnection saConn = new SAConnection();
saConn.ConnectionString =
    @"UID=dba;PWD=sql;DBF=<fullpath>..db;";
saConn.Open();

3.  The DataWindow Transaction class - doesn't work together with built-in .NET data objects

using Sybase.DataWindow.Transaction;
tConnection = new Sybase.DataWindow.Transaction();
tConnection.Dbms = Sybase.DataWindow.DbmsType.OleDb;
tConnection.Password = "sql";
tConnection.UserId = "dba";
tConnection.AutoCommit = false;
tConnection.DbParameter = @"PROVIDER='SAOLEDB.10',PROVIDERSTRING='DBF=<fullpath>.db'";
tConnection.Connect();

Does DataWindow .NET work with Visual Studio Express?
Yes, but one has to register it on the toolbar.

  • Right-click on the Toolbox, select add a new tab.
  • Give it a name like DataWindow .NET 2.0.
  • Right-click on the new tab and select 'Add Item' from the pop-up.
  • Browse to the installation folder for DataWindow .NET and select Datawindow.dll.
  • Do the same for the file WebDataWindow.dll.
How to use the authenticated version of SQL Anywhere in .NET.
Create an OleDB connection as shown before and execute the command below:

// authenticate the connection (and associated AdoTransaction)
OleDbCommand cmd = new OleDbCommand("SET TEMPORARY OPTION CONNECTION_AUTHENTICATION=<conn auth string>'",
oleDbConn);
cmd.ExecuteNonQuery();

I can't add a transaction to a WebForm. The transaction is grayed-out in the toolbox.
Right-click on the WebForm and select View Component Designer. Now one can see the transaction object on the toolbox.

I get a PBDWN105.DLL not found error.
The easiest solution is to install DataWindow .NET from the CD on this machine.

I installed an update of DataWindow .NET, now my references are broken.
Add DataWindow.DLL or WebDataWindow.dll when using a WebForm application again as reference. This changes the reference to the new version.

How can I deploy SQL Anywhere database drivers?
The simplest way to deploy .NET assemblies is to use the Deployment wizard that comes with SQL Anywhere 10.

Saving a DataWindow as PDF doesn't work (SaveAS didn't complete successfully - return -1).
Install Ghostscript 8.53. Make sure that AFPL Ghostscript\8.53 is under HKLM\SOFTWARE in your registry after the installation and that the correct access rights are set to the directory where the PDFs are stored.

Saving a DataWindow works local, but it does not work on Windows Server 2003.
By default a Group Policy that disallows the installation of printers that use kernel-mode drivers.

  • Select Run from the Windows Start menu.
  • In the Open box, type gpedit.msc and click OK.
In the Group Policy console, expand Computer Configuration, Administrative Templates, and Printers. Disable "Disallow Installation of Printers Using Kernel-Mode Drivers."

My created PDF file is 0 bytes long.
Set the property AutoRestoreDataCache="True" Set the property AutoSaveDataCacheAfterRetrieve="True"

Your body HTML tag has to look like this to get it to work:

<body id="PageBody" runat="server">

Use the following script in your button:

protected void bnSavePDF_Click(object sender, EventArgs e)
{
    String pdfFileName;
    String pdfUrl;
    String uniqueName;

    uniqueName = System.Guid.NewGuid().ToString() + ".pdf";
    pdfUrl = "pdfs/" + uniqueName;
    pdfFileName = Page.MapPath("") + "\\pdfs\\" + uniqueName;

    dwReport.SaveAs(pdfFileName, Sybase.DataWindow.FileSaveAsType.Pdf);
    HtmlGenericControl body;
    body = this.FindControl("PageBody") as HtmlGenericControl;
    body.Attributes.Clear();
    body.Attributes.Add("onLoad", "pdfWindow=window.open ('" + HttpUtility.UrlEncode(pdfUrl) + "',
    'pdf','dependent');pdfWindow.focus();");
    body.Attributes.AddAttributes(new HtmlTextWriter(Response.Output));
     }

VERSIONS USED IN WRITING THESE DOCUMENTS

  • Operating System: Windows Vista
  • Development tool: Visual Studio 2005 TeamSystem
  • DataWindow.NET 2.01: SQL Anywhere 10
Conclusion
SQL Anywhere and DataWindow .NET are a perfect combination for all your development needs. SQL Anywhere is an easy-to-use and powerful database. With DataWindow .NET, this combination speeds up database development dramatically; this was critical in a project that had an existing WinForm application and needed to be converted to a WebForm application with minimal effort and user interface changes. DataWindow .NET lets one design a complete view of the database (the actual user interface, the so-called DataWindow object) without any coding. The DataWindow object is then put in a WinForm or WebForm application by using the DataWindow control without any changes. It supports a developer with an extensive API including properties and events. Both products fit and integrate perfectly into the .NET world and the Visual Studio 2003 or 2005 IDE by supporting the use of DataSets and ADO.NET.

More Stories By Berndt Hamboeck

Berndt Hamboeck is a senior consultant for BHITCON (www.bhitcon.net). He's a CSI, SCAPC8, EASAC, SCJP2, and started his Sybase development using PB5. You can reach him under admin@bhitcon.net.

Comments (1) View Comments

Share your thoughts on this story.

Add your comment
You must be signed in to add a comment. Sign-in | Register

In accordance with our Comment Policy, we encourage comments that are on topic, relevant and to-the-point. We will remove comments that include profanity, personal attacks, racial slurs, threats of violence, or other inappropriate material that violates our Terms and Conditions, and will block users who make repeated violations. We ask all readers to expect diversity of opinion and to treat one another with dignity and respect.


Most Recent Comments
Frank 06/24/07 02:31:22 AM EDT

If my sql anywhere 10 db have encryption key , how do I config dbParameter ?