set a constant(same) time stamp for multiple data base rows

asp.net asp.net-mvc-4 excel sqlbulkcopy sql-server

Question

I'm creating an asp.net mvc4 application, that can write data into database tables from excel sheets(using sqlbulkcopy column mapping). when we select excel sheet and submit, it is writing data into database.

Now I want to set file uploaded time as timestamp for each data rows when it write data into database table.

Code:

namespace AFFEMS2_HEC.Controllers
{
    public class ExcelController : Controller
    {
        //
        // GET: /Excel/

        public ActionResult Index()
        {
            return View();
        }


        [HttpPost]
        [AcceptVerbs(HttpVerbs.Post)]
        public ActionResult Index(HttpPostedFileBase FileUpload1)
        {
            //Upload and save the file
            if (Request.Files["FileUpload1"].ContentLength > 0)
            {
                string excelPath = Path.Combine(HttpContext.Server.MapPath("~/Content/"), Path.GetFileName(FileUpload1.FileName));

                FileUpload1.SaveAs(excelPath);

                string conString = string.Empty;
                string extension = Path.GetExtension(FileUpload1.FileName);
                switch (extension)
                {
                    case ".xls": //Excel 97-03
                        conString = ConfigurationManager.ConnectionStrings["Excel03ConString"].ConnectionString;
                        break;
                    case ".xlsx": //Excel 07 or higher
                        conString = ConfigurationManager.ConnectionStrings["Excel07+ConString"].ConnectionString;
                        break;

                }
                conString = string.Format(conString, excelPath);
                using (OleDbConnection excel_con = new OleDbConnection(conString))
                {

                    string sheet1 = excel_con.GetOleDbSchemaTable(OleDbSchemaGuid.Tables, null).Rows[0]["TABLE_NAME"].ToString();
                    string sheet2 = excel_con.GetOleDbSchemaTable(OleDbSchemaGuid.Tables, null).Rows[1]["TABLE_NAME"].ToString();

                    DataTable dtExcelData = new DataTable();


                    //[OPTIONAL]: It is recommended as otherwise the data will be considered as String by default.

                    dtExcelData.Columns.AddRange(new DataColumn[4] { 
                    new DataColumn("Id", typeof(string)),
                    new DataColumn("Name", typeof(string)),
                    new DataColumn("Email",typeof(string)),
                    new DataColumn("Mobile", typeof(int)) });


                    string query = "SELECT s1.Id, " + 
                                        "s1.Name, " +
                                        "s1.Mobile, " + 
                                        "s2.Email " + 
                                        "FROM ([" + sheet1 + "] as s1 INNER JOIN [" + sheet2 + "] as s2 ON " + "s1.Id = s2.Id)";

                    using (OleDbDataAdapter oda = new OleDbDataAdapter(query, excel_con))
                    {
                        oda.Fill(dtExcelData);
                    }
                    excel_con.Close();

                    string consString = ConfigurationManager.ConnectionStrings["constr"].ConnectionString;
                    using (SqlConnection con = new SqlConnection(consString))
                    {
                        using (SqlBulkCopy sqlBulkCopy = new SqlBulkCopy(con,
                                                    SqlBulkCopyOptions.CheckConstraints|
                                                    SqlBulkCopyOptions.FireTriggers|
                                                    SqlBulkCopyOptions.KeepNulls|
                                                    SqlBulkCopyOptions.TableLock|
                                                    SqlBulkCopyOptions.UseInternalTransaction,
                                                    null))
                        {
                            //Set the database table name
                            sqlBulkCopy.DestinationTableName = "User1";

                            sqlBulkCopy.ColumnMappings.Add("Id", "Id");
                            sqlBulkCopy.ColumnMappings.Add("Name", "Name");
                            sqlBulkCopy.ColumnMappings.Add("Email", "Email");
                            sqlBulkCopy.ColumnMappings.Add("Mobile", "Mobile");

                            con.Open();

                            try
                            {
                                // Write from the source to the destination
                                sqlBulkCopy.WriteToServer(dtExcelData);
                            }
                            catch (Exception ex)
                            {
                                Console.WriteLine(ex.Message);
                            }
                            finally
                            {
                                con.Close();
                            }
                        }
                    }
                }

            }
            return View();
        }
    }
}

Accepted Answer

Just create another column as "TimeStamp_Inserted" but it type should be datetime or nvarchar and try to replace following code snippet

             string query = "SELECT s1.Id, " + 
                                    "s1.Name, " +
                                    "s1.Mobile, " + 
                                    "s2.Email " + 
                                    "'"+DateTime.Now.ToString() +"'"+" as TimeStamp_Inserted"+
                                    "FROM ([" + sheet1 + "] as s1 INNER JOIN [" + sheet2 + "] as s2 ON " + "s1.Id = s2.Id)";

sqlbulkcopy column mapping try to change like this

                        sqlBulkCopy.DestinationTableName = "User1";

                        sqlBulkCopy.ColumnMappings.Add("Id", "Id");
                        sqlBulkCopy.ColumnMappings.Add("Name", "Name");
                        sqlBulkCopy.ColumnMappings.Add("Email", "Email");
                        sqlBulkCopy.ColumnMappings.Add("Mobile", "Mobile");

                        sqlBulkCopy.ColumnMappings.Add("TimeStamp_Inserted", "TimeStamp");

                        con.Open();

just try it



Licensed under: CC-BY-SA with attribution
Not affiliated with Stack Overflow
Is this KB legal? Yes, learn why
Licensed under: CC-BY-SA with attribution
Not affiliated with Stack Overflow
Is this KB legal? Yes, learn why