how to do validation in sql bulk copy upload

asp.net asp.net-mvc-4 sqlbulkcopy

Question

I just created MVC4 application to upload data using excel file to store in the database table called tbl_hei_student.

    [HttpPost]
    [AcceptVerbs(HttpVerbs.Post)]
    public ActionResult Student(HttpPostedFileBase FileUpload1)
    {           
        try
        {
            string conString = string.Empty;
            //Upload and save the file    

           if (Request.Files["FileUpload1"].ContentLength > 1)
            {
                try
                {
                    string excelPath = Path.Combine(HttpContext.Server.MapPath("~/Content/"), Path.GetFileName(FileUpload1.FileName));
                    FileUpload1.SaveAs(excelPath);

                    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;
                        default:
                            this.SetNotification("The file type submitted is invalid", NotificationEnumeration.Error);
                             return RedirectToAction("Student", "Excel");

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

                        DataTable dtExcelData = new DataTable();

                        string query =    "SELECT " +
                                          "s1.HEC_ID, " +
                                          "s1.Student_Personal_ID, " +                                         
                                          "s1.Date_of_Birth "  ;

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

                        //if (dtExcelData.DefaultView.Count < 0)
                        //    {
                        //        throw new Exception("Your data sheet is empty. PLeaswe upload another.");
                        //    }

                        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 |
                                                        SqlBulkCopyOptions.KeepIdentity,
                                                        null))
                            {
                                //Set the database table name
                                sqlBulkCopy.DestinationTableName = "tbl_HEI_student";

                                sqlBulkCopy.BulkCopyTimeout = 0;



                                sqlBulkCopy.ColumnMappings.Add("HEC_ID", "HEC_ID");
                                sqlBulkCopy.ColumnMappings.Add("Student_Personal_ID", "Student_Personal_ID");
                                sqlBulkCopy.ColumnMappings.Add("Date_of_Birth", "Date_of_Birth");


                                con.Open();    


                                finally
                                {
                                    con.Close();
                                }
                            }
                        }
                    }
                }    
            }    
        }
    }

in my table I have following fields to capture the data that upload using excel file

Column Name            | Data Type

HEC_ID                 | nvarchar(100)

Student_Personal_ID    | nvarchar(100)     

Date_of_Birth          | datetime

In my excel I have hundreds of rows ,

I would like to terminate the upload action, if one data field is in the wrong format for the Date_of_Birth field

Popular Answer

Before inserting it into database, fetch it into a variable and check

DateTime date = DateTime.ParseExact(inputString, formatString, System.Globalization.CultureInfo.InvariantCulture) 


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