SqlBulkCopy无法正常工作？ [英] SqlBulkCopy not working properly ?

问题描述

Excel工作表数据

Excel sheet data

  0        1       2       3       4       5
InTime  9:00 AM 9:00 AM 9:00 AM 9:00 AM 9:00 AM
OutTime 7:00 PM 7:00 PM 7:00 PM 7:00 PM 7:00 PM

using (OleDbDataReader dReader = cmd.ExecuteReader())
      {
       using (SqlBulkCopy sqlBulk = new SqlBulkCopy(strConnection))
          {
           //Give your Destination table name
           sqlBulk.DestinationTableName = "tablename";
           DataTable dt = new DataTable();
           dt.Load(dReader);
           sqlBulk.WriteToServer(dt);
           }
      }

在上面的代码数据表中显示excel表的所有数据但是当 sqlBulk.WriteToServer（dt）; 它不在数据库中写0列



表结构

0列varchar

1列日期时间

2列日期时间

3列日期时间

4列日期时间

5列日期时间

In above code datattable show all the data of excel sheet but when sqlBulk.WriteToServer(dt); it not write 0 column in database

Table structur
0 column varchar
1 column datetime
2 column datetime
3 column datetime
4 column datetime
5 column datetime

column0 column1 column2 column3 column4 column5
        9:00 AM 9:00 AM 9:00 AM 9:00 AM 9:00 AM
        7:00 PM 7:00 PM 7:00 PM 7:00 PM 7:00 PM

第0列显示空白值

column 0 show blank value

推荐答案

首先查看如何从Excel读取数据：检查SELECT命令，然后检查Column0数据是否包含在读取中并且它是否正确加载到DataTable中 - 您的问题可能在该区域（我从Ex读取数据没有问题） cel使用OleDbConnection并使用BulkCopy更新SQL）

Start by looking at how you read the data from Excel: Check the SELECT command, then check that the Column0 data is included in the read and that it is loaded correctly into the DataTable - the chances are that your problem is in that area (I have no problem reading data from Excel with an OleDbConnection and updating SQL using BulkCopy)

DECLARE @StrNtLogin VARCHAR(4000)
SET NOCOUNT ON
SET @StrNtLogin = 'Deposit'
SET @StrNtLogin = @StrNtLogin + ','
declare @SplitOn CHAR(1)
SET @SplitOn= ','
DECLARE @SearchStr nvarchar(100)
Declare @Cnt int
Set @Cnt = 1
IF OBJECT_ID('tempdb..#Results') IS NOT NULL
    DROP TABLE #Results
CREATE TABLE #Results (ColumnName nvarchar(370), ColumnValue nvarchar(3630))
WHILE  (CHARINDEX(@SplitOn,@StrNtLogin)>0)
BEGIN
SELECT   @SearchStr = ltrim(rtrim(Substring(@StrNtLogin,1,Charindex(@SplitOn,@StrNtLogin)-1)))

    DECLARE @TableName nvarchar(256), @ColumnName nvarchar(128), @SearchStr2 nvarchar(110)
    SET  @TableName = ''
    SET @SearchStr2 = QUOTENAME('%' + @SearchStr + '%','''')

    WHILE @TableName IS NOT NULL
    BEGIN
        SET @ColumnName = ''
        SET @TableName =
        (
            SELECT MIN(QUOTENAME(TABLE_SCHEMA) + '.' + QUOTENAME(TABLE_NAME))
            FROM    INFORMATION_SCHEMA.TABLES
            WHERE       TABLE_TYPE = 'BASE TABLE'
                AND QUOTENAME(TABLE_SCHEMA) + '.' + QUOTENAME(TABLE_NAME) > @TableName
                AND OBJECTPROPERTY(
                        OBJECT_ID(
                            QUOTENAME(TABLE_SCHEMA) + '.' + QUOTENAME(TABLE_NAME)
                             ), 'IsMSShipped'
                               ) = 0
        )

        WHILE (@TableName IS NOT NULL) AND (@ColumnName IS NOT NULL)
        BEGIN
            SET @ColumnName =
            (
                SELECT MIN(QUOTENAME(COLUMN_NAME))
                FROM    INFORMATION_SCHEMA.COLUMNS
                WHERE       TABLE_SCHEMA    = PARSENAME(@TableName, 2)
                    AND TABLE_NAME  = PARSENAME(@TableName, 1)
                    AND DATA_TYPE IN ('char', 'varchar', 'nchar', 'nvarchar')
                    AND QUOTENAME(COLUMN_NAME) > @ColumnName
            )

            IF @ColumnName IS NOT NULL
            BEGIN
                INSERT INTO #Results
                EXEC
                (
                    'SELECT ''' + @TableName + '.' + @ColumnName + ''', LEFT(' + @ColumnName + ', 3630)
                    FROM ' + @TableName + ' (NOLOCK) ' +
                    ' WHERE ' + @ColumnName + ' LIKE ' + @SearchStr2
                )
            END
        END
    END

SET @StrNtLogin = Substring(@StrNtLogin,Charindex(@SplitOn,@StrNtLogin)+1,len(@StrNtLogin))
SET @Cnt = @Cnt + 1
END


SELECT ColumnName, ColumnValue FROM #Results

这篇关于SqlBulkCopy无法正常工作？的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！