Exam 70-487: Accessing Data - How to choose the Appropriate Data Access Technology - ADO.NET?
It is virtually impossible nowadays to find a software application that does not make use of some form of data Access.
There are many ways to build data-centric applications and many technologies available for use. Microsoft provides quite a few:
- Entity Framework,
- WCF Data Services,
- Azure Cosmos DB
The "Accessing Data" objective covers 20-25% of the overall Exam 70-487, and it is important to understand the characteristics and distinctive of each of the above data access technologies. When to use one vs the other.
ADO evolved from an earlier Microsoft data interface, Remote Data Objects (RDO). RDO works with Microsoft's ODBC to access relational databases, but not nonrelational databases such as IBM's ISAM and VSAM.
Okay, don't expect questions on the exam about ADO, but for your own education, you can read more about ADO (ActiveX Data Objects) here.
ADO.NET handles the above 3 activities using two main components known as the .NET Framework data providers and the DataSet.
Connectionobject provides connectivity to a data source.
Commandobject enables access to database commands to return data, modify data, run stored procedures, and send or retrieve parameter information.
DataReaderprovides a high-performance stream of data from the data source.
DataAdapterprovides the bridge between the
DataSetobject and the data source.
Commandobjects to execute SQL commands at the data source to both load the
DataSetwith data and reconcile changes that were made to the data in the
DataSetback to the data source.
- Understand Query Parametization and do it to avoid being hacked by an injection attack. Even then, still validate your inputs at every level.
- Minimize unnecessary connections to the database, so if you open it, close it. This can be done through try/catch/finally or try/finally structures. You can also use the Using Statement which ensures the Dispose method is called on all IDisposable objects (even then, get used to closing all database connections explicitly in the Finally block).
- Use some of the Asynchronous methods of the SqlDataReader to provide rich feedback to your application.