Deerwalk Blog

Deploying Custom Forms to Microsoft Office SharePoint Server

Posted by Sushanta Pokharel on January 21, 2011

Introduction

Microsoft describes SharePoint as a “Business Collaboration Platform for the Enterprise and the Web”. This product does not provide a single service but a family of services possibly spanning multiple machines. The different services have been made available under different packages, such as WSS (foundation server), Microsoft Office SharePoint Server (MOSS), and Microsoft Search Server. Initially SharePoint was just a combination of two separate products, one of which was for building websites similar to most CMS, and the other was for document management. During its evolution other features such as an extensive search mechanism, file sharing and many others were incorporated. However, all these services are still focused towards making it easy for Enterprises to build web sites, manage documents, search, categorize, share and collaborate. Like in any other application a central task is to collect user input and store it.
Applications need to collect data from users. SharePoint emphasizes ease of use for non technical users while providing a comprehensive API for programmers. This means a diversity of techniques that demand different levels of technical complexity and flexibility for design of forms. This article aims to introduce some of these techniques and evaluate them on different aspects like ease of deployment, security etc.

SharePoint Architecture Primer

All the information in SharePoint is stored in a relational database called the 'Content Database'. All of the information including the structure of the site, lists and libraries, the user uploaded documents and most of the other pages served by SharePoint are stored in the content database. Generally, only the pages covering common administrative tasks are in the file system. These pages are shared by all applications and are generally few in number.
The presentation layer which is also called the web-front end is comprised of IIS and the web-front end service. Generally speaking a SharePoint application is an ASP.NET site which uses the SharePoint Application Services underneath to provide the required functionality. This makes it easy to create a site which has document management, searching, access control etc. These application services already have most of the functionality and we just need to customize it. The different application services can run in different machines which can be grouped into a SharePoint Farm. For example the searching service, the database and the web front end can each run on different machines.
SharePoint Farm
Export data to SharePoint widzard

An important thing to notice is that since each page in SharePoint is an ASP.NET page it can write information to other sources, including other databases, file system and so on besides the content database. So, even though the pages themselves are stored in the content database, they might be referring to other sources for data to be presented or stored. This is a useful technique for custom forms which we will revisit later in the article.
Each site in SharePoint has some predefined storage units that are called libraries and lists. Each site for example has a default 'pages' library. This library holds pages, documents and images that a user can upload and view. Other libraries can also be created using either the web interface or through the provided APIs. Lists are similar to libraries but they can hold arbitrary information instead of pages. We can for example have a list of telephone numbers or a list of titles of pages in the site etc.
From the end user's perspective a SharePoint application is a single IIS website. Although, many applications can be handled by the same SharePoint server each is a different site under IIS. Each application is called a Site Collection because it can host other sites within it. These sub-sites are functionally equivalent to the top site in that they also can have their own sub-sites, libraries, lists, security etc.

Methods of data input in SharePoint

The SharePoint presentation layer as mentioned is an ASP.NET based interface. It also has native support for many Microsoft Office documents. This provides a number of options when data collection is needed. The following are the main methods that could be used:

  1. Using Microsoft Office based technology to update data in SharePoint
  2. Creating ASP.NET pages with input fields and extracting the information after submission
  3. Using InfoPath to design and deploy forms

Although these three methods have been listed, they can overlap in many respects. For example, even though “Using InfoPath to design and deploy forms” has been listed as separate from “Using Microsoft Office based products to update data”, InfoPath itself is a Microsoft Office based product. Another example is that ASP.NET pages themselves can have embedded Microsoft Office documents.

Using Microsoft Office based technology to update data in SharePoint

Products like Microsoft Excel and Microsoft Access work with tabular data. The tables in these products can be synchronized with a SharePoint list so that update in one is directly reflected into the other. The tables in these products appear as lists in the SharePoint site when linked. SharePoint also provides functionality to interact with these products from within the site itself. For example, we can design a database in Microsoft Access and export it to a SharePoint site. On the site the database tables are seen as Lists. The user can not only view the data but they can also use the queries and forms designed in Access for different tasks.
The benefit of working with this technique is that people can design tables in a technology they are already familiar with. Also, products like Access or Excel are desktop applications and are much less cumbersome to use than web pages. Designing and even modification of data can be done offline and synchronized when needed.
Export data to sharepoint wizard
Export data to SharePoint widzard

The process of import and export are quite simple and Microsoft provides wizards to make this task easier. Since the exports essentially create a list in SharePoint, security and access control can be managed well.
There are some downsides to this approach. Firstly, the format in which the data is presented is very hard to control. It is hard to change the layout of different fields in the form. Most of the time we need to contend with the format that is built in. It is often necessary that Microsoft Office be installed in client computers for them to be able to change the data. It might be unrealistic to assume that each user has Microsoft Office installed especially in public environments.

Creating ASP.NET pages with input fields

ASP.NET pages can be uploaded to a SharePoint site. The pages served through ASP.NET can generally be categorized into two type, Application Pages and Content Pages. Application pages are the ones that are found on the file system, like the ones under %SPRoot%\Template\Layouts. These pages are shared by all the sites in the application. Hence, the %SPRoot%\Template\Layouts\settings.aspx is used by all the sites. When we create a custom version of these pages then are they are stored in database and used privately. Obviously, it is for the reason that the customization may not affect other sites.
The Content Pages are all other pages that users create and upload to the system. These are stored in the content database. These are also the files we use to create forms. Although content pages are also ASP.NET pages they are by default not allowed to have any code in the CodeBehind. Therefore, by default all they are allowed to do is have other controls inside them. This is a problem because once we design a form using ASP.NET we will invariably need to validate, extract and store the data using some programming language. The way out is to override the default mode using configuration files.

<SharePoint>
<SafeMode ...>
<PageParserPaths>
<PageParserPath VirtualPath="/myFiles/*" CompilationMode="Always"
AllowServerSideScript="true" IncludeSubFolders="true"/>
</PageParserPaths>

When we include the above lines in the web.config file all the files in the /myFiles location of the site will be allowed to execute server side code. The myFiles perhaps is a document library where we want to upload the forms.For example we could create a file called address.aspx as follows

<form id="form1" runat="server">
<div>
<table style="width: 21%;">
<tr>
<td class="style1">Name</td>
<td><asp:TextBox ID="TBName" runat="server"></asp:TextBox></td>
</tr>
<tr>
<td class="style1">City</td>
<td><asp:TextBox ID="TBCity" runat="server"></asp:TextBox></td>
</tr>
<tr>
<td class="style1">Country</td>
<td><asp:TextBox ID="TBCountry" runat="server"><asp:TextBox></td>
</tr>
</table>
</div>
</form>

If we upload it to /myFiles location then the users could access it like /myFiles/address.aspx and they would see a form to fill in address. However this file also needs a code behind so that we could store the information in a database somewhereFor that we need to setup a connection string in the web.config

<connectionStrings>
<add name="LocalSqlServer"
connectionString="data source=.\SQLEXPRESS;Initial Catalog=userinfo;Integrated Security=SSPI;"
providerName="System.Data.SqlClient"/>
</connectionStrings>

When we use the connection string in the code-behind we can store the values to 'userinfo' database. A very simple method to do this is as follows

protected void ButtonSubmit_Click(object sender, EventArgs e)
{
SqlConnection myConn = new SqlConnection();
myConn.ConnectionString =
ConfigurationManager.ConnectionStrings["LocalSqlServer"].ConnectionString;
myConn.Open();
string strqry =
"Insert into addresses values ('" + TBName.Text +"','" + TBCity.Text + "','"
+ TBCountry.Text + "')";

SqlCommand myCom = new SqlCommand(strqry, myConn);
int numrow = myCom.ExecuteNonQuery();
myConn.Close();

}

The main benefit of this method is flexibility. We can create any type of layout as we please. Since we have a full fledged programming language at our disposal, complex logic can be written. We can even make use of JavaScript for a better design. SharePoint provides a comprehensive API for programming so accessing other structures and pages from within the page is also possible.
However, as powerful as this method is care should be taken to implement good security measures. The example above is perhaps prone to Sql injection. Even allowing users to run server side code overriding the default value is a security threat. In other methods the products the security is implemented in the product itself. Here programmers should implement many of them from scratch.
For the designers too it is a mixed blessing. Although very flexible it is coupled in many ways with the backend code. For non technical people this is useless and even designers can frequently find that to design well they need to learn bits and pieces of ASP.NET.

Custom Forms Using InfoPath

SharePoint is a tool that’s been designed to be usable by nontechnical users. One of the key design tenets is the notion that nontechnical users should be able to create basic web-based solutions using the web user interface and applications from the Microsoft Office suite. Although Microsoft Word and Excel undoubtedly have their place in SharePoint application design, when it comes to creating data capture forms, a more tailored tool is available.
The primary goal of InfoPath 2010 is to make it easy for nontechnical users to create forms that can be used to capture user input. Forms can be completed offline using the InfoPath client application, or, by using InfoPath Forms Services in SharePoint 2010, InfoPath forms can be rendered as web pages. In effect, coupling InfoPath 2010 with InfoPath Forms Services in SharePoint 2010 allows nontechnical users to create web-based data capture applications using a user interface that is familiar to users of other Office suite applications such as Word and Excel.
Form design in InfoPath
Form design in InfoPath

InfoPath is a drag and drop tool which creates an xml file with the name of the fields. The type of the field itself can also be defined and basic validation logic can be written. Controls like a date control also have date picker for user friendliness.
Most important feature of InfoPath is its integration with SharePoint. A form can be published directly from InfoPath to SharePoint. The form then can be filled from within the site itself or first downloaded to the user machine, filled offline using InfoPath and then uploaded again.
The problem with this method is that we need to buy InfoPath which is a part of Microsoft Office for design and deployment. Although InfoPath is a lot better than Microsoft Excel for designing forms it is not as complete as ASP.NET forms in both feature and flexibility of layout. It might be impossible to create a very artistic form using InfoPath alone. On the other hand we also need to buy Visual Studio for ASP.NET pages which might be costlier than Microsoft Office.
It is also better than other office products in that the forms can be filled within the site itself. The end users themselves do not need to install InfoPath therefore.
The level of programming skill required for design and deployment is very less. People might need to learn how to work with InfoPath itself first but then onwards it is easy. This means one more thing to learn for the designers who might already be used to other tools.
Another feature that might create a problem with InfoPath forms is that after submission complete forms are stored in the database. I mean to say that unlike with ASP.NET forms where we extract data and then put it in database, here, the complete xml needs to be saved. Thus each time the form is saved a duplicate copy with filled value is created. This means a lot of storage might be needed. Furthermore, When reports need to be created the filled forms have to be parsed first. So it might come down to using a programming language after all. This process might be inefficient compared to fetching values from database directly.

Conclusion

SharePoint has been designed to allow both programmers and non-programmers to create forms easily. There are various methods to do it accordingly. We examined three of them each one of them having their own pros and cons. For non programmers using Microsoft Office products like Access, Excel or InfoPath is the best option. It requires very less programming, wizards are available for most tasks and a lot can be used off the shelf. One down side of these methods is that they have less flexibility in the layout and complexity of logic that can be implemented. The data may be stored in such a way that it is not easy to generate reports and a lot of redundancy may be present. People may need to buy these products just to fill out forms and it may become very cumbersome.
InfoPath is one product that is better than rest of the Microsoft Office products for design of forms for SharePoint. SharePoint supports it natively. Users can simply fill it on-site or download the forms to fill it later. Since the product is specially designed to create forms a lot more options are available. Very less programming skill is needed for most typical forms. This approach also suffers from some of the deficiencies of the other products like it may not be as flexible as required for complex jobs.
To mitigate it we can make forms using ASP.NET and upload it to SharePoint. This is the most flexible approach and almost anything can be implemented. Availability of APIs for other technology makes it very tempting for complex jobs. However, most things have to be developed from scratch. Security should also be implemented correctly otherwise it can become easy to break. Technical skills required for the job is high and even designers may need to learn some programming.
The best method then might be to take the best of all the approaches and create a hybrid form. Instead of basing the forms purely on InfoPath for example we can have it embedded in an ASP.NET page which lets the programmers implement complex logic, while the non-programmers can just create the forms in InfoPath.

Subscribe to Blog Updates

Posts by Topic

see all

Recent Posts