SqlBulkCopy未从Excel中导入数据

本文关键字：导入 数据 Excel 未从 SqlBulkCopy | 更新日期: 2023-09-27 18:14:57

我正在做一个简单的项目，用Visual Studio 12和c#从Excel导出数据到SQL Server。我设法从Excel导入到数据集，但没有将它们插入到我的数据库中，尽管代码显示了一个积极的消息框，我已经设置为告诉我什么时候可以。

这是说，我的数据已成功导出到SQL，但当我选择"显示表数据"没有数据显示;桌子是空的。这是我的代码:

using System;
using System.Collections.Generic;
using System.ComponentModel;
using System.Data;
using System.Drawing;
using System.Linq;
using System.Text;
using System.Threading.Tasks;
using System.Windows.Forms;
using Excel = Microsoft.Office.Interop.Excel;
using System.Reflection;
using System.IO;
using System.Data.OleDb;
using System.Data.Common;
using System.Data.SqlClient;
namespace testoledb    
{
   public partial class Form1 : Form
   {
      DataSet OleDs = new DataSet();
      OleDbDataAdapter OleAdapter = new OleDbDataAdapter();
      public Form1()
      {
         InitializeComponent();
      }
      private void Form1_Load(object sender, EventArgs e)
      {    
      }
      private void openFileDialog1_FileOk(object sender, CancelEventArgs e)
      {
      }
      private void upload_excl_Click(object sender, EventArgs e)
      {
         string path = Path.GetDirectoryName(Assembly.GetExecutingAssembly().Location);
         path = Path.Combine(path, "AGENDA.xlsx");
         string path2 = Path.GetDirectoryName(Assembly.GetExecutingAssembly().Location);
         path2 = Path.Combine(path2, "Database1.mdf");
         string OleConnectionString = @"Provider=Microsoft.ACE.OLEDB.12.0;Data Source="+path+@";Extended Properties='Excel 12.0 Macro;MDR=Yes;ImportMixedTypes=Text;TypeGuessRows=0'"; //,HDR=Yes;IMEX=1""";
         OleDbConnection OleConn = new OleDbConnection(OleConnectionString);    
         string OleStrCmd = "select * from [Feuil1$A1:I330]";    
         OleDbCommand OleCmd = new OleDbCommand(OleStrCmd, OleConn);
         try
         {
            OleConn.Open();
            OleDs.Clear();
            OleAdapter.SelectCommand = OleCmd;
            OleAdapter.Fill(OleDs);
            dataGridView1.DataSource = OleDs.Tables[0];
            //***************************************charger la base*******************************************************************************
            using (DbDataReader dr = OleCmd.ExecuteReader())
            {
               // SQL Server Connection String
               string sqlConnectionString = @"Data Source=(LocalDB)'v11.0;AttachDbFilename=" + path2 + ";Integrated Security=True";
               // Bulk Copy to SQL Server
               using (SqlBulkCopy bulkCopy = new SqlBulkCopy(sqlConnectionString))
               {
                  bulkCopy.DestinationTableName = "[dbo].[Table]";
                  bulkCopy.WriteToServer(dr);
                  MessageBox.Show("Data Exoprted To Sql Server Succefully");
               }
            }
                //***********************************************************************************************
         }
         catch (Exception ex)
         {
            MessageBox.Show(ex.ToString());    
         }
         finally
         {
            OleConn.Close();
         }
      }
   }
}

SqlBulkCopy未从Excel中导入数据

要判断是否真的在导入数据，您可以这样做:

OleDBCommand commandRowCount = new SqlCommand(
            "SELECT COUNT(*) FROM " +
            "dbo.Table;",
            OleConn);
long countStart = System.Convert.ToInt32(
            commandRowCount.ExecuteScalar());

这将给你开始的行数#，可能是0。然后导入数据后，如下:

// Perform a final count on the destination  
// table to see how many rows were added. 
long countEnd = System.Convert.ToInt32(
            commandRowCount.ExecuteScalar());

这将给你行结束的#。然后通过计算countEnd - countStart可以知道导入了多少行。检查OleDs.Tables[0].Rows.Count的值也可能很有帮助，以确保DataSet中有行。

关于SqlBulkCopy.WriteToServer的更多信息:http://msdn.microsoft.com/en-us/library/434atets(v=vs.110).aspx

编辑:

我回头看你的原始代码，看起来你不需要使用DbDataReader。由于您已经将数据加载到DataSet中，因此有一个版本的SqlBulkCopy.WriteToServer将DataTable作为参数，因此您可以尝试以下操作:

       // SQL Server Connection String
       string sqlConnectionString = @"Data Source=(LocalDB)'v11.0;AttachDbFilename=" + path2 + ";Integrated Security=True";
       // Bulk Copy to SQL Server
       using (SqlBulkCopy bulkCopy = new SqlBulkCopy(sqlConnectionString))
       {
          bulkCopy.DestinationTableName = "[dbo].[Table]";
          bulkCopy.WriteToServer(OleDs.Tables[0]);
          MessageBox.Show("Data Exoprted To Sql Server Succefully");
       }

注意，我没有将外部using块与DbDataReader块一起包含。试一试，看看是否适合你。

相关文章：