Beginning Visual Basic .NET Database Programming phần 3

Chapter 5 A Brief Introduction to ADO.NET Before we can begin creating our application we need to learn about some basic components of ADO.NET and wizards that our application is to use. We will go into greater detail on ADO.NET in the next chapter so, for now, we`ll learn just enough to get us through this simple database project. ADO.NET provides us with a way of gathering data and information and presenting it through a user interface. By using some components, we`re able to connect to various data sources and can then build a user interface that accesses a database. We need four pieces to build our ADO.NET project: 1. A data source – where the actual data is stored, our database. 2. A Connection object – for connecting us to our database. 3. A DataAdapter object – to provide a mechanism for reading and writing data to the database. 4. A DataSet object – this will contain the table(s) that we will use. The following figure shows how all these pieces tie together. Firstly, we need a connection to the data source, provided by a Connection object. The Connectionobject requires certain information for it to connect to the data source. The Connectionobject is called by the DataAdapter object, which handles commands to select, update, insert, and delete data in the data source. Finally we have a DataSet that contains our tables and which uses the DataAdapter to populate itself and to update information in the data source. 4 The User Interface for the Database Basic Data Components DataSet Table1 DataAdapter Table2 Data Connection Data Source We will now cover these components in a bit more detail, taking each of the data source, Connection, DataAdapter and DataSet in turn. The Data Source A data source is the term used to describe any collection of information that can provide data to us. It can take the form of a database, an XML document, a Microsoft Excel spreadsheet, or even a flat text or binary file. It only takes one or two lines of code for us to change the kind of data source that we connect to. The Windows environment provides us a shared set of classes for use in our programs to communicate with these different sources using similar code. The Data Connection The first thing we need to connect to a database is the data Connection object. This comes in two versions – either a SqlConnection or OleDbConnection object. As we are working with the SQL Server Desktop Engine, we will use the SqlConnection object. When we create a connection using a SqlConnection object, we need to feed it the following connection parameters: Data Source – the name of the server where your data source is located. The data source can be anywhere, be it on your network or somewhere over the Internet. Usually, you will be working on your local network and so you need to specify the name of the computer that holds the data source here. Alternatively, we can give the name localhost or (local) to signify that we want to use the computer that is actually running the application. This terminology is used by many Windows applications when it is necessary to identify the current, local computer. User ID and Password – the authentication details required to communicate with the data source. The ID and password is set up by the database administrator and helps prevent people from viewing or modifying the database without permission. 5 Chapter 5 Initial Catalog – this is the name of the database we want to work with – in this case, NorthwindSQL. To create a new connection, we declare a new SqlConnection and set the ConnectionString property using these parameters as shown here: Dim myConnection As New SqlClient.SqlConnection() myConnection.ConnectionString = "Data Source=localhost;" & _ "Initial Catalog=NorthwindSQL;User Id=sa;Password=sa;" Alternatively, we can pass the connection string as a parameter to the SqlConnection as follows: Dim myConnection As New SqlClient.SqlConnection("Data Source=localhost;" & _ "Initial Catalog=NorthwindSQL;User Id=sa;Password=sa;"" Creating a new OleDbConnection object is similar, except that we also need a Provider parameter to describe the type of data source that we are connecting to. So why don`t we need that parameter with the SqlConnectionobject? You`ve got it – because the provider type will always be SQL and, in fact, if you do try to set the Provider parameter for an SqlConnection object, you will get an error. Now we can look at the component that requires a data Connection object to be set up in order to function, namely the DataAdapter. The DataAdapters The DataAdapter is the mechanism that sits between the data source and the DataSet. We have two types of DataAdapters, the SqlDataAdapter, which is used exclusively for SQL Server databases, and the OleDbDataAdapter, which is used for all other data sources and goes through another layer called OLE DB. Consequently, by avoiding the need for this extra layer, the SqlDataAdapter provides much faster access to data. The OleDbDataAdapter can be used to access SQL Server but, as it then goes through the OLE DB layer, you are well advised to stick with the SqlDataAdapter for optimum performance if you don`t anticipate using anything other than SQL Server. This applies to our simple database application in this chapter, and so we work strictly with SqlDataAdapter. The DataAdapter allows selecting, updating, deleting, or inserting data in the data source. These methods are accomplished through the use of the SelectCommand, UpdateCommand, InsertCommand, and DeleteCommand properties to set to the database command string required for that particular operation. Each of these properties is an instance of a Command object, whose job it is to execute a SQL statement or stored procedure and return a result set. For a SQL database, the Command object will hold the actual SELECT, UPDATE, INSERT, and DELETE statement required for a given operation, such as "SELECT * FROM Products" or "DELETE FROM Orders WHERE CustomerID=`ABC`". The Command object also stores connection information so it may connect to the database to execute the SQL statement that it contains. Note that, in our simplified case, we will only be working with a SelectCommand as we only need to select information from our database for viewing. 6 The User Interface for the Database As we are dealing with a SQL database, we will be working with the SqlCommand object (as opposed to the OleDBCommandobject). When we use the DataAdapter Wizards, for each table you work with you will have a corresponding DataAdapter. When we use the Wizards, the DataAdapters are configured specifically for the chosen table such that all of the methods for updating and retrieving information point to that specific table. To re-use the adapter for another table, we have to essentially rebuild the objects that make up the DataAdapter, which means all of the Command objects. The simpler solution is to assign one DataAdapter per table, and this helps keep your code nice and clean and easy to maintain. When you build a DataAdapter, you can specify more than one table if needed. For example, we could create a DataAdapter that links the Customers table and the Orders table – to enable us to view information from both tables using a single DataAdapter, without needing any code to link them. This method of linking multiple tables into a single view doesn`t work really well when it comes to updating information, however, as the DataAdapter Wizard isn`t able to properly link tables together to cascade updates or deletes, reinforcing the case for using one DataAdapter per table. This diagram shows the basic structure of a DataAdapter: DataAdapter Command Objects SelectCommand InsertCommand UpdateCommand DeleteCommand The DataSet Finally, a DataSet is a container or collection of tables; it can contain one or more tables and is maintained in memory. Relationships between tables are also stored here. The tables it holds contain information such as customer details or product information in the form of records, or rows. A table may consist of thousands of such rows. **Please insert 5555_05_39.bmp DataSet Each DataSet can contain many tables DataSet Table1 Table2 Each Table can contain many rows Table Row Row Row Rows contain data Row 7 Chapter 5 One useful illustration is to think of a DataSet as holding details of a book publisher. A technical publisher might publish books in several categories such as .NET, Java, ASP, and C++. Within each category are individual books – so that a .NET category could have books such as Professional VB.NET, Beginning VB.NET, Professional C#, and Introducing .NET. A table could represent each of these categories, and each book in a category would be represented by a row in the appropriate table. Each row holds details for each book – for example, title, price, ISBN number, publishing date, and the number of pages. Publisher Book Catagories Each publisher can publish books for several categories Wrox Press .NET Each category can contain Java several books ASP .NET C++ Professional VB.NET Beg. VB.NET Professional C# Introducing .NET Book Details: Title = Professional VB.NET Price = $59.99 ISBN = 1861004974 Published = August 2001 Pages = 950 There is no limit to the type of information you can store in a DataSet. Now that we have looked at the internals of a DataSet, let`s take a look at how we can put one to use in an application. The components shown in the figure below will be demonstrated in our application: Customer Table Components DataSet Customers SqlDataAdapter SqlConnection Northwind Database 8 ... - tailieumienphi.vn