Monday, December 21, 2009

How to Get the date-time of last windows shutdown using .NET

The solution is below, DateTime can be achieved  from a byte array with fewer statements using the BitConverter.The following  code will give the correct DateTime from the registry

 public static DateTime GetLastShutdownDate()

    string rKey = @"System\CurrentControlSet\Control\Windows";
    Microsoft.Win32.RegistryKey key = Microsoft.Win32.Registry.LocalMachine.OpenSubKey(rKey);

    string rValueName = "ShutdownTime";
    byte[] val = (byte[]) key.GetValue(rValueName);
    long AsLongValue= BitConverter.ToInt64(val, 0);
    return DateTime.FromFileTime(AsLongValue);

Saturday, December 19, 2009

How to save Gtalk conversion into the database in C# ?

In my recent project i work on a module where we need to track the Gtalk conversion into our sql server database and whenever required we have to show the report. basically you can say that its an application for tracking our employee !!!!!!!
Here is the code from where we are connected to the Gtalk ...
This is a sample of my module in future i will submit the whole code here ..

private void btnStart_Click(object sender, EventArgs e)
User = txtUsername.Text;
Pwd = txtPassword.Text;
jabberClient1.User = User;
jabberClient1.Server = "";
jabberClient1.Password = Pwd;
jabberClient1.AutoRoster = true;
rm = new RosterManager();
rm.Stream = jabberClient1;
rm.AutoSubscribe = true;
rm.AutoAllow = jabber.client.AutoSubscriptionHanding.AllowAll;
rm.OnRosterBegin += new bedrock.ObjectHandler(rm_OnRosterBegin);
rm.OnRosterEnd += new bedrock.ObjectHandler(rm_OnRosterEnd);
rm.OnRosterItem += new RosterItemHandler(rm_OnRosterItem);
rosterCount = 1;
pm = new PresenceManager();
pm.Stream = jabberClient1;
jabberClient1.OnAuthenticate += new bedrock.ObjectHandler(jabberClient1_OnAuthenticate);


Wednesday, December 16, 2009

Ajax BubbleTooltip

I got 40 tool tips from this site and these tool tips are really good
Here is the link from where you can see the demo and download the code

Tuesday, December 15, 2009

How to take ScreenShot in C#

Here by using this code i try to take the Screen Shot of my desktop and it works same as the print screen button does. Here the parameter filename is use for saving the screen shot image in a specified location.

private static Bitmap bmpScreenshot;

private static Graphics gfxScreenshot;

private void TakeScreenShot(string filename)



            bmpScreenshot = new Bitmap(Screen.PrimaryScreen.Bounds.Width, Screen.PrimaryScreen.Bounds.Height, PixelFormat.Format32bppArgb);

            gfxScreenshot = Graphics.FromImage(bmpScreenshot);

            gfxScreenshot.CopyFromScreen(Screen.PrimaryScreen.Bounds.X, Screen.PrimaryScreen.Bounds.Y, 0, 0, Screen.PrimaryScreen.Bounds.Size, CopyPixelOperation.SourceCopy);

            bmpScreenshot.Save(filename, ImageFormat.Png);

            attachedFile = filename;




How to give full access to a folder in C#

How to Give Permissions to a folder ? Here is the Solution
public static void SetPermissions(string dirPath)
DirectoryInfo info = new DirectoryInfo(dirPath);
WindowsIdentity self = System.Security.Principal.WindowsIdentity.GetCurrent();
DirectorySecurity ds = info.GetAccessControl();
ds.AddAccessRule(new FileSystemAccessRule(self.Name,
InheritanceFlags.ObjectInherit |

Monday, November 16, 2009

How to remove Duplicate Records using SQL Server 2005

By using a Common Table Expression (CTE) in SQL Server 2005 we can easily remove duplicate records from a table, even when the table without a primary key.

The windowing function named DENSE_RANK is used to group the records together based on the Symbol, MarketType,Trading_Date , Trading_Day,[Open],High,Low,[Close] and Volume fields, and assign them with a value randomly. This means that if I have two records with the exact same Symbol, MarketType,Trading_Date , Trading_Day,[Open],High,Low,[Close] and Volume values, the first record will be ranked as 1, the second as 2, and so on.

;WITH DupliRows(Symbol, MarketType,Trading_Date , Trading_Day,[Open],High,Low,[Close],Volume, Ranking)




Symbol, MarketType,Trading_Date , Trading_Day,[Open],High,Low,[Close],Volume,

Ranking = DENSE_RANK() OVER(PARTITION BY Symbol, MarketType,Trading_Date , Trading_Day,[Open],High,Low,[Close],Volume ORDER BY NEWID() ASC)

FROM dbo.tbl_Futures ---The Table from where duplicate records will be remove

--Select * from DupliRows
WHERE Ranking >1

Because a CTE acts as a virtual table, We able to process data modification statements against it, and the underlying table will be affected. In this case, We can remove any record from the DupliRows that is ranked higher than 1. This will remove all duplicate records.

Saturday, November 7, 2009

How to find a particular row from a table (SQL Query)

Declare cursor on table ,after that open the cusor and
using "fetch" keyword to retrive any row ,remove the cursor explictly because it is stored in the memory for this use dellocate keyword

Declare fetch_row insensitive scroll cursor for select *
from tbl1 [for{readonly}]

open fetch_row

Fetch absolute 5 from fetch_row

Deallocate fetch_row

It will display 5th row of the tbl1

Friday, November 6, 2009

How to Access the Windows File from SQL Server

Within my SQL Server T-SQL stored procedures and scripts I need to find out the files in a specific directory. What are some approaches to do so? I need some flexibility to capture the file names and general information for subsequent processing. What are the native SQL Server options as well as the custom options that are available?

Depending on the exact needs dictates the command(s) that need to be issued from SQL Server 2000 or 2005. Below outlines some options to access the Windows file system related information with both native and custom code. In general the following commands exist:

Extended Stored Procedure - xp_cmdshell - Ability to execute any DOS command line code.
(Platform = SQL Server 2000 and SQL Server 2005)
EXEC master.dbo.xp_cmdshell 'dir c:\'

Extended Stored Procedure - xp_fixeddrives - Ability to capture the free drive space in megabytes.
(Platform = SQL Server 2000 and SQL Server 2005)
EXEC master.dbo.xp_fixeddrives

Extended Stored Procedure - xp_subdirs - Ability to capture the sub directories, but not files.
(Platform = SQL Server 2000 and SQL Server 2005)
EXEC master.dbo.xp_subdirs 'c:\'

Custom Code - snippet - Ability to capture the file names in a temporary table with xp_cmdshell.
(Platform = SQL Server 2000 and SQL Server 2005)


-- 1 - Variable declarations
DECLARE @CMD1 varchar(5000)
DECLARE @CMD2 varchar(5000)
DECLARE @FilePath varchar(200)

-- 2 - Create the #OriginalFileList temporary table to support the un-cleansed file list
CREATE TABLE #OriginalFileList (
Col1 varchar(1000) NULL

-- 3 - Create the #ParsedFileList temporary table to suppor the cleansed file list
CREATE TABLE #ParsedFileList (
DateTimeStamp datetime NOT NULL,
FileSize varchar(50) NOT NULL,
FileName1 varchar (255) NOT NULL

-- 4 - Initialize the variables
SELECT @FilePath = 'C:\Progra~1\Micros~2\MSSQL.1\MSSQL\Log\'

-- 5 - Build the string to capture the file names in the restore location
SELECT @CMD1 = 'master.dbo.xp_cmdshell ' + char(39) + 'dir ' + @FilePath + '\*.*' + char(39)

-- 6 - Build the string to populate the #OriginalFileList temporary table
SELECT @CMD2 = 'INSERT INTO #OriginalFileList(Col1)' + char(13) +
'EXEC ' + @CMD1

-- 7 - Execute the string to populate the #OriginalFileList table

-- 8 - Delete unneeded data from the #OriginalFileList
DELETE FROM #OriginalFileList

DELETE FROM #OriginalFileList
WHERE COL1 LIKE '%Volume%'

DELETE FROM #OriginalFileList
WHERE COL1 LIKE '%Directory%'

DELETE FROM #OriginalFileList

DELETE FROM #OriginalFileList
WHERE COL1 LIKE '%bytes%'

-- 9 - Populate the #ParsedFileList table with the final data
INSERT INTO #ParsedFileList (DateTimeStamp, FileSize, FileName1)
SELECT LTRIM(SUBSTRING (Col1, 1, 20)) AS 'DateTimeStamp',
LTRIM(SUBSTRING (Col1, 21, 18)) AS 'FileSize',
LTRIM(SUBSTRING (Col1, 40, 1000)) AS 'FileName1'
FROM #OriginalFileList

-- ********************************************************************************
-- INSERT code here to process the data from the #ParsedFileList table
-- ********************************************************************************

-- 10 - Drop the temporary tables
DROP TABLE #OriginalFileList
DROP TABLE #ParsedFileList


Stored Procedure to delete files from a folder

When your only way of managing SQL Server is through Enterprise Manager or Query Analyzer, you will have to do a lot of tasks through command shell. If you find yourself routinely conduct some activities using xp_cmdshell, it is a good idea to wrap those routines into a stored procedure. This example takes an input parameter for directory name and delete all files within that directory. The directory name can be UNC path. I put some comments within this procedure so it should be easy to follow.

CREATE proc usp_DeleteFileInFolder @FolderName varchar(150) as
--Created by Haidong Ji

declare @DOSCommand varchar(150)
--Check whether the user supply \ in the directory name
if not (right(@FolderName, 1) = '\')
set @FolderName = @FolderName + '\'
--Delete all files within this folder. Note: del *.* only deletes files,
--not folders. Q is the quite switch so DOS will not ask for confirmation
set @DOSCommand = 'del /Q ' + '"' + @FolderName + '*.*' + '"'
print @DOSCommand
exec master..xp_cmdshell @DOSCommand

For Delete a particular file : xp_cmdshell 'del c:\testfile.txt'
For More Info :

Wednesday, November 4, 2009

What is the meaning of: COLLATE SQL_Latin1_General_CP1_CI_AS

Just to avoid confusion between Windows Code Page and SQL Server CP
For ex.,
The SQL Server 7.0 code page for a default installation is SQL_Latin1_General_CP1_CI_AS. For SQL Server 2000 default code page is Latin1_General_CI_AS.

Microsoft made this change to reduce the scope of differing results that the incompatible SQL Server and Windows code pages cause.

CP1 - Codepage 1

CI - Case Insensitive

AS - Accent Sensitive.

Lets say you are running a stored procedure (SP) that performed a cross database joins on our SQL Server 2000 box. This particular procedure was processing records from a table in one database (database A), that was originally created on our SQL Server 2000 box, while also processing records from a table in another database (database B), that was also on our SQL Server 2000 box, but was created by restoring a SQL Server 7.0 database backup. The SQL Server 7 machine had a different character set and sort order than our SQL Server 2000 database. The SELECT statement the SP was running was joining records from the table in database A with the table in database B on a varchar key field. Can you imagine what happened? The SP failed with a "collation error".

SQL Server 2000 finally solved the problem of taking database backups from servers with different character set / sort order and restoring them, without getting a collation error. Before SQL Server 2000, backups from one server could only be restored on a different server if the same character set and sort order where used by both servers. With SQL Server 2000 you are able to set the collating sequence at the server, database and even the column level. Now that you can have multiple collating sequences on a single database server, there are a few things you should understand about mixing collations.

When you have character data stored with different collation sequences, you need to be aware that comparing columns becomes more involved. Columns with the same collating sequences can be compared or joined without any additional considerations. Although when you compare columns that have different collating sequences it require some special coding considerations.

Wednesday, October 28, 2009

Friday, October 16, 2009

How to Insert data into a temporary table with the help of SQL stored procedure

This Stored Procedure shows how to create temporary table and insert data into it from another table
Create procedure sp_tempTable
create table #tempTable(ID int,Symbol nvarchar(50)) -- Create #tempTable
insert into #tempTable
select Max(ID) as MaxId,Symbol from Sheet1 Group by Symbol --Insert data from - --Sheet1 table
select * from #tempTable
drop table #tempTable
--To execute
--exec sp_tempTable

Saturday, October 10, 2009

What is ASP.Net MVC?

Hi all,
Since last month i'm getting busy with some another work so hope i will post new thing after a month or two. Now i am tryyyyyyyyyy... to learn ASP.Net MVC ..... its totally new for me.
How about you ...?

To learn I will refer you .... site..
So enjoy .....

Monday, September 21, 2009

Stored Procedure with isnull function in SQL Server 2005

Create Procedure [dbo].[GetSupply]
i.Title as Title,
i.Edition as Edition,
select count(ISBN)
from Purchase
where ISBN=i.ISBN and Email=i.Email group by ISBN
),0 ),
FirstPurchDate= isnull
select cast(min(Purchase_date) as nvarchar(50))
from Purchase
where ISBN=i.ISBN group by ISBN
'Not Purchase Yet'
from INFO as i
where i.ISBN is not NULL
and i.ISBN <>''
and i.contacted ='W'
--and i.Title<>''
group by i.ISBN,i.Title,i.Edition,i.Email
order by i.ISBN

The AvailableTitles will show 0 and FirstPurchDate will show 'Not Purchase Yet' if query returns null for the both.

Thursday, September 17, 2009

Lots of Code....

Lots of Code.... now need some fun !
So what can we do...?
Ok listen some bengali songs....
Hmm ! Just go for it...

Link :

Wednesday, September 16, 2009

Monday, September 14, 2009

How to Create and retrieve data from cookies in ASP.Net C#

This is the example for remember a user in a system when he login for a particular site
User objUser = new User();
protected void Page_Load(object sender, EventArgs e)
if (!IsPostBack)
//Retrieve data from cookie
HttpCookie cookie = Request.Cookies["User"];
chkCookies.Checked = true;
objUser.User ID = cookie["ID"];
objUser.Password = cookie["Password"];

private void UserLogin(User objUsr)
Collection userDetails = BLCustomer.UserLogin(objUsr);
if (userDetails .Count > 0)
Session["User"] = objUsr.UserID;
if (chkCookies.Checked == true)
//Here cookie is create............................

HttpCookie ck = Request.Cookies["User"];
if (ck == null)
ck = new HttpCookie("User");
ck["ID"] = objUsr.UserID;
ck["Password"] = objUsr.Password;
ck.Expires = DateTime.Now.AddMonths(1);
lblMsg.Text = "Login fail!";

Thursday, September 10, 2009

Stored Procedure for Import a .csv file with filename and table name as perameter

Many times there is a problem to pass a table name as a store procedure perameter, so this
procedure could help,

Store Procedure:
Create PROCEDURE [dbo].[sp_ImportCsvFile]
@tableName nvarchar(200),
@from nvarchar(300) -- .csv file path
DECLARE @SQLString nvarchar(1000)
DECLARE @S2 nvarchar(1000)
SET @SQLString = 'BULK INSERT ' + @tableName + ' FROM '''+ @from
Select @S2 = CAST(@SQLString as nvarchar(1000))

Execute the procedure:
exec sp_ImportCsvFile 'tbl_Forex','D:\20090908_forex.csv'

Monday, August 24, 2009

How to Display Sum of each column in the Footer of a GridView Control

Put OnRowDataBound event in Gridview tag
from design page , it will show how to display sum of each column in the footer of the GridView control

protected void gvAssets_RowDataBound(object sender, GridViewRowEventArgs e)
if (e.Row.RowType == DataControlRowType.DataRow)
decimal rowbal = Convert.ToDecimal(DataBinder.Eval(
e.Row.DataItem, "CurrentBalance"));
balance = balance + rowbal;
if (e.Row.RowType == DataControlRowType.Footer)
Label lblbalFooter = (Label)e.Row.FindControl("lblbalFooter");
lblbalFooter.Text = Convert.ToString(String.Format("{0:0,0.00}",

Saturday, August 8, 2009

How to Set your screen resolution in .net application [C#]

Sometimes we need change the screen resolution for running our .net desktop application
So here is the code from where we can change the screen resolution with the help of
proc.StartInfo.Arguments = "/x 800 /y 600"; // this will change the screen resolution to
using System;
using System.Windows.Forms;
using System.Diagnostics;
class Test
static void Main()
Screen scr = Screen.PrimaryScreen;
int oldWidth = scr.Bounds.Width;
int oldHeight = scr.Bounds.Height;
Process proc = new Process();
proc.StartInfo.FileName = @"c:\qres\ApplicationName.exe"; // put full path in here
proc.StartInfo.Arguments = "/x 800 /y 600"; // say
Console.WriteLine("Press enter to change back to original resolution and exit program");
proc.StartInfo.Arguments = "/x " + oldWidth.ToString() + "/y "+ oldHeight.ToString();

Crystal Report in C#

To show a report

private void Page_Load(object sender, System.EventArgs e)
CrystalReport1 report=new CrystalReport1();
DataSet ds=new DataSet("Account");//give same name as on

//dataset1 table header

DataTable table=new DataTable("Account");//give same name as on

//dataset1 table header

DataRow row=table.NewRow();
// add to table

// set report's dataset

// set report source

CrystalReportViewer1.ReportSource =report;

Thursday, August 6, 2009

String Format for Double in C#

Help link :

This is a very helpful to me . I have got lot of things from here ..

Few things i can share here also
String.Format("{0:0.00}",123.4567); // 123.46 -- just two decimal places
String.Format("{0:0,0.0}",12345.67); // 12,345.7 --Thousands separator

Sql Query for return columns name of a table


//Put the tablename as i did 'tempTble'

Wednesday, August 5, 2009

How to Listing Files in a Directory with C#

DirectoryInfo dirInfo = new DirectoryInfo(ryanDataPath + "\\RyansData");
FileInfo[] latestFile = dirInfo.GetFiles("*.csv");

Here latestFile have all the files with .csv extension within RyansData folder

How to Copy a file in C#

Here i am showing how to copy a file source to destination filepath in C#. The file with the filepath 'databasePath' will copy to the destination filepath 'ryanDataPath'
private void CopyDBToCommonFile(string databasePath, string ryanDataPath)
FileInfo fi = new FileInfo(ryanDataPath + "\\DB\\SmartChart.mdb"); \\Destination file path
if (!fi.Exists)
FileInfo fc = new FileInfo(databasePath); \\Source file path
fc.CopyTo(fi.FullName, true);

Wednesday, July 15, 2009

Download and unzip files form FTP server in .Net desktop Application with C#

Here i am showing how to download files from FTP server and also how to unzip a zip file.

using System;
using System.IO;
using System.Collections.Generic;
using System.ComponentModel;
using System.Data;
using System.Text;
using System.Net;
using ICSharpCode.SharpZipLib.Zip;
using System.Security;
using System.Security.Principal;
using System.Security.AccessControl;
namespace ftpAccess
public static class Download
static List SymbolLst;
static string savePath = string.Empty;

#region DownloadF File From FTP Server

public static bool DownloadFile(string fileSavePath, string DownloadfileName,
string ftpServerIP, string ftpUserID, string ftpPassword)
bool result = false;
SymbolLst = SymbolList(DownloadfileName);
savePath = fileSavePath;
for (int i = 0; i <>
FtpWebRequest reqFTP;
FileStream outputStream = new FileStream(fileSavePath + "\\" + Convert.ToString(SymbolLst[i]), FileMode.Create);
reqFTP = (FtpWebRequest)FtpWebRequest.Create(new Uri("ftp://" + ftpServerIP + "/" + Convert.ToString(SymbolLst[i])));
reqFTP.Method = WebRequestMethods.Ftp.DownloadFile;
reqFTP.UseBinary = true;
reqFTP.Credentials = new NetworkCredential(ftpUserID, ftpPassword);
FtpWebResponse response = (FtpWebResponse)reqFTP.GetResponse();
Stream ftpStream = response.GetResponseStream();
long cl = response.ContentLength;
int bufferSize = 6048;
int readCount;
byte[] buffer = new byte[bufferSize];
readCount = ftpStream.Read(buffer, 0, bufferSize);
while (readCount > 0)
outputStream.Write(buffer, 0, readCount);
readCount = ftpStream.Read(buffer, 0, bufferSize);
catch (Exception ex)
result = false;
if (unzipclickbank() == true)
result = true;
return result;

#region Return all files to download
private static List SymbolList(string ConnSymbol)
string[] namesArray = ConnSymbol.Split(',');
List lstTest = new List(namesArray.Length);
return lstTest;

#region UnZip All Files
public static bool unzipclickbank()
for (int i = 0; i <>
//Checks if file is open
FileStream fs = new FileStream(savePath + "/" + Convert.ToString(SymbolLst[i]), FileMode.Open, FileAccess.ReadWrite);
ZipInputStream s = new ZipInputStream(File.OpenRead(savePath + "/" + Convert.ToString(SymbolLst[i])));
s.Password = "ryancharts";
ZipEntry theEntry = default(ZipEntry);
theEntry = s.GetNextEntry();
while ((theEntry != null))
string directoryName = Path.GetDirectoryName(theEntry.Name);
string fileName = Path.GetFileName(theEntry.Name);

//create directory
if (directoryName.Length > 0)
Directory.CreateDirectory(savePath + "/" + directoryName);
if (fileName != string.Empty)
FileStream StreamWriter = File.Create(savePath + "/" + theEntry.Name);

int Size = 6048;

byte[] data = new byte[6049];
while (true)
Size = s.Read(data, 0, data.Length);

if (Size > 0)
StreamWriter.Write(data, 0, Size);
break; // TODO: might not be correct. Was : Exit While
theEntry = s.GetNextEntry();
catch (Exception ex)
return false;
return true;

#region Delete Zip File
private static void DeleteOldZipFile()
for (int i = 0; i <>
FileInfo finfo = new FileInfo(savePath + "\\" + Convert.ToString(SymbolLst[i]));
if (finfo.Exists)