| The Data Source Configuration wizard is one of the first things you'll see demonstrated if you watch Microsoft show off the new data access features of Visual Studio 2005. At this point, I'm going to step though some of these "demo" scenarios and discuss some of the details they might have failed to mention as they dragged-and-dropped their way across the stage. Later on, I'll show some other more sophisticated ways to leverage the DSCW. Tip The "Data Design" toolbar exposes basic Data Source operations like Generate DataSet, Show Data Sources, Preview Data, and Add New Data Source.
Building a Single-Table TableAdapter The goal of this first exercise is to create a single-layer (non-hierarchical) interface to a database Table located on SQL Server. To generate the behind-the-scenes code, the DSCW has to gather enough information to: Identify the "source" of the data: This means identifying the machine name of the system hosting the SQL Server instance and the instance name. Specify the credentials: Your application expects the application to use these credentials to gain access to this server. There is a lot to these two stepsso much that I devote Chapter 9, "Getting Started," to getting connected. Specifying where the ConnectionString should be persisted: The DSCW can save the generated (or selected) ConnectionString in the app.config file. Typically, this is what you'll want to do. Specify the data source object: Basically, this means choosing one or more of the visible Table, View, or stored procedure objects exposed to the credentials supplied.
Of course, these are the same steps you have to take as you code a ConnectionString. What the DACW does not do for you is add exception handlers to deal with the litany of issues that can (and do) happen as you try to connect to a data source. Let's get started by creating a new Windows forms (Smart Client) project. Once the project is created, walk through these steps to build a simple Data Source against the Authors Table. 1. | Open the Data Sources window by clicking on the Data | Show Data Sources (Shift+Alt+D) menu. Sure, it makes sense to dock this window in some convenient place.
| | | 2. | Click "Add New Data Source..." in the Data Sources window (or simply click the Data | Add New Data Source menu).
| 3. | This launches the Data Source Configuration wizard, shown in Figure 6.5.
Figure 6.5. The initial DSCW dialogchoosing the Data Source type. 
| 4. | Choosing "Database" tells the DACW that you want to set up a connection to a specific database and extract data from its data objects. Click "Database" and "Next".
| | | 5. | This launches the DSCW "Choose Your Data Connection" dialog. Here, you can choose one of the Data Connections already configured (as I discussed in Chapter 9) or create an entirely new Data Connection. Basically, what you're doing here is choosing a ConnectionString. In this case, I'm going to use a specific SQL Server login designed for this application type. It has limited rightsit's permitted to access only a couple of tables in the Biblio database and only one stored procedure. The configured dialog is shown in Figure 6.6.
Figure 6.6. Specifying a SQL Server instance and login credentials. 
| | | 6. | For this example, I'm using a SQL Server loginnot SSPI (integrated) security. Because I specified that the password is to be persisted in the ConnectionString, the DSCW dialog reminds us that this is not such a good idea. It gives us another chance to remove the password from the ConnectionString. This dialog (shown in Figure 6.7) also permits you to view the ConnectionString to make sure it includes all of the parameters you need to specify.
Figure 6.7. Choosing (or adding) a data connection for the DACW. 
By not saving the SQL Server password in the ConnectionString, you're going to have to remember to pass it to the DACW-generated code before you'll be able to open the connection. This approach also assumes that you've enabled "mixed mode" security on the target SQL Server instance. I discuss how (and when) to do this in Chapter 9.
| | | 7. | The next dialog asks if you want to persist the ConnectionString in the application configuration file (app.config) and, if so, how to name it in the file. You'll take the default here and save it as "BiblioConnectionString", as shown in Figure 6.8.
Figure 6.8. Naming the persisted ConnectionString. 
Once a ConnectionString is persisted in the app.config file[4], you can reuse it for additional Data Sources by selecting the named Data Connection (MySettings). If you need to modify the ConnectionString, you don't (shouldn't) have to open the app.config file (once the DSCW is done). When working with Windows Forms, you can simply open the project property page and examine the Settings section (as shown in Figure 6.9).
[4] No, I'm not going to show you the XML app.config file. Take a look, though, if you're curious. Just don't touch anything.
Figure 6.9. Reviewing and editing the ConnectionString generated by the DSCW. 
Tip ASP.NET programs don't support the DSCW. You'll need to use the Web project tools to change the web.config-persisted ConnectionString using a property page. Here, you'll find the generated ConnectionString in a strongly typed application setting. To change it, click the ellipsis (...) in the Value column.
| | | 8. | Next, the DSCW prompts you to choose the objects to query (as shown in Figure 6.11). Since this login has limited rights, the DSCW "Choose Your Database Objects" dialog only populates itself with a list of database tables and views that the login has rights to access. In this case, our SQL Server login has been granted rights to the Authors, Title_Author, Titles, and Publishers Tables and the AuthorsByYearBorn stored procedureand that's it.
Incidentally, to deny rights to a selected database user (tied to the SQL Server login I'm using for this Data Source), I used the custom stored procedure DenyPermissionsOnTo, which is included in the Biblio database and in the support files included with the book. Basically, this stored procedure walks the objects in the current database and executes three T-SQL DENY statements on each object: ALL, ALTER, and TAKE OWNERSHIP. You must leave the CONTROL and VIEW DEFINITION permissions unset (neither granted nor denied) so the DACW can access (see) the objects. These permissions were set using SQL Server Management Studio, as shown in Figure 6.10.
Figure 6.10. Setting rights to the Authors table for a specific database user. 
Whatever you do, don't just arbitrarily check off any of these check boxes unless you understand what's going to happen. If you do, all of the objects in the list will be used to generate TableAdapter classes. If each TableAdapter takes over 850 lines of code, it might take until next June to get all the code generated.
Figure 6.11. The "Choose Your Database Objects" dialog. 
| | | 9. | At this point, you can drill into the object lists by clicking the "+" sign next to the object icon. If there is no "+", the login does not have rights to any of this type of object. Clicking the "+" explodes the list and shows you the objects to which the login has rights to access. Remember, our login is allowed to see only a few tables and stored procedures, as shown in the list in Figure 6.12.
Figure 6.12. The exploded list of available database objects based on the login user's rights. 
| | | 10. | Before clicking "Finish", be sure to check the specific columns of the objects you wish to use to generate the TableAdapter and DataSet. No, don't select more columns than you actually need. You'll also discover that some DBAs restrict rights to specific columns. Individual columns might be set to deny UPDATE, DELETE, or SELECT (or other) rights.
If you choose an object that you don't have rights to view, you'll get an error dialog that warns you that you won't be able to access these objects. If this occurs, it's likely that the DBA has changed the rights for these objects since the list was generated.
In our example, I choose to access the Authors table and I named our DataSet accordingly. However, consider that a single DataSet might include data from several database Tables, Views, or stored procedure rowsets, so a more generic name might be more appropriate. The choice of this name is very important, though. It forms the basis of the strongly typed name used to reference all of the TableAdapter objects generated.
| | | 11. | Click "Finish" to start generation of the TableAdapter strongly typed class code and XSD XML for your Data Source. Once the DSCW is done, it displays a tree-view dialog that includes icons for each of the objects you chose, as shown in Figure 6.13.
Figure 6.13. The Data Sources tree-view control exposes the generated objects. 
At this point, the Solution Explorer includes the new Data Source definition file, as shown in Figure 6.14. In our example, it's named dsAuthors.xsd. No, the XSD file is not deployed with your applicationit's used only at design-time to manage the auto-generated TableAdapter. If you ever want to delete a Data Source, simply delete this file.
Figure 6.14. The Solution Explorer (with Show All Files checked). 
The generated TableAdapter code is stored in the dsAuthors.Designer.vb file. While you can review the code in this file, it's not wise to revise it. It's one of those "brain-surgery-in-the-mirror" things that can cause unexpected side effects. If you feel a need to make a change to your TableAdapter, I recommend that you try to use the Data Source configuration toolI'll discuss that next.
|
|
