Wednesday, January 25, 2012

Working with XMPP in .Net using agsXMPP SDK

XMPP

The Extensible Messaging and Presence Protocol (XMPP) is an open technology for real-time communication, which powers a wide range of applications including instant messaging, presence, multi-party chat, voice and video calls, collaboration, lightweight middleware, content syndication, and generalized routing of XML data.

How to work with XMPP in .Net?

agsXMPP is a SDK / library for the eXtensibleMessaging and Presence Protocol (XMPP) protocol written in managed C# dedicated to .NET and Mono technologies. The SDK is released as open source.

SDK could be used for XMPP client, server and component development.

SDK Download Path: http://www.ag-software.de/download-directory/

Here is a small sample how easy you can login to a XMPP Server.

a) I tried Connecting to my Gtalk Account through Jabber Server and tried Changing the Status Message Dynamically(Eg:Showing a CountDown for NewYear).

b) Sending auto reply to contacts in Gtalk while away.

3) Showing Current cricket live Score in your status message.

objXmpp = new agsXMPP.XmppClientConnection();

agsXMPP.Jid jid = null;

jid = new agsXMPP.Jid("my gmail id");

objXmpp.Password = "my gmail password";

objXmpp.Username = jid.User;

objXmpp.Server = jid.Server;

objXmpp.AutoResolveConnectServer = true;

objXmpp.Status = "Available J";

try

{

objXmpp.OnMessage += messageReceived;

objXmpp.OnAuthError += loginFailed;

objXmpp.OnLogin += loggedIn;

objXmpp.Open();

}

catch (Exception ex)

{

MessageBox.Show(ex.Message);

}

private void loggedIn(object sender)//On Successful Login to your Account

{

MessageBox.Show("Logged In");

MessageBox.Show(objXmpp.Authenticated.ToString());

}

private void loginFailed(object o, agsXMPP.Xml.Dom.Element el)//On Login Failure

{

MessageBox.Show("Loggin Failed");

}

private void messageReceived(object sender, agsXMPP.protocol.client.Messagemsg)//Handling Auto Reply on Message received

{

string[] chatMessage = null;

chatMessage = msg.From.ToString().Split('/');

agsXMPP.Jid jid = null;

jid = new agsXMPP.Jid(chatMessage[0]);

agsXMPP.protocol.client.Message autoReply = null;

autoReply = new agsXMPP.protocol.client.Message(jid, agsXMPP.protocol.client.MessageType.chat, "This is Auto reply");

objXmpp.Send(autoReply);

}

private void StatusTimer_Tick(object sender, EventArgs e)//To Show a Countdown

{

TimeSpan span = TargetDate.Subtract (System .DateTime ..Now );

string status = "Days More for My Bday:DD" + span.Days.ToString() +"hh" + span.Hours.ToString() + "mm:" + span.Minutes.ToString() + "ss:" + span.Seconds ..ToString();

Presence p = new Presence(ShowType.chat, status);

p.Type = PresenceType.available;

objXmpp.Send(p);

}

//To Get Live Cricket Score from a RSS Feed

private void GetLiveCricketScore()

{

try

{

// create here table and columns that will be used

// for storing items from the xml file for later binding into grid

DataTable dtable = new DataTable();

dtable.Columns.Add(new DataColumn("SlNo"));

dtable.Columns.Add(new DataColumn("title"));

dtable.Columns.Add(new DataColumn("link"));

// fetch webrequest. Here, give the path of the location where rss feed is stored.

//WebRequest WebReq = WebRequest.Create("http://static.cricinfo.com/rss/livescores.xml");

WebRequest WebReq =WebRequest.Create(" http://static.cricinfo.com/rss/livescores.xml ");

// get webresponse from the webrequset

WebResponse webRes = WebReq.GetResponse();

// use stream to stremline the input from from webresponse.

Stream rssStream = webRes.GetResponseStream();

// Create new xml document and load a XML Document

// with the strem.

XmlDocument xmlDoc = new XmlDocument();

// loads the url from the stream

xmlDoc.Load(rssStream);

// use XmlNodeList to get the matching xmlnodes from the xmldocument

XmlNodeList xmlNodeList = xmlDoc.SelectNodes("rss/channel/item");

// create array of the object for creating the row

object[] RowValues = {"", "", "" };

// Make a Loop through RSS Feed items

for (int i = 0; i < xmlNodeList.Count; i++)

{

XmlNode xmlNode;

RowValues[0] = i;

xmlNode = xmlNodeList.Item(i).SelectSingleNode("title");

if (xmlNode != null)

{

RowValues[1] = RemoveDigitsAndSpecialCharacters(xmlNode.InnerText);

}

else

{

RowValues[1] = "";

}

xmlNode = xmlNodeList.Item(i).SelectSingleNode("link");

if (xmlNode != null)

{

RowValues[2] = RemoveDigitsAndSpecialCharacters(xmlNode.InnerText);

}

else

{

RowValues[2] = "";

}

// creating datarow and add it to the datatable

DataRow dRow;

dRow = dtable.Rows.Add(RowValues);

dtable.AcceptChanges();

}

cmbCurrentMatches.DataSource = dtable;

cmbCurrentMatches.DisplayMember = "title";

}

catch (WebException)

{

MessageBox.Show("Not Able to Access Cricket Live Feeds");

}

catch (XmlException)

{

MessageBox.Show("The received Feed was not in a proper format");

}

}

Tuesday, January 24, 2012

Export Data from SqlServer to FlatFile along with Column Header using xp_cmdshell-BCP

This Export is achieved using xp_cmdshell commands.
Step1:

To enable xp_cmdshell in sqlserver execute the below query:

EXEC sp_configure 'show advanced options', 1
GO
reconfigure
EXEC sp_configure 'xp_cmdshell', 1
GO
reconfigure

Step2:
Configure other details to connect sqlserver in the stored procedure variable initialization part and

Execute below stored procedure which takes four input parameter(as per my requirement).

---INPUT PARAMETERS
@tObjectName AS nVARCHAR(255)--View/Table Name , “DBName..ViewName”
,@tColumnName AS nVARCHAR(255)--Column Name for Filtering
,@tDateValue AS DATETIME --Value for Filtering
,@tOutputFileName AS nVARCHAR(255)--Output File Name

--STORED PROCEDURE
--=============Testing==========================

--EXEC [Sproc_TPS_ExportDataToFlatFile] 'DBName..ViewName','View Column Name','Data Value','FileName'

--EXEC [Sproc_TPS_ExportDataToFlatFile] 'tlmain..view_tblcase','dcreatedat','12/27/2011','dump.txt'

--===============================================

--Note:Folder Path has to be created

GO

if exists (SELECT * from dbo.sysobjects where id = object_id(N'[dbo].[Sproc_TPS_ExportDataToFlatFile]') and OBJECTPROPERTY(id, N'IsProcedure') = 1)

drop procedure [dbo].[Sproc_TPS_ExportDataToFlatFile]

GO

CREATE PROCEDURE [dbo].[Sproc_TPS_ExportDataToFlatFile]

@tObjectName AS nVARCHAR(255)

,@tColumnName AS nVARCHAR(255)

,@tDateValue AS DATETIME

,@tOutputFileName AS nVARCHAR(255)

AS

BEGIN

DECLARE @nObjectID AS INT

DECLARE @tsql AS varchar(8000)

DECLARE @tServerInstanceName AS nVarchar(255),@tUSerID AS nVarchar(255),@tPassword AS nVarchar(255)

,@tHeaderDelimiter AS nVarchar(10),@tViewName AS nVarchar(255),@tOutputFilePath AS nVarchar(MAX)

,@tTempFilePath AS nVarchar(MAX),@tTempString AS nVarchar(255),@tColumnList AS nVarchar(MAX)

,@tDelimiter AS nVarchar(10)

DECLARE @dtCurrentDate As DateTime

SET @tServerInstanceName='ServerName/InstanceName'

SET @tUSerID='UserID'

SET @tPassword='Password'

SET @tHeaderDelimiter='^|'-- ^ character is prefixed since pipe is an internal command for cmd

SET @tDelimiter='|'

SET @tOutputFilePath='C:\'+@tOutputFileName--Output Path

SET @tTempFilePath='C:\Temp.txt'--This file gets deleted at the end of export

IF EXISTS(SELECT * FROM INFORMATION_SCHEMA .COLUMNS WHERE TABLE_NAME =@tViewName AND COLUMN_NAME =@tColumnName)

BEGIN--2

SELECT @tColumnList=COALESCE(@tColumnList + @tHeaderDelimiter, '')+ COLUMN_NAME FROM INFORMATION_SCHEMA .COLUMNS WHERE TABLE_NAME =@tViewName

--print @tColumnList

SELECT @tsql ='echo ' + @tColumnList + ' > ' + @tOutputFilePath--Copy Header columns to output Flatfile

exec xp_cmdshell @tsql

--export Data to temp file

SELECT @tsql = 'bcp "SELECT * FROM '+@tObjectName+' WHERE '+@tColumnName+' > '''+CAST(@tDateValue AS nVarchar(255))+'''" queryout '+@tTempFilePath+' -c -t"'+@tDelimiter+'" -r\n -U'+@tUSerID+' -P'+@tPassword+' -S'+@tServerInstanceName

--print @tsql

exec xp_CmdShell @tsql

--Copy Data to Output File

SELECT @tsql ='type ' + @tTempFilePath+ ' >> ' + @tOutputFilePath

exec xp_cmdshell @tsql

--Delete Temp File

SELECT @tsql ='del '+@tTempFilePath

exec xp_cmdshell @tsql

END--2

END

GO

Thursday, January 5, 2012

OPEN URLs IN SAME IE INSTANCE,VBA

How i came into .Net?

Hi all, this is my first technology blog and my first post i would like to share my knowledge gathered in .Net technology through this blog.

While drafting this first post i had couple of questions to myself ‘How did I came into this technology?’, ‘How did I got addicted to this technology?’

I could still remember the classic Microsoft Software development package VBA(VB6.0) which I studied and got used in my school days is the stating place.

I started developing small application in VBA like simple calculator, Notepad, Custom web browser, bill payments, payroll management etc. and more small applications which later driven me to this great platform called .Net.

I believe still VBA is widely used and developers exists working on VBA:)

Even I too got a recent requirement which I have to accomplish in VBA. WOW I am back to you VBA.

But the requirement was little crazy, do let me explain.

I am happy to start my post on VBA.

OPEN URL IN SAME IE INSTANCE IN VBA

Develop a VB6.0 .dll which has class modules with Custom events on implementing an interface from a referred third party .dll.

Assume one of the event is OnMenu_Click(),which has my custom code to handle specific customization on a Menu clicked from third party application.

There can be as many class modules to the count of menus in third-party application.

When a menu is clicked the applications has to open a Pop-up Internet Explorer Browser and navigate to a specific URl assigned for the menu.

Application has to maintain the same IE instance for all the fore coming menu clicks and navigate new URLs instead of opening new IE instance.

I Used SHDocVw.InternetExplorer WebBrowserObject

Since I was using Modules,Class Modules I was not able to have the WithEvents and capture the OnQuit(),Since WithEvents can be applicable only for Object Modules,Finally I decided to handle it programically as below.

Hope this would help someone met with same scenario.

##########################################################

Private objIEWindow As SHDocVw.InternetExplorer

--------------------------------------------------------------

Public Sub LaunchURLinSameIE(ByVal szURLtoLaunch As String)

Dim browserFlag As Boolean

browserFlag = True

If objIEWindow Is Nothing Then 'Create a new IE instance for the first time

Set objIEWindow = New SHDocVw.InternetExplorer

'MsgBox ("first")

objIEWindow.navigate szURLtoLaunch, , ""

objIEWindow.AddressBar = False

objIEWindow.MenuBar = False

' objIEWindow.StatusBar = False

objIEWindow.ToolBar = False

objIEWindow.Visible = True

Else

Dim ShellApp

Set ShellApp = CreateObject("Shell.Application")

Dim ShellWindows

Set ShellWindows = ShellApp.Windows()

Dim i

For i = 0 To ShellWindows.Count - 1'Loop through shell window to ensure if the IE instance is available

If Not ShellWindows.Item(i) Is Nothing Then

If InStr(UCase(ShellWindows.Item(i).FullName), UCase("iexplore.exe")) <> 0 Then

'MsgBox ("is ie")

Set objIEWindow = ShellWindows.Item(i)

If objIEWindow.AddressBar = False And objIEWindow.MenuBar = False And objIEWindow.ToolBar = False Then

browserFlag = False

GoTo SameBrowserInstance:

Else

browserFlag = True

End If

Else

browserFlag = True

End If

End If

Next

If browserFlag = True Then

Set objIEWindow = New SHDocVw.InternetExplorer

'MsgBox ("second")

objIEWindow.navigate szURLtoLaunch, , ""

objIEWindow.AddressBar = False

objIEWindow.MenuBar = False

' objIEWindow.StatusBar = False

objIEWindow.ToolBar = False

objIEWindow.Visible = True

Else

SameBrowserInstance:

'MsgBox ("third")

objIEWindow.Navigate2 (szURLtoLaunch)

End If

End If

End Sub

 
Twitter Bird Gadget