November 2009 - Posts - Paul Kimmel's Blog

Reading Guid Fields with the SafeRead Helper

Professional DevExpress ASP.NET Controls contains a sample that uses a helper method I created called SafeRead. SafeRead includes a check for DBNull and null and then converts the field to the correct (or desired) type. One reader asked if it could be modified to work with Guids. I created a simple table with a primary key and a Guid column and used the sample code (in Listing 1); it appears that SafeRead handles Guids fine.

Listing 1: A helper method that reads database fields with a built-in check for null.

using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Data.SqlClient;

namespace SafeReadTest
{
class Program
{
    static void Main(string[] args)
    {
      string connectionString =
        @"Data Source=.\SQLEXPRESS;Initial Catalog=SafeReadTest;Integrated Security=True;Pooling=False";

List<SafeReadTable> list = new List<SafeReadTable>();

      using (SqlConnection connection =
        new SqlConnection(connectionString))
      {
        connection.Open();
        string SQL = "SELECT * FROM SafeReadTable";
        SqlCommand command = new SqlCommand(SQL, connection);
        SqlDataReader reader = command.ExecuteReader();
        while (reader.Read())
        {
          SafeReadTable o = new SafeReadTable();
          o.ID = SafeRead<int>("ID", reader, 0);
          o.GuidColumn = SafeRead<Guid>("GuidColumn", reader, Guid.NewGuid());
          list.Add(o);
        }
      }

      Array.ForEach<SafeReadTable>(list.ToArray(),
        o=>Console.WriteLine(o.GuidColumn));
      Console.ReadLine();

}

    private static T SafeRead<T>(string fieldname,
      SqlDataReader reader, T defaultValue)
    {
      object o = reader[fieldname];
      return o == null || o == System.DBNull.Value ?
        defaultValue : (T)Convert.ChangeType(o, typeof(T));
    }
}

public class SafeReadTable
{
    private int iD;
    public int ID
    {
      get { return iD; }
      set { iD = value; }
    }

    private Guid guidColumn;
    public Guid GuidColumn
    {
      get { return guidColumn; }
      set
      {
        guidColumn = value;
      }
    }
}
}

A reasonable revision to custom entity classes is to use nullable types {e.g. int?}. This will let you assign null values instead of default values to entity properties.

Posted Nov 29 2009, 07:53 PM by Paul Kimmel (DevExpress) with 4 comment(s)

PricedPassengerManifest View

For the section in Chapter 1 of Professional DevExpress ASP.NET Controls, I added a PricePaid column to the TotalFlight.Leg table. The PricePaid column is defined as a decimal and some random values were added for the Leg.PricePaid column. A new view PricedPassengerManifest was created and is shown in the listing below. It was an omission on my part not to list the view code in the book.

Listing 1: The PricedPassengerManifest View.

SELECT
dbo.Flight.FlightNumber,
dbo.LegManifest.LegManifestID,
dbo.Person.FirstName,
dbo.Person.LastName,
dbo.LegManifest.Seat,
dbo.Leg.PricePaid
FROM
dbo.LegManifest INNER JOIN
dbo.Person ON dbo.LegManifest.PersonID = dbo.Person.PersonID INNER JOIN
dbo.Leg ON dbo.LegManifest.LegID = dbo.Leg.LegID INNER JOIN
dbo.Flight ON dbo.Leg.FlightID = dbo.Flight.FlightID

Listing 2: The revised schema for the TotalFlight.Leg table.

USE [TotalFlight]
GO

/****** Object: Table [dbo].[Leg] Script Date: 11/29/2009 13:49:49 ******/
SET ANSI_NULLS ON
GO

SET QUOTED_IDENTIFIER ON
GO

SET ANSI_PADDING ON
GO

CREATE TABLE [dbo].[Leg](
    [LegID] [int] IDENTITY(1,1) NOT NULL,
    [FlightID] [int] NOT NULL,
    [OriginID] [int] NOT NULL,
    [DestinationID] [int] NOT NULL,
    [Start] [datetime] NOT NULL,
    [EstimatedEnd] [datetime] NOT NULL,
    [ActualEnd] [datetime] NOT NULL,
    [PassengersBooked] [int] NOT NULL,
    [PassengersPresent] [int] NOT NULL,
    [Notes] [varchar](100) NULL,
    [PricePaid] [decimal](18, 0) NULL,
CONSTRAINT [PK_Leg] PRIMARY KEY CLUSTERED
(
    [LegID] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY]

GO

SET ANSI_PADDING OFF
GO

Posted Nov 29 2009, 06:55 PM by Paul Kimmel (DevExpress) with 1 comment(s)

TotalFlight Database on Wrox

If you are looking for the TotalFlight database on Wrox.com the link is http://content.wrox.com/wroxfiles/TotalFlight.zip. (This is probably a faster download than my personal server.)

Posted Nov 29 2009, 12:24 AM by Paul Kimmel (DevExpress) with 2 comment(s)

A Small Version of the TotalFlight Database is Posted

I created a smaller version of the TotalFlight database for Professional DevExpress ASP.NET Controls. The larger tables now contain only 1,000 rows of data. This should be more manageable to download. Updated book data can be downloaded from ftp://www.softconcepts.com/book_data/. Updates to the various Wrox and DevExpress sites will be made as quickly as possible.

Posted Nov 26 2009, 02:07 PM by Paul Kimmel (DevExpress) with no comments

PassengerManifest for TotalFlight

Another reader indicated that the PassengerManifest view was not listed in Professional DevExpress ASP.NET Controls. It should be in the TotalFlight database, but in the event that someone else needs this view here is a script that creates that view:

CREATE VIEW [dbo].[PassengerManifest]
AS
SELECT     dbo.Flight.FlightNumber, dbo.Person.LastName, dbo.Person.FirstName, dbo.Person.Title, dbo.LegManifest.Seat
FROM         dbo.LegManifest INNER JOIN
                      dbo.Person ON dbo.LegManifest.PersonID = dbo.Person.PersonID INNER JOIN
                      dbo.Leg ON dbo.LegManifest.LegID = dbo.Leg.LegID INNER JOIN
                      dbo.Flight ON dbo.Leg.FlightID = dbo.Flight.FlightID

GO

Posted Nov 26 2009, 01:35 PM by Paul Kimmel (DevExpress) with no comments

Installing SSMS for SQLEXPRESS 2008

I wrote this blog in support of a reader question about using the TotalFlight database for Professional DevExpress ASP.NET Controls with SQL Server 2008. Basically, if you install VS2010 beta or SQL Server EXPRESS 2008 SSMS (SQL Server Management Studio) is not installed. You can follow these instructions to install SSMS 2008.

Google for SSMS 2008 or use this link to go to the SQLEXPRESS 2008 download page and download the appropriate (x84 or x64 installation file). The installation program has a couple of dozen links, so its not really that clear. These instructions should lead you to the quickest route to the installation.

http://www.microsoft.com/downloads/details.aspx?FamilyID=7522a683-4cb2-454e-b908-e805e9bd4e28&DisplayLang=en
Save of Run the installation file. If you save it (about 240M) then run after the download is complete.
The first screen looks like Figure 1 with a lot of links. I installed on an x86 box running Windows 7 with SQLEXPRESS 2008 already installed. (You may have to tinker a bit if your configuration is vastly different.)
On screen 1 click the Installation link on the left.
On the Installation view click the New SQL Server stand-alone installation or add features to an existing installation link (Figure 2).
On the Setup Support Rules page click OK to install the setup support rules Figure 3).
On the next page click install to install setup support rules (no figure provided).
Click next after the rules report a has run unless you get some errors (see Figure 4).
Click Next to go to the next step. (This is where things are a little weird, see Figure 5.)
Leave the Perform a new installation of SQL Server 2008 checked and click Next. (It seems intuitive that you might check add features, but this doesn’t seem to get us where we want—installing SSMS).
Click Next on the Product Key page; no product key is required.
Check “I accept the license terms” and click Next.
On the Feature Selection step—see Figure 6—under Shared Features click Management Tools and click Next.
Click Next on Disk Space Requirements assuming you have the space available.
Click next on Error and Usage Reporting unless the holidays have you feeling like a good Samaritan.
The Installation Rules runs again--~snooze~
Click Next
Finally, (whew!) on the Ready to Install page click Install (see Figure 7).

I think no one read Alan Cooper because this all-in-one install process makes me think that the inmates are indeed loose and running the asylum. You could try searching for the SSMS install package and running it directly, but I found a few forum posts that indicated that this approach fails.

If you are reading Professional DevExpress ASP.NET Controls over the holidays—and again are feeling generous—then post a review on Amazon or my blog. I actually read these things good and bad and try to update information or use what I learn for future projects.

Happy Holidays!

Figure 1: Click the Installation link on the left.

Figure 2: Click the New SQL Server stand-alone installation or ad features to an existing installation.

Figure 3: Click OK to install the setup support rules.

Figure 4: Setup support rules detailed report.

Figure 5: Leave the perform a new installation of SQL Server 2008 checked.

Figure 6: Click Management Tools under shared features

Figure 7: SQL Server Management Studio 2008.

Posted Nov 25 2009, 08:14 PM by Paul Kimmel (DevExpress) with 1 comment(s)

TotalFlight Database

I am uploading the TotalFlight database to the Wrox site. You can also download a copy at ftp://www.softconcepts.com. Its a big file, about 126 megabytes. You can log on with an anonymous user and your email address for the password. Here is the script for the database if you want to create an empty version of the database.

/****** Object: StoredProcedure [dbo].[InsertPerson]    Script Date: 11/24/2009 13:57:50 ******/
IF EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[InsertPerson]') AND type in (N'P', N'PC'))
DROP PROCEDURE [dbo].[InsertPerson]
GO
/****** Object: View [dbo].[PassengerManifest]    Script Date: 11/24/2009 13:57:50 ******/
IF EXISTS (SELECT * FROM sys.views WHERE object_id = OBJECT_ID(N'[dbo].[PassengerManifest]'))
DROP VIEW [dbo].[PassengerManifest]
GO
/****** Object: StoredProcedure [dbo].[DeletePerson]    Script Date: 11/24/2009 13:57:50 ******/
IF EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[DeletePerson]') AND type in (N'P', N'PC'))
DROP PROCEDURE [dbo].[DeletePerson]
GO
/****** Object: StoredProcedure [dbo].[UpdatePerson]    Script Date: 11/24/2009 13:57:50 ******/
IF EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[UpdatePerson]') AND type in (N'P', N'PC'))
DROP PROCEDURE [dbo].[UpdatePerson]
GO
/****** Object: Table [dbo].[Cities]    Script Date: 11/24/2009 13:57:50 ******/
IF EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[Cities]') AND type in (N'U'))
DROP TABLE [dbo].[Cities]
GO
/****** Object: Table [dbo].[Flight]    Script Date: 11/24/2009 13:57:50 ******/
IF EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[Flight]') AND type in (N'U'))
DROP TABLE [dbo].[Flight]
GO
/****** Object: Table [dbo].[Person]    Script Date: 11/24/2009 13:57:50 ******/
IF EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[Person]') AND type in (N'U'))
DROP TABLE [dbo].[Person]
GO
/****** Object: Table [dbo].[Leg]    Script Date: 11/24/2009 13:57:50 ******/
IF EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[Leg]') AND type in (N'U'))
DROP TABLE [dbo].[Leg]
GO
/****** Object: Table [dbo].[LegManifest]    Script Date: 11/24/2009 13:57:50 ******/
IF EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[LegManifest]') AND type in (N'U'))
DROP TABLE [dbo].[LegManifest]
GO
/****** Object: Table [dbo].[LegManifest]    Script Date: 11/24/2009 13:57:50 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
IF NOT EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[LegManifest]') AND type in (N'U'))
BEGIN
CREATE TABLE [dbo].[LegManifest](
    [LegManifestID] [int] IDENTITY(1,1) NOT NULL,
    [LegID] [int] NOT NULL,
    [PersonID] [int] NOT NULL,
    [Seat] [nvarchar](5) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
CONSTRAINT [PK_LegManifest] PRIMARY KEY CLUSTERED
(
    [LegManifestID] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON)
)
END
GO
/****** Object: Table [dbo].[Leg]    Script Date: 11/24/2009 13:57:50 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
IF NOT EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[Leg]') AND type in (N'U'))
BEGIN
CREATE TABLE [dbo].[Leg](
    [LegID] [int] IDENTITY(1,1) NOT NULL,
    [FlightID] [int] NOT NULL,
    [OriginID] [int] NOT NULL,
    [DestinationID] [int] NOT NULL,
    [Start] [datetime] NOT NULL,
    [EstimatedEnd] [datetime] NOT NULL,
    [ActualEnd] [datetime] NOT NULL,
    [PassengersBooked] [int] NOT NULL,
    [PassengersPresent] [int] NOT NULL,
    [Notes] [varchar](100) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
CONSTRAINT [PK_Leg] PRIMARY KEY CLUSTERED
(
    [LegID] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON)
)
END
GO
/****** Object: Table [dbo].[Person]    Script Date: 11/24/2009 13:57:50 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
IF NOT EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[Person]') AND type in (N'U'))
BEGIN
CREATE TABLE [dbo].[Person](
    [PersonID] [int] IDENTITY(1,1) NOT NULL,
    [FirstName] [varchar](25) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL,
    [LastName] [varchar](25) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL,
    [Title] [varchar](50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
    [Height] [varchar](8) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
    [Weight] [int] NULL,
    [SeatPreference] [varchar](10) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
    [OriginCity] [varchar](25) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
    [OriginState] [varchar](25) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
    [DestinationCity] [varchar](25) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
    [DestinationState] [varchar](25) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
    [Address1] [varchar](30) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL,
    [Address2] [varchar](30) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
    [City] [varchar](30) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL,
    [State] [varchar](25) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL,
    [PostalCode] [varchar](50) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL,
CONSTRAINT [PK_Person] PRIMARY KEY CLUSTERED
(
    [PersonID] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON)
)
END
GO
IF NOT EXISTS (SELECT * FROM ::fn_listextendedproperty(N'MS_Description' , N'SCHEMA',N'dbo', N'TABLE',N'Person', N'COLUMN',N'Title'))
EXEC sys.sp_addextendedproperty @name=N'MS_Description', @value=N'Customer preferred form of address' , @level0type=N'SCHEMA',@level0name=N'dbo', @level1type=N'TABLE',@level1name=N'Person', @level2type=N'COLUMN',@level2name=N'Title'
GO
IF NOT EXISTS (SELECT * FROM ::fn_listextendedproperty(N'MS_Description' , N'SCHEMA',N'dbo', N'TABLE',N'Person', N'COLUMN',N'Height'))
EXEC sys.sp_addextendedproperty @name=N'MS_Description', @value=N'Height used for smart-seat (TM) option' , @level0type=N'SCHEMA',@level0name=N'dbo', @level1type=N'TABLE',@level1name=N'Person', @level2type=N'COLUMN',@level2name=N'Height'
GO
IF NOT EXISTS (SELECT * FROM ::fn_listextendedproperty(N'MS_Description' , N'SCHEMA',N'dbo', N'TABLE',N'Person', N'COLUMN',N'Weight'))
EXEC sys.sp_addextendedproperty @name=N'MS_Description', @value=N'Weight used for smart-seat (TM) option' , @level0type=N'SCHEMA',@level0name=N'dbo', @level1type=N'TABLE',@level1name=N'Person', @level2type=N'COLUMN',@level2name=N'Weight'
GO
IF NOT EXISTS (SELECT * FROM ::fn_listextendedproperty(N'MS_Description' , N'SCHEMA',N'dbo', N'TABLE',N'Person', N'COLUMN',N'OriginCity'))
EXEC sys.sp_addextendedproperty @name=N'MS_Description', @value=N'Default origin city' , @level0type=N'SCHEMA',@level0name=N'dbo', @level1type=N'TABLE',@level1name=N'Person', @level2type=N'COLUMN',@level2name=N'OriginCity'
GO
IF NOT EXISTS (SELECT * FROM ::fn_listextendedproperty(N'MS_Description' , N'SCHEMA',N'dbo', N'TABLE',N'Person', N'COLUMN',N'OriginState'))
EXEC sys.sp_addextendedproperty @name=N'MS_Description', @value=N'Default origin state' , @level0type=N'SCHEMA',@level0name=N'dbo', @level1type=N'TABLE',@level1name=N'Person', @level2type=N'COLUMN',@level2name=N'OriginState'
GO
IF NOT EXISTS (SELECT * FROM ::fn_listextendedproperty(N'MS_Description' , N'SCHEMA',N'dbo', N'TABLE',N'Person', N'COLUMN',N'DestinationCity'))
EXEC sys.sp_addextendedproperty @name=N'MS_Description', @value=N'Default destination city' , @level0type=N'SCHEMA',@level0name=N'dbo', @level1type=N'TABLE',@level1name=N'Person', @level2type=N'COLUMN',@level2name=N'DestinationCity'
GO
IF NOT EXISTS (SELECT * FROM ::fn_listextendedproperty(N'MS_Description' , N'SCHEMA',N'dbo', N'TABLE',N'Person', N'COLUMN',N'DestinationState'))
EXEC sys.sp_addextendedproperty @name=N'MS_Description', @value=N'Default destination state' , @level0type=N'SCHEMA',@level0name=N'dbo', @level1type=N'TABLE',@level1name=N'Person', @level2type=N'COLUMN',@level2name=N'DestinationState'
GO
IF NOT EXISTS (SELECT * FROM ::fn_listextendedproperty(N'MS_Description' , N'SCHEMA',N'dbo', N'TABLE',N'Person', N'COLUMN',N'Address1'))
EXEC sys.sp_addextendedproperty @name=N'MS_Description', @value=N'Default location for sending lost luggage and promotional items' , @level0type=N'SCHEMA',@level0name=N'dbo', @level1type=N'TABLE',@level1name=N'Person', @level2type=N'COLUMN',@level2name=N'Address1'
GO
/****** Object: Table [dbo].[Flight]    Script Date: 11/24/2009 13:57:50 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
IF NOT EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[Flight]') AND type in (N'U'))
BEGIN
CREATE TABLE [dbo].[Flight](
    [FlightID] [int] IDENTITY(1,1) NOT NULL,
    [OriginID] [int] NOT NULL,
    [DestinationID] [int] NOT NULL,
    [FlightNumber] [varchar](10) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL,
    [PlaneID] [int] NOT NULL,
    [Status] [varchar](15) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL,
    [PlannedStart] [datetime] NOT NULL,
    [ActualStart] [datetime] NULL,
    [PlannedCompletion] [datetime] NOT NULL,
    [ActualCompletion] [datetime] NULL,
    [Latitude] [varchar](15) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
    [Longitude] [varchar](15) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
    [Altitude] [int] NULL,
    [EstimatedAirspeed] [int] NULL,
CONSTRAINT [PK_Flight] PRIMARY KEY CLUSTERED
(
    [FlightID] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON)
)
END
GO
IF NOT EXISTS (SELECT * FROM ::fn_listextendedproperty(N'MS_Description' , N'SCHEMA',N'dbo', N'TABLE',N'Flight', N'COLUMN',N'Status'))
EXEC sys.sp_addextendedproperty @name=N'MS_Description', @value=N'InProgress, Cancelled, Completed, Delayed, Late, OnTime' , @level0type=N'SCHEMA',@level0name=N'dbo', @level1type=N'TABLE',@level1name=N'Flight', @level2type=N'COLUMN',@level2name=N'Status'
GO
/****** Object: Table [dbo].[Cities]    Script Date: 11/24/2009 13:57:50 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
IF NOT EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[Cities]') AND type in (N'U'))
BEGIN
CREATE TABLE [dbo].[Cities](
    [HubID] [int] IDENTITY(1,1) NOT NULL,
    [City] [varchar](30) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL,
    [State] [varchar](30) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL,
    [Country] [varchar](30) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL,
CONSTRAINT [PK_Cities] PRIMARY KEY CLUSTERED
(
    [HubID] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON)
)
END
GO
/****** Object: StoredProcedure [dbo].[UpdatePerson]    Script Date: 11/24/2009 13:57:50 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
IF NOT EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[UpdatePerson]') AND type in (N'P', N'PC'))
BEGIN
EXEC dbo.sp_executesql @statement = N'CREATE PROCEDURE [dbo].[UpdatePerson]
    @PersonID int,
    @FirstName varchar(25),
    @LastName varchar(25),
    @Title varchar(50) = NULL,
    @Height varchar(8) = NULL,
    @Weight int = NULL,
    @SeatPreference varchar(10) = NULL,
    @OriginCity varchar(25) = NULL,
    @OriginState varchar(25) = NULL,
    @DestinationCity varchar(25) = NULL,
    @DestinationState varchar(25) = NULL,
    @Address1 varchar(30),
    @Address2 varchar(30) = NULL,
    @City varchar(30),
    @State varchar(25),
    @PostalCode varchar(50)
AS
BEGIN
    UPDATE
        Person
    SET
        FirstName = @FirstName,
        LastName = @LastName,
        Title = @Title,
        Height = @Height,
        Weight = @Weight,
        SeatPreference = @SeatPreference,
        OriginCity = @OriginCity,
        OriginState = @OriginState,
        DestinationCity = @DestinationCity,
        DestinationState = @DestinationState,
        Address1 = @Address1,
        Address2 = @Address2,
        City = @City,
        State = @State,
        PostalCode = @PostalCode
    WHERE PersonID = @PersonID
END
'
END
GO
/****** Object: StoredProcedure [dbo].[DeletePerson]    Script Date: 11/24/2009 13:57:50 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
IF NOT EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[DeletePerson]') AND type in (N'P', N'PC'))
BEGIN
EXEC dbo.sp_executesql @statement = N'CREATE PROCEDURE [dbo].[DeletePerson]
    @PersonID int
AS
BEGIN
    DELETE FROM Person WHERE PersonID = @PersonID
END
'
END
GO
/****** Object: View [dbo].[PassengerManifest]    Script Date: 11/24/2009 13:57:50 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
IF NOT EXISTS (SELECT * FROM sys.views WHERE object_id = OBJECT_ID(N'[dbo].[PassengerManifest]'))
EXEC dbo.sp_executesql @statement = N'CREATE VIEW [dbo].[PassengerManifest]
AS
SELECT     dbo.Flight.FlightNumber, dbo.Person.LastName, dbo.Person.FirstName, dbo.Person.Title, dbo.LegManifest.Seat
FROM         dbo.LegManifest INNER JOIN
                      dbo.Person ON dbo.LegManifest.PersonID = dbo.Person.PersonID INNER JOIN
                      dbo.Leg ON dbo.LegManifest.LegID = dbo.Leg.LegID INNER JOIN
                      dbo.Flight ON dbo.Leg.FlightID = dbo.Flight.FlightID
'
GO
IF NOT EXISTS (SELECT * FROM ::fn_listextendedproperty(N'MS_DiagramPane1' , N'SCHEMA',N'dbo', N'VIEW',N'PassengerManifest', NULL,NULL))
EXEC sys.sp_addextendedproperty @name=N'MS_DiagramPane1', @value=N'[0E232FF0-B466-11cf-A24F-00AA00A3EFFF, 1.00]
Begin DesignProperties =
   Begin PaneConfigurations =
      Begin PaneConfiguration = 0
         NumPanes = 4
         Configuration = "(H (1[33] 4[21] 2[17] 3) )"
      End
      Begin PaneConfiguration = 1
         NumPanes = 3
         Configuration = "(H (1 [50] 4 [25] 3))"
      End
      Begin PaneConfiguration = 2
         NumPanes = 3
         Configuration = "(H (1 [50] 2 [25] 3))"
      End
      Begin PaneConfiguration = 3
         NumPanes = 3
         Configuration = "(H (4 [30] 2 [40] 3))"
      End
      Begin PaneConfiguration = 4
         NumPanes = 2
         Configuration = "(H (1 [56] 3))"
      End
      Begin PaneConfiguration = 5
         NumPanes = 2
         Configuration = "(H (2 [66] 3))"
      End
      Begin PaneConfiguration = 6
         NumPanes = 2
         Configuration = "(H (4 [50] 3))"
      End
      Begin PaneConfiguration = 7
         NumPanes = 1
         Configuration = "(V (3))"
      End
      Begin PaneConfiguration = 8
         NumPanes = 3
         Configuration = "(H (1[56] 4[18] 2) )"
      End
      Begin PaneConfiguration = 9
         NumPanes = 2
         Configuration = "(H (1 [75] 4))"
      End
      Begin PaneConfiguration = 10
         NumPanes = 2
         Configuration = "(H (1[66] 2) )"
      End
      Begin PaneConfiguration = 11
         NumPanes = 2
         Configuration = "(H (4 [60] 2))"
      End
      Begin PaneConfiguration = 12
         NumPanes = 1
         Configuration = "(H (1) )"
      End
      Begin PaneConfiguration = 13
         NumPanes = 1
         Configuration = "(V (4))"
      End
      Begin PaneConfiguration = 14
         NumPanes = 1
         Configuration = "(V (2))"
      End
      ActivePaneConfig = 0
   End
   Begin DiagramPane =
      Begin Origin =
         Top = -192
         Left = 0
      End
      Begin Tables =
         Begin Table = "Person"
            Begin Extent =
               Top = 70
               Left = 9
               Bottom = 187
               Right = 178
            End
            DisplayFlags = 280
            TopColumn = 12
         End
         Begin Table = "Leg"
            Begin Extent =
               Top = 36
               Left = 466
               Bottom = 153
               Right = 647
            End
            DisplayFlags = 280
            TopColumn = 6
         End
         Begin Table = "Flight"
            Begin Extent =
               Top = 252
               Left = 220
               Bottom = 369
               Right = 400
            End
            DisplayFlags = 280
            TopColumn = 0
         End
         Begin Table = "LegManifest"
            Begin Extent =
               Top = 9
               Left = 211
               Bottom = 112
               Right = 405
            End
            DisplayFlags = 280
            TopColumn = 1
         End
      End
   End
   Begin SQLPane =
   End
   Begin DataPane =
      Begin ParameterDefaults = ""
      End
      Begin ColumnWidths = 9
         Width = 284
         Width = 1500
         Width = 1500
         Width = 1500
         Width = 1500
         Width = 1500
         Width = 1500
         Width = 1500
         Width = 1500
      End
   End
   Begin CriteriaPane =
      Begin ColumnWidths = 11
         Column = 1440
         Alias = 900
         Table = 1170
         Output = 720
         Append = 1400
         NewValue = 1170
         SortType = 1350
         SortOrder = 1410
         GroupBy = 1350
         Filter = 1350
         Or = 1350
         Or = 1350
         Or = 1350
      End
' , @level0type=N'SCHEMA',@level0name=N'dbo', @level1type=N'VIEW',@level1name=N'PassengerManifest'
GO
IF NOT EXISTS (SELECT * FROM ::fn_listextendedproperty(N'MS_DiagramPane2' , N'SCHEMA',N'dbo', N'VIEW',N'PassengerManifest', NULL,NULL))
EXEC sys.sp_addextendedproperty @name=N'MS_DiagramPane2', @value=N'   End
End
' , @level0type=N'SCHEMA',@level0name=N'dbo', @level1type=N'VIEW',@level1name=N'PassengerManifest'
GO
IF NOT EXISTS (SELECT * FROM ::fn_listextendedproperty(N'MS_DiagramPaneCount' , N'SCHEMA',N'dbo', N'VIEW',N'PassengerManifest', NULL,NULL))
EXEC sys.sp_addextendedproperty @name=N'MS_DiagramPaneCount', @value=2 , @level0type=N'SCHEMA',@level0name=N'dbo', @level1type=N'VIEW',@level1name=N'PassengerManifest'
GO
/****** Object: StoredProcedure [dbo].[InsertPerson]    Script Date: 11/24/2009 13:57:50 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
IF NOT EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[InsertPerson]') AND type in (N'P', N'PC'))
BEGIN
EXEC dbo.sp_executesql @statement = N'CREATE PROCEDURE [dbo].[InsertPerson]
    @PersonID int,
    @FirstName varchar(25),
    @LastName varchar(25),
    @Title varchar(50) = NULL,
    @Height varchar(8) = NULL,
    @Weight int = NULL,
    @SeatPreference varchar(10) = NULL,
    @OriginCity varchar(25) = NULL,
    @OriginState varchar(25) = NULL,
    @DestinationCity varchar(25) = NULL,
    @DestinationState varchar(25) = NULL,
    @Address1 varchar(30),
    @Address2 varchar(30) = NULL,
    @City varchar(30),
    @State varchar(25),
    @PostalCode varchar(50)
AS
BEGIN
    INSERT INTO Person
    (
        FirstName,
        LastName,
        Title,
        Height,
        Weight,
        SeatPreference,
        OriginCity,
        OriginState,
        DestinationCity,
        DestinationState,
        Address1,
        Address2,
        City,
        [State],
        PostalCode
    )
    VALUES
    (
        @FirstName,
        @LastName,
        @Title,
        @Height,
        @Weight,
        @SeatPreference,
        @OriginCity,
        @OriginState,
        @DestinationCity,
        @DestinationState,
        @Address1,
        @Address2,
        @City,
        @State,
        @PostalCode
    )
END
'
END
GO

Posted Nov 24 2009, 06:49 PM by Paul Kimmel (DevExpress) with 10 comment(s)

Displaying Content from an ASPxMenu in an iframe

Conferences like PDC are good because it reminds us that real customers have a wide variety of challenges that it is possible for us—purveyors of code goodness—to lose sight of. Customers are using older styles of implementation. They are faced with upper boundary conditions that may be hard for us to emulate in a laboratory somewhere, and they may just be trying to do something that we haven’t thought of.

One customer mentioned a competitor’s editor that couldn’t handle 100M data files. Manually editing 100M+ files is probably very challenging and represents or at least approaches a boundary condition. Another customer said that he had a Web application that used iframes and that he wanted to update the controls to DevExpress ASP.NET controls but didn’t want to restructure the architecture of the site. His problem is: how do I load content using an ASPxMenu into an iframe on the same page. This, being a challenge I can handle while standing at a kiosk at PDC, is the one I solved this morning.

The basic idea as related to me was that one iframe had an ASPxMenu and the other iframe should display the content indicated by the menu item clicked. To keep the sample is structured with a Default.aspx page containing the two iframes and the ASPxMenu. In the first iframe a page containing the menu is assigned to the first iframe’s src attribute. The MenuPage.aspx contains an ASPxMenu with one menu item. The menu item references a page named OtherPage.aspx. Click on the menu item and the OtherPage.aspx is loaded into the second iframe. The key to the solution is to set the Target attribute of the menu item to the target iframe’s name.


<dxm:ASPxMenu ID="ASPxMenu1" runat="server" > 
  <Items> 

 <dxm:MenuItem NavigateUrl="~/OtherPage.aspx"  
 Text="Go" Target="iframe2"> 

</dxm:MenuItem> 

</Items> 

</dxm:ASPxMenu>

Setting the Target to an iframe name is not immediately intuitive because the dropdown list for the Target attribute contains just the standard choices: _blank, _parent, _search, _self, and _top. You have to know to manually type in the iframe name (not id but name). Listing 1 contains the complete listing for the page containing the iframes (and a stub page, StartPage.aspx just to show you where the second iframe is). Listing 2 contains the listing for the page that contains the ASPxMenu, so you can see the relationship between the iframe’s, menu, and content pages.

Listing 1: The main page containing the iframes.

<%@ Page Language="C#" AutoEventWireup="true" CodeFile="Default.aspx.cs" Inherits="_Default" %>

<%@ Register Assembly="DevExpress.Web.v9.2, Version=9.2.8.0, Culture=neutral, PublicKeyToken=b88d1754d700e49a"
Namespace="DevExpress.Web.ASPxMenu" TagPrefix="dxm" %>

<!DOCTYPE html PUBLIC "-//W3C//DTD XHTML 1.0 Transitional//EN" "http://www.w3.org/TR/xhtml1/DTD/xhtml1-transitional.dtd">

Listing 2: The page containing the ASPxMenu.

<%@ Page Language="C#" AutoEventWireup="true" CodeFile="MenuPage.aspx.cs" Inherits="MenuPage" %>

<%@ Register assembly="DevExpress.Web.v9.2, Version=9.2.8.0, Culture=neutral, PublicKeyToken=b88d1754d700e49a" namespace="DevExpress.Web.ASPxMenu" tagprefix="dxm" %>

<%@ Register assembly="DevExpress.Web.v9.2, Version=9.2.8.0, Culture=neutral, PublicKeyToken=b88d1754d700e49a" namespace="DevExpress.Web.ASPxSiteMapControl" tagprefix="dxsm" %>

<!DOCTYPE html PUBLIC "-//W3C//DTD XHTML 1.0 Transitional//EN" "http://www.w3.org/TR/xhtml1/DTD/xhtml1-transitional.dtd">

Posted Nov 19 2009, 01:41 PM by Paul Kimmel (DevExpress) with no comments

Dynamically Generating an ASPxGridView and Changing Binding Sources

Sometimes I mine our online examples for ideas about blogs or to explore nooks and crannies of controls. A general utility-type solution I routinely find useful is a dynamic database table browser. The user picks the database and the table and a simple form is generated. The simplest kind of form is a Web page with an ASPxGridView bound to the data source. Creating an ASPxGridView dynamically is easy enough, but if you want paging to work and to be able to dynamically change the data source then a few extra steps are needed.

The sample described in this blog reads the SQL Server master database to list all of the databases in a server instance. Pick a database and that databases tables and schemas tables are read to figure out all of the tables with their schemas and these are listed. Pick a table and an ASPxGridView is created and a query that provides data to the ASPxGridView is bound to the grid. This example is a marginal use case—you may not need to actually create and bind an ASPxGridView routinely—it does permit you to explore querying the master, generating dynamic SQL, and look at the basic properties you need to manage to get data into a grid. (I also checked the support pages: some of you have inquired about this very scenario.)

When you dynamically create an ASPxGridView and dynamically bind data to it there is some caching that happens on the grid columns. If you dynamically change the data source then the ghost of the previous columns exist, and binding to the ASPxGridView (with another data source) can lead to a “a field or property with the name ‘xxxxx’ was not found on the selected source”, an HttpException. The field not found will be a field from the previous data source. The solution is to clear the old fields and auto-generate the new columns. Next, you will want paging to work so you need to set the KeyFieldName. The KeyFieldName has to be set last, after the DataBind(ing) happens.

It is an ideal to reduce dependencies on the order and timing of property settings, but permitting changes to state to happen in any order is not always possible. The commented code in Listing 1 shows you the orchestration and basic property settings that support a functioning, dynamic ASPxGridView with a dynamically set data source. The ASPX in Listing 2 uses a client-side JavaScript function to clear the second ASPxComboBox—the one containing the table. Both combo boxes auto-postback, so the ASPxGridView is created when both a database and table have been picked.

Listing 1: The code-behind showing—most importantly—showing the orchestration of ASPxGridView initialization.

Imports DevExpress.Web.ASPxGridView
Imports System.Data.SqlClient
Imports System.Data

Partial Class _Default
Inherits System.Web.UI.Page

Protected Sub Page_Load(ByVal sender As Object, _
ByVal e As EventArgs) Handles Me.Load

    ' Always populate the list of databases
    PopulateDatabases()
    ' Create the ASPxGridView (conditionally), so that posts
    ' for paging and sorting, etc, have a grid
    CreateGridView()
End Sub

''' <summary>
''' Select all of the SQL Server instance's databases from
''' the master table and bind the results to the ASPxComboBox1
''' </summary>
''' <remarks></remarks>
Private Sub PopulateDatabases()
    ASPxComboBox1.ValueField = "name"
    ASPxComboBox1.TextField = "name"
    Dim sql As String = String.Format("select [name] from {0}.sys.{1}", _
      "master", "databases")
    ASPxComboBox1.DataSource = GetTable("master", sql)
    ASPxComboBox1.DataBind()
End Sub

''' <summary>
''' Create the ASPxGridView
''' </summary>
''' <remarks></remarks>
Private Sub CreateGridView()
' If both a database and table have been selected
If (DatabaseName = "" Or TableName = "") Then Return

    ' Instantiate the ASPxGridView
    Dim grid As ASPxGridView = New ASPxGridView()
    ' Define an ID/name
    grid.ID = "ASPxGridView1"

    ' Add the grid to the Page's Controls collection
    ' This is where ghost columns will be added if the a
    ' grid with the same ID was previously bound to a
    ' different data source. (The ghost-column behavior
    ' doesn't happen with the Microsoft GridView
    Page.Form.Controls.Add(grid)

grid.SettingsBehavior.AllowFocusedRow = True

' Define the SQL select command
Dim selectCommand = String.Format("select * from {0}", TableName)

    ' Clearing the columns gets rid of any ghost columns
    grid.Columns.Clear()
    ' Set auto-generate to true to have the grid define the
    ' grid's columns collection
    grid.AutoGenerateColumns = True

    ' Set the data source from a helper function
    grid.DataSource = CreateData(DatabaseName, selectCommand)
    ' Bind
    grid.DataBind()
    ' Set the key field here to support paging and sorting
    grid.KeyFieldName = GetKeyFieldName(DatabaseName, TableName)

End Sub

''' <summary>
''' A not wholly satisfactory additional hit on the database
''' used to determine the primary key field; multiply-keyed tables
''' might need a different approach. We only need to
''' read the schema for this method
''' </summary>
''' <param name="databaseName"></param>
''' <param name="tableName"></param>
''' <returns></returns>
''' <remarks></remarks>
Private Function GetKeyFieldName(ByVal databaseName As String, _
ByVal tableName As String) As String

Dim connectionString As String = String.Format("Data Source=.\SQLEXPRESS;Initial " + _
"Catalog={0};Integrated Security=True", databaseName)

    Dim table As DataTable = New DataTable()
    Using connection As SqlConnection = New SqlConnection(connectionString)
      connection.Open()
      Dim command As SqlCommand = New SqlCommand(String.Format( _
        "select * from {0}", tableName), connection)
      Dim adapter As SqlDataAdapter = New SqlDataAdapter(command)
      adapter.FillSchema(table, SchemaType.Source)
    End Using

    If (table.PrimaryKey.Count > 0) Then
      Return table.PrimaryKey(0).ColumnName
    Else
      Return ""
    End If
End Function

''' <summary>
''' A helper function creates a SQL data source from the
''' database and a query
''' </summary>
''' <param name="databaseName"></param>
''' <param name="sql"></param>
''' <returns></returns>
''' <remarks></remarks>
Private Function CreateData(ByVal databaseName As String, _
                              ByVal sql As String) As SqlDataSource
    Dim connection As String = String.Format("Data Source=.\SQLEXPRESS;Initial " + _
        "Catalog={0};Integrated Security=True", databaseName)

Return New SqlDataSource(connection, sql)
End Function

''' <summary>
''' Called surfacing constituent properties, this property
''' mitigates naming the combo box
''' </summary>
''' <value></value>
''' <returns></returns>
''' <remarks></remarks>
ReadOnly Property DatabaseName() As String
    Get
      Return ASPxComboBox1.Text
    End Get
End Property

''' <summary>
''' Ditto
''' </summary>
''' <value></value>
''' <returns></returns>
''' <remarks></remarks>
ReadOnly Property TableName() As String
    Get
      Return ASPxComboBox2.Text
    End Get
End Property

''' <summary>
''' When a database is selected in the first combo box
''' the second combo box is field with tables in the format
''' schema.tablename
''' </summary>
''' <param name="sender"></param>
''' <param name="e"></param>
''' <remarks></remarks>
Protected Sub ASPxComboBox1_SelectedIndexChanged(ByVal sender As Object, _
    ByVal e As System.EventArgs) Handles ASPxComboBox1.SelectedIndexChanged
    ASPxComboBox1.Text = ""
    PopulateTables(DatabaseName)
End Sub

''' <summary>
''' Gets the DataTable containing the data for the selected
''' database and table
''' </summary>
''' <param name="databaseName"></param>
''' <param name="sql"></param>
''' <returns></returns>
''' <remarks></remarks>
Private Function GetTable(ByVal databaseName As String, _
ByVal sql As String) As DataTable

    Dim connectionString As String = "Data Source=.\SQLEXPRESS;Initial " + _
    "Catalog={0};Integrated Security=True"
    connectionString = String.Format(connectionString, databaseName)

    Dim table As DataTable = New DataTable()
    Using connection As SqlConnection = New SqlConnection(connectionString)
      connection.Open()
      Dim command As SqlCommand = New SqlCommand(sql, _
                                                 connection)
      Dim adapter As SqlDataAdapter = New SqlDataAdapter(command)
      adapter.Fill(table)
    End Using

Return table
End Function

''' <summary>
''' Actually populates the combo box contain table names.
''' A join is used to get the name from one table (tables)
''' and the schema name from the schema table; the
''' schema information is normalized in system tables for
''' the selected database
''' </summary>
''' <param name="databaseName"></param>
''' <remarks></remarks>
Private Sub PopulateTables(ByVal databaseName As String)
ASPxComboBox2.ValueField = "name"
ASPxComboBox2.TextField = "name"

    Dim sql = String.Format("select s.name + '.' + t.name as name " + _
      "from {0}.sys.tables t inner join {0}.sys.schemas s on t.schema_id =s.schema_id", _
      databaseName)

ASPxComboBox2.DataSource = GetTable(databaseName, sql)
ASPxComboBox2.DataBind()
End Sub

End Class

Listing 2: The ASPX with a simple JavaScript client-side function for clearing the table combo box when the database name changes.

<%@ Page Language="VB" AutoEventWireup="false" CodeFile="Default.aspx.vb" Inherits="_Default" %>

<%@ Register assembly="DevExpress.Web.ASPxGridView.v9.2, Version=9.2.8.0, Culture=neutral, PublicKeyToken=b88d1754d700e49a" namespace="DevExpress.Web.ASPxGridView" tagprefix="dxwgv" %>
<%@ Register assembly="DevExpress.Web.ASPxEditors.v9.2, Version=9.2.8.0, Culture=neutral, PublicKeyToken=b88d1754d700e49a" namespace="DevExpress.Web.ASPxEditors" tagprefix="dxe" %>

<!DOCTYPE html PUBLIC "-//W3C//DTD XHTML 1.0 Transitional//EN" "http://www.w3.org/TR/xhtml1/DTD/xhtml1-transitional.dtd">

Rather than explain the code-behind separately in this example the code is explained with comments inline. When you are dynamically adding an ASPxGridView the order of initialization matters. I experimented with the code to make sure that the grid is fully functional as well as being dynamically added.

As an aside you may not know that there are a couple of relatively new features in Visual Basic, including XML comments (shown) and the upcoming elimination of the Visual Basic line continuation character (_). The latter being an anachronistic character I won’t miss.

Posted Nov 13 2009, 10:06 PM by Paul Kimmel (DevExpress) with 1 comment(s)

Tip 1: Querying the master Database

The master database in SQL Server contains all of the information about all of the other databases. You can query thee master database to find out things like all of the other databases on a server, tables on those databases, and schemas. I have used this approach before when building application generation/and ORM (object relational modeling) tools. For instance read the databases and list them. Let the user pick a database. Read that database’s tables and list those. Let the user pick the table and generate some kind of form based on this information.

One challenge you may encounter is that the master table has tables in schemas and depending on the approach you use to read the master tables it may or may not work. For example, if you use SQL Server Management Studio Express 2005 then you can query sys.databases in the master table to determine what databases or running on that server.

select * from sys.databases

The preceding query works in SQL Server Management Studio against the master database, but it does not work with ADO.NET in Visual Studio (at least the first time). When I used ADO.NET, the preceding query, and a SqlDataAdapter to fill a DataTable the code responded with an invalid object name sys.databases. By changing the table name to its SQL Server 2000 version (rewriting the query)

select * from master.dbo.sysdatabases

the code works and the exception no longer occurs. What’s really weird is that subsequently changing the query back to sys.databases no longer caused an exception either. I am still looking into whether this is an application caching issue, a connection caching issue, or because I have SQL Server Management Studio open. These flaky errors can consume a lot of time.

For the time being, if you get an invalid object error on ADO.NET code using a namespace other than dbo then try using the SQL 2000 version of the name, prefix the table, dbo, and a consolidated name for the table. (This looks like what the Data Source Wizard in Visual Studio is doing with databases like AdventureWorks.)

After a little examination it looks like the default database to connect to is not master, so even though master was expressed as the catalog argument in my connection string querying sys.databases without the database name caused the invalid object error. When I logged into SQL Server Management Studio and connected to the master database it seemed to resolve the problem in the code.

If you Google this issue—invalid object, sys.databases—you will see there is a very high noise ratio. Although of the hundreds of posts there is no single definitive answer. If you happen to know how to reproduce this error, why it occurs, then respond to this blog posting.

Posted Nov 12 2009, 08:03 PM by Paul Kimmel (DevExpress) with 4 comment(s)

Display an Image Directly in an ASPxGridView

When you use the Choose Data Source wizard to bind a data source to an ASPxGridView under some circumstances—I haven’t tested all—the Visual Studio data source wizard does not generate image columns. (I tried and verified this with the SQL Server AdventureWorks.Production.ProductPhoto table.) If you manually enter the SQL—select * from [Production].[ProductPhoto]—in the data source wizard then image columns will be rendered/generated in the ASPxGridView and something like system.byte[] will be displayed as the value for that column. (You can verify this by displaying the Production.ProductPhoto.LargePhoto in the ASPxGridView.)

The reason for this is that blob columns, if rendered, are rendered as GridViewDataTextColumn instances. The ASPX will look something like this:

  <dxwgv:GridViewDataTextColumn Caption="LargePhoto" FieldName="LargePhoto" VisibleIndex="1"> 
  </dxwgv:GridViewDataTextColumn>

The solution I provided in the Professional DevExpress ASP.NET Controls book was to define a DataItemTemplate for the LargePhoto column, place an ASPxBinaryImage control in the DataItemTemplate, and bind the Value attribute to the image field. That code looks something like this:

  <dxwgv:GridViewDataTextColumn Caption="LargePhoto" FieldName="LargePhoto" VisibleIndex="2">    
    <DataItemTemplate>
      <dxe:ASPxBinaryImage ID="ASPxBinaryImage1" runat="server"    
        Value='<%# Eval("LargePhoto") %>'>    
      </dxe:ASPxBinaryImage>    
    </DataItemTemplate>    
  </dxwgv:GridViewDataTextColumn>

This works of course, but there is an even easier means of display image data directly. The key is to change the grid view column type to the correct class manually. Change the view to the ASPX and modify the column tag to the correct type manually, GridViewDataBinaryImageColumn, set the FieldName property, and you are finished. No template is needed. No ASPxBinaryImage is needed, and there is nothing special to do. The corrected version of the tag will look something like this:

  <dxwgv:GridViewDataBinaryImageColumn Caption="LargePhoto" FieldName="LargePhoto" VisibleIndex="1">    
  </dxwgv:GridViewDataBinaryImageColumn>

This approach (shown above) is the easiest way I have found so far of displaying images directly from a database in an ASPxGridView.

The remaining question is why doesn’t the data source wizard make this leap of faith—figure out that the column is a GridViewDataBinaryImageColumn—for you? The answer is that the image is stored as a varbinary(max)—at least in the AdentureWorks database—and the actual binary bits may not be an image at all.

I also tried to automatically generate the correct column for the Northwind.Employees.Photo column, which is defined as an image, and the wizard ignored the image field again. Neither of the approaches above worked for the Northwind images. In the case of the Northwind database the problem is caused by another problem. The Northwind database has upscaled images from Access and there is some header bits that prevent the image from being displayed directly. Professional DevExpress ASP.NET Controls shows a third approach that uses an IHttpHandler to convert the image bytes into an actual image. (Refer to chapter 2, listing 2-23 for the IHttpHandler or check out earlier blog postings for an online implementation of this approach.)

Sometimes how you skin a cat depends on the cat. (No actual cats were harmed in the writing of this blog.)

Posted Nov 11 2009, 03:15 PM by Paul Kimmel (DevExpress) with no comments

TotalFlight Database for Chapter 1

The TotalFlight database for Chapter 1 was not posted on the Wrox site because its huge. I have it on good authority that they will post it, but after PDC when I get back and I can resend to the publisher. In the meantime you can reproduce the database using the .sql script that should be on the Wrox website and the .sqlgen file. Download a copy of Red-Gate’s SQL Data Generator 1 (trial edition) and load the script (the .sqlgen file). It will generate the million rows of data.

These are good skills to have as a general rule and might come in handy for future projects, especially projects that have HIPPAA protected data or other kinds of classified or sensitive data like social security numbers.

If you need the script, .sqlgen file or other book-related assistance drop me a line.

Paul

Posted Nov 11 2009, 08:13 PM by Paul Kimmel (DevExpress) with 3 comment(s)

ASPxGridView Columns: Point A to Z

Sometimes understanding how something is designed helps one understand all of the resources available. The ASPxGridView is very easy to use at its most fundamental level. Bind the ASPxGridView to a data source, set a few properties and you have a wide variety of viewing and editing features at your disposal. The ASPxGridView supports row editing, forms editing, and grid nesting—as described in our book Professional DevExpress ASP.NET Controls. However, all of this functionality comes at a cost. Fortunately for our customers the cost has been paid in terms of design and development by our internal ASPxGridView programmers. You could in fact use the ASPxGridView daily and not be aware of all of the infrastructure and plumbing that makes it so powerful yet easy to use.

Figure 1 shows you all of the interfaces and classes that make the grid columns work starting with the .NET Object class down to specific kinds of grid columns, like the GridViewDataBinaryImageColumn. (Read the model from top to bottom.) When you use the designer to bind data to a grid the actual columns are generated as instances of the GridViewDataxxx columns, rendered declaratively as tags in the ASPX. The specific column class determines the kind of editor the user will experience—whether a combobox, date/time editor, button or link.

Of course, you don’t have to memorize this information (or the model) to use the ASPxGridView but on rare occasions it doesn’t hurt to know. One such occasion is when you have a binary blob column that is actually an image. The ASPxGridView won’t guess that BLOB (binary columns) are always images, so images may be displayed—and generated as a GridViewDataTextColumn—as text. Usually the content is something like system.byte[]. Obviously this isn’t what you want. By knowing that the data is an image you can simply change the ASPX tag from GridViewDataTextColumn to a GridViewDataBinaryImage column and the image will magically appear at runtime.

Figure 1: The classes and interfaces that make up the ASPxGridViewColumns.

I was talking to a charming woman on the plane Monday, and in passing we talked about books. She said that she hadn’t read the Harry Pottter books. I said that in fact I had and that I enjoyed them. I enjoy them because an implied message in the books is that new knowledge can be obtained from reading, and knowledge imparts a magical power: information that can be used, employed, acted upon.

Posted Nov 11 2009, 07:52 PM by Paul Kimmel (DevExpress) with 6 comment(s)

Windows 7 Developer Boot Camp

The Windows 7 Developer Boot Camp is sold out--http://microsoftpdc.com/WhatsHappening/FREE-Windows-7-Developer-Boot-Camp-Nov-16. I am following this part of PDC because I will be at PDC representing DevExpress and because I will be participating in the Windows 7 Developer Boot Camp. I will be moderating the LINQ table topics on Monday, 11/16/2009 from 12:00 to 1:15—a lunch time event. I hope to see you there.

It looks like I will have a couple of copies of the book—Professional DevExpress ASP.NET Controls—on hand to give away.

Posted Nov 10 2009, 08:43 PM by Paul Kimmel (DevExpress) with 1 comment(s)

Book Update XXXXIII

Amazon.com indicates that Professional DevExpress ASP.NET Controls is in stock. There has already been some good feedback from our customers a few of our customers, and a common question is approximately why isn’t this content free?

The short answer is that DevExpress isn't [corrected] making money on the book. We negotiated a very low retail price in lieu of revenue. The long answer is that publishers publish, and producing content is a long way away from publishing a book. The collaboration with a professional publisher like Wrox is to ensure that the finished product—a book in this instance—is of high quality.

There is of course a lot of free content on the DevExpress website, and we hope the book provides additional content in a supplemental form. I will be blogging on related subject matter and the code is online on the Wrox site (for the book).

Posted Nov 10 2009, 07:23 PM by Paul Kimmel (DevExpress) with 17 comment(s)

This Blog

Recent Posts

Archives

Paul Kimmel's Blog