In the Dovetail SDK, there is a very useful SqlHelper class. SqlHelper provides a convenience and data provider-agnostic wrapper around ADO.NET. This allows an application to execute SQL commands in a database.
SqlHelper has various ways of being initialized, which all depend on a DbProvider instance to make a connection to the database. In most applications the DbProvider is separately instantiated based on configuration, so the examples here will not detail that process.
SqlHelper can execute a variety of SQL commands. SQL SELECT statements are usually performed by the ExecuteDataSet method. The ExecuteNonQuery method can be used to perform other SQL commands such as UPDATE or DELETE statements. ExecuteScalar is used frequently to get a value returned from a SQL statement for use later in the application.
In the first example, a simple database select statement is being executed to retrieve an ADO dataset. The dataset is then processed, writing the value from each of the two columns for each row in the first table in the dataset to the console. If no rows were retrieved, then nothing would be written to the console.
public void SqlHelper_example()
{
var sqlCommand = "select title, id_number from table_subcase";
var sqlHelper = new SqlHelper(_dbProvider, sqlCommand);
var dataset = sqlHelper.ExecuteDataSet();
var table = dataset.Tables[0];
foreach (DataRow row in table.Rows)
{
var subcaseInfo = String.Format("Subcase {0}: {1}", row["id_number"], row["title"]);
Console.WriteLine(subcaseInfo);
}
}
In this example, a database DELETE statement is being executed using a static SqlHelper instance to delete rows from the notes_log table.
public void SqlHelper_example()
{
SqlHelper.ExecuteNonQuery(“DELETE from table_notes_log WHERE internal = ‘-666’”);
}
Finally, here is an example using ExecuteScalar. The SQL SELECT statement will return the requested identifier, which is converted to an integer.
public void SqlHelper_example()
{
int caseObjid = Convert.ToInt32(SqlHelper.ExecuteScalar(“SELECT objid from table_case WHERE id_number = ‘1’”));
}
In reality, these examples just scratched the surface of possibilities for the uses of SqlHelper. The online documentation for SqlHelper provides a list if all the available methods, and putting them to use in applications gets easier with experience, and good examples.
Please add other examples that you have implemented in your comments, and we can all discuss!
The abilities to have global users and a centralized server is an important feature of Dovetail Agent. Making localized date handling for all of the users is an important part of getting the right date format displayed to the users, and making sure the dates are stored correctly in the database.
Dovetail Agent recognizes the computer and browser settings for each user. Dovetail Agent allows each user to input dates in their localized date format, and relies on Dovetail SDK to convert the dates into a consistent format for storage in the database.
Regional Options
From the Control Panel for each client, the Regional Options tab of the Regional and Language Options has a big impact on Dovetail Agent functionality. Dovetail Agent uses the standard Visual Basic FormatDate routine, which uses the Regional settings to determine the client's preferred date format.
For this discussion, the client is located in the Bristol, England office. His regional setting is set to English (United Kingdom).
Language Preferences
More importantly though, in order for Dovetail SDK to recognize the localized date formats and make the necessary adjustments, the browser needs to have the Language setting in IE correspond to the user's Regional Options setting.
From the Tools Menu in Internet Explorer, click Internet Options, then click on the Languages button to open the Language Preference dialog.
Since our user is located in the Bristol, England office, the English (United Kingdom) [en-GB] needs to be selected. If is is not displayed, click the Add… button and select it from the list. Once it is shown, move the English (United Kingdom) [en-GB] line to the top of the list by clicking the row and using the Move Up button.
Verifying in Dovetail Agent
Have the user log in to Dovetail Agent, and open the About Dovetail Agent page from the Profile menu. The Current Culture is displayed at the bottom of the page, and should now show en-GB:
After these configuration changes are made, dates throughout Dovetail Agent are displayed to the UK user in DD/MM/YYY format, but saved to the database in MM/DD/YYYY format.
Summary
Having dates localized is important for ease of use for the users, and is pretty easy to set up.
jQuery just released its latest version, and celebrates its 3rd birthday as a JavaScript Library.
The big features of this release are:
- Sizzle: A sizzlin’ hot CSS selector engine.
- Live Events: Event delegation with a jQuery twist.
- jQuery Event Overhaul: Completely rewired to simplify event handling.
- HTML Injection Rewrite: Lightning-fast HTML appending.
- Offset Rewrite: Super-quick position calculation.
- No More Browser Sniffing: Using feature detection to help jQuery last for many more years to come.
jQuery at Dovetail Software
Almost all of our current development projects are using jQuery, so upgrading to the new version to take advantage of the new features and performance will be an exciting improvement. The performance gains in the selector engine and HTML injection should help us improve our software. The benefits of using jQuery is already apparent, and we continue to use it more every day in development and maintenance of our software.
jQuery API browsers
One of the best development aids for jQuery is Visual jQuery. It shows the syntax and definition for every jQuery method available, in a very easy-to-use interface. Remy Sharp just released a new jQuery API browser, and this one is available offline as well as an Adobe AIR application.
Download and install the AIR API browser
The new API browser includes the following features:
- All the latest jQuery and jQuery UI documentation
- The ability to mark pages as favorites for those pages you keep wanting to return to
- Syntax highlighting in the code examples
- Live running of examples within the browser
- Links to edit and experiment with the code examples
This is always a good sign for a software company, and here at Dovetail Software this has just become an easier task.
It is important for us to get the news about a new product release to our customers. By automating some of the steps in this task, we can get the information to the customer faster, and also add some business value to the process.
Now when a new release is ready, we can use the new Product Announcement tool to prepare the message for our customers.
Opening this application from within our in-house version of Dovetail Agent, we can see a list of all of our products that are currently under contract with any of our customers.
Clicking on one of the products will retrieve all of the customers under contract, and show a second grid filled with the contact information for those sites. Each contact that has an email address will be selected, and those without an email address are highlighted differently.
The contact information can be updated by double-clicking the contact's row in the grid. Once the changes are saved for the contact, then the contact grid can be refreshed by clicking on the product again.
Each available email address is added to the BCC list, and the product details are filled into the subject and email body text. All of these text areas can be changed before the email is sent.
The body of the email is stored in the database, so the default template can be changed from within Dovetail Agent, instead of having to change to source code of the Product Announcement application.
When all of the changes are complete, the email can be sent. We send the message using Outlook, so clicking the Send Email button is the last step. It really makes it easy for us to get the word out to our customers.
Inside the Code
Inside the source code there a few interesting things that show how our applications are evolving as we develop new applications and features in our software.
The Product Announcement application has a built-in Help Page, accessible by clicking on the Help link at the top of the page. We use JavaScript to show the help contents directly in the page, so the focus of the user is not taken somewhere else.
Table Sorting
Both of the grids use the jQuery tableSorter plugin. This is a very simple way to add sorting for all of the columns in the grid. Here is the code that adds sorting for the customer grid, which adds the option of making the first column non-sortable.
$(".tablesorter").tablesorter({
headers: {
0: {
sorter: false
}
},
sortList: [[1,0]]
});
Tracking Announcement Activity
When the emails are sent, we also add an activity log for each contact, and one for the product itself. This allows us to track when the announcements were sent to the customers. To do this we are using AJAX through jQuery to add these activity entries for each customer and part.
$.ajax({
url: "addActivityLogs.asp",
type: "POST",
data: "site_part_list=" + site_part_list + "&product=" + escape(product) + "&contact_list=" + contact_list,
error: function(xhr) {
alert("Failed to add Activity Logs for " + product);
}
});
The addActivityLogs.asp code that gets executed by the AJAX call loops through the contacts and site parts, and uses the Dovetail Software SDK to create the new records for the database.
Better Layout and Style
All of the HTML layout and styling for the Product Announcement application is done using CSS. This is very different from the previous versions of Dovetail Agent. Dovetail Agent and Dovetail Admin will both be improving in this area as well in future releases.
This makes it easier to develop new applications, and much easier to change when necessary.
Using Outlook
Creating and sending emails through Outlook is very easy to do. First, when the the Product Announcement application is opened, we establish a connection to Outlook.
var objSession = new ActiveXObject("MAPI.Session");
objSession.Logon("","",true,false);
To create and send the email, we create the new Message with the subject and text, add the recipients to the BCC list and send!
var objMessage = objSession.Outbox.Messages.Add;
var objRecipients = objMessage.Recipients;
objMessage.Subject = $("#txtSubject").val();
objMessage.Text = $("#txtBody").val();
var bcc = new String($("#txtBCC").val()).split(";");
for(var i=0; i < bcc.length; i++) {
email = bcc
;
if(email > "") {
objRecip = objMessage.Recipients.Add();
objRecip.Name = email;
objRecip.Type = 3;
objRecip.Resolve();
}
}
// Send the Message
try{
objMessage.Send();
} catch (e){
objMessage = null;
objRecipients = null;
alert("Unexpected error:\n\n" + e.description + "\nError Number: " + e.number);
return false;
}
$("#sendButton").attr("disabled", true);
alert("Email successfully sent.");
You may notice that we use jQuery to interact with the page. This simplifies the JavaScript code required to get the subject, body, and recipients. We also disable the Send Email button after completion.
Summary
As we develop new applications, we also improve our techniques. Hopefully these examples will help you improve your techniques as well. Comments/Ideas/Suggestions are always welcome.
I am currently working on a project where I need to take a database from empty to usable with a script file. I am at the point where I need to import data files, and the data files I need to import don't exist. The customer has the data, and luckily they have a tool to create nice, tidy data files for me - Dovetail Software's Data Import Export Tool (DIET).
The challenge for me is to make it easy for them to use DIET, so I can get the data files that I need.
DIET is the perfect tool for the job, because it is designed to do exactly what I need. I need data from three related tables in the database for this file, so I created a directives file to specifically control what information to extract.
User-Defined Lists are one of the most challenging data structures within the database, but the directives file was pretty easy to build (with a little help from Gary Sherman). Here is the end result, which I will explain below:
EXPORT OBJECT hgbst_lst
UNIQUE_FIELD = "title"
ACTIONS = EXPORT, DELETE
TO hgbst_show THROUGH hgbst_lst2hgbst_show
ACTIONS = EXPORT, DELETE
TO hgbst_elm THROUGH hgbst_show2hgbst_elm
ACTIONS = EXPORT, DELETE
END_TO
TO hgbst_show THROUGH prnt_chld2hgbst_show
ACTIONS = EXPORT, DELETE
TO hgbst_elm THROUGH hgbst_show2hgbst_elm
ACTIONS = EXPORT, DELETE
END_TO
TO hgbst_show THROUGH prnt_chld2hgbst_show
ACTIONS = EXPORT, DELETE
TO hgbst_elm THROUGH hgbst_show2hgbst_elm
ACTIONS = EXPORT, DELETE
END_TO
TO hgbst_show THROUGH prnt_chld2hgbst_show
ACTIONS = EXPORT, DELETE
TO hgbst_elm THROUGH hgbst_show2hgbst_elm
ACTIONS = EXPORT, DELETE
END_TO
TO hgbst_show THROUGH prnt_chld2hgbst_show
ACTIONS = EXPORT, DELETE
TO hgbst_elm THROUGH hgbst_show2hgbst_elm
ACTIONS = EXPORT, DELETE
END_TO
END_TO
END_TO
END_TO
END_TO
END_TO;
At the top level, I want to export data from the hgbst_lst table, and then also export any related data from the hgbst_show and hgbst_elm tables at the lower levels. Since I want to be able to extract this data from one database and import it into a different database, I avoided using any unique record identifiers (objid's). Using "title" as the unique identifier for the hgbst_lst table prevents multiple copies from being imported into the database.
Each lower level is reached by the "TO <table> THROUGH <relation>" phrases, and that traverses through the parent-child relations that make up the user-defined list structure.
The batch file that I set up and used for this data extraction is the other key piece of the puzzle. Here it is, and again the explanation follows:
diet -user_name <USERNAME> -password <PASSWORD> -db_name <DATABASE> -license <LICENSE> -directive hgbst.dir -archive -export hgbst.dat -where "title IN ('WORKGROUP', 'Recipient')"
This batch file is built for an Oracle database. The command line options that were key were:
- -directive hgbst.dir - contains the directives file described above
- -archive - copies the data from the database but does not remove it
- -export hgbst.dat - specifies the file where the output data is written
- -where "title IN ('Msg Reason', 'Recipient', 'Reimbursement_addr')" - controls which lists are exported
The other command line options control the database connection. Each one has to have a valid value, but otherwise they are self-explanatory.
Here is a sample of the data that gets created:
OBJECT TYPE="hgbst_lst", NAME="hgbst_lst_268435457"
UNIQUE_FIELD=title
FIELDS
title = "WORKGROUP";
description = "Employee's work group designation";
deletable = 1;
END_FIELDS
RELATIONS
TO_NAME="hgbst_show_268435457" REL="hgbst_lst2hgbst_show";
END_RELATIONS
END_OBJECT NAME="hgbst_lst_268435457"
OBJECT TYPE="hgbst_show", NAME="hgbst_show_268435457"
FIELDS
last_mod_time = DATE "?/?/? ?:?:?";
title = "Level 1";
END_FIELDS
RELATIONS
TO_NAME="hgbst_elm_268435462" REL="hgbst_show2hgbst_elm";
TO_NAME="hgbst_elm_268435461" REL="hgbst_show2hgbst_elm";
TO_NAME="hgbst_elm_268435460" REL="hgbst_show2hgbst_elm";
TO_NAME="hgbst_elm_268435459" REL="hgbst_show2hgbst_elm";
TO_NAME="hgbst_elm_268435458" REL="hgbst_show2hgbst_elm";
TO_NAME="hgbst_elm_268435457" REL="hgbst_show2hgbst_elm";
END_RELATIONS
END_OBJECT NAME="hgbst_show_268435457"
OBJECT TYPE="hgbst_elm", NAME="hgbst_elm_268435457"
FIELDS
title = "Call Administrator";
state = "Default";
END_FIELDS
RELATIONS
TO_NAME="hgbst_show_268435457" REL="hgbst_elm2hgbst_show";
END_RELATIONS
END_OBJECT NAME="hgbst_elm_268435457"
Hopefully this will help get the data that you need out of your database - DIET can definitely do the job.
Overview
One of the new features coming soon in Dovetail Agent 4.1 is the enhanced capability of using parameters in fcQuery that will be evaluated when a query is executed. This allows queries to be designed with much more flexible structure.
Dates are a very important parameter to be evaluated at run-time, and being able to build the query to do this automatically keeps the user from having to enter the date manually when running the query. Queries can be created to look at specific date-oriented information (i.e. This Week) without having to change to values of the date parameters each time the query is run.
Shared queries are also a very easy place to see where run-time evaluation of a query parameter will be helpful. For example, if a query is created (and shared to all users) to find all Cases for the current case owner, then any user that runs that query will find their cases without having to provide their login-name themselves. When the query is run, the current user's login name (i.e. My Login Name) is matched against each case owner field.
Required Changes
There were a some changes that were necessary to implement this feature:
The fcQuery Edit form was modified to allow the run-time parameters to be used. In this release of Dovetail Agent there are run-time parameters for date comparisons and current user comparisons.
The SQL that is executed for a query is now evaluated every time it is run. Previously the SQL was built when the query was created or modified, and only regenerated at run-time if the query had changeable parameters. This also guarantees that if the base object was changed without updating the query itself, the resulting SQL that gets executed is current.
Defining the Parameters
The parameters that are set up to be used in the queries are currently defined within the source code of Dovetail Agent. The source code is contained in fcQueryVariables.js, and it can be customized by updating that file. Each parameter is built and evaluated based on the following properties:
Name - the visible name of parameter that is used on the fcQuery Edit page (i.e. My Login Name)
Value - how the parameter is referred to within the application itself (i.e. MyLoginName)
Operation - how the parameter is used in an expression when it is evaluated (i.e. "is equal to")
Expansion - indicates the run-time data source the parameter is evaluated against (i.e. FCSession('user.login_name'))
DataType - the data type of the table columns where the parameter will be available (i.e. "string")
The following parameters are currently defined for use in Dovetail Agent:
My Login Name - the login name of the user running the query
My First Name - the employee first name of the user running the query
My Last Name - the employee last name of the user running the query
Current Date/Time - the data and time the query is run - used to find any date Before the current time
This Year - this evaluates to January 1 of the current year (12:00 AM) - used to find any date Since the start of the current year
This Quarter - this evaluates to 1st day of the first month of the current quarter (12:00 AM) - used to find any date Since the start of this quarter
This Month - this evaluates to 1st day of this month (12:00 AM) - used to find any date Since the start of this month
This Week - this evaluates to Sunday day of this month (12:00 AM) - used to find any date Since the start of this week
Last Month - this evaluates to all of last month - used to find any date During the month prior to the current month
Last Week - this evaluates to all of last week (Sunday thru Saturday) - used to find any date During last week
Evaluating the Parameters
All of the user parameters are evaluated against the current Session object that is created during the login process. The user login_name and employee first and last name for the user are both properties of the session object, so that information is easily accessed at run-time.
All of the date parameters are evaluated at run time against the server clock. The current date is retrieved from the system clock, and then the proper date comparisons are built based on the criteria defined for the parameter. The SQL that is generated allows for SQL server and Oracle differences.
Implementing the New Feature
Once you have installed or upgraded to Dovetail Agent 4.1, you can get started with run-time parameters. The best way to understand this feature is to add or modify a query and put it to use. Any fcQuery object that has a date property can be used to get familiar with the run-time parameters. On the fcQuery Edit form, just select the date column, and the Substitution Values will be shown in a new drop-down list for selection. When one of the values is chosen from the list, the operator gets set to the correct option, and the clause can be added to the query. Finish the edits and save the changes, then use the Find button to see the results.
Summary
This new feature will be very useful when building queries for time-sensitive analysis. In previous versions, this type of query required the use of changeable properties, which had to be altered manually every time the query was executed. Now, the query can be built using the run-time evaluation parameters, and the targeted information will always included correctly without user intervention.
This will also be helpful when building queries to be shared with other users. The current user parameters can be used in the query, and any user that runs the query will see their own data without having to type in their login name when they run the query.
As part of the continuous effort to enhance and improve our applications, we welcome any comments or suggestions about this new feature.
As part of the continuous effort to enhance and improve our applications, a new Spell Check feature was added for the next release of Dovetail Agent.
This feature was added for every text area input in the pages of Agent. Currently there are almost 100 different pages in Dovetail Agent that use text area controls. Adding the new functionality to every page individually would be one way of implementing the change, but certainly not the most efficient. Instead, the new functionality is added dynamically to each page as it is opened in the browser. This approach makes a lot of sense. Since the code that adds the link under each text area is contained in one file, any future modifications will be very easy as well.
The Spell Check feature is made available to the user by adding a Check Spelling link under each text area control. jQuery is used to make this addition dynamically on each page that has a text area control, and will only add the link under a text area that is not "disabled" or marked as "Read-Only". Adding the jQuery module dynamically to every page also opens up a lot of other changes that are easier to implement.
On each page in Dovetail Agent where an entry has been made into a text area, clicking on the Check Spelling link will activate the Spell Check process. This process will analyze the contents of the text area, and if corrections are suggested the Spell Check window will be opened. The user then has the tools to correct the spelling as necessary, and any changes made will be reflected in the contents of the text area when the Spell Check window is closed.
If no other corrections are suggested, then the Check Spelling link changes to inform the user that the Spell Check is complete, as shown to the right. Changing the contents of the text area will revert the Check Spelling link back to its original state, ready to check the spelling again.
In Dovetail Agent, it is very easy to build a query against a table in the database, and there are standard routines to open most of the table objects to view the items returned in the query. Drilling down to view objects is a little tougher, but it can be done in a few easy steps.
In this example, I will show you how to build a query object for the queelm_case view, which will allow us to open each of the cases in the queue for inquiry. Building the fcQuery object in Dovetail Admin is the first step, so follow along.
After logging in to Dovetail Admin, click on the fcQuery Objects menu link to open the Manage fcQuery Objects page. Typing in queelm_case and clicking the List button locates the view object, and double-clicking the queelm_case row will add the object to the list of fcQuery objects for editing. This screen-shot to the right shows what the screen looks like after this step.
For a view, it is important to indicate what javascript function will be used to open the base object of the view. For this view it is easiest to use the OpenCaseWindow function, which is located in the code/open_window.js file in Dovetail Agent. To add this functionality, click on the queelm_case row in the grid, enter OpenCaseWindow('$objid$'); in the Open Function text box, and click the Save button.
The next step is to open the queelm_case object from the grid and add the columns that need to be shown in the query results. Click on the queelm_case row in the grid, then click on the Open button.
The most important field to add in this example is the elm_objid field. This field is what maps to the unique identifier of the case record, known as its objid. The tricky part is to make sure it gets indicated as the Record Id field when it is added as a column.
Adding the other columns follows the normal process, choosing the field by clicking the Path button and finding the desired field, picking the field by double-clicking it, then using the Quick Add button or setting the properties and clicking the Add button.
I chose to add a few other columns from the view regarding the cases, and the final fcQuery Object Columns are shown to the right.
Executing a query based on this object in Dovetail Agent will give me a list of all cases that are currently in queues, and when I double-click on a row in the results window the Case window will be opened so I can view all of the details of the case.
Without specifying the Record Id and Open Function in Dovetail Admin the view does not have enough information to know how to present the detailed view of the case.
Query objects provide a lot of power to Dovetail Agent users, and being able to get the most out of each query helps improve the efficiency of your applications.
I came across this code today, and it took me a second to figure out what it was doing:
strSortOrder = document.getElementById("ad").options[document.getElementById("ad").selectedIndex].value;
To break it down, it locates a Select control on the page (FindElementById), finds the element again and gets its selected value's index (selectedIndex), and sets the variable to the value of the option at the specified index (options[index].value).
There is a jQuery plugin that really simplifies this process. Using jquery.selectboxes.js, the code now is as simple as this:
strSortOrder = $("#ad").selectedValues();
This code now does the same thing, but it is also easy to read, and understand. It shortened from 86 characters to 26, and now requires no explanation.
There are many different cases where jQuery can simplify the code.
Dovetail Software, Inc. recently recently received a request from one of our customers to make Queue Membership Management easier and more efficient to use. The screen image to the left shows the Manage Queues form in Dovetail Admin, Version 2.5. When a queue is selected at the top of the form, its current members and supervisors are displayed at the bottom right of the Membership tab.
Adding or changing members and supervisors was done one by one, and with only the login name for each user to determine the identity of each user. The login names are unique, but it can be hard to tell users apart based on login name alone.
The new requested functionality to make queue management easier includes adding the first and last name for each user, and allowing multiple users to be added or removed at one time.
The new version of the screen shows the changes. There is now more information for each user, and the checkboxes on each row of the grids give an indication that multiple users can be moved at the same time.
Users are selected by clicking on each row in the grids. The arrow keys in the middle of the tab are still used to add the selected users to the desired groups, and all of the business rules still apply to each one of the selected users.
From the administrator's point of view, the forms don't look much different other than the first and last names, but the added functionality will make member management easier. That was the real goal.
The other interesting part is what it looks like from a developer's point of view. Changing the functionality of the forms is an opportunity to update the technology, and in this case it makes a big difference. For more details on the development and a look at some of the code, take a look behind the user interface and read this post.
