Pro Visual C++-CLI And The .NET 2.0 Platform (2006) [eng]-1
.pdf
518 C H A P T E R 1 2 ■ A D O . N E T A N D D A T A B A S E D E V E L O P M E N T
Creating a New Database
The first step in database development isn’t creating one. Obviously, creating the data model, designing the logical database, and designing the physical database should come first. But hey, I’m a programmer. I’ll code first and then go ask questions. (I’m joking—really!)
Visual Studio 2005 makes creating databases so easy that it’s almost not worth explaining how to do it.
The following steps create the database DCV_DB, which contains author information and their related stories. You will use this database throughout the chapter.
1.Select Server Explorer from the View menu.
2.Right-click the Data Connections folder.
3.Select the Create New SQL Server Database menu item, which displays the Create New SQL Server Database dialog box shown in Figure 12-2.
Figure 12-2. The Create New SQL Server Database dialog box
4.Enter the server name that the database will reside on (in my case, Amidala).
5.Enter DCV_DB in the New database name field.
6.Select the Use Windows Authentication radio button.
7.Click OK.
Microsoft SQL Server supports two types of security: Windows Authentication and SQL Server authentication. Covering these security systems is beyond the scope of this book. In the preceding database, I use the default security configuration. You should consult your DBA to see which security method you should use.
Now you should have a new database called DCV_DB in your database folder. You can expand it and see all the default folders built. If you click these folders, however, you will see that there is nothing in them. Okay, let’s fix that and add some stuff.
C H A P T E R 1 2 ■ A D O . N E T A N D D A T A B A S E D E V E L O P M E N T |
519 |
Adding and Loading Tables and Views to a Database
An empty database is really quite useless, so now you’ll add a couple of tables to the database to provide a place to store your content.
■Note The tables and views you use in this chapter are purposely very simple (you might even call them minimal) and aren’t the best schema around. I did this so that you don’t get bogged down with the details of the database and so it doesn’t take much effort or time for you to build these tables and views yourself.
The first table is for storing authors and information about them, and the second table is for storing headlines and stories. The two databases are linked together by a common AuthorID key. Figure 12-3 shows a data diagram of the database.
Figure 12-3. The DCV_DB data diagram
Having this separation means you have to store only one copy of the author information, even though the author may have written many stories. If you had created only one table to contain all the information, a lot of duplicated author information would have to be rekeyed each time a story is added to maintain the database. It also conveniently enables me to show you how to create a relationship between tables.
The process of building a new table is only slightly more difficult than creating a database. The hard part is figuring out what columns are needed and the format for each table in the database. It’s nice to know you can spend most of your time designing the ultimate database schema instead of figuring out how to implement it.
Creating Tables
To create the first table, follow these steps:
1.Expand the Date Connections folder.
2.Expand the DCV_DB folder. Usually the server name will precede the database name and be followed by dbo. For my system, I expand the Amidala.DCV_DB.dbo folder.
3.Right-click the Tables folder.
4.Select the Add New Table menu item. You should now have an entry form in which to enter the database columns shown in Table 12-2. (Note that Description and Identity Specification are entered in the Column Properties view, which comes available when you select the column definition row.)
522 C H A P T E R 1 2 ■ A D O . N E T A N D D A T A B A S E D E V E L O P M E N T
7.Select Authors as the primary key side of the relationship from the Primary key table dropdown list.
8.Select AuthorID as the primary key in the grid beneath the Primary key table drop-down list.
9.Select AuthorID as the foreign key in the grid beneath the Foreign key.
10.Click OK.
11.Click Close.
Now you have two tables and a relationship between them. Quite often, when you want to get data from a database, you need information from multiple tables. For example, in this case, you might want to get all stories with each author’s first and last name. As mentioned previously, you could have created the Content table that way, but then you would have a lot of duplicate data floating around. There is nothing stopping you from executing a SQL statement, also known as a query, that gets this information, as shown in Listing 12-1.
Listing 12-1. Getting Data from Two Tables
SELECT |
FirstName, |
|
LastName, |
|
Headline, |
|
Story |
FROM |
Authors, |
|
Content |
WHERE |
Authors.AuthorID = Content.AuthorID |
ORDER BY |
StoryID ASC |
Personally, I prefer to be able to write a query something like this instead:
SELECT FirstName, LastName, Headline, Story FROM Stories
This is exactly what you can do with database views. Basically, you might think of a view as a virtual table without any data of its own, based on a predefined query. If you know you are going to use the same set of data based on a query, you might consider using the view instead of coding.
Those of you who are knowledgeable about SQL and views might have noticed the ORDER BY clause. Microsoft SQL Server supports the ORDER BY clause in its views, unlike some older database systems.
Creating a View
Follow these steps to create a view:
1.Right-click the Views table from within the DCV_DB folder in Server Explorer.
2.Select Add New View from the menu. This will bring up an Add Table dialog box similar to the one shown in Figure 12-6.
524 C H A P T E R 1 2 ■ A D O . N E T A N D D A T A B A S E D E V E L O P M E N T
5.Click the Close button.
6.Click the check boxes for FirstName and LastName in the Authors table.
7.Click the check boxes for StoryID, Headline, and Story in the Content table.
8.Right-click StoryID and select Sort Ascending from the menu.
9.Select Save View1 from the File menu.
10.Enter Stories into text field.
11.Click OK.
Pretty painless, don’t you think? You have the option of testing your view right there, too. Click the Run Query button on the main toolbar. (It’s the button with an exclamation point on it.) The View Design window is pretty powerful. If you play with it for a while, you’ll see what I mean.
Did you click the Run Query button and get nothing? Oops . . . I forgot to tell you to load some data into the database. You can do this with Visual Studio 2005 as well. Simply double-click either of the tables you created, and an editable table will appear.
First enter the data for the authors. If you don’t, you won’t have an author ID to enter into the AuthorID column in the Content view. Enter the data from Table 12-4. Notice that there are no author IDs to enter—this field is automatically created. In fact, Visual Studio 2005 will yell at you if you try to enter something in the AuthorID column.
Table 12-4. Author Data
LastName |
FirstName |
Doors |
Bill |
Ellidaughter |
Larry |
Fraser |
Stephen |
|
|
Now enter the data in Table 12-5. Notice that StoryID cannot be entered. It, too, is an autogenerated number. You do have to enter AuthorID, though, because it is not automatically generated in this table.
Table 12-5. Content Data
AuthorID |
Headline |
Story |
1 |
.NET is the Best |
According to my research. The .NET product has no |
|
|
competition, though I am a little biased. |
2 |
Oracle is #1 |
Research suggests that it is the best database on the |
|
|
market, not that I have any biases in that conclusion. |
3 |
Content Management |
Not anymore. It now costs the price of a book and a |
|
is Expensive |
little work. |
4 |
SQL Server Will Be #1 |
This database has no real competition. But then |
|
|
again, I am a little biased. |
|
|
|
C H A P T E R 1 2 ■ A D O . N E T A N D D A T A B A S E D E V E L O P M E N T |
525 |
Building Stored Procedures
You don’t have to use stored procedures, because anything you can run using stored procedures you can run using standard SQL. So, why cover this utility at all?
There are two main reasons. First, stored procedures let a software developer call database code using function calls with arguments. Second, and more important, the utility is compiled before it gets loaded. This makes the calls to the database faster and more efficient because it has already been optimized.
Because you haven’t encountered ADO.NET code yet, you won’t be able to do much with the stored procedure you’ll create. Fortunately, Visual Studio 2005 provides an option so that it can be tested.
Unlike the previous utilities, you have to actually code stored procedures. If you don’t know SQL, don’t worry because the coding is short and, I think, pretty self-explanatory. As always, there are many good books you can read to get a better understanding of it.
You will create a stored procedure to insert data into the Authors table. You already did this process manually, so you should have a good idea of what the stored procedure needs to do.
To create a stored procedure, follow these steps:
1.Right-click the Stored Procedures table from within the DCV_DB folder in Server Explorer.
2.Select Add New Stored Procedure from the menu. This will bring up an editing session with the default code shown in Listing 12-2.
Listing 12-2. Default Stored Procedure Code
CREATE PROCEDURE dbo.StoredProcedure1 /*
(
@parameter1 datatype = default value, @parameter2 datatype OUTPUT
)
*/ AS
/* SET NOCOUNT ON */ RETURN
First you have to set up the parameters that will be passed from the program. Obviously, you need to receive all the mandatory columns that make up the row. In the Authors table’s case, that’s the entire row except AuthorID, which is auto-generated. Listing 12-3 shows the changes that need to be made to the default code provided in order to add parameters. Note that the comments (/*...*/) are removed.
Listing 12-3. Setting the Parameters
CREATE PROCEDURE dbo.StoredProcedure1
(
@LastName NVARCHAR(32) = NULL, @FirstName NVARCHAR(32) = NULL
)
AS
526 C H A P T E R 1 2 ■ A D O . N E T A N D D A T A B A S E D E V E L O P M E N T
The SET NOCOUNT ON option prevents the number of rows affected by the stored procedure from being returned to the calling program every time it is called. If you need a count on the number of records affected, you can leave the SET NOCOUNT ON option commented out, or you can delete the option altogether. Because I will use the count in a later example, I left the option commented out.
Finally, you code the actual insert command. The key to this stored procedure is that instead of hard-coding the values to be inserted, you use the parameters you previously declared. Listing 12-4 is the final version of the stored procedure. Note that you rename the stored procedure to dbo.InsertAuthor.
Listing 12-4. InsertAuthor Stored Procedure
CREATE PROCEDURE dbo.InsertAuthor
(
@LastName NVARCHAR(32) = NULL, @FirstName NVARCHAR(32) = NULL
) |
|
|
AS |
|
|
/* SET |
NOCOUNT ON */ |
|
INSERT |
INTO |
Authors ( LastName, FirstName) |
VALUES |
|
(@LastName, @FirstName) |
RETURN
All that’s left is to save the stored procedure. Saving the file will create a stored procedure with the name on the CREATE PROCEDURE line. If you made a mistake while coding, the save will fail, and an error message will tell you where the error is.
To run or debug the stored procedure, just right-click the newly created stored procedure and select Run Stored Procedure or Step Into Stored Procedure.
You now have a database to work with for the rest of the chapter. Let’s continue on and start looking at ADO.NET and how to code it using C++/CLI.
Managed Providers
Managed providers provide ADO.NET with the capability to connect to and access data sources. Their main purpose, as far as most developers are concerned, is to provide support for the DataAdapter class. This class is essentially for mapping between the data store and the DataSet.
Currently four (Microsoft supported) managed providers exist for ADO.NET:
•SQL Server managed provider: Connects to Microsoft SQL Server version 7.0 or higher databases
•OLE DB managed provider: Connects to several supported OLE DB data sources
•ODBC managed provider: Connects to ODBC-connected databases such as MySQL
•Oracle managed provider: Connects to the Oracle8i or higher databases
C H A P T E R 1 2 ■ A D O . N E T A N D D A T A B A S E D E V E L O P M E N T |
527 |
Determining which of these managed providers is actually used depends on the database that ADO.NET interfaces with. Currently, ADO.NET interfaces with four groups of database types: Microsoft SQL Server 7.0 and later, Oracle8i and later, databases that provide ODBC support, and databases that provide OLE DB support. Which database group you are using determines whether you implement the System::Data::SqlClient, System::Data::Oracle, System::Data::Odbc, or System::Data::OleDb namespace.
In addition, the group of databases interfaced with determines which classes you will use. You will find that if you are using the System::Data::SqlClient namespace, then all of your classes will be prefixed with Sql, as in SqlCommand() and SqlDataAdapter(). If you are using the System::Data::Oracle namespace, then the classes will be prefixed with Oracle, as in OracleCommand() and OracleDataAdapter(). If you are using the System::Data::Odbc namespace, then the classes will be prefixed with Odbc, as in OdbcCommand() and OdbcDataAdapter(). And, if you are using the System::Data::OleDb namespace, then the classes will be prefixed with OleDb, as in OleDbCommand() and OleDbDataAdapter().
Once you have learned one managed provider, you have pretty much learned all four because they are nearly the same, except for the Sql, OleDb, Odbc, and Oracle prefixes and a few other small differences.
Because this book uses Microsoft SQL Server 2000, I use the SQL Server managed provider and thus the namespace associated with it.
Connected ADO.NET
As I stated previously, you have two distinct ways of accessing a database using ADO.NET. I cover the one that’s easier to visualize and code (at least for me) first: connected access.
With connected access, you are continually connected to the database during the entire time you work with it. Like file access, you open the database, work with it for a while, and then you close it. Also like file I/O, you have the option of buffering data written to the database. This buffered access to the database is better known as transactional database access. I discuss this access method after I cover nontransactional database access.
Using Simple Connected ADO.NET
You’ll start with the easiest way of working with the database, where the commands you execute happen immediately to the database.
Figure 12-8 shows the basic flow of nontransactional database access.