Solved

About Client-Server Programming

Posted on 1998-11-02
4
282 Views
Last Modified: 2012-06-27
I am a newbie in VB, and I want to know how to
program VB in client-server system.
Any experts can give me a very simple example (in VB source code) of client-server programming in VB?
Also, any VB experts can give me a clue the web site which
give tutorial about Client-Server programming in VB?

Thanks for the kindness,

Daniel
0
Comment
Question by:daniel_c
  • 2
  • 2
4 Comments
 
LVL 4

Accepted Solution

by:
CraigLazar earned 50 total points
Comment Utility
Hi Hope this Helps you

In "Creating Multiuser Applications", you saw how Microsoft Jet and DAO can be used to build applications in a file-server environment. Recall that in a file-server database architecture, there is no database engine on the server — the server merely provides access to network files. The simplicity of the file-server architecture makes it ideal for successfully implementing small to moderate-sized applications.
However, there are certain applications whose needs go beyond the capabilities of file-server database systems. For example, once an application exceeds a certain threshold of concurrent transactions, or needs to take advantage of advanced features such as triggers, a client/server architecture may be better. Additionally, there are many performance, security, data-integrity and administration issues that arise which file-server database systems cannot accommodate. In this situation, you may want to consider using a client/server architecture.
Comparing File-Server and Client/Server
In a file-server database configuration, the Microsoft Jet database engine resides on each user's workstation and the data file containing all the data resides on a server. When your application needs data, the database engine on the workstation processes the query and then issues a request for specific data on the network drive. Because all requested data in the data file must be sent from the server to the workstation, this can generate a significant amount of network traffic.
In a client/server configuration, both the database engine and the data needed by the application reside on the network server. Instead of running a copy of the database engine on each workstation, the database engine runs only on one machine — the server. The workstation simply sends a high-level request in the form of an SQL statement to the server, which retrieves the specific data and sends it back to the client.
How it Works
To better compare how client/server systems differ from file-server systems, consider the following Visual Basic code :
Dim dbs As Database
Dim rstTest As Recordset
Dim strName As String

Set dbs = DBEngine(0).OpenDatabase ("mydb.mdb")
Set rstTest = dbs.OpenRecordset ("mytable")
strName = rstTest!LastName
      
In a file-server database application, the following operations are performed:
1.      Microsoft Jet opens the file mydb.mdb, a very inexpensive operation in terms of time and resources.
2.      The Microsoft Jet DLL running on your workstation looks inside the database file for information about a table named MyTable. This results in several relatively inexpensive disk reads and some memory manipulations on your machine.
3.      Microsoft Jet loads details about the MyTable table into your workstation memory, such as the number of fields, field data types, available indexes, and information about the position of the first data page in the database file.
4.      The executing copy of Microsoft Jet on your workstation reads the actual data from the disk and makes it available to the application.
      
In a client/server system, these operations are performed as follows:
1.      As in steps 1 and 2 in the previous list, Microsoft Jet opens the file mydb.mdb, retrieves some information from it, and then sets up some structures in memory on the local workstation.
2.      When Microsoft Jet loads MyTable, it sees that it is a link to an ODBC database and tries to create a connection to the ODBC server. This is a relatively expensive operation which may take several seconds.
3.      When the ODBC server receives a connection request, it sets aside memory and other resources and prepares for subsequent requests. It then replies with a connection.
4.      Microsoft Jet issues a request to the server for the MyTable data requested in the DAO code.
5.      The server returns the requested data to Microsoft Jet, which makes it available to the client application.
      
The Advantages of Client/Server
For many database applications, the client/server architecture offers several advantages, including the following:
·      More reliable and robust operations because a single database server interacts with the data (instead of multiple copies of a file-share database).
·      Better scalability due to database servers as opposed to file-share databases. If more users are added to the system, it is easier to scale the capabilities of the system to meet the additional requirements.
·      Significantly improved performance of some operations, especially when the user workstations are low-end computers with slow processors and limited RAM. For example, executing a large query may be many times faster on a high-end server than it is on a typical workstation.
·      Reduced network traffic due to the more efficient transmission of data. Only data that the application needs is transferred.
·      Mission-critical features such as transaction logs, sophisticated backup capabilities, redundant disk arrays, and failure recovery tools.
      
Fully understanding the client/server paradigm helps in the successful implementation of remote engine database front ends. Developers who are inexperienced with client/server development can easily make fundamental mistakes that doom their applications to failure or dismal performance. These failures may lead them to erroneously dismiss the client/server concept or the development components used to deploy the system.
For example, designing tables that contain too many columns or that are improperly normalized can negate many of the benefits of the relational database model. Designs that fetch entire tables or create cursors that contain too many rows can also lead to disappointing performance — regardless of the programming model.
Programming front ends for central server systems — especially for systems that access large amounts of data or have dozens to hundreds of users — is very different from programming for flat-file ISAM database systems. When you develop a front end that accesses a remote database, you should implement a carefully designed relational model. Although this is something that you might defer to a later date in an attempt to improve performance, doing so is like trying to replace the basement of your house after you finish adding an upper floor.
Before you make a final decision on which programming model to implement, you should be aware of a number of issues that will impact your design regardless of the model.



To gain access to a remote server, your application must establish a connection. This connection creates a communications link over the network between the server and the client. A handle to this connection is passed back to your application. Once created, the connection provides access to a specific server, to one of the databases on the server, and to any connection-specific temporary objects your application creates on the server.
Using Connection-Specific Temporary Objects
In many cases, temporary objects created on a database server belong to a specific connection.—.in other words, they belong to a specific instance of your application. Examples of these temporary objects include result sets, server-based cursors, work-in-progress save areas, and temporary tables. Once the connection is closed, the server automatically releases the objects. Except for global temporary objects, other connections cannot see or access them as they are only available to a specific instance of your application through a specific connection. Global temporary objects can be created and visible to other users of the database server, but are released and discarded when the last user of the objects has disconnected. If your application depends on the persistence of these objects, be sure to use connection strategies that do not arbitrarily close connections while these objects are still needed.
Establishing Client/Server Connections
Depending on the client/server data access programming model you choose, you can establish a connection to a remote server in code using the means described in the following table:
Programming model      Connection technique

DAO ODBC via Jet      Open an Jet database that has tables attached or linked to an ODBC server database table.
DAO ODBC via Jet      Use a DAO QueryDef object that includes an ODBC SQL pass-through query.
DAO ODBC via Jet      Open a DAO Database object that directly references an ODBC Data Source Name (DSN) or an appropriate ODBC server and driver.
DAO ODBC via Jet      Use the Data control that references an attached table or ODBC DSN.
DAO ODBCDirect      Use the Data control that references an ODBC DSN or an appropriate ODBC server and driver.
DAO ODBCDirect      Open a DAO Connection object that references a DSN or an appropriate ODBC server and driver.
RDO 2.0      Use the RDO OpenConnection method that references an ODBC DSN, or a specific ODBC server and driver.
RDO 2.0      Use the RDO EstablishConnection method that references an ODBC DSN, or a specific ODBC server and driver against a stand-alone RDO Connection object.
RDO 2.0      Use the RemoteData control that references an ODBC DSN, or a specific ODBC server and driver
ODBC API      Create an ODBC connection handle referencing an ODBC DSN using SQLConnect or SQLDriverConnect API functions.
VBSQL      Open a DB-Library connection to a specific Microsoft SQL Server using the VBSQL SqlOpen or SqlOpenConnection API functions.
      
All of these techniques create one or more connections that link the specific instance of your application with the remote server. In some cases, you are limited to a relatively low number of connections — with the Microsoft SQL Server Workstation Edition, 15 or less. In other cases, that number can be relatively high (32,767), as with SQL Server Enterprise Edition. Not all of these approaches will yield the same level of performance. For example, using DAO to open a remote database directly can be very expensive in time and network resources.
Note   Check with your server administrator for the number of available connections. These may be limited by license or resource constraints. Because a single application can create any number of connections, the number of users your remote server supports may be limited by its ability to handle enough connections.
      
Maintaining a constant connection to the remote server may not be necessary for your application, but in some cases losing a connection may mean connection-specific temporary objects are lost. In a multi-user system, connection management becomes even more important because the total number of users connected to a database may be limited by the number of connections each application uses. Depending on the server and number of available connections, you may find it more efficient to leave one or more connections established for the life of your application. In other cases, you may need to open and close connections as quickly as possible to accommodate other users because of a limited number of connection handles. Be sure the programming interface you choose supports a suitable connection strategy.


A cursor is a pointer to a set of data extracted from the database that meets a specific criteria. Cursors are intended to assist users in browsing through a logical set of information, also known as a result set. Read/write cursors also support individual row updates. Cursors can be defined with keysets that point back to base table rows or with actual data rows downloaded to the client computer. Depending on the options used to create the cursor, it can be fully scrollable or it can only permit movement of the current record point toward the end of the cursor.
A Jet dynaset-type Recordset object is an example of a Jet cursor. Cursors are involved in many of the data access strategies described in the Guide to Data Access Objects, and those you may already be familiar with when working with ISAM databases. However, many applications are built without the use of cursors, as they are not always the most optimal way to retrieve data.
Cursors can be built in a variety of ways:
·      With no rows at all.
·      With some or all of the rows in a single table.
·      With some or all of the rows from tables logically joined on common fields.
·      As read-only or updatable at the cursor or field level.
·      As fully scrollable or forward-only scrolling.
·      With the cursor keyset located on the client or server.
·      As buffered, cached, or index keyset.
·      Using keysets or actual data.
·      Having static or dynamic membership.
      
For example, a user could create a cursor that includes selected invoices for a certain customer. Once a cursor is created, users can quickly retrieve and update its rows through use of model-specific cursor Fetch and Update commands. Although each programming model uses a different syntax and a somewhat different approach to implementing cursors, they are all similar in most respects.
You should only request those cursor options that your application needs. Each additional option beyond a static, read-only, forward-only scrolling, unbuffered cursor has a price.—.either in client memory, network load, or performance. In many cases, the default options generate a far more complex and expensive cursor than you actually need.
Generally, cursors do not have the ability to perform bulk updates or deletions on all of their members. In contrast, the database engine.—.available in all of the Visual Basic data access programming models.—.can use the WHERE clause used to specify an equivalent result set and perform bulk operations on its rows.
Improper use of cursors is a root cause of several data access performance problems. When working with ISAM databases, it is not uncommon for front-end applications to open a base table and process each record one-by-one. Because there is often no "database engine" to perform any but the most basic data access operations, your application usually has to take on this role. When working with remote databases, the problems associated with cursor management are exacerbated by network access and shared data constraints. Understanding the role of a database engine, and how to best take advantage of its power, is critical in achieving better performance and program stability.
In virtually all cases, you should perform bulk operations using the database engine. When the number of rows affected begins to impact other users, this type of operation should be deferred to off-peak hours. Use of transactions and more sophisticated locking strategies can also improve overall system performance while executing bulk operations.
SQL is a powerful language for retrieving, manipulating, and aggregating data. Moreover, intelligent servers such as Microsoft SQL Server implement powerful extensions to standard SQL that support even more sophisticated operations. Using stored procedures or views to execute joins or perform data modification on the server will almost invariably be more efficient than retrieving rows to the client computer for local processing.


Once the cursor is created, it can be used to access any of the returned values as requested by the query. Generally, these values are returned in one of three forms:
·      One or more result sets as requested by SELECT statements in the query. These values are returned by positioning the current row pointer to a specific row and referencing the column (field) values.
·      Return Status. Stored procedures always return a return status value that indicates the success or failure of the procedure.
·      Output arguments. Some procedures also return additional arguments in addition to the rowset data.
Using Multiple Result Set Queries
In some SQL dialects, such as Transact SQL (TSQL) in Microsoft SQL Server, multiple SELECT statements can be combined into batches. Batches can contain program-like logic; update, delete, or insert statements,; or other complex code that can vary the number and type of result sets created. In addition, you can create server-based procedures compiled and stored as a part of the database. These stored procedures can contain logic that also varies the number and type of result sets. Because stored procedures can also call other stored procedures, you should code your application so that it passes result sets appropriately.
Without detailed knowledge of the procedure logic, there is no way to tell how many result sets, if any, the procedure will generate. Your code should recognize and handle procedures that return n result sets of indeterminate size and complexity.
All of the Visual Basic data access programming models support management of multiple result sets.—.but in very different ways. The data extracted from a stored procedure is static and cannot be updated. If you can use a WHERE clause to identify specific rows, the individual rows are updatable through an SQL action query.
Managing Arguments Returned from Query Procedures
Stored procedures may return output arguments and parameters in forms other than data row result sets. For example, it is possible to define a single return argument and multiple output parameters with SQL Server stored procedures. All of the programming models support output parameters and return values using a variety of techniques; however, many of these are somewhat cumbersome.
Capturing the Return Value and OUTPUT Parameters
When you execute a remote stored procedure, it returns an overall status to indicate success or failure. In some cases, this is the only way to tell if the procedure succeeded or not. When using RDO and ODBCDirect, the return value and OUTPUT arguments can be passed back to Visual Basic through a parameter whose Direction property is set to rdParamReturnValue, rdParamOutput, or rdParamInputOutput. ODBCDirect also supports similar parameter direction types. To capture these parameters, your query must use the ODBC Call syntax.
For more information   For more information about submitting parameter queries, see "Creating RDO Parameter Queries" in "Using Remote Data Objects and the RemoteData Control."
      
Using Buffered Result Sets
In most cases, it is far more efficient to use a buffered result set instead of an updatable cursor and action queries to make changes to the data as needed. Not all applications need to use cursors to access or update data. Actually, cursors should be one of the last techniques you choose to retrieve data — and then you should choose the lowest impact cursor possible. For example, you should only use a cursor if you need to scroll forward and backward through a result set. Cursors are easier to code, but the costs involved often make your application more difficult to scale.
A buffered result set is generated by a query that returns row data without the overhead of an associated keyset. Use buffered result sets when your application must access data from a remote server and needs to access more than a single row in the result set, or where the number of individually accessible rows in the result set can be limited. Buffered result sets are implemented by creating client-side buffers that range in size from one row to as many rows as the client machine can manage. Because only a subset of the result set keys or data needs to be downloaded, this approach is very fast and has a manageable impact on the client machine's RAM resources.
Buffered result sets are implemented by default in VBSQL and when using forward-only result set cursors in the other interfaces.


Once the cursor is created, it can be used to access any of the returned values as requested by the query. Generally, these values are returned in one of three forms:
·      One or more result sets as requested by SELECT statements in the query. These values are returned by positioning the current row pointer to a specific row and referencing the column (field) values.
·      Return Status. Stored procedures always return a return status value that indicates the success or failure of the procedure.
·      Output arguments. Some procedures also return additional arguments in addition to the rowset data.
Using Multiple Result Set Queries
In some SQL dialects, such as Transact SQL (TSQL) in Microsoft SQL Server, multiple SELECT statements can be combined into batches. Batches can contain program-like logic; update, delete, or insert statements,; or other complex code that can vary the number and type of result sets created. In addition, you can create server-based procedures compiled and stored as a part of the database. These stored procedures can contain logic that also varies the number and type of result sets. Because stored procedures can also call other stored procedures, you should code your application so that it passes result sets appropriately.
Without detailed knowledge of the procedure logic, there is no way to tell how many result sets, if any, the procedure will generate. Your code should recognize and handle procedures that return n result sets of indeterminate size and complexity.
All of the Visual Basic data access programming models support management of multiple result sets.—.but in very different ways. The data extracted from a stored procedure is static and cannot be updated. If you can use a WHERE clause to identify specific rows, the individual rows are updatable through an SQL action query.
Managing Arguments Returned from Query Procedures
Stored procedures may return output arguments and parameters in forms other than data row result sets. For example, it is possible to define a single return argument and multiple output parameters with SQL Server stored procedures. All of the programming models support output parameters and return values using a variety of techniques; however, many of these are somewhat cumbersome.
Capturing the Return Value and OUTPUT Parameters
When you execute a remote stored procedure, it returns an overall status to indicate success or failure. In some cases, this is the only way to tell if the procedure succeeded or not. When using RDO and ODBCDirect, the return value and OUTPUT arguments can be passed back to Visual Basic through a parameter whose Direction property is set to rdParamReturnValue, rdParamOutput, or rdParamInputOutput. ODBCDirect also supports similar parameter direction types. To capture these parameters, your query must use the ODBC Call syntax.
For more information   For more information about submitting parameter queries, see "Creating RDO Parameter Queries" in "Using Remote Data Objects and the RemoteData Control."
      
Using Buffered Result Sets
In most cases, it is far more efficient to use a buffered result set instead of an updatable cursor and action queries to make changes to the data as needed. Not all applications need to use cursors to access or update data. Actually, cursors should be one of the last techniques you choose to retrieve data — and then you should choose the lowest impact cursor possible. For example, you should only use a cursor if you need to scroll forward and backward through a result set. Cursors are easier to code, but the costs involved often make your application more difficult to scale.
A buffered result set is generated by a query that returns row data without the overhead of an associated keyset. Use buffered result sets when your application must access data from a remote server and needs to access more than a single row in the result set, or where the number of individually accessible rows in the result set can be limited. Buffered result sets are implemented by creating client-side buffers that range in size from one row to as many rows as the client machine can manage. Because only a subset of the result set keys or data needs to be downloaded, this approach is very fast and has a manageable impact on the client machine's RAM resources.
Buffered result sets are implemented by default in VBSQL and when using forward-only result set cursors in the other interfaces.

0
 
LVL 7

Author Comment

by:daniel_c
Comment Utility
Hmmm looks like you are very expert in this client-server programming.
Any recommended website for me to learn about this???

Thanks anyway for your kindness
0
 
LVL 4

Expert Comment

by:CraigLazar
Comment Utility
Hi daniel_c
thanx for the complimant
On the web i have found it quite hard to find actual information on topics u want to learn more about . Most of the stuff you either have to subscribe and purchase the information .
However look at microsofts site under development and sometimes u can pick up white papers on certain areas . Look at vbonline (ships with VB5 and i presume 6 aswell) . Or go to the book store and there are numerous books on client server applications . I would also go to amazon.com they have a great selection

hope it helps mate
cheers
craig
0
 
LVL 7

Author Comment

by:daniel_c
Comment Utility
great!!!
thanks Craig!!!
0

Featured Post

Maximize Your Threat Intelligence Reporting

Reporting is one of the most important and least talked about aspects of a world-class threat intelligence program. Here’s how to do it right.

Join & Write a Comment

There are many ways to remove duplicate entries in an SQL or Access database. Most make you temporarily insert an ID field, make a temp table and copy data back and forth, and/or are slow. Here is an easy way in VB6 using ADO to remove duplicate row…
Article by: Martin
Here are a few simple, working, games that you can use as-is or as the basis for your own games. Tic-Tac-Toe This is one of the simplest of all games.   The game allows for a choice of who goes first and keeps track of the number of wins for…
Get people started with the process of using Access VBA to control Excel using automation, Microsoft Access can control other applications. An example is the ability to programmatically talk to Excel. Using automation, an Access application can laun…
This lesson covers basic error handling code in Microsoft Excel using VBA. This is the first lesson in a 3-part series that uses code to loop through an Excel spreadsheet in VBA and then fix errors, taking advantage of error handling code. This l…

762 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

12 Experts available now in Live!

Get 1:1 Help Now