Insert, Update, Delete in SharePoint 2010 using LINQ

In SharePoint 2010 we can use LINQ syntax to query the list instead of using CAML query. In order to work with LINQ we need a command line tool called SPMetal.exe.

This tool is used to generate the entity classes that is required to perform object oriented queries towards SharePoint server. It is also required to get the intellisense when we are working in Visual Studio 2010.This tool resides in C:\Program Files\Common Files\Microsoft Shared\Web Server Extensions\14\BIN folder.

Creating the entity classes:

• Open Command Prompt as an administrator.
• Change the path to C:\Program Files\Common Files\Microsoft Shared\Web Server Extensions\14\BIN.

• Run the following command to generate the entity classes.
  
  SPMetal.exe /web:http://sharepointsiteaddress /code:d:\YourEntityFile.cs

• Open Visual Studio 2010.
• Go to File à New à Project.
• Select Console Application from the installed templates.

• Right click on the solution, select "Add an existing item".
• Add the MyEntities.cs class to the solution.

·         Add References by right click on the Reference option:

·         Choose Browse:

·         Go to the following location: C:\Program Files\Common Files\Microsoft Shared\Web Server Extensions\14\ISAPI.

·         Add Microsoft.SharePoint.dll and Microsoft.SharePoint.Linq.dll in Reference.

I have a following Products list in the SharePoint Server.

Add the following code in Program.cs file to perform DML operation in SharePoint list:

using System;

using System.Collections.Generic;

using System.Linq;

using System.Text;

using Microsoft.SharePoint.Client;

using Microsoft.SharePoint.Linq;

namespace DMLLinqinSP

{

    class Program

    {

        private void Insert()

        {

            MyEntitiesDataContext myEntitiesDataContext = new
                                   MyEntitiesDataContext("http://rohit:34143/");

            // Get the list from the site

            EntityList<ProductsItem> listItems =
                              myEntitiesDataContext.GetList<ProductsItem>("Products");

            //Create a new item

            ProductsItem newItem = new ProductsItem()

            {

                Title = "Hardware",

                ProductID = "5",

                ProductName = "RAM"

            };

            // Insert the new list item to the list

            listItems.InsertOnSubmit(newItem);

            //Submit the changes

            myEntitiesDataContext.SubmitChanges();

            Console.WriteLine("Item Inserted");

        }

        private void Update()

        {

            MyEntitiesDataContext myEntitiesDataContext = new
                                     MyEntitiesDataContext("http://rohit:34143/");

            // Querying the list item that has to be updated

            var updateItem = (from item in myEntitiesDataContext.Products where
                                          item.ProductID == "1" select item).First();

            updateItem.ProductID = "6";

            updateItem.ProductName = "MotherBoard";

            // Submit the changes

            myEntitiesDataContext.SubmitChanges();

            Console.WriteLine("Item Updated");

        }

        private void Delete()

        {

            // Create an instance

            MyEntitiesDataContext myEntitiesDataContext = new
                                      MyEntitiesDataContext("http://rohit:34143/");

            // Get the list from the site

            EntityList<ProductsItem> listItems =
                               myEntitiesDataContext.GetList<ProductsItem>("Products");

            // Querying the list item that has to be deleted

            var updateItem = (from item in myEntitiesDataContext.Products where
                                             item.ProductID == "6" select item).First();

            // Deleting the list item

            listItems.DeleteOnSubmit(updateItem);

            // Submit the changes            

            myEntitiesDataContext.SubmitChanges();

            Console.WriteLine("Item Deleted");

        }

        static void Main(string[] args)

        {

            Program obj = new Program();

            byte ch = 0;

            do

            {

                Console.WriteLine("\t\t\t----Select option----\t\t\t");

                Console.WriteLine("\t\t\t----Press (1) For Insert ----\t\t\t");

                Console.WriteLine("\t\t\t----Press (2) For Update ----\t\t\t");

                Console.WriteLine("\t\t\t----Press (3) For Delete ----\t\t\t");

                Console.WriteLine("\t\t\t----Press (0) For Exit ----\t\t\t");

                ch = Convert.ToByte(Console.ReadLine());

                switch (ch)

                {

                    case 1:

                        obj.Insert();

                        break;

                    case 2:

                        obj.Update();

                        break;

                    case 3:

                        obj.Delete();

                        break;

                    default:

                        Console.WriteLine("Invalid Option");

                        break;

                }

            } while (ch != 0);

        }

    }

}

After creating an application press F5 to debug your application:

·         When you Press (1) specified item is added in the SharePoint list. Simultaneously, you can also check in the SharePoint list (Products) that one row is added in the list.

·         When you Press (2) Product ID 1 is updated in SharePoint Products list.

·         When you Press (3) Product ID 6 is deleted from the SharePoint Products list.

Thanks for reading this article. I think this will help you a lot.http://mindstick.com

SPBuildInFieldTypeID

public static void SPBuildInFieldIDS()
        {


            using (SPSite site = new SPSite("http://localhost"))
            {
                using (SPWeb web = site.OpenWeb())
                {
                    // Get IDs for all fields used in the query.
                    string assignedToId = SPBuiltInFieldId.AssignedTo.ToString("B");
                    string taskDueDateId = SPBuiltInFieldId.TaskDueDate.ToString("B");
                    string titleId = SPBuiltInFieldId.Title.ToString("B");
                    string taskStatusId = SPBuiltInFieldId.TaskStatus.ToString("B");
                    string percentCompleteId = SPBuiltInFieldId.PercentComplete.ToString("B");


                    // Define the data selection.
                    string where = "<Where><Eq>";
                    where += "<FieldRef ID='" + assignedToId + "' />";
                    where += "<Value Type='Integer'><UserID/></Value>";
                    where += "</Eq></Where>";


                    // Define the sort order.
                    string orderBy = "<OrderBy>";
                    orderBy += "<FieldRef ID='" + taskDueDateId + "' />";
                    orderBy += "</OrderBy>";


                    SPSiteDataQuery query = new SPSiteDataQuery();


                    // Set the query string.
                    query.Query = where + orderBy;


                    // Query task lists.
                    query.Lists = "<Lists ServerTemplate='107'/>";


                    // Specify the view fields.
                    query.ViewFields = "<FieldRef ID='" + titleId + "' />";
                    query.ViewFields += "<FieldRef ID='" + taskDueDateId + "' Nullable='TRUE' />";
                    query.ViewFields += "<FieldRef ID='" + taskStatusId + "' Nullable='TRUE' />";
                    query.ViewFields += "<FieldRef ID='" + percentCompleteId + "' Nullable='TRUE' />";


                    // Query all Web sites in this site collection.
                    query.Webs = "<Webs Scope='SiteCollection'>";


                    // Run the query.
                    DataTable results = web.GetSiteData(query);


                    // Print the results.
                    Console.WriteLine("{0, -10} {1, -30} {2, -30} {3}", "Date Due", "Task", "Status", "% Complete");
                    foreach (DataRow row in results.Rows)
                    {
                        // Extract column values from the data table.
                        string dueDate = (string)row[taskDueDateId];
                        string task = (string)row[titleId];
                        string status = (string)row[taskStatusId];
                        string percentComplete = (string)row[percentCompleteId];


                        // Convert the due date to a short date string.
                        DateTime dt;
                        bool hasDate = DateTime.TryParse(dueDate, out dt);
                        if (hasDate)
                            dueDate = dt.ToShortDateString();
                        else
                            dueDate = String.Empty;


                        // Convert the PercentComplete field value to a percentage.
                        decimal pct;
                        bool hasValue = decimal.TryParse(percentComplete, out pct);
                        if (hasValue)
                            percentComplete = pct.ToString("P0");
                        else
                            percentComplete = "0 %";


                        // Print a line.
                        Console.WriteLine("{0, -10} {1, -30} {2, -30} {3, 10}", dueDate, task, status, percentComplete);
                    }
                }
            }
            Console.ReadLine();
        }

Microsoft.SharePoint Namespace

http://msdn.microsoft.com/en-us/library/ms464984.aspx

SharePointLists

http://www.mindfiresolutions.com/Attachments-in-SharePoint-Part-I-409.php

Programmatically upload a file to Document Library

http://www.sharepoint-insight.com/2009/01/10/programmatically-upload-a-file-to-document-library/

Following is a utility function which you can use to upload a file programmatically in SharePoint document library. It has two parameters. First is the source file path and second is the target document library path. 

Following is an example call to this function:

UploadFileToDocumentLibrary(@”C:\test.txt”, @”http://home-vs/Shared Documents/textfile.txt”);

and here is the function

    public static bool UploadFileToDocumentLibrary(string sourceFilePath, string targetDocumentLibraryPath)

    {

        //Flag to indicate whether file was uploaded successfuly or not

        bool isUploaded = true;

        try

        {

            // Create a PUT Web request to upload the file.

            WebRequest request = WebRequest.Create(targetDocumentLibraryPath);

            //Set credentials of the current security context

            request.Credentials = CredentialCache.DefaultCredentials;

            request.Method = “PUT”;

            // Create buffer to transfer file

            byte[] fileBuffer = new byte[1024];

            // Write the contents of the local file to the request stream.

            using (Stream stream = request.GetRequestStream())

            {

                //Load the content from local file to stream

                using (FileStream fsWorkbook = File.Open(sourceFilePath, FileMode.Open, FileAccess.Read))

                {

                    //Get the start point

                    int startBuffer = fsWorkbook.Read(fileBuffer, 0, fileBuffer.Length);

                    for (int i = startBuffer; i > 0; i = fsWorkbook.Read(fileBuffer, 0, fileBuffer.Length))

                    {

                        stream.Write(fileBuffer, 0, i);

                    }

                }

            }

            // Perform the PUT request

            WebResponse response = request.GetResponse();

            //Close response

            response.Close();

        }

        catch (Exception ex)

        {

            //Set the flag to indiacte failure in uploading

            isUploaded = false;

        }

        //Return the final upload status

        return isUploaded;

    }