One of the common ways to access data in a .NET application is to use the drag and drop TableAdapter tools or the new Entity Framework tools. But what if you want to write your data access code yourself?
There are many ways to access data in an application without using the drag and drop tools:
- Retrieve a DataTable using a stored procedure (example)
- Retrieve a DataTable using a SQL string (example)
- Access a DataReader using a stored procedure (example)
- Access a DataReader using a SQL string (example)
- Retrieve a populated business object using a DataContext
- Save data using a stored procedure (example)
- Save data using a SQL string (example)
- Retrieve a DataTable using Microsoft Access (example)
- Update a Microsoft Access database (example)
Data Provider Factory
- Retrieve a DataTable using any database (example)
Regardless of the technique you choose, you should consider writing the code in a separate project, often referred to as a data access component (DAC) or a data access layer (DAL). Placing this code in a separate project allows you to more readily reuse the code in any future solution. This separation is also good programming practice because it encapsulates all of your data access code in one place.
If you are a beginner with VB or C#, one of the most common practices for adding data access code in an application is to put the code directly in the Form Load or Selected Index Changed or Button Clicked events. The application then ends up with data access code and SQL statements sprinkled throughout the application. This creates a lot of repeated code and makes maintenance more difficult.
Even if you don’t want to create a separate project for your data access code, you should minimally put it in its own function so it can be handled in one place in your application.