VB .Net Programming
Unit-V
Database programming with ADO.NET – Overview of ADO, from ADO to ADO.NET,
Accessing Data using Server Explorer.
Creating Connection,
Command,
Data Adapter and Data Set with OLEDB and SQLDB.
Display Data on data bound controls,
display data on data grid.
Overview of ADO
ADO.NET provides a bridge between the front end controls and the back end database. The ADO.NET objects encapsulate all the data access operations and the controls interact with these objects to display data, thus hiding the details of movement of data.
The following figure shows the ADO.NET objects at a glance:
Creating Connection
The SqlConnection object has a ConnectionString property that is very similar to the one in ADO. You can specify the server name (using the data source, server, address, addr, or network-address keywords), the user ID, the password (using either the password or pwd keyword), and many other options. One thing you do not specify is a driver or provider because the SqlConnection object works with SQL Server only. Assuming you have an Imports System.Data statement (or the equivalent Using statement or Import directive) at the top of your code, the code to make the connection should look similar to that shown here:
Dim sConn As String = "Initial Catalog=Pubs;" & _
"Data Source=localhost;User ID=sa;Password="
Dim dbConnection As New SqlClient.SqlConnection(sConn)
dbConnection.Open()
To set up the command for the command object, you can either pass the command text into the constructor of the command object or set the CommandText property to the command text. For example, the code here uses the constructor to set the command text:
Dim sConn As String = "Initial Catalog=Pubs;" & _
Data Source=localhost;User ID=sa;Password="
Dim sSQL As String = "Select * from Authors"
Dim dbConnection As New SqlClient.SqlConnection(sConn)
Dim authorCommand As New _
SqlClient.SqlCommand(sSQL, dbConnection)
In this code snippet, the connection object is created the same as before. But now, a SQL statement is also created. The SqlCommand object is instantiated and passed both the SQL statement and the SqlConnection object.
Retrieving Forward-only Records
In ADO, it was common to open a forward-only (or forward-scrolling) cursor. The forward only cursor was often named a fire-hose cursor because the data was blasted down as though
through a fire hose. This type of cursor is not fully-scrollable. Instead, you can walk through one record at a time, which is exactly what you need for many Web applications. For example, the code in FIGURE 1 illustrates using a fire-hose cursor in ADO.
Dim cn As ADODB.Connection
Set cn = New ADODB.Connection
Dim rs As ADODB.Recordset
Set rs = New ADODB.Recordset
Dim sSQL As String
sSQL = "Select * from Authors"
cn.Open "provider=SQLOLEDB;data source=localhost;" & _
"initial catalog=Pubs;user id=sa;password=;"
rs.Open sSQL, cn, adOpenForwardOnly, adLockReadOnly
In ADO, you need to use the regular Recordset object to handle a fire-hose cursor. In contrast, ADO.NET provides a specialized object for handling this type of cursor: the SqlDataReader (or OleDbDataReader) object. This data-reader object s sole purpose is to make a single pass through the records coming back from a database.
To create a datareader, you must use the ExecuteReader method of the command object. The code in FIGURE 2 shows how you would create a SqlDataReader in ADO.NET.
Dim sConn As String = "Initial Catalog=Pubs;" & _
"Data Source=localhost;User ID=sa;Password="
Dim sSQL As String = "Select * from Authors"
Dim cn As New SqlClient.SqlConnection(sConn)
Dim authorCommand As New _
SqlClient.SqlCommand(sSQL, cn)
Dim drAuthor As SqlClient.SqlDataReader
cn.Open()
drAuthor = authorCommand.ExecuteReader
drAuthor.Read()
Notice that in ADO.NET, you need to call the Read method of the datareader object in order to begin accessing the data. ADO places you on the first record of a recordset, but this is not the case with ADO.NET.
Inserting a Record with ADO and ADO.NET
If you want to insert a record in ADO or ADO.NET using the command object (which is different from using an updateable Recordset or DataSet object) you will use the connection and command objects. FIGURE 4 shows the code in ADO, and FIGURE 5 shows the equivalent code in ADO.NET.
Dim cn As ADODB.Connection
Dim sSQL As String
Set cn = New ADODB.Connection
cn.Open "provider=SQLOLEDB;data source=localhost;" & _
"initial catalog=Pubs;user id=sa;password=;"
sSQL = "Insert Into Authors " & _
"(au_id,au_lname, au_fname,contract) values " & _
"('111-11-1111','Utley','Craig',1)"
cn.Execute sSQL, , adExecuteNoRecords + adCmdText
Dim sConn As String = "Initial Catalog=Pubs;" & _
"Data Source=localhost;User ID=sa;Password="
Dim sSQL As String = "Insert Into Authors " & _
"(au_id,au_lname,au_fname,contract) values " & _
"('111-11-1111','Utley','Craig',1)"
Dim cn As New SqlClient.SqlConnection(sConn)
Dim insertAuthor As New SqlClient.SqlCommand(sSQL, cn)
cn.Open()
insertAuthor.ExecuteNonQuery()
Command
There are two command objects: SqlCommand and OleDbCommand. Like ADO, the command object is used to issue SQL commands or call stored procedures. Unlike ADO, however, there are multiple execute methods available. The four execute methods of the command objects are:
1) ExecuteReader This method returns records from the database into a datareader in a forward-only method, much like the forward-only cursor in ADO.
2) ExecuteNonQuery This method executes statements that do not return records. For example, you would use this method for inserts, updates, deletes, and most Data Definition Language statements, such as CREATE TABLE, CREATE INDEX, and so forth.
3) ExecuteScalar This method is used to return a single value, like the count when you issue a SQL statement such as SELECT COUNT(*) FROM AUTHORS.
4) ExecuteXmlReader This method returns data into an XmlReader object. The SQL statement will have used the For XML clause found in SQL Server.
Data Adapter and Data Set with OLEDB and SQLDB.
The DataSet Object
One of the most fundamental changes from ADO to ADO.NET is the addition of the DataSet object. The DataSet object is inherently disconnected. In fact, a DataSet object has no knowledge of underlying data sources. Instead, the dataset represents data, stored in one or more tables, all in memory. This is basically an in-memory database.
You can work with a dataset from your application just as if it was a database. You can insert, update, and delete records. The tables in the DataSet object can have constraints defined for uniqueness. Relationships with foreign key constraints can be defined, also. This means you can enforce at least some of your referential constraints on this in-memory database structure.
By being completely disconnected from the actual data source, the DataSet object allows you to define a schema in any way you see fit. For example, you could combine data from multiple, heterogeneous databases into one logical, in-memory schema. Then, your application would have a more consistent data store from which to pull.
In addition to storing data, the dataset can be updated, and, at any point, you can re synchronize the data in the dataset with the underlying data sources. This synchronization is not done by the DataSet object itself because it has no knowledge of the underlying data sources. Instead, another object, the DataAdapter object, handles synchronization.
The DataAdapter Object
The dataadapter represents one connection object and four command objects (one each for select, insert, update, and delete). Dataadapters are used to connect to the underlying data source, retrieve records, and populate a dataset. The dataadapter also handles synchronizing the changes in a dataset with the underlying data sources.
There are two DataAdapter objects: the SqlDataAdapter and the OleDbDataAdapter. The DataAdapter object contains a Fill method, which is used to retrieve the data and place it into a DataTable object within the dataset. If a datatable does not exist, one will be created.
Display Data on data bound controls
Working with data-bound controls in VS.NET IDE is a fun. You just set couple of properties and you're all set to view, add, and update data using these data-bound controls. Some of these common controls are DataGrid, DataCombo, and ListBox.
In this article, I'll show you how to connect your data source to these controls using ADO.NET components. In my sample applications, I've used SQL Server database. Working with OLE-DB data sources or ODBC data sources are same accept the database connection.
DataGrid Control
Add a datagrid control to a form and write the following code. Change your data source, table and column names if you're using different databases.
Private Sub Form1_Load(ByVal sender As System.Object, ByVal e As System.EventArgs) H andlesMyBase.Load
Dim cmdString As String = "Select * from Employees"
Dim connString As String = "user id=sa; password=; database=northwind; server=MCB" Dim myConnection As SqlConnection = New SqlConnection(connString) Try
' Open connection
myConnection.Open()
Dim da As SqlDataAdapter = New SqlDataAdapter(cmdString, myConnection) Dim dataSet1 As DataSet = New DataSet
da.Fill(dataSet1, "Employees")
DataGrid1.DataSource = dataSet1.DefaultViewManager
Catch ae As SqlException
MessageBox.Show(ae.Message)
End Try
End Sub
ListBox Control
Add a list box control to a form and add the following code to the form load event.
Private Sub Form1_Load(ByVal sender As System.Object, ByVal e As System.EventArgs) H andlesMyBase.Load
Dim cmdString As String = "Select * from Employees"
Dim connString As String = "user id=sa; password=; database=northwind; server=MCB" Dim myConnection As SqlConnection = New SqlConnection(connString) Try
' Open connection
myConnection.Open()
Dim da As SqlDataAdapter = New SqlDataAdapter(cmdString, myConnection) Dim dataSet1 As DataSet = New DataSet
da.Fill(dataSet1, "Employees")
ListBox1.DataSource = dataSet1.DefaultViewManager
ListBox1.DisplayMember = "LastName"
Catch ae As SqlException
MessageBox.Show(ae.Message)
End Try
End Sub
Combo Box
Just add a combo box to a form and add the following code:
Private Sub Form1_Load(ByVal sender As System.Object, ByVal e As System.EventArgs) H andlesMyBase.Load
Dim cmdString As String = "Select * from Employees"
Dim connString As String = "user id=sa; password=; database=northwind; server=MCB" Dim myConnection As SqlConnection = New SqlConnection(connString) Try
' Open connection
myConnection.Open()
Dim da As SqlDataAdapter = New SqlDataAdapter(cmdString, myConnection) Dim dataSet1 As DataSet = New DataSet
da.Fill(dataSet1, "Employees")
Dim DataViewManager1 As DataViewManager = dataSet1.DefaultViewManager ComboBox1.DataSource = DataViewManager1
ComboBox1.DisplayMember = "Employees.FirstName"
Catch ae As SqlException
MessageBox.Show(ae.Message)
End Try
End Sub
Display data on data grid.
∙ Step 1: First is open the Visual Basic, Select File on the menu, then click New and create a new project.
∙ Step 2: Then a New Project Dialog will appear. You can rename your project, depending on what you like to name it. After that click OK
∙ Step 3: Then design your form like this just like what I’ve shown you below. Add a DataGridview and a Button from the toolbox.
∙ Step 4: Go to code view the add this following code above the Public Class Form.
∙ Step 5: Then, Add this following declaration below the Public Class Form.
Dim connString As String = "Provider=Microsoft.ACE.OLEDB.12.0; Data Source=C:\Users\Clive\Documents\Visual Studio 2008\clive
projects\Displaydata\Displaydata\bin\Debug\Items.accdb"
Dim Myconnection As OleDbConnection
Dim dbda As OleDbDataAdapter
Dim dbds As DataSet
Dim tables As DataTableCollection
Dim source As New BindingSource
∙ Step 6: After that, Go back to design and click the Button and add this following codes.
Myconnection.ConnectionString = connString
dbds = New DataSet
tables = dbds.Tables
bda = New OleDbDataAdapter("Select * from [tblitems]", Myconnection) dbda.Fill(dbds, "tblitems")
Dim view As New DataView(tables(0))
source.DataSource = view
DataGridView1.DataSource = view
∙ Step 7: Finally, Click F5 to run the program.
The End
