multiple separate sql queries in one stored procedure

Question

I have need of many (16) different queries to get row count at the same time. I was running 16 different connections, but thought I could combine to just 1 stored procedure.

I am having trouble as it gives error on datareader (system out of range excpection) for this line:

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

Here is my 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()

Stored procedure:

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

Answer 1

Try to change your query on this -

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

For additional information about duplicate scans read this topic.

Answer 2

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

FOR INSERT

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

FOR SELECT

    cmd = new SqlCommand("sp_UserLogin", con);
    cmd.CommandType = CommandType.StoredProcedure;
    SqlDataReader dr;


    try
    {

        cmd.Parameters.AddWithValue("@op", "Select");
        dr = cmd.ExecuteReader();

     }

--------------------------------Store Procedure-----------------------------------

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