In this article, I am going to tell you how to create a procedure in the real time scenario. A stored procedure is a set of SQL statements, which has been created and stored in the database as an object. Stored procedure will accept the input and output parameters, so that a single procedure can be used over the network by several users, using different input. Stored procedure will reduce the network traffic and increase the performance.

Real time scenario
Step 1: Create a table to describe and create the stored procedure.
    create table Product 
    ( 
          ProductId int primary key, 
          ProductName varchar(20) unique, 
          ProductQty int, 
          ProductPrice float 
    ) 

Step 2: Insert some value to the describe scenario.
    insert product values(1,'Printer',10,4500) 
    insert product values(2,'Scanner',15,3500) 
    insert product values(3,'Mouse',45,500)  

Step 3: Check your table with the inserted value.
    select * from product  

Step 4: Real time scenario is given below:
Create a stored procedure, which is used to perform the requirements, given below:

Before inserting, check the detail about the product name. If the product name is available, update an existing product qty + inserted product qty,

• Before inserting, check the detail about the product name.
  
• If the product name is available, check the product price.
• If the existing product price is less, the inserted product product price replaces the existing product price with the inserted product price.
• If first and second conditions are not satisfied, insert the product information, as new record into the table.

    create procedure prcInsert  
    @id int, 
    @name varchar(40), 
    @qty int, 
    @price float 
    as 
    begin 
     declare @cnt int 
     declare @p float 
     select @cnt=COUNT(ProductId)from Product where pname=@name 
     if(@cnt>0) 
     begin 
      update Product set ProductQty=ProductQty+@qty where ProductName=@name 
      select @p=ProductPrice from Product where ProductName=@name 
      if(@p<@price) 
      begin 
       update Product set ProductPrice=@price where ProductName=@name 
      end 
     end 
     else 
     begin 
      insert Product values(@id,@name,@qty,@price) 
     end 
    end  

HostForLIFE.eu SQL Server 2016 Hosting
HostForLIFE.eu is European Windows Hosting Provider which focuses on Windows Platform only. We deliver on-demand hosting solutions including Shared hosting, Reseller Hosting, Cloud Hosting, Dedicated Servers, and IT as a Service for companies of all sizes.

Angular js URL:
<script src="http://ajax.googleapis.com/ajax/libs/angularjs/1.3.14/angular.min.js"></script> 

Html tag:
<div ng-app="myApp" ng-controller="myCntrl"> 
    <table> 
        <thead> 
            <tr> 
                <th> 
                    Emp Code. 
                </th> 
                <th> 
                    Employee Name 
                </th> 
                <th> 
                    Pan No 
                </th> 
                  
            </tr> 
        </thead> 
        <tr ng-repeat="student in EmployeeList"> 
            <td ng-bind="student.StudentID"> 
            </td> 
            <td ng-bind="student.StudentName"> 
            </td> 
            <td ng-bind="student.PanNO"> 
            </td> 
              
        </tr> 
    </table> 
</div> 

Angular js Script :

<script> 
   var app = angular.module("myApp", []); 
   app.controller("myCntrl", function ($scope, $http) { 

       $scope.fillList = function () { 
           $scope.EmployeeName = ""; 
           var httpreq = { 
               method: 'POST', 
               url: 'Default2.aspx/GetList', 
               headers: { 
                   'Content-Type': 'application/json; charset=utf-8', 
                   'dataType': 'json' 
               }, 
               data: {} 
           } 
           $http(httpreq).success(function (response) { 
               $scope.EmployeeList = response.d; 
           }) 
       }; 
       $scope.fillList(); 
   }); 
</script> 

Asp.net Cs page code:
using System; 
using System.Collections.Generic; 
using System.Data.SqlClient; 
using System.Data; 

public partial class Default2 : System.Web.UI.Page 
{ 
protected void Page_Load(object sender, EventArgs e) 
{ 

} 
[System.Web.Services.WebMethod()] 
public static List<Employee> GetList() 
{ 
    List<Employee> names = new List<Employee>(); 
    DataSet ds = new DataSet(); 
    using (SqlConnection con = new SqlConnection(@"Data Source=140.175.165.10;Initial Catalog=Payroll_290716;user id=sa;password=Goal@12345;")) 
    { 
        using (SqlCommand cmd = new SqlCommand()) 
        { 
            cmd.Connection = con; 
            cmd.CommandText = "select EmpId,Empcode, name,PanNo from EMPLOYEEMASTER  order by Name;"; 
            using (SqlDataAdapter da = new SqlDataAdapter(cmd)) 
            { 
                da.Fill(ds); 
            } 
        } 
    } 
    if (ds != null && ds.Tables.Count > 0) 
    { 
        foreach (DataRow dr in ds.Tables[0].Rows) 
            names.Add(new Employee(int.Parse(dr["EmpId"].ToString()), dr["name"].ToString(), dr["PanNo"].ToString())); 
    } 
    return names; 
} 
} 
public class Employee 
{ 
public int StudentID; 
public string StudentName; 
public string PanNO; 
public Employee(int _StudentID, string _StudentName, string _PanNO) 
{ 
    StudentID = _StudentID; 
    StudentName = _StudentName; 
    PanNO = _PanNO; 
} 
}  

Whole HTML page:
<%@ Page Language="C#" AutoEventWireup="true" CodeFile="Default2.aspx.cs" Inherits="Default2" %> 

<!DOCTYPE html PUBLIC "-//W3C//DTD XHTML 1.0 Transitional//EN" "http://www.w3.org/TR/xhtml1/DTD/xhtml1-transitional.dtd"> 
<html xmlns="http://www.w3.org/1999/xhtml"> 
<head runat="server"> 
<title></title> 
<script src="http://ajax.googleapis.com/ajax/libs/angularjs/1.3.14/angular.min.js"></script> 
</head> 
<body> 
<form id="form1" runat="server"> 
<div ng-app="myApp" ng-controller="myCntrl"> 
    <table> 
        <thead> 
            <tr> 
                <th> 
                    Emp Code. 
                </th> 
                <th> 
                    Employee Name 
                </th> 
                <th> 
                    Pan No 
                </th> 
                  
            </tr> 
        </thead> 
        <tr ng-repeat="student in EmployeeList"> 
            <td ng-bind="student.StudentID"> 
            </td> 
            <td ng-bind="student.StudentName"> 
            </td> 
            <td ng-bind="student.PanNO"> 
            </td> 
              
        </tr> 
    </table> 
</div> 
<script> 
    var app = angular.module("myApp", []); 
    app.controller("myCntrl", function ($scope, $http) { 

        $scope.fillList = function () { 
            $scope.EmployeeName = ""; 
            var httpreq = { 
                method: 'POST', 
                url: 'Default2.aspx/GetList', 
                headers: { 
                    'Content-Type': 'application/json; charset=utf-8', 
                    'dataType': 'json' 
                }, 
                data: {} 
            } 
            $http(httpreq).success(function (response) { 
                $scope.EmployeeList = response.d; 
            }) 
        }; 
        $scope.fillList(); 
    }); 
</script> 
</form> 
</body> 
</html>  

HostForLIFE.eu AngularJS Hosting

HostForLIFE.eu is European Windows Hosting Provider which focuses on Windows Platform only. We deliver on-demand hosting solutions including Shared hosting, Reseller Hosting, Cloud Hosting, Dedicated Servers, and IT as a Service for companies of all sizes.

In this post, let me show you how to fix SQL Server Can't Connect to Local Host. Many times we find issues when connecting to the SQL Server. It gives us the message “SQL Server is not able to connect to  local host” as you can see on the following picture:

To fix this issue:
Go to Start->Run->Services.msc.

Once the Services are open, select SQL Server and start it, as per the given screenshot, given below:

After you do it, SQL server will be up and running again.

SQL Server sometimes stops because of some problem. These steps help to make it up. Thanks for reading.

HostForLIFE.eu SQL Server 2012 Hosting
HostForLIFE.eu is European Windows Hosting Provider which focuses on Windows Platform only. We deliver on-demand hosting solutions including Shared hosting, Reseller Hosting, Cloud Hosting, Dedicated Servers, and IT as a Service for companies of all sizes.

In this post, I will show you how to Convert String To Color in SQL Server. The following code will describe how to convert String To Color In SQL Server 2016. And now write code below:

CREATE FUNCTION dbo.fn_conversion_string_color(  

@in_string VARCHAR(200)  

)  

RETURNS NVARCHAR(500)  

AS  

BEGIN  

DECLARE @fsetprefix BIT, -- append '0x' to the output   

@pbinin VARBINARY(MAX), -- input binary stream   

@startoffset INT, -- starting offset    

@cbytesin INT, -- length of input to consider, 0 means total length   

@pstrout NVARCHAR(MAX),   

@i INT,   

@firstnibble INT ,  

@secondnibble INT,   

@tempint INT,  

@hexstring CHAR(16)  

SELECT @fsetprefix = 1,  

@pbinin = SUBSTRING(HASHBYTES('SHA1', @in_string), 1, 3),  

@startoffset = 1,  

@cbytesin = 0   

-- initialize and validate   

IF (@pbinin IS NOT NULL)   

BEGIN    

SELECT @i = 0,   

@cbytesin = CASE  WHEN (@cbytesin > 0 AND @cbytesin <= DATALENGTH(@pbinin))  

  THEN @cbytesin  

  ELSE DATALENGTH(@pbinin)  

  END,   

@pstrout =  CASE  WHEN (@fsetprefix = 1)  

  THEN N'0x'  

  ELSE N''  

  END,   

@hexstring = '0123456789abcdef'   

--the output limit for nvarchar(max) is 2147483648 (2^31) bytes, that is 1073741824 (2^30) unicode characters   

IF (  

((@cbytesin * 2) + 2 > 1073741824)  

OR ((@cbytesin * 2) + 2 < 1)  

OR (@cbytesin IS NULL )  

)   

RETURN NULL   

IF (  

( @startoffset > DATALENGTH(@pbinin) )  

OR (@startoffset < 1 )  

OR (@startoffset IS NULL )  

)   

RETURN NULL   

-- adjust the length to process based on start offset and total length   

IF ((DATALENGTH(@pbinin) - @startoffset + 1) < @cbytesin)   

SELECT @cbytesin = DATALENGTH(@pbinin) - @startoffset + 1   

-- do for each byte   

WHILE (@i < @cbytesin)   

BEGIN   

-- Each byte has two nibbles  which we convert to character   

SELECT @tempint = CAST(SUBSTRING(@pbinin, @i + @startoffset, 1) AS INT)   

SELECT @firstnibble = @tempint / 16   

SELECT @secondnibble = @tempint % 16   

-- we need to do an explicit cast with substring for proper string conversion.     

SELECT @pstrout = @pstrout +   

  CAST(SUBSTRING(@hexstring, (@firstnibble+1), 1) AS NVARCHAR) +   

  CAST(SUBSTRING(@hexstring, (@secondnibble+1), 1) AS NVARCHAR)   

SELECT @i = @i + 1   

END   

END   

RETURN  '#' + UPPER(RIGHT(@pstrout, 6))  

 END  

HostForLIFE.eu SQL 2016 Hosting
European best, cheap and reliable ASP.NET hosting with instant activation. HostForLIFE.eu is #1 Recommended Windows and ASP.NET hosting in European Continent. With 99.99% Uptime Guaranteed of Relibility, Stability and Performace. HostForLIFE.eu security team is constantly monitoring the entire network for unusual behaviour. We deliver hosting solution including Shared hosting, Cloud hosting, Reseller hosting, Dedicated Servers, and IT as Service for companies of all size.

OnceSQL Server Full-Text Search with rank values  I wrote a post titled enabling Fulltext search in Azure SQL database discussing Full-Text search in Azure. while using it with one of my databases, needed to show the result of the search ordered by however well they match to the search criteria. in order to sort the result as i need, the best is, get a rank generated for every row and use it for ordering the result. I had used Freetext operate for obtaining the result but if i realized that this can not be achieved using the Freetext function.

The CONTAINSTABLE and FREETEXTTABLE functions return a column named Rank for showing the rank related to the record based on matching. this can be used get the result sorted based on it, showing most relevant records at the top. Remember, the higher value of the Rank generated indicates the best matching.

Now, write the following code:
view plainprint?

    -- Creating a table  
    CREATE TABLE dbo.EmployeeDetails  
    (  
     EmployeeDetailsId int identity(1,1) not null  
     , constraint pk_EmployeeDetails primary key (EmployeeDetailsId)  
     , WorkingExperience nvarchar(4000) not null  
     , ProjectsWorked nvarchar(4000) not null  
     , Resume nvarchar(max)   
    )  
    GO  
      
    CREATE FULLTEXT CATALOG EmployeeCatelog;  
    GO  
      
    CREATE FULLTEXT INDEX ON dbo.EmployeeDetails   
     (WorkingExperience, ProjectsWorked, Resume) KEY INDEX pk_EmployeeDetails  
     ON EmployeeCatelog;  
     -- By default CHANGE_TRACKING = AUTO  
      
      
    -- Once enabled, search can be performed;  
    SELECT *  
    FROM dbo.EmployeeDetails  
    WHERE freetext ((WorkingExperience, ProjectsWorked, Resume), 'SQL');  
      
    SELECT *  
    FROM dbo.EmployeeDetails  
    WHERE freetext ((Resume), 'SQL');  
      
    -- Get the rank and sort the result using it  
    SELECT t.Rank, e.*  
    FROM dbo.EmployeeDetails e  
     INNER JOIN CONTAINSTABLE (dbo.EmployeeDetails, (WorkingExperience, ProjectsWorked, Resume), 'SQL') AS t  
      ON e.EmployeeDetailsId = t.[Key]  
    ORDER BY t.Rank DESC  

HostForLIFE.eu SQL 2016 Hosting
European best, cheap and reliable ASP.NET hosting with instant activation. HostForLIFE.eu is #1 Recommended Windows and ASP.NET hosting in European Continent. With 99.99% Uptime Guaranteed of Relibility, Stability and Performace. HostForLIFE.eu security team is constantly monitoring the entire network for unusual behaviour. We deliver hosting solution including Shared hosting, Cloud hosting, Reseller hosting, Dedicated Servers, and IT as Service for companies of all size.

In This code snippet, i will tell you about how Uploading Excelsheet Data in SQL Server Table. Now, write the followind code:

protected void btnSend_Click(object sender, EventArgs e) { 
    try { 
        string path = string.Concat(Server.MapPath("~/File/" + fileuploadExcel.FileName)); 
        fileuploadExcel.SaveAs(path); 
        string connExcelString = string.Format("Provider=Microsoft.ACE.OLEDB.12.0;Data Source={0};Extended Properties=\"Excel 12.0 Xml;HDR=YES;IMEX=1\";", path); 
        OleDbConnection excelConnection = new OleDbConnection(connExcelString); 
        OleDbCommand cmd = new OleDbCommand("Select [Name],[Mobile],[Email],[City],[DataId],[Date],[Source] from [Sheet1$]", excelConnection); 
        excelConnection.Open(); 
        OleDbDataReader dReader; 
        dReader = cmd.ExecuteReader(); 
        SqlBulkCopy sqlBulk = new SqlBulkCopy(strConnection); 
        SqlBulkCopyColumnMapping mapName = new SqlBulkCopyColumnMapping("Name", "Name"); 
        sqlBulk.ColumnMappings.Add(mapName); 
        SqlBulkCopyColumnMapping mapMobile = new SqlBulkCopyColumnMapping("Mobile", "Mobile"); 
        sqlBulk.ColumnMappings.Add(mapMobile); 
        SqlBulkCopyColumnMapping mapEmail = new SqlBulkCopyColumnMapping("Email", "Email"); 
        sqlBulk.ColumnMappings.Add(mapEmail); 
        SqlBulkCopyColumnMapping mapCity = new SqlBulkCopyColumnMapping("City", "City"); 
        sqlBulk.ColumnMappings.Add(mapCity); 
        //SqlBulkCopyColumnMapping mapState = new SqlBulkCopyColumnMapping("State", "State"); 
        //sqlBulk.ColumnMappings.Add(mapState); 
        SqlBulkCopyColumnMapping mapDataId = new SqlBulkCopyColumnMapping("DataId", "DataId"); 
        sqlBulk.ColumnMappings.Add(mapDataId); 
        SqlBulkCopyColumnMapping mapAmount = new SqlBulkCopyColumnMapping("Date", "Date"); 
        sqlBulk.ColumnMappings.Add(mapAmount); 
        SqlBulkCopyColumnMapping mapSource = new SqlBulkCopyColumnMapping("Source", "Source"); 
        sqlBulk.ColumnMappings.Add(mapSource); 
        //Give your Destination table name 
        sqlBulk.DestinationTableName = "UploadedExcelData"; 
        sqlBulk.WriteToServer(dReader); 
        excelConnection.Close(); 
        UpdateRecords(); 
        lblMsg.Text = "File Data Uploaded Successfully... "; 
        File.Delete(path); 
    } catch (Exception ex) { 
        lblMsg.Text = "Something Went Wrong... Plz Check Excel File "; 
        //string script = "<script>alert('" + ex.Message + "');</script>"; 
    } 
}

HostForLIFE.eu SQL Server 2012 Hosting
HostForLIFE.eu is European Windows Hosting Provider which focuses on Windows Platform only. We deliver on-demand hosting solutions including Shared hosting, Reseller Hosting, Cloud Hosting, Dedicated Servers, and IT as a Service for companies of all sizes.

In this post, i will tell you about ng ng-options instance but remove the selected items from previous uses in Angularjs. Now write the following code:

    <!DOCTYPE html> 
    <html> 
     
    <head> 
        <title></title> 
        <meta charset="utf-8" /> 
        <script src="Scripts/angular.js"></script> 
        <link href="Content/bootstrap.css" rel="stylesheet" /> 
        <script> 
    var app = angular.module('myApp', []); 
    app.controller('demoCtrl', function ($scope) 
    { 
        $scope.options = [ 
        { 
            name: 'Apple', 
            id: '1' 
        }, 
        { 
            name: 'Orange', 
            id: '2' 
        }, 
        { 
            name: 'Banana', 
            id: '3' 
        }, 
        { 
            name: 'Pear', 
            id: '4' 
        }, ]; 
    }); 
        </script> 
    </head> 
     
    <body> 
        <div ng-app="myApp" ng-controller="demoCtrl"> 
            <div class="form-group col-md-3"> 
                <label for="select1">Select 1:</label> 
                <select ng-model="newForm.select1" ng-options="option.name as option.name for option in options" class="form-control"> 
                    <option value=""></option> 
                </select> 
            </div> 
            <div class="row"> 
                <br> </div> 
            <div class="form-group col-md-3"> 
                <label for="select2">Select 2:</label> 
                <select ng-model="newForm.select2" ng-options="option.name as option.name for option in options | filter: newForm.select1 && {name: '!' + newForm.select1}" class="form-control"> 
                    <option value=""></option> 
                </select> 
            </div> 
        </div> 
    </body> 
     
    </html> 

HostForLIFE.eu AngularJS Hosting

HostForLIFE.eu is European Windows Hosting Provider which focuses on Windows Platform only. We deliver on-demand hosting solutions including Shared hosting, Reseller Hosting, Cloud Hosting, Dedicated Servers, and IT as a Service for companies of all sizes.

In this post, I'll share few useful SQL queries for database analysis on SQL Server 2012. I shared few SQL queries useful in analyzing database, which I use quite often. This query will return all table names and no.of rows in it for built-in tables.

    -- List all table names and number of rows in it for user-defined tables 
    SELECT distinct t.name,prt.rows 
    FROM sys.tables t INNER JOIN sys.partitions AS prt 
    ON t.object_id = prt.object_id where t.is_ms_shipped=1 -- 0 for user-defined tables 
    order by prt.rows desc 

This query will return column names and its data type of a table.
    -- Get column names and its types of a table 
    SELECT cols.name,t.name 
    FROM sys.objects o join sys.columns cols on o.object_id= cols.object_id 
    join sys.types t on t.system_type_id=cols.system_type_id 
    and o.name='Employee'-- Table Name

This query will return file name, its size and file group name of a database.
    SELECT sdf.name AS [FileName], 
    size/128 AS [Size], 
    fg.name AS [File_Group_Name] 
    FROM sys.database_files sdf 
    INNER JOIN 
    sys.filegroups fg 
    ON sdf.data_space_id=fg.data_space_id 

Batch file to execute all sql files in a directory, Save it as .bat in a folder that have sql script files to be executed.
    @Echo Off 
    FOR /f %%i IN ('DIR *.Sql /B') do call :RunSql %%i 
    GOTO :END 
    :RunSql 
    Echo Executing SQL: %1 
    SQLCMD -S server1 -U user1 -P pwd1 -d DB1 -i %1 
    Echo Completed SQL: %1 
    :END 

This query will return all table names that have a Foreign key:
    SELECT SCHEMA_NAME(schema_id) AS SchemaName, 
    name AS TableName 
    FROM sys.tables where OBJECTPROPERTY(OBJECT_ID,'TableHasForeignKey') = 1 -- Return all

HostForLIFE.eu SQL Server 2012 Hosting
HostForLIFE.eu is European Windows Hosting Provider which focuses on Windows Platform only. We deliver on-demand hosting solutions including Shared hosting, Reseller Hosting, Cloud Hosting, Dedicated Servers, and IT as a Service for companies of all sizes.

Today, I will show you How to Use sys.dm_os_performance_counters to get your Buffer cache hit ratio. In order to calculate the Buffer cache hit ratio we need to query the sys.dm_os_performance_counters dynamic management view. There are 2 counters we need in order to do our calculation, one counter is Buffer cache hit ratio and the other counter is Buffer cache hit ratio base. We divide Buffer cache hit ratio base by Buffer cache hit ratio and it will give us the Buffer cache hit ratio.

Here is the query that will do that, this query will only work on SQL Server 2005 and up.
SELECT (a.cntr_value * 1.0 / b.cntr_value) * 100.0 AS BufferCacheHitRatio
FROM sys.dm_os_performance_counters  a
JOIN  (SELECT cntr_value,OBJECT_NAME

FROM sys.dm_os_performance_counters 
WHERE counter_name = 'Buffer cache hit ratio base'
AND OBJECT_NAME = 'SQLServer:Buffer Manager') b ON  a.OBJECT_NAME = b.OBJECT_NAME
WHERE a.counter_name = 'Buffer cache hit ratio'

AND a.OBJECT_NAME = 'SQLServer:Buffer Manager'

Page life expectancy
Now let's look at Page life expectancy. Page life expectancy is the number of seconds a page will stay in the buffer pool, ideally it should be above 300 seconds. If it is less than 300 seconds this could indicate memory pressure, a cache flush or missing indexes.

Here is how to get the Page life expectancy
SELECT *
FROM sys.dm_os_performance_counters
WHERE counter_name = 'Page life expectancy'

HostForLIFE.eu SQL 2016 Hosting
European best, cheap and reliable ASP.NET hosting with instant activation. HostForLIFE.eu is #1 Recommended Windows and ASP.NET hosting in European Continent. With 99.99% Uptime Guaranteed of Relibility, Stability and Performace. HostForLIFE.eu security team is constantly monitoring the entire network for unusual behaviour. We deliver hosting solution including Shared hosting, Cloud hosting, Reseller hosting, Dedicated Servers, and IT as Service for companies of all size.

Today, let me explain you about Export to Excel in c# from list and using Linq filter. You will find lot export to excel coding in other website. I'm not going to give any new in this. I am also giving here with simple List collection with basic LINQ filter and export to excel sheet.
Note: Include the Microsoft.Office.Interop.Excel in your project.

List Collection With LINQ Filter:
    Eg: lstLocal = lstFlspc.Where(m => m.ClientLocation == “Chennai”).ToList();

Export to Excel in c#.net with List and LINQ Filter Query:
    public void ExportToExcel(List<FileInfoLocal> lst)
    {
        try
        {
//Filter in List collection
          lstLocal = lstFlspc.Where(m => m.ClientPath.Path == child.Text).ToList();


          Microsoft.Office.Interop.Excel.Application xlApp = new Microsoft.Office.Interop.Excel.Application();

          if (xlApp == null)
          {
              MessageBox.Show("Excel is not properly installed!!");
              return;
          }   

          Microsoft.Office.Interop.Excel.Workbook xlWorkBook;
          Microsoft.Office.Interop.Excel.Worksheet xlWorkSheet;
          object misValue = System.Reflection.Missing.Value;

          xlWorkBook = xlApp.Workbooks.Add(misValue);
          xlWorkSheet =(Microsoft.Office.Interop.Excel.Worksheet)xlWorkBook.Worksheets.get_Item(1);
             

          int iRow = 1, iColumn = 1;
          //Header Test
          xlWorkSheet.Cells[iRow, iColumn] = "Column1 Name";
          xlWorkSheet.Cells[iRow, iColumn + 1] = "Column2 Name";
          xlWorkSheet.Cells[iRow, iColumn + 2] = "Column3 Name";
          xlWorkSheet.Cells[iRow, iColumn + 3] = "Column4 Name";
          xlWorkSheet.Cells[iRow, iColumn + 4] = "Column5 Name";
          iRow++;

// Filter in List collection using LINQ

          if (lstLocal!= null)
          {
          foreach (var item in lstLocal)
            {
              // .. Add other parameters here. Body Text
              xlWorkSheet.Cells[iRow, iColumn] = item.Name;
              xlWorkSheet.Cells[iRow, iColumn + 1] = item.Date;
              xlWorkSheet.Cells[iRow, iColumn + 2] = item.Path;
              xlWorkSheet.Cells[iRow, iColumn + 3] = item.UserName;
              xlWorkSheet.Cells[iRow, iColumn + 4] = item.Desc;
              iRow++;                                  
            }
          }

         string sExcelFile = Directory.GetCurrentDirectory() + "Report.xls";

         xlWorkBook.SaveAs(sExcelFile, Microsoft.Office.Interop.Excel.XlFileFormat.xlWorkbookNormal, misValue, misValue, misValue, misValue, Microsoft.Office.Interop.Excel.XlSaveAsAccessMode.xlExclusive, misValue, misValue, misValue, misValue, misValue);
         xlWorkBook.Close(true, misValue, misValue);
         xlApp.Quit();

         releaseObject(xlWorkSheet);
         releaseObject(xlWorkBook);
         releaseObject(xlApp);

         MessageBox.Show("Excel file created successfully , you can find the file " + sExcelFile, "Export To Excell");

        }
        catch (Exception ex)
        {
            MessageBox.Show(ex.Message);
        }
    }



    // Release the Excel object from memory
    private void releaseObject(object obj)
    {
        try
        {
            System.Runtime.InteropServices.Marshal.ReleaseComObject(obj);
            obj = null;
        }
        catch (Exception ex)
        {
            obj = null;
            MessageBox.Show("Exception Occured while releasing object " + ex.ToString());
        }
        finally
        {
            GC.Collect();
        }
    }

HostForLIFE.eu SQL 2016 Hosting
European best, cheap and reliable ASP.NET hosting with instant activation. HostForLIFE.eu is #1 Recommended Windows and ASP.NET hosting in European Continent. With 99.99% Uptime Guaranteed of Relibility, Stability and Performace. HostForLIFE.eu security team is constantly monitoring the entire network for unusual behaviour. We deliver hosting solution including Shared hosting, Cloud hosting, Reseller hosting, Dedicated Servers, and IT as Service for companies of all size.