1

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, string 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
Improve this question
3
  • you're missing the = null in the parameter declaration - this is causing your error. Commented Nov 8, 2010 at 11:40
  • @Paddy - thanks, need coffee... Commented Nov 8, 2010 at 11:44
  • Also, I think you might need to cast the null to your nullable type. For example; int? tradeGrade = (int?)null. Commented Nov 8, 2010 at 11:46

7 Answers 7

Reset to default
5

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.

Improve this answer
Sign up to request clarification or add additional context in comments.

3 Comments

Thanks, please could you look at the UPDATE section of my question? I get an error, and I'm not sure why.
@Mark Allison - you have made your parameters nullable only (int? MFEPips,). I have made them optional as well (int? MFEPips = null,). See the extra = null?
thanks, works great now. I'll have to go and do some research on the parameter object thing you talked about.
3

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

Improve this answer

1 Comment

Wow thanks, I didn't know there were so many ways to skin a cat!
1

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.

Improve this answer

Comments

0

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")); }
Improve this answer

3 Comments

Surely he has no need to check whether it has a value as it will be passed straight through to the Stored Procedure, null or otherwise?
@Moo-Juice a SQL NULL is a different thing from a C# null. To mention a SQL NULL in C#, you would need to say DBNull.Value. Here, since the default in the sproc definition is NULL as we want, the easiest thing to do is just not say anything.
@Moo-Juice - the desticntion is that by not even adding the parameter, it uses whatever the Stored Proc developer chose as the default - which is not necessarily null.
0

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 )
Improve this answer

Comments

0

another way could be using optional parameters

Improve this answer

Comments

0

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

Improve this answer

Comments

Start asking to get answers

Find the answer to your question by asking.

Ask question

Explore related questions

See similar questions with these tags.