Insert excel records into MS SQL database

Question

I tried to insert excel data to database, MS SQL.

Currently I looped the excel records and insert. It took too long.

Is there any way to insert excel records to database once ?

Thanks and Regards,

Here is my code:

User user = new User();
cmd_obj = new OleDbCommand("SELECT * FROM [Sheet1$]", con_obj);
OleDbDataReader dr = cmd_obj.ExecuteReader();

while (dr.Read())
{
   int blnBadSyntax = 0;
   int blnBadDomain = 0;
   int blnBadSMTP = 0;
   int blnGreylisted = 0;
   int blnBadMailbox = 0;
   bool blnIsValid = false;

   string key = "2CH3W-7ENLC-FWLZ4-WEUVY-JRQ11-AU69U-W63V5-ULF1C-DA5RC-RU7XS-XK6JY-6JT5U-MYLX";
   MXValidate.LoadLicenseKey(key);
   MXValidate mx = new MXValidate();
   mx.LogInMemory = true;
   mx.CheckLiteralDomain = true;
   mx.CheckGreylisting = true;
   try
   {
      MXValidateLevel level = mx.Validate(user.StrEmailId, MXValidateLevel.Mailbox);
      switch (level)
      {
         case MXValidateLevel.NotValid:
         blnBadSyntax = 1;
         break;

         case MXValidateLevel.Syntax:
         blnBadDomain = 1;
         break;

         case MXValidateLevel.MXRecords:
         blnBadSMTP = 1;
         break;

         case MXValidateLevel.SMTP:
         blnGreylisted = 1;
         blnIsValid = true;
         break;

         case MXValidateLevel.Greylisted:
         blnBadMailbox = 1;
         blnIsValid = true;
         break;

         case MXValidateLevel.Mailbox:
         blnIsValid = true;
         break;
     }

     user.BlnBadSyntax = blnBadSyntax;
     user.BlnBadDomain = blnBadDomain;
     user.BlnBadSMTP = blnBadSMTP;
     user.BlnGraylisted = blnGreylisted;
     user.BlnBadMailBox = blnBadMailbox;
     if (blnIsValid)
     {
        user.StrStatus = "Valid";
     }
     else
     {
        user.StrStatus = "InValid";
        logFile.writeLog(mx.GetLog());
     }
   }
   catch (DnsException ex)
   {
      logFile.writeLog(mx.GetLog());
   }
InsertuserDetails(user);
}

Shaikh Farooque · Accepted Answer · 2014-07-16 08:44:53Z

1

You can do this with the help of SqlBulkCopy if the data is large.

Kindly check the following post for more details:

http://technico.qnownow.com/bulk-copy-data-from-excel-to-destination-db-using-sql-bulk-copy/

// Connection String to Excel Workbook
            string excelConnectionString = @"Provider=Microsoft.Jet.OLEDB.4.0;Data Source=Book1.xls;ExtendedProperties=""Excel 8.0;HDR=YES;""";

            // Create Connection to Excel Workbook
            using (OleDbConnection connection = new OleDbConnection(excelConnectionString))
            {
                OleDbCommand command = new OleDbCommand("Select ID,Data FROM [Data$]", connection);

                connection.Open();

                // Create DbDataReader to Data Worksheet
                using (DbDataReader dr = command.ExecuteReader())
                {
                    // SQL Server Connection String
                    string sqlConnectionString = "Data Source=.;Initial Catalog=Test;Integrated Security=True";

                    // Bulk Copy to SQL Server
                    using (SqlBulkCopy bulkCopy = new SqlBulkCopy(sqlConnectionString))
                    {
                        bulkCopy.DestinationTableName = "ExcelData";
                        bulkCopy.WriteToServer(dr);
                    }

answered Jul 16, 2014 at 8:44

Shaikh Farooque

2,6402 gold badges22 silver badges36 bronze badges

Sign up to request clarification or add additional context in comments.

3 Comments

Lamin Over a year ago

@Shaikh, using SqlBulkCopy, can I add condition of excel data(whether email is valid or not)?

Jerry Over a year ago

Much better. And @Lamin sorry, I don't know about this module. I hope that Shaikh can answer that :) Also, I usually import excel data from MSSQL (the import wizard).

Shaikh Farooque Over a year ago

@Lamin, just visit the link, first it is retrieving data from excel to DataTable, and you can do all validation on DataTable data. Pass the final validated DataTable to CopyData() method.

Collectives™ on Stack Overflow

Insert excel records into MS SQL database

1 Answer 1

3 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

3 Comments

Your Answer

Sign up or log in

Post as a guest

Related