Resetting a ID field of a list in SharePoint

When you are developing solutions you may have used to create plenty of items in the lists interact with you solution. But when you are trying to clear up and ready the contents of your solution for production environments you may have struggle with resetting Id field of list and libraries to restart numbering at 1 sometimes. In this scenario there’s no any list options or direct way to handle this case but there’s way.

This post going to describe the way which you can reset the Id field of a list. This can achieve by using the content database of the site which the list has created.

Follow this steps:

First you need to go the content database.(Go to Central Administration > Application Management. Under the Site Collections click View all site collections. Navigate to your site collection and get the Database Name)

Open the content database using MS SQL Server Management Studio.

Open New Query and run following query.

SELECT * FROM [Your_Content_Database_Name].[dbo].[AllListsAux] WHERE ListID='Your_List_GUID'

You can see a list called AllListsAux which is maintain ID details of all the lists of the site. You can see the NextAvailableId of your list there.

Now run the following query to update the NextAvailableId. This will reset the Id field of the list and will restart numbering at “1”.

UPDATE [Your_Content_Database_Name].[dbo].[AllListsAux] SET NextAvailableId=1 WHERE ListID='Your_List_GUID'

Check this Post see how to find the GUID of a list.

Note: You need to be careful when querying on and doing any modifications to content databases to avoid malfunctioning site contents. Also note that this not a recommended approach by Microsoft.

How to Find the GUID of a SharePoint List

When you are working with SharePoint Lists, sometime you may need to get the ID (Guid) of a list.
For example, when setting the Task list to be used with SharePoint Designer Workflows.

Follow this steps:

1. Go to the SharePoint List which you need to get ID.
2. Select the Settings > List Settings menu option.
3. Copy the entire URL from the browser address bar and paste into the notepad. It should looks like this:

http://spdevsite:8585/sites/admin/_layouts/15/listedit.aspx?List=%7BEF97CD69%2D7D68%2D421F%2DA5CA%2D69768D83B328%7D

*        Delete all before and including “List=”.

*        Replace “%7B” with “{”

*        Replace all “%2D” with “-“

*        Replace “%7D” with “}”

Now you have left with the GUID you need:

{EF97CD69-7D68-421F-A5CA-69768D83B328}

Upload files to SharePoint Document Library and Sub Folders with Meta Data Programmatically

This post going to describe that how to create a Folder(Main Folder) within a Document Library and create a Sub Folder within Main Folder and upload files to Sub Folder.

private void UploadContractDocuments(string paraMainFolderName, string paraSubFolderName, 
            string paraContractNumber, DateTime paraStartDate, DateTime paraEndDate) 
        {
            try
            {
                string mainFolderName = paraMainFolderName;
                string subFolderName = paraSubFolderName;

                using (SPSite site = new SPSite(SPContext.Current.Web.Url))
                {
                    site.AllowUnsafeUpdates = false;

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

                        //Check whether file upload control contains a file
                        if (ContractFileUpload.HasFile)
                        {
                            SPDocumentLibrary documentLibrary = web.Lists["ContractDocuments"] as SPDocumentLibrary;

                            string FolderPath = documentLibrary.RootFolder.Url + "/" + mainFolderName;//Main folder path
                            string SubFolderPath = documentLibrary.RootFolder.Url + "/" + mainFolderName + "/" + subFolderName;//Sub folder path

                            //Check whether main folder already exist
                            if (!web.GetFolder(FolderPath).Exists)
                            {
                                //Create main folder if not exist
                                SPListItem newFolder = documentLibrary.Items.Add("", SPFileSystemObjectType.Folder, mainFolderName);
                                newFolder.Update();

                                //Create sub folder inside main folder
                                string subFolderUrl = web.Url + "/" + newFolder.Url;

                                SPListItem subFolder1 = documentLibrary.Items.Add(subFolderUrl, SPFileSystemObjectType.Folder, subFolderName.ToString());
                                subFolder1.Update();
                            }
                            else
                            {
                                //Check for subfolder if main folder is already exist
                                if (!web.GetFolder(SubFolderPath).Exists)
                                {
                                    //Create sub folder if not exist
                                    string subFolderUrl = FolderPath;

                                    SPListItem subFolder1 = documentLibrary.Items.Add(subFolderUrl, SPFileSystemObjectType.Folder, subFolderName.ToString());
                                    subFolder1.Update();
                                }
                            }

                            Stream fStream = RequestModuleFileUpload.PostedFile.InputStream;
                            byte[] _byteArray = new byte[fStream.Length];
                            fStream.Read(_byteArray, 0, (int)fStream.Length);
                            fStream.Close();

                            string fileName = RequestModuleFileUpload.PostedFile.FileName;

                            string _fileUrl = documentLibrary.RootFolder.Url + "/" + mainFolderName + "/" + subFolderName + "/" + Path.GetFileNameWithoutExtension(fileName) + Path.GetExtension(fileName);
                            bool IsOverwriteFile = true;

                            //Create meta data
                            var docMetadata = new Hashtable {
                                        { "ContractNumber", paraContractNumber },
                                        { "StartDate", paraStartDate},
                                        { "EndDate", paraEndDate }
                                        };

                            SPFile file = documentLibrary.RootFolder.Files.Add(_fileUrl, _byteArray, docMetadata, IsOverwriteFile);
                            SPListItem item = file.Item;

                            file.Update();
                            documentLibrary.Update();
                        }
                    }
                }
            }
            catch (Exception)
            {
                throw;
            }
        }