Mapping and Importing Excel Files to a Database using SqlBulkCopy

public void importExcelData(ListItemCollection dbColumns, ListItemCollection excelColumns,
        String tableName)
    {
 
        //set up and make connection
        //connection string specified in the web.config
        ConnectionStringSettings connString = 
              ConfigurationManager.ConnectionStrings["Conn"];
        SqlConnection conn = new SqlConnection(connString.ConnectionString);
        conn.Open();
        using (SqlBulkCopy s = new SqlBulkCopy(conn))
        {
            try
            {
                s.DestinationTableName = tableName;
                s.NotifyAfter = 10000;
                for (int i = 0; i < dbColumns.Count; i++)
                {
                    s.ColumnMappings.Add(excelColumns[i].Value.ToString(),
                       dbColumns[i].Value.ToString());
                }
                s.WriteToServer(data);
                s.Close();
            }
            catch(Exception ex)
            {
                //Error handling
            }
        }
        conn.Close();
    }

Ref: http://blog.shuasolutions.com/2008/10/mapping-and-importing-excel-files-to-a-database-using-sqlbulkcopy/

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

MVC application life cycle

Any web application has two main execution steps first understanding the request and depending on the type of the request sending out appropriate response. MVC application life cycle is not different it has two main phases first creating the request object and second sending our response to the browser.

Creating Response object: - The request object creation has four major steps. Below is the detail explanation of the same.

Step 1 Fill route: - MVC requests are mapped to route tables which in turn specify which controller and action to be invoked. So if the request is the first request the first thing is to fill the route table with routes collection. This filling of route table happens in the global.asax file.

Step 2 Fetch route:- Depending on the URL sent "UrlRoutingModule" searches the route table to create "RouteData" object which has the details of which controller and action to invoke.

Step 3 Request context created: - The "RouteData" object is used to create the "RequestContext" object.

Step 4 Controller instance created: - This request object is sent to "MvcHandler" instance to create the controller class instance. Once the controller class object is created it calls the "Execute" method of the controller class.

Creating Response object: - This phase has two steps executing the action and finally sending the response as a result to the view.

Step 5 Execute Action: - The "ControllerActionInvoker" determines which action to executed and executes the action.

Step 6 Result sent: - The action method executes and creates the type of result which can be a view result , file result , JSON result etc.

So in all there are six broad steps which get executed in MVC application life cycle.

Regular expression

http://rubular.com/