'string' does not contain a definition for 'Connection'

Question

I am trying to import and excel file into my sql database using Epplus library in MVC5 and Entity framework 6 in C#. I am getting this error. I know there is something not right the way I am getting my connection string.

I am getting the error on the await new bulkwriter line on 'Connection'

 public async Task<ActionResult> StructureAsync(FormCollection postedFile)
    {
        var usersList = new List<bomStructuredImportTgt>();
        if (Request != null)
        {
            HttpPostedFileBase file = Request.Files["postedFile"];
            if ((file != null) && (file.ContentLength > 0) && !string.IsNullOrEmpty(file.FileName))
            {
                string fileName = file.FileName;
                string fileContentType = file.ContentType;
                byte[] fileBytes = new byte[file.ContentLength];
                var data = file.InputStream.Read(fileBytes, 0, Convert.ToInt32(file.ContentLength));
                using (var package = new ExcelPackage(file.InputStream))
                {
                    var currentSheet = package.Workbook.Worksheets;
                    var workSheet = currentSheet.First();
                    var noOfCol = workSheet.Dimension.End.Column;
                    var noOfRow = workSheet.Dimension.End.Row;
                    for (int rowIterator = 2; rowIterator <= noOfRow; rowIterator++)
                    {
                        var user = new bomStructuredImportTgt();
                        user.ACTUAL_DATE = Convert.ToDateTime(workSheet.Cells[rowIterator, 1].Value);
                        user.DESCRIPTION = workSheet.Cells[rowIterator, 2].Value?.ToString();
                        user.LEVEL = Convert.ToInt32(workSheet.Cells[rowIterator, 3].Value);
                        user.PARENT_PARTNO = workSheet.Cells[rowIterator, 4].Value?.ToString();
                        user.PART_NO = workSheet.Cells[rowIterator, 5].Value?.ToString();
                        user.PART_NAME = workSheet.Cells[rowIterator, 6].Value?.ToString();
                        user.HNS = workSheet.Cells[rowIterator, 7].Value?.ToString();
                        user.DWGSZ = workSheet.Cells[rowIterator, 8].Value?.ToString();
                        user.PART = workSheet.Cells[rowIterator, 9].Value?.ToString();
                        user.L1QTY = Convert.ToInt32(workSheet.Cells[rowIterator, 10].Value);
                        user.COLORM = workSheet.Cells[rowIterator, 11].Value?.ToString();
                        user.ATTCD = workSheet.Cells[rowIterator, 12].Value?.ToString();
                        user.KD = workSheet.Cells[rowIterator, 13].Value?.ToString();
                        user.SELL = workSheet.Cells[rowIterator, 14].Value?.ToString();
                        user.PL_GROUP = workSheet.Cells[rowIterator, 15].Value?.ToString();
                        user.PL1 = workSheet.Cells[rowIterator, 16].Value?.ToString();
                        user.AT1 = workSheet.Cells[rowIterator, 17].Value?.ToString();
                        user.PL2 = workSheet.Cells[rowIterator, 18].Value?.ToString();
                        user.AT2 = workSheet.Cells[rowIterator, 19].Value?.ToString();
                        user.PL3 = workSheet.Cells[rowIterator, 20].Value?.ToString();
                        user.PLANT = workSheet.Cells[rowIterator, 21].Value?.ToString();
                        user.SHRPCMINMAX = workSheet.Cells[rowIterator, 22].Value?.ToString();
                        usersList.Add(user);
                    }
                }
            }
        }

        using (SqlConnection excelImportDBEntities = new SqlConnection("Dev_Purchasing_New_ModelEntities"))
        {

            await new BulkWriter().InsertAsync(usersList, "bomStructuredImportTgt", excelImportDBEntities.Database.Connection, CancellationToken.None);
        }
        return View("Structure");
    }

  public class BulkWriter
    {
        private static readonly ConcurrentDictionary<Type, SqlBulkCopyColumnMapping[]> ColumnMapping =
            new ConcurrentDictionary<Type, SqlBulkCopyColumnMapping[]>();

        public async Task InsertAsync<T>(IEnumerable<T> items, string bomStructuredImportTgt, SqlConnection excelImportDBEntities,
            CancellationToken cancellationToken)
        {
            using (var bulk = new SqlBulkCopy(excelImportDBEntities))
            using (var reader = ObjectReader.Create(items))
            {
                bulk.DestinationTableName = bomStructuredImportTgt;
                foreach (var colMap in GetColumnMappings<T>())
                    bulk.ColumnMappings.Add(colMap);
                await bulk.WriteToServerAsync(reader, cancellationToken);
            }
        }

        private static IEnumerable<SqlBulkCopyColumnMapping> GetColumnMappings<T>() =>
            ColumnMapping.GetOrAdd(typeof(T),
                type =>
                    type.GetProperties()
                        .Select(p => new SqlBulkCopyColumnMapping(p.Name, p.Name)).ToArray());
    }

I have a class BulkWriter which is used to bulk copy the records in the sql. I wanted to use this class for my code to be time efficient.

Answer 1

As your excelImportDBEntities variable is of type SqlConnection, it has Database property. That property returns a string - the name of the database this connection connects to. Of course, you can't get .Connection from the database name and you get compilation error when you try to.

Then, how to fix it. Your BulkWriter's InsertAsync method expects SqlConnection object here and you already have this object, excelImportDBEntities. Why not using it directly? Replace

await new BulkWriter().InsertAsync(usersList, "bomStructuredImportTgt", excelImportDBEntities.Database.Connection, CancellationToken.None);

with

await new BulkWriter().InsertAsync(usersList, "bomStructuredImportTgt", excelImportDBEntities, CancellationToken.None);

and this error should be gone.

Another issue is that, as mentioned in other answers and comments, you seem to mishandle your connection string, which will produce an error after you compile and run your code.