PIVOT/UNPIVOT expression in SQL Server.

Pivot expression in SQL converts values returned by a query into columns. For example in below example, the temp table contains revenue data for different months for companies. If the result has to be displayed with each month as columns , then it can done easily using PIVOT expression.

---

Create temp table to store data

CREATE table #tmp (comp_id int,comp_name varchar(20), [month] int,rev int)

GO

insert into #tmp values (200,'comp1',1,32144)
insert into #tmp values (200,'comp1',2,56234)
insert into #tmp values (200,'comp1',3,1000)
insert into #tmp values (211,'comp2',1,2364)
insert into #tmp values (211,'comp2',2,8762)
insert into #tmp values (211,'comp2',3,7644)
insert into #tmp values (211,'comp2',3,1111)
insert into #tmp values (211,'comp2',3,1111)
insert into #tmp values (213,'comp3',3,1111)

Now if we want to show the month as columns (like ‘1’,’2’,’3’ etc) we can use PIVOT expression as given below

SELECT * FROM #tmp t PIVOT 
 (SUM(rev) FOR [month] IN ([1],[2],[3])) pvt

UNPIVOT

We can also UNPIVOT the result as given below

/*get  ‘pivot’ data into another temp table #tmp2*/

SELECT * INTO #tmp1 FROM #tmp t PIVOT 
 (SUM(rev) FOR [month] IN ([1],[2],[3])) pvt

/*UNPIVOT the data*/
SELECT * FROM #tmp1 t  UNPIVOT 
 (rev FOR [month] IN ([1],[2],[3])) pvt

Generating Excel file using .NET

Given below is an example of how to create excel file using .NET (C#)

Excel has to be installed on the machine for the below example to work, since EXCEL COM object is used to generate excel file

---

1. Create a (C#) console application in Visual Studio

2. Add reference to “Microsoft.Office.Interop.Excel”

3. import the required namespaces

using Microsoft.Office.Interop.Excel;
using System.Reflection;
using System.Text;
using System.Data;
using System.Runtime.InteropServices;

4. The function to create EXCEL is given below

public void WriteToExcel(System.Data.DataTable dtResult, string strFileName, string strDirectoryName)
{
   
    Application xlApp = default(Application);
    Workbook xlWorkBook = default(Workbook);
    Worksheet xlWorkSheet = default(Worksheet);

    string fName = strFileName = strDirectoryName + strFileName;

    try
    {
        object misValue = Missing.Value;
        xlApp = new ApplicationClass();
        xlWorkBook = xlApp.Workbooks.Add(misValue);
        xlWorkSheet = (Worksheet)xlWorkBook.Worksheets.get_Item(1);

        int iRow = 1;
        int iColumn = 1;

        /*Add header to excel File*/
        StringBuilder oBuilder;
        oBuilder = new StringBuilder();
        foreach (DataColumn dc in dtResult.Columns)
        {
            if (dc.ColumnName != null)
            {
                xlWorkSheet.Cells[iRow, iColumn] = dc.ColumnName;
            }
            else
            {
                xlWorkSheet.Cells[iRow, iColumn] = "";
            }

            iColumn = iColumn + 1;
        }


        /*Add data to excel File*/
        foreach (DataRow dr in dtResult.Rows)
        {
            iColumn = 1;
            iRow = iRow + 1;
            oBuilder = new StringBuilder();
            foreach (DataColumn dc in dtResult.Columns)
            {
                if (dr[dc.ColumnName] == null || dr[dc.ColumnName] == DBNull.Value)
                {
                    xlWorkSheet.Cells[iRow, iColumn] = "";
                }
                else
                {
                    xlWorkSheet.Cells[iRow, iColumn] = dr[dc.ColumnName].ToString();
                }
                iColumn = iColumn + 1;
            }
        }

        
        xlWorkBook.SaveAs(fName, XlFileFormat.xlWorkbookNormal, misValue, misValue, misValue, misValue, XlSaveAsAccessMode.xlExclusive, misValue, misValue, misValue, misValue, misValue);
        xlWorkBook.Close(true, misValue, misValue);
        xlApp.Quit();

        Marshal.ReleaseComObject(xlWorkSheet);
        Marshal.ReleaseComObject(xlWorkBook);
        Marshal.ReleaseComObject(xlApp);

        if (xlApp != null)
        {
            Marshal.FinalReleaseComObject(xlApp);
        }

    }
    finally
    {
        if (xlApp != null)
            Marshal.ReleaseComObject(xlApp);
        if (xlWorkBook != null)
            Marshal.ReleaseComObject(xlWorkBook);
        if (xlWorkSheet != null)
            Marshal.ReleaseComObject(xlWorkSheet);

        if (xlApp != null)
        {
            Marshal.FinalReleaseComObject(xlApp);
        }
    }
}

5. Now call the function by passing in the datatable ,excel name and folder path

DataTable dt = new DataTable();
dt.Columns.Add("Name");
dt.Columns.Add("Age");

DataRow dr = dt.NewRow();
dr[0] ="Name1";
dr[1] = "12";
   
dt.Rows.Add(dr);

WriteToExcel(dt, "test.xls", @"Path");

Delegates And Events in .NET

Today I was working on a project which required the use of 'delegates'. I wanted to see the difference in calling a function using delegates and by events; so created a simple example to show the difference in invoking methods using events and delegates.

Delegates are function pointers in .net. The advantage of using delegates is that the calling program can define the function for a specific task. For example, let us consider the logging of message or exceptions in an application. We can just expose a delegate with signature “public delegate void LogMessage (string msg);” and the calling program can decide whether to do the logging in file or database or to display in console/UI.

An event is a message sent by an object to signal the occurrence of an action.Events are raised in response of an user action or it could be program triggered. It normally has the sender and event arguments as input parameters. But for the example below, I am comparing delegate and event calls and not using sender/event argument parameters

---

1) Create a console application in .NET (C#).

2) Create a delegate as given below in 'Program.cs'

public delegate void LogMessage(string msg);

3) Create a class (DelegateTestClass) for delegate test

public class DelegateTestClass
    {
        public LogMessage MessageLogger { get; set; }

        public void PerformTask1()
        {
            MessageLogger("Performing Task1");
        }

        public void PerformTask2()
        {
            MessageLogger("Performing Task2");
        }
    }

4) Create a class for Event test as given below

class EventTestClass
    {
        public event LogMessage MessageLogger;

        public void PerformTask1()
        {
            MessageLogger("Performing Task1");
        }

        public void PerformTask2()
        {
            MessageLogger("Performing Task2");
        }
    }

5) In “Program.cs” create a function to handle the delegate and event

private static void ShowMessage(string strMsg)
        {
            Console.WriteLine(strMsg);
        }

6) Now call the event and delegate from ‘Main’ as given below

static void Main(string[] args)
        {
            DelegateTestClass oDelegateTest = new DelegateTestClass();
            /*Specify the function to handle the message logging*/
            oDelegateTest.MessageLogger = new LogMessage(ShowMessage);

            oDelegateTest.PerformTask1();
            oDelegateTest.PerformTask2();


            EventTestClass oEventTest = new EventTestClass();
            /*Attach function to the event*/
            oEventTest.MessageLogger += new LogMessage(ShowMessage);

            oEventTest.PerformTask1();
            oEventTest.PerformTask2();

            Console.ReadLine();
        }

Convert image to binary and upload to database using ASP.NET

This article is about uploading images to server/database using ASP.NET. For uploading an image to database ,it has to be first converted to binary format. Then it has to be saved to a database table; the table should have a column with data type [image] for storing the binary image data.

Ok, enough of theory. Let us try out an example.

---

1) Create a ASP.NET web application using VS2008/2010

2) In default.aspx provide a input control of type “file” and a button

<input type="file" runat="server" id="photoupload" />
<asp:Button runat="server" ID="btnSave" Text="Save" onclick="btnSave_Click"/>

3) Create a function to convert stream to byte[]

public byte[] StreamToByteArray(Stream input)
{
    byte[] total_stream = new byte[0];
    byte[] stream_array = new byte[0];
    byte[] buffer = new byte[1024];

    int read = 0;
    while ((read = input.Read(buffer, 0, buffer.Length )) > 0)
    {
 stream_array = new byte[total_stream.Length + read];
 total_stream.CopyTo(stream_array, 0);
 Array.Copy(buffer, 0, stream_array, total_stream.Length, read);
 total_stream = stream_array;
    }

    return total_stream;
}

4) In the button click event, write code to get the stream from uploaded image and convert to byte array.(Import System.IO- using System.IO;)

Stream oStream = photoupload.PostedFile.InputStream;
byte[] arr = StreamToByteArray(oStream);
oStream.Close();
oStream.Dispose();

5) Now pass the byte[] data to database

SqlConnection oConnection= new SqlConnection("[connectionstring]”);
SqlParameter oParam1 = new SqlParameter("@data", arr);
oParam1.DbType = System.Data.DbType.Binary;
SqlParameter oParam2 = new SqlParameter("@description", photoupload.PostedFile.FileName);

SqlCommand oCommand = new SqlCommand(“[storedprocname]”);
oCommand.Parameters.Add(oParam1);
oCommand.Parameters.Add(oParam2);

oCommand.CommandType = CommandType.StoredProcedure;
oCommand.Connection = oConnection;
oConnection.Open();
int i = oCommand.ExecuteNonQuery();
oConnection.Close();

Done! Now the image is saved in database.

6) Code snippet for getting the binary data back from database is given below

/*Get binary data into dataset*/
DataSet ds = oDal.GetData("[stored procedure name]" + photoid.ToString());

byte[] arr = (byte[])ds.Tables[0].Rows[0]["data"];
MemoryStream oStream = new MemoryStream(arr);
Response.ContentType = "image/png";
Response.BinaryWrite(arr);

C#.NET and Greenplum

This article is about connecting to greenplum (GP) database using C#.NET. Greenplum DB is based on the open source database PostgreSQL.

Greenplum database is optimized for faster searches. For searching data in tables having millions of rows, Greenplum is really fast.

Given below is a C# example for opening a connection to GP database and executing a search query.

---

1) The first step is to download the dll which connects to GP. It is called Npgsql.dll. It can be downloaded from http://npgsql.projects.postgresql.org/docs/manual/UserManual.html

2) Create a C#.NET console application using Visual Studio.


3) Create a new file for GP connection. Lets name it GPDData.cs

4) Copy the dll to a local folder in the project and add a reference to the dll from project. Or you can register it in Global Assembly Cache using "gacutil".

5) Include the namespace

using Npgsql;

6) In the GPData class create method to execute query in greenplum as given below.

public DataSet FillDataSet(string strCommand,string strConnection)
{
NpgsqlConnection oConnection = new NpgsqlConnection(strConnection);
DataSet ds = new DataSet();

NpgsqlCommand oCommand = new NpgsqlCommand(strCommand, oConnection);
NpgsqlDataAdapter oAdp = new NpgsqlDataAdapter();
oCommand.CommandTimeout = 100;
oAdp.SelectCommand = oCommand;

oConnection.Open();
oAdp.Fill(ds);
CloseConnection();

return ds;
}

public void CloseConnection()
{
if (oConnection.State == System.Data.ConnectionState.Open)
{
oConnection.Close();
}
}

7) Now you can execute query by calling the method as given below.

GPData oData = new GPData();
DataSet ds = oData.FillDataSet([command],[connectionstring]);