| There are two ways to use the Connection classes. In either way, you first have to create an instance of the class. The first way of creating an instance is to drag and drop the component onto your form, user control, or component class. The other way is to programmatically declare a variable of the SqlConnection or OleDbConnection type. Both of these methods do effectively the same thing. The advantage to using the Component Model is that you can design your component visually. If you create it programmatically, then you will have to do all the work in code yourself. VB6 Many VB6 programmers did not like using drop-in toolbox components because in VB6 you often had to sacrifice flexibility in favor of ease of use. With VB .NET this is no longer the case. I would recommend using the Component model as much as possible. It not only saves time, but the generated code conforms to a good coding standard. You can also use features like dynamic properties (where property values are automatically saved to disk in XML format) when you use the Component model. Moreover, you can use your own components as drop-in tools if they are built using the Component model. You don't sacrifice any flexibility at all when using the .NET Component model.
Since most of the properties of the Connection classes are read-only, we will be concentrating on the ConnectionString property and its various elements. It is through the connection string that the class is configured. The open and close methods take no parameters in ADO.Net so there really isn't much to say about them. Later on we will cover the BeginTransaction method and the SqlTransaction class. We will be doing hands-on work in this chapter so it's time to fire up the Visual Studio IDE. Let's look at the first scenario. You want to add an SqlConnection object to a Windows Form. First let's create an empty Windows application. Name the application ADOBook05-01. Leave the form named Form1. We'll be using it as a platform for demonstrating the Connection class. The easiest way to do this is to drag and drop an SqlConnection object onto the form. Roll out the toolbox if it's not already displayed. Click the Data tab. You will see a variety of data components. Drag the SqlConnection icon and drop it anywhere on the form. You should end up with the results in Figure 5.1. Figure 5.1. An empty form with an SqlConnection object.  We've mentioned this in a previous chapter, but I mention it again for reinforcement. Notice how the component did not create a visual image on the form itself; instead the image appears in a separate pane below the form. In previous versions of VB, we had controls that were invisible at runtime. They really had no visual interface and were code components, but VB6 did not have a Component model like VB .NET. The only way to create code-only components was to create an OCX control and force the programmer to host it on a form or other container. This is no longer the case with VB .NET and the Component model. The Component model was built just for allowing nonvisual components to be treated the same way as visual ones. Look at the Properties window in Figure 5.2. Figure 5.2. The SqlConnection object's properties.  Notice there are only three properties that you can change: the name of the component, its scope modifier, and the Connection string. Unlike in previous versions of ADO, the individual Connection properties are read-only this time around. The Connection string is used to provide the object with the information it needs to access the database. When you want to provide the Connection string, you can just type the full syntax of the string into the ConnectionString property, but this is a computer after all. There is a visual way of creating the Connection string. Click on the ConnectionString property. A drop-down Arrow button appears. Click this button. Your display should look like Figure 5.3. Figure 5.3. The ConnectionString property with the drop-down open.  What you see in the drop-down list are the connections you have defined in the Server Explorer, plus one entry for creating a connection. Click the entry for a new connection. The ADO Data Link Properties page appears as in Figure 5.4. Figure 5.4. The Data Link Properties page.  If this looks familiar, it is the same Data Link dialog that you would get with the old version of ADO as well. The Connection object uses the information provided from this dialog to build the Connection string for you. The Connection page is preselected for you. Since we are using the SqlConnection class there is no need to select a provider; SQL Server is presumed . Let's look at the three required entries on this screen. -
Select or enter a server name.You can enter a Windows machine name, a DNS name, a WINS name, or an IP address ( assuming you are connecting using TCP/IP protocol). If you are using named pipes, you must use a Windows or WINS name. If you use a Windows machine name, the machine must be in the same Windows domain or workgroup as your machine. In a Windows 2000 network with Active Directory, you can also use a machine in a trusted domain by qualifying the domain, with the typical Domain\MachineName format. The drop-down list will only show SQL Servers that are in the same domain or workgroup. If you typically work with an SQL Server that is in a remote domain, and you know its address, you can create an alias using the client network utility. When you create an alias, you tell the SQL Server client software to associate an alias name (that you make up) with an IP address or machine name. The alias is only valid on the local machine. The alias name will then appear in the drop down as well. -
Enter information to log on to the server.You have two choices here, depending on how your administrators have set up your server. If you are using the MSDE [2] locally, you should select Use Windows NT Integrated Security as this is the only way to connect. When you install the MSDE, it configures it to use integrated security. With this method, SQL Server uses Windows NT/2000/XP user accounts and just adds them to SQL Server automatically with full privileges. If your server requires a user name and password, then select Use a Specific User Name and Password. If you check Allow Saving Password the password will be visible in the Connection string that is generated. This may be acceptable when designing your application, as you will not have to enter the password each time you use a database object. It may represent a security flaw when the application is distributed because the password may be able to be retrieved by a programmer using a hex editor. If you select this option, remember to unselect it before creating your release build. My advice is to leave it unselected . [2] In case you skipped Chapter 1, the MSDE is the Microsoft Database Engine, a desktop implementation of SQL Server 2000 that is freely redistributable and intended to replace the MS Access Jet engine. -
You can then enter the information in the text boxes as shown in Figure 5.5. Figure 5.5. Using a specific user name and password to log on.  -
ConnectionStringSelect the database on the server.This setting is for selecting the default database. If you leave this out, you may have to qualify any object names (tables, stored procedures, views, etc.) with the database name when designing queries. Unless you absolutely need to do this, I advise selecting the database name you wish to use. I can't think of any reason to leave this blank. Even if you want to select the database programmatically at runtime, there are better ways to do that. -
Attach a database file as a database name. You can use this selection instead of supplying a database name. When you use this option, you are going to supply a file name that is the name of an SQL Server database file. This is an interesting option because it allows you to copy databases by simply copying the data file that contains the database. This option is only available for SQL Server 7 and above. You can specify a data file (.mdf) file, formerly called a device file, and attach it to an existing database with a given database name. When you click OK, the Connection string is formatted and placed in the ConnectionString property. If you want to see some of the other Connection string settings, click the Advanced tab prior to closing the dialog. On this page, you can edit any of the Connection string settings that are valid for SQL Server as in Figure 5.6. Note that there is no error checking here. You will not know if there is an error in the Connection string until you try to open the database, either explicitly by executing the Open method or implicitly by executing the Fill or Update method of a data adapter that references the Connection object. If the Connection string has a parsing error, the SqlConnection class will throw an error of type ArgumentException. You can trap the error by Catching the exception type with structured exception handling. Figure 5.6. The Advanced tab for SQL Server.  Even though we used the SqlConnection class in our example, the same process is used for the OleDbConnection class as well. The only difference is that you must also select an OLEDB provider before filling in the Connection tab. Next we will look at the providers in detail. |
