Download All type of File in Web

protected void btnDownLoad_Click(object sender, EventArgs e)
        {
            string FilePath = txtFileLogPath.Text.Trim();
            string fileName = Path.GetFileName(FilePath);
            string fileExtension = Path.GetExtension(FilePath);
            if (!string.IsNullOrEmpty(fileExtension))
            {
                System.IO.FileStream fs = new System.IO.FileStream(FilePath, System.IO.FileMode.Open, System.IO.FileAccess.Read);
                byte[] bt = new byte[fs.Length];
                fs.Read(bt, 0, (int)fs.Length);
                fs.Close();
                Response.ContentType = "application/x-unknown/octet-stream";
                Response.AppendHeader("Content-Disposition", "attachment; filename=\"" + fileName);
                try
                {
                    if (bt != null)
                    {
                        System.IO.MemoryStream stream1 = new System.IO.MemoryStream(bt, true);
                        stream1.Write(bt, 0, bt.Length);
                        Response.BinaryWrite(bt);
                        Response.Flush();
                    }
                }
                catch (Exception ex)
                {
                    Response.Write(ex.Message);
                    txtFileLogPath.Text = "";
                    Display(this.GetType(), "Error", "Error Occured");
                }
                finally
                {
                    Response.End();
                    txtFileLogPath.Text = "";
                }
            }
            else
            {
                Display(this.GetType(), "Warning", "Please enter correct path with extension");
                txtFileLogPath.Text = "";
            }
        }
        private void Display(Type t, string errorCaption, string errorMessage)
        {
            string scriptError = "<script>alert('" + errorMessage + "');</script>";
            Page.ClientScript.RegisterStartupScript(this.GetType(), errorCaption, scriptError);
        }

Why/Can we use 'virtual' for properties in classes?

public class Employee
{
   public int EmpID { get; set; }
   public string EmpName{ get; set; }  
   public string Address { get; set; }
   public virtual ICollection<Dept> dept{ get; set; }
}

public class Dept
{
   public int DeptID{ get; set; }
   public string DeptName{ get; set; }
   public string Email { get; set; }
   public virtual Division div { get; set; }
}
--------------------------------------------------------------------------------------

The virtual keyword has an effect when used on properties in EF Code First.

• Lazy Loading: Any virtual ICollections will be lazy-loaded unless you specifically mark them otherwise.
• More efficient change tracking. If you meet all the following requirements then your change tracking can use a more efficient method by hooking your virtual properties. From the link:

To get change tracking proxies, the basic rule is that your class must be public, non-abstract or non-sealed. Your class must also implement public virtual getters/setters for all properties that are persisted. Finally, you must declare collection based relationship navigation properties as ICollection<T> only. They cannot be a concrete implementation or another interface that derives from ICollection<T> (a difference from the Deferred Loading proxy)

Download multiple excel sheet using NPOI.dll

Download latest NPOI.dll and add into your project reference.


using NPOI.HSSF.UserModel;

 public partial class WebForm1 : System.Web.UI.Page
    {
        string Connstring = ConfigurationManager.ConnectionStrings["Test"].ConnectionString;
        protected void Page_Load(object sender, EventArgs e)
        {
            GetData();
        }

        private void GetData()
        {
            DataSet ds = new DataSet();
            string[] tableNames;
            using (SqlConnection con = new SqlConnection(Connstring))
            {
                using (SqlCommand cmd = new SqlCommand())
                {
                    cmd.Connection = con;
                    cmd.CommandType = CommandType.StoredProcedure;
                    cmd.CommandText = "ExcelDownload";

                    SqlParameter param = new SqlParameter("@tableNames", SqlDbType.VarChar,50);
                    param.Direction = ParameterDirection.Output;
                    param.Value = 50;
                    cmd.Parameters.Add(param);


                    using (SqlDataAdapter da = new SqlDataAdapter(cmd))
                    {
                        da.Fill(ds);
                        string strTableNames = Convert.ToString(cmd.Parameters["@tableNames"].Value);
                        tableNames = strTableNames.Split(',');
                    }

                }
            }

            writeDataToExcelFile(ds, tableNames);
        }
        private void writeDataToExcelFile(DataSet ds, string[] tableNames)
        {
            for (int i = 0; i < tableNames.Length; i++)
            {
                ds.Tables[i].TableName = tableNames[i];
            }
            var workbook = new HSSFWorkbook();
            string filename = "MultiplesheetDownloadDemo";
            for (int i = 0; i < ds.Tables.Count; i++)
            {
                DataTable dt = ds.Tables[i];
                if (dt.Rows.Count > 0)
                {
                    var Sheet = workbook.CreateSheet(ds.Tables[i].TableName);

                    int cols = 0;
                    var myRow = Sheet.CreateRow(0);
                    var style1 = workbook.CreateCellStyle();

                    // cell background
                    style1.FillBackgroundColor = NPOI.HSSF.Util.HSSFColor.Violet.Index;

                    // font color
                    var font1 = workbook.CreateFont();
                    font1.Color = NPOI.HSSF.Util.HSSFColor.Blue.Index;
                    style1.SetFont(font1);


                    foreach (DataColumn column in dt.Columns)
                    {
                        var Cell = myRow.CreateCell(cols);
                        Cell.SetCellValue(column.ColumnName);
                        Cell.CellStyle = style1;
                        cols++;
                    }


                    for (int rowNum = 0; rowNum < dt.Rows.Count; rowNum++)
                    {
                        myRow = Sheet.CreateRow(rowNum + 1);
                        for (int cellNum = 0; cellNum < dt.Columns.Count; cellNum++)
                        {
                            var Cell = myRow.CreateCell(cellNum);
                            Cell.SetCellValue(Convert.ToString(dt.Rows[rowNum][cellNum]));
                        }

                    }
                }
            }
            using (var buffer = new MemoryStream())
            {
                workbook.Write(buffer);

                Response.ContentType = "application/vnd.ms-excel";
                Response.AddHeader("Content-Disposition", string.Format("attachment;filename={0}", filename + ".xls"));
                Response.Clear();
                Response.BinaryWrite(buffer.GetBuffer());
                Response.End();
            }


        }
    }

Stored Procedure:

ALTER PROC ExcelDownload
(
@tableNames VARCHAR(500) OUTPUT
)
AS
BEGIN
SET @tableNames = 'tblTable1,tblTable2'
SELECT * FROM dbo.tblTable1
SELECT * FROM dbo.tblTable2
END

Insert Stored Procedure Results Into Table

CREATE PROCEDURE GetList
AS
BEGIN
SELECT
ListName = 'MyList'
,ListNumber = 1
END
GO

-- this table will house our results
CREATE TABLE #List
(
ListName VARCHAR(25),
ListNumber INT
)

-- finally, execute and insert into our table
INSERT INTO #List
(
ListName,
ListNumber
)
EXEC dbo.GetList

select * from #List