multiples séparées des requêtes sql dans une procédure stockée

J'ai besoin de beaucoup de (16) requêtes différentes pour obtenir le nombre de lignes en même temps. Je courais 16 connexions différentes, mais pensé que je pourrais combiner à tout juste 1 de la procédure stockée.

Je vais avoir des ennuis, car il donne l'erreur sur l'objet datareader (système hors de portée excpection) pour cette ligne:

Count2 = Convert.ToInt32(objDRL("Row_Count2").ToString).

Voici mon code:

Dim strConn As String = "Data Source=myDataSource"
Dim Conn As New SqlConnection(strConn)
Dim Cmd As New SqlCommand("adminStats", Conn)
Cmd.CommandType = CommandType.StoredProcedure
Dim objDRL As SqlDataReader
Cmd.Parameters.Add(New SqlParameter("@campDate", "June 2014"))
Conn.Open()
objDRL = Cmd.ExecuteReader(System.Data.CommandBehavior.CloseConnection)
If objDRL.HasRows Then
While objDRL.Read()
Count1 = Convert.ToInt32(objDRL("Row_Count1"))
Count2 = Convert.ToInt32(objDRL("Row_Count2"))
Count3 = Convert.ToInt32(objDRL("Row_Count3"))
Count4 = Convert.ToInt32(objDRL("Row_Count4"))
Count5 = Convert.ToInt32(objDRL("Row_Count5"))
Count6 = Convert.ToInt32(objDRL("Row_Count6"))
Count7 = Convert.ToInt32(objDRL("Row_Count7"))
Count8 = Convert.ToInt32(objDRL("Row_Count8"))
Count9 = Convert.ToInt32(objDRL("Row_Count9"))
Count10 = Convert.ToInt32(objDRL("Row_Count10"))
Count11 = Convert.ToInt32(objDRL("Row_Count11"))
Count12 = Convert.ToInt32(objDRL("Row_Count12"))
Count13 = Convert.ToInt32(objDRL("Row_Count13"))
Count14 = Convert.ToInt32(objDRL("Row_Count14"))
Count15 = Convert.ToInt32(objDRL("Row_Count15"))
End While
Else
End If
link1.Text = Count1.ToString
link2.Text = Count2.ToString
link3.Text = Count3.ToString
link4.Text = Count4.ToString
Link5.Text = Count5.ToString
Link6.Text = Count6.ToString
Link7.Text = Count7.ToString
Link8.Text = Count8.ToString
Link9.Text = Count9.ToString
Link10.Text = Count10.ToString
Link11.Text = Count11.ToString
Link12.Text = Count12.ToString
Link13.Text = Count13.ToString
Link14.Text = Count14.ToString
Link15.Text = Count15.ToString
Conn.Close()

Procédure stockée:

CREATE PROCEDURE adminStats 
@campDate VARCHAR(20)
AS
BEGIN
SELECT COUNT(*) AS Row_Count1 FROM Customer
SELECT COUNT(*) AS Row_Count2 FROM campRegistration WHERE campDate = @campDate
SELECT COUNT(*) AS Row_Count3 FROM campRegistration WHERE paidFull = 'True'
SELECT COUNT(*) AS Row_Count4 FROM campRegistration WHERE shirtSize = 'S'
SELECT COUNT(*) AS Row_Count5 FROM campRegistration WHERE shirtSize = 'M'
SELECT COUNT(*) AS Row_Count6 FROM campRegistration WHERE shirtSize = 'L'
SELECT COUNT(*) AS Row_Count7 FROM campRegistration WHERE shirtSize = 'XL'
SELECT COUNT(*) AS Row_Count8 FROM campRegistration WHERE shirtSize = 'XXL'
SELECT COUNT(*) AS Row_Count9 FROM campRegistration WHERE Staff = 'True'
SELECT COUNT(*) AS Row_Count10 FROM campRegistration WHERE days = '1'
SELECT COUNT(*) AS Row_Count11 FROM campRegistration WHERE days = '2'
SELECT COUNT(*) AS Row_Count12 FROM campRegistration WHERE days = '3'
SELECT COUNT(*) AS Row_Count13 FROM campRegistration WHERE days = '12'
SELECT COUNT(*) AS Row_Count14 FROM campRegistration WHERE days = '23'
SELECT COUNT(*) AS Row_Count15 FROM campRegistration WHERE days = '123'
END
GO

OriginalL'auteur mlg74 | 2013-09-05

Essayez de changer votre requête sur ce -

CREATE PROCEDURE adminStats 
@campDate varChar(20)
AS
BEGIN
SELECT 
(SELECT COUNT(1) FROM dbo.Customer) AS Row_Count1
, COUNT(CASE WHEN campDate = @campDate THEN 1 END) AS Row_Count2
, COUNT(CASE WHEN paidFull = 'True' THEN 1 END) AS Row_Count3
, COUNT(CASE WHEN shirtSize = 'S' THEN 1 END) AS Row_Count4  
, COUNT(CASE WHEN shirtSize = 'M' THEN 1 END) AS Row_Count5  
, COUNT(CASE WHEN shirtSize = 'L' THEN 1 END) AS Row_Count6  
, COUNT(CASE WHEN shirtSize = 'XL' THEN 1 END) AS Row_Count7  
, COUNT(CASE WHEN shirtSize = 'XXL' THEN 1 END) AS Row_Count8  
, COUNT(CASE WHEN Staff = 'True' THEN 1 END) AS Row_Count9
, COUNT(CASE WHEN [days] = '1' THEN 1 END) AS Row_Count10 
, COUNT(CASE WHEN [days] = '2' THEN 1 END) AS Row_Count11
, COUNT(CASE WHEN [days] = '3' THEN 1 END) AS Row_Count12
, COUNT(CASE WHEN [days] = '12' THEN 1 END) AS Row_Count13
, COUNT(CASE WHEN [days] = '23' THEN 1 END) AS Row_Count14
, COUNT(CASE WHEN [days] = '123' THEN 1 END) AS Row_Count15
FROM dbo.campRegistration 
WHERE [days] IN ('1', '2', '3', '12', '23', '123')
OR shirtSize IN ('S', 'M', 'L', 'XL', 'XXL')
OR Staff = 'True'
OR paidFull = 'True'
OR campDate = @campDate
END

Pour plus d'informations sur dupliquer des scans de lire ce sujet.

J'apprécie un vrai sql génie!
Laisser de côté l'complet WHERE clause. C'est probablement plus rapide dans ce cas.
de Ruiter, peut-être, vous avez raison, mais nous ne pouvons pas le tester.

OriginalL'auteur Devart

----------------------------------Le Code C# --------------------------------------------

POUR INSÉRER

    cmd = new SqlCommand("sp_UserLogin", con);
cmd.CommandType = CommandType.StoredProcedure;
try
{
cmd.Parameters.AddWithValue("@op", "Insert");            
cmd.Parameters.AddWithValue("@UserName", userdetail.UserName);
cmd.Parameters.AddWithValue("@Password", userdetail.Password);
cmd.Parameters.AddWithValue("@ConfirPassword", userdetail.ConfirmPassword);
cmd.ExecuteNonQuery();
}

POUR SÉLECTIONNER

    cmd = new SqlCommand("sp_UserLogin", con);
cmd.CommandType = CommandType.StoredProcedure;
SqlDataReader dr;
try
{
cmd.Parameters.AddWithValue("@op", "Select");
dr = cmd.ExecuteReader();
}

--------------------------------Magasin De Procédure-----------------------------------

Create PROCEDURE [dbo].[sp_UserLogin] 
-- Add the parameters for the stored procedure here
@op varchar(50)='g',
@UserName varchar(50)=null,
@Password varchar(100)=null,
@ConfirPassword varchar(100)=null
AS
BEGIN
-- SET NOCOUNT ON added to prevent extra result sets from
-- interfering with SELECT statements.
if @op='Insert'
BEGIN
-- Insert statements for procedure here
insert into UserLogin values (@UserName,@Password,@ConfirPassword)
End
if @op='Update'
BEGIN
-- Insert statements for procedure here
Select * from UserLogin 
End
END

OriginalL'auteur user2925678

Vous devez vous connecter pour publier un commentaire.