In earlier chapters I told you about 2/3-tier architecture. Now I’m going to show you an example that how VB can be connected to an Oracle database! The database connectivity is easy. There’s very little trouble on your part. First of all, you need to know what ODBC is. ODBC stands for ‘Open Data Base Connectivity’. It is technology using which any program can ‘talk’ to any database.
Microsoft’s latest invention on database connection is ADO (ActiveX Data Object). The functionality of ADO is represented schematically by the following diagram.
Figure 13-1
All you required to do is just to set up the connection. The rest (the real difficult part) will be automatically handled! Before ADO.NET, Microsoft used other ODBC technology namely ADO, DAO and RDO. But since they are not used nowadays, I’m not going to tell you anything about them.
Using ADO/OLE DB, you can access all database (be in Oracle, SQL Server, Sybase etc.) in a uniform way (this uniform access is the main aim of ADO.NET)!
Don’t conclude that only VB can be used as front end. Any application that supports COM can be used as front end with ADO.
In the front end, usually following operations are carried out by the end user.
1. Viewing of data.
2. Insertion, updation and deletion of data.
3. Validation of data before insertion into database.
4. Creation of report.
.NET front end
client ADO OLE DB Database
In our further discussion, we shall use the terms ‘client’ for the front end and
‘server’ for the database in backend.
In this chapter I shall show you how to connect to a database and create front-end forms using VB.NET. You may definitely ask why I am giving the example with only VB.NET. The answer is that VB is still one of the most popular programming language – moreover, the VB.NET code can be easily converted to C# code.
There are several books available in the market on how to connect VB.NET with databases and develop forms. Most of them, unfortunately, lack the overall clarity. So, here I shall try to present the contents in such a way that you can very quickly implement this in your projects.
The VB.NET front-end form mainly can be of following types:
1. Single record format – only master table data
2. Multi record (grid) format – only one table (master) 3. Single record master and multi record (grid) detail 4. Multi record master (grid) and multi record detail (grid)
5. More than one level of master detail data (single record format multiple grids)
Here I shall give example of case 2 and 3. From that, you should be able to create forms of other cases as well.
You might be prompted to use VB’s Data Form Wizard. However, the wizard genereated code has following drawbacks – they are too slow for large data sets (test yourself by querying 50,000 records through wizard generated code the code given below) and they are difficult to modify. Usually, in real life applications, wizard generated codes are not used.
Multi record VB.NET form
In the following example, I present a data grid example in such a manner that it shows – how to show query result from any SQL to data grid, how to update records through data grid, how to connect with various databases (e.g. Oracle, SQL Server etc.)
Figure 13-2 shows how the form looks like.
Create a similar looking form – the menu names will be obvious from code listing. Name of the form is frmUserDefinedQuery.
The name of the query text box is txtQuery and name of the datagrid is dg (as used in the code). The db type combo is cboDbType. Other text box names are obvious from code.
Concentrate on these functions/procedures – ShowSqlResultInDatagrid, code inside mnuQueryConnect, mnuQueryExecute and mnuQueryUpdate. Observe how different connection string has been used for different databases.
Figure 13-2
The following code is written inside this form.
Code 13-1
Imports System.Data
Imports System.Data.OleDb
Public Class frmUserDefinedQuery
Inherits System.Windows.Forms.Form
[Windows Form Designer code] has been omitted for brievity
Dim connstr As String
Dim cnn As System.Data.OleDb.OleDbConnection Dim da As OleDbDataAdapter
Dim ds As DataSet
Dim cmdBld As OleDbCommandBuilder
Public Function ShowSqlResultInDatagrid(ByVal ConnectionDetail As
String, ByVal SqlText As String, ByVal DataGridName As DataGrid) As Integer '0 return means success
Try
da = New OleDbDataAdapter(SqlText, connstr) cmdBld = New OleDbCommandBuilder(da)
ds = New DataSet()
da.Fill(ds, "UserDefinedQuery")
DataGridName.SetDataBinding(ds, "UserDefinedQuery") DataGridName.CaptionText = "Result of query"
Return 0
Catch q As System.InvalidOperationException
MessageBox.Show(q.Message & vbCrLf & "Try to connect first before executing query.", "Alert", MessageBoxButtons.OK,
MessageBoxIcon.Error) Return 1
Catch x As System.Exception
MessageBox.Show(x.Message, "Error", MessageBoxButtons.OK, MessageBoxIcon.Error)
Return 2 End Try End Function
Private Sub frmUserDefinedQuery_Load(ByVal sender As Object, ByVal e As System.EventArgs) Handles MyBase.Load
Private Sub mnuQueryConnect_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles mnuQueryConnect.Click
Dim db As String ID=" & txtUser.Text & ";Data Source=" & txtDatabase.Text
ElseIf db = "SQL Server" Then 'connect with SQL Server
connstr = "Provider=SQLOLEDB.1;User ID=" & txtUser.Text &
";Pwd=" & txtPwd.Text & ";Initial Catalog=" & txtDatabase.Text & ";Data Source=" & txtServer.Text & ";"
Else
Me.Text = "SQL - " & txtUser.Text & "@" & txtDatabase.Text & "
- " & cboDbtype.Text
Catch x As Exception
stbStatus.Text = x.Message
MessageBox.Show(x.Message, "Error", MessageBoxButtons.OK, MessageBoxIcon.Error)
End Try End Sub
Private Sub cboDbtype_SelectedIndexChanged(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles
cboDbtype.SelectedIndexChanged
Private Sub mnuQueryExecute_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles mnuQueryExecute.Click
If txtQuery.Text = "" Then
MessageBox.Show(x.Message, "Error", MessageBoxButtons.OK, MessageBoxIcon.Error)
End Try End Sub
Private Sub mnuQueryUpdate_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles mnuQueryUpdate.Click
Try
MessageBox.Show(x.Message, "Error", MessageBoxButtons.OK, MessageBoxIcon.Error)
End Try End Sub End Class
This application assumes that database client components are installed and accessible in your computer during runtime (where you will run this code).
There is another catch – using this method, you can update the underlying table only if the table has a primary key.
Master detail VB.NET form
Now we are going to develop a master detail form. Where master record will be single record instace with all navigational controls (first, last, previous, next record and insert/update/delete) and detail records in data grid.
It is assumed that we are connecting to an Oracle database as mkm/mkm@mdb.
The tables we are going to use are – product (master) and price (detail). Not all columns of the table will be used in the example.
Figure 13-3
The form should look like this. The main text boxes are txtProdCode, txtProdName, txtCategory and txtCategoryDesc (non base table field). Though in the example, a tab page is being shown, you should feel free to design in your own way. The datagrid is named as dgD. Menu names will be obvious from the
code. Don’t get fightened by the length of the code, try to comprehened – it is not very difficult.
Code 13-2
Imports System.Data.OleDb
Public Class frmProductPrice
Inherits System.Windows.Forms.Form
[Windows Form Designer code] has been omitted for brievity
#Region "Generic common code - but some changes are required "
'***************************************************************
'generic common code, except connection string and sql 'change connection string and SQL as required
'***************************************************************
Dim Conn As OleDbConnection = New
OleDbConnection("Provider=MSDAORA.1;Password=MKM;User ID=MKM;Data Source=MDB")
Dim sql As String = "SELECT * FROM PRODUCT"
Dim da As OleDbDataAdapter = New OleDbDataAdapter(sql, Conn) Dim cmdBld As OleDbCommandBuilder = New OleDbCommandBuilder(da)
Dim ds As DataSet Dim dv As DataView
'***************************************************************
'for detail part of master detail relationship detail data 'change SQL as required
'***************************************************************
Dim sqlD As String = "SELECT PRODCODE, STARTDATE, ENDDATE, BUYPRICE, SELLPRICE FROM PRICE"
Dim daD As OleDbDataAdapter = New OleDbDataAdapter(sqlD, Conn) Dim cmdBldD As OleDbCommandBuilder = New OleDbCommandBuilder(daD) Dim dsD As DataSet
Dim dvD As DataView Dim dr As DataRelation
'***************************************************************
Dim cm As CurrencyManager
Private Sub FillDataSetAndView() ds = New DataSet()
da.Fill(ds, "Product") 'change table name as required
dv = New DataView(ds.Tables("Product")) 'change table name as required
cm = CType(Me.BindingContext(dv), CurrencyManager)
'***************************************************************
'***************************************************************
Dim dtProduct, dtPrice As DataTable dtProduct = ds.Tables("Product") dtPrice = ds.Tables("Price")
Dim dcParent, dcChild As DataColumn
dcParent = ds.Tables("Product").Columns("ProdCode") dcChild = ds.Tables("Price").Columns("ProdCode")
dr = New DataRelation("ProductPrice", dcParent, dcChild) ds.Relations.Add(dr)
dgD.SetDataBinding(ds, "Price") 'change table name as required ShowDetail()
End Sub
#End Region
#Region "Generic common code - no customization required "
'***************************************************************
Private Sub PreviousRecord() cm.Position -= 1
Public Sub FormLoad()
Public Sub NewRecord(ByVal InsertSql As String) Dim intPosition As Integer
Dim objCommand As OleDbCommand = New OleDbCommand() intPosition = cm.Position
objCommand.Connection = Conn
objCommand.CommandText = InsertSql
objCommand.CommandType = CommandType.Text
Try
MessageBox.Show(q.ToString, q.Message, MessageBoxButtons.OK, MessageBoxIcon.Error)
Catch x As Exception
MessageBox.Show(x.ToString, x.Message, MessageBoxButtons.OK, MessageBoxIcon.Error)
Private Sub UpdateRecord(ByVal UpdateSql As String) Dim intPosition As Integer
Dim objCommand As OleDbCommand = New OleDbCommand() intPosition = cm.Position
objCommand.Connection = Conn
objCommand.CommandText = UpdateSql
objCommand.CommandType = CommandType.Text
Try
MessageBox.Show(q.ToString, q.Message, MessageBoxButtons.OK, MessageBoxIcon.Error)
Catch x As System.Exception
MessageBox.Show(x.ToString, x.Message, MessageBoxButtons.OK, MessageBoxIcon.Error)
Finally
Conn.Close()
Private Sub DeleteRecord(ByVal DeleteSql As String) Dim intPosition As Integer
Dim objCommand As OleDbCommand = New OleDbCommand() intPosition = cm.Position
objCommand.Connection = Conn
objCommand.CommandText = DeleteSql
objCommand.CommandType = CommandType.Text
Dim i As Integer
#Region "Form specific changed common code - change method bodies for each form "
'***************************************************************
'Form specific changed common code
'change body of functions/procedures as necessary
'***************************************************************
Private Sub BindFields()
txtProdCode.DataBindings.Clear() txtProdName.DataBindings.Clear() txtCategory.DataBindings.Clear()
txtProdCode.DataBindings.Add("Text", dv, "ProdCode") txtProdName.DataBindings.Add("Text", dv, "ProdName") txtCategory.DataBindings.Add("Text", dv, "Category") End Sub
Public Sub ShowNonBaseTableFields() 'write code
MessageBox.Show(z.ToString, z.Message, MessageBoxButtons.OK, MessageBoxIcon.Error)
MessageBox.Show(x.Message, "Record Save", MessageBoxButtons.OK, MessageBoxIcon.Error)
End Try End Sub
Private Sub ShowDetail()
dvD.RowFilter = "PRODCODE='" & txtProdCode.Text & "'"
dgD.DataSource = dvD End Sub
Private Sub FormatDataGrid()
'***************************************************************
'for formatting data grid
'***************************************************************
Dim GTS As New DataGridTableStyle() GTS.MappingName = "Price"
GTS.BackColor = System.Drawing.Color.GhostWhite
GTS.AlternatingBackColor = System.Drawing.Color.GhostWhite GTS.ForeColor = System.Drawing.Color.MidnightBlue
GTS.GridLineColor = System.Drawing.Color.RoyalBlue GTS.HeaderBackColor = System.Drawing.Color.MidnightBlue
GTS.HeaderForeColor = System.Drawing.Color.White
Private Sub frmProductPrice_Load(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles MyBase.Load
FormatDataGrid() End Sub
Private Sub mnuNavigateFirst_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles mnuNavigateFirst.Click
FirstRecord() End Sub
Private Sub mnuNavigatePrevious_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles mnuNavigatePrevious.Click
PreviousRecord() End Sub
Private Sub mnuNavigateNext_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles mnuNavigateNext.Click
NextRecord() End Sub
Private Sub mnuNavigateLast_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles mnuNavigateLast.Click
LastRecord() End Sub
Private Sub mnuRecordInsert_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles mnuRecordInsert.Click
NewRecord("INSERT INTO PRODUCT(PRODCODE,PRODNAME,CATEGORY) VALUES('" & txtProdCode.Text & "','" & txtProdName.Text & "','" &
txtCategory.Text & "')") End Sub
Private Sub mnuRecordUpdate_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles mnuRecordUpdate.Click
'UpdateRecord("UPDATE PRODUCT SET PRODNAME = '" & txtProdName.Text
& "', CATEGORY='" & txtCategory.Text & "' WHERE PRODCODE = '" &
txtProdCode.Text & "'") SaveRecords() End Sub
Private Sub mnuRecordDelete_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles mnuRecordDelete.Click
DeleteRecord("DELETE FROM PRODUCT WHERE PRODCODE = '" &
txtProdCode.Text & "'") End Sub
Private Sub mnuFileClose_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles mnuFileClose.Click
Me.Close() End Sub
Private Sub txtProdCode_TextChanged(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles txtProdCode.TextChanged
ShowDetail() End Sub
Private Sub mnuFileLoad_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles mnuFileLoad.Click
FormLoad() End Sub
End Class
Well, what’s the problem?
I tried to write the code in as much generic way as possible. Please observe following points.
1. After loading the form, click File – Load to show data in the form.
2. To insert a new record in master table, first write all the values of the record in relevant text boxes (erase shown values by selecting texts in textboxes and pressing Del key) and then click Record – Insert.
3. To delete a record from master, simple click Record – Delete.
4. To update master record, change values in text boxes as necessary and click Record – Update.
5. After doing any insert, update or delete in detail grid, click Record – Update to save changes in database.
6. I also described how you could use data grid formatting.
7. The category description is being read from another table (category), which has a foreign key relationship with master (product) table’s CategoryNo column. The following stored procedure in Oracle database is being accessed in the code.
CREATE OR REPLACE PROCEDURE ShowCategoryDescription (
piCatNo IN NUMBER, poCatDesc OUT VARCHAR2 )
AS BEGIN
SELECT G.DESCRIPTION INTO poCatDesc
FROM CATEGORY G
WHERE G.CATEGORYNO = piCatNo;
EXCEPTION
WHEN OTHERS THEN
poCatDesc := 'N/A';
END;
Don’t worry if you don’t understand the entire code on first round. Read several times to read a grasp of the thing. If the words like dataset, data adapter, connection string appears weird to you – please read a VB.NET basic textbook first!
Happy detailing.
Can you explain a little bit how to implement 3-tier architecture using ADO and COM?
Definitely! Take the example of a CARS table. Suppose we want to increase price of all cars by 10%. Assume that we shall keep this 'business logic' in middle tier.
Clearly the database is the back end. In the front end, we have our client application in VB. Now how do we implement the business logic in middle tier?
Well, we'll write following code in our middle tier, say in a class module (DLL) in VB so it would be a COM component.
1. Open the connection with database 2. Open a client side ADO record set 3. Disconnect the database
4. Write code in ADO syntax to update the ADO record set that is fetched in step 2 above i.e. you first update the records on client side record set
5. Make connection with database again
6. Save the changes made to client side record set into the database using batch update method
In your front end, you'll have to call the above procedure to update your database.
Clearly, you've isolated your business logic in the middle tier using ADO. Also modifying your business logic involves no change in your front or back end.
Moreover, since you adopted ADO syntax in your business logic code, you don’t need to change this code if you change your front end or back end database to some other systems.
How do I call Stored Procedures from VB6?
First I write a stored function and a stored procedure. Next we shall call it from VB6. Please note that these are written in Oracle PL/SQL syntax.
Code 13-3
create or replace function MySP(x in number) return number is begin
return(x*x);
exception
when others then
return 0;
end MySP;
create or replace procedure MySP1(x in number, y out number) is
Write this code in VB6.
Code 13-4
'call a stored function
x = InputBox("Enter value to square")
Set rs = cnn.Execute("select mysp(" & Val(x) & ") from dual", , adCmdText)
MsgBox rs(0).Value
'call a stored procedure Dim cmd As ADODB.Command Set cmd = New ADODB.Command Dim x As Integer
.Parameters.Append .CreateParameter("@Param1", adInteger, adParamInput)
.Parameters.Append .CreateParameter("@Param2", adInteger, adParamOutput)
You can call stored procedures in similar way from VB.NET. It has already been shown in code 13-2.
10 commandments of successful software development – You will start development with software requirements.
You will honor thy users and communicate with them often.
You will not allow unwarranted requirement changes.
You will invest up front in software architecture.
You will not confuse products with standards.
You will recognize and retain your top talent.
You will understand object-oriented technology.
You will design web centric applications and reusable components.
You will plan for change.
You will implement and always adhere to a production acceptance process.
Source: Ref. 17