Recently I discovered the SharePoint survey list type. It allows you to quickly create questions that you would like to have people answer and then view graphical summaries of the responses. It can for example be useful for companies to collect consumer opinions about their products or services.
In this article I will show how to integrate SharePoint surveys on your external sites using the Camelot .NET Connector. I will also try to briefly explain the structure of SharePoint survey lists. I have developed a simple survey control based on the ASP.NET Wizard control that walks the user through the questions. In the last step of the wizard, it completes the survey by inserting the results back into the SharePoint survey list. This is a great example of how to creatively use the Camelot .NET Connector and give you more time to work on what matters most – your business.
Requirements
This example project can be downloaded here!
The structure of SharePoint survey lists
When you see a survey list in SharePoint for the first time, it may not be obvious how to work with its content. Each question is represented as a column in the list. The column display name (data column DisplayName) contains the actual question. The column type (data column Type) indicates the type of the answer, e.g. Text, Choice, MultiChoice, Boolean, etc. Depending on the type, the question needs to be presented differently. In addition, we should validate that required questions are answered by the user (data column Required). For simplicity in this example, we will only implement some of the types.
Each inserted item in a survey list represents one participation in the survey. By default, the same SharePoint user can only participate in a survey once. If you want to publish your survey externally and allow non-sharepoint users to participate, you must allow multiple responses. This setting can be changed under survey options.
The order of the questions is another difficult issue when selecting. I found that every survey has a content type with the same name as the list itself. By querying the list with this content type, the columns always come out in the same order as arranged in SharePoint. For example, to select all the answers in correct order from the survey list “Customer Survey”, you should use “SELECT * FROM `Customer Survey`.`Customer Survey`“.
Preparation
In SharePoint, create a new survey list in one of your sites. Add a number of questions to the survey. Make sure to enable multiple responses under survey options as shown below. In this example, I have created a silly “Cats” survey, that evaluates what type of cat person you are.
Building a web user control for presenting surveys
My aim was to create a common control that can be used on several sites so I decided to go with a ASP.NET web user control. This control takes two properties; ListName and ConnectionString. The first gives the name of a survey list that should be displayed and the second the connection string used for connecting to SharePoint.
In Visual Studio 2010, add a new Web User Control (language C#) to your web site and call it SurveyControl.ascx. Ensure that “Place code in separate file” is checked.
SurveyControl.ascx
Replace the content of the markup file (SurveyControl.ascx) with the following lines. It shows a standard ASP.NET Wizard control with a start step that should present the survey and an end step that completes the survey. All the questions will be added as steps in between the start and the end step. The OnFinishButtonClick and OnNextButtonClick events are handled by us. We have also added some css styling and graphics to the control to improve the visual impression. You can of course make the control more general by adding all static texts as inputs to the control.
<%@ Control Language="C#" AutoEventWireup="true" CodeBehind="SurveyControl.ascx.cs" Inherits="SurveyWebSiteExample.SurveyControl" %>
<style type="text/css">
.survey
{
font-family:trebuchet ms;
padding:10px;
}
</style>
<div style="margin-left:auto; margin-right:auto; margin-top:100px; width:700px; overflow:hidden; border:solid black 1px;">
<div><img src="images/cat.jpg" width="100%" alt="" /></div>
<div class="survey">
<asp:Wizard ID="SurveyWizard" runat="server" DisplaySideBar="false" OnFinishButtonClick="FinishButtonClicked" OnNextButtonClick="NextButtonClicked">
<WizardSteps>
<asp:WizardStep ID="StartStep" runat="server">
<p>We would like to ask you a few questions about cats. This survey will help you find what temperament of cat is a good match for you. Please click next to continue!</p>
</asp:WizardStep>
<asp:WizardStep ID="EndStep" runat="server" StepType="Complete" AllowReturn="false" >
<p>Thank you for participating!</p>
</asp:WizardStep>
</WizardSteps>
</asp:Wizard>
</div>
</div>
SurveyControl.ascx.cs
Now its time to implement the code behind our control. In Page_Init we will add the questions to the survey. To get all the questions, we simply execute a SHOW FIELDS query on our list. The columns returned are listed here. We have added a public property named ListName, that should give us the name of the survey list. Using some additional LINQ on our results, we sort out all the questions in the list and iterate over the result set. Each question will be added as new wizard step in our wizard control. The Type column is used to determine in what way the question will be presented. For example, Boolean is represented by a RadioButton control showing YES/NO. For Choice and MultiChoice fields we can use the EnumValues column to retrieve the alternatives. The question itself is shown at the top of the page.
The Camelot .NET Connector requires a connection string for connecting to SharePoint so we need to provide a property for that as well. The connection string is assumed to be stored in the web.config. The following connection string template can be used in most cases but all options are descibed in the connector documentation.
<add name="sharepoint_sales" connectionString="Server=my.sharepointserver.com;Database=customers;User=myuser;Password=mypass;Authentication=Ntlm;TimeOut=60;" />
So, altogether, the code in Page_Init looks as follows.
protected void Page_Init(object sender, EventArgs e)
{
_questions = Helper.ExecuteDataTable("SHOW FIELDS FROM `" + ListName + "`.`" + ListName + "`", ConfigurationManager.ConnectionStrings[this.ConnectionString].ConnectionString);
foreach (DataRow q in _questions.AsEnumerable().Where(r => (bool)r["VisibleInView"] == true && (bool)r["ReadOnly"] == false && (string)r["Name"] != "ContentType"))
{
var step = new WizardStep();
if ((bool)q["Required"])
step.Controls.Add(new LiteralControl("<h2>" + q["DisplayName"] + "<font color="red"> *</font></h2>"));
else
step.Controls.Add(new LiteralControl("<h2>" + q["DisplayName"] + "</h2>"));
SurveyWizard.WizardSteps.Insert(SurveyWizard.WizardSteps.Count - 1, step);
switch ((string)q["Type"])
{
case "Choice":
var singleChoice = new RadioButtonList();
singleChoice.ID = ((Guid)q["ID"]).ToString();
foreach (Match choice in Regex.Matches((string)q["EnumValues"], @"[(.*?)]"))
singleChoice.Items.Add(choice.Groups[1].Value);
step.Controls.Add(singleChoice);
break;
case "MultiChoice":
var multiChoice = new CheckBoxList();
multiChoice.ID = ((Guid)q["ID"]).ToString();
foreach (Match choice in Regex.Matches((string)q["EnumValues"], @"[(.*?)]"))
multiChoice.Items.Add(choice.Groups[1].Value);
step.Controls.Add(multiChoice);
break;
case "Text":
var singleLineText = new TextBox();
singleLineText.ID = ((Guid)q["ID"]).ToString();
step.Controls.Add(singleLineText);
break;
case "Boolean":
var yesNo = new RadioButtonList();
yesNo.ID = ((Guid)q["ID"]).ToString();
yesNo.Items.Add("Yes");
yesNo.Items.Add("No");
step.Controls.Add(yesNo);
break;
default:
break;
}
}
}
We also need to implement event handlers for the OnFinishButtonClick and OnNextButtonClick events. In NextButtonClicked method we will do some validation of the answers before going to the next question. In this example, we will only validate required questions. However, it would be possible to also validate the actual data entered by the user, such as text length or number range. If a required question is not answered by the user, this method will return a cancel notification to the wizard control.
The code in NextButtonClicked looks as follows.
protected void NextButtonClicked(object sender, WizardNavigationEventArgs e)
{
if (SurveyWizard.ActiveStepIndex == 0) return;
var index = 0;
foreach (DataRow q in _questions.AsEnumerable().Where(r => (bool)r["VisibleInView"] == true && (bool)r["ReadOnly"] == false && (string)r["Name"] != "ContentType"))
{
if (index == SurveyWizard.ActiveStepIndex - 1)
{
var control = SurveyWizard.ActiveStep.FindControl(((Guid)q["ID"]).ToString());
if (control != null)
{
string value = null;
switch ((string)q["Type"])
{
case "Choice":
value = ((RadioButtonList)control).SelectedValue;
break;
case "MultiChoice":
value = ((CheckBoxList)control).Items.Cast<ListItem>().Where(m => m.Selected == true).Select(m => m.Value).FirstOrDefault();
break;
case "Text":
value = ((TextBox)control).Text;
break;
case "Boolean":
value = ((RadioButtonList)control).SelectedValue == "Yes" ? "true" : "false";
break;
default:
break;
}
if ((bool)q["Required"] && string.IsNullOrEmpty(value))
{
e.Cancel = true;
return;
}
}
return;
}
index++;
}
}
The FinishButtonClicked method is called when the user finishes the survey. It will gather all the answers and form the INSERT query. Again, each type of question needs to be handled differently. The answers are first added to a dictionary and then the insert statement is built using LINQ.
The code in FinishButtonClicked looks as follows.
protected void FinishButtonClicked(object sender, WizardNavigationEventArgs e)
{
NextButtonClicked(sender, e);
if (e.Cancel) return;
var answers = new Dictionary<string, object>();
foreach (DataRow q in _questions.AsEnumerable().Where(r => (bool)r["VisibleInView"] == true && (bool)r["ReadOnly"] == false && (string)r["Name"] != "ContentType"))
{
var control = SurveyWizard.FindControl(((Guid)q["ID"]).ToString());
switch ((string)q["Type"])
{
case "Choice":
string singleChoiceAnswer = ((RadioButtonList)control).SelectedValue;
answers.Add(((Guid)q["ID"]).ToString("N"), singleChoiceAnswer);
break;
case "MultiChoice":
string[] multiChoiceAnswer = ((CheckBoxList)control).Items.Cast<ListItem>().Where(m => m.Selected == true).Select(m => m.Value).ToArray();
answers.Add(((Guid)q["ID"]).ToString("N"), multiChoiceAnswer);
break;
case "Text":
string singleLineTextAnswer = ((TextBox)control).Text;
answers.Add(((Guid)q["ID"]).ToString("N"), singleLineTextAnswer);
break;
case "Boolean":
bool yesNoAnswer = ((RadioButtonList)control).SelectedValue == "Yes" ? true : false;
answers.Add(((Guid)q["ID"]).ToString("N"), yesNoAnswer);
break;
default:
break;
}
}
var insertCommand = "INSERT INTO `" + ListName + "` SET " + _questions.AsEnumerable().Where(r => (bool)r["VisibleInView"] == true && (bool)r["ReadOnly"] == false && (string)r["Name"] != "ContentType").Select(m => "`" + (string)m["Name"] + "`" + " = @" + ((Guid)m["ID"]).ToString("N")).Aggregate((a, b) => a + ", " + b);
using (var connection = new SharePointConnection(ConfigurationManager.ConnectionStrings[this.ConnectionString].ConnectionString))
{
connection.Open();
using (var command = new SharePointCommand(insertCommand, connection))
{
foreach (var answer in answers)
command.Parameters.Add("@" + answer.Key, answer.Value);
command.ExecuteNonQuery();
}
}
}
Creating a sample page
Add a new web page to your project (we are using default.aspx in this example) and replaced the content with the following markup code. On the top of the page, we register our new survey control and later down we add an instance of this control to the page. Remember that we need to set the ListName and ConnectionString properties to initialize the control. The named ConnectionString must also be added to the web.config.
<%@ Page Language="C#" AutoEventWireup="true" CodeBehind="Default.aspx.cs" Inherits="SurveyWebSiteExample._Default" UICulture="en-US" %>
<%@ Register TagName="Survey" TagPrefix="camelot" Src="~/SurveyControl.ascx" %>
<!DOCTYPE html PUBLIC "-//W3C//DTD XHTML 1.0 Transitional//EN" "http://www.w3.org/TR/xhtml1/DTD/xhtml1-transitional.dtd">
<html xmlns="http://www.w3.org/1999/xhtml">
<head runat="server">
<title></title>
</head>
<body>
<form id="form1" runat="server">
<div>
<camelot:Survey ID="Survey" runat="server" ListName="Cats" ConnectionString="sharepoint_connection"></camelot:Survey>
</div>
</form>
</body>
</html>
Testing the page
Now, start your web site and browse to your page. If everything goes fine, your survey should show up.
