What are the important points when developing a data access app with ADO.Net?

• Always try to use the base interfaces for connection, command, data reader and other objects.
• Always try to use the SqlClient, SqlServerCe and OracleClient to connect with the Sql Server, Sql Server CE and Oracle Database servers as they are specialized and optimized for the specific database servers.
• Still remember to reference the data provider specific objects (SqlConnection, OracleCommand) to reference with the base interface (IDbConnection, IDbCommand)

C# Version

	IDbConnection conn = new SqlConnection();
	...
	IDbCommand cmd = new OracleCommand();

VB.Net Version

	Dim conn As IDbConnection 
	conn = New SqlConnection();
	...
	Dim cmd As IDbCommand 
	cmd = new OracleCommand();

• Do not write the connection string in your code as it may change. Either write it in a text file or an xml file and read it on the application startup. For security purposes, you may also write the encrypted connection string in the text/xml file
• Try to use the stored procedures wherever possible especially when you are to write a series of queries whose individual results are not required to be used in the code in between this series of queries.
• Do not use the complex queries in the source code. If the query is getting complex, try to make the views inside the database server and use the views instead.
• Practice using the transactions when it makes sense, especially with error handling codes
• Put special consideration in the error handling code. The database operation may fail due to various reasons such as invalid connection string, invalid table/field name in the query, database server failure, connection failure, too many connections on the server or the server busy, invalid query, etc You need to consider all these while writing the code for error handling.
• Using Visual Studio.Net’s debugger is a very good and useful practice to find the possible errors. Remember, Ado.Net exception messages are not much useful (or quite vague) in general for debugging; hence the use of watch and quick watch debugger windows is extremely useful and helpful in when debugging the code.
• When using dataset and disconnected architecture, we don’t update the data source (by calling DataAdapter’s Update() method and DataSet’s AcceptChanges() method) for each update. Instead we make the changes local and update all these changes later as a batch. This provides optimized use of network bandwidth. BUT, this off course is not a better option when multiple users are updating the same database. When changes are not to be done locally and need to be reflected at database server at the same time, it is preferred to use the connected oriented environment for all the changes (UPDATE, INSERT and DELETE) by calling the ExecuteNonQuery() method of your command (SqlCommand or OleDbCommand) object.
• Disconnected data access is suited most to read only services. In common practice clients are often interested in reading and displaying data. In this type of situation, the disconnected data access excels as it fetches the whole data in a single go and store it in the local buffer (dataset). This local storage of data eliminates the need of staying connecting to the database and fetching single record at a time. On the down side, disconnected data access architecture is not designed to be used in the networked environment where multiple users are updating data simultaneously and each of them needs to be aware of current state of database at any time (e.g., Airline Reservation System).