Where to put SQL Parameters in a layered application?

Solarion

Solarion

Honorary Master
Joined
Nov 14, 2012
Messages
28,051
Reaction score
17,805
Business Layer?

I am aware that the Data Layer is a no no such as this:

Capture.jpg

So where do you put them guys?
 
Last edited:
In a aptly named namespace?

What do you mean this is a "no no"?
 
Also not sure why you think it's a 'no no' to do it that way?
 
What's happening there is fine.

Or:

You could pass through a List<Sqlparameter> as a parameter to your functions - then do a mycommand.AddRange(List) to add the parameters to the command.

I would even go as far as to have a completely generic function that returns only a datatable / dataset (depending on your requirements) - and then build the
ContactPersonCollection elsewhere.
 
Is it your concern that the method is too specific to qualify as part of the Data Layer ? I would make it more generic as previous poster suggests but even if you don't, in my opinion, your are not applying business logic or the such and can leave it in the data layer.

In production code you would also avoid prefixing a variable name with 'my' (SqlCommand myCommand), if the scope of the variable is limited and there is little to no chance of confusion with other variables I often name the variable after the type, hence SqlCommand sqlCommand, in a larger scope, say with variables of custom class type Entity I will use Entity consumer, Entity beneficiary and in rare cases even Entity consumerEntity or Entity beneficiaryEntity (mostly for readability and easily understanding the logic for other developers).
 
Last edited:
It's fine. You don't want to go too granular. Your function returns a contact person collection (very .NET 1 :wtf: ) and does it by using the criteria given. How it does it is nothing else's concern. Setting query parameters is part of the database operation - definitely wouldn't split it out. What happens if you swap that db operation with a rest call and the method signature was made to accept db parameters? With what you have there you can change the underlying operation and nothing consuming it will be need to change.

What I would change is the name of the function unless it is in a ContactPerson specific helper class. In other words, unless it is obvious what GetList is doing, change it to be more descriptive.

Make your life easier though and use Dapper. Seriously.
 
Last edited:
Seems fine to me. Couple of things

Use code block, not image.
Drop the "my" prefixes.
"tempList" is a horrible name. "result" is much better, and can be introduced as a coding "standard" for function return variables
 
_kabal_ said:
Seems fine to me. Couple of things

Use code block, not image.
Drop the "my" prefixes.
"tempList" is a horrible name. "result" is much better, and can be introduced as a coding "standard" for function return variables
Click to expand...

Agreed with this, seems like a bit of a copy+paste operation here. Newbie?
 
Hamster said:
In a aptly named namespace?

What do you mean this is a "no no"?
Click to expand...

I will come back to this just have to pop out to the shops quickly I see there are a lot of responses wow.

Some months back somebody said to me or I read somewhere that I am forbidden from putting the parameterized queries inside the data layer. This has led to some frustration since then because 1. I have no idea why I am not supposed to put them in there and 2. It makes perfect sense too.

I think I possibly listened to bad advice back then.

Anyway I will be back on this shortly.
 
Just for the record, that image is from a book. Not sure how old it is but fairly old I think.

I can't post any code blocks only images because mybroadband blocks me every time.
 
Hamster said:
If you use Dapper you can skip all that mapping crap

Code: 
List<Job> jobs = conn.Query<Job>("spGetJob");
Click to expand...

I think it's time to have a gander at Dapper. That looks pretty awesome!
 
That if statement of yours:

1. Use != instead of !(... == ...)
2. It's pointless because SqlParameters will never be null
 
Pho3nix said:
Linq? Dapper? EF?

All these available and using SqlConnection :(
Click to expand...
How would you use Dapper without SqlConnection. The extension methods are on the IDbConnection interface if I recall.
 
Hamster said:
How would you use Dapper without SqlConnection. The extension methods are on the IDbConnection interface if I recall.
Click to expand...

You are correct!
Point I was trying to make though is that there are easier ways to do what OP wants to do.

Albeit there is a lesson in learning it this way.

/goes to watch MLP
 
Pho3nix said:
You are correct!
Point I was trying to make though is that there are easier ways to do what OP wants to do.

Albeit there is a lesson in learning it this way.

/goes to watch MLP
Click to expand...

There may be an easier way but I don't mind the scenic route...sometimes.
 
You must log in or register to reply here.
Back
Top
Sign up to the MyBroadband newsletter
X