SharePoint and .NET

Microsoft SharePoint is quickly becoming a dominate application for storing and maintaining enterprise data and intelligence for organizations of all sizes. This has created a need and opportunity for people who has specialized in data integration and visualization between SharePoint and external applications. In SharePoint 2007 there are a few options. You can use web services to interact with SharePoint lists and other features or use the server object model to extend the capabilities. With SharePoint 2010, Microsoft has stepped up by introducing the client object model, making it possible to use the object model remotely. Querying the underlying SharePoint SQL server directly is not recommended, because the underlying SharePoint database is highly sensitive to data integrity and consistency.

Many developers out there experience that the learning time to be able to rapidly develop applications working with SharePoint is too long. The developer often spend a lot of time understanding the SharePoint data structure, naming conventions and data type conversion requirements. The developer also needs to understand the CAML (Collaborative Application Markup Language) syntax used to query lists and views. The code tends to grow to a size that is not in proportion to the functionality.

The Camelot .NET Connector lets you easily develop .NET applications that require secure, high-performance data connectivity with SharePoint using standard SQL language. It implements the required ADO.NET interfaces and integrates into ADO.NET aware tools. Developers can build applications using their choice of .NET languages.

Besides standard CRUD operations, the Connector supports features that you will not find in any other tool, such as JOIN and UNION. The Connector can be used by any developer with basic SQL knowledge.

Code examples

Select example

var connectionString = @"Server=mysharepointserver.com;User=spuser;Password=******;Authentication=Ntlm;TimeOut=10;DefaultLimit=1000;CacheTimeout=30";
using (var connection = new SharePointConnection(connectionString))
{
    connection.Open();
    using (var command = new SharePointCommand(@"SELECT Title, PhoneNumber FROM Customers ORDER BY ID DESC LIMIT 10, 10", connection))
    {
        using (var reader = command.ExecuteReader())
        {
            while (reader.Read())
            {
                Console.WriteLine(reader["Title"].ToString().PadRight(30) + " : " + reader["PhoneNumber"].ToString().PadRight(30));
            }
        }
    }
}
Dim connectionString = "Server=mysharepointserver.com;User=spuser;Password=******;Authentication=Ntlm;TimeOut=10;DefaultLimit=1000;CacheTimeout=30"
Using connection = New SharePointConnection(connectionString)
    connection.Open()
    Using command = New SharePointCommand("SELECT Title, PhoneNumber FROM Customers ORDER BY ID DESC LIMIT 10, 10", connection)
        Using reader = command.ExecuteReader()
            While reader.Read()
                Console.WriteLine(reader("Title").ToString().PadRight(30) & " : " & reader("PhoneNumber").ToString().PadRight(30))
            End While
        End Using
    End Using
End Using

Update example

var connectionString = @"Server=mysharepointserver.com;User=spuser;Password=******;Authentication=Ntlm;TimeOut=10;DefaultLimit=1000;CacheTimeout=30";
using (var connection = new SharePointConnection(connectionString))
{
    connection.Open();
    using (var command = new SharePointCommand(@"UPDATE Customers SET Name = 'Anne Baxter' WHERE ID = 5", connection))
    {
        command.ExecuteNonQuery();
    }
    connection.Close();
}
Dim connectionString = "Server=mysharepointserver.com;User=spuser;Password=******;Authentication=Ntlm;TimeOut=10;DefaultLimit=1000;CacheTimeout=30"
Using connection = New SharePointConnection(connectionString)
	connection.Open()
	Using command = New SharePointCommand("UPDATE Customers SET Name = 'Anne Baxter' WHERE ID = 5", connection)
		command.ExecuteNonQuery()
	End Using
	connection.Close()
End Using

Join example

var connectionString = @"Server=mysharepointserver.com;User=spuser;Password=******;Authentication=Ntlm;TimeOut=10;DefaultLimit=1000;CacheTimeout=30";
using (var connection = new SharePointConnection(connectionString))
{
    connection.Open();
    using (var command = new SharePointCommand(@"SELECT Customers.Title AS Name, Log.Title AS Event, Log.Created FROM Customers INNER JOIN Log ON Log.Customer = Customers.ID WHERE Customers.ID = 6 ORDER BY Log.Created ASC", connection))
    {
        using (var reader = command.ExecuteReader())
        {
            while (reader.Read())
            {
                Console.WriteLine(reader["Name"].ToString().PadRight(30) + " : " + reader["Event"].ToString().PadRight(30) + " : " + reader["Created"].ToString());
            }
        }
    }
}
Dim connectionString = "Server=mysharepointserver.com;User=spuser;Password=******;Authentication=Ntlm;TimeOut=10;DefaultLimit=1000;CacheTimeout=30"
Using connection = New SharePointConnection(connectionString)
    connection.Open()
    Using command = New SharePointCommand("SELECT Customers.Title AS Name, Log.Title AS Event, Log.Created FROM Customers INNER JOIN Log ON Log.Customer = Customers.ID WHERE Customers.ID = 6 ORDER BY Log.Created ASC", connection)
        Using reader = command.ExecuteReader()
            While reader.Read()
                Console.WriteLine(reader("Name").ToString().PadRight(30) & " : " & reader("Event").ToString().PadRight(30) & " : " & reader("Created").ToString())
            End While
        End Using
    End Using
End Using

Union example

var connectionString = @"Server=mysharepointserver.com;User=spuser;Password=******;Authentication=Ntlm;TimeOut=10;DefaultLimit=1000;CacheTimeout=30";
using (var connection = new SharePointConnection(connectionString))
{
    connection.Open();
    using (var command = new SharePointCommand(@"SELECT ID, Title FROM Customers UNION DISTINCT SELECT ID, Title FROM CustomersBackup ORDER BY ID ASC", connection))
    {
        using (var reader = command.ExecuteReader())
        {
            while (reader.Read())
            {
                Console.WriteLine(reader["ID"].ToString().PadRight(30) + " : " + reader["Title"].ToString());
            }
        }
    }
}
Dim connectionString = "Server=mysharepointserver.com;User=spuser;Password=******;Authentication=Ntlm;TimeOut=10;DefaultLimit=1000;CacheTimeout=30"
Using connection = New SharePointConnection(connectionString)
    connection.Open()
    Using command = New SharePointCommand("SELECT ID, Title FROM Customers UNION DISTINCT SELECT ID, Title FROM CustomersBackup ORDER BY ID ASC", connection)
        Using reader = command.ExecuteReader()
            While reader.Read()
                Console.WriteLine(reader("ID").ToString().PadRight(30) & " : " & reader("Title").ToString())
            End While
        End Using
    End Using
End Using


Skip Navigation Links.

Visa     MasterCard