c# - OleDbDataReader cmd.ExecuteReader() “枚举没有结果”

Question

我试图从excel文件中获取数据并将其设置在数据库中

我的代码看起来像这个答案

            var path = GetPath(activityId);
            path = Path.Combine(path, fileName);

            var strConnection = GetConnectionString();

            var excelConnString = String.Format("Provider=Microsoft.ACE.OLEDB.12.0;Data Source={0};Extended Properties=\"Excel 12.0\"", path);

            using (OleDbConnection excelConnection = new OleDbConnection(excelConnString))
            {
                excelConnection.Open();
                DataTable dtSchema = excelConnection.GetOleDbSchemaTable(OleDbSchemaGuid.Tables, new object[] { null, null, null, "TABLE" });
                string firstSheetName = dtSchema.Rows[0]["TABLE_NAME"].ToString();

                using (OleDbCommand cmd = new OleDbCommand("Select * from [" + firstSheetName + "]" , excelConnection))
                {
                    using (OleDbDataReader dReader = cmd.ExecuteReader())
                    {
                        using (SqlBulkCopy sqlBulk = new SqlBulkCopy(strConnection))
                        {
                            sqlBulk.DestinationTableName = tableName;

                            while (dReader.Read())
                            {
                                sqlBulk.WriteToServer(dReader);
                            }
                        }
                    }
                }

但是当我调试时，我sqlBulk.WriteToServer(dReader);也显示了这个错误，数据也没有加载到表中：

Empty = "枚举没有结果"

我尝试了很多答案但没有成功

注意：我上传的 excel 字段和我的表格看起来一样

标签： c#sqlasp.net-mvcoledbdatareader

Answer 1

我通过如下更改我的代码（OleDbDataAdapter改为使用OleDbDataReader）解决了这个问题：

   DataTable Contents = new DataTable();
   using (OleDbDataAdapter cmd = new OleDbDataAdapter("Select * from [" + firstSheetName + "]", excelConnection))
   {
     cmd.Fill(Contents);
     using (SqlBulkCopy sqlBulk = new SqlBulkCopy(strConnection))
     {
       sqlBulk.DestinationTableName = tableName;
       sqlBulk.WriteToServer(Contents);
      }
   }