To date, the three most common execution commands I have used are ExecuteReader, ExecuteScalar, and ExecuteNonQuery.
Reference the following detail for examples on using the three common SQL execution commands.
As an example, the following will address the database tables handling information with the ExecuteReader method.
The SQL Management Studio shows A: Table List, B: Command Pane, C: Results Pane. Referencing Detail 2, “A” is the list of tables from the selected (B) database to be addressed in the command pane (C) and upon execution the data is displayed in the results pane (D).
In a data intensive application, the program should not be pulling all the data all the time. Notice in area C, there is the “isCurrent” column. Unless otherwise requested of the program, database information should be requested to pull only necessary data to reduce execution times. “isCurrent” is used often in most of the routines addressing the project database table. Notice there are more than 1500 records shown in the lower right of the results pane. Operators generally are only interested in the current project set. The remaining projects are often used as references for future projects or if there are project problems, possibly a failed instrument or device, specific information can be recovered.
Example of information differences may be instrument failure requiring manufacturer assistance. Please review the post under the Concepts of Material Handling – RMA’s.
Example concept: A requisition has been created that is a list of material to be purchased. The requisition is one of many in the project. The requisition has been reviewed for supplier, component accuracy (Manufacturer, Model, and Description), and cost.
Example 1 – Collecting Projects
Task: Retrieve all projects in the database for selection.
Command: SELECT * FROM dbo.ProjectDataSheet
Though this command returns all the information from the ProjectDataSheet database table, some of the projects may not be current. In the ProjectDataSheet is the column isCurrent. The command line could be modified with a WHERE clause to limit the quantity of records returned. Column isCurrent in this context implies the project is still in progress and has not reached the end of project completion or warranty period.
Command: SELECT * FROM dbo.ProjectDataSheet WHERE isCurrent = 1
Note that “1” is the Boolean equivalent of true
Extending the task, possibly all the columns are not to be returned. Recall that “*” is the symbol used to request all columns in the row are to be returned. Replacing the “*” with specific columns titles will instruct the command to limit the returned data further.
Command: SELECT ProjectNo FROM dbo.ProjectDataSheet WHERE isCurrent = 1
The updated command line will return only the current project numbers. Continuing, note that the tables are linked with the Primary Key (PK) and Foreign Key (FK). Referencing the PK/FK relationships will makes for more convenient database communications.
Command: SELECT ProjectNoID, ProjectNo FROM dbo.ProjectDataSheet WHERE isCurrent = 1
This command now returns the PK and the Project number. Now, if the project number is collected as projects are acquired, there is no guarantee the project number was entered into the database in sequential order. Also, there is the option to collect the projects in Ascending or Descending order. An ORDER BY clause is added at the end of the command line to organize the information in a desired order. The ORDER BY clause can contain more than one column, in order of importance. The ORDER BY clause is ORDER BY Column1, then by Column2, then by Column3, and so on. Mostly for the project number in this database I order by ProjectNo DESC (descending).
ProjectNoID – A column specified as the table identity, seeded with “1” to increment sequentially as records are generated, and established as the Primary Key. Other tables using the ProjectNoID column and tied to the origin table will note the column as a foreign key. See Table 1‘s images of ProjectDataSheet and Requisitions tables showing the PK and FK positions. Recall the gold key icon denotes the primary key in the table.
ProjectNo – A non-sequential column where the project number is added. In a bidding environment, awarded projects will not be processed in down-line software packages in a sequential order. Bid projects have factors such as delays, cancellations, rebidding, and long awarded set-up procedures. Sometimes an awarded project may even require a section to be removed or added. Additionally, if a bond is required the processing time can be extended.
Command: SELECT ProjectNoID, ProjectNo FROM dbo.ProjectDataSheet WHERE isCurrent = 1 ORDER BY ProjectNo DESC
The data collected in last command will collect only the current projects with there PK in descending order. The following document insert shows a Visual Studio combobox subroutine. Within the subroutine is the cmd.CommandText statement using the above SQL select command. Since the goal is to return a list of current (isCurrent) projects along with the PK, the SQL communications will use the sqlDataReader command structure.
Considerations when using the Combobox tool. 1) Use a title, like an instruction, for the operator, 2) Always clear the Combobox contents and any collections associated with the Combobox upon each call to populate or repopulate the list.
SQL commands were tested in the SQL Server Management Studio using the Query window. If using data in SQL commands in a windows form, the project number can be added to a ComboBox and the project number ID (PK) to a collection. When clicking on the selected ComboBox project number, the index of the ComboBox can be used to collect the primary key from collection.
A computer science professor years ago instructed the class to define variables outside of the subroutines and functions and not inside, so the variables are defined at build time and not during run time. I am not sure this guideline still holds true today with the advancement of computer software, but I have stayed with the advice ever since.
The following function is used in the preceding subroutine. The function has been used at least ninety-nine (99) times in one program which reduces repeating code blocks, and keeps the handling of integers (for example) consistent.
Looking at the above ReturnSQLInteger function, there appears the possibility the function could cause a program crash if the data reader passes in a non-numeric value. Though this could be considered problematic, the table columns handled by the routine are defined as integer and unless the programmer generates an error, there should not be a problem with the function’s data handling. Though there is always the possibly a user will attempt to enter a non-numeric value in numeric fields, adding a catch statement prior to updating the database is useful.
My method of operation is to handle the data error possibility, such as passing a string into an integer field, during data entry and then have reduced data error when reading the database or handling the data in the program. Mostly the errors are caught while debugging. The greatest culprit to errors has been using the cut-copy-paste functions available on the software.
Detail 6 is part of a Case tree handling data edits from a DataGridView. The IsNumeric built in function is used to affirm the operator has entered valid data. I prefer to handle data integrity at the beginning of logic flow that towards the end. The function call UpdateDatabaseDouble will crash if the function attempts to update a database column of value type Float with a string which I consider at the end of the logic flow. Note in the Else of the If-Then-Else-End If statement a Message Box MsgBox will pop up a message to the operator about the data entry error.