Sunday, October 28, 2012

Google Picasa Web API
How to pull thumbnail of all photos from Picasa web Album?

Picasa Album is a great service provided by Google to store and share online images. There are many plug-in and interfaces available to access images stored in Picasa web album. These images can be accessed from many devices such as cellphone, iPhone, tablets, web applications, desktop applications and many more.

Google has provided Google Data API which can be used by developer community to develop applications and integrate it with Picasa web album. Developers need to first download the Google Data API library to interact with Google services such as Picasa web albums, Google Docs, Google BigQuery, YouTube and other Google provided services.

On this site, I have published articles on using Google Data API with .NET and accessing Google services such as Picasa web Albums, Google Analytics, Google Spreadsheet, Google BigQuery. This article is another extension to the same set of articles.

In this post we will learn to pull thumbnails of all photos stored in a particular Picasa web album using .NET. First we will interact with Google services by providing our Google credentials and then pull the albums stored with the specific Google account. We will then pull the thumbnails of all images stored in a specific web album

To start with, I have designed following form in Visual Studio 2008. The form has two text boxes to enter Google credentials i.e. Google account and password. The login button has code logic to connect to Google Picasa service by supplying the Google credentials entered into the two text boxes.

Get Album List button has code logic to pull all albums name and id stored with the Google account. The lists of Albums are shown in ListView1 control

ListView1 control has an event SelectedIndexChanged which contains the code logic to access all the images stored in the selected web album. Each of the image is than loaded into ImageList1 control. Once all the images are read and added into ImageList1, the thumbnail of all images are shown in ListView2 control.

Showing thumbnail of all images stored web album "Harshita".

Showing thumbnail of all images stored in web album "Canada.Halifax.2010"

The code snippet on this form is following: 

using System;
using System.Collections.Generic;
using System.ComponentModel;
using System.Data;
using System.Drawing;
using System.Linq;
using System.Text;
using System.Windows.Forms;
using Google.GData.Client;

using Google.GData.Photos;
using Google.GData.Extensions;
using Google.GData.Extensions.Location;
using Google.Picasa;
using System.IO;

namespace TestDOTNET
{
    public partial class Form1 : Form
    {
        String myAlbumID;
        PicasaService myPicasa;
        AlbumQuery myAlbumQuery;
        PicasaFeed myPicasaFeed;

        public Form1()
        {
            InitializeComponent();
        }

        private void button1_Click(object sender, EventArgs e)
        {
            //Code logic to connect with Google Picasaservice with the supplied credentials
            myPicasa = new PicasaService("Vikash-Test-Picasa");
            myPicasa.setUserCredentials(textBox1.Text, textBox2.Text);
            label3.Text = "Click on Get Album List";
        }

        private void button2_Click(object sender, EventArgs e)
        {
            //Code logic to pull Album names and ID
            listView1.Columns.Add("Album Title");
            listView1.Columns.Add("Album ID");            
            listView1.FullRowSelect = true;

            myAlbumQuery = new AlbumQuery(PicasaQuery.CreatePicasaUri(textBox1.Text));
            myAlbumQuery.KindParameter = "album";

            myPicasaFeed = myPicasa.Query(myAlbumQuery);
            foreach (PicasaEntry p in myPicasaFeed.Entries)
            {

                AlbumAccessor myAlbum = new AlbumAccessor(p);
                listView1.Items.Add((new ListViewItem(new string[] { myAlbum.AlbumTitle.ToString(), myAlbum.Id.ToString() })));
            }


        }

        private void listView1_SelectedIndexChanged(object sender, EventArgs e)
        {
            //Code logic to pull all thumbnails of selected web album
            myAlbumID = "";
            imageList1.Images.Clear();
            listView2.Clear();
            ListView.SelectedListViewItemCollection AlbumList = listView1.SelectedItems;

            foreach (ListViewItem item in AlbumList)
            {
                myAlbumID = item.SubItems[1].Text.ToString();

            }

            if (myAlbumID.Length > 0)
            {
                PhotoQuery myPhotoQuery = new PhotoQuery(PicasaQuery.CreatePicasaUri(textBox1.Text, myAlbumID));

                PicasaFeed myPicasaFeed = myPicasa.Query(myPhotoQuery);
                foreach (PicasaEntry p in myPicasaFeed.Entries)
                {
                    string firstThumbUrl = p.Media.Thumbnails[0].Attributes["url"] as string;
                    imageList1.Images.Add(LoadMyImage(firstThumbUrl));               
                }
                listView2.SmallImageList = imageList1;                

                listView2.View = View.LargeIcon;
                this.imageList1.ImageSize = new Size(32, 32);
                listView2.LargeImageList = this.imageList1;
                
                for (int j = 0; j < this.imageList1.Images.Count; j++)
                {
                    ListViewItem item = new ListViewItem();
                    item.ImageIndex = j;
                    listView2.Items.Add(item);
                }
            }
        }

        private Image LoadMyImage(string url)
        {
            //Code logic to download all thumbnails of images
            System.Net.WebRequest request = System.Net.WebRequest.Create(url);

            System.Net.WebResponse response = request.GetResponse();
            System.IO.Stream responseStream = response.GetResponseStream();

            Bitmap bmp = new Bitmap(responseStream);

            responseStream.Dispose();

            return bmp;
        }

    }
}

So this is how you can download the thumbnails of all images stored in a Picasa web album. Just in case you want to download the source code please visit the Download Zone.

Labels: ,

Saturday, October 27, 2012

SSIS: How to export Ms-Excel data into PDF?

Today while exploring SSIS, I learn a very good feature of exporting Ms-Excel data to PDF. In case our requirement is to print the Ms-Excel data into PDF and mail it to someone SSIS provides very good feature with Script Task.

For demonstration purpose let us consider we have following Ms-Excel file. We will export this excel file into PDF.

In SSIS designer, I have added one Script Task.

I have added one Package level variable with data type as string and store the Ms-Excel file path and file name into it.

In Script Task, I have added following VB.NET code to export the Ms-Excel file to PDF. In this code snippet, I am using reference of Microsoft.Office.Interop.Excel

The code snippet in Script Task is following:

Imports System
Imports System.Data
Imports System.Math
Imports Microsoft.SqlServer.Dts.Runtime
Imports Excel = Microsoft.Office.Interop.Excel

<System.AddIn.AddIn("ScriptMain", Version:="1.0", Publisher:="", Description:="")> _
<System.CLSCompliantAttribute(False)> _
Partial Public Class ScriptMain
    Inherits Microsoft.SqlServer.Dts.Tasks.ScriptTask.VSTARTScriptObjectModelBase

    Enum ScriptResults
        Success = Microsoft.SqlServer.Dts.Runtime.DTSExecResult.Success
        Failure = Microsoft.SqlServer.Dts.Runtime.DTSExecResult.Failure
    End Enum
    

    Public Sub Main()

        Dim myExcelApp As New Excel.Application
        Dim myExcelWb As Excel.Workbook
        myExcelWb = myExcelApp.Workbooks.Open(Dts.Variables("ExcelfilePath").Value, , True)
        myExcelWb.ExportAsFixedFormat(Excel.XlFixedFormatType.xlTypePDF, Dts.Variables("ExcelfilePath").Value)
        myExcelWb.Close()
        myExcelApp.Quit()

        Dts.TaskResult = ScriptResults.Success
    End Sub

End Class

After running the SSIS package, the SSIS package runs successfully and it exported the data into PDF.

So just in case you requirement is to export Ms-Excel data into PDF, you can achieve this without using any third part tool.

Labels: ,

Friday, October 26, 2012

How to Run SSIS Package programmatically using Web Applications?

SSIS packages can be run from many platforms. SQL Server Agent, BIDS designer, Scheduler Jobs are the most used and familiar platforms. There are other platform that we can use to run SSIS packages such as Ms-Excel, .NET windows form, web application, WPF applications and web services. In one of the previous post, I had written about how to run SSIS package from Ms-Excel. In this post we will learn how to run SSIS package using web application.

For demonstration purpose, I have created a sample SSIS package. This SSIS package loads data from employee table to newEmployee SQL Table. We have following data into tblEmployee.

Execute SQL Task truncates all the rows from the newEmployee table. The Data Flow task reads all the data from OLE DB Source (employee table) and dumps it into OLE DB Destination (newEmployee table).

This package runs fine inside BIDS designer. We will try to run this package programmatically using a .NET based web application.

Microsoft has provided a Dynamic Link Library (DLL) Microsoft.SqlServer.Dts.Runtime that we can use to run our SSIS package from .NET environment. For demonstration purpose, I have created a web application and added a form in Visual Studio 2008. I have added a button and one label on the web form. The label will show the result status of the SSIS package execution.

I have added reference of Microsoft.SqlServer.Dts.Runtime dll into project. This form contains following code logic.

using System;
using System.Collections;
using System.Configuration;
using System.Data;
using System.Linq;
using System.Web;
using System.Web.Security;
using System.Web.UI;
using System.Web.UI.HtmlControls;
using System.Web.UI.WebControls;
using System.Web.UI.WebControls.WebParts;
using System.Xml.Linq;
using Microsoft.SqlServer.Dts.Runtime;

namespace WebApplication1
{
    public partial class _Default : System.Web.UI.Page
    {
        protected void Button1_Click(object sender, EventArgs e)
        {
            string strpackageName;
            Package SSISpkg;
            Microsoft.SqlServer.Dts.Runtime.Application Myapp;
            DTSExecResult dtresult;
            strpackageName = @"E:\DOTNET\Employees\Employees\Package.dtsx";
            Myapp = new Microsoft.SqlServer.Dts.Runtime.Application();
            SSISpkg = Myapp.LoadPackage(strpackageName, null);
            dtresult = SSISpkg.Execute();
            Label1.Text = "Result for SSIS Pacage Execution is " + dtresult.ToString();
        }

    }
}

After setting up the code with Visual Stuio web application. I run the web application and clicked on RUN SSIS Package button. The package executed successfully and showed the rest status as Success in the label.

I went to check my newEmployee table and as expected the data were loaded into the table.

The above example can be used if our SSIS package has been stored locally in a file system. If your SSIS package is stored in a remote location or file and print server, you can use that path, the only thing you need to consider is your web application hosted machine can access that file path. The account that is running the web application under IIS has access to the file system path.

Labels:

Tuesday, October 23, 2012

Google Picasa Web API
How to pull Album data size from Picasa web Albums?

On this site I have published many post on how to use Google Picasa web API with .NET. This article is another extension to the set of articles on Picasa Web API with .NET.

All of us love to share or store our favourite pictures on various Social Media sites. Google Picasa Web is one of the popular media to store photos online. With each photo that we stored in Picasa web album, we consumed some space. Over the time as we add more and more photos to our web album, the album size is grown.

With .NET if your requirement is to pull each of the web album data size, you can use Google Data API and read this information. In this article we will learn how we can pull the data size of each Picasa web album using .NET.

The prerequiste for this is you must have Google Data API installed on your sytem and you can use any of your visual studio IDE 2005/2008/20010 or 2012. You also need an active Internet connection as you will be pulling online information.

For demonstration purpose I have created following form in Visual Studio 2008.

I have two text boxes to take Google credentials as input. Once the use enter their Google credentials, they need to click on Login. The code logic written on Login button create an instance of Picasa web service and pass on the credentials to Picasa web service. If the Google credentials are correct a message appears on the form to ask user to clikc on Album Size.

Once the user click on Album Size button the code logic written on this button pulls all the web albums and their respective data size . The album name and their size will be shown in the list box. Google Picasa feed provides data size in bytes. So if you need to convert the data size from bytes to Megabytes or Gigabytes you can do it.

The .NET code logic on this form is following:

using System;
using System.Collections.Generic;
using System.ComponentModel;
using System.Data;
using System.Drawing;
using System.Linq;
using System.Text;
using System.Windows.Forms;
using Google.GData.Client;

using Google.GData.Photos;
using Google.GData.Extensions;
using Google.GData.Extensions.Location;
using Google.Picasa;
using System.IO;

namespace TestDOTNET
{
    public partial class Form1 : Form
    {
        String myAlbumID;
        PicasaService myPicasa;
        AlbumQuery myAlbumQuery;
        PicasaFeed myPicasaFeed;

        public Form1()
        {
            InitializeComponent();
        }

        private void button1_Click(object sender, EventArgs e)
        {


            myPicasa = new PicasaService("Vikash-Test-Picasa");
            myPicasa.setUserCredentials(textBox1.Text, textBox2.Text);
            label3.Text = "Click on Album Size to get albums storage size.";
        }

        private void button2_Click(object sender, EventArgs e)
        {
            listView1.Columns.Add("Album Title");
            listView1.Columns.Add("Album Size");

            listView1.FullRowSelect = true;


            myAlbumQuery = new AlbumQuery(PicasaQuery.CreatePicasaUri(textBox1.Text));
            myAlbumQuery.KindParameter = "album";
            

            myPicasaFeed = myPicasa.Query(myAlbumQuery);
            foreach (PicasaEntry p in myPicasaFeed.Entries)
            {

                AlbumAccessor myAlbum = new AlbumAccessor(p);
                listView1.Items.Add((new ListViewItem(new string[] { myAlbum.AlbumTitle.ToString(), myAlbum.BytesUsed.ToString() })));
            }


        }
    }
}
Labels: ,

Friday, October 12, 2012

Google BigQuery Aggregate functions

Google BigQuery has provided aggregate functions that are very useful when you are reading data from Google Big Table. The list of Aggregate functions includes Avg, Count, Max, Min and Sum which are very common. If you have used Ms-Excel spreadsheet or Google Spreadsheet or if you are familiar with any DBMS or RDBMS system, you may have used them at some point of time.

I have following Big Table with some sample rows into it. We will apply Aggregate functions on them. I have issued a SELECT query to see what data I have in this Big Table and its column.

Count: Count Function, as it name suggest will give count of total rows in the Big Table. So, I issued a COUNT(*) statement and it returned me a count 19.

Count(Distinct, field,[n]): Count(Distinct, field,[n]) function, will give count of total unique rows in the Big Table. In my Big Table Employee there are three unique departments, so I issued a COUNT (DISTINCT [DeptCode]) statement and it returned me a count 3.

GROUP_CONCAT('str'): GROUP_CONCAT('str') function concatenates the group values into one separating each value by a comma(,). I wanted to show each department and their concerned employees so, I issued a Group_CONCAT statement. They return each department and their employees. Do you remember how much code you had to write to achieve the same with SQL Server or Oracle or other RDBMS. I appreciate Google BigQuery has introduced this function.

STDDEV: This aggregate function returns Standard deviation of a particular column.

Variance: This function returns Variance in a particular column of Big Table.

SUM: As its name suggests it returns the sum total of a particular numeric column.

The list of Google Big Query Aggregrate function includes following functions

  • Avg
  • Count
  • Count(Distinct, field,[n])
  • GROUP_CONCAT('str')
  • QUANTILES(expr[, buckets])
  • STDDEV(numeric_expr)
  • VARIANCE(numeric_expr)
  • LAST(field)
  • MAX(field)
  • MIN(field)
  • NTH(n, field)
  • SUM(field)
  • TOP(field, [max_records], [multiplier])


Labels: ,

Thursday, October 11, 2012

MS-Excel Articles

Ms-Excel Tips
Labels:

Wednesday, October 10, 2012

SQL Server Integration Services (SSIS) and Ms-Excel

Labels:

SSIS.FINDSTRING function

SQL Server Integration Services has provided many useful functions to the developers. FINDSTRING is one of the valuable string functions that come handy in many situations. Technically, this function is used to find occurrence of a specific character or string in a given series of string.

This function only returns integer values. It can return a 0 or any integer value greater than 0. This integer value is character index location in the string. It treats each character of the string as one index item. So, for example string “Hello”, it has five index items each storing one character. This function has following syntax.

FINDSTRING(StringValueToLookInto, StringValueToLookFor, Occurence)

This function has three parameters.

The first parameter is string value where this function will look into. It could be a column of a table or some expression or some hard coded string values. It is the string value which will be searched to find out specific string/character by FINDSTRING function.

The second parameter is string value that this function will look for. It could be some expression or some hard coded values. This is your specific string/character that you want to search.

The third parameter is numeric value where we specify how many occurrences till we want to search for.

An example will make it clear.

Let us say we want to search for string “hello” in string “You should say hello to people when you meet”. Then we should write

FINDSTRING(“You should say hello to people when you meet”, “hello”, 1)

This will return value 16, which is the place from where the string “hello” starts.

When this function returns 0, it means that the function did not find any character/string that matches. Following example will return 0 because the string value “bye” is not found.

Let us take another example. For demonstration, let us say we have following data in excel spreadsheet. We want to figure out which company sells DVD from the list.

I created one SSIS package and added a Data Flow Task. I added a Derived Column where we will use and evaluate FINDSTRING function.

I added a column FindProducts and used FINDSTRING function in the expression. We know that FINDSTRING function returns a numeric value so we want to evaluate does it return any value that is greater than 0, if yes, we want to display “Company Sells DVD”. If the FINDSTRING () function returns 0, we want to display “Company Donot sell DVD”.

FINDSTRING(Produts,"DVD",1) > 0 ? "Company Sells DVD" : "Company Donot sell DVD"

Running the package returns following values in FindProducts column. There were three companies who were selling DVD and one company that was not selling. So the result was as expected.

The point to remember about FINDSTRING function is that it only takes string values as its first and second parameter i.e. it only searches inside a string. So if we have a situation where we want to search in a date or integer or other data type columns, we need to explicitly convert that to String.

Following example will show, how we can use FINDSTRING function with a date. Let say we want to find out what day is today if today is Saturday or Sunday we want to display “Weekend day WOW” else we want to display Monday to Friday as “Work Day”.

(FINDSTRING((DT_STR,20,1252)DATEPART("dw",GETDATE()),"1",1) == 1 || FINDSTRING((DT_STR,20,1252)DATEPART("dw",GETDATE()),"7",1) == 1) ? "Weekend day WOW" : "Work Day"

Thanks for reading till this point.



Related Article:
Labels: ,

Tuesday, October 9, 2012

Miscellaneous Articles

Labels:

SSIS Articles@SinghVikash.blogspot.com

Labels:

Wednesday, October 3, 2012

Tips@SinghVikash.blogspot.com

GMail Tips
Facebook Tips
Blogger Tips
YouTube Tips
Google Search Tips
Miscellaneous Tips
Labels:

Tuesday, October 2, 2012

Project Management

Download PMP - ITTO Table
My Journey to PMP




Project Integration
What is a Project Charter?
What is a Project Management Plan?
What are Organizational Structure?
What is Opportunity Cost?
Organizational Process Assets (OPA) vs Enterprise Environmental factor (EEF)
Project Planning
What is Brainstorming?
Project Cost
PM: Do you know your Project Cost?
PM: How accurate is your Project Cost Estimates?
Project Team
How to calculate Team utilization index?
How to motivate your Project Team?
Project Communication
PM: Do you know your Communication Channel?
Project Quality
Control Charts Vs Run Charts
What is XMR Chart?
Procurement Management
What is Plan Procurement Management?
What is Conduct Procurement Management?
Labels:
Subscribe to: Posts (Atom)

Popular Posts

Real Time Web Analytics