SQLBulkCopy [英] SQLBulkCopy

问题描述

我正在尝试使用以下代码将一些数据从excel文件导入到SQL表中

Hi,

I am trying to import some data from an excel file to a SQL table using the below code

public void ImportData()
{
    // Connection String to Excel Workbook
    string excelConnectionString = @"Provider=Microsoft.Jet.OLEDB.4.0;Data Source=\\stagingserver\abc\UPLOAD\MyFile.xls;Extended Properties=""Excel 8.0;HDR=YES;IMEX=1""";
    OleDbConnection xlConn = new OleDbConnection(excelConnectionString);
    OleDbCommand xlCommand = new OleDbCommand("select col1, col2 from [Sheet1$]", xlConn);
    xlConn.Open();

    using(DbDataReader dr = xlCommand.ExecuteReader())
    {
        using(SqlBulkCopy copyData = new SqlBulkCopy(MYConnectionString))
        {
            copyData.DestinationTableName = "MY_TABLE";
            copyData.WriteToServer(dr);

            copyData.Close();
        }
        dr.Close();
        dr.Dispose();
    }
    xlCommand.Dispose();
    xlConn.Close();
    xlConn.Dispose();
}

.这在我的本地系统上运行良好.从服务器尝试相同操作时，不会插入任何行.也没有错误...任何想法将不胜感激.

. This works fine from my local system. When trying the same from the server no rows get inserted. There is no error also... Any thoughts will be appreciated.

推荐答案

"，xlConn); xlConn.Open(); 使用(DbDataReader dr = xlCommand.ExecuteReader()) { 使用(SqlBulkCopy copyData = 新 SqlBulkCopy(MYConnectionString)) { copyData.DestinationTableName = " ; copyData.WriteToServer(dr); copyData.Close(); } dr.Close(); Dr.Dispose(); } xlCommand.Dispose(); xlConn.Close(); xlConn.Dispose(); }

", xlConn); xlConn.Open(); using(DbDataReader dr = xlCommand.ExecuteReader()) { using(SqlBulkCopy copyData = new SqlBulkCopy(MYConnectionString)) { copyData.DestinationTableName = "MY_TABLE"; copyData.WriteToServer(dr); copyData.Close(); } dr.Close(); dr.Dispose(); } xlCommand.Dispose(); xlConn.Close(); xlConn.Dispose(); }

.这在我的本地系统上运行良好.从服务器尝试相同操作时，不会插入任何行.也没有错误...任何想法将不胜感激.

. This works fine from my local system. When trying the same from the server no rows get inserted. There is no error also... Any thoughts will be appreciated.

如果您使用DTS包，则可能要检查源excel文件的文件名.该文件可能在本地存在，但在服务器上不存在.

祝您好运！

If you use a DTS package you might want to check the filename of the source excel file. The file could exist locally, but just doesn''t on the server.

Good luck!

我同意E.F.检查导入文件和格式文件(如果使用)的路径.在大多数情况下，某些功能在本地而不是远程运行

A.路径
B.连接字符串.
C.权限.

您是说服务器上没有错误吗?我建议检查服务器日志.

您是通过命令行还是通过代码执行此操作?您是说代码中没有例外，还是没有通过命令行显示?

I agree with E.F. Check the paths of the import file and the format file if used. Most of the time something works locally but not remotely is

A. Paths
B. Connection strings.
C. Permissions.

Do you mean there is no error on the server? I would recommend checking the server log.

Are you doing this via command line or in code? Do you mean there are no exceptions in your code or displayed via command line?

这篇关于SQLBulkCopy的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！