Visual Basic allows us to work with databases in two ways, visually and code. In Visual Basic, Server Explorer allows us to work with connections across different data sources visually. Lets see how we can do that with Server Explorer. Server Explorer can be viewed by selecting View->Server Explorer from the main menu or by pressing Ctrl+Alt+S on the keyboard. The window that is displayed is the Server Explorer which lets us create and examine data connections. The Image below displays the Server Explorer.
Lets start working with the Server Explorer. We will work with SQL Server, the default provider for .NET. We'll be displaying data from Customers table in sample Northwind database in SQL Server. First, we need to establish a connection to this database. To do that, right-click on the Data Connections icon in Server Explorer and select Add Connection item. Doing that opens the Data Link Properties dialog which allows you to enter the name of the server you want to work along with login name and password. The Data Link properties window can be viewed in the Image below.
Since we are working with a database already on the server, select the option "select the database on the server". Selecting that lists the available databases on the server, select Northwind database from the list. Once you finish selecting the database, click on the Test Connection tab to test the connection. If the connection is successful, the message "Test Connection Succeeded" is displayed. When connection to the database is set, click OK and close the Data Link Properties. Closing the data link properties adds a new Northwind database connection to the Server Explorer and this connection which we created just now is part of the whole Visual Basic environment which can be accessed even when working with other applications. When you expand the connection node ("+" sign), it displays the Tables, Views and Stored Procedures in that Northwind sample database. Expanding the Tables node will display all the tables available in the database. In this example we will work with Customers table to display its data.
Now drag Customers table onto the form from the Server Explorer. Doing that creates SQLConnection1 and SQLDataAdapter1 objects which are the data connection and data adapter objects used to work with data. They are displayed on the component tray. Now we need to generate the dataset that holds data from the data adapter. To do that select Data->Generate DataSet from the main menu or right-click SQLDataAdapter1 object and select generate DataSet menu. Doing that displays the generate Dataset dialogbox like the image below.
Once the dialogbox is displayed, select the radio button with New option to create a new dataset. Make sure Customers table is checked and click OK. Clicking OK adds a dataset, DataSet11 to the component tray and that's the dataset with which we will work. Now, drag a DataGrid from toolbox. We will display Customers table in this data grid. Set the data grid's DataSource property to DataSet11 and it's DataMember property to Customers. Next, we need to fill the dataset with data from the data adapter. The following code does that:
Private Sub Form1_Load(ByVal sender As System.Object, ByVal e As System.EventArgs)_
Handles MyBase.Load
DataSet11.Clear()
SqlDataAdapter1.Fill(DataSet11)
'filling the dataset with the dataadapter's fill method
End Sub
Once the application is executed, Customers table is displayed in the data grid. That's one of the simplest ways of displaying data using the Server Explorer window.
Microsoft Access and Oracle Database
The process is same when working with Oracle or MS Access but with some minor changes. When working with Oracle you need to select Microsoft
OLE DB Provider for Oracle from the Provider tab in the DataLink dialog. You need to enter the appropriate Username and password. The Data Link Properties window can be viewed in the Image below.
When working with MS Access you need to select Microsoft Jet 4.0 OLE DB provider from the Provider tab in DataLink properties.
No comments:
Post a Comment