While leading projects, I came across situations where team provides high effort estimates when it comes to storing or retrieving the information into /from the data store. But then, when I understand, at high level, how we can store or retrieve data into / from database, I experienced that I could contribute to optimization of effort estimates provided. In this post, I shall explain, at high level, the communication architecture of storing and retrieving data using ADO.NET where ADO stands for Active Data Object.
From middle layer, we will first initiate Connection Object by specifying exact server location. Connection object establishes connection channel to the database store based on the server address and access permission set in it's attributes.
We will then take help of Data Adapter Object which connects to the target set of tables, stored procedures or views stored in the data store. If we want to store information into the database, we will pass on necessary parameters to the data adapter object so that according information can be stored.
To store information into the data store, we may use Data Set object by setting below parameters
Using the parameters of the Data Set object we can then perform below operations on the data store.- Header & Footer templates: to describe titles and rule lines
- Item templates:
- Data Source: Name of the data set
- Data text: Text property of the list item
- Data Value: To set the value property of the list item
- ADD: Dataset1.<<Table_name>>.Rows.ADD.<<insert_command_string >>
- DELETE: Dataset1.<<Table_name>>.Rows.DELETE.<< delete_command_string >>
- UPDATE: Dataset1.<<Table_name>>.Rows.UPDATE.<<update_command _string >>
To Retrieve information from the data store, we may simple reference Data Set object through our middle layer functions and retrieve the collection of rows and columns.
To store data, we may also use command object method that contain below high level steps.
- Open DB Connection: Connection to DB gets established.
- Create Command Object: It can be stored procedure with required input parameters or simple text of SQL statements that need to be executed on the data store end.
- Execute Command: The command object will be executed at the data store end. That is data is added / stored/ modified/ selected using this step. It all depends upon the nature of the Stored Procedure/ SQL text we refer to in our Command Object
- Close connection: We finally close our database connection once are done with our purpose for which we crated the channel connection.