As you have just seen, the ExecuteReader() method extracts a data reader object that allows you to examine the results of a SQL Select statement using a forward-only, read-only flow of information. However, when you wish to submit SQL commands that result in the modification of a given table, you will call the ExecuteNonQuery() method of your command object. This single method will perform inserts, updates, and deletes based on the format of your command text.
Note Technically speaking, a nonquery is a SQL statement that does not return a result set. Thus, Select statements are queries, while Insert, Update, and Delete statements are not. Given this, ExecuteNonQuery() returns an Integer that represents the number of rows affected, not a new set of records. You can also use ExecuteNonQuery() to execute catalog commands such as CREATE TABLE and DROP TABLE, plus DCL commands such as GRANT PERMISSION and REVOKE PERMISSION.
To illustrate how to modify an existing database using nothing more than a call to ExecuteNonQuery(), your next goal is to build a custom data access library that will encapsulate the process of operating upon the AutoLot database. In a production-level environment, your ADO.NET logic will almost always be isolated to a .NET *.dll assembly for one simple reason: code reuse! The first examples of this chapter did not do so, just to keep focused on the task at hand; however, as you might imagine, it would be a waste of time to author the same connection logic, the same data reading logic, and the same command logic for every application that needs to interact with the AutoLot database.
By isolating data access logic to a .NET code library, multiple applications using any sort of front end (console based, desktop based, web based, etc.) can reference the library at hand in a language-independent manner. Thus, if you author your data library using VB, other .NET programmers would be able to build a UI in their language of choice (C#, C++/CLI, etc.).
In this chapter, our data library (AutoLotDAL.dll) will contain a single namespace (AutoLotConnectedLayer) that interacts with AutoLot using the connected layer. The next chapter will add a new namespace (AutoLotDisconnectionLayer) to this same *.dll that contains types to communicate with AutoLot using the disconnected layer. This library will then be used by numerous applications over the remainder of the text.
To begin, create a new VB Class Library project named AutoLotDAL (short for AutoLot Data Access Layer) and rename your initial VB code file to AutoLotConnDAL.vb. Next, define a namespace scope named AutoLotConnectedLayer and change the name of your initial class to InventoryDAL, as this class will define various members to interact with the Inventory table of the AutoLot database. Finally, import the System.Data.SqlClient namespace:
' We will make use of the SQL server ' provider; however, it would also be ' permissible to make use of the ADO.NET ' factory pattern for greater flexibility.
Namespace AutoLotConnectedLayer Public Class InventoryDAL End Class End Namespace
Note Recall from Chapter 8 that when objects make use of types managing raw resources (such as a database connection), it is a good practice to implement IDisposable and author a proper finalizer. In a production environment, classes such as InventoryDAL would do the same; however, I'll avoid doing so to stay focused on the particulars of ADO.NET.
Now, recall from Chapter 15 that every VB project created with Visual Studio receives a default root namespace, which is identically named to the project you are creating. Therefore, at this point, the InventoryDAL class is defined within the nested AutoLotDAL.AutoLotConnectedLayer namespace.
To simplify how others can import the InventoryDAL type into their projects, open the My Project editor, select the Application tab, and delete the current value within the Root Namespace text box. Given that the Root Namespace is now empty, the fully qualified name of our class is simply AutoLotConnectedLayer.InventoryDAL.
Was this article helpful?