After programmatically creating a connection string, to apply it and actually
establish the connection, you must call the Open() method of the OleDbConnection
class. Its
syntax is:
Public Sub Open
Here is an example of calling it:
Imports System.Data.OleDb
Partial Class _Default
Inherits System.Web.UI.Page
Protected Sub Page_Load(ByVal sender As Object, _
ByVal e As System.EventArgs) Handles Me.Load
Dim strConnection As String
Dim connection As OleDbConnection = New OleDbConnection
strConnection = "Provider=Microsoft.Jet.OLEDB.4.0;" & _
"Data Source=" & Server.MapPath("App_Data/exercise2.mdb")
connection.ConnectionString = strConnection
connection.Open()
End Sub
End Class
As you can see, this method does not take any argument. The OleDbConnection
object that calls it is responsible to get the connection string ready:
After using a connection and getting the necessary
information from it, you should terminate it.
To
close a connection an OleDbConnection connection, you can call its
Close() method. Its
syntax is:
Public Sub Close
This method is simply called to close the current
connection. Here is an example of calling it:
Imports System.Data.OleDb
Partial Class _Default
Inherits System.Web.UI.Page
Protected Sub Page_Load(ByVal sender As Object, _
ByVal e As System.EventArgs) Handles Me.Load
Dim strConnection As String
Dim connection As OleDbConnection = New OleDbConnection
strConnection = "Provider=Microsoft.Jet.OLEDB.4.0;" & _
"Data Source=" & Server.MapPath("App_Data/exercise2.mdb")
connection.ConnectionString = strConnection
connection.Open()
' Blah Blah Blah
connection.Close()
End Sub
End Class
While you should avoid calling the Open() method more
than once if a connection is already opened, you still can.
You should always remember to close the connection so
that the resources that the database application was using can be made
available to other applications. Fortunately, the Visual Basic language provides an
alternative that can close the connection for it. To assist you with this,
you can use the Using operator as follows:
Imports System.Data.OleDb
Partial Class _Default
Inherits System.Web.UI.Page
Protected Sub Page_Load(ByVal sender As Object, _
ByVal e As System.EventArgs) Handles Me.Load
Using connection As OleDbConnection = _
New OleDbConnection("Provider=Microsoft.Jet.OLEDB.4.0;" & _
"Data Source=" & Server.MapPath("App_Data/exercise2.mdb"))
connection.Open()
' Blah Blah Blah
End Using
End Sub
End Class
When this code executes, it opens the connection. Inside of
the curly brackets, you can do whatever you want. When the compiler reaches the
closing curly bracket, it calls the OleDbConnection.Close() method, which
means you do not need to remember to close it.
After establishing a connection to a database, if you are successful, the
database system becomes available to you and you can take actions. An action you
perform on the
database server or on a database is called a command.
To support the various commands you can perform on a
Microsoft Access database, the System.Data.OleDb namespace provides the OleDbCommand
class. To use
it, you can declare a variable of type OleDbCommand using one of its
constructors. Here is an example:
Imports System.Data.OleDb
Partial Class _Default
Inherits System.Web.UI.Page
Protected Sub Page_Load(ByVal sender As Object, _
ByVal e As System.EventArgs) Handles Me.Load
Dim CommandToExecute As OleDbCommand = New OleDbCommand
End Sub
End Class
The OleDbCommand class is equipped with four constructors. The default constructor allows you to
initiate a command without specifying what action would be taken. The action to
perform is created as a string. This action is represented by the CommandText
property of the OleDbCommand class, which is of type String.
If you want to use the default
constructor, you can then create a string that would carry the action to
perform. Once the string is ready, you can assign it the CommandText property.
This would be done as follows:
Imports System.Data.OleDb
Partial Class _Default
Inherits System.Web.UI.Page
Protected Sub Page_Load(ByVal sender As Object, _
ByVal e As System.EventArgs) Handles Me.Load
Dim CommandToExecute As OleDbCommand = New OleDbCommand
Dim strCommandToExecute As String = "Blah Blah Blah"
CommandToExecute.CommandText = strCommandToExecute
End Sub
End Class
After creating the action that would be performed, you must
specify what connection would carry it. To do this, you can first create a OleDbConnection
object. To provide it to the command, the OleDbCommand class is equipped
with a property named Connection that is of type OleDbConnection. After
creating a OleDbConnection object, to provide it to the command, you can assign it
to the OleDbCommand .Connection property. This would be done as follows:
Imports System.Data.OleDb
Partial Class _Default
Inherits System.Web.UI.Page
Protected Sub Page_Load(ByVal sender As Object, _
ByVal e As System.EventArgs) Handles Me.Load
Dim strConnection As String
Dim connection As OleDbConnection = New OleDbConnection
strConnection = "Provider=Microsoft.Jet.OLEDB.4.0;" & _
"Data Source=" & Server.MapPath("App_Data/exercise.mdb")
connection.ConnectionString = strConnection
Dim CommandToExecute As OleDbCommand = New OleDbCommand()
Dim strCommandToExecute As String = "Blah Blah Blah"
connection.Open()
CommandToExecute.Connection = connection
CommandToExecute.CommandText = strCommandToExecute
connection.Open()
' Blah Blah Blah
connection.Close()
End Sub
End Class
Instead of declaring a OleDbCommand variable and the
command text separately, as an alternative, you can define the command text when
declaring the OleDbCommand variable. To do this, you can use the
second constructor of the OleDbCommand class. The syntax of this
constructor is:
Public Sub New(cmdText As String)
Once again, after using this constructor, you must specify
what connection would carry the action. To do this, you can assign a OleDbConnection
object to the Connection property of your OleDbCommand . Here is an
example:
Imports System.Data.OleDb
Partial Class _Default
Inherits System.Web.UI.Page
Protected Sub Page_Load(ByVal sender As Object, _
ByVal e As System.EventArgs) Handles Me.Load
Dim strConnection As String
Dim connection As OleDbConnection = New OleDbConnection
strConnection = "Provider=Microsoft.Jet.OLEDB.4.0;" & _
"Data Source=" & Server.MapPath("App_Data/exercise.mdb")
connection.ConnectionString = strConnection
Dim CommandToExecute As OleDbCommand = New OleDbCommand("Blah Blah Blah")
connection.Open()
CommandToExecute.Connection = connection
connection.Close()
End Sub
End Class
Instead of assigning a OleDbConnection object to the OleDbCommand
.Connection
property, you can specify what
connection would carry the action at the same time you are creating the command. To specify the connection when declaring the
OleDbCommand variable, you can use the third constructor of this class. Its syntax is:
Public Sub New(cmdText As String, connection As OleDbConnection)
The second argument to this constructor is an established
connection you would have defined. Here is an example:
Imports System.Data.OleDb
Partial Class _Default
Inherits System.Web.UI.Page
Protected Sub Page_Load(ByVal sender As Object, _
ByVal e As System.EventArgs) Handles Me.Load
Dim strConnection As String
Dim connection As OleDbConnection = New OleDbConnection
strConnection = "Provider=Microsoft.Jet.OLEDB.4.0;" & _
"Data Source=" & Server.MapPath("App_Data/exercise.mdb")
connection.ConnectionString = strConnection
Dim CommandToExecute As OleDbCommand = _
New OleDbCommand("Blah Blah Blah", connection)
connection.Open()
CommandToExecute.Connection = connection
connection.Close()
End Sub
End Class
If you had initiated the action using the
default constructor of the OleDbCommand class, you can assign a OleDbConnection
object to the Connection property of the OleDbCommand class.
After establishing a connection and specifying what command
needs to be carried, you can execute it. To support this, the OleDbCommand
class is equipped with the ExecuteNonQuery() method. Its syntax is:
Public Overrides Function ExecuteNonQuery As Integer
This method does not take any argument. The OleDbCommand
object that calls it must have prepared a valid command.
In some cases, some actions take longer than others to
execute. For this type of command, the compiler would keep trying to execute a
command until successful. If there is a problem, this operation can take long or
too long. You can specify how long the compiler should wait to try executing the
command, again.
The OleDbCommand .CommandTimeOut property allows you to
specify the time to wait before trying to execute a command. The default value
of this property is 30 (seconds). If you want a different value, assign it to
your OleDbCommand variable.
To allow you to specify the type of command
you want to perform, the OleDbCommand class is equipped with the CommandType
property, which is based on the CommandType enumeration.
The CommandType enumeration has three members: StoredProcedure,
TableDirect, and Text. For a OleDbCommand object, the default value
is Text.
A data command is used to initiate an action to perform on a
database. To read data of a database, one of the objects you can use is called a
data reader. With a data reader, the compiler reads the first value, then moves to the
second value, then moves to the third
value, and so on. One of the particularities of a data reader is that, once it visits
a value, reads it, and moves to the next value, the compiler cannot refer to the
previous value. This can be illustrated as follows:
To support data readers, the .NET Framework provides, for a
Microsoft SQL Server
database, a class named OleDbDataReader. To get a data reader, you can declare a
variable of type OleDbDataReader.
This class does not have a constructor. This means that, to use it, you must
(directly) specify where it would read its data.
To provide data to the reader,
the OleDbCommand class is equipped with the ExecuteReader() method
that is overloaded with two versions. The simplest version of this method uses
the following syntax:
Public Function ExecuteReader As SqlDataReader
Before using a data reader, you should first
create a command that would specify how data would be acquired. Once the data is
read, you can pass it to the data reader by assigning the result of a call to
a OleDbCommand .ExecuteReader() method to a SqlDataReader object.
Once data is supplied to the reader, you can access it, one
value at a time, from top to bottom. To access data that the reader acquired,
you can call its Read() method whose syntax is:
Public Overrides Function Read As Boolean
As mentioned already, the Read() method simply reads a
value and moves on. When reading the values, the data reader reads one value at a time and moves to the next.
|
Introduction to ADO.NET
Subscribe to:
Post Comments (Atom)
Related Scripting
No comments:
Post a Comment