How to pass datatable from C# to SQL Server stored procedure

Question

I'm developing a video game tournament handling site. Trying to get user with specified parameters(city where they last logged in local e-cafe, and game they played). For optimal performance I started with writing a stored procedure and its working. But when calling from code following exception thrown.

Exception.Message: Column, parameter, or variable @cityIds. : Cannot find data type 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;
}

Answer 1

If you want to do "set based" sql, you can "convert" your DataTable to xml (via a faux DataSet)

            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();

And now send this xml do your stored procedure. In the stored procedure, you will "shred" the xml into a @variable or #temp table...and do your CUD (create/update/delete) from there OR (as you need), can do do JOINS or EXISTS clauses on the @variable or #temp tables.

See full example here:

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

Generic example: (using Northwind database) (DDL here: 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

Here is a link showing the "basics" as well. From Microsoft:

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

EXTRA:

This "style" can be used for CRUD functionality as well. You can Create/Read/Update/Delete by joining or where-existing on the shredded xml as well.

CU (create /update ) generic example below:

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