Archive for May, 2009

Case and Exists in TSQL to LINQ To SQL

I had a pretty interesting scenario for which I needed a solution. I wanted to translate the following query to LINQ to SQL:


SELECT
 CaseNumber,
 FirstName,
 LastName,
 CASE WHEN Exists(Select 1 from Waiver.CaseHistory WHERE CaseNumber = t1.CaseNumber) THEN 'Yes'
   ELSE 'No' END
FROM
 Waiver.Cases t1

After a bit of searching I finally found a way to exactly do it. I have included that below:


var objQuery = from objWaiver in objDataContext.Cases
      select new
      {
       CN = objWaiver.CaseNumber,
       FN = objWaiver.FirstName,
       LN = objWaiver.LastName,
       HS = ((from objHistory in objDataContext.CaseHistories select objHistory.CaseNumber).Contains(objWaiver.CaseNumber) ? "Yes" : "No")
      };

Hope this helps.

Happy Programming !!!

Cheers,

Raja

DateTime.ParseExact method a life saver :-)

This is a pretty sweet method when it comes to checking the validity of formatted dates. Our users are so much used to typing in MMDDYY in the mainframe that they wanted this option in our application too. We could not just make them type in MMDDYY without checking for the validity of it. That is when DataTime.ParseExact helped us so much. All you do is something like this:


DateTime objDate = DateTime.ParseExact("051109", "MMddyy", System.Threading.Thread.CurrentThread.CurrentCulture);

Hope this helps.

Happy Programming!!!

Cheers,

Raja

Export To Excel

This is a pretty simple console application which I wrote for exporting query results into excel. We prepare a lot of reports which we send it as excel files. It was getting tedious for us to copy the results and then paste it in excel. So Ron asked me to create a utility to export the query results directly into Excel. I used the Office 2007 excel interop. Given below is the code for the utility:

using System;
using System.Data.SqlClient;
using System.Data;
using Microsoft.Office.Interop.Excel;
using System.Reflection;
using System.IO;

namespace ExportToExcel
{
    class Program
    {
        //Usage:
        //ExportToExcel -c, ConnectionString,-q, Query, -d, Directory (exclude the last \),-f FileName (add .xls)
        static void Main(string[] args)
        {
            Console.WriteLine(“Initializing Variables…..”);
            ApplicationClass objExcel = new ApplicationClass();
            Workbook objWorkbook;
            Worksheet objWorksheet;
            DirectoryInfo objDirectoryInfo;
            try
            {
                if (args != null)
                {
                    object objMissingValue = Missing.Value;
                    if (args.Length != 8)
                    {
                        Console.WriteLine(“\nIncorrect number of arguments. (-c connectionstring -q query -d directory -f filename)”);
                        return;
                    }
                   
                    string ConnectionString = “”;
                    string Query = “”;
                    string Directory = “”;
                    string Filename = “”;

                    for (int counter = 0; counter <= args.Length - 1; counter++)                     {                         switch (args[counter])                         {                             case "-c":                                 ConnectionString = args[counter + 1];                                 break;                             case "-q":                                 Query = args[counter + 1];                                 break;                             case "-d":                                 Directory = args[counter + 1];                                 break;                             case "-f":                                 Filename = args[counter + 1];                                 break;                             default:                                 break;                         }                     }                     Console.WriteLine("Initializing Excel.....");                     objWorkbook = objExcel.Workbooks.Add(objMissingValue);                     objWorksheet = (Worksheet)objWorkbook.Worksheets.Add(objMissingValue, objMissingValue, objMissingValue, objMissingValue);                     objWorksheet.Name = "Query Data";                     Console.WriteLine("Opening Data Connection.....");                     SqlConnection objConn = new SqlConnection(ConnectionString);                     objConn.Open();                     SqlCommand objCommand = new SqlCommand(Query, objConn);                     SqlDataAdapter objAdapter = new SqlDataAdapter(objCommand);                     DataSet objDataset = new DataSet();                     Console.WriteLine("Filling Dataset.....");                     objAdapter.Fill(objDataset);                     System.Data.DataTable objDatatable;                     objDatatable = objDataset.Tables[0];                     int ColCounter = 0;                     int RowCounter = 0;                     int Column, Row;                     Row = 1;                     Column = 1;                     Console.WriteLine("Setting Header Information.....");                     //Settting the header                     while (ColCounter <= objDatatable.Columns.Count - 1)                     {                         objWorksheet.Cells[Row, Column] = objDatatable.Columns[ColCounter].ColumnName;                         Column++;                         ColCounter++;                     }                     Console.WriteLine("Generating Data.....");                     //Generating the data                                Row++;                     while (RowCounter <= objDatatable.Rows.Count - 1)                     {                         Column = 1;                         ColCounter = 0;                         while (ColCounter <= objDatatable.Columns.Count - 1)                         {                             objWorksheet.Cells[Row, Column] = objDatatable.Rows[RowCounter][ColCounter].ToString();                             Column++;                             ColCounter++;                         }                         Row++;                         RowCounter++;                     }                     Console.WriteLine("Checking for Directory Existence and Saving file.....");                     objDirectoryInfo = new DirectoryInfo(Directory);                     //Updated on 05/08/2009 by Raja                     //Checking for the existence of Directory before saving the file since Excel is not automatically creating the file                     //Starts here                     if (objDirectoryInfo.Exists) //all is well 🙂                         objWorkbook.SaveAs(Directory + "\\" + Filename, objMissingValue, objMissingValue, objMissingValue, objMissingValue, objMissingValue, XlSaveAsAccessMode.xlNoChange, objMissingValue, objMissingValue, objMissingValue, objMissingValue, objMissingValue);                     else                     {                         //oops....create the directory and then save the file                         objDirectoryInfo.Create();                         objWorkbook.SaveAs(Directory + "\\" + Filename, objMissingValue, objMissingValue, objMissingValue, objMissingValue, objMissingValue, XlSaveAsAccessMode.xlNoChange, objMissingValue, objMissingValue, objMissingValue, objMissingValue, objMissingValue);                     }                     //Ends here                     objWorkbook.Close(false, objMissingValue, objMissingValue);                     Console.WriteLine("Successfully exported the data to the given file");                                    }             }             catch (Exception ex)             {                 objExcel.Quit();                 objWorksheet = null;                 objWorkbook = null;                 objExcel = null;                 objDirectoryInfo = null;                 Console.WriteLine(ex.Message);             }                     }             } } [/sourcecode] As always I would appreciate your feedback. Hope this helps. Happy Programming!!! Cheers, Raja