QuickAdmin Tutorial

Getting started

We recommend using a diagram of the table structure you wish to edit - it helps to visualise the relationships. Below is a diagram of the Products table from the IBuySpy Store, which we will be using for this demo.

To initialise QuickAdmin follow the instructions below:

• Open Visual Studio .NET.
• Create your new web project, or connect to an existing one.
• Make the toolbox visible (this may require you to click View > Toolbox).
• Right-click on the toolbox and click Customise Toolbox.
• Click on the .NET Framework Components tab, and then click Browse.
• Browse to your installation directory (probably C:\Program Files\Common Files\Cambro\QuickAdmin), and select the Cambro.Web.QuickAdmin.dll. Click Open, then OK.
• A number of controls will have been added to your toolbox.
• To organise the controls, drag the Admin control () onto a new aspx page.
• After a few seconds the controls will be organised in several tabs.
• QuickAdmin is now ready for configuration.

Configuration

Right-click on the QuickAdmin control, and click Property Builder.

The dialog box pictured below will apear. This will enable you to configure QuickAdmin's database connection.

• Choose your database type (SQL or OLEDB), and enter the connection string.
• Enter the name of the table you want to edit in the Database Table textbox, and enter the name of the primary key column in the DataField textbox.
  (Note: if you have a joint primary key of two columns, specify the second in the DataField2 textbox)
  For the demo, the Database Table is 'Products' and the DataField is 'ProductID'.
• Click on the Check Connection button to ensure the connection is working.
• Click OK.

Right-click on the QuickAdmin control and choose Step 2/3 - Other Properties. This enables you to change the global QuickAdmin settings, and the default functionality (Search, Add, Edit, Delete, etc.) that QuickAdmin will expose. When you are finished, click OK.

Right-click on the QuickAdmin control and choose Step 3/3 - Templates Introduction.

Templates

There are five templates:

• Simple Search - Defines the fields to search with the simple, full-text search (add one control per field)
  • Search a field in the local table with the Simple Local Search () control
  • Search a field in a linked table with the Simple One-to-Many Search () control
  • Search a field in a many-many linked table with the Simple Many-to-Many Search () control
• Advanced Search (optional) - Defines the fields on which to carry out an advanced search:
  • Search the local table using the Advanced Local Search () controls
  • Search a linked table using the Advanced One-to-Many Search () controls
  • Search a many-many linked table using the Advanced Many-to-Many Search () controls
• Results - This template should hold a single ResultsDataGrid () control. This control displays the results of the search.
• View - This template determines the controls present in the View, Edit and Add pages:
  • Edit a field in the local table with the Local Edit () controls
  • Edit a field in a linked table with the One-to-Many Edit () controls
  • Edit a field in a many-many linked table with the Many-to-Many Edit () controls
• Data (optional) - The DataList () control allows you to create simple data lists for use in various controls.
  This enables you to map a set of integer values in the database to string reprasentations in a drop-down or other control.

Simple Search Template

Right-click on the Admin panel and choose Edit Template > Simple Search

Text based fields (varchar, text, memo etc.) may be included in the simple search by adding a Simple Search control to the template. Add one Simple Search control for each field to be included in the search.

For fields that are in the local table, drag a Simple Local Search () control from the toolbox (above right) into the template (above left). The control will appear in the template as below:

Right-click on the control and select Property Builder. You will be presented with the following dialog box:

In the Local Field textbox, enter the name of the field to be included in the search. For this demo, enter 'ModelName', and then click OK. The control should change to show it has been correctly configured, as below:

If you are following the IBuySpy demo, add Simple Local Search () controls for the ModelName and Description columns.

Now drag a Simple One2Many Search () control to the template. Open the property builder and set the values as below. If you view the database structure at the top of this page you will see how the link between the Products and Categories tables is created. We specify that the CategoryID field in the Products table is associated with the CategoryID field in the Categories table, and we specify that the field to be included in the simple search is CategoryName.

Configuration of the Simple Search is complete. We'll move onto the advanced search template. Right click on the template header, and choose Edit Template > Advanced Search.

Advanced Search Template

Above left is Advanced search toolbox tab. Drag a LocalTextBox () into the Advanced Search Template and open the property puilder. This contains more options than in the case of the Simple controls.

• As before, the field to be searched should be entered in the LocalField box.
• The Title box enables you to specify the title of the Advanced Search Control (displayed in the left column of the advanced search panel).
• The Match Multiplier is used in a fuzzy search to weight the ranking.
• The Quotes check-box should be checked if the LocalField is text-based, and requires quotes in the SQL query.

After completing the advanced search property builder, the control will look as below:

Now for a slightly more complex Advanced Search control - drag a One2ManyDropDownList () into the template and open the property builder.

You will be presented with the above dialog. Here the default value for Quotes is false, as the Local Field in such a relationship is usually an integer. The Foreign Group Fields will be described later in this document. When the above is complete, click OK.

For all tables which are linked to the local table in a One-to-Many relationship, you can use the Advanced One-to-Many Search () controls to search them. For the field above, the current control could be replaced with any one of: One2ManyCheckBoxList (), One2ManyListBox (), One2ManyRadioButtonList () or a One2ManyTextBox ().

Create another couple of Advanced Search controls, one to search the Model Number (), and another to search the price of the product and return any within a specified range ().

Results Template

Drag the ResultsDataGrid () control into the template:

Again, right-click and choose Property Builder. The most important setting for the ResultsDataGrid is the Data Key Field, which is the primary key of your local table. You should enter 'ProductID' in the case of this demo. If you have a joint primary key you can specify the second key in the Data Key Field 2 box.

After setting the key field(s), the columns visible to the user must be added. Click to the Columns tab on the left of the dialog, and you will be presented with the following:

The ResultsDataGrid is similar to the DataGrid control with a few extras. The supported column types are Results Column, Hyperlink Column and Template Column.

Results Column

The most regularly used is the Results Column, which displays as a generic column in the grid. Select Results Column, and add it to the Selected Columnns list by clicking the arrow button. Ensuring the column you have just added is selected, enter the following information in the dialog:

Click OK, and the ResultsDataGrid will render the first 100 records from the database. Add two more columns, one for ModelName, the other for ModelNumber.

You can also add a column for the CategoryName, as this is in a table linked by a search control (see 'Important Note' below).

The other options in this dialog are explained below:

• Footer Text - If the Show Footer check-box is checked on the General page of the ResultsDataGrid property designer, this text appears at the bottom of the results column.
• Header Image - If specified, this displays an image in the header instead of the text
• Sort Expression - If the Allow Sorting check-box is checked on the General page of the ResultsDataGrid property designer, this field determines on which field to sort. If specified, the header will appear as a link that changes the sorting.
• Visible - This shows and hides the column.
• Table - This must be specified if the Data Field specified an ambiguous column name (one that appears in more than one table - e.g. CategoryID in the demo).
• Data Format String - Use this to format the results.
• Tool Tip - This is displayed in a tool-tip when the mouse is hovered over the column.
• DataSet to bind to - This field is used to bind a DataSet to the column - it will be explained later in the document.
• Allow Click Through To View Page - If checked, this renders as a link to the View/Edit page.
• Text Or Memo Field – If this database field is a large text type (Text in SQL, Memo in Access), this should be checked.

HyperLink Column

Another useful column is the HyperLink column. See below for an example relevant to this demo:

The search results are products on the IBuySpy Store Demo. A relavent link would be to the product on the website itself. Complete the column as in the image above.

Several additional properties are available for the HyperLink column:

• Text and URL - Specifies the Text or URL properties of the link without binding to the data.
• Data Text Field and Data URL Field - Specified the field in the results set to bind to either the Text or URL property of the link.
• Data Text Field Table and Data URL Field Table - These should be specified if either the Data Text Field or Data URL Field specify an ambiguous column name (a column name that appears in two linked tables).
• Data Text Format and Data URL Format - Use these to format the Text or URL of the link.
• Target - This is the target html propery of the link - e.g. which window the link should open in. Set to '_blank' for a new window.

Paging

Moving on, we look at the possibilities of paging:

QuickAdmin looks after the paging for you; all you need to do is specify what sort of paging you want. Here, we've specified paging by Page Numbers, showing the navigation buttons at the bottom of the page.

View Template

Finally, you need to create the View/Add/Edit part of QuickAdmin. This is the part where users get to view, change and add new records to the database. It is entirely up to you what the users are able to do to the database and you’ll see how easy it is. Firstly, here are all the controls you have to choose from:

To create the View template, there are a couple of important properties to be aware of. Each Edit control has an Addable and Editable property. If a Edit control is Addable, a value can be set in Add mode, otherwise the field is hidden. If a control is Editable, it will be editable in Edit mode, otherwise its value will just be displayed, but cannot be changed. Below is the finished View template.

Once you are done, select the Template, Right-click and choose End Template Editing. Right-clicking on the Admin panel now will enable you to Show and Hide the Advanced Search controls, change the rendering mode from Search to Results, View, Add and Edit, depending on your settings. When you are happy, right-click on the page and select View in Browser – you will now have a fully working QuickAdmin page for the Products table of the IBuySpy Store.

DataLists

DataLists are remarkably easy to use but very powerful. Provided with your Install are some IBuySpy Demos. If you haven’t installed this Demo from Microsoft it is well worth it; if you don’t want to install it, follow this walk-through anyway, as it is not crucial to actually complete this demo to understand how these controls work.

Right Click on your QuickAdmin panel in design-mode and click Edit Template – Data.

You will be presented with an Empty Template. Drag the DataLists control from the toolbox (left) into the Empty Template. You will be presented with something resembling the right image.

As it says on the DataLists control, you must add Items to this Controls Collection by clicking on the Items | (Collection) row in the properties window and clicking the button which appears on the right. You will be presented with a dialog box. Click on the Add Button and it will look something like the image below:

In the case of the Reviews page from the IBuySpy Store, based on an integer value coming out of the database in the range 1 to 5, we want to display a graphic. We need to therefore create 5 different ListItems. Firstly, we add a value of 1 to the Value field. We then add an image tag:
<img src='http://www.ibuyspystore.com/images/ReviewRating1.gif' border=0> into the Text Field.

This is repeated for all the rest of the values between 1 and 5 with the text changing to …ReviewRatingx.gif…. where x is the Value in the database.

(Note – these images are being called from the Microsoft site. For faster download speed etc… it is probably best to reference a local copy. Also, you obviously won’t get the images if you are not connected to the internet)

Once you’ve added all the ListItems, click OK. Retuning to the Design page, you should now see your DataLists rendered differently:

Finally, to use your DataLists, you can use any control which has the DataSet property. Just set the DataSet to equal the ID of your DataLists. This can be done in the ResultsColumn, Edit Control and Search Controls.

(Note – here we have used html, which will render onto the page and call the image from the IBuySpy site. If you try and use an EditDropDownList or LocalDropDownList you’ll see that the text will appear in the dropdown and not the image as ‘text’ is treated as text. An EditRadioList or LocalCheckBoxList will work as it is written directly to the page and is parsed as html. If it was crucial to use a DropDown, simply setting the text of value 5 to be ‘Fantastic’, value 1 to be ‘Awful’ would work fine.)

Foreign Group Fields

On occasion, you want to create links between two tables – in this demo of the Territories page in the Northwind database, each territory has at least one Employee assigned to it. When you create a link between the two tables you can either choose to display the Employees FirstName or LastName, but not both. This becomes a major dilemma if you have more than few employees. You’re bound to get multiples of Jones’ or Smith’s or James’. This is where the ForeignGroupFields come in. Firstly, look at the Many2ManyCheckBoxList in the Advanced Controls on the Territories page. You only get the surname. Right Click and open the Property Builder. You should be presented with the dialog below:

The way the Foreign Group Fields are used here are this: "SELECT ForeignGroupFields AS ForeignDataField, ForeignKeyField FROM ForeignTable". Even if you don’t know SQL, you can see how useful this is. You could put not only the FirstName and LastName in, but their unique ID, age, position, title – anything in the Employees Table. Your only consideration is that the ForeignDataField is not the name of any other field. Below is how the above dialog can be changed to produce their Full Name in a EditListBox, Many2ManyCheckBoxList etc...