Dart Feld Dart Feld - 1 month ago 9
C# Question

Extracting a stored procedure COUNT result from the DataSet in C#

I am running a stored procedure returning the result of a

COUNT
on a huge
SELECT
.

I find myself having a DataSet as a result in my C# code but after numerous tries I fail to extract the result of the
COUNT
.

Here's my code, you can ignore the long Stored procedure It's just a
COUNT (*)
in the end, but it's there in case.




C# Code:

internal int ObtenirCount(string ProcedureName, IConnexionBD _ConnexionBD, params SqlParameter[] parametres)
{
int count = 0;
IConnexionBD _ConnBd = _ConnexionBD ?? new ConnexionBD(ObtenirChaineDeConnexion());

DataSet ds = _ConnBd.ExecuteDataSet(string.Format(ProcedureName, NomTable), parametres);

if (ds != null && ds.Tables.Count > 0 && ds.Tables[0].Rows.Count > 0)
{
count = ds.Tables[0].Rows.Count; // Doesn't work
}
return count;
}





Stored Procedure:

ALTER PROCEDURE [dbo].[PSS_us_net_010]
@PageSize int,
@PageNumber int,
@filtreType varchar,
@filtrePrincipal varchar,
@filtreNumUtilisateur varchar,
@filtreNom varchar,
@filtreCourriel varchar,
@filtreCompteCentral varchar,
@sortColumnUser varchar

AS

DECLARE @Sort char(35)
DECLARE @StartRow int
DECLARE @strFiltreType varchar(100) = ''
DECLARE @strFiltrePrincipal varchar(100) = ''
DECLARE @strFiltreNumUtilisateur varchar(100) = ''
DECLARE @strFiltreNom varchar(100) = ''
DECLARE @strFiltreCourriel varchar(100) = ''
DECLARE @strFiltreCompteCentral varchar(100) = ''
DECLARE @strSortColUser varchar(100) = ''

SET @StartRow = CAST(((@PageNumber - 1)*@PageSize + 1) AS varchar(50))
IF @filtreType != ''
BEGIN
SET @strFiltreType = @filtreType
END
IF @filtrePrincipal != ''
BEGIN
IF @filtrePrincipal = 'o' OR @filtrePrincipal = 'u' OR @filtrePrincipal = 'i' OR @filtrePrincipal = 'ou' OR @filtrePrincipal= 'oui' OR @filtrePrincipal = 'ui'
SET @strFiltrePrincipal = 00
ELSE
SET @filtrePrincipal = NULL
END
IF @filtreNumUtilisateur != ''
BEGIN
SET @strFiltreNumUtilisateur = @filtreNumUtilisateur
END
IF @filtreNom != ''
BEGIN
SET @strFiltreNom = @filtreNom
END
IF @filtreCourriel != ''
BEGIN
SET @strFiltreCourriel = @filtreCourriel
END
IF @filtreCompteCentral != ''
BEGIN
SET @strFiltreCompteCentral = @filtreCompteCentral
END
IF @sortColumnUser != ''
BEGIN
SET @strSortColUser = @sortColumnUser
END

BEGIN

SELECT COUNT(*)FROM
(
SELECT
ty_util_net,
ident_util_net,
no_usager_net,
nip,
nip_temporaire,
adr_courriel,
dte_creer,
dte_modif,
dte_dern_utilisation,
hre_dern_utilisation,
nb_visites,
nom_prenom,
dte_avant_dern_util,
hre_avant_dern_util,
adr_courriel_erronee,
phrase_mot_passe,
nom_navigateur,
version_navigateur,
date_env_courriel,
ChoixAucunePhrase,
EnvoiCourriel,
DetCCReg,
IndAffPhrase
FROM us_net
WHERE nom_prenom >= @Sort
AND(@filtreType IS NULL OR ty_util_net LIKE '%' + @strFiltreType + '%')
AND(@filtrePrincipal IS NULL OR no_usager_net LIKE '%' + @strFiltrePrincipal + '%')
AND(@filtreNumUtilisateur IS NULL OR ident_util_net LIKE '%' + @strFiltreNumUtilisateur + '%')
AND(@filtreNom IS NULL OR nom_prenom LIKE '%' + @strFiltreNom + '%')
AND(@filtreCourriel IS NULL OR adr_courriel LIKE '%' + @strFiltreCourriel + '%')
) AS countResult
END

Answer

The result of that stored procedure is in the first row, first column.
The property Rows.Count return always 1 in this context because its purpose is to count the number of rows present in the DataTable

So your code should be simply

count = Convert.ToInt32(ds.Tables[0].Rows[0][0]);

As noted in comments, when you need just a single scalar value like the ones returned by COUNT, SUM or any single row/single column query, you should think to use ExecuteScalar. It is a lot more efficient because it doesn't need to build DataSets, DataTables and all the infrastructure required to support these objects.