Sharepoint 2007: C# Building Dynamic SPQuery to Query a Sharepoint List

If you are developing a Sharepoint page or Sharepoint web part which requires writing or constructing dynamic SPQuery or query, such as a Search Criteria page where users can choose to enter one or more search criteria. You can use Caml.cs to construct the SPQuery dynamically. The following example shows you how you can write you dynamic SPQuery using caml.cs helper class.

The following example shows how you can generate dynamic query.

//Build Search String Query 
//Product ID
if (strProdID != "")
    clauses.Add(Caml.Eq("" + strProdID + ""));
//Product Name
if (strProdName != "")
    clauses.Add(Caml.Contains("" + strProdName + ""));
//Product Classification
if (strProductClassification != "0")
    clauses.Add(Caml.Eq("" + strProductClassification + ""));
//Division
if (strDivision != "0")
   clauses.Add(Caml.Eq("" + strDivision + ""));
//Product Manager
if (uvcProdManager.Count != 0)
    clauses.Add(Caml.Eq("" + uvcProdManager[0].User.ID.ToStringSafe() + ""));

//append where 
if (clauses.Count != 0 )
    query.Query = Caml.Where(Caml.And(clauses));
query.Query += Caml.OrderBy("ID", false);

C# Export Dataset or Datatable to Excel

The following function provides the functionality to export a DataSet or DataTable into Excel file / format. It takes two arguments: Source and fileName. Source is in DataSet datatype. So if you are using DataTable, convert it to DataSet before calling the function.

public static void exportToExcel(DataSet source, string fileName)
        {

            System.IO.StreamWriter excelDoc;

            excelDoc = new System.IO.StreamWriter(fileName);
            const string startExcelXML = "\r\n\r\n \r\n " +
                  "\r\n " +
                  "\r\n " +
                  "\r\n \r\n " +
                  "\r\n \r\n " +
                  "\r\n ";
            const string endExcelXML = "";

            int rowCount = 0;
            int sheetCount = 1;
            /*
           
           
           
           
           
           
           
           
           
           
           
           
           
           */
            excelDoc.Write(startExcelXML);
            excelDoc.Write("");
            excelDoc.Write("");             excelDoc.Write("");             for (int x = 0; x < source.Tables[0].Columns.Count; x++)             {                 excelDoc.Write("");                 excelDoc.Write(source.Tables[0].Columns[x].ColumnName);                 excelDoc.Write("");             }             excelDoc.Write("");             foreach (DataRow x in source.Tables[0].Rows)             {                 rowCount++;                 //if the number of rows is > 64000 create a new page to continue output                  if (rowCount == 64000)                 {                     rowCount = 0;                     sheetCount++;                     excelDoc.Write("");
                    excelDoc.Write(" ");
                    excelDoc.Write("");
                    excelDoc.Write("");                 }                 excelDoc.Write(""); //ID=" + rowCount + "                  for (int y = 0; y < source.Tables[0].Columns.Count; y++)                 {                     System.Type rowType;                     rowType = x[y].GetType();                     switch (rowType.ToString())                     {                         case "System.String":                             string XMLstring = x[y].ToString();                             XMLstring = XMLstring.Trim();                             XMLstring = XMLstring.Replace("&", "&");                             XMLstring = XMLstring.Replace(">", ">");                             XMLstring = XMLstring.Replace("<", "<");                             excelDoc.Write("" +                                            "");                             excelDoc.Write(XMLstring);                             excelDoc.Write("");                             break;                         case "System.DateTime":                             //Excel has a specific Date Format of YYYY-MM-DD followed by                                //the letter 'T' then hh:mm:sss.lll Example 2005-01-31T24:01:21.000                              //The Following Code puts the date stored in XMLDate                               //to the format above                              DateTime XMLDate = (DateTime)x[y];                             string XMLDatetoString = ""; //Excel Converted Date                              XMLDatetoString = XMLDate.Year.ToString() +                                  "-" +                                  (XMLDate.Month < 10 ? "0" +                                  XMLDate.Month.ToString() : XMLDate.Month.ToString()) +                                  "-" +                                  (XMLDate.Day < 10 ? "0" +                                  XMLDate.Day.ToString() : XMLDate.Day.ToString()) +                                  "T" +                                  (XMLDate.Hour < 10 ? "0" +                                  XMLDate.Hour.ToString() : XMLDate.Hour.ToString()) +                                  ":" +                                  (XMLDate.Minute < 10 ? "0" +                                  XMLDate.Minute.ToString() : XMLDate.Minute.ToString()) +                                  ":" +                                  (XMLDate.Second < 10 ? "0" +                                  XMLDate.Second.ToString() : XMLDate.Second.ToString()) +                                  ".000";                             excelDoc.Write("" +                                          "");                             excelDoc.Write(XMLDatetoString);                             excelDoc.Write("");                             break;                         case "System.Boolean":                             excelDoc.Write("" +                                         "");                             excelDoc.Write(x[y].ToString());                             excelDoc.Write("");                             break;                         case "System.Int16":                         case "System.Int32":                         case "System.Int64":                         case "System.Byte":                             excelDoc.Write("" +                                     "");                             excelDoc.Write(x[y].ToString());                             excelDoc.Write("");                             break;                         case "System.Decimal":                         case "System.Double":                             excelDoc.Write("" +                                   "");                             excelDoc.Write(x[y].ToString());                             excelDoc.Write("");                             break;                         case "System.DBNull":                             excelDoc.Write("" +                                   "");                             excelDoc.Write("");                             excelDoc.Write("");                             break;                         default:                             throw (new Exception(rowType.ToString() + " not handled."));                     }                 }                 excelDoc.Write("");             }             excelDoc.Write("");
            excelDoc.Write(" ");
            excelDoc.Write(endExcelXML);
            excelDoc.Close();
        }