ASP.net c# database connectivity getting into a mess

Question

I'm just starting development on a new website and am trying to do it correctly, that is, with a DAL and not with queries in code which I had before.

I understand the principles of it all, but I am finding this DAL with table adapters and wizards that do too much to be really messy to work with. Does anyone have any recommendations on what I should be using instead and a link to a tutorial on how to get going quickly with it?

Answer 1

I have a code generation tool that I've posted on my blog (including the source code). Data Access Layer CodeGen Essentially it will produces the large portion of the DAL code for you. That is 100% of the code for "Gets" and the command parameters and assignments etc. for Insert, Update, Delete stored procedures.

The code generation uses ADO.NET Core (and nothing else). You can just take a look at the code so you get a sense of how you should write your code (as a learning tool).

I hope it helps.

Answer 2

I would suggest you consider a lightweight and easy to use ORM such as Linq to SQL. The company I work for is a $2.5B solar manufacturing company and we use L2S as the foundation for the next generation of our manufacturing applications. It allows us to do all our queries in Linq (strongly type queries - huge benefit) and it's very easy to work with.

Answer 3

Just as with all other things: keep it simple. If you want to write a DAL yourself, then only write the code you need to perform database operations.

public class MyDal
{
  private SqlConnection _connection;
  public MyDal() 
  {
    _connection = new SqlConnection("connection string here");

  }
  public DataSet GetSomeData()
  {
    // write the code that reads from the database and parses the data in a DataSet
  }
}

Of course, if you want to, there are numerous good solutions that prevent you from having to write your own DAL. Personally, I like Castle ActiveRecord, since it's that simple. You just write your data objects and give them some attributes, and ActiveRecord takes care of all database communication.

If you are working on bigger projects, you might investigate in other solutions, ORMs, like Microsoft Entity Framework (EF), etc.

Answer 4

If you have the option, I would suggest that you go with .NET 4.0 and look at building your site using WebMatrix Check out the Thoughts on WebMatrix post by Rob Conery for more details on using WebMatix and the Data Access strategy that it employs.

Answer 5

Building your own is useful exercise - it'll give you an awareness of what is involved.

As a first pass, why not roll your own entities and data access with a combination of Codesmith and some common DB routines for doing CRUD operations? If your app isn't massive, this will probably be all you need.

Moving on, LINQ is probably going to be the way to go with this stuff in future, but of course is language-specific.