Programmatically uploading List templates to List template gallery from "File System" and creating SharePoint List based on uploaded Templates:


One of my client wants to automate the template upload process in List template gallery and create list based on that. We had all the list template stored on local file system .My job is to upload them on list template gallery  only if they are not there  and create the list on some destination web using those template.


 This can be also useful if you want to re-create any list from one site collection to another site collection on different farm. Below is my console apps that will do this job:


static void Main(string[] args)
        {
            try
            {
                Console.WriteLine("Enter site collecton URL:"); // Prompt                
                string siteURL = Console.ReadLine();
                Console.WriteLine("Enter Folder location of template files:"); // Prompt                
                string folderLocation = Console.ReadLine();
                SPSite osite = new SPSite(siteURL);                
                SPWeb rootWeb = osite.RootWeb;
                SPWeb referenceWeb = osite.OpenWeb("/reference");
                string strSTPFileName = string.Empty;
                string strSTPFileNameWithoutExt = string.Empty;
                SPDocumentLibrary lstListTemplateGallery = (SPDocumentLibrary)rootWeb.Lists["List Template Gallery"];
                SPFolder rootFolder = lstListTemplateGallery.RootFolder;


                string[] fileCollection = Directory.GetFiles(folderLocation);
                foreach (string strfile in fileCollection)
                {
                    int lstIndexofSlash = strfile.LastIndexOf("\\");
                    strSTPFileName = strfile.Substring(lstIndexofSlash + 1);
                    strSTPFileNameWithoutExt = strSTPFileName.Substring(0, strSTPFileName.IndexOf("."));
                    SPFile newFile = rootWeb.GetFile(rootFolder.Url + "/" + strSTPFileName);
                    if (!newFile.Exists)
                    {
                        FileStream fileStream = new FileStream(strfile, FileMode.Open);
                        SPFile spfile = rootFolder.Files.Add(strSTPFileName, fileStream, true);
                        spfile.Item["TemplateTitle"] = strSTPFileNameWithoutExt;
                        spfile.Item["Description"] = strSTPFileNameWithoutExt;
                        spfile.Item.Update();
                        Console.WriteLine(strSTPFileName + " is added successfully to list template gallery");
                    }
                    else
                    {
                        Console.WriteLine(strSTPFileName + " already exists on list template gallery");
                    }


                    SPListTemplateCollection listtempcol = rootWeb.Site.GetCustomListTemplates(rootWeb);
                    if (referenceWeb.Lists.TryGetList(strSTPFileNameWithoutExt) == null)
                    {
                        referenceWeb.Lists.Add(strSTPFileNameWithoutExt, "", listtempcol[strSTPFileNameWithoutExt]);
                        SPList olist = referenceWeb.Lists[strSTPFileNameWithoutExt];
                        olist.Update();
                        Console.WriteLine("List: " + strSTPFileNameWithoutExt + " successfully added on web," + referenceWeb.Title);
                    }
                    else
                    {
                        Console.WriteLine("List: " + strSTPFileNameWithoutExt + " already exists on web," + referenceWeb.Title);
                    }
                }
                rootWeb.Dispose();
                referenceWeb.Dispose();
                osite.Dispose();
            }
            catch (Exception ex)
            {
                Console.WriteLine("Error:",ex); // Prompt      
            }
            
        }

Tag: Sharepoint, List Templates.

Happy Coding :)

SQL Query for Multiple Column filter using ( WHERE.. IN)  and "CONCAT" function:


Lets say we have a table as follows:

GroupID	DepartmentID	UserId	State	Name
100	201	111	NJ	Krishna
101	202	222	CT	Dhakal
102	203	333	PA	John

Now, lets say I want value of  "Name" column   based on four filters (GroupID, DepartmentID,UserId,State). Here is the query for that:

string query =
@" SELECT Distinct Name FROM  "Table Name"

 WHERE concat(concat(concat(GroupID, concat('-', DepartmentID)),  concat('-', UserId)),    (concat('-', Name))) in ('100-201-111-NJ,101-202-222-CT');

                     
The beauty of this query is that you can pass as many value as you want on above query after "in" statement. If you pass this query on any .Net application, it will return the DataTable  as below:

Name
Krishna
Dhakal

Tag: SQL Query, Sharepoint

~ Happy Coding..

How to create field type of  multi line text and allow unlimited length in a document library

Here is the Eelement.xml file that will needed to created custom filed type of multi line text:


  Type="Note"
  Name="Parameter6Value"
  DisplayName="Parameter6Value"
  Description=""
 Required="FALSE"
Group="TEST"
DisplaceOnUpgrade="True"
Hidden="FALSE"
Sealed="FALSE"
UnlimitedLengthInDocumentLibrary="True"
 />

Here  property Type="Note" will define the filed type as "Multi line Text" and UnlimitedLengthInDocumentLibrary="True" allows unlimited length of this field.


Tag: Sharepoint, Custom Field Type

~ Happy Coding...

C# code to read distinct value from each "Column"  from DataTable and storing them in comma separated value



public string GetColumnValueInString(DataTable dt, string ColumnName)
        {
            string result = string.Empty;
            try
            {
                if (!string.IsNullOrEmpty(ColumnName))
                {
                    DataTable uniqueCols = dt.DefaultView.ToTable(true, ColumnName);
                    result = string.Join(",", uniqueCols.Rows.Cast().Select(row => row[0].ToString()).ToArray());
                }
            }
            catch (Exception ex)
            {
                Logging.Exception("GetColumnInString(DataTable dt, string ColumnName)", ex);
            }
            return result;
        }

So The above function will take Data Table and Column name as input and stores the columns value in string.

So lets say i have table with column name "Age" then i can say:

string strAge=GetColumnInString(dt, "Age"));

This will give me all the value in the Age column with "," separated.

~ Happy Coding...