Good Coding Practices: Basic Data Handling

7.1 General Database Access

Database Access should be done through a predefined class and Developer must create this class in his project or include the pre-defined component for same if available.

The class is expected to hold fully documented functions that are capable of handling data access through queries, stored procedures and UDFs.

All developers must make sure at there part that connection should be opened only when required and should be closed and objects disposed when the context ends.


7.2 Data Fetching and Display
While displaying data, following things must be taken care of…

NULL should be explicitly handled while displaying field.
The condition that no rows are present should be explicitly handled and a proper message must appear stating that no data is present.
Query should be optimized in such a way that only relevant fields are fetched and only relevant rows are retrieved from database.
Developer should try to create the query in Query Analyzer as it helps in creating more efficient queries.


7.3 Data Insertion and Updating
While inserting or updating data in database, following things must be taken care of…

1. Data of correct data-type is sent for insertion or updation to avoid any error
2. Single quote or other terminating character must be explicitly replaced.
e.g.: variable = replace(variable,”’”,”’’”) (replacing single quote with two single quoted for MS-SQL)
3. The insert query must specify all field names explicitly (e.g. insert into tablename(fieldname1,fieldname2) values(1,’2’) )

7.4 Schema and Database Structure
While creating database, following points should be kept in mind…

Naming Conventions should be strictly followed.
The Database should be partially normalized using the star normalization structure
Proper constraints like Primary Key and Default value should be specified
Data Size should be in such a manner that space is not wasted and objective is also fulfilled

7.5 Data Transfer
While transferring data from one place to another, please observe the following procedure. It should be noted that the following procedure is must as it helps in bypassing several possibilities of errors and is very fast also

Create a Script of Database with all Triggers, Stored Procedures, Functions, Views, Users and Roles etc.
Execute the script at destination. (If database is to be created with a different name, manual changes can be done to replace the old name with new name in script)
Use normal Data Transformation Service or any available Import/Export utility to transfer data from source to destination (without structure)