Where to put SQL Parameters in a layered application?

[Solarion]

Business Layer?

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



So where do you put them guys?

 

[Hamster]

In a aptly named namespace?

What do you mean this is a "no no"?

 

[C4Cat]

Also not sure why you think it's a 'no no' to do it that way?

 

[Necropolis]

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.

 

[etienne_marais]

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).

 

[Hamster]

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.

 

[_kabal_]

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

 

[Shi]

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

Agreed with this, seems like a bit of a copy+paste operation here. Newbie?

 

[Solarion]

In a aptly named namespace?

What do you mean this is a "no no"?

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.

 

[Solarion]

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]

Just for the record, that image is from a book. Not sure how old it is but fairly old I think.

That explains the *Collection part.

 

[Solarion]

That explains the *Collection part.

This is my own one I am currently using.

 

[Hamster]

This is my own one I am currently using.

If you use Dapper you can skip all that mapping crap

List<Job> jobs = conn.Query<Job>("spGetJob");

 

[Solarion]

If you use Dapper you can skip all that mapping crap

List<Job> jobs = conn.Query<Job>("spGetJob");

I think it's time to have a gander at Dapper. That looks pretty awesome!

 

[Hamster]

That if statement of yours:

1. Use != instead of !(... == ...)
2. It's pointless because SqlParameters will never be null

 

[Pho3nix]

Linq? Dapper? EF?

All these available and using SqlConnection

 

[Hamster]

Linq? Dapper? EF?

All these available and using SqlConnection

How would you use Dapper without SqlConnection. The extension methods are on the IDbConnection interface if I recall.

 

[Pho3nix]

How would you use Dapper without SqlConnection. The extension methods are on the IDbConnection interface if I recall.

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

 

[Solarion]

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

There may be an easier way but I don't mind the scenic route...sometimes.

 

[Solarion]

Is Dapper only for ASP.NET?

Also if someone could suggest me a resource to get started else I am just going to smash codeproject and see what I can find.

eg. https://www.codeproject.com/articles/212274/a-look-at-dapper-net