How to display Exception as Java Script Alert Message?

Some times it will be required to display exception message as a script alert.
Here is a code snippet for that from .aspx.cs page:

catch( Exception ex )
            {
                Response.Write("<script>alert('" + ex.Message + "');</script>");
                return;
            }

Unzipping files in C#

         ///<summary>
        ///UnZipping all the user uploaded file
        ///</summary>    
        private void UnzipFiles()
        {
            try
            {
                if (File.Exists(InputPathOfZipFile))
                {
                    string baseDirectory = Path.GetDirectoryName(InputPathOfZipFile);
                    using (ZipInputStream ZipStream = new ZipInputStream(File.OpenRead(InputPathOfZipFile)))
                    {
                        ZipEntry theEntry;
                        while ((theEntry = ZipStream.GetNextEntry()) != null)
                        {
                            if (theEntry.IsFile)
                            {
                                if (theEntry.Name != "")
                                {
                                    string strNewFile = @"" + baseDirectory + @"\" + theEntry.Name;
                                    if (File.Exists(strNewFile))
                                    {
                                        continue;
                                    }
                                    using (FileStream streamWriter = File.Create(strNewFile))
                                    {
                                        int size = 2048;
                                        byte[] data = new byte[2048];
                                        while (true)
                                        {
                                            size = ZipStream.Read(data, 0, data.Length);
                                            if (size > 0)
                                                streamWriter.Write(data, 0, size);
                                            else
                                                break;
                                        }
                                        streamWriter.Close();
                                    }
                                }
                            }
                            else if (theEntry.IsDirectory)
                            {
                                string strNewDirectory = @"" + baseDirectory + @"\" + theEntry.Name;
                                if (!Directory.Exists(strNewDirectory))
                                {
                                    Directory.CreateDirectory(strNewDirectory);
                                }
                            }
                        }
                        ZipStream.Close();
                    }
                }
            }
            catch (Exception ex)
            {
                //TODO:Exception Handling
            }
        }

Zipping files in C# using SharpZipLib

  private void zipFiles()
        {
            try
            {
                string sTargetFolderPath = @"TargetFolderToZip";
                string sZipFileName = @"FileNametoZip";
                string[] filenames = Directory.GetFiles(sTargetFolderPath, "*.csv");
                // Zip up the files - From SharpZipLib Demo Code              
                using (ZipOutputStream s = new ZipOutputStream(File.Create(sTargetFolderPath + "\\" + sZipFileName + ".zip")))
                {
                    s.SetLevel(9); // 0-9, 9 being the highest compression
                    byte[] buffer = new byte[4096];
                    foreach (string file in filenames)
                    {
                        ZipEntry entry = new
                        ZipEntry(Path.GetFileName(file));
                        entry.DateTime = DateTime.Now;
                        s.PutNextEntry(entry);
                        using (FileStream fs = File.OpenRead(file))
                        {
                            int sourceBytes;
                            do
                            {
                                sourceBytes = fs.Read(buffer, 0, buffer.Length);
                                s.Write(buffer, 0, sourceBytes);
                            } while (sourceBytes > 0);
                        }
                    }
                    s.Finish();
                    s.Close();
                }
                // clean up files by deleting the temp folder and its content
                System.IO.Directory.Delete(sTargetFolderPath + "file://tempzipfile//", true);               
            }
            catch (Exception ex)
            {
                //TODO: Exception Handling                               
            }
        }

File Transfer using FTP in C#

   private void UploadFiles()
        {
            try
            {
                string ftplocation = "ftp://ServerIp//Directory";              
                string file = @"InputFilePath";
                string user = "username";
                string password = "password";
                String filename = Path.GetFileName(file);              
                FtpWebRequest request = (FtpWebRequest)FtpWebRequest.Create(ftplocation + "/" + "UploadFileName");              
               
                // Configure the connection request            
                request.Method = WebRequestMethods.Ftp.UploadFile;            
                request.Credentials = new NetworkCredential(user, password);            
                request.UsePassive = true;            
                request.UseBinary = true;            
                request.KeepAlive = false;
                request.Proxy = null;
                request.Timeout = 600000;
               
                // Create a stream from the file            
                FileStream stream = File.OpenRead(file);            
                byte[] buffer = new byte[stream.Length];              
               
                // Read the file into the a local stream            
                stream.Read(buffer, 0, buffer.Length);              
               
                // Close the local stream            
                stream.Close();              
               
                // Create a stream to the FTP server            
                Stream reqStream = request.GetRequestStream();              
               
                // Write the local stream to the FTP stream    2 bytes at a time                            
                int offset = 0;            
                int chunk = (buffer.Length > 2048) ? 2048 : buffer.Length;                                    
                while (offset < buffer.Length)            
                    {                
                        reqStream.Write(buffer, offset, chunk);                
                        offset += chunk;                
                        chunk = (buffer.Length - offset < chunk) ? (buffer.Length - offset) : chunk;            
                    }            
                // Close the stream to the FTP server            
                reqStream.Close();                
            }
            catch (Exception ex)
            {
                //TODO: Exception handling
           
            }         
                   
        }

Querying SysObjects in Sql Server

Sometimes as a developer we are required to change Table Structure or delete a table it self. In such a scenario it is always better to understand the impact of table change, to get the dependent Stored Procedures/Views of a table use sp_depends stroed procedure which will list out all Stored Procedures.Views that depends on table.

exec sp_depends 'tableName'

Using SP_Depends we can get dependencies information for Stored Procedure also.
exec sp_depends 'SpName'

When queried with sp_depends for a Stored Procedure it lists all the tables and columns referenced in Stored Procedure.

Another Important Sys Object that we can use is information_schema.routines

In scenarios like changing Column Name in a Table, before we need to update column names it is required for us to list out all the Stored Procedures which uses the particular column, so that we can update corresponding StoredProcedures with name change.

select Specific_name from information_schema.routines
where routine_definition like 'ColumnNametoChange'

Extraction of Pipe Delimited CSV Files from MSAccess

PreRequisites:
MS Access Back up file
Entries in Web.Config
<add name="MSAccessDB" connectionString="Provider=Microsoft.ACE.OLEDB.12.0;Data Source=FileLocation where Backup file available;"/>

 <appSettings>
  <add key="TablesList" value="Table1,Table2,Table3,Table4"/>
 </appSettings>

using System.Configuration;
using System.Data;
using System.Data.OleDb;
using System.IO;
using System.Text;

       /// <summary>
        /// Extracting Pipe Delimited .CSV files from MSAccess
        /// </summary>
        /// <param name=""></param>
   private void ExtractCSVFromBackUp()
        {
            try
            {
                strConnection = System.Configuration.ConfigurationManager.ConnectionStrings["MSAccessDB"].ToString();
                FilePath = @"File";
                TableNames = ConfigurationManager.AppSettings["TablesList"].ToString(); //List of tables to extract from MS Access, save this as Key/Value pair in .config file
                TablesList = TableNames.Split(new char[] { ',' });
                for (int tblCount = 0; tblCount < TablesList.Length; tblCount++)
                {
                    DataTable dt = new DataTable();
                    dt.TableName = TablesList[tblCount];
                    OleDbDataAdapter oDAAccessory = new OleDbDataAdapter("Select * from " + TablesList[tblCount], strConnection);
                    oDAAccessory.Fill(dt);
                   
                    // If table has Data then generate Pipe Delimited CSV file
                    if (dt.Rows.Count > 0)
                    {
                        string fileOut = string.Empty;
                        string strRow = string.Empty; // represents a full row 
                        fileOut = FilePath + TablesList[tblCount] + ".csv";
                        // Creates the CSV file as a stream, using the given encoding.                   
                        StreamWriter sw = new StreamWriter(fileOut, false, Encoding.ASCII);
                        // Reads the rows one by one from the DataTable transfers them to a string with the given
                        //separator character and writes it to the file.                                       
                        for (int i = 0; i < dt.Rows.Count; i++)
                        {
                            strRow = "";
                            for (int j = 0; j < dt.Columns.Count; j++)
                            {
                                if (dt.Columns[j].DataType.ToString() == "System.String" && dt.Rows[i][j].ToString() != "")
                                {
                                    strRow += "\"";
                                }
                                strRow += dt.Rows[i][j].ToString();
                                if (dt.Columns[j].DataType.ToString() == "System.String" && dt.Rows[i][j].ToString() != "")
                                {
                                    strRow += "|"";
                                }
                                if (j < dt.Columns.Count - 1)
                                {
                                    strRow += ",";
                                }
                            }
                            sw.WriteLine(strRow);
                        }
                        sw.Close();
                    }
                }
            }
            catch (Exception ex)
            {
                LogMessageToFile("Exception in Admin Extrct  " + ex.Message.ToString());             
            }
        }

Logging in C#

        /// <summary>
        /// Writting a log file for Code Flow
        /// </summary>
        /// <param name="msg"> String to Log</param>
        public void LogMessageToFile(string msg)
        {
            System.IO.StreamWriter sw = System.IO.File.AppendText(FilePath);
            try
            {
                string logLine = System.String.Format("{0:G}: {1}.", System.DateTime.Now, msg);
                sw.WriteLine(logLine);
            }
            finally
            {
                sw.Close();
            }
        }

Query to delete duplicate records in Sql

In Sql there is Common Table Expression (CTE) which is a temporary named result set that you can reference within a SELECT, INSERT, UPDATE, or DELETE statement. You can also use a CTE in a CREATE VIEW statement, as part of the view’s SELECT query.


I used CTE to delete duplicate records in a table. The following script contains defining table, inserting data into table and then identifying duplicates.


CREATE TABLE [dbo].[Table1](
 [ID] [uniqueidentifier] NOT NULL,
 [SERNO] [varchar](25) NOT NULL,
 [StartDATE] [datetime] NULL,
 [EndDATE] [datetime] NULL
) ON [PRIMARY]

INSERT INTO [Table1] ([ID],[SERNO],[StartDATE],[EndDATE])VALUES('DA41B9AB-BEAF-412C-9815-0C48E9018293','URR025979','Sep 27 2006 12:00:00','Sep 30 2011 12:00:00')
INSERT INTO [Table1] ([ID],[SERNO],[StartDATE],[EndDATE])VALUES('DA41B9AB-BEAF-412C-9815-0C48E9018293','URR021379','Sep 27 2006 12:00:00','Sep 29 2011 12:00:00')
INSERT INTO [Table1] ([ID],[SERNO],[StartDATE],[EndDATE])VALUES('57CF812D-A4A5-4521-9A0B-346208913424','WTM001540','Aug 26 2008 12:00:00','Aug 30 2012 12:00:00')
INSERT INTO [Table1] ([ID],[SERNO],[StartDATE],[EndDATE])VALUES('57CF812D-A4A5-4521-9A0B-346208913424','WTM001540','Aug 26 2008 12:00:00','Aug 30 2012 12:00:00')
INSERT INTO [Table1] ([ID],[SERNO],[StartDATE],[EndDATE])VALUES('5169908F-D524-4034-9690-38ED87790DC8','YHT651144','Nov  2 2010 12:00:00','Aug 30 2015 11:00:00')
INSERT INTO [Table1] ([ID],[SERNO],[StartDATE],[EndDATE])VALUES('5169908F-D524-4034-9690-38ED87790DC8','YHT651144','Nov  2 2010 12:00:00','Aug 30 2015 12:00:00')
INSERT INTO [Table1] ([ID],[SERNO],[StartDATE],[EndDATE])VALUES('5169908F-D524-4034-9690-38ED87790DC8','YHT651145','Nov  2 2010 12:00:00','Aug 30 2015 12:00:00')
INSERT INTO [Table1] ([ID],[SERNO],[StartDATE],[EndDATE])VALUES('5169908F-D524-4034-9690-38ED87790DC8','YHT651145','Nov  2 2010 12:00:00','Aug 30 2015 10:00:00')
WITH cte AS    
( SELECT ID, SERNO, StartDATE, EndDATE,        
  ROW_NUMBER() OVER (PARTITION BY ID, SERNO, StartDATE, EndDATE                           
 ORDER BY EndDATE DESC) AS sequence FROM Table1) 
SELECT ID, SERNO, StartDATE, EndDATE, sequence FROM cte WHERE sequence>=1

If you need to delete the duplicates, delete the records which have sequence >1
WITH cte AS    
( SELECT ID, SERNO, StartDATE, EndDATE,        
  ROW_NUMBER() OVER (PARTITION BY ID, SERNO, StartDATE, EndDATE                           
 ORDER BY EndDATE DESC) AS sequence FROM Table1) 
DELETE FROM cte WHERE sequence > 1

Using Rank() to get first row based on a filter

In Sql Server we can use Rank() to fetch a row with rank 1 by filtering data on certian criteria.


For ex, using the following table i am fetching highest runs scored by a player in his career.


The following script contains table creation, fetching details for each player with highest runs scored.


Create table tblTest
 (Name varchar(10), Year int,Country varchar(10),RunsScored int, primary key(Name,Year))
insert into tblTest
select 'A',2001,'India',13 union
select 'A',2002,'India',23 union
select 'A',2003,'India',19 union
select 'A',2004,'India',14 union
select 'A',2005,'India',11 union
select 'B',2001,'England',42 union
select 'B',2002,'England',39 union
select 'B',2003,'England',42 union
select 'B',2004,'England',29 union
select 'C',2002,'England',2 union
select 'C',2003,'England',3 union
select 'C',2004,'India',6 union
select 'C',2005,'India',9
select * from
(
select Name, Year, RunsScored, Rank() over (Partition BY Name order by RunsScored DESC, Year DESC) as Rank
from
tblTest
) tmp
where Rank = 1


Results:
======
A 2002 23 1
B 2003 42 1
C 2005  9 1

File Transfer using FTP in C#

In day to day life, we will come across various scenarios which requires file transfer from application to Web Server/Data Base server to have certian functionalities to achieve. So , i thought if code is available online it will be easy for the developers to write code in less time.

Here is the Code snippet for the FTP File Transfer:

///This method will copy files from Application Server Directory to a remote server using FTP.

 private void UploadFiles()
        {
                       try
            {

                string ftplocation = "ftp://TargetServerwithport//directorynameintargetserver/";
                string file = @"D:\BaseFolder\Sample.txt";
                //Provide the credentials to connect to Target FTP Server , good practice is store them in config files 
                string user = "test";
                string password = "test123";
                String filename = Path.GetFileName(file);              
                // Open a request using the full URI
                FtpWebRequest request = (FtpWebRequest)FtpWebRequest.Create(ftplocation + "/" + "Sample.txt");              
                              
                // Configure the connection request            
                request.Method = WebRequestMethods.Ftp.UploadFile;            
                request.Credentials = new NetworkCredential(user, password);            
                request.UsePassive = true;            
                request.UseBinary = true;            
                request.KeepAlive = false;
                request.Proxy = null;
                request.Timeout = 600000; //Time out for request to be processed
               
                // Create a stream from the file            
                FileStream stream = File.OpenRead(file);            
                byte[] buffer = new byte[stream.Length];              
               
                // Read the file into the a local stream            
                stream.Read(buffer, 0, buffer.Length);              
               
                // Close the local stream            
                stream.Close();              
               
                // Create a stream to the FTP server            
                Stream reqStream = request.GetRequestStream();              
               
                // Write the local stream to the FTP stream            
                // 2 bytes at a time                            
                int offset = 0;            
                int chunk = (buffer.Length > 2048) ? 2048 : buffer.Length;                                    
                while (offset < buffer.Length)            
                    {                
                        reqStream.Write(buffer, offset, chunk);                
                        offset += chunk;                
                        chunk = (buffer.Length - offset < chunk) ? (buffer.Length - offset) : chunk;            
                    }            
                // Close the stream to the FTP server            
                reqStream.Close();                
            }
            catch (Exception ex)
            {
                //TODO: Exception handling
           
            }         
                   
        }