| In the first version of the Employee Locator, the employee information was generated in two separate steps. First we retrieved data from SQL Server and saved it as an XML file, and then we used an XSL file (employee1.xsl) to transform it into an HTML file. This approach can be used in many different scenarios, as long as the frequency of changes to the data is not high. In such cases, the XML file can be generated through a scheduled task that generates the XML file on a daily or weekly basis, depending on the frequency of changes to data. Although this can be a valid and reasonable solution, it might not work well for cases when the data changes frequently. Probably a better approach for this scenario is to gain access directly to the SQL Server, and reflect all the updates as users view the data in the Web Part. Employee Locator version 2 will demonstrate how your Web Part can connect to the database to show the data in real-time. By changing just a few lines of code, we will be able to retrieve and present employee data in real-time. In this version, we will use ActiveX Data Objects to store XML in a stream instead of a physical XML file. Lets look at the differences between two versions of Employee Locator Web Parts: NOTE For more information about ActiveX Data Objects (ADO), you may want to look at http://msdn.microsoft.com/library/default.asp?url=/library/en-us/ado270/htm/mdmscsection1_ado.asp
The first difference is the way that employee data was stored. In version 1 of the Web Part, the data was stored in employee1.xml: rs.Save "employee1.xml", adPersistXML In version 2, the data was stored in a stream object and converted to a string: set oStream = CreateObject ("ADODB.STREAM") rs.Save oStream, adPersistXML strRst = oStream.ReadText(adReadAll) The second difference between the two Web Parts is the way that the XSL file is used for the transformation of the XML code. In both Web Parts, we have used the same XSL file, because the XML data is the same. This reveals another advantage of separation of data and user interface, which is, as you might have guessed, reusability. In the first version, we let the SharePoint dashboard factory take care of loading the XML file and applying the XSL. But in the second version, we have used the msxml DOM object for this purpose. ' Load the strRst string which has XML data into xmlBody DOM object Set xmlBody = CreateObject("MSXML2.domdocument") xmlBody.async = False xmlBody.LoadXML strRst ' Load the xsl file into xmlBody DOM object Set displayWeb = CreateObject("MSXML2.domdocument") displayWeb.async = False displayWeb.Load "e:\projects\SPSbook\employee1.xsl" ' Transform the XML into an HTML string strBody = xmlBody.transformNode(displayWeb) In the previous code, strBody has the final HTML form of employee data that must be presented in the Web Part.(Listing 15.5). Listing 15.5 The Complete VBScript Code for Retrieving Employee Data from SQL Server in XML Format into a String Const adPersistXML = 1 Const adUseClient = 3 Const adOpenKeyset = 1 Const adLockOptimistic = 3 Const adReadAll = -1 Dim strConnection Dim rs Dim strSQL Dim oStream Dim strRst Dim xmlBody Dim displayWeb Dim strBody strConnection = "Provider=SQLOLEDB;Data Source=dianatr;Initial Catalog=NorthWind;User  Id=sa;Password=;Connect Timeout=30;" strSQL = " select EmployeeID, LastName, FirstName, HomePhone, email from Employees " set rs = CreateObject ("ADODB.RECORDSET") rs.CursorLocation = adUseClient rs.Open strSQL, strConnection, adOpenKeyset, adLockOptimistic set oStream = CreateObject ("ADODB.STREAM") rs.Save oStream, adPersistXML strRst = oStream.ReadText(adReadAll) Set xmlBody = CreateObject("MSXML2.domdocument") xmlBody.async = False xmlBody.LoadXML strRst Set displayWeb = CreateObject("MSXML2.domdocument") displayWeb.async = False displayWeb.Load "e:\projects\SPSbook\employee1.xsl" strBody = xmlBody.transformNode(displayWeb) rs.close To create the second version of the Employee Locator Web Part, we follow the same steps mentioned in the "Date Web Part" section for creating a new Web Part. In the Advanced Settings section of Web Part - Settings, we select VBScript for the type of content, and then we create a function in the Embedded Content text area with three lines. Function getContent() 'Line 1 << Insert the code segment of Listing 15.5 here>> getContent = strBody ' Line 2 End Function 'Line 3 Finally, we copy the VBScript code shown in Listing 15.5 and paste it in between lines 1 and 2. Then we save the settings, and we are done with the second version of the Employee Locator Web Part. | 
