Hi there

How to set a column value random number when importing from excel files into a database in ASP.Net MVC

I want to assign a column called IDP_Key to a random number generator so that when I import excel data into the database, each record gets a unique number but other column values are inserted into a database directly. Here is the controller code that I have tried so far.

Thank you for your support.

[HttpPost]
public ActionResult ImportFromExcel(HttpPostedFileBase postedFile)
{
    if (ModelState.IsValid)
    {
        if (postedFile != null && postedFile.ContentLength > (1024 * 1024 * 50))  // 50MB limit 
        {
            ModelState.AddModelError("postedFile", "Your file is to large. Maximum size allowed is 50MB !");
        }
 
        else
        {
            string filePath = string.Empty;
            string path = Server.MapPath("~/Content/");
            if (!Directory.Exists(path))
            {
                Directory.CreateDirectory(path);
            }
 
            filePath = path + Path.GetFileName(postedFile.FileName);
            string extension = Path.GetExtension(postedFile.FileName);
            postedFile.SaveAs(filePath);
 
            string conString = string.Empty;
            switch (extension)
            {
                case ".xls": //For Excel 97-03. 
                    conString = ConfigurationManager.ConnectionStrings["Excel03ConString"].ConnectionString;
                    break;
                case ".xlsx": //For Excel 07 and above. 
                    conString = ConfigurationManager.ConnectionStrings["Excel07ConString"].ConnectionString;
                    break;
            }
 
            try
            {
                DataTable dt = new DataTable();
                conString = string.Format(conString, filePath);
 
                using (OleDbConnection connExcel = new OleDbConnection(conString))
                {
                    using (OleDbCommand cmdExcel = new OleDbCommand())
                    {
                        using (OleDbDataAdapter odaExcel = new OleDbDataAdapter())
                        {
                            cmdExcel.Connection = connExcel;
 
                            //Get the name of First Sheet. 
                            connExcel.Open();
                            DataTable dtExcelSchema;
                            dtExcelSchema = connExcel.GetOleDbSchemaTable(OleDbSchemaGuid.Tables, null);
                            string sheetName = dtExcelSchema.Rows[0]["TABLE_NAME"].ToString();
                            connExcel.Close();
 
                            //Read Data from First Sheet. 
                            connExcel.Open();
                            cmdExcel.CommandText = "SELECT * From [" + sheetName + "]";
                            odaExcel.SelectCommand = cmdExcel;
                            odaExcel.Fill(dt);
                            connExcel.Close();
                        }
                    }
                }
 
                conString = ConfigurationManager.ConnectionStrings["DBCS"].ConnectionString;
                using (SqlConnection con = new SqlConnection(conString))
                {
                    using (SqlBulkCopy sqlBulkCopy = new SqlBulkCopy(con))
                    {
                        
                        ////Set the database table name. 
                        sqlBulkCopy.DestinationTableName = "FamilyHeads";
 
                        sqlBulkCopy.ColumnMappings.Add("Age", "Age");
                        sqlBulkCopy.ColumnMappings.Add("Sex", "Sex");
                        sqlBulkCopy.ColumnMappings.Add("RegistrationDate", "RegistrationDate");
                        sqlBulkCopy.ColumnMappings.Add("RegionID", "RegionID");
                        sqlBulkCopy.ColumnMappings.Add("ZoneID", "ZoneID");
                        sqlBulkCopy.ColumnMappings.Add("WoredaID", "WoredaID");
                        sqlBulkCopy.ColumnMappings.Add("KebeleID", "KebeleID");
                        sqlBulkCopy.ColumnMappings.Add("IDP_Reg_No", GetUniqueKey(5));
                        sqlBulkCopy.ColumnMappings.Add("First_Name", "First_Name");
                        sqlBulkCopy.ColumnMappings.Add("Middle_Name", "Middle_Name");
                        sqlBulkCopy.ColumnMappings.Add("Last_Name", "Last_Name");
                        sqlBulkCopy.ColumnMappings.Add("ProjectID", "ProjectID");
                        sqlBulkCopy.ColumnMappings.Add("PG", "PG");
                        sqlBulkCopy.ColumnMappings.Add("LC", "LC");
                        sqlBulkCopy.ColumnMappings.Add("SC", "SC");
                        sqlBulkCopy.ColumnMappings.Add("SM", "SM");
                        sqlBulkCopy.ColumnMappings.Add("PROT", "PROT");
                        sqlBulkCopy.ColumnMappings.Add("Remark", "Remark");
                        sqlBulkCopy.ColumnMappings.Add("FS", "FS");
                        sqlBulkCopy.ColumnMappings.Add("UserName", "UserName");
                        sqlBulkCopy.ColumnMappings.Add("DistributionID", "DistributionID");
                        sqlBulkCopy.ColumnMappings.Add("DistributionInfo_ID", "Distributioninfo_ID");
                        sqlBulkCopy.ColumnMappings.Add("SiteID", "SiteID");
                        con.Open();
                        sqlBulkCopy.WriteToServer(dt);
                        con.Close();
                        return Json("File uploaded successfully");
                    }
                }
            } 
            catch (Exception e)
            {
                return Json("error" + e.Message);
            }
        }
    }
    return Json("no files were selected !");
}