Helper function to Convert DataTable to CSV file using LINQ

Below Function takes input as DataTable and file path where CSV file is generated. LINQ approach is so fast that it convert a big DataTable to CSV file in few seconds.


public static void ConvertToCSV(DataTable dt, string strFilePath)
        {
            StreamWriter sw = new StreamWriter(strFilePath, false);
            StringBuilder sb = new StringBuilder();
            var columnNames = dt.Columns.Cast().Select(column => column.ColumnName.ToUpper()).ToArray();
            sb.AppendLine(string.Join(",", columnNames));
            foreach (DataRow row in dt.Rows)
            {
                var fields = row.ItemArray.Select(field => field.ToString()).ToArray();
                sb.AppendLine(string.Join(",", fields));
            }         
            sw.Write(Convert.ToString(sb));
        }

~cheers...

All about SharePoint File Version History:


Lately I have been working on Sharepoint file version. Like how to display all the versions of SPFile, how to restore specific version and how to show current version of file.

Below are two class that gives you all the versions collection:

1.The SPListItemVersionCollection object contains all the versions, including the current one.
2.The SPFileVersionCollection object does not include the current version.


How to get all the information about versions:



SPFileVersionCollection versions = file.Versions;
 SPFieldUserValue userValue = new SPFieldUserValue(file.ParentFolder.ParentWeb, Convert.ToString(file.Item["Modified By"]));                   
                    if (versions != null)
                    {
                        foreach (SPFileVersion _version in versions)
                        {
                          string  fileURL = file.Versions.GetVersionFromLabel(_version.VersionLabel).Url;                          
                          string  displayName = web.EnsureUser(Convert.ToString(_version.CreatedBy)).Name;
                          int versionID=_version.ID;
                          string versionLabel= _version.VersionLabel;                           
                        }
                    }


After you have all the information you can bind the data to gridview.


How to open specific version in binary file:


SPWeb web= SPContext.Current.Web; // open the web where list exists.
SPList list= web.Lists["My List"]; // open the list where the file exists.
SPListItem  item = list.items[0]; // open the item. for now i am getting first item in list.
string FileVersionNumber="1.0";
SPFileVersionCollection verCollection= item.File.Versions; 

SPFileVersion version = versions.GetVersionFromLabel(FileVersionNumber);
 byteArray = version.OpenBinary();



How SharePoint generates URL for specific Version:


For each Published version SharePoint will have regular URL. For example, if the item is publish then its url will be :
        "/sitename/Lists/Listname/folder name/test.xlsx.


Lets say it has two major version, 1.0 and 2.0. Then what will be url of version 1.0?. It will be like shown below:
      " vti_history/512/Lists/mylist/myfolder/test.xlsx"
and version 2.0 will have regular URL.


SharePoint automatically adds number between its each major version. So version "1.0" will be "512", version "2.0" will have "1024" appended to URL and so on..


How to Restore specific version:


Lets say we want to restore version "1.0"

file.CheckOut();

file.Versions.RestoreByLabel(fileVersionLabel);

file.CheckIn(string.Empty); 




Tag: SharePoint, File Version.


~Happy Coding.

LINQ Approach to filter SharePoint List and view only given column programmatically:


 We can easily  filter a SharePoint List using CAML Query but  Microsoft is phasing out CAML query with new version of SharePoint . In this example I want to show how you can filter the SharePoint List using LINQ. This is very fast and good approach. Lets say I have SharePoint List called Customer and it has three column Name, State and City. In this example i will filter the item based on Name and  State column and return the City value.

1. First you need to add following name space:
     using System.Linq;
2. Code snippet to filter the list is below:

SPList  myList = ReferenceWeb.Lists["Customer"];

IEnumerable TranslationItem = myList.Items.OfType();

var qry = from row in TranslationItem.Distinct()

               where Convert.ToString(row["Name"]) ==   "Krish" &

               Convert. ToString (row["State"]) ==   "NJ"

                select new

                 {

                     row.Title,    // This will give you title if any.

                    CityName = Convert.ToString(row["City"]) // Here CityName is just a variable.

                 };

Lets say it returns one string value(city), how do we get it?

 if ( qry  .Count() > 0) // check if your it returns any item or not.

             {

                string   strCity=qry.ElementAt(0).CityName ;    

              }

If it returns more than one value you can convert it to DataTable, List or Array.

Eg:

   qry .ToList();

Tag: LINQ, ShrePoint, Filter SPList.

~ Happy Coding...

                 

                             

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...