循环访问动态数据集并插入存储过程

本文关键字：插入 存储过程 数据集 访问 动态 循环 | 更新日期: 2023-09-27 17:55:41

我有一个 C# 数据表，我需要插入到表中。数据表是完全动态的（列不是预定义的）。我使用 C# 代码逐行插入此数据表，但由于效率低下，我将数据表批量发送到 SQL 存储过程中。我需要存储过程遍历批量，逐行插入，并返回一组无效数据。

C# 代码： 

SqlConnection sqlConnection = getDBConnection();
SqlCommand command = sqlConnection.CreateCommand();
command.CommandType = System.Data.CommandType.StoredProcedure;
command.CommandText = "[dbo].[saveDataIntoTable]";
SqlParameter parameter = new SqlParameter();
//The parameter for the SP must be of SqlDbType.Structured
parameter.ParameterName = "@Sample";
parameter.SqlDbType = System.Data.SqlDbType.Structured;
parameter.Value = dataTable;
command.Parameters.Add(parameter);
        foreach (DataRow row in dataTable.Rows)
        {
            System.Diagnostics.Debug.WriteLine(row.ItemArray);
            if (row.ItemArray[0] == null)
            {
                dataTable.Rows.Remove(row);
            }
        }
SqlDataReader dr = command.ExecuteReader();
DataTable dt = new DataTable();
dt.Load(dr);
//handling the dt DataTable here

存储过程：

USE [DATABASE_NAME]
GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE PROCEDURE [dbo].[saveDataIntoTable]
(
    -- which accepts one table value parameter. It should be noted that   the parameter is readonly
    @Sample As [dbo].[SampleUserData] Readonly
)
AS
BEGIN
BEGIN TRY
    Insert  Into    USER(USER_ID,EMAIL,PASSWORD,PASSWORD_HINT,PWD_CHANGED_DATE,
        CREATED_BY,CREATED_DATE,UPDATED_BY,UPDATED_DATE,STATUS,VERSION,VALIDATE)
        Select USER_ID, EMAIL,PASSWORD,PASSWORD_HINT,PWD_CHANGED_DATE,
        CREATED_BY,CREATED_DATE,UPDATED_BY,UPDATED_DATE,STATUS,VERSION,VALIDATE From @Sample
END TRY
BEGIN CATCH
    Select USER_ID, EMAIL,PASSWORD,PASSWORD_HINT,PWD_CHANGED_DATE,
        CREATED_BY,CREATED_DATE,UPDATED_BY,UPDATED_DATE,STATUS,VERSION,VALIDATE From @Sample 
END CATCH
END

我对存储过程使用了用户定义的表类型：

-- Create a table data type
CREATE TYPE [dbo].[SampleUserData] As Table
(
        --This type has structure similar to the DB table           
        USER_ID Nvarchar(20) ,
        EMAIL Nvarchar(50),
        PASSWORD Nvarchar(100),
        PASSWORD_HINT Nvarchar(20),
        PWD_CHANGED_DATE date,
        CREATED_BY Nvarchar(20),
        CREATED_DATE date,
        UPDATED_BY Nvarchar(20),
        UPDATED_DATE date,
        STATUS Nvarchar(20),
        VERSION Int,
        VALIDATE Nvarchar(10)
);

现在，我的存储过程一次插入整个数据。当发生异常时，它会返回整个数据集（我不知道如何分隔行）。

PS：如果上述情况还有其他更简单的方法，请告诉我。

谢谢。

循环访问动态数据集并插入存储过程

这将与

存储过程相同，一次一行：这是为 SQL Server 2005 编写

的 
BEGIN
--I'm only using your first three columns in this example
DECLARE @USER_ID as Nvarchar(20);
DECLARE @Email as Nvarchar(20);
DECLARE @Password as Nvarchar(20);
DECLARE @SampleCursor as CURSOR;
SET @SampleCursor = CURSOR FOR
SELECT USER_ID, EMAIL, PASSWORD
 FROM  @Sample;
OPEN @SampleCursor;
--Can't insert directly into table from table variable so save as scalar variable first
FETCH NEXT FROM @SampleCursor INTO @USER_ID, @Email, @Password;
WHILE @@FETCH_STATUS = 0
BEGIN
FETCH NEXT FROM @SampleCursor INTO @USER_ID, @Email, @Password;
BEGIN TRY
--then insert scalar variables into table
INSERT INTO USER (USER_ID, Email, Password) VALUES( @USER_ID, @Email, @Password)
END TRY
BEGIN CATCH
SELECT  @USER_ID, @Email, @Password
END CATCH
END
CLOSE @SampleCursor;
DEALLOCATE @SampleCursor;
END

这可能有效。只需使用 TOP（1） 一次插入一行源表即可。下面的解决方案会临时创建一个表，以便不会删除源表。

--create copy of source table
SELECT * INTO TempTbl FROM Source_Table
--loop through temp table
WHILE EXISTS (SELECT * FROM TempTbl)
BEGIN
--insert first line of temp table into destination table
BEGIN TRY 
INSERT INTO [USER] SELECT TOP (1) * FROM TempTbl
END TRY
BEGIN CATCH
SELECT TOP(1) FROM TempTbl
END CATCH
--remove inserted line from temp table
DELETE TOP (1) FROM TempTbl
END
DROP TABLE TempTbl

更新。

这对我有用：

CREATE PROCEDURE SOProc 
    -- Add the parameters for the stored procedure here
    @Source_Table_Name sysname = '' 
AS
BEGIN
EXEC(
'SELECT * INTO TempTbl FROM ' +  @Source_Table_Name)
WHILE EXISTS (SELECT * FROM TempTbl)
BEGIN
BEGIN TRY 
INSERT INTO User_Table SELECT TOP (1) * FROM TempTbl
END TRY
BEGIN CATCH
SELECT TOP(1) * FROM TempTbl
END CATCH
DELETE TOP (1) FROM TempTbl
END
DROP TABLE TempTbl
END
GO

为什么不使用 where 子句来验证数据

-- insert valid data
insert into [USER] (USER_ID,EMAIL,PASSWORD,PASSWORD_HINT,PWD_CHANGED_DATE,
                  CREATED_BY,CREATED_DATE,UPDATED_BY,UPDATED_DATE,STATUS,VERSION,VALIDATE)
select USER_ID, EMAIL,PASSWORD,PASSWORD_HINT,PWD_CHANGED_DATE,
       CREATED_BY,CREATED_DATE,UPDATED_BY,UPDATED_DATE,STATUS,VERSION,VALIDATE
from @Sample
where USER_ID is not null
-- select invalid data
select USER_ID, EMAIL,PASSWORD,PASSWORD_HINT,PWD_CHANGED_DATE,
       CREATED_BY,CREATED_DATE,UPDATED_BY,UPDATED_DATE,STATUS,VERSION,VALIDATE
from @Sample
where USER_ID is null

更详细的验证检查示例

select USER_ID, EMAIL,PASSWORD,PASSWORD_HINT,PWD_CHANGED_DATE,
       CREATED_BY,CREATED_DATE,UPDATED_BY,UPDATED_DATE,STATUS,VERSION,VALIDATE
from @Sample S
where USER_ID is not null
and EMAIL is not null
and PASSWORD is not null -- etc
-- check record is not duplicate
and not exists (select 1 from [USER] U where U.USER_ID = S.USER_ID)
and isnumeric(USER_ID)
相关文章：