Comparing Data Access with ASP.NET and SharePoint 2010

Article
10/20/2016

Summary: Learn about the various data access techniques in Microsoft SharePoint 2010 and how they differ with respect to data access in Microsoft ASP.NET.

Provided by: Sohail Sayed, Sriram Reddy, Microsoft Corporation

Contents

Accessing Data in ASP.NET versus SharePoint
Accessing Data in ASP.NET
Disadvantages of Accessing Data in ASP.NET
Security Issues with Data Access in ASP.NET
Accessing Data in SharePoint 2010
Issues with Directly Accessing a SharePoint Database
Conclusion
Additional Resources

Accessing Data in ASP.NET versus SharePoint

Data access is an inseparable part of most applications. If you have worked with Microsoft ASP.NET, you are familiar with the various data access techniques that the Microsoft .NET Framework provides. All of these techniques enable you to access data directly from a data source such as Microsoft SQL Server. In most cases, you must define the schema for the database and then create the various database objects such as stored procedures, functions, and so on. Some of the data access techniques available in ASP.NET are listed in the following sections.

If you are transitioning to SharePoint 2010, you will quickly realize that these data access techniques do not apply. The main difference between data access in SharePoint 2010 as opposed to data access in ASP.NET is that you never access the database directly in SharePoint 2010. Instead, you access it through a set of well-defined APIs. This comparison of data access is the main topic of this article.

Accessing Data in ASP.NET

The data access mechanism in ASP.NET varies from version to version. In ASP.NET 1.x, data access is done by using the familiar SqlConnection class, SqlCommand class, and SqlDataReader class, binding the results to the data web controls through their DataSource properties.

In ASP.NET 2.0, data source controls bring the advantages of data display to data access. Data source controls enable you to access and modify data without code, thereby greatly reducing the repetitive code needed in ASP.NET 1.x. Furthermore, ASP.NET 2.0 provides five different data source controls, each designed to access different types of data. For example, the SqlDataSource control can access data from a relational database, whereas the XmlDataSource control accesses data from an XML file. Regardless of the data source control being used, the control connects to the data web controls in the same manner. For more details about data source controls, see Data Source Controls in ASP.NET 2.0.

To interact with databases, Dynamic Data was introduced in ASP.NET 3.5. This functionality enables you to create a data-driven web application to view data within a database, and insert, update, and delete records, all of which can use automated validation. For more details about Dynamic Data, see Dynamic Data and ASP.NET Dynamic Data Content Map.

Disadvantages of Accessing Data in ASP.NET

Typical data access in ASP.NET applications involves interaction with data and the storage of information about that data by users, possibly from untrusted sources. In these cases, it is important that you validate the data before processing it.

The following are the some of the issues you might encounter:

Users might type malicious code into input controls. The content in these controls should be validated before processing.
Malicious user input can be used to launch attacks, such as script injection and SQL injection.
View state information is encoded and stored with the contents of the page. This information could be decoded and exposed to an unwanted source.
If caching is enabled, cached data for a single user can be viewed by all users. Sensitive information could be exposed to an unwanted source if client impersonation is enabled.
Connection string data and related information might be stored at the page level, where it can be tracked.

Security Issues with Data Access in ASP.NET

The following are measures that you should implement to improve data access security:

Keep the web server up to date with the latest security updates for the Windows operating system, Internet Information Services (IIS), and SQL Server.
Always store connection string information in web.config files, and do not store connection string information as plain text. Instead, encode the string by using protected configurations. For more information, see How To: Secure Connection Strings when Using Data Source Controls.
Ensure that the identity of the process that is running ASP.NET is the default process account or a restricted user account. If the situation demands that credentials be stored, store them in a secured manner.
Configure data controls to perform the minimum functionality that is required on a page or in your application, even if a variety of SQL operations are supported.
The process attached to SQLExpress requires administrative credentials. If this is a requirement in the ASP.NET application, store the .mdf files in the site's App_Data folder. In this way, the contents of the folder will not be returned to direct HTTP requests. This is true in the case of reading data from XML files, which should also be placed inside the App_Data folder. You should also map the .mdf extension to ASP.NET in IIS and to the HttpForbiddenHandler handler in ASP.NET by using the following element in the site's web.config file.
```
<httpHandlers>
  <add verb="*" path="*.mdf" type="System.Web.HttpForbiddenHandler" />
</httpHandlers>
```
Give fewer privileges to the userid account that connects to the SQL Server databases in your application.
Validate user input because users may use techniques such as Script injections and SQL Injection to inject malicious data. For more information about securing data access, see Securing Data Access.

Accessing Data in SharePoint 2010

Up to this point, you have seen issues with using ASP.NET for data access. The following sections describe issues with data access in SharePoint 2010.

Using CAML for Querying Data

One recommended approach to accessing data in SharePoint 2010 is to use Collaborative Application Markup Language (CAML). CAML is an XML-based language to query SharePoint data. You can use CAML for data access by using the following classes.

Using the SPQuery class

You will frequently use the SPQuery class when you query data through CAML. The SPQuery class enables you to query data from a single SharePoint 2010 list or library. This class lets you define the filtering and sorting information for the query and the fields to be returned.

The following code retrieves the name and code of all the states in the United States from a custom list named States, where CountryCode is set to US.
```
SPWeb rootWeb = SPContext.Current.Site.RootWeb;
SPList stateList = rootWeb.Lists["States"];

SPQuery query = new SPQuery();

// Specify the filter.
query.Query = "<Where><Eq><FieldRef Name='CountryCode'/><Value Type='Text'>US</Value></Eq></Where> 
   <OrderBy><FieldRef Name='Title' Ascending='True' /></OrderBy>";

// Specify the fields to retrieve.
query.ViewFields = "<FieldRef Name='Title' /><FieldRef Name='StateCode' />";
query.ViewFieldsOnly = true;

// Retrieve the items. 
SPListItemCollection items = stateList.GetItems(query);

foreach (SPListItem listItem in items)
{
   Response.Write(SPEncode.HtmlEncode(listItem["Title"].ToString()) + " : " + SPEncode.HtmlEncode(listItem["StateCode"].ToString()) + "<BR>");
}
```
In this code, you first retrieve the States list. You then create a new SPQuery object and specify the data filtering based on the CountryCode and then sort based on the Title field. This is achieved through the CAML defined in the Query property of the SPQuery object. Then you specify the fields to be retrieved by the query as Title and StateCode through the ViewFields property. Finally, you call the GetItems method of the SPList class, passing the SPQuery object as a parameter. The retrieved items are then displayed on the page through the Response.Write method.
Using the SPSiteDataQuery class

The SPSiteDataQuery class enables you to query data from multiple lists and libraries across multiple sites within a site collection. The SPSiteDataQuery class has many properties that are similar to the SPQuery class.

The following code queries all the document libraries for documents where the author is ABC.
```
SPSiteDataQuery query = new SPSiteDataQuery();

// Set the various properties for the query.
query.Lists = "<Lists ServerTemplate='101' />";
query.ViewFields = "<FieldRef Name='Title' />";            

query.Query = "<Where><Eq><FieldRef Name='Author'/>" +
              "<Value Type='User'>ABC</Value></Eq></Where> <OrderBy><FieldRef Name='Title' Ascending='True' /></OrderBy>";
query.Webs = "<Webs Scope='Recursive' >";

DataTable table = SPContext.Current.Web.GetSiteData(query);
```
In this code, you query lists having ServerTemplate set to 101, which represents SharePoint Document Libraries. You specify that the query should occur across the current site and its subsites by using the Webs property. Finally, you call the GetSiteData method of the SPWeb class and pass the SPSiteDataQuery object. This method returns a data table that contains the data from the various document libraries where the author is set to ABC.
Using the CrossListQueryInfo class and the CrossListQueryCache class

Similar to the SPSiteDataQuery class, the CrossListQueryInfo class and the CrossListQueryCache class enable you to query across multiple lists within multiple sites and sub sites. These classes also provide data caching, which can significantly improve performance. These classes are present in the Microsoft.SharePoint.Publishing namespace, which can be found in the Microsoft.SharePoint.Publishing assembly.

The following code demonstrates the use of the CrossListQueryInfo class and the CrossListQueryCache class.
```
ContentByQueryWebPart cqwp = new ContentByQueryWebPart();
CbqQueryVersionInfo versionInfo = cqwp.BuildCbqQueryVersionInfo();
CrossListQueryInfo queryInfo = versionInfo.VersionCrossListQueryInfo;

// Set the query properties.
queryInfo.Query = "<Where><Eq><FieldRef Name='Author'/><Value Type='User'>ABC</Value></Eq></Where> <OrderBy><FieldRef Name='Title' Ascending='True' /></OrderBy>";
queryInfo.Lists = "<Lists ServerTemplate='101' />";
queryInfo.Webs = "<Webs Scope='Recursive' >";

CrossListQueryCache crossListCache = new CrossListQueryCache(queryInfo);

SiteDataResults results = crossListCache.GetSiteDataResults(SPContext.Current.Site, false);
```
In this code, you query for all document libraries within the current site and its sub sites where the author is set to ABC. For more information about the CrossListQueryInfo class and the CrossListQueryCache class, see CrossListQueryInfo and CrossListQueryCache.

Accessing SharePoint Data by Using LINQ to SharePoint

Although CAML is very useful for querying SharePoint 2010 data, it is not the simplest way, especially if you are new to using CAML. SharePoint 2010 includes LINQ to SharePoint to access SharePoint 2010 data. LINQ to SharePoint has many advantages over accessing data through CAML. Two main advantages are simplicity in syntax compared to CAML, and strongly typed code to represent the various objects in the SharePoint 2010 site collection, such as lists, libraries, and fields. However, LINQ to SharePoint is tightly integrated to the schema of the lists and libraries, and any change in the schema requires recompilation; otherwise the code may start failing.

If you have previously worked with LINQ to SQL, LINQ to SharePoint is very similar. LINQ to SharePoint has a command-line utility that enables you to create data context to interact with the SharePoint objects. To access data by using LINQ to SharePoint, first create the data context by executing the following command at a command prompt:

<<14 Hive>>\bin\spmetal.exe /web:http:<<site collection url>> /code:SPLinqData.cs

In this command, replace <<14 hive>> with the path of the SharePoint root directory, and replace <<site collection url>> with the URL of the site collection.

Then you need to create a new console application and add the newly created SPLinqData.cs file to it. Next, add references to Microsoft.SharePoint.dll and Microsoft.SharePoint.Linq.dll. You can now access data through LINQ.

The following code queries the list items from the States list where the CountryCode is set to US.

using (SPLinqDataDataContext context = new SPLinqDataDataContext(siteUrl))
{
    var states= from state in context.States
    where state.CountryCode == "US"
    select state;

    foreach (var state in states)
    {
        Console.WriteLine(state.Title + " : " + state.StateCode);
    }
}

You can see that the code is much more readable than CAML, and it is also strongly typed. For more information, see Using LINQ to SharePoint.

Accessing Data by Using Client-Side Techniques

You have options to access SharePoint 2010 data from .NET Framework applications that are running on client computers, from Microsoft Silverlight applications, and from ECMAScript (JavaScript, JScript) running in a SharePoint 2010 Web Part on the client. The client object model enables this without requiring you to write or install code on the server that is running SharePoint 2010. The SharePoint 2010 client object model provides two DLLs, Microsoft.SharePont.Client.dll and Microsoft.SharePont.Client.Runtime.dll, which contain the classes shown in Table 1.

Table 1. Client object model classes

Client-side classes	Server-side equivalents
ClientContext	ClientContext
Site	SPSite
Web	SPWeb
List	SPList
ListItem	SPListItem
Field	SPField

The following is sample code to use the client context.

ClientContext clientContext = new ClientContext(siteUrl);
clientContext.Load(clientContext.Web);

This code creates the context for the SharePoint 2010 site that you want to access and then loads that respective context. With this context, you can access details of sites, lists, list items, and fields. In addition to these, you can perform operations such as creating lists, updating lists, deleting lists, and so on.

The following code demonstrates retrieving data from a list by using the SharePoint client object model.

ClientContext clientContext = new ClientContext(siteUrl);
List list = clientContext.Web.Lists.GetByTitle("Announcements");
CamlQuery camlQuery = new CamlQuery();
camlQuery.ViewXml = "<View/>";
ListItemCollection listItems = list.GetItems(camlQuery);
clientContext.Load(list);
clientContext.Load(listItems);
clientContext.ExecuteQuery();
foreach (ListItem listItem in listItems)
Console.WriteLine("Id: {0} Title: {1}", listItem.Id, oListItem["Title"]);

In this code, you retrieve a List object by using the GetByTitle method. This List object has no data in it and does not have data in any of its properties until the application calls the ExecuteQuery method. The call to the ExecuteQuery method causes the SharePoint Foundation managed client object model to send the request to the server. There is no network traffic until the application calls the ExecuteQuery method. You can find more information about the ExecuteQuery method in ExecuteQuery().

You can further refine the returned results by customizing a CAML query. For more information about the SharePoint client object model, see Using the SharePoint Foundation 2010 Managed Client Object Model.

Querying Data by Using SharePoint Search

Although all the methods of data access discussed so far let you query data within a single site collection, SharePoint 2010 search enables you to query data across multiple site collections. When you use SharePoint 2010 search, the data in your SharePoint 2010 application is first searched and catalogued, and it can then be queried based on your requirements. You can also write custom code to query data across multiple site collections, but this can be performance intensive. SharePoint 2010 search provides a good performance solution because you are not querying the data directly from the SharePoint site collections, but from the catalogued data index. However, this means that the queried data may not be the most up-to-date data, depending on the frequency that the site data is catalogued.

Accessing Data by Using the SPList Class

The Items collection of the SPList class enables you to iterate through records within a specific list. However, we do not recommend this approach because it causes multiple calls to the database server and can decrease the performance. However, you can still use some methods that are provided by the SPList class to query data quickly.

The following code example demonstrates how you can retrieve an item from the list by using the SPList object.

SPList list = SPContext.Current.Web.Lists["Document"];
SPListItem item = list.GetItemById(10);
Response.Write(string.Format("Title : {0}", item["Title"].ToString()));

Issues with Directly Accessing a SharePoint Database

You should not interact with the SharePoint database directly. The following are the some of the problems you may encounter by interacting with the database directly:

The queries you write may not work after applying any patches or service packs to SharePoint 2010 because Microsoft may change the schema of the SharePoint database based on its requirements.
Direct querying places an extra load on the database, and thus there may be performance issues while dealing with large amounts of data.
Custom queries may lead to incorrect data being retrieved.
Accessing the SharePoint 2010 database violates Microsoft licensing agreements.

Conclusion

This article presented the various data access techniques for SharePoint 2010 and ASP.NET. You looked at how data access in ASP.NET differs from that in SharePoint 2010 and the advantages of one over the other. This information should make it easier to choose which method will work the best for your organization.

Additional Resources

You can find more information in the following locations: