如何将C#中的Datatable传递给SQL Server存储过程

Question

我正在开发一个视频游戏比赛管理网站。 试图获取具有指定参数的用户（他们最后登录的本地电子咖啡馆城市和他们玩的游戏）。 为了获得最佳性能，我首先编写了一个存储过程并且它正在工作。 但是在从代码中调用时，会抛出以下异常。

异常消息：Column、parameter 或 variable @cityIds。: 无法找到数据类型 dbo.CityIds。

SQL

CREATE TYPE [dbo].[CityIds] AS TABLE(
  [Id] [uniqueidentifier] NOT NULL
)
GO

ALTER   PROCEDURE [dbo].[spGetUsersByCityAndGame] 
  @gameProcessName NVARCHAR(50),
  @cityIds       [CityIds] READONLY
AS
BEGIN
SET NOCOUNT ON;
SELECT users.Id
FROM AspNetUsers users WITH( NOLOCK )
JOIN
( -- select below finds last log entry by startDateTime column for each user
  SELECT authLog1.*
  FROM AuthenticationLog authLog1 WITH( NOLOCK )
  LEFT OUTER JOIN
  AuthenticationLog authLog2 WITH( NOLOCK )
  ON authLog1.ClientId=authLog2.ClientId
     AND authLog1.StartDateTime<authLog2.StartDateTime
  WHERE authLog2.ClientId IS NULL
) lastAuthLog
ON users.Id=lastAuthLog.ClientId
    JOIN
    Club club
    ON lastAuthLog.ClubId=club.Id
        JOIN
        City city
        ON club.CityId=city.Id
            JOIN
            ProcessLogs processLog
            ON lastAuthLog.Id=processLog.AuthenticationLogId
            INNER JOIN @cityIds cids on city.Id = cids.Id
WHERE users.Birthday IS NOT NULL
      AND users.PhoneNumber IS NOT NULL
      AND users.UserName IS NOT NULL
      AND users.Email IS NOT NULL
      AND users.FullName IS NOT NULL
      AND processLog.ProcessName=@gameProcessName;
END;

C#

private async Task<List<ApplicationUser>> GetUserByParameters(Guid gameId, IEnumerable<Guid> citiesIds)
{
    Game game = await _gamesRepository.GetAsync(gameId);

    SqlParameter gameNameParam = new SqlParameter("@gameProcessName", SqlDbType.NVarChar, 50)
    {
        Value = (object)game.ExecutableName ?? DBNull.Value
    };

    List<SqlDataRecord> table = new List<SqlDataRecord>();

    foreach (Guid cityId in citiesIds)
    {
        SqlDataRecord tableRow = new SqlDataRecord(new SqlMetaData[] { new SqlMetaData("Id", SqlDbType.UniqueIdentifier) });

        tableRow.SetGuid(0, cityId);

        table.Add(tableRow);
    }

    SqlParameter citiesIdsParam = new SqlParameter
    {
        TypeName = "[dbo].[CityIds]",
        SqlDbType = SqlDbType.Structured,
        ParameterName = "@cityIds",
        Value = table,
    };

    string sql = @"EXECUTE dbo.spGetUsersByCityAndGame @gameProcessName, @cityIds";

    List<ApplicationUser> users = await _dbContext.Users.FromSqlRaw(sql, gameNameParam, citiesIdsParam).ToListAsync();

    return users;
}

- Igor Suslov

---

考虑到你的代码中从未出现过 dbo.CityIdsTTV，我觉得我们可能遗漏了某些东西。另外，应该使用 EXECUTE dbo.spGetUsersByCityAndGame @gameProcessName, @cityIds READONLY。 - Thom A

用户是否有访问权限？要使用表类型，需要对该类型具有“CONTROL”权限。（从技术上讲，“EXECUTE”和“REFERENCES”就足够了，但这并没有更多的限制。） - Jeroen Mostert

通常情况下，您可以使用DataAdapter查询数据库并将结果放入DataTable中，DataAdapter将数据库行链接到C# DataTable。然后，您只需要使用DataTable的Update方法即可将更改存储回数据库。 - jdweng

@SowmyadharGourishetty 已经可以从 SSMS 调用存储过程，但我在使用 C# 代码时遇到了问题。应用您的建议后问题仍未解决。 - Igor Suslov

@Larnu 是拼写错误，因为我重新创建了类型并忘记在这里更新它。异常仍然存在。 - Igor Suslov

Answer 1

如果你想要进行“基于集合”的 SQL 操作，你可以通过一个虚拟的 DataSet 将你的 DataTable 转换成 XML。

            System.Data.DataTable dbfacs = System.Data.Common.DbProviderFactories.GetFactoryClasses(); /* replace this with YOUR datatable code */
            System.Data.DataSet ds1 = new DataSet();
            ds1.Tables.Add(dbfacs);
            string xml = ds1.GetXml();

现在将此xml发送到您的存储过程中。在存储过程中，您将把xml“解析”成@变量或#temp表...然后从那里进行CUD（创建/更新/删除）或（根据需要），可以在@变量或#temp表上执行JOIN或EXISTS子句。

请参阅完整示例：

https://www.sqlservercentral.com/articles/the-zero-to-n-parameter-problem-sql-server-2005-and-up-update

通用示例：（使用Northwind数据库）（DDL在此处：https://github.com/Microsoft/sql-server-samples/tree/master/samples/databases/northwind-pubs）

IF EXISTS (
            SELECT * FROM INFORMATION_SCHEMA.ROUTINES
            WHERE ROUTINE_TYPE = N'PROCEDURE' and ROUTINE_SCHEMA = N'dbo' and ROUTINE_NAME = N'uspCustomerFindByXml'  
        )   
BEGIN
    DROP PROCEDURE [dbo].[uspCustomerFindByXml]
END

GO

CREATE PROCEDURE dbo.uspCustomerFindByXml (
    @xmlSource xml , 
    @numberRowsAffected int output  --return
)

AS 

SET NOCOUNT ON 

DECLARE @errorTracker int -- used to "remember" the @@ERROR

DECLARE @updateRowCount int
DECLARE @insertRowCount int 


-- build a table (variable table) to store the xml-based result set
DECLARE @CustomerHolder TABLE (  
    identityid int IDENTITY (1,1) , 
CustomerID varchar(6) 
)


INSERT @CustomerHolder
    (
        CustomerID 
    )
SELECT 
    T.parameter.value('(CustomerID)[1]', 'varchar(6)') AS CustomerID

FROM @xmlSource.nodes('/CustomersDS/Customers') AS T(parameter);



select * from @CustomerHolder



SET NOCOUNT OFF


Select @updateRowCount = @@ROWCOUNT

SELECT cust.CustomerID, cust.CompanyName, cust.ContactName FROM

    dbo.Customers cust
WHERE
    exists (   select null from @CustomerHolder holder where ltrim(rtrim(upper(holder.CustomerID))) = ltrim(rtrim(upper(cust.CustomerID)))   )

Select @insertRowCount = @@ROWCOUNT

select @numberRowsAffected = @insertRowCount + @updateRowCount

--select * from Customers

SET NOCOUNT OFF


GO




GRANT EXECUTE on dbo.uspCustomerFindByXml TO public



GO





declare @numberRowsAffected int

EXEC dbo.uspCustomerFindByXml
'

<CustomersDS>
  <Customers>
    <CustomerID>ALFKI</CustomerID>
  </Customers>
  <Customers>
    <CustomerID>ANATR</CustomerID>
  </Customers>
  <Customers>
    <CustomerID>ANTON</CustomerID>
  </Customers>
</CustomersDS>

' , @numberRowsAffected OUT

print '/@numberRowsAffected/'
print @numberRowsAffected
print ''


GO

这里有一个链接，显示了“基础知识”。来自微软：

https://learn.microsoft.com/en-us/dotnet/framework/data/adonet/sql/specifying-xml-values-as-parameters

额外信息：

这种“样式”也可用于CRUD功能。您也可以通过联接或where-existing在切碎的xml上进行创建/读取/更新/删除。

下面是CU（创建/更新）的通用示例：

IF EXISTS (
            SELECT * FROM INFORMATION_SCHEMA.ROUTINES
            WHERE ROUTINE_TYPE = N'PROCEDURE' and ROUTINE_SCHEMA = N'dbo' and ROUTINE_NAME = N'uspTitleUpdate'  
        )   
BEGIN
    DROP PROCEDURE [dbo].[uspTitleUpdate]
END

GO

CREATE PROCEDURE dbo.uspTitleUpdate (
    @xmlSource xml , 
    @numberRowsAffected int output  --return
)

AS 

SET NOCOUNT ON 

DECLARE @errorTracker int -- used to "remember" the @@ERROR

DECLARE @updateRowCount int
DECLARE @insertRowCount int 


-- build a table (variable table) to store the xml-based result set
DECLARE @TitleHolder TABLE (  
    identityid int IDENTITY (1,1) , 
 
title_id varchar(6) , 
title varchar(80) , 
type varchar(32) , 
pub_id varchar(32) , 
price money , 
advance money , 
royalty varchar(32) , 
ytd_sales varchar(32) , 
notes TEXT , 
pubdate datetime
)


INSERT @TitleHolder
    (
        title_id ,
        title ,
        [type] ,
        pub_id ,
        price ,
        advance ,
        royalty ,
        ytd_sales ,
        notes ,
        pubdate
    )
SELECT 
    T.parameter.value('(title_id)[1]', 'varchar(6)') AS title_id
    , T.parameter.value('(title)[1]', 'varchar(80)') AS title
    , T.parameter.value('(type)[1]', 'varchar(32)') AS [type] 
    , T.parameter.value('(pub_id)[1]', 'varchar(32)') AS pub_id
    , T.parameter.value('(price)[1]', 'money') AS price
    , T.parameter.value('(advance)[1]', 'money') AS advance
    , T.parameter.value('(royalty)[1]', 'varchar(32)') AS royalty
    , T.parameter.value('(ytd_sales)[1]', 'varchar(32)') AS ytd_sales
    , T.parameter.value('(notes)[1]', 'varchar(max)') AS notes
    , dbo.udf_convert_xml_date_to_datetime (T.parameter.value('(pubdate)[1]', 'varchar(64)') ) AS pubdate
FROM @xmlSource.nodes('/TitlesDS/Titles') AS T(parameter);


/* 
select * from @TitleHolder
*/


SET NOCOUNT OFF


Update 
    titles 
set 
    title = tu.title , 
    [type]  = tu.[type]  , 
    pub_id = tu.pub_id , 
    price = tu.price , 
    advance  = tu.advance , 
    royalty  = tu.royalty , 
    ytd_sales  = tu.ytd_sales , 
    notes  = tu.notes , 
    pubdate  = tu.pubdate 
FROM
    @TitleHolder tu , titles
WHERE
    ltrim(rtrim(upper(titles.title_id))) = ltrim(rtrim(upper(tu.title_id)))


Select @updateRowCount = @@ROWCOUNT

INSERT INTO titles
    (
        title_id ,
        title ,
        [type]  ,
        pub_id ,
        price ,
        advance ,
        royalty ,
        ytd_sales ,
        notes ,
        pubdate
    )
Select
    title_id ,
    title ,
    [type]  ,
    pub_id ,
    price ,
    advance ,
    royalty ,
    ytd_sales ,
    notes ,
    pubdate
FROM
    @TitleHolder tu
WHERE
    not exists (   select null from dbo.titles innerRealTable where ltrim(rtrim(upper(innerRealTable.title_id))) = ltrim(rtrim(upper(tu.title_id)))   )

Select @insertRowCount = @@ROWCOUNT

select @numberRowsAffected = @insertRowCount + @updateRowCount

--select * from titles

SET NOCOUNT OFF


GO




GRANT EXECUTE on dbo.uspTitleUpdate TO public



GO





/* 


declare @numberRowsAffected int

EXEC dbo.uspTitleUpdate
'

<TitlesDS>
  <Titles>
    <title_id>BU1032</title_id>
    <title>The Busy Executives Database Guide</title>
    <type>business    </type>
    <price>19.99</price>
    <pubdate>2013-12-10T13:42:27.020604-05:00</pubdate>
  </Titles>
  <Titles>
    <title_id>BU1111</title_id>
    <title>Cooking with Computers: Surreptitious Balance Sheets</title>
    <type>business    </type>
    <price>11.95</price>
    <pubdate>2013-12-10T13:42:27.021604-05:00</pubdate>
  </Titles>
  <Titles>
    <title_id>BU2075</title_id>
    <title>You Can Combat Computer Stress!</title>
    <type>business    </type>
    <price>2.99</price>
    <pubdate>2013-12-10T13:42:27.021604-05:00</pubdate>
  </Titles>
</TitlesDS>

' , @numberRowsAffected OUT

print '/@numberRowsAffected/'
print @numberRowsAffected
print ''

*/

GO

- granadaCoder

---

现在是2020年，自定义表类型是将表值数据传递到SQL的正确方式，而不是XML。通过SQL解析XML会消耗大量CPU资源，速度非常慢。 - Ian Kemp

碎片化处理速度有点慢。但是RBAR与一些碎片化处理相比要慢得多。TVP和UDT是特定于SQL Server的。XML在跨关系型数据库管理系统兼容性方面可能更高一些。因此，有利有弊。 - granadaCoder

用户定义的表类型不够灵活，建议使用 XML。 - elle0087