| We will now create a simple command-line program using ADO .NET. We will access a table, list the rows to the console, and close the connection. -
Begin by opening the Visual Studio integrated development environment (IDE). -
Next select the FileNewNewProject menu item. The New Project dialog opens. On the left you will notice a tree view with several different types of project groups listed: VB, Visual C# and Visual C++, and others. -
Select the VB tree node. In the list view on the right you will see a number of project templates. -
Scroll down and select Console Application. -
Change the name of the project to ADOBook1. If you wish, you can create a subfolder under the default location so you can group the projects. -
Click OK. Visual Studio creates a VB console application with one module named Module1. It is also creates a Sub Main procedure to act as the program entry point. Module Module1 Sub Main() End Sub End Module VB 6 In VB 6 you could not create a console application easily. It was possible, but involved application program interface (API) calls and lots of arcane syntax. Even so-called batch programs with only a Sub Main() function still ran as a Windows code image. This was always one of the big complaints of the C/C++ crowd and Unix programmers moving to the Windows platform. As a participant in both realms, I could understand their frustration. Some jobs simply do not require the overhead of the windowing environment. This is especially true of server-side applications that do no more than massage data, such as batch billing and posting programs. Well, Microsoft finally addresses this issue in VB .NET. It only took seven versions!
Look at the Project Explorer. Expand the References node. Notice that there are three namespaces: System, System.Data, and System.XML. These are the basic namespaces required to write a console database application. Now let's add code. First we want to declare our objects and variables . Enter the following declarations right after the Sub Main() declaration: Dim cn As New SqlClient.SqlConnection() Dim ad As New SqlClient.SqlDataAdapter() Dim cmd As New SqlClient.SqlCommand() Dim ds As New DataSet() Dim strCn As String Dim strSQL As String Dim row As DataRow Notice how the IDE automatically indents for you. My advice is to get used to this. If you used a different formatting standard (or none at all) in the past, you can adjust the IDE to your standard but I advise against it. The defaults conform to the VB coding standards set forth by Microsoft. I am a strong proponent of coding standards as they give the code a uniform look and feel within an organization. This is important when several generations of coders must work with a program. VB 6 In VB 6 it was not considered good practice to declare object variables using the New keyword. This would cause bizarre behavior because merely referencing the variable would cause an instance to be created. This is no longer true in VB .NET. Instances of classes are created when they are declared if they are declared as New. Because the .NET common language runtime (CLR) uses garbage collection, you do not have to be as concerned about releasing references to objects (by assigning them to Nothing). In addition, you can assign an object variable to Nothing in VB .NET even if it was declared As New. You can also have some stay over when the object is destroyed by calling the Dispose method. This does not actually destroy the object. It marks it for garbage collection, so there is not really full control, but it is better than nothing.
Now go ahead and add the rest of the code: Try ' NOTE: Replace the name MyServer with the name of your server. ' ALSO: Be sure to use a user name and password that is valid for your server. strCn = "Server=MyServer;Initial Catalog=Northwind;UID=adobook;pwd=xxxx" cn.ConnectionString = strCn strSQL = "Select * from Customers" cmd.CommandText = strSQL ad.SelectCommand = cmd ad.SelectCommand.Connection = cn ad.Fill(ds) For Each row In ds.Tables(0).Rows Console.WriteLine(CStr(row.Item("CustomerID")) & vbTab & CStr(row.Item("CompanyName"))) Next Catch errobj As Exception Console.WriteLine(errobj.Message) End Try Take note of the Try, Catch, End Try blocks of code. This is the new structured error-handling construct of VB .NET. This replaces the On Error method of older versions of VB. When an error occurs within the Try block, control jumps to the first Catch block that references the Error Object returned by the system. The Error Object's root class is Exception. All other custom error classes derive from this class. Therefore, since we are catching all errors that send an Exception class, this will catch the error and assign the exception object to the errobj variable. Once the Catch exception code executes, control passes to the first statement after the End Try, which in this case is the end of the program. Compiling the Program Now we must compile the program. Point to the Build menu and select Build ADOBook01-01. We don't want to just run the project. This would compile the program, and start a console session, but the program would execute too fast to see the results. Instead let's start our own console session by using a special command prompt from the Visual Studio .NET program menu. This is the same command prompt (cmd.exe), but sets up all the correct environment variables for running .NET programs. You may want to drag a copy to the command bar or desktop for easy access. It is found in the Start menu, Programs, Microsoft Visual Studio .NET, Microsoft Visual Studio .NET Tool, Visual Studio .NET Command Prompt. Once you have the console session running, navigate to the folder where your program executable resides using the cd command. Be sure to put path names with spaces inside double quotes. By default, all .NET compilers put their executables in a "bin" folder immediately under the folder where you have your source code. Run the program by typing ADOBook1 at the command prompt. After a second (depending on your machine's speed and the location of SQL Server or MSDE), a list of customer IDs and names should appear on the screen, after which the command prompt will reappear. Let's take a look at the program line-by-line . strCn = "Server=MyServer;Initial Catalog=Northwind;UID=adobook;pwd=xxxx" cn.ConnectionString = strCn These two lines set up the ConnectionString. I like to assign my string properties to string variables first and then assign the string variable to the property. This is so I can examine the string while debugging in case I formatted it incorrectly. Substitute the name of your server and use a user name and password that will allow access to your server. The user name and password in the example are not real. Note that we did not specify a provider (SQLOLEDB.1) as we would have had to in VB 6. This is because we are using the SqlClient classes and it is assumed we are connecting to SQL Server. If we were using the OleDbClient classes we would have had to include the OLEDB provider in the Connection string. These next four lines set up the DataAdapter: strSQL = "Select * from Customers" cmd.CommandText = strSQL cmd.Connection = cn ad.SelectCommand = cmd Notice that DataAdapter contains the SqlCommand object cmd. The command object in turn contains the SQL statement and a reference to the Connection object. As we said, the DataAdapter acts as the mediator between the DataSet and the database or data source. So far, all we have done is set up the objects. We have not actually opened a Connection or read any data. The next line does all of this for us. ad.Fill(ds) The Fill method of the DataAdapter opens the connection, executes the SQL statement, and caches the results in a DataTable object in the DataSet (ds). It then closes the connection. Remember, we are working under the assumption of inherently disconnected and sessionless data access. This is a good thing, because your SQL Server will not have to maintain 3,000 open connections for all concurrent users. Additionally, we did not have to release any of the objects. When an object goes out of scope, the garbage collector automatically releases the reference and deallocates the memory. There is a way to force an object to dereference itself, but we will look at that later. In most cases it is not necessary. | 
