Google

Saturday, December 13, 2008

SQL Interview Questions - Time Series Based Problems


I was just wondering about time-series based SQL problems, and decided to put up a data sample based on the tea & chocolate consumption of Microsoft & Sun Microsystems. Let's take the sample data below.

SALESORDERID ITEM_NAME QUANTITY COST_PER_UNIT  ORDERDATE  DUEDATE ORDERSTATUS  CUSTOMERNAME 
1 TEA 45.0000 1.2900 2/29/2008 3/31/2008 P MICROSOFT
2 TEA 30.0000 1.2500 4/30/2008 7/31/2008 P MICROSOFT
3 TEA 90.0000 1.5000 5/31/2008 7/31/2008 P MICROSOFT
4 TEA 12.0000 2.0000 6/30/2008 8/31/2008 P MICROSOFT
5 TEA 34.0000 4.3800 2/29/2008 4/30/2008 P SUN
6 TEA 45.0000 3.9900 3/31/2008 5/31/2008 P SUN
7 TEA 79.0000 3.5000 4/30/2008 7/31/2008 P SUN
8 TEA 36.0000 5.3800 6/30/2008 7/31/2008 P SUN
9 TEA 12.0000 4.3800 7/31/2008 7/31/2008 P SUN
10 TEA 50.0000 4.2200 8/30/2008 9/30/2008 P SUN
11 CHOCOLATE 110.0000 0.6900 3/31/2008 4/30/2008 P SUN
12 CHOCOLATE 130.0000 1.2200 4/30/2008 5/31/2008 P SUN
13 CHOCOLATE 76.0000 0.5500 5/31/2008 6/30/2008 P SUN
14 CHOCOLATE 200.0000 0.8900 6/30/2008 7/31/2008 P SUN
15 CHOCOLATE 230.0000 0.2300 7/31/2008 8/31/2008 P SUN
16 CHOCOLATE 210.0000 0.7800 8/31/2008 9/30/2008 P SUN
17 CHOCOLATE 199.0000 1.1000 9/30/2008 10/31/2008 P SUN
18 CHOCOLATE 102.0000 2.0000 2/29/2008 3/31/2008 P MICROSOFT
19 CHOCOLATE 245.0000 2.0000 3/31/2008 4/30/2008 P MICROSOFT
20 CHOCOLATE 312.0000 2.0000 4/30/2008 5/31/2008 P MICROSOFT
21 CHOCOLATE 345.0000 2.0000 5/31/2008 6/30/2008 P MICROSOFT
22 CHOCOLATE 307.0000 2.0000 6/30/2008 7/31/2008 P MICROSOFT
23 CHOCOLATE 120.0000 2.0000 7/15/2008 7/31/2008 P MICROSOFT
24 CHOCOLATE 300.0000 2.0000 7/31/2008 8/31/2008 P MICROSOFT
25 CHOCOLATE 299.0000 2.0000 8/31/2008 9/30/2008 P MICROSOFT
26 CHOCOLATE 290.0000 2.0000 9/30/2008 10/31/2008 P MICROSOFT
27 CHOCOLATE 10.0000 2.0000 10/5/2008 10/31/2008 P MICROSOFT
28 CHOCOLATE 12.0000 2.0000 10/15/2008 10/31/2008 P MICROSOFT
29 CHOCOLATE 12.0000 2.0000 10/20/2008 10/31/2008 P MICROSOFT

 

--Write a query to get the TOTAL AMOUNT SPENT on tea consumption by each company between 03/31/2008 and 08/31/2008

SELECT CUSTOMERNAME, SUM(QUANTITY*COST_PER_UNIT) FROM #TMP_SALES

WHERE ORDERDATE BETWEEN '03/31/2008' AND '08/31/2008'

GROUP BY CUSTOMERNAME

 

--QUERY TO FIND AVERAGE NUMBER OF DAYS EACH CUSTOMER GIVES FOR AN ORDER

SELECT CUSTOMERNAME, AVG(DATEDIFF(D, ORDERDATE, DUEDATE))

FROM #TMP_SALES

GROUP BY CUSTOMERNAME

 

--QUERY TO FIND THE MONTHS WHEN EACH CUSTOMER DID NOT PLACE

--ANY ORDERS

SELECT * FROM

#TMP_SALES WHERE SALESORDERID NOT IN

(

      SELECT T_OUTER.SALESORDERID

      FROM #TMP_SALES T_OUTER

      INNER JOIN #TMP_SALES T_INNER

      ON T_OUTER.CUSTOMERNAME = T_INNER.CUSTOMERNAME

      AND T_OUTER.ITEM_NAME = T_INNER.ITEM_NAME

      AND MONTH(T_OUTER.ORDERDATE) = MONTH(DATEADD(M, -1, T_INNER.ORDERDATE))

)

 

Tuesday, September 9, 2008

Email Attachments using C#

How to automatically generate e-mails with attachments from your C# Code ?

Now-a-days everyone wants Emails to be generated from the applciaiton itself. in my previous blog I have already discussed how to generate simple text e-mails.

But if you want to generate e-mails with attachments , still its not difficult at all. I have done it for sending an Excel Attachment. The txt attachment again is simpler, you just need to follow the same logic as excel but less complicated to copy and send. So, Lets Start.

PART I 

The code below shows the following steps for copying the excel sheet to be posted into the mailing attachment sheet(If you already have the sheet and just need to mail it, you can skip this part):-
1. Create a new excel workbook.
2. Create a new Directory.
3. Copy the old sheet to the new workbook sheet
4. You can turn on the displays on/off for the sheet by just doing
          application.displayAlerts = false/true;
5. Save the new workbook with the name required and close the excel.For saving
          you can use
            newWorkbook.saveAs(Save Name,System.Type.Missing,
            System.Type.Missing,System.Type.Missing,false, false,
            XlSaveAsAccessMode.xlNoChange,System.Type.Missing,
            System.Type.Missing,System.Type.Missing,
            System.Type.Missing,System.Type.Missing);
          and for closing you can use
            newWorkbook.Close(false,false,System.Type.Missing);
PART II 

Now, the steps below shows the e-mailing of the excel sheet as an attachment.
1. Make an object of MailMessage.
2. Set the parameters ,
          TO(toAddress); FROM(fromAddress); SUBJECT(a string one liner);     
          BODY(message to write inside the main body of mail);
3. Set the MailAttachment object with the filename to send an simply add the
           Attachment to the mail message.
4. SmtpMail.SmptServer needs to be set. It is the address of your office mail
          server.And then finally the message needs to be send, SmtpMail.Send();
private void SendEmail()
{

    Excel.Workbook newWorkbook = this.thisApplication.Workbooks.Add
                                 (XlWBATemplate.xlWBATWorksheet);

    string windowsLogin = Environment.UserName.ToString();

    object m = System.Reflection.Missing.Value;

    string fMsg = windowsLogin + @"@gmail.com";

    string note = "";

    try
    {

         Directory.CreateDirectory("ForEmail");

         Directory.SetCurrentDirectory("ForEmail");

         oldSheet.Copy(newWorkbook.Sheets[1],System.Type.Missing);

         Excel.Worksheet emailingSheet = ExcelHelpers.GetWorksheet
                                          (newWorkbook, "Recap");

         Excel.Range tempRange= emailingSheet.UsedRange;

         Excel.Worksheet copySheet = ExcelHelpers.GetWorksheet
                                          (newWorkbook, "Sheet1");

         newWorkbook.Sheets.FillAcrossSheets
                                 (tempRange,XlFillWith.xlFillWithAll);
         string accountName = acctComboBox.Text.Trim();

         copySheet.Cells[5,2] = accountName;

         thisApplication.DisplayAlerts = false;

         emailingSheet.Delete();

         thisApplication.DisplayAlerts = true;

         copySheet.Columns.AutoFit();

         newWorkbook.SaveAs("Info.xls",System.Type.Missing,
            System.Type.Missing,System.Type.Missing,false, false,
            XlSaveAsAccessMode.xlNoChange,System.Type.Missing,
            System.Type.Missing,System.Type.Missing,
            System.Type.Missing,System.Type.Missing);

         newWorkbook.Close(false,false,System.Type.Missing);
       

       

//***************************PART TWO BEGINS HERE******************************//

         string to = null;

         for(int r = 2; r < 10; r++)

         {

             if(((Range)mEmailSheet.Cells[r, 1]).Text != null &&

                  ((string)((Range)mEmailSheet.Cells[r, 1]).Text).Length < 1)

             {

                     break;

             }

             if(to == null)
               to = (string)((Range)mEmailSheet.Cells[r, 1]).Text+";";

             else

               to = to +((Range)mEmailSheet.Cells[r, 1]).Value2.ToString()+ ";";

         }

         MailMessage message = new MailMessage();

         message.To = to;

         message.From = fMsg.ToString();

         message.Subject="Email Submitted " ;  

         message.Body ="Attachment enclosed";

         Thread.Sleep(3000);

         DirectoryInfo dirInfo = new DirectoryInfo(Directory.GetCurrentDirectory
                                                                            ());

         FileInfo[] fInfo = dirInfo.GetFiles();

         string fileName = fInfo[0].Name;

         MailAttachment attach = new MailAttachment
                                 (Directory.GetCurrentDirectory() +

                                 @"\"+fileName,
                                 System.Web.Mail.MailEncoding.Base64);

         message.Attachments.Add(attach);

         SmtpMail.SmtpServer = "xyz.CompanyDomain.com";

         SmtpMail.Send(message);

         MessageBox.Show("E-Mail send.");

         Directory.SetCurrentDirectory("..");

         Directory.Delete("ForEmail",true);

     }

     catch(Exception e)

     {

          Console.WriteLine(e);

          newWorkbook.Close(false,false,System.Type.Missing);

          Directory.SetCurrentDirectory("..");

          Directory.Delete("ForEmail",true);

     }

}

Wednesday, June 4, 2008

How to automatically generate E-mails from your C# code?

Generating E-mails through C# code can be very helpful to users. They just need to click a button and boom, the information is sent on the e-mail to the required people. If you want to generate a normal E-mail without attachment, these are the steps you can write:-- 1. You need to include , using System.Web.Mail; 2. The code for sending e-mail is as follows, string userID = Environment.UserName.ToString(); MailMessage message = new MailMessage(); string windowsLogin = Environment.UserName.ToString(); string fromMsg =windowsLogin+@"@companyDomainName.com"; string toMsg = null ; toMsg = (string) ((Range)sheet.Cells[i,15]).Text; // In case of Excel Application "To field can take in any constant or user supplied value from Text box etc. message.To = toMsg.ToString(); message.From = fromMsg.ToString(); message.Body = "STATUS HAS BEEN CONFIRMED. PLEASE CHECK YOUR RESPECTIVE UPDATES FROM THE APPLICATION."; message.Subject=" STATUS CONFIRMED BY " + userID.ToUpper(); SmtpMail.SmtpServer = "server-name.com"; SmtpMail.Send(message); Console.WriteLine(“E-mail Has Been Sent successfully”);

You have to know your server name so that you can send an e-mail. For knowing this, go to your Outlook Express

Click on Tools --> Send/Receive --> Send/Receive Settings --> Define Send/Receive Groups --> Edit --> Account Properties -->

There you find Microsoft Exchange Server name written in General Tab

Also in Tools --> Options --> Mail Setup --> You can find Send/Receive Settings , this will take you to the same pop-up , Define Send/Receive Groups. In case you want to Email an attachment using C# , please read my another blog topic "Emailing Attachments ".

Tuesday, June 3, 2008

Using Microsoft.Office.Interop.Excel, How to sort rows in a worksheet?

It is very easy to sort rows using Microft.Office.Interop.Excel in a worksheet. PART I - SORTING BASED ON 1 OR 2 COLUMNS We can use the Sort method of the NamedRange object to sort the data in a worksheet. Just create a Range object i.e. Excel.Range sRng; Also, you already have a worksheet object, Worksheet SummSheet; Now assign Range to sRng , sRng = SummSheet.get_Range("A4","N" + rowNum);// here A4 is the starting cell and N"rowNum" is the ending cell Apply this sort method

sRng.Sort(sRng.Columns[1,System.Type.Missing],XlSortOrder.xlAscending, sRng.Columns[10,System.Type.Missing], System.Type.Missing,XlSortOrder.xlAscending,  System.Type.Missing,XlSortOrder.xlAscending,XlYesNoGuess.xlNo,System.Type.Missing,System.Type.Missing,XlSortOrientation.xlSortColumns,XlSortMethod.xlPinYin,XlSortDataOption.xlSortNormal,XlSortDataOption.xlSortNormal,XlSortDataOption.xlSortNormal);
In the above method we are sorting the rows as per 1st column .In case 1st column has same values the row will be sorted according to the 10th column. The Sorting Order is Ascending order. The same can be applied for Descending Order also. Only the SortOrder needs to be changed. PART II - SORTING BASED ON MORE THAN 2 COLUMNS In case you need to sort the rows based on more than 2 columns, we can implement ICOMPARABLE Interface's CompareTo method. We can have a class implement IComparable Interface and then re-define the CompareTo method. For e.g.

public class TryCompare:IComparable
{
 public string ID
 {
  get
  {
   return Did;
  }
  set
  {
   Did = value;
  }
 }
 public string QuanDiff
 {
  get
  {
   return DQuanDiff;
  }
  set
  {
   DQuanDiff = value;
  }
 }
 public string PriceDiff
 {
  get
  {
   return DPriceDiff;
  }
  set
  {
   DPriceDiff = value;
  }
 }


 public int CompareTo(object obj)
 {
  TryCompare CompareObj =(TryCompare)obj;
  int compResults = this.ID.CompareTo(CompareObj.ID);
  if(compResults==0)
  {
   if(this.QuanDiff==CompareObj.QuanDiff)
   {
    if(this.PriceDiff==CompareObj.PriceDiff)
     return 0;
    else if(this.PriceDiff>CompareObj.PriceDiff)
     return 1;
    else if(this.PriceDiff&lt;CompareObj.PriceDiff)
                  return -1;
   }
   else if(this.QuanDiff>CompareObj.QuanDiff)
   {
    return 1;
   }
   else if(this.QuanDiff&lt;CompareObj.QuanDiff)
              {
               return -1;
              }
  }
  return compResults;
 }

}
So in the example above , we are sorting based on 3 values. Now we just need to call , TryCompareObj.Sort(); This will solve all our sorting requirements.

To convert an INT into ASCII Char

Converting INTEGER into an ASCII char is very simple . In C# we dont need to do anything special, just type-cast the INT into a CHAR. int i = 65; char a = (char) i; Console.WriteLine("Result : "+a.toString()); Result : A Converting ASCII char into an INTEGER is also same way but vice versa.Type-casting CHAR into an INT. char a = 'A'; int i = (int) a; Console.WriteLine("Result : "+i.toString()); Result : 65

Thursday, May 29, 2008

Error : The assembly could not be found at or could not be loaded

I have an excel application created using .Net framework 1.1 and Microsoft Office Excel 2003. This application named CBlotter with the same settings and dll in all the user computers was running fine but only in 1 computer , I encountered the error saying "The assembly CBlotter could not be found at .\CBlotter_bin\ or could not be loaded. You can still edit and save the document.Contact your administrator or the author of this document for further assistance." I checked all the user permissioning of the folder. I gave the full trust policy if not already given to the folder by using Caspol.exe(Code Access Security Policy Tool). This can be done by going to the specified v1.1/v2.0 folder. If you want to grant full trust to a local folder , then type caspol -u -ag All_Code -url C:\\\* FullTrust -n "" -d "" If you want to grant full trust to a netwrok folder , then type caspol -pp off -m -ag LocalIntranet_Zone -url \\\\* FullTrust -n "" -d "" After assigning the right permissioning and full trust , usually the assembly loading/cannot find error goes away but in my case it dint. So Now, I checked for the assemblies required for my .net code to run . In VS2003 we add reference to the COM library called 'Microsoft Excel 11.0 Object Library'. This helps us use the Excel Objects and write our piece of code using VS2003. When I checked the library in assembly cache (GAC), using microsoft 1.1 configuration GUI, I was not able to locate the assembly. This actually raised a question as Microsoft Office Excel 2003 and .Net 1.1 framework was completely installed on User's PC then why were the Primary Interop Libraries (PIA) missing. After researching I found out that these libraries require re-registration or installation sometimes when some other office application initiates a setup that causes library registration to be rewritten. In my case Microsoft Excel SP3 installation had removed the PIAs altogether . So I just installed the PIAs again by going to the site http://support.microsoft.com/kb/897646 This solved my problem.

Tuesday, January 1, 2008

Access Previous Page's Controls Using Cross Page Postback

How to access previous page's controls

Before Asp.Net 2.0 was introduced, accessing the previous page's controls from a given page was a tricky affair. You had to use nice but not so right features like Session, HttpContext, even cookies in some cases, to persist relevant information from one page to another. Session is useful when you have to persist a bunch of data across the user's session, but it is not suitable for passing random values between pages. The primary reason for this being that as your application gets more and more complex, the Session state could become cluttered and confusing because of similarly named session values, and could easily become a maintainence nightmare. After a period of time, you would not remember what Session state variables you used to pass values between which pages.

Asp.Net 2.0 comes with a very handy feature called the PreviousPage property. This is a public property of the Page class, and refers to the PreviousPage instance. Using this property, you can access the previous page's public properties. You can also access the previous page's controls using the FindControll method.

Let us take an example. Suppose we have the following web page, that has a simple textbox and a button. Note that the button's PostBackUrl property is set to another page, meaning that when the button is clicked, the current webform will post back to the PostBackUrl page (If you are not clear with cross page HTTP postbacks, look at this post).

    <%@ Page Language="C#" AutoEventWireup="true"  CodeFile="Default.aspx.cs" Inherits="_Default" %>

    <!DOCTYPE html PUBLIC "-//W3C//DTD XHTML 1.0 Transitional//EN" "http://www.w3.org/TR/xhtml1/DTD/xhtml1-transitional.dtd">

    <html xmlns="http://www.w3.org/1999/xhtml" >
    <head runat="server">
     <title>Untitled Page</title>
    </head>
    <body>
     <form id="form1" runat="server">
     <div>
      <asp:Button ID="Button1" runat="server" PostBackUrl="~/Default2.aspx" Text="Button" />
      <asp:TextBox ID="TextBox1" runat="server"></asp:TextBox></div>
     </form>
    </body>
    </html>
   

What happens here is that Asp.Net looks at the PostBackUrl property of the button and understands that form needs to be submitted to another form. To achieve this, it emits a javascript function called WebForm_DoPostBackWithOptions that makes sure that the form is correctly submitted to another page. To further understand what I am saying, look at the source for your page (right click, view source). Here's what our button looks like in actual HTML :

     <input type="submit" name="Button1" value="Button" onclick="javascript:WebForm_DoPostBackWithOptions(new WebForm_PostBackOptions(&quot;Button1&quot;, &quot;&quot;, false, &quot;&quot;, &quot;Default2.aspx&quot;, false, false))" id="Button1" />
   

Now to understand fully how Asp.Net posts back to another form, we must first understand the mechanism Asp.Net uses to postback a form. If you carefully look at the above HTML source for the button, you will see that onclick, the button calls a javascript method called [WebForm_DoPostBackWithOptions]. This method comes out of the box with Asp.Net. To view it's contents, we need to find the Javascript file to which the method belongs to. If you glance at the HTML source of the page, you will see that there is a file called WebResource.axd included with your page :

    <script src="/WebSites1/WebResource.axd?d=wKUbMd8wFV3uZ8WZXaQryA2&amp;t=633245101798773778" type="text/javascript"></script>
   

To look at the actual webresource.axd, do this : Internet Explorer --> Tools --> Settings --> View Files. This will open up a folder, where there will be one or many instances of WebResource.axd. This is a standard Javascript file that contains common Asp.Net page related functionality.

You can go through the WebForm_DoPostBackWithOptions method in the file to understand the exact mechanism for postback. Specifically, note how the [action] property of the webform is changed to the other page. Upon form submission, Asp.Net posts the form to the other page, instead of doing a normal page postback to itself. Upon postback, even though the form posts to another page, the original page's page lifecycle executes upto the [OnLoadComplete] event. After that, Asp.Net transfers the page execution to the other page (I assume it does a Server.Transfer), and also sets the IsCrossPagePostback property for the next page. Once on the other page, it continues the normal page lifecycle of the other page, and renders on page.

All this can get a bit confusing, so let's go through the entire thing again :

1. The first page is Page_A. User clicks a button that has the PostBackURL property set to Page_B.

2. Upon the button click, the [WebForm_DoPostBackWithOptions] javascript method is called. This method is part of the WebResource.axd file.

3. This method sets the form's [action] property to the Page_B, and posts the form.

4. Page_A page lifecycle starts executing upto the OnLoadComplete event, after which Asp.Net transfers execution to the Page_B, also setting Page_B's [IsCrossPagePostback] property on the way. Also, Page_B's PreviousPage property is set to the Page instance object of Page_A. (You know that each page is compiled into an object that derives from System.Web.UI.Page, right. That's the Page Instance Object I was referring to.)

5. Page_B page now starts executing it's normal page lifecycle. Since PreviousPage refers to the instance of Page_A, you can access any public property on the page, any web control on the page, infact, you can call public methods on Page_A.

6. Page_B now executes its normal page lifecycle, accessing public properties/methods/controls on Page_A if required, and renders it's HTML.

I would have loved to explain how I figured out all this internal stuff about Cross Page postbacks, but can't for "lack of space" (it's more like "lack of desire to keep on drooling on the same thing" ;) ), but you can take a look at the source code right here I used to figure out what I did. You can put this code into Debug, setting breakpoints at various key methods to understand how it works.

An oh yes, do remember that you can access 1. Public Properties, 2. Page Controls, 3. Static Variables, 4. Call public methods, using the PreviousPage property. So go ahead and play around with this powerful feature, and find interesting ways to use it in your Asp.Net WebApps.