CODEWITHSUNDEEP
sql-serverexcel
Lamin
Lamin

Reputation: 109

Insert excel records into MS SQL database

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);
}

Upvotes: 0

Views: 100

Answers (1)

Shaikh Farooque
Shaikh Farooque

Reputation: 2630

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);
                    }

Upvotes: 1

Related Questions