c# 将datatable传递给存储过程实现批量跟新或者添加数据

1 存储过程执行的方法

 

public static void addPerformanceSuggestReportList(DataTable dt)
{
using (SqlConnection con = GetEditorConnection())
{
con.Open();
using (SqlCommand cmd = new SqlCommand("dbo.addPerformanceSuggestReportList", con))
{
cmd.CommandType = CommandType.StoredProcedure;
cmd.Parameters.AddWithValue("@p_Source", dt);
cmd.ExecuteNonQuery();
}
}
}

2 编写存储过程

USE [StatusData]
GO

IF EXISTS (SELECT * FROM sysobjects WHERE id = OBJECT_ID('dbo.addPerformanceSuggestReportList')
AND OBJECTPROPERTY(id, N'IsProcedure') = 1)
DROP PROCEDURE dbo.addPerformanceSuggestReportList
GO

if exists(select * from sysobjects where id=object_id(N'[dbo.tp_PerformanceSuggestReport]')and OBJECTPROPERTY(id, N'IsUserTable') = 1)
DROP type dbo.tp_PerformanceSuggestReport
GO

CREATE TYPE dbo.tp_PerformanceSuggestReport AS TABLE
(
Id char(10) NOT NULL,
DataPointType TINYINT NOT NULL,
SuggestType TINYINT NOT NULL,
OriginalValue varchar(75) NULL,
SuggestValue varchar(75) NULL,
Change INT NULL,
Description varchar(200) NULL,
IdInformation varchar(275) NULL,
UserId INT NULL,
Status TINYINT NULL
)
GO

GRANT EXECUTE ON TYPE::dbo.tp_PerformanceSuggestReport TO public
GO

 

-- Author: mibing
-- Create Date: 2016-12-8
-- Revision:
CREATE PROCEDURE dbo.addPerformanceSuggestReportList
@p_Source dbo.tp_PerformanceSuggestReport READONLY
AS
BEGIN
DECLARE @l_Err INTEGER,
@l_Msg NVARCHAR(100),
@l_Id NVARCHAR(15),
@l_ProcName VARCHAR(30),
@l_ProcDB VARCHAR(30)

DECLARE @l_Cnt INTEGER

-- Initialize error handle-related constants
SET @l_Id = ''
SET @l_ProcName = OBJECT_NAME (@@PROCID)
SET @l_ProcDB = DB_NAME()
BEGIN TRY
MERGE INTO dbo.PerformanceSuggestReport psr
USING @p_Source src ON psr.Id = src.Id AND psr.DataPointType = src.DataPointType
and psr.SuggestType=src.SuggestType
WHEN MATCHED THEN
UPDATE SET psr.OriginalValue = src.OriginalValue,
psr.SuggestValue = src.SuggestValue,
psr.Change = src.Change,
psr.Description =src.Description,
psr.IdInformation =src.IdInformation,
psr.UserId =src.UserId,
psr.Status = src.Status,
psr.LastUpdate =getdate()
WHEN NOT MATCHED BY TARGET THEN
INSERT( Id,
DataPointType,
SuggestType,
OriginalValue,
SuggestValue,
Change,
Description,
IdInformation,
UserId,
Status,
LastUpdate)
VALUES( src.Id,
src.DataPointType,
src.SuggestType,
src.OriginalValue,
src.SuggestValue,
src.Change,
src.Description,
src.IdInformation,
src.UserId,
src.Status,
getdate());

END TRY

BEGIN CATCH
IF @@TRANCOUNT > 0
BEGIN
ROLLBACK;
END
-- Log error message
SET @l_Msg = 'Number: ' + CONVERT(VARCHAR, ERROR_NUMBER()) + CHAR(10) +
'Line: ' + CONVERT(VARCHAR, ERROR_LINE()) + CHAR(10) +
'Severity: ' + CONVERT(VARCHAR, ERROR_SEVERITY()) + CHAR(10) +
'State: ' + CONVERT(VARCHAR, ERROR_STATE()) + CHAR(10) +
'Procedure: ' + ISNULL(ERROR_PROCEDURE(), '') + CHAR(10) +
'Message: ' + ERROR_MESSAGE()

SET @l_Err = ERROR_NUMBER()

-- Store error and raise error
EXECUTE dbo.sp_LogError @l_Err, @l_Msg, @l_Id, @l_ProcName, @l_ProcDB
RAISERROR(@l_Msg, 18, 1)
RETURN @l_Err
END CATCH

END
GO

GRANT EXECUTE ON dbo.addPerformanceSuggestReportList TO rl_DataUpload
GO