Chapter Six

Database Programming
[Link]
• [Link] is designed to provide a disconnected architecture
• This means that applications connect to the database to retrieve a load of
data and store it in-memory
• They then disconnect from the database and manipulate the in - memory
copy of the data
• If the database needs to be updated with changes made to the in -
memory copy, a new connection is made and the database is updated
• The main in- memory data store is the DataSet
• which contains other in - memory data stores, such as DataTable objects
[Link] Data Namespaces

• The core [Link] classes exist in the [Link] namespace.

• This namespace, in turn, contains some child namespaces.
• [Link] , [Link] and [Link].
• These provide classes for accessing SQL Server databases and OLE (Object Linking
and Embedding) DB- compliant databases, respectively.
• The [Link], [Link], and [Link]
namespaces are known as data providers in [Link]
• The [Link] namespace provides access to older Open Database
Connectivity (ODBC) data sources that do not support the OleDb technology.
• you access SQL Server databases using the SqlClient namespace
Cont.

• The following list contains the [Link] classes

• SqlConnection
• SqlDataAdapter
• SqlCommand
• SqlParameter

• Whenever you want to use these classes, you must add a reference to the
[Link] namespace
• we can use the Imports keyword
Imports [Link]
The SqlConnection Class

• It provides a connection to an SQL Server database

• When you construct an SqlConnection object, you can choose to specify a

connection string as a parameter

• The connection string contains all the information required to open a

connection to your database
Working with the Connection String Parameters

• The way that the connection string is constructed depends on what data
provider you are using
• When accessing SQL Server, you usually provide a Server and a Database
parameter
Parameter Description Parameter Description

Server The name of the SQL Server that you want to access.

Database The name of the database that you want to connect to.
Cont.
• You also need some form of authentication information which you
can provide in two ways: by using a
• user name and password in the connection string or
• by connecting to SQL Server using the NT account under which the
application is running
• If you want to connect to the server by specifying a user name and
password
Parameter Description Parameter Description

User ID The user name for connecting to the database. An account with this user ID
needs to exist in SQL Server and have permission to access the specified
database.

Password The password for the specified user.

Cont.
• SQL Server can be set up to use the Windows NT account of the user
• In this case, you don ’ t need to specify a user name and password
• You just need to specify that you are using integrated security
• The method is called integrated security because SQL Server is integrating
with Windows NT ’ s security system and provides the most secure
• You do this using the Integrated Security parameter, which you set to True
when you want the application to connect to SQL Server using the current
user ’ s NT account.
Dim objConnection As SqlConnection = New _

SqlConnection(“Server=localhost\wrox;Database=pubs;” & _
“User ID=sa;Password=wrox;”)
Opening and Closing the Connection

• After you initialize a connection object with a connection string you

can invoke the methods of the SqlConnection object such as Open
and Close
‘ Open the database connection...
[Link]()
‘ ... Use the connection
‘ Close the database connection...
[Link]()
SqlCommand
• The SqlCommand class represents an SQL command to execute against a
data store
• The command is usually a select, insert, update, or delete query
• The following code fragment shows how to initialize an
SqlCommand object:

Dim objCommand As SqlCommand = New SqlCommand()

The Connection Property

• Certain properties must be set on the SqlCommand object before you can
execute the query.
• The first of these properties is the Connection property.
• This property is set to an SqlConnection object, as shown in the next code
fragment
• For the command to execute successfully, the connection must be open at
the time of execution
[Link] = objConnection
The CommandText Property

• This property specifies the SQL string or stored procedure to be

executed.
• Most databases require that you place all string values in single quote
marks, as shown here:
[Link] = “INSERT INTO authors “ & _
“(au_id, au_lname, au_fname, contract) “ & _
“VALUES(‘123-45-6789’, ‘Barnes’, ‘David’, 1)
The ExecuteNonQuery Method

• you can execute the command to do this, the connection needs to be

opened
• You can invoke the ExecuteNonQuery method of the SqlCommand
object.
• This method executes the SQL statement and causes the data to be
inserted into the database.
• To complete your code fragment, you need to open the connection,
execute the query, and close the connection again
[Link]()
[Link]()
[Link]()
SqlDataAdapter
• The SqlDataAdapter class is similar to the OleDbDataAdapter
• The main difference is that the OleDbDataAdapter can access any data
source that supports OLE DB, while the SqlDataAdapter supports only SQL
Server databases
• Data adapters act as bridges between your data source and in - memory
data objects such as the DataSet.
• To access the data source, they use the command objects
• These command objects are associated with connections, so the data
adapter relies on command and connection objects to access and
manipulate the data source
Cont.
• The SqlDataAdapter class ’ s SelectCommand property is used to hold an
SqlCommand that retrieves data from the data source.
• The data adapter then places the result of the query into a DataSet or
DataTable.
• The SqlDataAdapter also has UpdateCommand, DeleteCommand, and
InsertCommand properties.
• These are also SqlCommand objects, used to write changes made to a
DataSet or DataTable back to the data source.
The SelectCommand Property
• The SqlDataAdapter class ’ s SelectCommand property is used to fill a
DataSet with data from an SQL Server database
Cont.
• When you want to read data from the data store, you must set the
SelectCommand property of the SqlDataAdapter class first.
• This property is an SqlCommand object and is used to specify what data to
select and how to select that data.
• The SelectCommand property has properties of its own, and you need to
set them just as you would set properties on a normal command
• Connection: Sets the SqlConnection object to be used to access the data
store.
• CommandText: Sets the SQL statements or stored procedure name to be used
to select the data.
The DataSet Class
• The DataSet class is used to store data retrieved from a data store and
stores that data in memory on the client.
• The DataSet object contains a collection of tables, relationships
• It acts as a lightweight database engine all by itself,
• It enabling you to store tables, edit data, and run queries against it using a
DataView object.
• The data in a DataSet is disconnected from the data store, and you can
operate on the data independently from the data store
• You can manipulate the data in a DataSet object by adding, updating, and
deleting the records.
• The DataSet class is present in the [Link] namespace
DataView
• The DataView class is typically used for sorting, filtering, searching,
editing, and navigating the data from a DataSet.
• A DataView is bindable,
• meaning that it can be bound to controls in the same way that the DataSet
can be bound to controls
• You can create a DataView from the data contained in a DataTable
• For example, if the data in a DataTable contains all authors sorted by last
name and first name, you can create a DataView that contains all authors
sorted by first name and then last name.
• The constructor for the DataView class initializes a new instance of
the DataView class and accepts the DataTable as an argument.
‘ Set the DataView object to the DataSet object...
Dim objDataView = New DataView([Link](“authors”))
Insert row into table
Delete row from a table
Update a table
• The program below updates the data to the [Link] from
[Link]
END