Integrating the SharePoint Server Object Model and the Entity Data Model (part 1) - Create a Console Application to Write Data to a SharePoint List

8/17/2011 6:36:38 PM

The SharePoint server object model is a server-only API through which you can interact with SharePoint in many different ways. This API is appropriate for applications that reside on the server. For example, you can use the SharePoint server object model when building and deploying Web Parts to the SharePoint server with which they need to interact. You can also use the server object model when creating management tools that people use directly on the server. You can imagine that the console application you’ll create in this article is a server-side application deployed to the SharePoint server.

Before you start this application, you’ll need to create another table in the SQL Azure Customers database you created in this article . Follow the same procedures you did in this article to first create a new table called StoreInformation and then populate that table with a specific set of data, which will include records comprising the following fields:

Store ID Primary key
Store Title Unique integer value for store
Store Name Name of store
Store Address Address of store
Store Phone Number Phone number for store
Latitude Latitudinal location of store
Longitude Longitudinal location of store
Hours Measurement of daylight hours for a particular latitude and longitude
Timestamp Time stamp of data entry

If you remember back, the script that you use to create the table is as follows:

CREATE TABLE [StoreInformation](
        [StoreID] [int] IDENTITY(1,1)NOT NULL PRIMARY KEY CLUSTERED,
         [StoreTitle] [nvarchar](8)NULL,
         [StoreName] [nvarchar](50)NOT NULL,
         [StoreAddress] [nvarchar](50)NOT NULL,
         [StorePhoneNumber] [nvarchar](50)NULL,
         [Latitude] [nvarchar](30)NULL,
         [Longitude] [nvarchar](30)NULL,
         [Hours] [nvarchar](30)NULL,
         [Timestamp] [timestamp] NOT NULL,
)

The script to insert data into the table is as follows (note that LLK Sports is a fictional company):

INSERT INTO [StoreInformation]
([StoreID],[StoreTitle],[StoreName],[StoreAddress],[StorePhoneNumber],[Latitude],[Longitude],
[Hours],[Timestamp])
     VALUES
       ('1', '1-CONS', 'LLK Sports', 'Stanton Mall, Denver, CO, USA',
        '322-555-0188', '39.6274999999999998', '-104.221', '12', '0x0000000000000067')

Be sure to add the above record as shown and then a few more records that are valid addresses and latitude/longitudes.

At this point, you can get started creating the application.

1. Create a Console Application to Write Data to a SharePoint List

1.1. Create a Console Application to Write Data to a SharePoint List

Open Microsoft Visual Studio 2010, and select File | New Project.
Select Windows, and then select the Console Application project type. Note that the default .NET Framework setting for a console application is .NET Framework 4.0 Client profile. You’ll need to set your project properties to .NET Framework 3.5 because you’ll be adding the Microsoft.SharePoint.dll to your application, which is not conversant with the .NET Framework 4.
Provide a name for the application (such as AzureConsoleApp), and click OK.
After the project is created, right-click the References node in the Solution Explorer, and select Add Reference.
Click the Browse tab, navigate to the ISAPI folder within the SharePoint root folder system (C:\Program Files\Common Files\Microsoft Shared\Web Server Extensions\14\ISAPI), select the Microsoft.SharePoint.dll as shown in the following image, and click OK.
Next, right-click the project and select Add | New Item. In the installed Templates category, click Data, and then select the ADO.NET Entity Data Model.
Provide a name for the model (for example, AzureStores), and click Add.
In the Entity Data Model wizard, select Generate From Database, and then click Next.
In the Choose Your Data Connection dialog box, click New Connection, and add the name of your SQL Azure server (for example, yourservername.database.windows.net). Click Use SQL Server Authentication to enter your SQL Server Authentication (the user name and password you created in the Windows Azure developer portal), and then in the Select Or Enter A Database Name list, select the Customers database you created in this article.
This will take you back to the Choose Your Data Connection dialog box, which will have generated a connection string for you. To create a demo application, click Yes, and include the sensitive data in the connection string. Note that you would not want to send this information across the wire in production code; instead, you can set it programmatically or from a configuration file.
Provide a name for the model (such as CustomersEntities), and click Next.
In the Choose Your Data Objects dialog box, select the tables you want to include in the model in the Tables node, and click Finish. The table you want to select is the StoreInformation table.
Now that you have added the ADO.NET EDM to the project, you can use it in your code. In the next steps in this exercise, you’ll programmatically create a “data context”—a reference to your model in code—that you can use to query the model directly.
Right-click the Program.cs file, and select View Code.

Add the following bold code:

using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using Microsoft.SharePoint;

namespace AzureConsoleApp
{
    class Program
    {
        static void Main(string[] args)
        {
            string storeAddressToAdd = "";
            string storePhoneToAdd = "";
             //Be sure to change to your SharePoint server.
            string mySPSite = "http://blueyonderdemo";

            CustomersEntities dc = new CustomersEntities();

            var query = from store in dc.StoreInformations
                        where store.StoreName == "LLK Sports"
                        select store;

            foreach (var item in query)
            {
                StoreInfo tempStore = new StoreInfo();
                tempStore.storeAddress = item.StoreAddress;
                tempStore.storePhone = item.StorePhone;

                storeAddressToAdd = tempStore.storeAddress;
                storePhoneToAdd = tempStore.storePhone;
            }

            using (SPSite site = new SPSite(mySPSite))
            {
                using (SPWeb web = site.OpenWeb())
                {
                    web.AllowUnsafeUpdates = true;

                    SPList list = web.Lists["Announcements"];
                    SPListItem item = list.Items.Add();
                    item["Title"] = "Contoso Store Follow-Up";
                    item["Body"] = "Contact request from the store in this
                        announcement.";
                    item["Store Address"] = storeAddressToAdd;
                    item["Store Phone"] = storePhoneToAdd;
                    item.Update();

                    web.AllowUnsafeUpdates = false;
                }
            }
           Console.Writeline("Hit any key to exit.");
Console.ReadLine();
        }
    }
    class StoreInfo
    {
        public string storeAddress { get; set; }
        public string storePhone { get; set; }
    }
}

The preceding example code is fairly straightforward. The goal of the code is to insert two variables (a store address and a store phone number) into a SharePoint list, along with some other information. The store address and phone number (storeAddressToAdd and storePhoneToAdd) are the variables used to store this information in the code. You got this information by adding the ADO.NET EDM to the Visual Studio project, which was represented as a data model (or context) that you could use in your code, and subsequently querying this data context by using a simple LINQ statement. The following code shows the instantiation of the new data context (dc) along with the query, which uses a hard-coded value of LLK Sports—one of the stores you should have added earlier to the SQL Azure database. Using the hard-coded store name ensures that you’ll retrieve this one store record, which you can then use in the code:

CustomersEntities dc = new CustomersEntities();

var query = from store in dc.StoreInformations
            where store.StoreName == "LLK Sports"
            select store;

Note that the same query run as an SQL statement in SQL Azure would return the following record.

After you iterate through the results of the query, the results are mapped to a custom object. Note that although this example used a custom class (StoreInfo), because you only need two fields within one entity, you could use two string variables and save yourself some coding.

After you query the results by using the LINQ statement, you then take that information and use the SharePoint server object model to add the information to SharePoint. In this part of the application, you set the context for the SharePoint site by using the SPSite and SPWeb objects, allowed updates to occur by setting the AllowUnsafeUpdates property to true, programmatically created a new list item (using the item variable), and then called the Update method to update that list item in SharePoint:

 using (SPSite site = new SPSite(mySPSite))
{
    using (SPWeb web = site.OpenWeb())
    {
        web.AllowUnsafeUpdates = true;

        SPList list = web.Lists["Announcements"];
        SPListItem item = list.Items.Add();
        item["Title"] = "Contoso Store Follow-Up";
        item["Body"] = "Contact request from the store " +
            "in this announcement.";
        item["Store Address"] = storeAddressToAdd;
        item["Store Phone"] = storePhoneToAdd;
        item.Update();

        web.AllowUnsafeUpdates = false;
    }
}

Now that you understand the code, you can jump back into Visual Studio and complete the exercise.

Having built the console application to write data to the Announcements list, you need to add those columns (Store Address and Store Phone) to the list. If you don’t add these columns, your console application will throw an exception.

Navigate to SharePoint, click Lists, and then click the Announcements list.
Click the List tab, and then select List Settings.
Under Columns, click Create Column to add the two new columns. Ensure that the column names are Store Address and Store Phone. You can mark each column as a Single Line Of Text column type.
When you’re done, return to your Visual Studio project, and press F5 to debug the application.
After you create the new list item, press Enter to exit the application. Then navigate to the Announcements list. You should see that a new list item has been added to the Announcements list, which pulled its data from SQL Azure and inserted it into the list by using the ADO.NET EDM you added to your console application.

Using a console application is a simple way to illustrate how you can integrate SharePoint and SQL Azure by using ADO.NET and the SharePoint server object model. However, you’re more than likely going to want to move beyond the simple console application to build common SharePoint artifacts such as Web Parts.

In this next exercise, you’ll create a Visual Web Part. You’re going to use the ADO.NET EDM and the server object model; you’ll also integrate the SQLDataAdapter class so you can see the differences between these two data integration methods.