From the Editor-in-Chief of PowerBuilder Developer's Journal

Bruce Armstrong

Subscribe to Bruce Armstrong: eMailAlertsEmail Alerts
Get Bruce Armstrong: homepageHomepage mobileMobile rssRSS facebookFacebook twitterTwitter linkedinLinkedIn


Article

DataWindow.NET How To: DataWindow Formatting

A simple but powerful way of formatting data in the presentation layer

In this article, we're going to look at how DataWindow.NET technology is a simpler but more powerful way of formatting data in the presentation layer.

We'll be taking a sample application provided by Microsoft for .NET and implementing it using DataWindow.NET technology. In this case, we're using the Visual Basic .NET Code Sample: DataGrid Formatting sample application available at www.microsoft.com/downloads/details.aspx? FamilyID=28fd458f-5571-42b4-a2fe-09b69ddf0863&displaylang=en.

The original sample was written to connect to the Microsoft Northwind sample database. So you'll need MSDE or SQL Server with that sample database installed if you want to try it out for yourself. Also like last month's article, the sample code for this article is intended as an addition to the original sample so you can compare them side-by-side. After adding the sample code files from this article to the original sample, you can switch between the DataGrid and DataWindow implementations by changing the startup form for the project (see Figure 1).

Main Form
The main form of the application consists of a DataGrid or DataWindow and four command buttons that apply "Default," "Table Style," "Column Style," and "Grid Properties" formatting to the DataGrid or DataWindow control. Table 1 shows the results of the different formatting applied to the DataGrid and DataWindow respectively.

The primary difference between the DataGrid and DataWindow implementations with regard to form variables is that the DataGrid has a DataSet defined (ProductData) and a constant (PRODUCT_TABLE_NAME). This particular VB.NET example, unlike the last two we looked at, is a typical ADO.NET sample that uses a DataAdapter and a DataSet to retrieve and store the data and then fill the DataGrid. The PRODUCT_TABLE_NAME constant is used to name the result set of interest in the DataSet because a .NET DataSet can actually hold multiple result sets.

Each of the command buttons calls two form methods (ResetDemo and BindDataGrid or BindDataWindow) and then a third form method for the non-default styles that sets up that particular style (see Table 2).

In the DataGrid implementation, the ResetDemo method clears the DataSet and then resets each of the various formatting properties that get set during the demo.

DataGrid ResetDemo

If Not IsNothing(ProductData.Tables(PRODUCT_TABLE_NAME)) Then
  ProductData.Tables(PRODUCT_TABLE_NAME).Clear()
End If

With grdProducts
   .BackgroundColor = SystemColors.InactiveCaptionText
   .CaptionText = ""
   .CaptionBackColor = SystemColors.ActiveCaption
   .TableStyles.Clear()
   .ResetAlternatingBackColor()
   .ResetBackColor()
   .ResetForeColor()
   .ResetGridLineColor()
   .ResetHeaderBackColor()
   .ResetHeaderFont()
   .ResetHeaderForeColor()
   .ResetSelectionBackColor()
   .ResetSelectionForeColor()
   .ResetText()
   .BorderStyle = DefaultGridBorderStyle
End With

The DataWindow implementation of ResetDemo is much simpler because we only need to reset the properties on the DataWindow control. The properties of the DataWindow object get reset during the BindDataWindow method when we reassign the DataWindow object to the control.

DataWindow ResetDemo

dw_products.BorderStyle = DataWindowBorderStyle.None
dw_products.TitleBar = False
dw_products.Text = ""

Both the BindDataGrid and BindDataWindow methods try to connect to the SQL Server version of the Northwind database and then try to connect to the MSDE version if the SQL Server version can't be found. The DataGrid implementation then uses an SqlDataAdapter class to retrieve the data from the database and routes it to the DataSet. Then it assigns that DataSet as the DataSource for the DataGrid control.

DataGrid BindDataGrid

Dim ProductAdapter As New SqlDataAdapter( _
  "SELECT ProductID, ProductName, UnitPrice, UnitsInStock FROM products", _
  northwindConnection)
ProductAdapter.Fill(ProductData, PRODUCT_TABLE_NAME)
grdProducts.DataSource = ProductData.Tables(PRODUCT_TABLE_NAME)

The DataWindow implementation of the BindDataWindow method does pretty much the same thing using DataWindow related classes. First the OleDB connection is bound to a DataWindow.AdoTransaction object. We then reset the DataWindow object assigned to the DataWindow control, assign the AdoTransaction object to the DataWindow control, and then retrieve the DataWindow.

DataWindow BindDataWindow

Dim SQLCA As New Sybase.DataWindow.AdoTransaction(conn, "")
SQLCA.BindConnection()

dw_products.LibraryList = "dwgridformatting.pbl"
dw_products.DataWindowObject = ""
dw_products.DataWindowObject = "d_g_products"
dw_products.SetTransaction(SQLCA)
dw_products.CreateHeaderSortArrows()
dw_products.Retrieve()

The FormatGridwithoutTableStyles, FormatGridwithTableStyles, and FormatGridwithBothTablesAndColumnStyles methods are all somewhat similar. The FormatGridwithoutTableStyles method (only an excerpt is shown below) sets the format by manipulating the properties of the DataGrid itself:

FormatGridWithoutTableStyles

With grdProducts
   .BackColor = Color.GhostWhite
   .BackgroundColor = Color.Lavender
   .BorderStyle = BorderStyle.None
   .CaptionBackColor = Color.RoyalBlue
   .CaptionFont = New Font("Tahoma", 10.0!, FontStyle.Bold)
   .CaptionForeColor = Color.Bisque
   .CaptionText = "Northwind Products"
   .Font = New Font("Tahoma", 8.0!)
   .ParentRowsBackColor = Color.Lavender
   .ParentRowsForeColor = Color.MidnightBlue
End With

The FormatGridwithTableStyles method (only an excerpt is shown below) takes the same approach for many properties, but it also defines a DataGridTableStyle object to handle some of the other properties and then assigns that to the DataGrid:

FormatGridWithBothTableAndColumnStyles

Dim grdTableStyle1 As New
  DataGridTableStyle()
With grdTableStyle1
   .AlternatingBackColor = Color.GhostWhite
   .BackColor = Color.GhostWhite
   .ForeColor = Color.MidnightBlue
   .GridLineColor = Color.RoyalBlue
   .HeaderBackColor = Color.MidnightBlue
   .HeaderFont = New Font("Tahoma", 8.0!, FontStyle.Bold)
   .HeaderForeColor = Color.Lavender
   .SelectionBackColor = Color.Teal
   .SelectionForeColor = Color.PaleGreen
   .MappingName = PRODUCT_TABLE_NAME
   .PreferredColumnWidth = 125
   .PreferredRowHeight = 15
End With
grdProducts.TableStyles.Add
   (grdTableStyle1)

More Stories By Bruce Armstrong

Bruce Armstrong is a development lead with Integrated Data Services (www.get-integrated.com). A charter member of TeamSybase, he has been using PowerBuilder since version 1.0.B. He was a contributing author to SYS-CON's PowerBuilder 4.0 Secrets of the Masters and the editor of SAMs' PowerBuilder 9: Advanced Client/Server Development.

Comments (11) 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
monkfish 11/27/08 08:48:36 AM EST

thanks for the information. betsson iddaa

SohbetGor 11/17/08 09:42:36 AM EST

thanks you...

Sohbet

Qarisma 11/05/08 05:54:22 PM EST

But real Güzeş Site head turn before such excellent Sitelent see

_______________
evden eve nakliyat Sohbet

alvin 07/23/08 08:39:54 PM EDT

how can i change the highlight color of selected row? the default is blue which looks heavy.
thanks

Bruce Armstrong 12/10/07 12:27:42 PM EST

I'd suggest posting your question in Sybase's DataWindow.Net forum.

http://www.sybase.com/detail?id=1031119

It would be much easier to address there.

Praveen 12/10/07 02:02:28 AM EST

Sir ,

We are using Datawindow.net 2.0[Trail] in VS2005 with ASP.Net 2.0 & C#.Net . Using WebDatawindow.net in ASP.Net data retrieving and also
data interting are so smoth . We using connection ADO.net & ASP.Net security trust level default security level ( full trust level )

For security problem most of developer suggests that 'medium trust'and also our webserver support only medium trust.So we are changing Security policy to 'medium'.
( In %windir%\Microsoft.NET\Framework\v2.0.50727\CONFIG\Web.Config
to change
to
)

***********Then I am facing a crucial problem ************
Required permissions cannot be acquired.
Description: An unhandled exception occurred during the execution of the current web request. Please review the stack trace for more information about the error and where it originated in the code.

Exception Details: System.Security.Policy.PolicyException: Required permissions cannot be acquired.

How can overcome to in this problem . If you have any solution ... pls forward me ...

Bruce Armstrong 11/14/07 07:09:07 AM EST

I'd suggest posting your question in Sybase's DataWindow.Net forum:

http://www.sybase.com/detail?id=1031119

It would be much easier to address there.

Atif Riaz 11/14/07 01:51:39 AM EST

Hey this is atif i have read your articles they were really help full. what i am trying is that i am using data window 2.0 in Dotnet now i want any CSS file like we do it for html or asp pages i want to define CSS file from where i can formate the report made in Data Window automatically. I need your help?

Bruce Armstrong 02/07/06 03:58:48 PM EST

If you can't find it here, check the Sybase CodeXchange site. I loaded it there as well:

http://datawindownet.codexchange.sybase.com/servlets/ProjectDocumentList...

Mike 02/07/06 02:58:03 PM EST

Where is an arcticle code?

SYS-CON Australia News Desk 02/03/06 01:45:59 PM EST

In this article, we're going to look at how DataWindow.NET technology is a simpler but more powerful way of formatting data in the presentation layer. We'll be taking a sample application provided by Microsoft for .NET and implementing it using DataWindow.NET technology.