Question Read Excel Files and Save into Database

[raysefo]

Hi Guys,

I am reading inbox and downloading excel files from a specific e-mail address in a console application. I am saving those excel files by adding unique ids into the file names.

Here is the sample code:

static void Main(string[] args)
        {
            using var client = new ImapClient();
            client.Connect("imap.gmail.com", 993, true);

            client.Authenticate("test@gmail.com", "abc12345");

            var inbox = client.Inbox;
            inbox.Open(FolderAccess.ReadWrite);

            
            var query = SearchQuery.FromContains("test@gmail.com")
                .And(SearchQuery.SubjectContains("Test101")).And(SearchQuery.NotSeen);

            foreach (var uid in inbox.Search(query))
            {
                var message = inbox.GetMessage(uid);

                foreach (var attachment in message.Attachments)
                {
                    if (attachment is MessagePart)
                    {
                        var fileName = attachment.ContentDisposition?.FileName;
                        var rfc822 = (MessagePart)attachment;

                        if (string.IsNullOrEmpty(fileName))
                            fileName = "attached-message.eml";

                        using var stream = File.Create(fileName);
                        rfc822.Message.WriteTo(stream);
                    }
                    else
                    {
                        var part = (MimePart)attachment;
                        var fileName = part.FileName;
                        var file = Path.GetFileNameWithoutExtension(fileName);
                        var newPath = fileName.Replace(file, file + "-"+ uid);
                        Console.WriteLine(newPath);
                        Console.WriteLine(uid);
                        using var stream = File.Create(newPath);
                        part.Content.DecodeTo(stream);
                    }

                    inbox.AddFlags(uid, MessageFlags.Seen, true);
                    
                }

            }
            client.Disconnect(true);
        }

Now I need to read those excel files one by one and insert the unique id and data inside into a database via a .net core console application. I should read the unique id of the excel file and if it is NOT already saved into the database, I will insert it. Any suggestions on how to do it in the .net core console application?

Best Regards.

 

[raysefo]

This seems working, any suggestions for imprevement?

class Program
    {
        static async Task Main(string[] args)
        {
            try
            {
                string s = null;
                var d = new DirectoryInfo(@"C:\Test");
                var files = d.GetFiles("*.xlsx");
                var usersList = new List<User>();

                foreach (var file in files)
                {
                    var fileName = file.FullName;

                    using var package = new ExcelPackage(file);
                    ExcelPackage.LicenseContext = LicenseContext.NonCommercial;
                    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 User
                        {
                            GameCode = workSheet.Cells[rowIterator, 1].Value?.ToString(),
                            Count = Convert.ToInt32(workSheet.Cells[rowIterator, 2].Value),
                            Email = workSheet.Cells[rowIterator, 3].Value?.ToString(),
                            Status = Convert.ToInt32(workSheet.Cells[rowIterator, 4].Value)
                        };


                        usersList.Add(user);
                    }
                }

                var conn = ConfigurationManager.ConnectionStrings["Development"].ConnectionString;
                await using var connString = new SqlConnection(conn);
                connString.Open();
                await BulkWriter.InsertAsync(usersList, "[Test].[dbo].[Orders]", connString, CancellationToken.None);
            }
            catch (Exception e)
            {
                Console.WriteLine(e);
                throw;
            }
            
        }

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

            public static async Task InsertAsync<T>(IEnumerable<T> items, string tableName, SqlConnection connection,
                CancellationToken cancellationToken)
            {
                using var bulk = new SqlBulkCopy(connection);
                await using var reader = ObjectReader.Create(items);
                bulk.DestinationTableName = tableName;
                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());
        }
    }