Passing DataTable from C# to SQL Server Stored Procedure

Introduction

In this article, we will explore how to pass a DataTable from C# to a SQL Server stored procedure. We will go through the process of converting the DataTable to an XML string and then passing it as a parameter to the stored procedure.

Problem Description

The question states that you are developing a video game tournament handling site and have written a stored procedure for retrieving users based on their location and game played. However, when calling the stored procedure from code, an exception is thrown due to the CityIds data type not being recognized.

Solution Overview

To solve this problem, we will use the following approach:

1. Convert the DataTable to an XML string using a DataSet.
2. Pass the XML string as a parameter to the stored procedure.
3. In the stored procedure, convert the XML string back to a DataTable and perform the desired operations.

Converting DataTable to XML String

To convert the DataTable to an XML string, we will use the following code:

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

This code creates a DataSet object, adds the DataTable to it, and then gets the XML string representation of the dataset.

Passing XML String as Parameter

To pass the XML string as a parameter to the stored procedure, we will use the following code:

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";

This code creates a SqlParameter object for the @gameProcessName parameter and sets its value to the executable name of the game. It also creates a list of SqlDataRecord objects representing the cities IDs and passes them as a parameter to the stored procedure.

Converting XML String Back to DataTable

To convert the XML string back to a DataTable, we will use the following code in the stored procedure:

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;

This code creates a temporary table to store the result of the XML query and then selects all columns from the table.

Example Use Case

Here is an example use case for passing a DataTable from C# to a SQL Server stored procedure:

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.FromSQL(sql);

This code calls the stored procedure and retrieves a list of ApplicationUser objects.

Conclusion

In this article, we have explored how to pass a DataTable from C# to a SQL Server stored procedure. We have covered the process of converting the DataTable to an XML string and then passing it as a parameter to the stored procedure. We have also provided an example use case for passing a DataTable from C#.

Last modified on 2023-06-10