Hi Pro-do ;)
Take a look @ this tutor
No isn't me who write it all ! but just organise information found on the web
Connecting to an OLE DB Data Source
OLE DB is a specification for wrapping data sources in a COM-based API so that data sources can be accessed in a polymorphic way. The concept is the same as ADO.NET's concept of managed providers. OLE DB predates ADO.NET and will eventually be superseded by it. However, over the years, OLE DB providers have been written for many data sources, including Oracle, Microsoft Access, Microsoft Exchange, and others, whereas currently only one product--SQL Server--is natively supported by an ADO.NET managed provider. To provide immediate support in ADO.NET for a wide range of data sources, Microsoft has supplied an ADO.NET managed provider for OLE DB. That means that ADO.NET can work with any data source for which there is an OLE DB data provider. Furthermore, because there is an OLE DB provider that wraps ODBC (an even older data-access technology), ADO.NET can work with virtually all legacy data, regardless of the source.
Connecting to an OLE DB data source is similar to connecting to SQL Server, with a few differences: the OleDbConnection class (from the System.Data.OleDb namespace) is used instead of the SqlConnection class, and the connection string is slightly different. When using the OleDbConnection class, the connection string must specify the OLE DB provider that is to be used as well as additional information that tells the OLE DB provider where the actual data is. For example, the following code opens a connection to the Northwind sample database in Microsoft Access:
' Open a connection to the database.
Dim strConnection As String = _
"Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" _
& "C:\Program Files\Microsoft Office\Office\Samples\Northwind.mdb"
Dim cn As OleDbConnection = New OleDbConnection(strConnection)
cn.Open( )
Similarly, this code opens a connection to an Oracle database:
' Open a connection to the database.
Dim strConnection As String = _
"Provider=MSDAORA.1;User ID=MyID;Password=MyPassword;" _
& "Data Source=MyDatabaseService.MyDomain.com"
Dim cn As OleDbConnection = New OleDbConnection(strConnection)
cn.Open( )
The values of each setting in the connection string, and even the set of settings that are allowed in the connection string, are dependent on the specific OLE DB provider being used. Refer to the documentation for the specific OLE DB provider for more information.
Table 8-2 shows the provider names for several of the most common OLE DB providers.
Table 8-2: Common OLE DB provider names
Data source
OLE DB provider name
Microsoft Access
Microsoft.Jet.OLEDB.4.0
Microsoft Indexing Service
MSIDXS.1
Microsoft SQL Server
SQLOLEDB.1
Oracle
MSDAORA.1
Reading Data into a DataSet
The DataSet class is ADO.NET's highly flexible, general-purpose mechanism for reading and updating data.
Example 8-1 shows how to issue a SQL SELECT statement against the SQL Server Northwind sample database to retrieve and display the names of companies located in London. The resulting display is shown in
Figure 8-1.
Figure 8-1. The output generated by the code in
Example 8-1FPRIVATE "TYPE=PICT;ALT=Figure 1"
Example 8-1: Retrieving data from SQL Server using a SQL SELECT statement
' Open a connection to the database.
Dim strConnection As String = _
"Data Source=localhost; Initial Catalog=Northwind;" _
& "Integrated Security=True"
Dim cn As SqlConnection = New SqlConnection(strConnection)
cn.Open( )
' Set up a data set command object.
Dim strSelect As String = "SELECT * FROM Customers WHERE City = 'London'"
Dim dscmd As New SqlDataAdapter(strSelect, cn)
' Load a data set.
Dim ds As New DataSet( )
dscmd.Fill(ds, "LondonCustomers")
' Close the connection.
cn.Close( )
' Do something with the data set.
Dim dt As DataTable = ds.Tables.Item("LondonCustomers")
Dim rowCustomer As DataRow
For Each rowCustomer In dt.Rows
Console.WriteLine(rowCustomer.Item("CompanyName"))
Next
The code in
Example 8-1 performs the following steps to obtain data from the database:
Opens a connection to the database using a SqlConnection object.
Instantiates an object of type SqlDataAdapter in preparation for filling a DataSet object. In
Example 8-1, a SQL SELECT command string and a Connection object are passed to the SqlDataAdapter object's constructor.
Instantiates an object of type DataSet and fills it by calling the SqlDataAdapter object's Fill method.
The DataSet class encapsulates a set of tables and the relations between those tables.
Figure 8-2 shows a class model diagram containing the DataSet and related classes. The DataSet is always completely disconnected from any data source. In fact, the DataSet has no knowledge of the source of its tables and relations. They may be dynamically created using methods on the DataSet, or they may be loaded from a data source. In the case of the SQL Server managed provider, a DataSet can be loaded from a SQL Server database using an SqlDataAdapter object. This is what was done in
Example 8-1.
Figure 8-2. A class model diagram for the DataSet and related classes
FPRIVATE "TYPE=PICT;ALT=Figure 2"
After a DataSet is loaded, its data can be changed, added to, or deleted, all without affecting the data source. Indeed, a database connection does not need to be maintained during these updates. When ready, the updates can be written back to the database by establishing a new connection and calling the SqlDataAdapter object's Update method. Examples of writing updates to a database are shown later in this chapter.Navigating the DataSet
In this section you'll learn how to find specific data in a DataSet object, how to make changes to that data, and how to write those changes back to a database.
Finding Tables
The DataSet object's Tables property holds a TablesCollection object that contains the DataTable objects in the DataSet. The following code loops through all the tables in the DataSet and displays their names:
' Iterate through the tables in the DataSet ds.
Dim dt As DataTable
For Each dt In ds.Tables
Console.WriteLine(dt.TableName)
Next
This code does the same thing, using a numeric index on the TablesCollection object:
' Iterate through the tables in the DataSet ds.
Dim n As Integer
For n = 0 To ds.Tables.Count - 1
Console.WriteLine(ds.Tables(n).TableName)
Next
The TablesCollection object can also be indexed by table name. For example, if the DataSet ds contains a table named "Categories", this code gets a reference to it:
Dim dt As DataTable = ds.Tables("Categories")
Finding Rows
The DataTable object's Rows property holds a DataRowCollection object that in turn holds the table's DataRow objects. Each DataRow object holds the data for that particular row. The following code loops through all the rows in the DataTable and displays the value of the first column (column 0) in the row:
' Iterate through the rows.
Dim row As DataRow
For Each row In dt.Rows
Console.WriteLine(row(0))
Next
This code does the same thing, using a numeric index on the RowsCollection object:
' Iterate through the rows.
Dim n As Integer
For n = 0 To dt.Rows.Count - 1
Console.WriteLine(dt.Rows(n)(0))
Next
To assist with locating specific rows within a table, the DataTable class provides a method called Select. The Select method returns an array containing all the rows in the table that match the given criteria. The syntax of the Select method is:
Public Overloads Function Select( _
ByVal filterExpression As String, _
ByVal sort As String, _
ByVal recordStates As System.Data.DataViewRowState _
) As System.Data.DataRow( )
The parameters of the Select method are:
filterExpression
This parameter gives the criteria for selecting rows. It is a string that is in the same format as the WHERE clause in an SQL statement.
sort
This parameter specifies how the returned rows are to be sorted. It is a string that is in the same format as the ORDER BY clause in an SQL statement.
recordStates
This parameter specifies the versions of the records that are to be retrieved. Record versions are discussed in the later section "Changing, Adding, and Deleting Rows." The value passed in this parameter must be one of the values given by the System.Data.DataViewRowState enumeration. Its values are:
CurrentRows
Returns the current version of each row, regardless of whether it is unchanged, new, or modified.
Deleted
Returns only rows that have been deleted.
ModifiedCurrent
Returns only rows that have been modified. The values in the returned rows are the current values of the rows.
ModifiedOriginal
Returns only rows that have been modified. The values in the returned rows are the original values of the rows.
New
Returns only new rows.
None
Returns no rows.
OriginalRows
Returns only rows that were in the table prior to any modifications. The values in the returned rows are the original values.
Unchanged
Returns only unchanged rows.
These values can be combined using the And operator to achieve combined results. For example, to retrieve both modified and new rows, pass this value:
DataViewRowState.ModifiedCurrent And DataViewRowState.New
The return value of the Select method is an array of DataRow objects.
The Select method is overloaded. It has a two-parameter version that is the same as the full version, except that it does not take a recordStates parameter:
Public Overloads Function Select( _
ByVal filterExpression As String, _
ByVal sort As String _
) As System.Data.DataRow( )
Calling this version of the Select method is the same as calling the full version with a recordStates value of DataViewRowState.CurrentRows.
Similarly, there is a one-parameter version that takes only a filterExpression:
Public Overloads Function Select( _
ByVal filterExpression As String _
) As System.Data.DataRow( )
This is the same as calling the three-parameter version with sort equal to "" (the empty string) and recordStates equal to DataViewRowState.CurrentRows.
Lastly, there is the parameterless version of Select:
Public Overloads Function Select( ) As System.Data.DataRow( )
This is the same as calling the three-parameter version with filterExpression and sort equal to "" (the empty string) and recordStates equal to DataViewRowState.CurrentRows.
As an example of using the Select method, this line of code returns all rows whose Country column contains the value "Mexico":
Dim rows( ) As DataRow = dt.Select("Country = 'Mexico'")
Because the sort and recordStates parameters were not specified, they default to "" (the empty string) and DataViewRowState.CurrentRows, respectively.
The Select method versus the SQL SELECT statement
If an application is communicating with a database over a fast, persistent connection, it is more efficient to issue SQL SELECT statements that load the DataSet with only the desired records, rather than to load the DataSet with a large amount of data and then pare it down with the DataTable's Select method. The Select method is useful for distributed applications that might not have a fast connection to the database. Such an application might load a large amount of data from the database into a DataSet object, then use several calls to the DataTable's Select method to locally view and process the data in a variety of ways. This is more efficient in this case because the data is moved across the slow connection only once, rather than once for each query.
Finding Column Values
The DataRow class has an Item property that provides access to the value in each column of a row. For example, this code iterates through all the columns of a row, displaying the value from each column (assume that row holds a reference to a DataRow object):
' Iterate through the column values.
Dim n As Integer
For n = 0 To row.Table.Columns.Count - 1
Console.WriteLine(row(n))
Next
Note the expression used to find the number of columns: row.Table.Columns.Count. The DataRow object's Table property holds a reference to the DataTable object of which the row is a part. As will be discussed shortly, the Table object's Columns property maintains a collection of column definitions for the table. The Count property of this collection gives the number of columns in the table and therefore in each row.
The DataRow object's Item property is overloaded to allow a specific column value to be accessed by column name. The following code assumes that the DataRow row contains a column named "Description". The code displays the value of this column in this row:
Console.WriteLine(row("Description"))
Finding Column Definitions
The DataTable object's Columns property holds a ColumnsCollection object that in turn holds the definitions for the columns in the table. The following code iterates through the columns in the table and displays their names:
' Iterate through the columns.
Dim column As DataColumn
For Each column In dt.Columns
Console.WriteLine(column.ColumnName)
Next
This code does the same thing, using a numeric index on the ColumnsCollection object:
' Iterate through the columns.
Dim n As Integer
For n = 0 To dt.Columns.Count - 1
Console.WriteLine(dt.Columns(n).ColumnName)
Next
The ColumnsCollection object can also be indexed by column name. For example, if DataTable dt contains a column named "Description", this code gets a reference to the associated DataColumn object:
Dim column As DataColumn = dt.Columns("Description")
Changing, Adding, and Deleting Rows
To change data in a DataSet, first navigate to a row of interest and then assign new values to one or more of its columns. For example, the following line of code assumes that row is a DataRow object that contains a column named "Description". The code sets the value of the column in this row to be "Milk and cheese":
row("Description") = "Milk and cheese"
Adding a new row to a table in a DataSet is a three-step process:
Use the DataTable class's NewRow method to create a new DataRow. The method takes no parameters.
Set the values of the columns in the row.
Add the new row to the table.
For example, assuming that dt is a DataTable object, and that the table has columns named "CategoryName" and "Description", this code adds a new row to the table:
' Add a row.
Dim row As DataRow = dt.NewRow( )
row("CategoryName") = "Software"
row("Description") = "Fine code and binaries"
dt.Rows.Add(row)
The DataRow object referenced by row in this code can be indexed by the names "CategoryName" and "Description" because the DataRow object was created by the DataTable object's NewRow method and so has the same schema as the table. Note that the NewRow method does not add the row to the table. Adding the new row to the table must be done explicitly by calling the DataRowCollection class's Add method through the DataTable class's Rows property.
Deleting a row from a table is a one-liner. Assuming that row is a reference to a DataRow, this line deletes the row from its table:
row.Delete( )
When changes are made to a row, the DataRow object keeps track of more than just the new column values. It also keeps track of the row's original column values and the fact that the row has been changed. The Item property of the DataRow object is overloaded to allow you to specify the desired version of the data that you wish to retrieve. The syntax of this overload is:
Public Overloads ReadOnly Property Item( _
ByVal columnName As String, _
ByVal version As System.Data.DataRowVersion _
) As Object
The parameters are:
columnName
The name of the column whose value is to be retrieved.
version
The version of the data to retrieve. This value must be a member of the System.Data.DataRowVersion enumeration. Its values are:
Current
Retrieve the current (changed) version.
Default
Retrieve the current version if the data has been changed, the original version if not.
Original
Retrieve the original (unchanged) version.
Proposed
Retrieve the proposed change. Proposed changes are changes that are made after a call to a DataRow object's BeginEdit method but before a call to its EndEdit or CancelEdit methods.
For example, after making some changes in DataRow row, the following line displays the original version of the row's Description column:
Console.WriteLine(row("Description", DataRowVersion.Original))
The current value of the row would be displayed using any of the following lines:
Console.WriteLine(row("Description", DataRowVersion.Current))
Console.WriteLine(row("Description", DataRowVersion.Default))
Console.WriteLine(row("Description"))
Calling the DataSet object's AcceptChanges method commits outstanding changes. Calling the DataSet object's RejectChanges method rolls records back to their original versions.
TIP: The code shown in this section affects only the DataSet object, not the data source. To propagate these changes, additions, and deletions back to the data source, use the Update method of the SqlDataAdapter class, as described in the next section, "
Writing Updates Back to the Data Source."
If there are relations defined between the DataTables in the DataSet, it may be necessary to call the DataRow object's BeginEdit method before making changes.
Writing Updates Back to the Data Source
Because DataSets are always disconnected from their data sources, making changes in a DataSet never has any effect on the data source. To propagate changes, additions, and deletions back to a data source, call the SqlDataAdapter class's Update method, passing the DataSet and the name of the table that is to be updated. For example, the following call to Update writes changes from the DataTable named Categories back to the SQL Server table of the same name:
da.Update(ds, "Categories")
Before using the Update method, however, you should understand how an SqlDataAdapter object performs updates. To change, add, or delete records, an SqlDataAdapter object must send SQL UPDATE, INSERT, or DELETE statements, respectively, to SQL Server. The forms of these statements either can be inferred from the SELECT statement that was provided to the SqlDataAdapter object or can be explicitly provided to the SqlDataAdapter object.
Example 8-2 shows an example of allowing an SqlDataAdapter object to infer the SQL UPDATE, INSERT, and DELETE statements required for applying updates to a database.
Example 8-2: Allowing an SqlDataAdapter object to infer SQL UPDATE, INSERT, and DELETE statements from a SELECT statement
' Open a database connection.
Dim strConnection As String = _
"Data Source=localhost;Initial Catalog=Northwind;" _
& "Integrated Security=True"
Dim cn As SqlConnection = New SqlConnection(strConnection)
cn.Open( )
' Create a data adapter object and set its SELECT command.
Dim strSelect As String = _
"SELECT * FROM Categories"
Dim da As SqlDataAdapter = New SqlDataAdapter(strSelect, cn)
' Set the data adapter object's UPDATE, INSERT, and DELETE
' commands. Use the SqlCommandBuilder class's ability to auto-
' generate these commands from the SELECT command.
Dim autogen As New SqlCommandBuilder(da)
' Load a data set.
Dim ds As DataSet = New DataSet( )
da.Fill(ds, "Categories")
' Get a reference to the "Categories" DataTable.
Dim dt As DataTable = ds.Tables("Categories")
' Modify one of the records.
Dim row As DataRow = dt.Select("CategoryName = 'Dairy Products'")(0)
row("Description") = "Milk and stuff"
' Add a record.
row = dt.NewRow( )
row("CategoryName") = "Software"
row("Description") = "Fine code and binaries"
dt.Rows.Add(row)
' Delete a record.
row = dt.Select("CategoryName = 'MyCategory'")(0)
row.Delete( )
' Update the database.
da.Update(ds, "Categories")
' Close the database connection.
cn.Close( )
Note the following in
Example 8-2:
A SqlDataAdapter object is constructed with an argument of "SELECT * FROM Categories". This initializes the value of the SqlDataAdapter object's SelectCommand property.
A SqlCommandBuilder object is constructed with the SqlDataAdapter object passed as an argument to its constructor. This step hooks the SqlDataAdapter object to the SqlCommandBuilder object so that later, when the SqlDataAdapter object's Update method is called, the SqlDataAdapter object can obtain SQL UPDATE, INSERT, and DELETE commands from the SqlCommandBuilder object.
The SqlDataAdapter object is used to fill a DataSet object. This results in the DataSet object containing a DataTable object, named "Categories", that contains all the rows from the Northwind database's Categories table.
One record each in the table is modified, added, or deleted.
The SqlDataAdapter object's Update method is called to propagate the changes back to the database.
Step 5 forces the SqlCommandBuilder object to generate SQL statements for performing the database update, insert, and delete operations.When the Update method is called, the SqlDataAdapter object notes that no values have been set for its UpdateCommand, InsertCommand, and DeleteCommand prperties, and therefore queries the SqlCommandBuilder object for these commands. If any of these properties had been set on the SqlDataAdapter object, those values would have been used instead.
The SqlCommandBuildObject can be examined to see what commands were created. To see the commands that are generated in
Example 8-2, add the following lines anywhere after the declaration and assignment of the autogen variable:
Console.WriteLine("UpdateCommand: " & autogen.GetUpdateCommand.CommandText)
Console.WriteLine("InsertCommand: " & autogen.GetInsertCommand.CommandText)
Console.WriteLine("DeleteCommand: " & autogen.GetDeleteCommand.CommandText)
The auto-generated UPDATE command contains the following text (note that line breaks have been added for clarity in the book):
UPDATE Categories
SET CategoryName = @p1 , Description = @p2 , Picture = @p3
WHERE (
(CategoryID = @p4)
AND
((CategoryName IS NULL AND @p5 IS NULL) OR (CategoryName = @p6)) )
Similarly, the INSERT command is:
INSERT INTO Categories( CategoryName , Description , Picture )
VALUES ( @p1 , @p2 , @p3)
And the DELETE command is:
DELETE FROM Categories
WHERE (
(CategoryID = @p1)
AND
((CategoryName IS NULL AND @p2 IS NULL) OR (CategoryName = @p3)) )
Note the use of formal parameters (@p0, @p1, etc.) in each of these statements. For each row that is to be changed, added, or deleted, the parameters are replaced with values from the row, and the resulting SQL statement is issued to the database. The choice of which value from the row to use for which parameter is controlled by the SqlCommand object's Parameters property. This property contains an SqlParameterCollection object that in turn contains one SqlParameter object for each formal parameter. The SqlParameter object's ParameterName property matches the name of the formal parameter (including the "@"), the SourceColumn property contains the name of the column from which the value is to come, and the SourceVersion property specifies the version of the value that is to be used. Row versions were discussed in the previous section, "Changing, Adding, and Deleting Rows."
If desired, a DataSet object's UpdateCommand, InsertCommand, and DeleteCommand properties can be set directly.
Example 8-3 sets the value of UpdateCommand and then performs an update using this command.
Example 8-3: Setting a DataSet object's UpdateCommand property
' Open a database connection.
Dim strConnection As String = _
"Data Source=localhost;Initial Catalog=Northwind;" _
& "Integrated Security=True"
Dim cn As SqlConnection = New SqlConnection(strConnection)
cn.Open( )
' Set up a data adapter object.
Dim da As SqlDataAdapter = New SqlDataAdapter("SELECT * FROM Categories", cn)
' Create an UPDATE command.
'
' This is the command text.
' Note the parameter names: @Description and @CategoryID.
Dim strUpdateCommand As String = _
"UPDATE Categories" _
& " SET Description = @Description" _
& " WHERE CategoryID = @CategoryID"
'
' Create a SqlCommand object and assign it to the UpdateCommand property.
da.UpdateCommand = New SqlCommand(strUpdateCommand, cn)
'
' Set up parameters in the SqlCommand object.
Dim param As SqlParameter
'
' @CategoryID
param = da.UpdateCommand.Parameters.Add( _
New SqlParameter("@CategoryID", SqlDbType.Int))
param.SourceColumn = "CategoryID"
param.SourceVersion = DataRowVersion.Original
'
' @Description
param = da.UpdateCommand.Parameters.Add( _
New SqlParameter("@Description", SqlDbType.NChar, 16))
param.SourceColumn = "Description"
param.SourceVersion = DataRowVersion.Current
' Load a data set.
Dim ds As DataSet = New DataSet( )
da.Fill(ds, "Categories")
' Get the table.
Dim dt As DataTable = ds.Tables("Categories")
' Get a row.
Dim row As DataRow = dt.Select("CategoryName = 'Dairy Products'")(0)
' Change the value in the Description column.
row("Description") = "Milk and stuff"
' Perform the update.
da.Update(ds, "Categories")
' Close the database connection.
cn.Close( )
