Confused about method overloading

I'm using .NET 4.0 with ASP.NET 4.0 and C# (would that be C# 4.0?).

I want to insert some data into my SQL Server database and I have a method like so to handle this:

public int InsertTrade(
    string symbol,
    stri开发者_如何学Gong tradeSetupId,
    int tradeTypeId,
    decimal lotsPerUnit,
    string chartTimeFrame,
    int tradeGrade,
    int executionGrade,
    int MFEPips,
    int MAEPips,
    decimal pctAccountRisked
    )
{
    SqlCommand cmd = new SqlCommand("usp_InsertTrade");
    cmd.Parameters.AddWithValue("@symbol", symbol);
    cmd.Parameters.AddWithValue("@pctAccountRisked", pctAccountRisked);
    cmd.Parameters.AddWithValue("@tradeSetupId", tradeSetupId);
    cmd.Parameters.AddWithValue("@lotsPerUnit", lotsPerUnit);
    cmd.Parameters.AddWithValue("@tfCode", chartTimeFrame);
    cmd.Parameters.AddWithValue("@MAEPips", MAEPips);
    cmd.Parameters.AddWithValue("@MFEPips", MFEPips);
    cmd.Parameters.AddWithValue("@tradeGrade", tradeGrade);
    cmd.Parameters.AddWithValue("@executionGrade", executionGrade);
    return (InsertData(cmd, "trade"));
}

There are several non-required fields: tradeGrade, executionGrade, MFEPips, MAEPips. The usp_InsertTrade stored procedure exposes these optional parameters as NULLable. What is the best way to code this in C#? I'm learning to program so it would be great if you could offer guidance on best practise.

Here are the stored procedure parameters for usp_InsertTrade:

CREATE procedure [dbo].[usp_InsertTrade]
@symbol char(6),
@tradeSetupId varchar(10),
@tradeTypeId int,
@lotsPerUnit decimal(18,1),
@chartTimeFrame varchar(5),
@tradeGrade smallint = NULL,
@executionGrade smallint = NULL,
@MFEPips int = NULL,
@MAEPips int = NULL,
@pctAccountRisked decimal(3,2)
AS

Thanks a lot.

UPDATE

I've changed my function so that the optional parameters are at the bottom. Like so:

public int InsertTrade(
    string symbol,
    string tradeSetupId,
    int tradeTypeId,
    decimal lotsPerUnit,
    string chartTimeFrame,
    decimal pctAccountRisked,
    int? tradeGrade,
    int? executionGrade,
    int? MFEPips,
    int? MAEPips
    )
{
    SqlCommand cmd = new SqlCommand("usp_InsertTrade");
    // required parameters
    cmd.Parameters.AddWithValue("@symbol", symbol);
    cmd.Parameters.AddWithValue("@tradeSetupId", tradeSetupId);
    cmd.Parameters.AddWithValue("@tradeTypeId", tradeTypeId);
    cmd.Parameters.AddWithValue("@lotsPerUnit", lotsPerUnit);
    cmd.Parameters.AddWithValue("@tfCode", chartTimeFrame);
    cmd.Parameters.AddWithValue("@pctAccountRisked", pctAccountRisked);

    // optional parameters
    if (MAEPips.HasValue)
        cmd.Parameters.AddWithValue("@MAEPips", MAEPips);
    if (MFEPips.HasValue)
        cmd.Parameters.AddWithValue("@MFEPips", MFEPips);
    if (tradeGrade.HasValue)
        cmd.Parameters.AddWithValue("@tradeGrade", tradeGrade);
    if (executionGrade.HasValue)
        cmd.Parameters.AddWithValue("@executionGrade", executionGrade);
    return (InsertData(cmd, "trade"));
}

When I call the function using this code:

DBUtil DB = new DBUtil();
int tradeId = DB.InsertTrade (
    ddlSymbols.SelectedValue,
    ddlTradeSetups.SelectedValue, 
    ddlTradeTypes.SelectedValue, 
    decimal.Parse(txtLotsPerUnit.Text),
    ddlTimeFrames.Text,
    decimal.Parse(txtAcctRisk.Text));

I get this error:

No overload for method 'InsertTrade' takes 6 arguments

---

With C# 4.0, you can use optional parameters in conjunction with nullable types:

public int InsertTrade(
    string symbol,
    string tradeSetupId,
    int tradeTypeId,
    decimal lotsPerUnit,
    string chartTimeFrame,
    decimal pctAccountRisked,
    int? tradeGrade = null,
    int? executionGrade = null,
    int? MFEPips = null,
    int? MAEPips = null
    )
{
    SqlCommand cmd = new SqlCommand("usp_InsertTrade");
    cmd.Parameters.AddWithValue("@symbol", symbol);
    cmd.Parameters.AddWithValue("@pctAccountRisked", pctAccountRisked);
    cmd.Parameters.AddWithValue("@tradeSetupId", tradeSetupId);
    cmd.Parameters.AddWithValue("@lotsPerUnit", lotsPerUnit);
    cmd.Parameters.AddWithValue("@tfCode", chartTimeFrame);
    if(MAEPips.HasValue)
        cmd.Parameters.AddWithValue("@MAEPips", MAEPips);
    if(MFEPips.HasValue)
        cmd.Parameters.AddWithValue("@MFEPips", MFEPips);
    if(tradeGrade.HasValue)
        cmd.Parameters.AddWithValue("@tradeGrade", tradeGrade);
    if(executionGrade.HasValue)
        cmd.Parameters.AddWithValue("@executionGrade", executionGrade);
    return (InsertData(cmd, "trade"));
}

With this many parameters, you may want to consider the introduce parameter object refactoring - it will make your code easier to read and modify in the future.

---

I would create a struct in order to encapsulate parameters.

Here are the viable ways I might think about:

1. Optional parameters. You can define the method with some optional parameters, using C# 4.0 syntax (this feature was already present in VB but recently added to C#). Drawback: you are constrained on how to use optional parameters. Only the last parameters can be made optional. I mean, if you have (name, address, phone) as parameters, in this order, you can't skip address and set name
2. Structure. As mentioned, it's my favourite way. You can set any null value, but you have to create a struct for each method
3. Define overloads: the worst technique is to define an overload for each combination of params, which is unfeasible with large parameters collection
4. Object array: feasible only if all parameters have different type. You can determine the parameter basing on the type of each entry in the object array
5. Dictionary<string,object>: another interesting method. Each entry is mapped with a key

Hope to have been of help

---

I would create a extension method to get rid of the repeated checks on the nullable variables. The extension method would look something like:

public static class SqlCommandExtensions
{
    public static void AddNullableInParameter<T>(this SqlCommand command, string columnName, Nullable<T> value) where T : struct
    {
        if (value.HasValue)
        {
            command.Parameters.AddWithValue(columnName, value.Value);
        }
    }
}

Now you can just write command.AddNullableInParameter("@yourParameter", YourNullableType); instead of all those if statements.

---

You could use nullable values in the parameters, and check for a value before adding the parameter, this way it will use the stored proc's default if the value is not supplied:

public int InsertTrade(
    ...
    int? executionGrade,
    ...
    )
{
    SqlCommand cmd = new SqlCommand("usp_InsertTrade");
    ...
    if(executionGrade.HasValue)
        cmd.Parameters.AddWithValue("@executionGrade", executionGrade);
    return (InsertData(cmd, "trade"));
}

---

See this link on nullable types: http://msdn.microsoft.com/en-us/library/1t3y8s4s(VS.80).aspx

In short, declare your function as:

public int InsertTrade(
    string symbol,
    string tradeSetupId,
    int tradeTypeId,
    decimal lotsPerUnit,
    string chartTimeFrame,
    int? tradeGrade,
    int? executionGrade,
    int? MFEPips,
    int? MAEPips,
    decimal pctAccountRisked
    )

---

another way could be using optional parameters

---

Sounds like you are using Visual Studio 2010, C# 4.0, so they now introduced optional parameters: http://msdn.microsoft.com/en-us/library/dd264739.aspx