Push Logs to AWS CloudWatch

AWS CloudWatch

AWS CloudWatch logs service has the capability to store custom logs and process metrics generated from your application instances. Here is some example use cases for custom logs and metrics.

Webserver (Nginx, Apache etc ) access or error logs can be pushed to CloudWatch logs it acts as central log management for your applications running on AWS
Custom application logs (java, python, etc) can be pushed to CloudWatch and you can set up custom dashboards and alerts based on log patterns.
Ec2 instance metrics/custom system metrics/ app metrics can be pushed to CloudWatch.
Create AMI of the instance for further use.

Application Logs To AWS CloudWatch Workflow

You can send logs from any number of ec2 sources to Cloudwatch. All you need to have is a Cloudwatch agent running on your instance.

Here is what you have to do

Install Cloudwatch agent
Configure log sources in the Cloudwatch agent configuration file.
Start the agent with the configuration file.
Validate logs in Cloudwatch dashboard.

Setup CloudWatch on the instance

Install CloudWatch agent

The CloudWatch agent is available as a package in Amazon Linux 2.

You can install the package by entering the following command. You must also make sure that the IAM role attached to the instance has the CloudWatchAgentServerPolicy attached.

sudo yum install amazon-cloudwatch-agent

Configure profiles for the AWS CLI

# setup AWS CLI profile
$ aws configure
AWS Access Key ID [None]:
AWS Secret Access Key [None]:
Default region name [None]: us-west-2
Default output format [None]: json

# verify the AWS CLI profile 
$ cat ~/.aws/credentials
$ cat aws configure
$ aws configure list

Create the CloudWatch agent configuration file

Before running the CloudWatch agent on any servers, you must create a CloudWatch agent configuration file.

The agent configuration file is a JSON file that specifies the metrics and logs that the agent is to collect, including custom metrics.

Any time you change the agent configuration file, you must then restart the agent to have the changes take effect.

sudo /opt/aws/amazon-cloudwatch-agent/bin/amazon-cloudwatch-agent-config-wizard

sudo /opt/aws/amazon-cloudwatch-agent/bin/amazon-cloudwatch-agent-config-wizard
================================================================
= Welcome to the Amazon CloudWatch Agent Configuration Manager =
=                                                              =
= CloudWatch Agent allows you to collect metrics and logs from =
= your host and send them to CloudWatch. Additional CloudWatch =
= charges may apply.                                           =
================================================================
On which OS are you planning to use the agent?
1. linux
2. windows
3. darwin
default choice: [1]:

Trying to fetch the default region based on ec2 metadata...
Are you using EC2 or On-Premises hosts?
1. EC2
2. On-Premises
default choice: [1]:

Which user are you planning to run the agent?
1. root
2. cwagent
3. others
default choice: [1]:

Do you want to turn on StatsD daemon?
1. yes
2. no
default choice: [1]:

Which port do you want StatsD daemon to listen to?
default choice: [8125]

What is the collect interval for StatsD daemon?
1. 10s
2. 30s
3. 60s
default choice: [1]:

What is the aggregation interval for metrics collected by StatsD daemon?
1. Do not aggregate
2. 10s
3. 30s
4. 60s
default choice: [4]:

Do you want to monitor metrics from CollectD? WARNING: CollectD must be installed or the Agent will fail to start
1. yes
2. no
default choice: [1]:
2
Do you want to monitor any host metrics? e.g. CPU, memory, etc.
1. yes
2. no
default choice: [1]:

Do you want to monitor cpu metrics per core?
1. yes
2. no
default choice: [1]:
2
Do you want to add ec2 dimensions (ImageId, InstanceId, InstanceType, AutoScalingGroupName) into all of your metrics if the info is available?
1. yes
2. no
default choice: [1]:

Do you want to aggregate ec2 dimensions (InstanceId)?
1. yes
2. no
default choice: [1]:

Would you like to collect your metrics at high resolution (sub-minute resolution)? This enables sub-minute resolution for all metrics, but you can customize for specific metrics in the output json file.
1. 1s
2. 10s
3. 30s
4. 60s
default choice: [4]:

Which default metrics config do you want?
1. Basic
2. Standard
3. Advanced
4. None
default choice: [1]:

Current config as follows:
{
	"agent": {
		"metrics_collection_interval": 60,
		"run_as_user": "root"
	},
	"metrics": {
		"aggregation_dimensions": [
			[
				"InstanceId"
			]
		],
		"append_dimensions": {
			"AutoScalingGroupName": "${aws:AutoScalingGroupName}",
			"ImageId": "${aws:ImageId}",
			"InstanceId": "${aws:InstanceId}",
			"InstanceType": "${aws:InstanceType}"
		},
		"metrics_collected": {
			"collectd": {
				"metrics_aggregation_interval": 60
			},
			"disk": {
				"measurement": [
					"used_percent"
				],
				"metrics_collection_interval": 60,
				"resources": [
					"*"
				]
			},
			"mem": {
				"measurement": [
					"mem_used_percent"
				],
				"metrics_collection_interval": 60
			},
			"statsd": {
				"metrics_aggregation_interval": 60,
				"metrics_collection_interval": 10,
				"service_address": ":8125"
			}
		}
	}
}
Are you satisfied with the above config? Note: it can be manually customized after the wizard completes to add additional items.
1. yes
2. no
default choice: [1]:

Do you have any existing CloudWatch Log Agent (http://docs.aws.amazon.com/AmazonCloudWatch/latest/logs/AgentReference.html) configuration file to import for migration?
1. yes
2. no
default choice: [2]:

Do you want to monitor any log files?
1. yes
2. no
default choice: [1]:

Log file path:
/var/log/httpd/error_log
/var/log/nginx/error.log
Log group name:
default choice: [error_log]
s1web_php7_al2_error_log / prodweb_al2_error_log / Prod_CRON_al2_error_log
s1apiv2_lumen_error_log / prodapiv2_lumen_error_log
s1apiv2_error_log / prodapiv2_error_log
s1web_vuejs_al2_error_log / prodweb_vuejs_al2_error_log
Log stream name:
default choice: [{instance_id}]
s1web_php7_al2_error_log / prodweb_al2_error_log / Prod_CRON_al2_error_log
s1apiv2_lumen_error_log / prodapiv2_lumen_error_log
s1apiv2_error_log / prodapiv2_error_log
s1web_vuejs_al2_error_log / prodweb_vuejs_al2_error_log
Log Group Retention in days
1. -1
2. 1
3. 3
4. 5
5. 7
6. 14
7. 30
8. 60
9. 90
10. 120
11. 150
12. 180
13. 365
14. 400
15. 545
16. 731
17. 1827
18. 2192
19. 2557
20. 2922
21. 3288
22. 3653
default choice: [1]:
7
Do you want to specify any additional log files to monitor?
1. yes
2. no
default choice: [1]:

Log file path:
/var/log/httpd/access_log
/var/log/nginx/access.log
Log group name:
default choice: [access_log]
s1web_php7_al2_access_log / prodweb_al2_access_log / Prod_CRON_al2_access_log
s1apiv2_lumen_access_log / prodapiv2_lumen_access_log
s1apiv2_access_log / prodapiv2_access_log
s1web_vuejs_al2_access_log / prodweb_vuejs_al2_access_log
Log stream name:
default choice: [{instance_id}]
s1web_php7_al2_access_log / prodweb_al2_access_log / Prod_CRON_al2_access_log
s1apiv2_lumen_access_log / prodapiv2_lumen_access_log
s1apiv2_access_log / prodapiv2_access_log
s1web_vuejs_al2_access_log / prodweb_vuejs_al2_access_log
Log Group Retention in days
1. -1
2. 1
3. 3
4. 5
5. 7
6. 14
7. 30
8. 60
9. 90
10. 120
11. 150
12. 180
13. 365
14. 400
15. 545
16. 731
17. 1827
18. 2192
19. 2557
20. 2922
21. 3288
22. 3653
default choice: [1]:
7
Do you want to specify any additional log files to monitor?
1. yes
2. no
default choice: [1]:
2
Saved config file to /opt/aws/amazon-cloudwatch-agent/bin/config.json successfully.
Current config as follows:
{
	"agent": {
		"metrics_collection_interval": 60,
		"run_as_user": "root"
	},
	"logs": {
		"logs_collected": {
			"files": {
				"collect_list": [
					{
						"file_path": "/var/log/httpd/error_log",
						"log_group_name": "prodapiv2_error_log",
						"log_stream_name": "prodapiv2_error_log",
						"retention_in_days": 30
					},
					{
						"file_path": "/var/log/httpd/access_log",
						"log_group_name": "prodapiv2_access_log",
						"log_stream_name": "prodapiv2_access_log",
						"retention_in_days": 30
					}
				]
			}
		}
	},
	"metrics": {
		"aggregation_dimensions": [
			[
				"InstanceId"
			]
		],
		"append_dimensions": {
			"AutoScalingGroupName": "${aws:AutoScalingGroupName}",
			"ImageId": "${aws:ImageId}",
			"InstanceId": "${aws:InstanceId}",
			"InstanceType": "${aws:InstanceType}"
		},
		"metrics_collected": {
			"collectd": {
				"metrics_aggregation_interval": 60
			},
			"disk": {
				"measurement": [
					"used_percent"
				],
				"metrics_collection_interval": 60,
				"resources": [
					"*"
				]
			},
			"mem": {
				"measurement": [
					"mem_used_percent"
				],
				"metrics_collection_interval": 60
			},
			"statsd": {
				"metrics_aggregation_interval": 60,
				"metrics_collection_interval": 10,
				"service_address": ":8125"
			}
		}
	}
}
Please check the above content of the config.
The config file is also located at /opt/aws/amazon-cloudwatch-agent/bin/config.json.
Edit it manually if needed.
Do you want to store the config in the SSM parameter store?
1. yes
2. no
default choice: [1]:
2
Program exits now.
[ec2-user@ip-172-31-47-125 ~]$

For the question, Do you want to store the config in the SSM parameter store?, select No.

The final config files get stored in the following location/

sudo cat /opt/aws/amazon-cloudwatch-agent/bin/config.json

The config.json file should look like below

Saved config file to /opt/aws/amazon-cloudwatch-agent/bin/config.json successfully.
Current config as follows:
{
	"agent": {
		"metrics_collection_interval": 60,
		"run_as_user": "root"
	},
	"logs": {
		"logs_collected": {
			"files": {
				"collect_list": [
					{
						"file_path": "/var/log/httpd/error_log",
						"log_group_name": "prodweb_al2_error_log",
						"log_stream_name": "prodweb_al2_error_log"
					},
					{
						"file_path": "/var/log/httpd/access_log",
						"log_group_name": "prodweb_al2_access_log",
						"log_stream_name": "prodweb_al2_access_log"
					}
				]
			}
		}
	},
	...
	...
}

Start, Stop & check the status of the CloudWatch agent on the Linux Server

Start the CloudWatch agent with the above custom configuration

sudo /opt/aws/amazon-cloudwatch-agent/bin/amazon-cloudwatch-agent-ctl -a fetch-config -m ec2 -c file:/opt/aws/amazon-cloudwatch-agent/bin/config.json -s

Start the CloudWatch agent with system Default Config

sudo /opt/aws/amazon-cloudwatch-agent/bin/amazon-cloudwatch-agent-ctl -a fetch-config -s -m ec2

Status of CloudWatch agent

sudo /opt/aws/amazon-cloudwatch-agent/bin/amazon-cloudwatch-agent-ctl -m ec2 -a status

Stop CloudWatch agent

sudo /opt/aws/amazon-cloudwatch-agent/bin/amazon-cloudwatch-agent-ctl -m ec2 -a stop

Verify Logs on CloudWatch

Once the setup is done, you can view all the configured logs under the CloudWatch dashboard (under the logs option)

Go to Logs –> Log Groups and you will see the log group you mentioned in the agent configuration.
Select the log group and you should see the instance identified you mentioned in the config.
If you click the instance identifier, it shows all the logs. You can use the cloud watch filter option to filter and query required logs.

Analyzing log data with CloudWatch Logs Insights

CloudWatch Logs Insights enables you to interactively search and analyze your log data in Amazon CloudWatch Logs. You can perform queries to help you more efficiently and effectively respond to operational issues. If an issue occurs, you can use CloudWatch Logs Insights to identify potential causes and validate deployed fixes.

Steps :

Select the log group
Filter data using the query

Reference: How To Push Ec2 Logs To Cloudwatch [Logs And Metrics]

Thanks!
Prasanti Prusty

Update Table using JOIN in SQL Server

Update a table value from another Table using JOIN Query in SQL Server

Code :

    UPDATE
        A
    SET
        A.Colx = B.Coly
    FROM
        Table1 A
    JOIN
        Table2 B ON A.col1 = B.col5
    WHERE
        A.Col3 = 'xyz'

Convert Text to XML Data in SQL Server.

Convert Text to XML Data in SQL Server.

In the following Stored Procedure we can Use XML Data as Follows.
Send XML Data to Stored Procedure as "Text" Data Type.
In SP Convert this "Text" (XML Text) to "XML" Data
Read and Fetch the XML Data into Table.
Use the Cursor to Traverse through Data.

Code :

/**************************************************************************
 
-----------------------  
Developers Note   
-----------------------  
Object          : StoredProcedure [dbo].[canon_UpdateValidUserFromXML]      
Script Date     : 19/11/2012   
Developed By    : Rohit Shram  
Used For        : Update Valid  Users 
Implementaion   :   
    SELECT *  FROM [USER] where ID in ( 80309 ,80310)
    GO
    UpdateValidUserFromXML
    '<UsersList>
        <Users>
            <ID>80309</ID>
            <UserName>KARL TYLER</UserName>
            <Email>CRAIG.WASHINGTON80309@testmail.com</Email>
        </Users>
        <Users>
            <ID>80310</ID>
            <UserName>CHAD MARTIN</UserName>
            <Email>LEWIS.CUNNINGHAM80310@testmail.com</Email>
        </Users>
    </UsersList>',
    'File1234',
    80311
    GO
    SELECT *  FROM [USER] where ID in ( 80309 ,80310)

*************************************************************************************/  

CREATE PROCEDURE [dbo].[UpdateValidUserFromXML] 

    @UsersXml TEXT  
--'<UsersList><Users><ID>12</ID><UserName>KAYR</UserName><Email>CRAI@test.com</Email></Users></UsersList>' 
AS  
BEGIN  
    SET NOCOUNT ON;  
    
    BEGIN TRANSACTION Update
    
    BEGIN TRY
    
    DECLARE @idoc INT
    DECLARE @ID INT                  
    DECLARE @UserName NVARCHAR(500)                
    DECLARE @Email NVARCHAR(500)                
 
    -- Used to Keep ErrorList for all Users Data as Table
    DECLARE @UsersErrorList TABLE                  
    (                  
        ID INT,                 
        Name NVARCHAR(500),                
        Email NVARCHAR(500),                 
        Error NVARCHAR(MAX)       
    ) 
 
    -- Used to Keep XML Data as Table
    DECLARE @UsersList TABLE                  
    (                  
        ID INT,                 
        UserName NVARCHAR(500),                
        Email NVARCHAR(500)                
    ) 

    -- Prepare XML Doc
    EXEC sp_xml_preparedocument @idoc OUTPUT, @UsersXml                  
          
    -- Insert into Table Variable From XML
    INSERT @UsersList                
    (                 
        ID,                
        UserName,                 
        Email                
    )                  
    SELECT                   
        ID,                
        UserName,                 
        Email               
       
    FROM                  
    OPENXML (@idoc, '/UsersList/Users')                  
    WITH                 
    (                  
        ID INT 'ID',                 
        UserName NVARCHAR(500) 'UserName',                
        Email NVARCHAR(500) 'Email'               
    )                  
           
    -- Remove XML Do after getting Data
    EXEC sp_xml_removedocument @idoc           
   
    -- Declare Cursor
    DECLARE Cursor CURSOR FOR                  
    SELECT                  
    ID,                
    UserName,                 
    Email                
    FROM @UsersList                   
         
    -- Open Cursor
    OPEN Cursor                  
         
    -- Fetch Cursor
    FETCH NEXT FROM Cursor INTO                   
    @ID,                  
    @UserName,                  
    @Email       
         
    -- Traverse through Data                
    WHILE @@FETCH_STATUS = 0                
    BEGIN  
  
        -- Update User's Name and Email    
        UPDATE [USER] 
        SET Email = @Email, UserName = @UserName 
        WHERE ID = @ID 
  
        -- Fetch Cursor
        FETCH NEXT FROM Cursor INTO                   
        @ID,                  
        @UserName,                  
        @Email                 
                  
    END  -- WHILE @@FETCH_STATUS = 0                
                 
        CLOSE Cursor                  
        DEALLOCATE Cursor 
 
    END TRY
    BEGIN CATCH

        -- Rollback Transaction If any Transaction Occured
        IF @@ROWCOUNT > 0
            ROLLBACK TRANSACTION Update
  
        -- Insert Into Temp table to Keep Error Details for all Users
        INSERT INTO @UsersErrorList
        VALUES ( @ID , @UserName , @Email , ERROR_MESSAGE())

    END CATCH
 
    -- Returns Error ResultSet
    SELECT * FROM @UsersErrorList;  

    -- Commit Transaction If any Transaction Occured
    IF @@TRANCOUNT > 0
        COMMIT TRANSACTION Update
    
    SET NOCOUNT OFF;  

END

Use Try...Catch Block and Transactions in SQL Server

Use Try...Catch Block and Transactions in Stored Procedure as Followes :

Code :

/******************************************************************************    
-----------------------  
Developers Note   
-----------------------  
Object          : StoredProcedure [dbo].[sp_SetUsersDetails]     
Script Date     : 19/11/2012   
Developed By    : Rohan Sharma
Used For        : Add/Update User
Implementaion   :   

    sp_SetUsersDetails 5, 'Rohan Sharma', 'Block-B', '87867675765'

*****************************************************************************/ 
 
CREATE PROCEDURE [dbo].[sp_SetUsersDetails] 
    -- Parameters Here
    @ID   INT, 
    @Name  NVARCHAR(500), 
    @Address NVARCHAR(500) = NULL, 
    @Phone  NVARCHAR(500) = NULL, 
AS  
BEGIN  

    SET NOCOUNT ON;  

    BEGIN TRANSACTION UpdateLMS

    BEGIN TRY

        -- Put Your Tansaction DB Codes Here

    END TRY
    BEGIN CATCH

        -- Rollback Transaction If any Transaction Occured
        IF @@ROWCOUNT > 0
            ROLLBACK TRANSACTION UpdateLMS

        -- Put Any ErrorLog Codes Here

    END CATCH

    -- Commit Transaction If any Transaction Occured

    IF @@TRANCOUNT > 0
        COMMIT TRANSACTION UpdateLMS

    SET NOCOUNT OFF;  

END

Export Data From Excel File in ASP.Net

Export Data From Excel File in ASP.Net

The following function takes the following parameter :

"excelFileName" : Its specify the Path with File Name .i.e: "D:\MyExcel.xls"

Use the following NameSpace :

    using System.IO;
    using System.Reflection;
    using Microsoft.Office.Interop.Excel;

Code :

    /// <summary>
    /// This function convert uploaded Ecxcel sheet into DataTable and then returns 
    /// </summary>
    /// <param name="fileToConvert"></param>
    /// <param name="fileForDs"></param>
    /// <param name="uniqueFileName"></param>
    /// <returns></returns>
    public System.Data.DataTable ReadFromExcelFile(string excelFileName)
    {
        OleDbConnection connection = null;

        //this datatable contains Active Sheet  records 
        System.Data.DataTable dtImprtExcel = new System.Data.DataTable();

        try
        {

            // Get Active Sheet Name
            string activeSheetName = getActiveSheetName(excelFileName);

            //If Active Sheet Name is not Null or Blank
            if (!string.IsNullOrEmpty(activeSheetName))
            {

                // <add key="ExcelConnectionString" value="Provider=Microsoft.Jet.OLEDB.4.0;Data Source=Path;Extended Properties=Excel 8.0;"/>
                string connectionString = ConfigurationManager.AppSettings.Get("ExcelConnectionString").Replace("Path", excelFileName);
                OleDbConnection excelCon = new OleDbConnection(connectionString);
                excelCon.Open();
                OleDbDataAdapter adapter = new OleDbDataAdapter("SELECT * FROM [" + activeSheetName + "$]", excelCon);
                adapter.Fill(dtImprtExcel);
                excelCon.Close();

                //cleans up the temporary file that was stored
                File.Delete(excelFileName);
            }
        }
        catch (Exception ex)
        {
            throw ex;
        }
        finally
        {
            if (connection != null)
            {
                if (connection.State == ConnectionState.Open)
                {
                    //close a open connection
                    connection.Close();
                }
            }
        }
        return dtImprtExcel;
    }

Get Active Sheet Name :

The following function takes the following parameter :
"strFileName" : Its specify the Path with File Name .i.e: "D:\MyExcel.xls"

Use the following NameSpace :

    using System.IO;
    using System.Reflection;
    using Microsoft.Office.Interop.Excel;

Code :

    /// <summary>
    /// Get Excel Sheet Name
    /// </summary>
    /// <param name="path"></param>
    /// <returns></returns>
    private string getActiveSheetName(string strFileName)
    {
        string activeSheetName = string.Empty;
        try
        {
            Application oXL;
            Workbook oWB;
            Worksheet oSheet;

            // Start Excel and get Application object. 
            oXL = new Application();

            // Set some properties 
            oXL.Visible = false;
            oXL.DisplayAlerts = false;

            // Open the workbook. 
            oWB = oXL.Workbooks.Open(strFileName);

            // Get the active sheet 
            oSheet = (Worksheet)oWB.ActiveSheet;
            activeSheetName = oSheet.Name;

            // Save the sheet and close 
            oSheet = null;
            oWB.SaveAs(strFileName, Excel.XlFileFormat.xlWorkbookNormal,
                Missing.Value, Missing.Value, Missing.Value, Missing.Value,
                XlSaveAsAccessMode.xlExclusive,
                Missing.Value, Missing.Value, Missing.Value,
                Missing.Value, Missing.Value);
            oWB.Close(Missing.Value, Missing.Value, Missing.Value);
            oWB = null;
            oXL.Quit();

            // Clean up 
            // NOTE: When in release mode, this does the trick 
            GC.WaitForPendingFinalizers();
            GC.Collect();
            GC.WaitForPendingFinalizers();
            GC.Collect();
        }
        catch (Exception ex)
        {
            throw ex;
        }
        return activeSheetName;
    }

Get ActiveSheet Name of Uploaded Execl in ASP.Net

The following function takes the following parameter :

"strFileName" : Its specify the Path with File Name .i.e: "D:\MyExcel.xls"

Use the following NameSpace :

    using System.IO;
    using System.Reflection;
    using Microsoft.Office.Interop.Excel;

Code :

    /// <summary>
    /// Get Excel Sheet Name
    /// </summary>
    /// <param name="path"></param>
    /// <returns></returns>
    private string getActiveSheetName(string strFileName)
    {
        string activeSheetName = string.Empty;
        try
        {
            Application oXL;
            Workbook oWB;
            Worksheet oSheet;

            // Start Excel and get Application object. 
            oXL = new Application();

            // Set some properties 
            oXL.Visible = false;
            oXL.DisplayAlerts = false;

            // Open the workbook. 
            oWB = oXL.Workbooks.Open(strFileName);

            // Get the active sheet 
            oSheet = (Worksheet)oWB.ActiveSheet;
            activeSheetName = oSheet.Name;

            // Save the sheet and close 
            oSheet = null;
            oWB.SaveAs(strFileName, Excel.XlFileFormat.xlWorkbookNormal,
                Missing.Value, Missing.Value, Missing.Value, Missing.Value,
                XlSaveAsAccessMode.xlExclusive,
                Missing.Value, Missing.Value, Missing.Value,
                Missing.Value, Missing.Value);
            oWB.Close(Missing.Value, Missing.Value, Missing.Value);
            oWB = null;
            oXL.Quit();

            // Clean up 
            // NOTE: When in release mode, this does the trick 
            GC.WaitForPendingFinalizers();
            GC.Collect();
            GC.WaitForPendingFinalizers();
            GC.Collect();
        }
        catch (Exception ex)
        {
            throw ex;
        }
        return activeSheetName;
    }

Download Excel in ASP.Net

The Following Function downloads the Excel .

It accepts the following paramete :
fileName : It contains the path with FileName . I.e : "D:\MyExcel.exe".

Code :

    /// <summary>
    /// Download Excel template with some existing data
    /// </summary>
    /// <param name="fileName"></param>
    private void DownloadExcel(string fileName)
    {
        try
        {
            FileInfo file = new FileInfo(fileName);
            if (file.Exists)
            {
                // Clear the content of the response
                Response.ClearContent();
                Response.Clear();

                // add the header that specifies the default filename for the Download/SaveAs dialog
                Response.AddHeader("Content-Disposition", "attachment; filename=LMSUsers.xls");

                // add the header that specifies the file size, so that the browser can show the download progress
                Response.AddHeader("Content-Length", file.Length.ToString());

                // specify that the response is a stream that cannot be read by the client and must be downloaded
                Response.ContentType = "application/vnd.ms-excel";

                // send the file stream to the client
                Response.WriteFile(file.FullName);
                Response.BufferOutput = true;
                Response.Flush();
                Response.Close();

                //Delete the file if Required
                file.Delete();
            }
        }
        catch (Exception ex)
        {
            throw new Exception(ex.InnerException.Message);
        }
    }

Create Excel with Data in ASP.Net

Create Excel in ASP.Net with Dynamic data .

The following function takes the following parameter :
"DataTable" : which holds the data from Database.
"strFileName" : It is specify the Path with File Name .i.e: "D:\MyExcel.xls"

Use the following NameSpace :

    using System.IO;
    using System.Reflection;
    using Microsoft.Office.Interop.Excel;

Code :

    /// <summary>
    /// Creates the excel file from the provided dataset.
    /// </summary>
    /// <param name="dtSource">The dt source.</param>
    /// <param name="strFileName">Name of the STR file.</param>
    private void GenerateExcel(System.Data.DataTable dtSource, string strFileName)
    {
        Application oXL;
        Workbook oWB;
        Worksheet oSheet;
        Range oRange;

        // Start Excel and get Application object. 
        oXL = new Application();

        // Set some properties 
        oXL.Visible = false;
        oXL.DisplayAlerts = false;

        // Get a new workbook. 
        oWB = oXL.Workbooks.Add(Missing.Value);

        // Get the active sheet 
        oSheet = (Worksheet)oWB.ActiveSheet;
        oSheet.Name = "LMS Users";
        int rowCount = 1;

        // Add Headrer to Excel
        for (int i = 1; i < dtSource.Columns.Count + 1; i++)
        {
            oSheet.Cells[1, i] = dtSource.Columns[i - 1].ColumnName;
            oRange = (Microsoft.Office.Interop.Excel.Range)oSheet.Cells[1, i];
            oRange.Font.Bold = true;
        }

        // Add the Data Rows to the Excel
        if (dtSource.Rows.Count > 0)
        {
            foreach (DataRow dr in dtSource.Rows)
            {
                rowCount += 1;
                for (int i = 1; i < dtSource.Columns.Count + 1; i++)
                {
                    if (rowCount > 1)
                    {
                        oSheet.Cells[rowCount, i] = dr[i - 1].ToString();
                    }
                }
            }
        }
        
        // Resize the columns 
        oRange = oSheet.get_Range(oSheet.Cells[1, 1],
                      oSheet.Cells[rowCount, dtSource.Columns.Count]);
        oRange.EntireColumn.AutoFit();

        // Save the sheet and close 
        oSheet = null;
        oRange = null;
        oWB.SaveAs(strFileName, Excel.XlFileFormat.xlWorkbookNormal,
            Missing.Value, Missing.Value, Missing.Value, Missing.Value,
            XlSaveAsAccessMode.xlExclusive,
            Missing.Value, Missing.Value, Missing.Value,
            Missing.Value, Missing.Value);
        oWB.Close(Missing.Value, Missing.Value, Missing.Value);
        oWB = null;
        oXL.Quit();

        // Clean up 
        // NOTE: When in release mode, this does the trick 
        GC.WaitForPendingFinalizers();
        GC.Collect();
        GC.WaitForPendingFinalizers();
        GC.Collect();
    }

How to get File and Function Details from Exception object

Its better to use ErrorLog in Exception Handling. In ErrorLog we need to keep track of the File and the Function details where this Exception occurs. So, instead of sending the Hardcoded Values (i.e : Function Name , File Name ) to ErrorLog, we can directly get this details from Exception object (i.e : Exception ex) as follows :

Code :

Use namespace :

using System.Diagnostics;
using System.Reflection;

Function where Error Occurred :

        /// <summary>
        /// Set AssetName for the AssetID
        /// </summary>
        /// <param name="programId"></param>
        /// <param name="languageId"></param>
        /// <param name="assetId"></param>
        /// <param name="assetName"></param>
        public void SetAssetName(Int32 progId, Int32 langId, int astId, string astName)
        {
            AssetClient assetClient = null;
            try
            {
                assetClient = new AssetClient(appBindingAssets);
                //return assetClient.SetAssetName(progmId, langId, astId, astName);
            }
            catch (Exception ex)
            {
                //call ErrorLog using the File Details
                LogError(ex);
            }
            finally
            {
                if (assetClient != null)
                {
                    assetClient.Close();
                }
            }
        }

ErrorLog Function accepts Exception object as Parameter :

        /// <summary>
        /// Logs the exception. 
        /// </summary>
        /// <param name="ex"></param>
        public static void LogError(Exception ex)
        {
            // Get the File Details from Exception
            StackTrace st = new StackTrace(ex);
 
            // Get the object of MethodBase from StackTrace
            MethodBase mb = st.GetFrame(st.FrameCount - 1).GetMethod();
 
            // Get FileName with NameSpace 
            // Ex: IGroup.Modules.SalesZone.Controller.SalesZoneController"
            string source = mb.DeclaringType.FullName;
 
            // Get Function Name "GetPhraseTranslationList"
            string methodName = mb.Name;
 
            //Implement ErrorLog Logics here using the File Details
            //AddLogError(source, ex, methodName);
        }

SQL JOINS

Let check here a overall picture of JOIN

Tech Stack Pool

Labels

Push Logs to AWS CloudWatch

Application Logs To AWS CloudWatch Workflow

Setup CloudWatch on the instance

Install CloudWatch agent

Configure profiles for the AWS CLI

Create the CloudWatch agent configuration file

Start, Stop & check the status of the CloudWatch agent on the Linux Server

Start the CloudWatch agent with the above custom configuration

Start the CloudWatch agent with system Default Config

Status of CloudWatch agent

Stop CloudWatch agent

Verify Logs on CloudWatch

Analyzing log data with CloudWatch Logs Insights

Update Table using JOIN in SQL Server

Convert Text to XML Data in SQL Server.

Use Try...Catch Block and Transactions in SQL Server

Export Data From Excel File in ASP.Net

Get ActiveSheet Name of Uploaded Execl in ASP.Net

Download Excel in ASP.Net

Create Excel with Data in ASP.Net

How to get File and Function Details from Exception object

SQL JOINS