How can we connect to Microsoft Access, FoxPro, and Oracle etc?
Microsoft provides System.Data.OleDb namespace to communicate with databases like success , Oracle etc. In short, any OLE DB-Compliant database can be connected using System.Data.OldDb namespace.
Note :- Small sample of OLEDB is provided in “WindowsAppOleDb” which uses “Nwind.mdb” in bin directory to display data in Listbox.
Private Sub loadData()
Dim strPath As String
strPath = AppDomain.CurrentDomain.BaseDirectory
Dim objOLEDBCon As New OleDbConnection(“Provider=Microsoft.Jet.OLEDB.4.0;Data Source =” & strPath & “Nwind.mdb”)
Dim objOLEDBCommand As OleDbCommand
Dim objOLEDBReader As OleDbDataReader
objOLEDBCommand = New OleDbCommand(“Select FirstName from Employees”)
objOLEDBCommand.Connection = objOLEDBCon
objOLEDBReader = objOLEDBCommand.ExecuteReader()
Do While objOLEDBReader.Read()
Catch ex As Exception
The main heart is the “Load data ()” method which actually loads the data in list box.
Note:- This source code has the connectionstring hard coded in the program itself which is not a good programming practice. For windows application the best place to store connectionstring is “App.config”. Also note that “AppDomain.CurrentDomain.BaseDirectory” function gives the current path of the running exe which is “BIN” and the MDB file is in that directory. Also note that the final block which executes irrespective that there is error or not. Thus ensuring that all the connection to the datastore is freed. Its best practice to put all clean up statements in finally block thus ensuring that the resources are deallocated properly.
How do we connect to SQL SERVER, which namespace do we use?
Below is the code, after the code we will try to understand the same in a more detailed manner. For this sample, we will also need a SQL Table setup, which I have imported, using the DTS wizard.
Private Sub LoadData()
‘ note :- with and end with makes your code more readable
Dim strConnectionString As String
Dim objConnection As New SqlConnection
Dim objCommand As New SqlCommand
Dim objReader As SqlDataReader
‘ this gets the connectionstring from the app.config file.
‘ note if this gives error see where the MDB file is stored in your pc and point to thastrConnectionString = AppSettings.Item(“ConnectionString”)
‘ take the connectiostring and initialize the connection object
.ConnectionString = strConnectionString
objCommand = New SqlCommand(“Select FirstName from Employees”)
.Connection = objConnection
objReader = .ExecuteReader()
‘ looping through the reader to fill the list box
Do While objReader.Read()
Catch ex As Exception
Note:- The above code is provided in CD in folder WindowsAppSqlClient”. Comments in the code do explain a lot but we will again iterate through the whole code later. “LoadData” is the main method which loads the data from SQL SERVER. Before running this code you have to install SQL SERVER in your machine. As we are dealing with SQLCLIENT we need to setup database in SQL SERVER. For this sample I have imported access “Nwind.mdb” in “SampleAccessDatabase” folder in CD in to SQlSERVER. Depending on computer you will also have to change the connectionstring in Web.config file.
For setting up the sample SQL table, we can use the DTS import wizard to import the table. See the below figure which is using data source as Microsoft Access. While importing the database author had, give the database name as “Employees”.
Figure: - 9.2 Loading “Nwind.mdb” in SQL SERVER for the sample
Figure :- 9.3 Load only the Employee table.
To make it simple we will only import the employee table as that is the only thing needed in our sample code.
Figure: - 9.4 View of loaded Employee table
Now from interview point of view definitely you are not going to say the whole source code, which is given in the book. Interviewer expects only the broader answer of what are the steps needed to connect to SQL SERVER. For fundamental sake author has explained the whole source code. In short, you have to explain the “Load Data” method in broader way. Following are the steps to connect to SQL SERVER:-
• First imports the namespace “System.Data.SqlClient”.
• Create a connection object as shown in “Load Data” method.
.Connection String = strConnectionString
• Create the command object with the SQL. Also, assign the created connection object to command object and execute the reader.
ObjCommand = New SqlCommand (“Select First Name from Employees”)
.Connection = objConnection
Breeder = .Execute Reader ()
• Finally loop through the reader and fill the list box. If old VB programmers are expecting the move next command it is replaced by Read () which returns true if there is any data to be read. If the .Read () return is false that means that it’s end of data reader and there is no more data to be read.
Do while objReader.Read ()
lstData.Items.Add (objReader.Item (“First Name”))
• Do not forget to close the connection object.
Note:- In “LoadData” you will see that connectionstring is stored in Web.config file and is loaded using “AppSettings.Item(“ConnectionString”)”. While running this sample live on your database do not forget to change this connectionstring accordingly to your machine name and SQL SERVER or else the source code will not run.