ODBC Applications: Writing Good Code in .NET Integrated 2d Data Matrix barcode in .NET ODBC Applications: Writing Good Code

ODBC Applications: Writing Good Code generate, create ecc200 none in .net projects iPhone OS a single connection; oth ECC200 for .NET er requests on the same connection must wait for the preceding request to complete. Sybase ASE, Microsoft SQL Server, and MySQL are examples of streaming protocol databases.

In contrast, when connecting to cursor-based protocol databases, the driver tells the database server when to work and how much data to retrieve. Several cursors can use the network, each working in small slices of time. Oracle and DB2 are examples of cursor-based protocol databases.

For a more detailed explanation of streaming versus cursor-based protocol databases, see One Connection for Multiple Statements, page 16. The advantage of using one connection for multiple statements is that it reduces the overhead of establishing multiple connections, while allowing multiple statements to access the database. The overhead is reduced on both the database server and client machines.

The disadvantage is that the application may have to wait to execute a statement until the single connection is available. See One Connection for Multiple Statements, page 16, for guidelines on using this model of connection management. Obtaining Database and Driver Information Efficiently Remember that creating a connection is one of the most performance-expensive operations that an application performs.

. Performance Tip Because of the performance hit your application pays for opening connections, once your application is connected, you should avoid establishing additional connections to gather information about the driver and the database, such as supported data types or database versions, using. SQLGetInfo and SQLGetTyp Data Matrix ECC200 for .NET eInfo. For example, some applications estab-.

lish a connection and th en call a routine in a separate DLL or shared library that reconnects and gathers information about the driver and the database.. How often do databases c visual .net datamatrix 2d barcode hange their supported data types or database version between connections Because this type of information typically doesn t change between connections and isn t a large amount of information to store, you may want to retrieve and cache the information so the application can access it later..

Managing Transactions Managing Transactions To ensure data integrity , all statements in a transaction are committed or rolled back as a unit. For example, when you use a computer to transfer money from one bank account to another, the request involves a transaction updating values stored in the database for both accounts. If all parts of that unit of work succeed, the transaction is committed.

If any part of that unit of work fails, the transaction is rolled back. Use the guidelines in this section to help you manage transactions more efficiently. Managing Commits in Transactions Committing (and rolling back) transactions is slow because of the disk I/O and, potentially, the number of network round trips required.

What does a commit actually involve The database must write to disk every modification made by a transaction to the database. This is usually a sequential write to a journal file (or log); nevertheless, it involves expensive disk I/O. In ODBC, the default transaction commit mode is auto-commit.

In autocommit mode, a commit is performed for every SQL statement that requires a request to the database (Insert, Update, Delete, and Select statements). When auto-commit mode is used, your application doesn t control when database work is committed. In fact, commits commonly occur when there s actually no real work to commit.

Some databases, such as DB2, don t support auto-commit mode. For these databases, the database driver, by default, sends a commit request to the database after every successful operation (SQL statement). The commit request equates to a network round trip between the driver and the database.

The round trip to the database occurs even though the application didn t request the commit and even if the operation made no changes to the database. For example, the driver makes a network round trip even when a Select statement is executed. Let s look at the following ODBC code, which doesn t turn off auto-commit mode.

Comments in the code show when commits occur if the driver or the database performs commits automatically:. /* For conciseness, this VS .NET datamatrix 2d barcode code omits error checking */ /* Allocate a statement handle */ rc = SQLAllocHandle(SQL_HANDLE_STMT, hdbc, &hstmt);.
Copyright © . All rights reserved.