Camelot ConnectionString Errors

One of the most common support issues we have is related to connection errors. Setting up connections correctly to SharePoint (or any .NET system for that matter) can be tricky and require some reading. Regardless of which there may be other things that create these errors.

The most common error is

The client was not authorised by the server. Ensure that the
ConnectionString property on the Connection is correct.

at Camelot.SharePointConnector.Data.SharePointCommand.ExecuteReader(CommandBehavior
  behavior, Boolean returnScalar)
at Camelot.SharePointConnector.Data.SharePointCommand.ExecuteDbDataReader(
  CommandBehavior behavior)
at System.Data.Common.DbCommand.System.Data.IDbCommand.ExecuteReader(CommandBehavior
  behavior)
at System.Data.Common.DbDataAdapter.FillInternal(DataSet dataset, DataTable[]
  datatables, Int32 startRecord, Int32 maxRecords, String srcTable, IDbCommand
  command, CommandBehavior behavior)
at System.Data.Common.DbDataAdapter.Fill(DataTable[] dataTables, Int32 startRecord,
  Int32 maxRecords, IDbCommand command, CommandBehavior behavior)
at System.Data.Common.DbDataAdapter.Fill(DataTable dataTable)
at Camelot.SharePointConnector.Data.Helper.ExecuteDataTable(String query, String
  connectionString)
at Camelot.WebParts.BlogReader.BlogReader.Page_Load(Object sender, EventArgs e)

This usually means that the

  • credentials in the connectionstring is wrong, or
  • connection wasn’t allowed from the client
  • the authentication didn’t succeed from a client that is on a different domain

Credential management

It is important that the parameters in the connectionstring are correct, if you have used authentication=default you may try using the authentication=ntlm instead and remember to check and recheck that your user have access rights to the specified SharePoint Server and Site

Allowing connections

There is something called “loopback errors” that occurs internally in Windows. There are a lot of articles on how to work with this; we prefer the simple approach to solve this by using PowerShell

Execute the following PowerShell command on each SharePoint frontend server.

New-ItemProperty HKLM:\System\CurrentControlSet\Control\Lsa -Name "DisableLoopbackCheck" -Value "1" -PropertyType dword

In some very rare occasions you may need to reset IIS.

More reading at Microsoft

  • http://support.microsoft.com/default.aspx?scid=kb;EN-US;896861
  • http://support.microsoft.com/kb/281308
Posted in Camelot .NET Connector | Leave a comment

Introduction to COM+ in Camelot .NET Connector 2

The Camelot .NET Connector version 2 comes with COM+ support out of the box. This extends the Connector to be used in a large range of applications running on windows, including VB Script, ASP Classic and PHP for IIS. In this article I will give a very simple example that shows how to get started using VB Script. This adds many new possibilities to create maintenance scripts, automated tasks and integrations with enterprise applications with minimal effort.

This blog is based on the VB Script example that comes with the Connector version 2 located under “$installpath\Bendsoft\CamelotSharePointConnector_v2\Examples\VBScript”.

Requirements

The COM+ component is installed with the Connector on the same machine. You should be able to see it under Component Services in Windows.

The COM+ methods are more or less the same as the ones that are offered with the Camelot Integration Toolkit For SharePoint.

  • DownloadFile (download file from document library)
  • Execute (execute any query and returns the results)
  • ExecuteCount (returns the number of rows of query)
  • ExecuteNonQuery (execute any query without returning any results)
  • ExecuteScalar (execute any query and returns the first column of the first row)
  • GetUser (shows the user under which the query is executed)
  • UploadFile (upload file to document library)

Editing the VB Script file

Simply make a copy of the example script included with the Connector. Change the associated connection string and edit the SQL command to any SELECT command of your choice. I created a small list called “Customers” in our example that contains Name and Email of each customer. The example includes some basic fault handling. The output is by shown in a standard message box using the WScript.Echo method. Therefore, it can be a good idea to set a limit to the number of rows retrieved in this particular example.

On Error Resume Next

Dim connectionString
connectionString = "Server=sharepointserver.com;User=username;Password=xxxxx;Database=;Authentication=Ntlm;RecursiveMode=RecursiveAll;"

Dim connector
Set Connector = CreateObject("Camelot.SharePointConnector.Com.Connector")

Dim sql
sql = "SELECT * FROM `Customers` ORDER BY ID ASC LIMIT 5"
	
Dim data
data = connector.Execute(sql, connectionString)

If Err.Number <> 0 Then
	WScript.Echo "Error: " &  Err.Description
	WScript.Quit
End If

Dim i, j
If IsArray(data) Then
	Dim s
	For i = 0 To UBound(data, 1)
	    For j = 0 To UBound(data, 2)
	    	If Not IsArray(data(i, j)) Then
		    	If j = 0 Then
		    		s = s & data(i, j) & ""
		    	Else
		        	s = s & " : " & data(i, j)
		    	End If
			Else
				dim k
				dim s2
				For k = 0 To UBound(data(i, j))
					s2 = s2 & "" & "[" & data(i, j)(k) & "]"
				Next	    	
		    	If j = 0 Then
		    		s = s2 & ""
		    	Else
		        	s = s & " : " & s2
		    	End If
	    	End If
	    Next
	    s = s & vbCrLf
	Next
	WScript.Echo(s)
Else
	WScript.Echo("No data")
End If

Test the script

Simply click on your script file and see what happens. If the connection string is correct and the SQL statement is ok, you should see a popup message similar to the below.

Posted in Camelot .NET Connector, General, Integrations, SharePoint, Tutorials | Leave a comment

Joomla Tools 2.5 for SharePoint Released, now with NTLM support

We implemented NTLM support with personal or global (static) authentication. This provide safer integrations and personalized content from SharePoint.

Ensure that you use the latest version of the Camelot SharePoint Integration Toolkit when using this version of Joomla Tools.

Using NTLM Authentication

To use this features please ensure your Camelot SharePoint Integration Toolkit is version 2.0.4 or newer.


Static vs. Personal Credentials

Each connection holds which authentication to use. If you simply enable NTLM and leave the NTLM Username and Password fields’ blank personal authentication is used. This will trigger a login form to show whenever the connection is requested

Login dialog for personal login


Setting up a NTLM Connection to SharePoint

This is done in the “Camelot SharePoint Connections
section where we added a few new options.

Use NTLM This enables NTLM authentication
NTLM Username (Static) If you enter credentials here these will be used at any time, leave this field blank if you want personal credentials.
NTLM Password Password associated with the static username

Setting modes summary

NTLM Off

NTLM On with personal authentication

NTLM On with static authentication


Setting up the Camelot SharePoint Integration Toolkit for NTLM authentication

Please review the NTLM section of the toolkit documentation at http://camelottoolkit.codeplex.com/documentation.


Download Joomla Tools for SharePoint

Download Button

Posted in Joomla | Leave a comment

Introducing Camelot PHP Tools 1.5

This release fixes a few minor errors in the previous release and introduces two new commands that we made available in the latest release of the Camelot SharePoint Integration Toolkit available at http://camelottoolkit.codeplex.com/. We always keep the documentation up to date, checkout http://docs.bendsoft.com/camelot-php-tools/latest/ for the latest version of the PHP Tools documentation.

The goodies in this update

  • ExecuteCount
  • ExecuteCamelotXmlLimit

View the changelog for complete details.

Counts

The counting method is reached through the SharePointQuery class by replacing the SQL argument name with count

$SharePointQuery = new SharePointQuery(array(
    'count' => "SELECT ID FROM Tasks Where ID > 10",
    'connection_name' => 'SharePointConnection1'
));
$recordCount = $SharePointQuery->CamelotSoap->_count;

Limits, or skip and take

The SharePoint API does not support offset in limit commands so we had to implement this in a separate method. This query looks like an ordinary SharePointQuery but we added skip and take parameters.

$SharePointQuery = new SharePointQuery(array(
    'sql' => "SELECT * FROM Tasks Where ID > 10",
    'connection_name' => "SharePointConnection1",
    'skip' => 7,
    'take' => 10
));

The above command would select stuff from the Tasks list and skip the first 7 result rows returning row 8 to 17.

A good way to use this is to first run a count to verify the amount of records available and then run the skip/take query

// Query arguments
$query = "SELECT * FROM Tasks Where ID > 10";
$connection = "SharePointConnection1";
$skip = 5;
$take = 4;

// Get the total amount of records
$CountQuery = new SharePointQuery(array(
    'count' => $query,
    'connection_name' => $connection
));

$recordCount = $CountQuery->CamelotSoap->_count;

// Cannot perform query, to many skips
if($skip > $recordCount){
    die("You cannot skip more records than available");
}

// The results are returned to the $SharePointQuery variable
$SharePointQuery = new SharePointQuery(array(
    'sql' => $query,
    'connection_name' => $connection,
    'skip' => $skip,
    'take' => $take
));

// Print the result
pr($SharePointQuery); // or print_r($SharePointQuery);

Let us know if you have any questions; also consider using the forums http://forum.bendsoft.com

Download Button

Posted in SharePoint | Tagged , | 2 Comments

Downloading SharePoint Attachments with PHP, a snippet

To use this code, please download the latest version of Camelot PHP Tools and the Camelot SharePoint Integration Toolkit

We have gotten a few questions how to download attachments from SharePoint List Items using Camelot PHP Tools. Originally we planned to implement a separate library doing this but recently came to the conclusion that it wasn’t really necessary. Instead we will rely on Camelot XML and the original SQL syntax.

$SharePointQuery = new SharePointQuery(array(
    'sql' => "SELECT FileName, Data FROM Testlist.Attachments WHERE ItemID = 1",
    'connection_name' => 'SharePointConnection1'
));

Snippet

The snippet below illustrates how to download the first attachment available with ItemId 1

/*
 * Camelot PHP Tools
 *
 * @version 1.5
 * @author Bendsoft
 * @package PHP Tools
 * @subpackage Camelot
 * @license FreeBSD License (www.bendsoft.com/licensing/)
 *
 * Note! 
 * Set up settings.php before using this file
 */
error_reporting(E_COMPILE_ERROR);
ini_set('display_errors', '1');


// Load all required files for Camelot PHP Tools
require_once '../loader.php';

try {
    $SharePointQuery = new SharePointQuery(array(
                'sql' => "SELECT FileName, Data FROM Testlist.Attachments WHERE ItemID = 1",
                'connection_name' => 'SharePointConnection1'
            ));

    // Store the file info, change the index [0] if you have multiple attachments
    $file = $SharePointQuery->CamelotSoap->_sorted->_content[0];
    
    // Send the file to the browser
    header("Content-type: application/data");
    header('Content-Disposition: attachment; filename="' . $file["FileName"] . '"');
    header("Content-Description: PHP Generated Data");
    
    echo base64_decode($file["Data"]);
    
    $file = null;
    
} catch (Exception $exc) {
    echo $exc->getTraceAsString();
}

It doesn’t really get harder than this, enjoy.
 

 

Posted in PHP | 1 Comment

New free web part, Introducing the Camelot SharePoint SQL Commander Web Part

The Camelot SharePoint SQL Commander Web Part is a simple yet powerful query browser for Microsoft SharePoint 2007 and 2010.

Note! This web part does not query the tables in the SQL server; instead it queries the lists and libraries that are enclosed within SharePoint. Read more about this in the Camelot .NET Connector section.

Official web page

http://www.bendsoft.com/downloads/sharepoint-web-parts/sharepoint-sql-commander/

FAQ

What does this web part do?

It give you an interface in SharePoint where you can query your SharePoint lists and libraries with sql. You can experiment with queries of modify your list items directly with SQL. This tool is great for for anyone who quickly want to change large amounts of list items or simply learn to know how the lists works in depth.

How do I install it?

Since this is a highly custom built web part you need to install it manually, you will find everything you need to know about the installation here.

What else do I need?

You must install the Camelot .NET Connector and the Camelot SharePoint Integration Toolkit on the same SharePoint server as the Web Part.

Are there any costs involved?

The web part and the integration toolkit is free to use, they are also open source so you will be able to modify them if you want to. The Camelot .NET Connector is licensed but you can use the trial license to try it, you will find everything you need to know here.

I’m a developer, do I need it?

This web part is great for developers, you can quickly have a look at the stuff under the hood and see how columns are named, what datatype they are using and so on.

I’m an SharePoint administrator, will it improve my life?

Yes, most likely. If you ever tried to delete or rename 10000 records in a list at once you will benefit from using this. Or if you simply want an inline CLI to view and modify your SharePoint Lists and Libraries, it’s very powerful.

 

Download Button

Use our contact form if you have any questions!

 

Posted in Web Parts | Leave a comment

Bendsoft announces Camelot .NET Connector 2.0 in May 2012!

Nyköping, Sweden, Jan 26, 2012

Bendsoft Corporation announced today that it will release version 2.0 of its core software product for accessing data in Microsoft SharePoint, the Camelot .NET Connector, in May of 2012. The Camelot .NET Connector 2.0 will include an extensive suite of new features including an updated SQL syntax with JOIN and UNION support. The Camelot .NET Connector 1.1 is the latest version of Bendsoft’s industry-leading ADO.NET driver for connecting to Microsoft SharePoint, designed and built on open standards, enabling developers to query data in Microsoft SharePoint using standard SQL language. In combination with open source toolkits provided by Bendsoft, such as the Camelot SharePoint Integration Toolkit and SharePoint PHP Tools, it is the best solution for integrating SharePoint from virtually any other platform or programming language like PHP, Java or Objective-C.

The Camelot .NET Connector 2.0 will dramatically extend the core capabilities of the connector. This release will offer the first implementation of JOIN and UNION SQL commands that will work between any lists and columns in your SharePoint site. Advantages of this feature will be simplified integration with external applications and extended possibilities for data synchronization, data reporting and analytics inside or outside SharePoint.

Eric Herlitz, Co-Founder and Sales Director commented, “We are very excited to make this announcement, introducing ground-breaking capabilities of the Camelot .NET Connector that will most definitely lower the costs of integration and maintenance in many of our customer’s SharePoint projects.”

Ulf Engstrand, Co-Founder and Systems Architect, adds, “The Camelot .NET Connector in combination with our open source toolkits provide a unique solution that developers need to efficiently integrate SharePoint with other applications. Many of our customers have specifically asked for JOIN support and we feel that this is a natural step to take.”

Existing customers will be offered a free upgrade to Camelot .NET Connector version 2.0 when it becomes available. The new version will not break feature compatibility with version 1.1, but recompilation may be required.

Posted in Camelot .NET Connector, General, SharePoint | Leave a comment

Camelot PHP Tools 1.4 for SharePoint released, now supporting NTLM Authentication!

When the latest version of the Camelot PHP Tools is paired with the Camelot SharePoint Integration Toolkit version 2.0.2 or newer it has full support for NTLM authentication.

Either static or personal credentials can be used. Read more about this in the documentation, http://docs.bendsoft.com/camelot-php-tools/latest/examples-and-tutorials/ntlm-authentication/

Setting up a NTLM Connection to SharePoint

 

Static NTLM Credentials

Static credentials are used when you only want to authenticate the connection using a single account.

// Enter in settings.php

$GLOBALS['NTLM_CREDENTIALS'] = array(
    'ntlm_username' => 'domain user',
    'ntlm_password' => 'password'
);

Personal NTLM Credentials

When using personal NTLM Credentials each user/client will have to handle their own authentication, this is automatically handled by the class CamelotSoapClient that outputs a login form when required.


Download Button

Use our contact form if you have any questions!

Posted in PHP | 1 Comment

SharePoint surveys on external sites

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!

Download example!

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.

 

 

Posted in Camelot .NET Connector | Leave a comment

New syntax in PHP Tools 1.3 Connections

Since we simplified the connection handling in PHP Tools 1.3 and store all connections in the settings file we have also changed the connection syntax for PHP Tools.

The connection settings are now stored in the WcfSettingsObject object set up in settings.php. We removed the compression, connString and sharedKey parameters from the argument array and moved this into the WcfSettingsObject.

// Excerpt from settings.php
$WcfSettingsList = new WcfSettingsList();
$WcfSettingsList->append(new WcfSettingsObject("SharePointConnection1", "http://sharepointserver.com:8080/?wsdl", "MySharedKey", "sharepoint_connection", 0));
$WcfSettingsList->append(new WcfSettingsObject("SharePointConnection2", "http://sharepointserver.com:8080/?wsdl", "MySharedKey", "sharepoint_customerssite", 0));
$WcfSettingsList->append(new WcfSettingsObject("SharePointConnection3", "http://sharepointserver.com:8080/?wsdl", "MySharedKey", "sharepoint_testsite", 0));

Snippets

Selecting with SELECT

$SharePointQuery = new SharePointQuery(array(
    'sql' => "SELECT * FROM Tasks WHERE ID > 10",
    'connection_name' => 'SharePointConnection1'
));

Selecting by list and view name

$SharePointQuery = new SharePointQuery(
    array(
        'listName' => 'Tasks',
        'viewName' => 'All Tasks',
        'includeAttachements' => false,
        'connection_name' => 'SharePointConnection1',
        'columns' => ''
    )
);

Inserting

$SharePointNonQuery = new SharePointNonQuery(array(
    'sql' => "INSERT INTO Tasks (Title,AssignedTo,Status,Priority,DueDate,PercentComplete) VALUES ('Test task from PHP',1,'In Progress','(1) High', '".  date('Y-m-d H:i:s') ."',0.95)",
    'method' => 'ExecuteNonQuery',
    'connection_name' => 'SharePointConnection1'
));

Updating

$SharePointNonQuery = new SharePointNonQuery(array(
    'sql' => "UPDATE Tasks SET Title = 'Test task from PHP (updated)' WHERE PercentComplete = 0.95 AND TITLE = 'Test task from PHP'",
    'method' => 'ExecuteNonQuery',
    'connection_name' => 'SharePointConnection1'
));

Deleting

$SharePointNonQuery = new SharePointNonQuery(array(
    'sql' => "DELETE FROM Tasks WHERE Title = 'Test task from PHP (updated)' OR Title = 'Test task from PHP'",
    'method' => 'ExecuteNonQuery',
    'connection_name' => 'SharePointConnection1'
));

Using with stored procedures

There is a complete list with available procedures at the documentation site, http://docs.bendsoft.com/camelot-net-connector/latest/procedures/

Downloading a file

$download = new CamelotDownloadFile(array(
    "file" => $_GET["file"],
    "listName" => 'Shared Documents',
    "connection_name" => 'SharePointConnection1'
));

$download->download_file();

Uploading a file

$args = array(  "file" => $_FILES,
    "listName" => 'Shared Documents',
    "folder" => 'Folder/',
    "connection_name" => 'SharePointConnection2'
); 

$UploadFile = new CamelotUploadFile($args);

PHP Tools official documentation: http://docs.bendsoft.com/camelot-php-tools/latest/

Contact us if you have any questions regarding the PHP Tools.

Posted in PHP | Leave a comment