Microsoft SQL Server 2000
The SQL Server 2000 relational database engine is designed for highly scalable and reliable data storage. Figure 5.1 shows an overview of the component structure of SQL Server 2000. The database engine stores data in tables that represent an object of interest to the developer. Each table contains a schema that is modeled with columns and rows that represent a single occurrence of the object modeled by the table.
Figure 5.1: The SQL Server 2000 architecture.
Applications submit either SQL statements or XPATH queries to the database engine that returns the results. SQL statements are based on the Transact-SQL (TSQL) dialect and return a tabular row set. TSQL is a sophisticated dialect of SQL that has a variety of enhanced features that are extensions to ANSI SQL 92. All database activities are done through a set of commands defined within TSQL. XPATH queries are first converted to TSQL and then converted back to a standard XML document before display to the user .
An SQL Server 2000 database consists of a collection of tables that store a specific set of structured data. Tables contain a collection of rows and records, and each column in the table is designed to store certain types of information based on the defined schema. Tables have a variety of controls defined within their schema that the database designer can customize to ensure the data s business validity. Tables can also contain indexes that allow for quick retrieval of row information.
Declarative referential integrity (DRI) constraints are added to the table to ensure that interrelated data in different tables remains constant. A database can also store procedures that use the TSQL programming language to perform operations on the data. This includes storing views and inserting, updating, and deleting data within tables.
Database Design Considerations
When the developer is designing a database application, it is important to understand the exact business functions needed as well as the database concepts and features used for their representation. Database changes can become expensive from both a resource and time perspective. They often require a migration of the data to the new format and a re-design of applications and forms (like InfoPath) that are bound to the database. When designing a database application, architects and developers should keep the following things in mind:
-
The purpose of the database and how this will affect the design
-
Database normalization rules that prevent mistakes in database design
-
The protection of data integrity and object relationships
-
Specific application and database security requirements
-
Performance needs of the applications
-
Maintenance requirements
-
The estimated size and hardware configuration of the database
CD-ROM With these things in mind, let s create a database named CompanyInfo to manage the data within a company. (The database script can be found on the companion CD-ROM in \Code\Chapter 5\SampleDataBaseScript\Sample.sql.) This database contains a table named Customers that contains information about the company s current customers and their main point of contact. This table contains columns named CustomerID, CustomerName, CustomerPhone, CustomerEmail, and CustomerContact. In order to ensure that no two customers share the same CustomerID, we add a constraint to the table. We also define a set of indexes because it is important for the business to quickly locate customer information based on their ID or name . Using the TSQL language, we define this schema structure with Listing 5.1, a query run from the SQL Server query analyzer.
 |
CREATE TABLE [dbo].[Customers] ( [CustID] [int] IDENTITY (1, 1) NOT FOR REPLICATION NOT NULL , [CustomerName] [varchar] (75) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL , [CustomerPhone] [varchar] (12) COLLATE SQL_Latin1_General_CP1_CI_AS NULL , [CustomerEmail] [varchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL , [CustomerContact] [varchar] (75) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ) ON [PRIMARY] GO
| |
SQL Server can have as many as two billion rows per database and 1,024 columns per table. The number of rows and total size of the tables are limited only by the available storage. The maximum number of bytes per row is 8,060. Statements that are used to alter or create a database structure are part of the Data Definition Language (DDL). This is a subset of the TSQL dialect and is part of the database management system that is used to define all attributes and properties of a database. This language defines a set of constructs that include the CREATE TABLE statement to define all attributes and properties of the database, including row layout, column definitions, key columns, file location, and even storage strategy.
InfoPath and Database Connectivity
Connecting to SQL Server and Microsoft Access databases is done through the same design process that was used for Web Services and XML. The New option from the data source link launches the Data Source Wizard, which allows you to either select or create a database connection object. Table 5.3 shows the Data Source Wizard s common underlying set of services that enable InfoPath to connect with external data sources.
| Connection Type | Adapter Used |
|---|---|
| XML Schema or XML Data File | XMLFileAdapter |
| Database (Microsoft SQL Server | ADOAdapter or Microsoft Access Only) |
| Web Service | WebServiceAdapter |
Database connectivity is provided through either the ActiveX Data Object or an ODBC DSN connection. Both of these provide a connection string to the underlying data source. The main difference is that the ADO connection object is optimized for both SQL and Microsoft Access and is the preferred method for database connectivity. The Data Source Wizard creates a reusable file that is stored in the user s My Data Sources folder and contains all the information needed to connect with the database.
The additional benefit of these connection files is that these files become a shared connection that is common to all Microsoft Office System 2003 applications. The Data Connection Wizard steps the user through the process of connecting to an OLE DB data source and persisting the connection into the Data Connection file. This wizard can also edit the connection information in existing data sources in case they change. The metadata stored within these files enables users to search for connections based on their descriptive data.
The Data Connection Wizard also provides access to an additional kind of common data source called the data retrieval service when you re connecting through Microsoft Excel 2003 and Office 2003 Web components . A data retrieval service is a Web Service installed on a WSS server used for connecting to and retrieving data. When the data retrieval service is used this way, the client application sends an HTTP request to the data retrieval service on WSS that is returned as an XML document containing the requested data. In addition to databases, WSS contains a data retrieval service for connecting to data in Sharepoint lists.
When a form that is connected to a database is designed, we encounter the same restrictions as apply to a Web Service. InfoPath builds a data structure based on the database, and the resulting form can be used to submit data and query the existing database. Just as with a Web Service, the underlying data source matches the database and the existing fields or groups can t be modified except at the root group in the data source.
Defining the Data Source
When a new database connection is defined within Office, an Office Database Connection (.ODC) file is created. This file contains the reusable connection information that is used to connect to the OLE DB provider used to obtain the data within a database. When we use a data source created for the MyCompany database and point to an invoice file, all the information needed to connect to the data source is maintained within the HTML file created during the creation of the connection. Listing 5.2 shows the connection file to the MyCompany database.
| |
<html> <head> <meta http-equiv=Content-Type content="text/x-ms-odc; charset=utf-8"> <meta name=ProgId content=ODC.Table> <meta name=SourceType content=OLEDB> <meta name=Catalog content=CompanyInfo> <meta name=Schema content=dbo> <meta name=Table content=Invoice> <xml id=docprops></xml><xml id=msodc><odc:OfficeDataConnection xmlns:odc="urn:schemas-microsoft-com:office:odc" xmlns="http://www.w3.org/TR/REC-html40"> <odc:Connection odc:Type="OLEDB"> <odc:ConnectionString>Provider=SQLOLEDB.1;Integrated Security=SSPI;Persist Security Info=True;Data Source=ThomsDev;Use Procedure for Prepare=1;Auto Translate=True;Packet Size=4096;Workstation ID=THOMSDEV;Use Encryption for Data=False;Tag with column collation when possible=False;Initial Catalog=CompanyInfo</odc:ConnectionString> <odc:CommandType>Table</odc:CommandType> <odc:CommandText>"CompanyInfo"."dbo"."Invoice& quot;</odc:CommandText> </odc:Connection> </odc:OfficeDataConnection> </xml> <style> <!-- .ODCDataSource { behavior: url(dataconn.htc); } --> </style> </head> <body onload='init()' scroll=no leftmargin=0 topmargin=0 rightmargin=0 style='border: 0px'> <table style='border: solid 1px threedface; height: 100%; width: 100%' cellpadding=0 cellspacing=0 width='100%'> <tr> <td id=tdName style='font-family:arial; font-size:medium; padding: 3px; background-color: threedface'> </td> <td id=tdTableDropdown style='padding: 3px; background-color: threedface; vertical-align: top; padding-bottom: 3px'> </td> </tr> <tr> <td id=tdDesc colspan='2' style='border-bottom: 1px threedshadow solid; font-family: Arial; font-size: 1pt; padding: 2px; background- color: threedface'> </td> </tr> <tr> <td colspan='2' style='height: 100%; padding-bottom: 4px; border- top: 1px threedhighlight solid;'> <div id='pt' style='height: 100%' class='ODCDataSource'></div> </td> </tr> </table> <script language='javascript'> function init() { var sName, sDescription; var i, j; try { sName = unescape(location.href) i = sName.lastIndexOf(".") if (i>=0) { sName = sName.substring(1, i); } i = sName.lastIndexOf("/") if (i>=0) { sName = sName.substring(i+1, sName.length); } document.title = sName; document.getElementById("tdName").innerText = sName; sDescription = document.getElementById("docprops").innerHTML; i = sDescription.indexOf("escription>") if (i>=0) { j = sDescription.indexOf("escription>", i + 11); } if (i>=0 && j >= 0) { j = sDescription.lastIndexOf("</", j); if (j>=0) { sDescription = sDescription.substring(i+11, j); if (sDescription != "") { document.getElementById("tdDesc").style.fontSize="x-small"; document.getElementById("tdDesc").innerHTML = sDescription; } } } } catch(e) { } } </script> </body> </html> | |
Once created, this connection can be used by InfoPath as well as opened within a Web browser and rendered through an Excel Web Component, as shown in Figure 5.2.
Figure 5.2: The ODC viewed through a Web browser.
The ADOAdapter is actually a subset of the OLE DB provider and existed before the .NET Framework. With the release of the Framework, this has become part of the System.Data.OleDB namespace. OLE DB is a COM-based data access object that provides access to all types of data and even provides access to a disconnected data store. OLE DB sits between the ODBC layer and the application. Within traditional ASP applications, OLE DB was the way that data was retrieved using the recordset object. Since InfoPath is not currently built using managed code, the default provider types for database access are OLE DB and ADO.
OLE DB is the system-level programming interface for accessing data and is the underlying technology for ADO as well as the source of data for ADO.NET. OLE DB is an open standard for accessing all kinds of data, both relational and non- relational. Natively, InfoPath exposes only the Microsoft Access and SQL Server portions of the provider. However, using the scripting interface, you can access other types of data using the built-in provider. For native code-based applications, ADO provides the COM-based application-level interface for all OLE DB data providers.
ADO also supports some behaviors not exposed through ADO.NET, such as scrollable, server-side cursors. However, because server-side cursors require holding database resources, their use might have a significant negative impact on the performance and scalability of applications. In order to send ADO recordsets through corporate firewalls, COM marshalling ports need to be opened. This is an additional security requirement that Web Services don t require because their XML-based payload utilizes HTTP as the transport mechanism. In these scenarios, the preferred method of a recordset object is to persist the source ADO recordset into an XML format and transmit the text instead. On the receiving end, these records are then re-hydrated into an ADO recordset.
Database Queries
CD-ROM InfoPath enables access to the relational data stored in SQL Server through the ADOAdapter. By default, this adapter is enabled for both the read and update of the underlying data source. Using InfoPath, this can be restricted to a query only view. The CompanyInfo database contains an invoice table that contains the current outstanding invoices. (This example is included on the companion CD-ROM, in \Code\Chapter 5\InvoiceLookup\InvoiceLookup.xsn.) Once connected to the data source, InfoPath renders the underlying data source fields, as shown in Figure 5.3.
Figure 5.3: The tables available for query are shown.
Based on the selection of the data source fields, the underlying 
manifest.xsf solutions file maintains the generated SQL statement as part of the XML query element, as shown in Listing 5.3.
| |
<xsf:query> <xsf:adoAdapter connectionString="Provider=SQLOLEDB.1;Integrated Security=SSPI;Persist Security Info=True;Initial Catalog=CompanyInfo;Data Source=ThomsDev;Use Procedure for Prepare=1;Auto Translate=True;Packet Size=4096;Workstation ID=THOMSDEV;Use Encryption for Data=False;Tag with column collation when possible=False" commandText="select "InvoiceID","CustomerID","CustomerName&quo t;,"InvoiceNumber","InvoiceAmount" from "dbo"."Invoice" as "Invoice"" queryAllowed="yes" submitAllowed="no"></xsf:adoAdapter> </xsf:query>
| |
In this solution, the query view acts as the view. When a user opens the solution, this is the page he will use to enter either the Customer ID or Customer Name. This generates a round-trip query to the database using the stored SQL statement. Once the query is complete, the code in the internal.js script is executed, the view switch is activated, and the data entry query is rendered. All fields within a data source are updateable. Selecting a field as read only within the Properties dialog box restricts the input capabilities of the underlying data source, as shown in Figure 5.4
Figure 5.4: Defining a read-only field.
Defining an Expression
As part of the lookup, end users would like a total of the outstanding invoices owed by the customer. This can be done through code or using the expression box control. This control allows for you to add an XPATH expression to the current form. One of the benefits of the expression control is that the existing schema isn t modified. Expressions are designed using the current schema and standard XPATH syntax, as shown here:
sum(dfs:dataFields/d:Invoice/@InvoiceAmount)
The actual rendering of this field is maintained in the XSL that is used to render the view and is stored within the InfoPath solution file, as shown in Listing 5.4.
| |
<font size="2">Total Outstanding Invoices: <span class="xdExpressionBox xdDataBindingUI xdBehavior_Formatting" title="" tabIndex="-1" xd:datafmt=""number","numDigits:auto;negativeOrder:1;&qu ot;" xd:disableEditing="yes" xd:xctname="ExpressionBox" d:CtrlId="CTRL7" xd:binding="sum(dfs:dataFields/d:Invoice/@InvoiceAmount)" style="WIDTH: 50%"> <xsl:choose> <xsl:when test="function- available('xdFormatting:formatString')"> <xsl:value-of select="xdFormatting:formatString(sum(dfs:dataFields/d:Invoice/ @InvoiceAmount),"number","numDigits:auto;negativ eOrder:1;")"/> </xsl:when> <xsl:otherwise> <xsl:value-of select="sum(dfs:dataFields/d:Invoice/@InvoiceAmount)"/> </xsl:otherwise> </xsl:choose> </span> </font> | |
Based on the values of the Invoice Amount field, the form will calculate a total for these fields and display it in the expression box. The last things for this form to do are to turn off any type of update and to remove or update the InfoPath repeating sections. By default, all repeating sections can add or delete rows. Since this is a read-only form, there is no reason that users should be able to do anything other than view the invoices. You can ensure this by using the repeating table properties. Uncheck the option to allow users to add and remove rows, as shown in Figure 5.5.
Figure 5.5: Turning off the ability to add or delete table rows.
Secondary Data Sources
InfoPath forms can host multiple data sources. One of the uses of a secondary data source is to load values for lookups and populate drop-down lists. Within the Invoice lookup, one of the requirements is that the lookup provide a list of existing customers. Having this list available will make it easier for the person that is using the form to search on a known set of values. You can make the list available by changing the existing text boxes on the query form to drop-down list boxes. List boxes by default can contain a fixed set of values, or they can be attached to a secondary data source.
Using the Tools menu, create a secondary data source to the database. To constrain the values in the list box to a unique set, you just need to enter the following SQL statement:
select distinct "CustomerID","CustomerName" from "dbo"."Invoice" as "Invoice"
Secondary data sources are also maintained as part of the manifest.xsf and stored in the solution file, as shown in Listing 5.5.
| |
<xsf:dataObjects> <xsf:dataObject name="Invoice" schema="Invoice3.xsd" initOnLoad="yes"> <xsf:query> <xsf:Adoadapter connectionstring="provider=sqloledb.1;integrated security=sspi;persist security info=true;initial catalog=companyinfo;data source=thomsdev;use procedure for prepare=1;auto translate=true;packet size=4096;workstation id=thomsdev;use encryption for data=false;tag with column collation when possible=false" commandtext="select distinct "custoterid","customername" from "dbo"."invoice" as "invoice"" queryallowed="yes":@/xsf:adoadapter: </xsf:query> </xsf:dataObject>
| |
Once created, the secondary data source can then be bound to the form using the List Box Properties dialog box, as shown in Figure 5.6.
Figure 5.6: Assigning a secondary data source to a drop-down list box.
Executing Stored Procedures
Stored procedures are a precompiled collection of TSQL statements stored under a unique name and processed as a single unit. SQL Server supplies a variety of these that are used to manage and display information about the database and users. Stored procedures provided by SQL Server are called system stored procedures and are stored as part of the Master database. This database is responsible for controlling the operation of each instance of SQL Server. During the initial install of SQL Server, this database is installed automatically and keeps track of things like user accounts and remote servers. It is also responsible for tracking the ongoing processes, environmental variables , system error messages, and backups . One of the stored procedures maintained in this database is sp_who. This system stored procedure provides information about the current system users and processes, as shown in Figure 5.7.
Figure 5.7: Running the sp_who system stored procedure.
Executing a stored procedure is similar to executing a prepared statement except that stored procedures exist as a permanent compiled object in the database. Also, from a developer perspective, stored procedures hide the complexity of SQL statements from an application. Stored procedures also assist in providing consistent implementation logic across applications. The SQL statements and logic needed to perform common tasks can be designed, coded, and tested once in a stored procedure. Each application needing to perform that task can simply execute the stored procedure.
This offers not only a centralized set of business logic but also a single point of control for ensuring that business logic and rules are consistently enforced. Stored procedures also help to improve application performance. Each task implemented in the stored procedure is part of a single execution plan on a server. All of the execution and application logic is applied on the server before being returned to the client.
| Note | Every SQL query has one or more associated execution plans. An execution plan consists of one or more component operations. SQL Server 2000 determines the best plan based on a cost-based structure that is designed to provide the fastest response and lowest overhead. |
Stored Procedure Execution
CD-ROM The data source capabilities of InfoPath don t directly support the execution of stored procedures. The ADO Adapter object within the data source provides table-based access. Using scripts, we can extend the current Invoice Lookup solution to include the execution of a stored procedure that provides a total of all the outstanding invoices. (This example is included on the companion CD-ROM, in \Code\Chapter 5\InvoiceLookup\InvoiceLookup.xsn.)
Using the CompanyInfo database, create a new stored procedure that totals the InvoiceAmount field:
CREATE PROCEDURE SumOfInvoices AS Select Sum(InvoiceAmount) as TotalAmount from Invoice GO
The CREATE PROCEDURE statement is used to define a stored procedure. This is used by the SQL Query engine to determine the type of execution plan for a set of statements. Using an algorithm defined within SQL Server, the database engine determines if a new stored procedure matches an existing execution plan or if a new one should be defined. This reduces the need of pre-compiling all stored procedures and extending the execution plan reuse within SQL Server.
The SELECT statement uses the SUM keyword as a way of totaling all the values within the InvoiceAmount field. By default, these types of queries are designed to return blank row names . Using the AS keyword enables the stored procedure to identify a row name for the return values.
Secondary Data Source Objects
All data access within InfoPath is done through the data source object of the XDocument object. Executing the SumOfInvoices stored procedure uses this object. Create the secondary data source and then edit the SQL statement generated by the ADO Adapter, as shown in Figure 5.8.
Figure 5.8: Updating the SQL statement field in InfoPath.
Replace the generated SQL statement with a call to the stored procedure. Using the EXECUTE keyword runs the stored procedure using the predefined execution path . When the data source is saved, ignore the error, as shown in Figure 5.9.
Figure 5.9: Ignore this error message when using a stored procedure.
Based on the use of XML, the ADO Adapter attempts to return all executable queries in a tree view or set of hierarchical data. When you execute stored procedures, the data returned from the stored procedures is not initially returned in a properly formatted XML string until the execution of the form. This secondary data source simply provides an entry in the XDocument object model that can be used to programmatically access the stored procedure execution.
Extending InfoPath
Within InfoPath, add a button control to the data entry page. The button control is a script component that is maintained as part of the XSL style sheet rendered for the page. This control provides a single OnClick event when pressed. Within MSE, enter the script shown in Listing 5.6 into the OnClick event.
| |
function GetTotal::OnClick(eventObj) { // Write your code here //Set the command for the data source's Query Adapter Document.DataObjects("TotalInvoice").QueryAdapter.Command = "execute SumOfInvoices"; XDocument.DataObjects("TotalInvoice").Query(); //Set the "SelectNamespaces" property so we can do selectNodes calls on the DOM XDocument.DataObjects("TotalInvoice").DOM.setProperty("SelectionName spaces",'xmlns:dfs="_ http://schemas.microsoft.com/office/infopath/2003/dataFormSolution" xmlns:d="http://schemas.microsoft.com/office/infopath/2003/ado/dataFi elds"'); //Get a reference to the total var objTotalAmount = XDocument.DataObjects("TotalInvoice").DOM.selectNodes("/dfs:myFields /dfs:dataFields/d:row"); // Display the value XDocument.UI.Alert("Total Customer Invoices: $" + objTotalAmount.item(0).selectSingleNode( "@TotalAmount" ).text) } | |
This function accesses the secondary data source created earlier in this chapter, within the XDocument DataObjects and returns the stored procedure values. The actual XML being returned by SQL Server is in the following format:
<dfs:myFields xmlns:dfs=\"http://schemas.microsoft.com/office/infopath/2003/dataFormS olution\"> <dfs:dataFields> <d:row xmlns:d=\"http://schemas.microsoft.com/office/infopath/2003/ado/dataFie lds\" TotalAmount=\"3235.67\"/> </dfs:dataFields></dfs:myFields>
In order to access the SQL data returned as an XML structure, you need to define the SelectionNamespaces property. This property specifies a new set of XML namespaces used for an XPATH statement. When an XML document contains elements defined in an external namespace, this property specifies the namespaces to use for DOM navigation methods like SelectSingleNode .
EAN: 2147483647
Pages: 111