SqlBulkCopy无法正常工作? [英] SqlBulkCopy not working properly ?
本文介绍了SqlBulkCopy无法正常工作?的处理方法,对大家解决问题具有一定的参考价值,需要的朋友们下面随着小编来一起学习吧!
问题描述
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屋!
查看全文