15.3. Working with Non-SharePoint Data Sources
When working with data sources in non-SharePoint Web sites, the following options are available in the Data Source Library task pane:
Local XML files
ASP.NET connections
The treeview appearance of the Data Source Library task pane allows for collapsing data sources in case the list of available data sources becomes long (which might become the case during extensive Web site development).
15.3.1. Local XML files
The Data Source Library is intelligent enough to list the existing XML files inside your Web site for you to use. As its name suggests, these XML files need to reside inside the Web site content location. If you want to use an XML file as the source of data for your Web site, you can do so by following these steps:
Click the Add an XML file link in the Data Source Library task pane. The Data Source Properties dialog box, as shown in Figure 15.4, opens, allowing you to choose the location of the XML you want to use.
NOTE
The Data Source Properties dialog box changes its user interface depending on the type of data source you're working with.
Figure 15.4. The Data Source Properties dialog box for XML files
Click Browse to open the Open dialog box and then navigate to the location where your XML file exists.
Select the XML file and then click Open. Every time you select an XML file by using this process, SharePoint Designer asks if you want to import the XML file into the Web site content location. Because you need to have the XML file inside your Web site for use, the obvious answer is yes.
Click ok when you receive the message box "To use <FileLocation/FileName> as a data source, you must import it into your Web site. Would you like to import the file now?". This opens the Import dialog box, which allows you to select the location/ folder where the XML file should reside inside your Web site.
Choose the desired location for the XML file and then click OK. Apart from the XML file being imported into your Web site, SharePoint Designer also updates the Data Source Library task pane to show you the newly imported XML file along with the existing ones. The only way now to delete the XML file from the Data Source Library is to remove it from the Web site. Click the Refresh Library link in the Data Source Library task pane to refresh the library after you delete the XML file.
Hover over the newly added XML file, and SharePoint Designer displays a screen tip to show you the location of the XML file inside your Web site. A dropdown menu to use the data source also appears.
Click the newly added XML file. A dropdown menu appears on the right.
NOTE
This dropdown menu is standard for the Data Source Library task pane and is available for all data sources. The available menu options change depending on the data source you choose from the task pane.
Choose Show Data from the dropdown menu to open the Data Source Details task pane. Here, you can see a preview of the data exposed by the XML file.
Switch back to the Data Source Library task pane, open the dropdown menu again, and then choose Properties. The Data Source Properties dialog box opens, which displays information about the XML file that you're working with.
|
15.3.2. ASP.NET connections
The ASP.NET connections section in the Data Source Library task pane provides you with a no-code interface to start connecting to a number of data sources, such as SQL Server, Oracle, MS-Access, and other OLEDB/ODBC sources. What you can essentially make using the ASP.NET connections are connections strings that your data source controls can use later to connect to data sources.
Connection strings are important because they provide the underlying mechanism that enables you to work with data sources. The most basic ASP.NET connection string has at least the following components:
The name of the data source to which you're connecting; for example, an SQL server database name
The object in the database to which you want to make the connection; for example, a table name
Information about the authentication mechanism to use; for example, a username and password
The name of the data provider to use for making the connection
NOTE
The Web site www.connectionstrings.com shows examples of connection strings that could be used to connect to various data sources.
SharePoint Designer by default saves connection strings for non-SharePoint sites in the configuration section of the web.config file of your Web site (or ASP.NET Web application, as many developers like to call it). Whenever you create a connection string by using the Data Source Library task pane or other mechanisms, the connection string is placed in the web.config file, as shown in Figure 15.5, at the root folder of your Web site/Web application. If the web.config file doesn't exist in the root folder, SharePoint Designer creates one for you to use.
Figure 15.5. The Code view of a web.config file that's created when SharePoint Designer is used to create an ASP.NET connection
To create a new ASP.NET connection string, click the New ASP.NET Connection link in the Data Source Library. The Choose Data Source dialog box, as shown in Figure 15.6, opens. This is where you choose the data source to which you want to connect. The next set of dialog boxes changes depending on the selection you make here.
Figure 15.6. The Choose Data Source dialog box
Depending on the selection you make in the data source list, an appropriate set of data provider lists is made available in the data provider dropdown menu. Table 15.1 lists the available ASP.NET data provider options.
To create an ASP.NET connection to an MS-Access Database and thereby understand the user interface and the resulting connection string, follow these steps:
In the Data Source list, select the Microsoft Access Database file option. The only option for Data Provider is .NET Framework Data Provider for OLEDB.
Click OK. The Connection Properties dialog box opens. This dialog box is specifically tailored for working with MS-Access database files.
NOTE
The interface of the Connection Properties dialog box changes depending on your selection of a data source, thus facilitating the creation of a corresponding connection string. The Data Source box shows your choice of data source. Clicking Change takes you back to the Choose Data Source dialog box to let you change your selection.
Click Browse to open the Select Microsoft Database file dialog box. Here, you can browse to the location where your MS-Access database resides.
Select the database file and then click Open. If your database file is protected with a username and password, type the username and password in the text fields provided.
Click Advanced. The Advanced Properties dialog box opens, allowing you to manually select or change the properties for the connection string. The steps you have followed until now have already filled in some properties, such as Data Provider, UserID, File Name, etc. The available properties also change based on the data source you chose.
Click Test Connection to test whether the connection to the data source would succeed based on the information you provided in the Connection Properties dialog box.
NOTE
Depending on the data source you're working with, you might run into a number of problems while connecting to the data source. You're shown an appropriate message indicating the error SharePoint Designer received while trying to establish the connection to the data source by using the connection string.
Click OK to save your connection string to the Web site's web.config file.
The first thing you see when you finish creating the connection string is that the new connection is displayed in the Data Source Library task pane. If you want to change the properties of the connection, just click the connection, and a dropdown menu appears on the right. Choose Properties from this dropdown menu, and SharePoint Designer takes you back to the Connection Properties dialog box, where you can make the changes you desire. Or you can choose Delete to delete the connection and start over.
You should now be able to use this ASP.NET connection string inside a Data Source Control to connect to and work with the data provided by the data source.
Right now, I take you through the steps for creating an ODBC connection to an MS-Access 2007 database file by using Data Source Names (DSN) through SharePoint Designer.
NOTE
DSNs are an ODBC mechanism to locate and connect to data sources. There can be two types of DSNs: File or Machine. The machine data sources are machine-specific; that is, they can only be used on the machine where they're created. If you're authoring directly on your Web server, you can first create a machine data source on the Web server and then access it in SharePoint Designer for creating the connection string by using the Use user or system data source name dropdown menu.
Although SharePoint Designer provides for an interface for creating DSNs, you can also create them by choosing Start 
Administrative Tools 
Data Sources (ODBC). On Windows XP and Vista, you can access Administrative Tools by choosing Start 
Control Panel 
Administrative Tools.
For this exercise, it's assumed that you're authoring directly on your Web server and that the MS-Access 2007 database file resides inside the root content location of your Web site. Follow these steps:
Click the New ASP.NET Connection link in the Data Source Library task pane. The Choose Data Source dialog box opens.
In the Choose Data Source dialog box, select Microsoft ODBC Data Source as the data source and then click OK. The Connection Properties dialog box opens, which is customized for making connections to ODBC data sources.
In the Connection Properties dialog box, select Use connection string and then click Build. The Select Data Source dialog box opens.
In the Create New Data Source dialog box, select System Data Source (Applies to this machine only) and then click Next.
Select Microsoft Access Driver [*.mdb, *.accdb], click Next, and then click Finish. The ODBC Microsoft Access Setup dialog box opens. You use this dialog box to specify the location of the MS-Access 2007 database file that you want to use for your ODBC connection.
Type the name and description of the DSN by using the Data Source Name and Description text fields, respectively.
Inside Database section, click Select to open the Select Database dialog box.
Specify the location of your MS-Access 2007 database file and then click OK twice. You should now see the newly created DSN in the list of Machine Data Sources.
Select the newly created DSN and then click OK. A Login dialog box opens that allows you to specify a username and password to use for the connection if required.
Type the username and password if required (or leave them blank) and then click OK to return to the Connection Properties dialog box.
Click Test Connection to test your connection string and then click OK. If the test succeeds, you're displayed the message "Test connection succeeded." In case of failure, an appropriate message indicating the cause of the failure is displayed.
In the New Connection dialog box, type a name for your newly created connection and then click OK.
You can now open the web.config file inside the root folder of your Web site to see the newly added connection string in the configuration section:
<configuration>
<connectionStrings>
<add name="ConnectionString1" connectionString="Dsn=MyAcc
essDSN;dbq=C:WEBSITESAcc2007DB.accdb;driverid=25;fil=MS Access;
maxbuffersize=2048;pagetimeout=5;uid=admin" providerName="System.
Data.Odbc" />
</connectionStrings>
</configuration>Notice the providerName and connectionString attributes. These change depending on the type of data source that you choose and the settings you make in the Choose Data Source and Connection Properties dialog boxes.
While working with MS-Access databases is fun, most of the enterprise-level Web applications leverage the capabilities of a more robust and scalable database solution — for example, SQL Server or Oracle databases.
However, you can manage connections to these data sources as easily as you manage one for an MS-Access database file inside SharePoint Designer. The Connection Properties dialog box, where you make connections to SQL server databases, as shown in Figure 15.7, is very simple and requires only a few basic user inputs.
The Server Name dropdown menu provides a list of available SQL Server instances that could be found on the network to which your Web server machine is connected (if you're authoring directly to the Web server by using SharePoint Designer). You can either select one or type the name of the SQL Server instance that you want to use.
After you select the SQL Server, the Select or enter the database name dropdown menu inside the Connect to a database section is enabled and allows you to either select or type the name of the database to which you want to connect by using the connection. Or you can attach an SQL Server database file (*.mdf) to the SQL Server instance you selected earlier.
Figure 15.7. The Connection Properties dialog box for Microsoft SQL Server
NOTE
The Log on to the server section is important here, as it allows you to choose what authentication mechanism you want to use to connect to the SQL Server instance. SQL Server supports either Windows Authentication or both Windows and SQL Server authentication. Based on how SQL Server is configured, you have to decide whether to use Windows Authentication or SQL Server Authentication. SQL Server Authentication (also known as Mixed Authentication) isn't as secure as Windows Authentication, and many database administrators endeavor to keep their servers set up for Windows Authentication.
Again, by clicking Test Connection, you can test if you can connect to the SQL Server instance successfully. If SharePoint Designer fails to connect to SQL Server due to a configuration issue, it displays a message indicating the probable cause of failure.