Using LINQ to SQL in Visual Basic [Best Practice]

[Solarion]

I'm looking for best practice. I'm hoping to get some feedback from you guys on this topic. Please take a look at this project, specifically the Accessor.vb Class and how it's used for CRUD.

I can program C# too and I do for my job, however at the moment I am focused on VB.NET as I love both languages equally. Anyway please check it out and see make any recommendations.

http://www.codeproject.com/script/Articles/ViewDownloads.aspx?aid=26739

 

[Hamster]

Recommendation 1: Drop Link to SQL and use Dapper.

 

[Solarion]

Recommendation 1: Drop Link to SQL and use Dapper.

I'll look into it thanks Hamster.

 

[deweyzeph]

You should move your data access layer code into a separate class library project. Also, why aren't you using Entity Framework?

 

[DominionZA]

Recommendation 2: Use a mix. There are benefits to both sides. Good analysis here https://www.linkedin.com/pulse/dapper-vs-entity-framework-adonet-performance-munasinghe-mcts-

 

[Hamster]

Also, why aren't you using Entity Framework?

Because life is already full of enough crap

 

[FarligOpptreden]

What's wrong with good old ADO.NET? Try and find your stride in mapping data to your business logic and decide upon a framework from there. You'll realise soon enough NoSQL datastores are a better fit and install MongoDB in no time.

My current stack of choice is proprietary .NET MVC-like presentation tier (APS.NET MVC has become too bloated) feeding off Node.js/Express.js API tier that gets data from a sharded Mongo store. Requests seldom go over 60ms in a high transaction volume application (currently around 5 transactions per second, still in testing).

 

[scudsucker]

What's wrong with good old ADO.NET?

This is good advice.

you'll realise soon enough NoSQL datastores are a better fit and install MongoDB

This is poor advice.

Mongo is very good at very few things; for almost all use cases a relational DB may be slower but will be much better. And you can mitigate against the speed of read access by use of a good cache system.

MongoDB (like most, if not all, NoSQL DBs) have an extremely limited set of uses. In that range, they rock. Trying to force them to work outside that tiny range... is asking for trouble.

Also OP: drop VB.NET; it's a **** language. Focus on C#. If you ever need to switch to another language C# is a better place to be starting than VB

 

[Solarion]

This is good advice.



This is poor advice.

Mongo is very good at very few things; for almost all use cases a relational DB may be slower but will be much better. And you can mitigate against the speed of read access by use of a good cache system.

MongoDB (like most, if not all, NoSQL DBs) have an extremely limited set of uses. In that range, they rock. Trying to force them to work outside that tiny range... is asking for trouble.

Also OP: drop VB.NET; it's a **** language. Focus on C#. If you ever need to switch to another language C# is a better place to be starting than VB

Yeah I'm sticking with VB thanks. My last job with VB was a top notch salary and I got to see first hand just what VB is capable of. I'm looking for ways to improve myself and my knowledge of it, not dump it in the bin because C# is the flavor of the day. I am not a web developer by the way and VB.NET/C# have both served me equally well in programming.

 

[FarligOpptreden]

This is poor advice.

Mongo is very good at very few things; for almost all use cases a relational DB may be slower but will be much better. And you can mitigate against the speed of read access by use of a good cache system.

MongoDB (like most, if not all, NoSQL DBs) have an extremely limited set of uses. In that range, they rock. Trying to force them to work outside that tiny range... is asking for trouble.

Also OP: drop VB.NET; it's a **** language. Focus on C#. If you ever need to switch to another language C# is a better place to be starting than VB

It was meant in slight jest, you know?

I agree that Mongo (and NoSQL) has a place and that not all systems are equal. When you're working with complex, dynamic, non-relational schemas you can't do better than Mongo - especially for workflow-type systems where tracking boatloads of data that can vary at every step.

 

[MrGray]

LINQ to SQL was kind of superseded by LINQ to Entities (a.k.a Entity Framework). Great for strong typing your DB but in practice it is vile for large recordsets with any level of joining. Been full cycle on many projects and ended up reverting to ADO.NET or a mixture of ADO.NET and EF for performance where larger tables or more complex code side queries are involved. Typically you find it all works wonderfully until you test with real data.

EF is definitely not workable in any high volume transaction processing system. The other practical issue with EF is you quickly have to become an expert at fixing borked data models especially if you opt for database first instead of code first (just don't!). At the end of the day, plain old ADO.NET still works out faster to develop and runs faster. There are proprietary ORMs that are apparently quite adept at handling those issues so you might want to experiment with those instead if you're sold on the idea, but sometimes implementing LINQ/EF is just a time wasting overkill for many projects where just using ADO.NET achieves what you need to in half the time.

 

[Necropolis]

Also OP: drop VB.NET; it's a **** language. Focus on C#. If you ever need to switch to another language C# is a better place to be starting than VB

The only difference between VB.Net and C# is syntax - I've never understood why people hate VB so much - sure it's a bit more verbose - but that's it.

 

[Solarion]

LINQ to SQL was kind of superseded by LINQ to Entities (a.k.a Entity Framework). Great for strong typing your DB but in practice it is vile for large recordsets with any level of joining. Been full cycle on many projects and ended up reverting to ADO.NET or a mixture of ADO.NET and EF for performance where larger tables or more complex code side queries are involved. Typically you find it all works wonderfully until you test with real data.

EF is definitely not workable in any high volume transaction processing system. The other practical issue with EF is you quickly have to become an expert at fixing borked data models especially if you opt for database first instead of code first (just don't!). At the end of the day, plain old ADO.NET still works out faster to develop and runs faster. There are proprietary ORMs that are apparently quite adept at handling those issues so you might want to experiment with those instead if you're sold on the idea, but sometimes implementing LINQ/EF is just a time wasting overkill for many projects where just using ADO.NET achieves what you need to in half the time.

There you go! +1

The Bank I worked for I programmed an application to traverse hundreds of thousands of banking records a day looking for anomalies related to money fraud and terrorism. Because of the sheer volume I went with a the ADO.NET approach to keep things as straight forward as possible. Never had a problem and she ran daily like a boss.

 

[MrGray]

Sometimes people over complicate an application. I've see people create a full Customer class just for one form. I'm more inclined to simply have a DB class that will handle most if not all of the CRUD. Just saying.

I don't know what it is but the level of over-engineered systems I've been getting to work on recently is insane. Recently took over a project with a massive solution containing something like 50+ projects. The data layer was modelled to the nth degree with each field in it's own class file, as well as every database operation. Multi-tiered with WCF and dependency injection and using customised Log4Net configuration (in fact every kind of customised/overridden modification you can think of). It contains whole projects dedicated to just interface classes. And what does it do? A couple of screens that maintain a small database of CV's. As a result it takes about a day to change one field. NASA level engineering for something that you could belt out in a day using straightforward tools. I'm starting to think devs are doing this to justify billing time.

 

[Solarion]

I don't know what it is but the level of over-engineered systems I've been getting to work on recently is insane. Recently took over a project with a massive solution containing something like 50+ projects. The data layer was modelled to the nth degree with each field in it's own class file, as well as every database operation. Multi-tiered with WCF and dependency injection and using customised Log4Net configuration (in fact every kind of customised/overridden modification you can think of). It contains whole projects dedicated to just interface classes. And what does it do? A couple of screens that maintain a small database of CV's. As a result it takes about a day to change one field. NASA level engineering for something that you could belt out in a day using straightforward tools. I'm starting to think devs are doing this to justify billing time.

Yup this. One application that the bank used was a full blow n-tier entity framework everything classed to the maximum. The application had two Win forms.

I was asked to make some changes to it, after staring in amazement at this work of art if that's what you want to call it, I scrapped the idea of trawling through layer after layer trying to add a few fields to the Clients Form and re-wrote the whole thing in 3 hours. Two forms, one data class to handle all the Crud, simple ADO.NET. Done.

You are right, they do this to by time with IT managers who don't know if an application takes 1 day or 1 month to build.

 

[Necropolis]

I don't know what it is but the level of over-engineered systems I've been getting to work on recently is insane. Recently took over a project with a massive solution containing something like 50+ projects. The data layer was modelled to the nth degree with each field in it's own class file, as well as every database operation. Multi-tiered with WCF and dependency injection and using customised Log4Net configuration (in fact every kind of customised/overridden modification you can think of). It contains whole projects dedicated to just interface classes. And what does it do? A couple of screens that maintain a small database of CV's. As a result it takes about a day to change one field. NASA level engineering for something that you could belt out in a day using straightforward tools. I'm starting to think devs are doing this to justify billing time.

I've recently had to start working with some Java EE applications - and this sort of over engineering seems to be the norm.

It's crazy ridiculous.

 

[freddster]

I've recently had to start working with some Java EE applications - and this sort of over engineering seems to be the norm.

It's crazy ridiculous.

I worked a bit on a project using EF, C# on the datagrid. Simple windows apps. Good grief. Nope, that sucks. There is much better out there. But MS can market, and being marketable doesn't necessarily mean its the best. Those databindings and crap...

 

[DA-LION-619]

I've recently had to start working with some Java EE applications - and this sort of over engineering seems to be the norm.

It's crazy ridiculous.

But but separation of concerns

 

[DA-LION-619]

What's wrong with good old ADO.NET? Try and find your stride in mapping data to your business logic and decide upon a framework from there. You'll realise soon enough NoSQL datastores are a better fit and install MongoDB in no time.

My current stack of choice is proprietary .NET MVC-like presentation tier (ASP.NET MVC has become too bloated) feeding off Node.js/Express.js API tier that gets data from a sharded Mongo store. Requests seldom go over 60ms in a high transaction volume application (currently around 5 transactions per second, still in testing).

Time to try ASP.Net Core.

 

[me_]

Yup this. One application that the bank used was a full blow n-tier entity framework everything classed to the maximum. The application had two Win forms.

I was asked to make some changes to it, after staring in amazement at this work of art if that's what you want to call it, I scrapped the idea of trawling through layer after layer trying to add a few fields to the Clients Form and re-wrote the whole thing in 3 hours. Two forms, one data class to handle all the Crud, simple ADO.NET. Done.

You are right, they do this to by time with IT managers who don't know if an application takes 1 day or 1 month to build.

Usually in banking N-Tier is preferred over straight thick client to database because of it's superior security. With classic client-server apps, there is always the issue about securing the database connection. Either you can use Windows auth - in which case the user can bypass the front-end and edit anything in the DB or you can use a dedicated SQL account, but the password needs to be encrypted and sometimes it's even necessary to enable SSL on SQL to prevent the password from being sniffed.
All I'm saying is are you sure in your gusto to simplify things, you didn't just remove the entire security layer?