2

I'm really late to the .Net game and struggling to learn ADO.Net. I prefer to learn how to do data access the "right way". Somewhere I've picked up on the idea that it's considered superior to manually code your own Connections, Data Adapters, DataSets, DataTables, and even command statements for updating, adding, and deleting rather than using Visual Studios data wizard. I understand from my reading that there are some things you can only do by writing your own command statements but it isn't completely clear to me what that might be.

Should I always code my own connections, data adapters, datasets and datatables? What about my update, insert, and delete command statements? How do I know when I should code those manually?

Improve this question
3
  • Similar question: Should I Use the Data Wizard in Visual Studio? Commented Mar 11, 2011 at 3:04
  • Which version of .NET are you targeting? Commented Mar 11, 2011 at 3:46
  • Visual Studio 2008 targeting .Net 3.5. Sorry my response is so late. Commented Mar 16, 2011 at 1:04

5 Answers 5

Reset to default
2

There is no right or wrong way. However I would suggest you first do things the "hard way" in that you write your own code for each of the data access routines you need. Of course that would mean you'll also need to know and understand SQL. Eventually you could use/build tools that generate all of your code just the way you need it.

Preferably you'll use stored procedures instead of SQL statements in code, because stored procedures provide an additional level of abstraction, abstracting your database schema from even your data layer and of course your business layer.

I'd used ADO.NET core (that is writing your own code for data access and such). I'd use DataSets/DataTable (if you have to) purely as in-memory data structures without using them to do automatic updates/deletes and the like. Stick to DataReaders to the extent possible converting them over to DTOs (for data retrieval methods). For data modification methods, your data layer should get DTOs as parameters (or simple data types as parameters if there are just one or two).

Personally I use tools to generate the data access layer code that uses ADO.NET core (and not EF or LINQ2SQL and such). That is my personal preference and depending on the size of your application it goes a very long way in towards performance as well as needing to have in-depth knowledge of only two things. Your database and SQL and C# code without also having to learn about the nuances of abstraction layers and specialized languages (in some cases).

In large projects (and teams) leaving the database schema and stored procedures to people specialized in that area becomes a necessity and requirement and in those cases using ADO.NET core also becomes a requirement.

On my blog I have posted an article where in I introduce a tool that generates all of the code. The tool and source code are available for download. The tool also generates code for strongly typed datareaders. That is under the covers you're using a DataReader while in code it looks/feels like a DTO in terms of strongly typed properties.

Data Access Layer CodeGen

DataReader Wrappers - TypeSafe

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

2 Comments

I'm not convinced of your second paragraph. I'm reading some other places that it is preferable to use some type of abstraction layer to avoid having business logic in stored procedures. However, it seems this approach would have negative performance implications.
@HK1 the abstraction is purely for the data layer and not business layer. Stored procedures provide an abstraction between your tables and relationships allowing you to change the schema without impacting the data layer and the rest of the system.
1

in my own experience is preferred to always use hard code instead using smart control wizard.

Improve this answer

Comments

1

I think you should learn how its done under the covers first and then pick your own abstraction layer of which there are many.

Improve this answer

Comments

1

LINQ to SQL does a great job of automating common Db tasks. All your basic CRUD (Create,Read,Update,Delete) operations will be much easier to code by using a DataContext dbml file. The code is much easier to write, does not rely on strings, is compatible with other ADO.NET commands (You can execute a direct DbCommand against your DataContext, and it is more highly optimized than anything most people will write (Especially a beginner!). You will save yourself a whole lot of time by using something like LINQ to SQL or another ORM. Unless your objective is pure learning, you would be best off by creating a working DataContext, and analyzing the source to see how it is working instead of teaching yourself ADO.NET. The fact that you are at a point where you need to ask this question, probably indicates that you will not add value to your application by writing your own boiler plate DB access code.

It looks like a lot of people are recommending that you hard code your DAL first, before you use an ORM like LINQ to SQL. I would just like to point out that the logic involved in this line of thinking would necessitate that we also learn to code with IL before writing C# code, build a computer before we use one, and sail across the ocean before we take an international air plane.

Improve this answer

5 Comments

I'd go the opposite way - and suggest using Entity Framework instead of LINQ to SQL.
@John Saunders, sure - why not. I thought LINQ to SQL was easier to pick up, but the point I'm trying to make is that writing SQL queries is becoming a less relevant aspect of developing a functional application. It's certainly important for many objectives, but there's no reason a beginner should spend the bulk of his time learning to do something that is now, for the most part, automated far beyond what he will be capable of producing.
I would say that L2S is simpler because it has fewer features. It always maps one-to-one to the database, for instance.
I like the explanation you added in bold. It addresses part of the issue I'm battling here. A lot of programmers (including myself) are idealists and sometimes we lose site of just getting the job done.
@HK1, Yep. Then we learn the hard way when we have a deadline in a week and we're only almost done implementing the framework... Been there
0

There's not really going to be a black-and-white answer for this, but in my experience, I've always been better off coding my own stuff. This has largely been because I'm just an anal-retentive obsessive-compulsive control freak, and I just don't trust wizards to write code the way I want it written. I'm sure that many people agree with me, just as I'm sure that many people disagree with me.

The fact that OR/Ms exist is plenty of proof to prove that you don't always need to roll your own code. The fact that it's not mandatory is also proof that you aren't compelled to use it.

Do whatever feels right and meets the needs of your solution and its time and budgetary constraints.

Improve this answer

Comments

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Start asking to get answers

Find the answer to your question by asking.

Ask question

Explore related questions

See similar questions with these tags.