Calling multiple stored procedures and Gridviews with 1 button click

Background: I'm converting an access run time that searches plans by zip code, plan type, and age.

So far, I have the main search down with the stored procedure get_zip_plan_age displaying correctly but am not sure if I'm calling the stored procedure get_lowest_female_insurance_rate correctly in the C# code-behind and how to write the code differently for get_lowest_female_rate which displays the lowest female rate for each age group vs get_zip_plan_age which displays all data.

Question:

• How do you call get_lowest_female_rate correctly in the code-behind? Is what I have correct?
• How would you write the stored procedure code for get_lowest_female_rate which displays the lowest female rate for each age group (65, 70, 75, 80)?

Here's a screenshot of the access runtime:

Here's my code for default.aspx.cs:

    protected void Search_Zip_Plan_Age_Button_Click(object sender, EventArgs e)
    {                          
        using (SqlConnection cn = new SqlConnection())
        {
            cn.ConnectionString = System.Configuration.ConfigurationManager.ConnectionStrings["PriceFinderConnectionString"].ToString();
            cn.Open();

            using (SqlCommand cmd = cn.CreateCommand())
            {
                cmd.CommandText = "get_zip_plan_age";                    
                cmd.CommandType = CommandType.StoredProcedure;

                cmd.CommandText = "get_lowest_female_rate";
                cmd.CommandType = CommandType.StoredProcedure;

                cmd.CommandText = "get_lowest_male_rate";
                cmd.CommandType = CommandType.StoredProcedure;

                cmd.CommandText = "get_carrier_info";
                cmd.CommandType = CommandType.StoredProcedure;


                SqlParameter parm = cmd.CreateParameter(); 
                parm.ParameterName = "@insur_age";
                parm.DbType = DbType.Int64;
                parm.Value = Convert.ToInt64(this.insur_age.Text);
                cmd.Parameters.Add(parm);

                parm = cmd.CreateParameter(); 
                parm.ParameterName = "@zip_code";
                parm.DbType = DbType.String;
                parm.Value = this.ZipCode.Text;
                cmd.Parameters.Add(parm);

                parm = cmd.CreateParameter(); 
                parm.ParameterName = "@plan_code";
                parm.DbType = DbType.String;
                parm.Value = this.PlanCode.Text;
                cmd.Parameters.Add(parm);

                SqlDataReader reader = cmd.ExecuteReader();
                Zip_Plan_Age_GridView.DataSource = reader;
                Zip_Plan_Age_GridView.DataBind();
            }
        }
    }

Here's the code for the stored procedure get_zip_plan_age that displays all data:

    ALTER PROCEDURE get_zip_plan_age 
-- Add the parameters for the stored procedure here
@zip_code nvarchar(16),
@plan_code nvarchar(16),
@insur_age int = 0 

    AS
    BEGIN
-- SET NOCOUNT ON added to prevent extra result sets from
-- interfering with SELECT statements.
SET NOCOUNT ON;

IF @insur_age > 0 
BEGIN 

    SELECT 

      [s开发者_如何学Pythontate_code], 
      [zip_code], 
      [female_value], 
      [male_value], 
      [carrier_name], 
      [update_date], 
      [insur_age], 
      [plan_code], 
      [spousal_discount] 

      FROM [state_zipcode_plans] 

      WHERE (([insur_age] = @insur_age) 
      AND ([zip_code] = @zip_code) 
      AND ([plan_code] = @plan_code)) 

      ORDER BY [male_value], [plan_code]

END  

    ELSE BEGIN

      SELECT 

      [state_code], 
      [zip_code], 
      [female_value], 
      [male_value], 
      [carrier_name], 
      [update_date], 
      [insur_age], 
      [plan_code], 
      [spousal_discount] 

      FROM [state_zipcode_plans] 

      WHERE (([zip_code] = @zip_code) 
      AND ([plan_code] = @plan_code)) 

      ORDER BY [male_value], [plan_code]

END
 END
 GO

In the method Search_Zip_Plan_Age_Button_Click you've set the cmd.CommandText and cmd.CommandType multiple times. In this case it should only be set only once - they would have values cmd.CommandText = "get_carrier_info"; cmd.CommandType = CommandType.StoredProcedure;

To make your code more maintainable consider separating out the creation of commands to a factory the command pattern.

In terms of the sp - I can only see the sp for get_zip_plan_age.

My recommendation would be: don't use a SqlDataReader if you want to bind stuff to a form that a user can look at / edit. This will keep a database connection open - potentially for a very long time!

If you're using forms to show data, I would use an ORM (Object-relational mapper) to get back a list of objects from your database - or if that's not what you want, then at least use a DataTable which gets the data and then allows you to work disconnected from the database up to the point where you actually need to update stuff (or read new data).

As for calling the stored proc:

• BIG bonus on using the using blocks!!
• I would try to open the connection as late as possible - it doesn't have to be open while you create parameters and stuff - only open it just before you need to read the data
• I would try to separate the UI code (reading the textboxes, binding to the grid) from the actual code to load the data - you might want to put this into a data access layer (separate class library) at some point - don't mix loading data with manipulating the UI all over the place!
• I would separate the individual calls to the individual stored procs - just have four methods, which you can call, when needed, with the params needed.

So I'd try to use something like this:

protected void Search_Zip_Plan_Age_Button_Click(object sender, EventArgs e)
{       
    string _connStr = ConfigurationManager.ConnectionStrings["PriceFinderConnectionString"].ConnectionString;

    DataTable data = LoadZipPlanAge(_connStr, .......);

    Zip_Plan_Age_GridView.DataSource = data;
    Zip_Plan_Age_GridView.DataBind();
}


protected DataTable LoadZipPlanAge(string connString, Int64 insurAge, string zipCode, string planCode)
{
    string storedProcName = "dbo.get_zip_plan_age";                    
    DataTable table = new DataTable();

    using (SqlConnection cn = new SqlConnection(connString))
    using (SqlCommand cmd = new SqlCommand(storedProcName, cn))
    {
       cmd.CommandType = CommandType.StoredProcedure;

       // create parameters
       cmd.Parameters.Add("@insur_age", SqlDbType.Int64).Value = int64Value;
       .......


       SqlDataAdapter dap = new SqlDataAdapter(cmd);
       dap.Fill(table);
    }

    return table;
}

Using the logic,

• If an Insurance Age is selected, only display the min Female rate for that age SELECTing the Top(1) Min Female Value WHERE the Age is the specified AGE
• If ALL is selected for Insurance Age, Display Lowest Female Rate by SELECTing the Top(1) Min Female Value, WHERE the Zip Code, Plan Code Matches the specified search values and the Male Rate is Greater than 0, Grouped by insur_age, male_value, carrier_name and UNIONed for each age for Each of the Age Groups 65, 70, 75, 80

Here's the stored procedure I used for getting the lowest female rate:

      ALTER PROCEDURE [dbo].[get_lowest_female_rate] 
-- Add the parameters for the stored procedure here
@zip_code nvarchar(16),
@plan_code nvarchar(16),
@insur_age int = 0 

      AS
      BEGIN

-- SET NOCOUNT ON added to prevent extra result sets from
-- interfering with SELECT statements.
SET NOCOUNT ON;

          -- Insert statements for procedure here

          -- If an Insurance Age is selected, only display the min Female rate for that age 

          IF @insur_age > 0 
          BEGIN 

    SELECT TOP(1)  
      [carrier_name], 
      [insur_age],
      MIN([female_value])

      FROM [state_zipcode_plans] 

      WHERE (([insur_age] = @insur_age) 
      AND ([zip_code] = @zip_code) 
      AND ([plan_code] = @plan_code)
      AND ([female_value] > 0)) 

      GROUP BY        
      [insur_age], [female_value], [carrier_name]

END  

        -- If ALL is selected for Insurance Age, Display Lowest Female Rate for Each of the Age Groups 65, 70, 75, 80 

        ELSE BEGIN

      SELECT TOP(1)           
      [carrier_name], 
      [insur_age],
      MIN([female_value])

      FROM [state_zipcode_plans]

      WHERE  (([zip_code] = @zip_code) 
      AND ([plan_code] = @plan_code)
      AND ([female_value] > 0)
      AND ([insur_age] = 65)) 

      GROUP BY        
      [insur_age], [female_value], [carrier_name]

        UNION 
      SELECT TOP(1)           
      [carrier_name], 
      [insur_age],
      MIN([female_value])

      FROM [state_zipcode_plans]

      WHERE  (([zip_code] = @zip_code) 
      AND ([plan_code] = @plan_code)
      AND ([female_value] > 0)
      AND ([insur_age] = 70)) 

      GROUP BY        
      [insur_age], [female_value], [carrier_name]

UNION 
      SELECT TOP(1)           
      [carrier_name], 
      [insur_age],
      MIN([female_value])

      FROM [state_zipcode_plans]

      WHERE  (([zip_code] = @zip_code) 
      AND ([plan_code] = @plan_code)
      AND ([female_value] > 0)
      AND ([insur_age] = 75)) 

      GROUP BY        
      [insur_age], [female_value], [carrier_name]

       UNION 
      SELECT TOP(1)           
      [carrier_name], 
      [insur_age],
      MIN([female_value])

      FROM [state_zipcode_plans]

      WHERE  (([zip_code] = @zip_code) 
      AND ([plan_code] = @plan_code)
      AND ([female_value] > 0)
      AND ([insur_age] = 80)) 

      GROUP BY        
      [insur_age], [female_value], [carrier_name]     

     END
   END