VB Access SQL Server

VB access to SQL Server discussion ----------- (VB database)

Abstract This paper discusses the common method of Visual Basic Applications to access SQL Server data, which illustrates the internal mechanism of each method and gives a simple instance of each method, and finally compares the performance of each method. Advantage. Introduction Microsoft Visual Basic (referred to as VB) As an object-oriented visual programming tool, it is easy to learn, flexible and easy to expand. Moreover, Microsoft provides an API function set and toolset that communicates with SQL Server. So it is increasingly used as front ends of large company data and client-server applications. Combined with the backed Microsoft SQL Server, VB can provide a robust, high-performance client-server solution. Use Visual Basic as a front-end development language, there are three common methods with the SQL Server interface, namely:  * Data Access Object / Jet  * is ODBC API program  * Use SQL Server Visual Basic Library (VBSQL) to program the DB library API programming  This article will discuss the three ways to manage SQL Server data, and compare the performance and advantages and disadvantages of each method.  Method 1. Data Access Objects / Jetvisual Basic supports subsets of Data Access Objects (DAOS). DAO's method is not a performance-based management client-server, but it does have many advantages. The process of accessing SQL Server using DAOS is as follows: Application Prepare statements and send it to Jet, JET Engine (MASJT200.DL) to optimize queries, load the driver manager and communicate with it, the driver manager (ODBC.DLL) To modify the function of the drive (SQLSRVR.DLL), implement the connection to the data source, translate and submit the SQL statement to SQL Server and return the result. Below is a VB instance for accessing SQL Server with DAOS. 'form declarationsdim mydb as database dim mydynaset as dynaset private suborm_load () SET mydb = OpenDatabase ("", FA | SE, FA | SE, "ODBC; DSN = MyServer; WSID = LCL; Database = SA | ES ") SET mydyNaset = MyDB CreateDynaset (" Select * from customers ") end Sub The above example opens the SALES database in non-exclusive, non-read mode, and retrieves all the fields in the Customers table. The last parameter of the OpenDatabase function is an ODBC connection string parameter, which indicates some of the content you need to know by Microsoft Access to connect to SQL Server. The "DSN" is the data source name, "WSID" is the work station name, "Database" is the database name to be accessed.  Method 2. The idea of ​​using ODBC API program ODBC (Open Database Connectivity is a portable way to access the xenograft database. The public function of the data resource dialog is assembled in a dynamic connection called the Driver Manager (ODBC.DLL). The application calls the function in the Driver Manager, while the driver manager is in turn to the drive (SQLSRVR.DLL) to the server through the drive. Programmatically programmed with an ODBC API, generally use some of the following functions. The commonly used functions are listed below and their features.

 Function function SQLallocenv initializes the ODBC environment, returning the environment handle SQLallocConnect to allocate memory for the connection handle SQLConnect Connect a SQL data resource SQLDRIVERCONNECT Connect a SQL data resource, allow the drive to ask the user SQLALLOCSTMT to assign memory to the user And return the statement handle SQLExecdirect send the SQL statement to the server SQLFETCHADVANCES to the results set (or first line) SQLGETDATA Retrieve data from the specific column of the result set Remarks data SQLFREESTMT Release the resource related to the statement handle Sqldisconnect cut Connection SQLFreeConnect release and connection handle Related Resources SQLFreeEnv Releases Resources Related to Environment Handle  The following code uses some of the above functions first logged in to a server database, and set the statement handle for the subsequent work. Global giHEnv As LongGlobal giHDB As LongGlobal giHStmt As LongDim myResult As integerDim myConnection As SrtingDim myBuff As String * 256Dim myBufflen As IntegerIf SQLA || ocEnv (giHEnv) <> SQL_SUCCESS ThenMsgBox "A || ocssion couldn't happen! "end ififf sql || occonnect (gihenv, gihdb) <> sql_success kilobox" SQL Server COULDNA CONNECT! "end ifmyconnection =" dsn = myserver; uid = | c | pwd =; APP = ODBCTest; WS | D = LCL; DATABASE = sales "myResult = SQLDriverConnect (giHDB, Test, form1.hWnd, myConnection.len (myConnection), myBuff, 256, myBufflen, SQL_DRIVER_COMPLETE_REQUIED) myResult = SQLA || ocStmt (giHDS, giHStmt) myResult = SQLFreeStmt (giHStmt, SQL_COLSE) rsSQL = "Select * from Customers Where City =" Wuhan "" myResult = SQLExecDirect (giHStmt, rsSQL, Len (rsSQL)) 3. VBSQL use the DB API programming library DB library is SQL Server's local API, SQL Server's Visual Basic Library (VBSQL) provides an API for Visual Basic programmers. In a certain sense, VBSQL is the best way to connect the Visual Basic program to SQL Server. VBSQL includes the following three files: VBSQL.vbx contains library functions, with access to important messages and handleable capabilities vbsql.bi includes all constants and variables Description vbsql.hlp Windows help file, using VBSQL guidance  Use When VBSQL, you must add VBSQL.bi to the Visual Basic project file and make sure that the VB program is running with a vbsql.vbx file.  A typical VBSQL dialog, generally used these functions.