How to use SQL Server Profiler in easy steps

Most of the .NET developers have to deal with SQL server. Some may be good in database while most of the developers are great in front end but know only the basic knowledge of SQL Server database.

During development, these developers have to face the database performance issues. For most of the developers this database query performance kind of tasks are bouncers. 

Here I am trying to explain how you can use SQL Server profiler for identifying query performance.

Suppose I have to check performance of below queries

SELECT * FROM TableA

GO

SELECT * FROM TableB

GO

SELECT * FROM TableC

GO

1.    First open SQL Server Profiler from Tools -> SQL Server Profiler

2.    Login to SQL Server Profiler and click on Events Selection tab of Trace Properties window.

 

3.    Uncheck all events in Events Selection tab except SQL:BatchCompleted. Select Show All Columns radiobutton and click on Column Filters… button.

 

4.    Select HostName filter from Edit Filter screen and click on Like option. Enter here filter value as ‘%ComputerName%’. Click Ok.

 

5.    Click Run.

6.    Now run queries in SQL Server.

7.    Check status of all queries in SQL Server profiler as below

 

8.    The most important data are CPU, Reads, Writes and Duration. Here higher figures of these columns show the action required for query optimization.

Please leave your comments or share this article if it’s useful for you.

No server is available to process the request in NCache

If you are getting error "No server is available to process the request" for NCache then follow below steps to fix it.

·         Restart NCache Express from windows service

·         Go to command prompt and start cache as

Startcache  cache-id /s serverName

Please leave your comments or share this tip if it’s useful for you.

How to download file via FTP in ASP.NET using C#

In almost all ASP.NET web application, we have to write a code to download file from FTP server.

Here is simple and full code to download file via FTP.

It uses FTP web request and response stream for connecting and downloading file from FTP server by using credentials.

public static bool FTPDownloadFile(string sAddress, string sUserName, string sPassword, bool isUsePassive, string sDownloadFileName, string sServerFileName)

{

    Stream ftpStream = null;

    int bufferSize = 2048;

    bool bReturnValue = false;

    FtpWebRequest ftpRequest = (FtpWebRequest)FtpWebRequest.Create(sAddress + @"/" + sServerFileName);

    ftpRequest.Method = WebRequestMethods.Ftp.DownloadFile;

    ftpRequest.Proxy = null;

    ftpRequest.UseBinary = true;

    ftpRequest.KeepAlive = false;

    ftpRequest.EnableSsl = isSSL;

    ftpRequest.Credentials = new NetworkCredential(sUserName, sPassword);

    ftpRequest.UsePassive = isUsePassive;

    FtpWebResponse ftpResponse = (FtpWebResponse)ftpRequest.GetResponse();

    ftpStream = ftpResponse.GetResponseStream();

    FileStream localFileStream = new FileStream(sDownloadFileName, FileMode.Create);

    byte[] byteBuffer = new byte[bufferSize];

    int bytesRead = ftpStream.Read(byteBuffer, 0, bufferSize);

    try

    {

        while (bytesRead > 0)

        {

            localFileStream.Write(byteBuffer, 0, bytesRead);

            bytesRead = ftpStream.Read(byteBuffer, 0, bufferSize);

        }

        bReturnValue = true;

    }

    catch (Exception ex)

    {

        throw ex;

    }

    finally

    {

        if (localFileStream != null)

        {

            localFileStream.Close();

        }

        if (ftpStream != null)

        {

            ftpStream.Close();

        }

        if (ftpResponse != null)

        {

            ftpResponse.Close();

        }

        ftpRequest = null;

    }

    return bReturnValue;

}

Please leave your comments or share this code if it’s useful for you.

How to download file via SFTP in ASP.NET using C#

In almost all ASP.NET web application, we have to write a code to download file from secure SFTP server.

Here is simple and full code to download file via SFTP.

First you need to download SshNet DLL from http://sshnet.codeplex.com/.

You can connect SFTP by two ways

1.    By using credentials

2.    By using Username and private key. Private Key may be bind with Pass phrase.

This code is specific to connect to SFTP by using credentials.

Note:-

·         To connect by using private key refer my another article on the blog.

·         Your private key must be compatible with SshNet. To convert any private key to SshNet compatible private key, refer my other article How to convert Private key to OpenSSH Key to connect to SFTP server.

It uses SftpClient for creating SFTP connection to server by providing SFTP URL with credentials and private key to connect to specific folder or root folder. Code read and download the file from SFTP server.

using Renci.SshNet;

private static object threadLock = new object();

public static bool SFTPDownloadFile(string Address, string UserName, int Port, string Password, string DownloadFileName, string ServerFileName)

{

    SftpClient client = null;

    bool bReturnValue = false;

    try

    {

        client = new SftpClient(Address, Port, UserName, Password);

        client.Connect();

        lock (threadLock)

        {

            using (var file = File.OpenWrite(DownloadFileName))

            {

                client.DownloadFile(ServerFileName, file);

                bReturnValue = true;

            }

        }

    }

    catch (Exception ex)

    {

        throw ex;

    }

    finally

    {

        if (client != null)

        {

            client.Disconnect();

            client.Dispose();

        }

    }

    return bReturnValue;

}

Please leave your comments or share this code if it’s useful for you.

How to upload file via SFTP using private key in ASP.NET using C#

In almost all ASP.NET web application, we have to write a code to upload file to secure SFTP servers.

Here is simple and full code to upload file via SFTP.

First you need to download SshNet DLL from http://sshnet.codeplex.com/.

You can connect SFTP by two ways

1.    By using credentials

2.    By using Username and private key. Private Key may be bind with Pass phrase.

This code is specific to connect to SFTP by using private key.

Note:- Your private key must be compatible with SshNet. To convert any private key to SshNet compatible private key, refer my other article How to convert Private key to OpenSSH Key to connect to SFTP server.

It uses SftpClient for creating SFTP connection to server by providing SFTP URL with credentials and private key to connect to specific folder or root folder. Code read the file and uploads the final stream to SFTP server.

using Renci.SshNet;

public static void UploadFileToSFTPServer(string FilePath, string Address, int Port, string UserName, string Password, string FolderName)

{

    SftpClient client = null;

    string sPrivateKeyPath = "PrivateKeyFilePath";

    PrivateKeyFile ObjPrivateKey = null;

    PrivateKeyAuthenticationMethod ObjPrivateKeyAutentication = null;

    using (Stream stream = File.OpenRead(sPrivateKeyPath))

    {

        if (ConfigurationSettings.AppSettings["PassPhraseCode"] != null)

        {

            string sPassPhrase = ConfigurationSettings.AppSettings["PassPhraseCode"];

            ObjPrivateKey = new PrivateKeyFile(stream, sPassPhrase);

            ObjPrivateKeyAutentication = new PrivateKeyAuthenticationMethod(UserName, ObjPrivateKey);

        }

        else

        {

            ObjPrivateKey = new PrivateKeyFile(stream);

            ObjPrivateKeyAutentication = new PrivateKeyAuthenticationMethod(UserName, ObjPrivateKey);

        }

        ConnectionInfo objConnectionInfo = new ConnectionInfo(Address, Port, UserName, ObjPrivateKeyAutentication);

        client = new SftpClient(objConnectionInfo);

    }

SftpClient client = new SftpClient(Address, Port, UserName, Password);

    client.Connect();

    if (!string.IsNullOrEmpty(FolderName))

    {

        client.ChangeDirectory(FolderName + @"/");

    }

    using (var fileStream = new FileStream(FilePath, FileMode.Open))

    {

        client.BufferSize = 4 * 1024;

        client.UploadFile(fileStream, Path.GetFileName(FilePath), null);

    }

    client.Disconnect();

    client.Dispose();

} 

Please leave your comments or share this code if it’s useful for you.