Introduction

Initializing or establishing a connection is a common pattern in many areas of Windows programming. If you want to start drawing on a window, you need first to obtain a device context by calling GetDC or CreateDC, and when you are done, you close it by calling ReleaseDC or DeleteDC. If you want to start a conversation between a client and a server using Windows sockets then you establish a connection first, and when you are done, you close it. In a similar pattern, if you want to query a database for certain records then you need to establish the connection, and when you are done, you close it. In this article, I will show you how to start a connection using ATL OLE DB consumer classes and how to obtain a session so that you can use it to query or edit the database.

To initialize the connection to a database using an OLE DB provider, you need two ATL classes: CDataSource and CSession. To use them, include the atldbcli.h file:

#include <atldbcli.h>

Why two classes? It is because you can create many sessions on the same connection. Once you open the data source using the Open or OpenFromInitializationString methods, you most likely want to start a session too. Through this session, you may create transactions and query or edit the database. This is actually needed even if you are using the schema classes. There is only one overload for the OpenFromInitializationString method, and nine overloads for the Open method.

• HRESULT OpenFromInitializationString(LPCOLESTR szInitializationString);

I will explain all of the Open method overloads, but will mostly use the first one in case I need to display the Data Link Properties dialog to allow the user to select an OLE DB provider and its connection properties.

• HRESULT Open(HWND hWnd = GetActiveWindow(), DBPROMPTOPTIONS dwPromptOptions = DBPROMPTOPTIONS_WIZARDSHEET);
• HRESULT Open(const CLSID& clsid, DBPROPSET* pPropSet = NULL, ULONG nPropertySets=1);
• HRESULT Open(const CLSID& clsid, LPCTSTR pName, LPCTSTR pUserName = NULL, LPCTSTR pPassword = NULL, long nInitMode = 0);
• HRESULT Open(LPCSTR szProgID, DBPROPSET* pPropSet = NULL, ULONG nPropertySets=1);
• HRESULT Open(LPCWSTR szProgID, LPCTSTR pName, LPCTSTR pUserName = NULL, LPCTSTR pPassword = NULL, long nInitMode = 0);
• HRESULT Open(LPCWSTR szProgID, DBPROPSET* pPropSet = NULL, ULONG nPropertySets=1);
• HRESULT Open(LPCSTR szProgID, LPCTSTR pName, LPCTSTR pUserName = NULL, LPCTSTR pPassword = NULL, long nInitMode = 0);
• HRESULT Open(const CEnumerator& enumerator, DBPROPSET* pPropSet = NULL, ULONG nPropertySets=1);
• HRESULT Open(const CEnumerator& enumerator, LPCTSTR pName, LPCTSTR pUserName = NULL, LPCTSTR pPassword = NULL, long nInitMode = 0);

Note that ATL consumer classes are based on COM, and this means:

1. We have to call CoInitialize before using these classes, probably at InitInstance, and then similarly call CoUninitialize later at the program in ExitInstance.
2. We will need to deal with HRESULT return values as indicators for success or failure.

Enumerating OLE DB Providers using CEnumerator class

CEnumerator oProviders;

HRESULT hr = oProviders.Open( );
if(SUCCEEDED(hr))
{
    // The following macro is to initialize
    // the conversion routines
    USES_CONVERSION;

    while(oProviders.MoveNext( ) == S_OK)
    {
// Now you have the provider name
// in oProviders.m_szName and description
// in oProviders.m_szDescription

#ifdef _UNICODE
        TRACE(oProviders.m_szName);
        TRACE(L"\n");
#else
        TRACE(W2A(oProviders.m_szName));
        TRACE("\n");
#endif
    }
    oProviders.Close( );
}

CEnumerator class provides the means to enumerate all OLE DB providers installed on the system. It is a convenient way of providing a custom interface for the user to select a provider and its connection properties. If you need to enumerate OLE DB providers using the standard dialog then just call the Open method of the CDataSource class without any parameters.

hr = ds.Open( );

The Standard OLE DB Data Link Properties Dialog

Note that m_szName of CEnumerator is usually the ProgID of the provider which may be used to open the connection in several overloads of the Open method.

The code works in both Unicode and ANSI builds of the application because of the #ifdef used. The USES_CONVERSION macro is used to initialize several conversion routines such as A2W which converts an ANSI string to Unicode, and W2A which does the opposite. m_szName is a wide string pointer which can be used directly in Unicode functions, but we have to convert it to ANSI using W2A routine when working in single byte builds.

The algorithm described in the example above is a typical way of ATL consumer classes. We first declare a class which actually contains an accessor with several data members such as m_szName and m_szDescription. When we call the MoveNext method, these data members are filled with the appropriate values from the next available row until MoveNext returns DB_S_ENDOFROWSET and exits the loop. We will find this pattern common when we start retrieving records from the database.

Description	ProgID	ClassID	Database
IBM OLE DB Provider for DB2	IBMDADB2.1	{1E29B6C3-8EC6-11D2-AF46-000629B3CD56}	DB2
Microsoft Jet 4.0 OLE DB Provider	Microsoft.Jet.OLEDB.4.0	{DEE35070-506B-11CF-B1AA-00AA00B8DE95}	Microsoft Access
Microsoft OLE DB Provider for SQL Server	SQLOLEDB	{0C7FF16C-38E3-11d0-97AB-00C04FC2AD98}	Microsoft SQL Server
MySQL.OLEDB Provider	MySQLProv	{C86FB69E-3664-11D2-A112-00104BD15372}	MySQL
Oracle Provider for OLE DB	OraOLEDB.Oracle	{3F63C36E-51A3-11D2-BB7D-00C04FA30080}	Oracle
Microsoft OLE DB Provider for Oracle	MSDAORA	{E8CC4CBE-FDFF-11D0-B865-00A0C9081C1D}	Oracle

Please refer to ProvDefs.h on the accompanying disk for typedefs of the ProgIDs and ClassIDs of the above mentioned providers.

Using DBPROPSET structure and CDBPropSet class

The DBPROPSET structure is used to pass certain properties about the connection and, as we will see later, rowsets retrieved. A DBPROPSET structure contains an array of DBPROPs which resemble the desired properties. To use the structure, we first create an array of DBPROPs and assign their values. Then we attach this array to a DBPROPSET structure. If the properties belong to different property sets, then we combine the same set properties into one DBPROPSET. At the end, we will have an array of property sets. In several of the CDataSource::Open method overloads, we see a pointer to a DBPROPSET structure. This structure is filled with the connection properties and passed along with the count of the property sets. The following code segment clarifies our point:

Collapse

// the macro for conversions
USES_CONVERSION;

// build the property array of 3 properties
DBPROP rgProperties[3];
// property 1: the datasource
rgProperties[0].colid = DB_NULLID;
rgProperties[0].dwOptions = DBPROPOPTIONS_REQUIRED;
rgProperties[0].dwPropertyID = DBPROP_INIT_DATASOURCE;
rgProperties[0].dwStatus = 0;
rgProperties[0].vValue.vt = VT_BSTR;
rgProperties[0].vValue.bstrVal = ::SysAllocString(W2COLE(L"server_name"));
// property 2: the database name
rgProperties[1].colid = DB_NULLID;
rgProperties[1].dwOptions = DBPROPOPTIONS_REQUIRED;
rgProperties[1].dwPropertyID = DBPROP_INIT_CATALOG;
rgProperties[1].dwStatus = 0;
rgProperties[1].vValue.vt = VT_BSTR;
rgProperties[1].vValue.bstrVal = ::SysAllocString(W2COLE(L"Northwind"));
// property 3: the user id
rgProperties[2].colid = DB_NULLID;
rgProperties[2].dwOptions = DBPROPOPTIONS_REQUIRED;
rgProperties[2].dwPropertyID = DBPROP_AUTH_USERID;
rgProperties[2].dwStatus = 0;
rgProperties[2].vValue.vt = VT_BSTR;
rgProperties[2].vValue.bstrVal = ::SysAllocString(W2COLE(L"sa"));

// combine the 3 properties into a property set structure
DBPROPSET rgPropertySet[1];
rgPropertySet[0].cProperties = 3;
rgPropertySet[0].guidPropertySet = DBRGPROPERTIESET_DBINIT;
rgPropertySet[0].rgProperties = rgProperties;

// now open the SQL Server database connection
HRESULT hr = m_ds.Open(L"SQLOLEDB", rgPropertySet, 1);
if(SUCCEEDED(hr))
    m_ds.Close();

DBPROPOPTIONS_REQUIRED is specified to indicate that this property is required to establish the connection. If not, the provider returns an error and sets dwStatus to DBPROPSTATUS_NOTSUPPORTED. The other value for dwOptions is DBPROPOPTIONS_OPTIONAL which indicates that this property is optional and the provider should not fail if the property is not met.

We could have a two property set array instead of one. In each item, we define a set of related properties as below:

Collapse

// the macro for conversions
USES_CONVERSION;

// build the property array of 3 properties
DBPROP rgProperties1[3];
// property 1: the datasource
rgProperties1[0].colid = DB_NULLID;
rgProperties1[0].dwOptions = DBPROPOPTIONS_REQUIRED;
rgProperties1[0].dwPropertyID = DBPROP_INIT_DATASOURCE;
rgProperties1[0].dwStatus = 0;
rgProperties1[0].vValue.vt = VT_BSTR;
rgProperties1[0].vValue.bstrVal = ::SysAllocString(W2COLE(L"server_name"));
// property 2: the database name
rgProperties1[1].colid = DB_NULLID;
rgProperties1[1].dwOptions = DBPROPOPTIONS_REQUIRED;
rgProperties1[1].dwPropertyID = DBPROP_INIT_CATALOG;
rgProperties1[1].dwStatus = 0;
rgProperties1[1].vValue.vt = VT_BSTR;
rgProperties1[1].vValue.bstrVal = ::SysAllocString(W2COLE(L"Northwind"));
// property 3: the user id
rgProperties1[2].colid = DB_NULLID;
rgProperties1[2].dwOptions = DBPROPOPTIONS_REQUIRED;
rgProperties1[2].dwPropertyID = DBPROP_AUTH_USERID;
rgProperties1[2].dwStatus = 0;
rgProperties1[2].vValue.vt = VT_BSTR;
rgProperties1[2].vValue.bstrVal = ::SysAllocString(W2COLE(L"sa"));

// build another property array of 1 property
DBPROP rgProperties2[1];
// Property 1: application name
rgProperties2[0].colid = DB_NULLID;
rgProperties2[0].dwOptions = DBPROPOPTIONS_REQUIRED;
rgProperties2[0].dwPropertyID = SSPROP_INIT_APPNAME;
rgProperties2[0].dwStatus = 0;
rgProperties2[0].vValue.vt = VT_BSTR;
rgProperties2[0].vValue.bstrVal = 
   ::SysAllocString(W2COLE(L"my application title"));

// combine the 3 properties into a property set structure
DBPROPSET rgPropertySet[2];
rgPropertySet[0].cProperties = 3;
rgPropertySet[0].guidPropertySet = DBPROPSET_DBINIT;
rgPropertySet[0].rgProperties = rgProperties1;
rgPropertySet[1].cProperties = 1;
rgPropertySet[1].guidPropertySet = DBPROPSET_SQLSERVERDBINIT;
rgPropertySet[1].rgProperties = rgProperties2;

// now open the SQL Server database connection
HRESULT hr = m_ds.Open(L"SQLOLEDB", rgPropertySet, 2);
if(SUCCEEDED(hr))
    m_ds.Close();

What is this application name property doing? Open “master” database and look for “sysprocesses” table before calling m_ds.Close method. Open this table and you will find “my application title” listed in the program_name field. This means that it is one of the processes that is currently opening an SQL Server database.

The setting of properties in the previous examples may look a little complex. I don’t like working with structures in this way. Therefore, I will present an ATL class that will cut the learning curve of dealing with properties and property sets. This class is CDBPropSet. Look how the above code will look like using this class:

CDBPropSet rgPropertySet[1] = {DBPROPSET_DBINIT};

rgPropertySet[0].AddProperty(DBPROP_INIT_DATASOURCE, L"server_name");
rgPropertySet[0].AddProperty(DBPROP_INIT_CATALOG, L"Northwind");
rgPropertySet[0].AddProperty(DBPROP_AUTH_USERID, L"sa");

hr = m_ds.Open("SQLOLEDB", rgPropertySet, 1);

That’s it. AddProperty method takes care of adding properties of different types and initializing them. Passing the pointer to the class is the same as passing the pointer to the DBPROPSET structure because the class is derived from that structure.

Connection related properties

There are many connection related properties. I will not list all of them here but for a full list, please refer to the MSDN Library. The following table shows the most important properties for our purpose. These properties are applicable in part or in full to most relational databases.

Some of the terms have been broken with a '-' (hyphen) to avoid browser scrolling.

Property	Property Set	Type	Applicable to
DBPROP_INIT_DATASOURCE	DBPROPSET_DBINIT	VT_BSTR	Microsoft.Jet.OLEDB.4.0 MSDAORA MySQLProv OraOLEDB.Oracle SQLOLEDB IBMDADB2.1
DBPROP_INIT_CATALOG	DBPROPSET_DBINIT	VT_BSTR	SQLOLEDB
DBPROP_AUTH_USERID	DBPROPSET_DBINIT	VT_BSTR	Microsoft.Jet.OLEDB.4.0 MSDAORA MySQLProv OraOLEDB.Oracle SQLOLEDB IBMDADB2.1
DBPROP_AUTH_PASSWORD	DBPROPSET_DBINIT	VT_BSTR	Microsoft.Jet.OLEDB.4.0 MSDAORA MySQLProv OraOLEDB.Oracle SQLOLEDB IBMDADB2.1
DBPROP_AUTH_INTEGRATED	DBPROPSET_DBINIT	VT_BSTR	MySQLProv SQLOLEDB
DBPROP_AUTH_MASK_PASSWORD	DBPROPSET_DBINIT	VT_BOOL	MySQLProv SQLOLEDB
DBPROP_AU-TH_ENCRYPT_PASSWORD	DBPROPSET_DBINIT	VT_BOOL	MySQLProv SQLOLEDB
DBPROP_INIT_ASYNCH	DBPROPSET_DBINIT	VT_I4	SQLOLEDB
DBPROP_INIT_GENERALTIMEOUT	DBPROPSET_DBINIT	VT_I4	SQLOLEDB
DBPROP_INIT_LOCATION	DBPROPSET_DBINIT	VT_BSTR	MySQLProv
DBPROP_INIT_MODE	DBPROPSET_DBINIT	VT_I4	MySQLProv Microsoft.Jet.OLEDB.4.0 IBMDADB2.1
DBPROP_INIT_HWND	DBPROPSET_DBINIT	VT_I8	Microsoft.Jet.OLEDB.4.0 MSDAORA MySQLProv OraOLEDB.Oracle SQLOLEDB
DBPROP_INIT_PROMPT	DBPROPSET_DBINIT	VT_I2	Microsoft.Jet.OLEDB.4.0 MSDAORA MySQLProv OraOLEDB.Oracle SQLOLEDB
DBPROP_INIT_TIMEOUT	DBPROPSET_DBINIT	VT_I4	MySQLProv SQLOLEDB

• DBPROP_INIT_DATASOURCE

  The server name, or the database name. If a server name, then DBPROP_INIT_CATALOG carries the database name.

• DBPROP_INIT_CATALOG

  The database name in case DBPROP_INIT_DATASOURCE carries the server name.

• DBPROP_AUTH_USERID

  The login user ID.

• DBPROP_AUTH_PASSWORD

  The login password.

• DBPROP_AUTH_INTEGRATED

  Indicates the security system used whether it is a user ID-password or SSPI for the Windows security system which uses the currently logged-in user credentials.

• DBPROP_AUTH_MASK_PASSWORD

  Send the password to the provider in a masked form.

• DBPROP_AUTH_ENCRYPT_PASSWORD

  Send the password to the provider in an encrypted form.

• DBPROP_INIT_ASYNC

  Indicates whether the connection is synchronous or asynchronous. If asynchronous, the Open method returns immediately and the connection is performed in the background. In synchronous mode, the open attempt does not return until success or failure.

• DBPROP_INIT_GENERALTIMEOUT

  The default timeout period that is used when obtaining rowsets and executing commands.

• DBPROP_INIT_LOCATION

  The location of the database.

• DBPROP_INIT_MODE

  The sharing mode of the opened database. This is usually applicable to file databases such as Microsoft Access.

• DBPROP_INIT_HWND

  The window handle of the prompt dialog parent.

• DBPROP_INIT_PROMPT

  Indicates whether to prompt the user for missing connection information or not.

• DBPROP_INIT_TIMEOUT

  The database connection attempt timeout period in seconds. If the time specified in this property expires and the connection attempt is still in process, the Open method returns unsuccessfully.

CDataSource::Open method

Since we have described all the components of establishing a connection, we can now describe the CDataSource::Open method overloads. I will list a code example on how to use each overload, but each time a different database type is used. OLE DB is a powerful framework because once you open a connection to a database of your choice, you can start querying it or executing commands in a similar way among all databases.

• HRESULT Open(HWND hWnd = GetActiveWindow(), DBPROMPTOPTIONS dwPromptOptions = DBPROMPTOPTIONS_WIZARDSHEET);

  This is the only method that allows you to open the standard Data Link Properties dialog to specify the connection parameters including the provider visually. hWnd is the window handle of the standard dialog parent which defaults to the currently active window. The second parameter is to set the style of the dialog. The default is DBPROMPTOPTIONS_WIZARDSHEET which applies to a wizard style property page. You may specify DBPROMPTOPTIONS_PROPERTYSHEET for a property sheet style.

  HRESULT hr = m_ds.Open( );
  if(SUCCEEDED(hr))
      m_ds.Close( );

  The rest of the overloads are a mix-and-match among a set of parameters. Those parameters are:

  • clsid: the ClassID of the provider to be used
  • pPropSet: the property set array
  • nPropertySets: the property set array count
  • pName: the server name or database name
  • pUserName: the login user ID
  • pPassword: the login password
  • nInitMode: the sharing mode for file databases such as Microsoft Access
  • szProgID: the ProgID of the provider to be used
  • enumerator: the CEnumerator class used to specify the provider
• HRESULT Open(const CLSID& clsid, DBPROPSET* pPropSet = NULL, ULONG nPropertySets=1);

  // Open Microsoft SQL Server database named "Northwind" installed on a 
  // server name "server_name" using the currently Windows logged-in user 
  // credentials
  CDBPropSet rgPropertySet[1] = {DBPROPSET_DBINIT};
  
  rgPropertySet[0].AddProperty(DBPROP_INIT_DATASOURCE, L"server_name");
  rgPropertySet[0].AddProperty(DBPROP_INIT_CATALOG, L"Northwind");
  rgPropertySet[0].AddProperty(DBPROP_AUTH_INTEGRATED, L"SSPI");
  
  CLSID clsid = {0xc7ff16cL,0x38e3,0x11d0,
                {0x97,0xab,0x0,0xc0,0x4f,0xc2,0xad,0x98}};
  hr = m_ds.Open(clsid, rgPropertySet, 1);

• HRESULT Open(const CLSID& clsid, LPCTSTR pName, LPCTSTR pUserName = NULL, LPCTSTR pPassword = NULL, long nInitMode = 0);

  // Open a Microsoft Access database with
  // path C:\Program Files\Microsoft
  //      Office\Office10\Samples\Northwind.mdb exclusively
  
  CLSID clsid = {0xdee35070L,0x506b,0x11cf, 
                {0xb1,0xaa,0x0,0xaa,0x0,0xb8,0xde,0x95}};
  hr = m_ds.Open(clsid, _T("C:\\Program Files\\Microsoft Office" 
       "\\Office10\\Samples\\Northwind.mdb"), 
       NULL, NULL, DB_MODE_SHARE_EXCLUSIVE);

• HRESULT Open(LPCSTR szProgID, DBPROPSET* pPropSet = NULL, ULONG nPropertySets=1);

  // Open an Oracle database using Microsoft OLE DB Provider for Oracle
  // Open OraDB1 database with user name SYS and password 
  // change_on_install
  // Note that it is using the ANSI string for the provider
  CDBPropSet rgPropertySet[1] = {DBPROPSET_DBINIT};
  
  rgPropertySet[0].AddProperty(DBPROP_INIT_DATASOURCE, L"OraDB1");
  rgPropertySet[0].AddProperty(DBPROP_AUTH_USERID, L"SYS");
  rgPropertySet[0].AddProperty(DBPROP_AUTH_PASSWORD, L"change_on_install");
  
  hr = m_ds.Open("MSDAORA", rgPropertySet, 1);

• HRESULT Open(LPCWSTR szProgID, LPCTSTR pName, LPCTSTR pUserName = NULL, LPCTSTR pPassword = NULL, long nInitMode = 0);

  // Open an Oracle database using Oracle Provider for OLE DB
  // Open OraDB1 database with user name SYS and password 
  // change_on_install
  // Note that it is using the UNICODE string for the provider ProgID
  
  hr = m_ds.Open(L"OraOLEDB.Oracle", _T("OraDB1"), 
                 _T("SYS"), _T("change_on_install"));

• HRESULT Open(LPCWSTR szProgID, DBPROPSET* pPropSet = NULL, ULONG nPropertySets=1);

  // Open an MySQL database using a location "localhost", a database 
  // name of "mysql", a user id of "root"
  
  CDBPropSet rgPropertySet[1] = {DBPROPSET_DBINIT};
  
  rgPropertySet[0].AddProperty(DBPROP_INIT_LOCATION, L"localhost");
  rgPropertySet[0].AddProperty(DBPROP_INIT_DATASOURCE, L"mysql");
  rgPropertySet[0].AddProperty(DBPROP_AUTH_USERID, L"root");
  
  hr = m_ds.Open("MySQLProv", rgPropertySet, 1);

• HRESULT Open(LPCSTR szProgID, LPCTSTR pName, LPCTSTR pUserName = NULL, LPCTSTR pPassword = NULL, long nInitMode = 0);

  // Open a DB2 database named TOOLSDB with dbadmin user id and password
  hr = m_ds.Open("IBMDADB2.1", "TOOLSDB", "dbadmin", "dbadmin");

• HRESULT Open(const CEnumerator& enumerator, DBPROPSET* pPropSet = NULL, ULONG nPropertySets=1);
  Collapse

  // Open Northwind SQL Server database in "server_name" using SSPI 
  // Integrated security
  CEnumerator oProviders;
  
  CDBPropSet rgPropertySet[1] = {DBPROPSET_DBINIT};
  
  rgPropertySet[0].AddProperty(DBPROP_INIT_DATASOURCE, L"server_name");
  rgPropertySet[0].AddProperty(DBPROP_INIT_CATALOG, L"Northwind");
  rgPropertySet[0].AddProperty(DBPROP_AUTH_INTEGRATED, L"SSPI");
  
  hr = oProviders.Open( );
  if(SUCCEEDED(hr))
  {
      // The following macro is to initialize the conversion routines
      USES_CONVERSION;
  
      while((hr = oProviders.MoveNext( )) == S_OK)
      {
  #ifdef _UNICODE
          if(lstrcmpi(oProviders.m_szName, L"SQLOLEDB") == 0)
  #else
          if(lstcmpi(W2A(oProviders.m_szName), "SQLOLEDB") == 0)
  #endif
          {
              hr = m_ds.Open(oProviders, rgPropertySet, 1);
              break;
          }            
      }
      oProviders.Close( );
  }

• HRESULT Open(const CEnumerator& enumerator, LPCTSTR pName, LPCTSTR pUserName = NULL, LPCTSTR pPassword = NULL, long nInitMode = 0);

  // Open Northwind Access database exclusively
  CEnumerator oProviders;
  
  hr = oProviders.Open( );
  if(SUCCEEDED(hr))
  {
      // The following macro is to initialize
      // the conversion routines
      USES_CONVERSION;
  
      while((hr = oProviders.MoveNext( )) == S_OK)
      {
  #ifdef _UNICODE
          if(lstrcmpi(oProviders.m_szName, 
             L" Microsoft.Jet.OLEDB.4.0") == 0)
  #else
          if(lstcmpi(W2A(oProviders.m_szName), 
                  " Microsoft.Jet.OLEDB.4.0") == 0)
  #endif
          {
              hr = m_ds.Open(oProviders, T("C:\\Program Files\\" 
                   "Microsoft Office\\Office10\\Samples\\Northwind.mdb"), 
                   NULL, NULL, DB_MODE_SHARE_EXCLUSIVE);
              break;
          }            
      }
      oProviders.Close( );
  }

I intentionally went through each overload to demonstrate the connection to different types of databases.

OpenFromInitializationString method

HRESULT OpenFromInitializationString(LPCOLESTR szInitializationString, 
                                             bool fPromptForInfo = false);

The first parameter is the connection string which specifies all the properties to perform the connection including the provider ProgID. The string should be supplied in a wide format. A2W and W2COLE functions may be used to obtain the wide format of an ANSI string. The second parameter indicates whether to prompt the user for the missing information or not. The default is not to prompt and the connection attempt just fails. The connection string must be in a certain format where key-value pairs are separated by a semicolon. The key is the property name and the value is its value. The following table shows the property names and their corresponding property IDs:

Property ID	Property Keyword
Provider ProgID	“Provider”
DBPROP_INIT_DATASOURCE	“Data Source”
DBPROP_INIT_LOCATION	“Location”
DBPROP_INIT_CATALOG	“Initial Catalog”
DBPROP_AUTH_USERID	“User Id”
DBPROP_AUTH_PASSWORD	“Password”
DBPROP_AUTH_INTEGRATED	“Integrated Security”
DBPROP_INIT_HWND	“Window Handle”
DBPROP_INIT_PROMPT	“Prompt”
DBPROP_INIT_TIMEOUT	“Connect Timeout”

The following shows correct connection strings:

// Open an Oracle database using Microsoft Provider
Provider=MSDAORA;Data Source=OraDB1;User Id=SYS;Password=change_on_install

// Open an MySQL database
Provider=MySQLProv;Location=localhost;Data Source=mysql;User Id=root

// Open a Microsoft Access database
Provider=Microsoft.Jet.OLEDB.4.0;Data Source=C:\Program Files\
             Microsoft Office\Office 10\Samples\Northwind.mdb

So far, we have seen how to open a database. To close it, simply call the Close method of CDataSource. Opening a database is a time consuming task, so unless you specifically need to close the connection after a query, you should open the database at program load and create the CDataSource and CSession members of the main window or application class and use this same connection to query or edit the database throughout the application.

CSession class

Now, as we have the connection open, we are ready to create a session to be able to work with the database. The CSession class handles the opening of sessions and creating transactions. To open a session, use the CSession::Open method.

HRESULT Open(const CDataSource& ds);

It takes one parameter which is a reference to the open data source. For example:

CDataSource ds;
CSession session;
HRESULT hr = ds.OpenFromInitializationString(L"Provider=MySQLProv;" 
             "Location=localhost;Data Source=mysql;User Id=root");

if(SUCCEEDED(hr))
{
    hr = session.Open(ds);
    if(SUCCEEDED(hr))
    {
        // The session is now open
        // use it to query or edit the database

        session.Close();
    }
    ds.Close();
}

As shown in the example, the Close method should be called to close the session.

The most important characteristic of a session is the ability to create transactions. A transaction is a set of one or more commands executed on the database with the necessity to either commit all the commands or discard all of them. For example, if we want to make changes to the Orders table and then add an entry in the history log table, then we need to make sure that the sequence is fully executed and not just updating the Orders table without updating the history log due to an interruption in the process. Therefore, we wrap the sequence within a transaction and no changes are persisted to the database until all the commands are executed. Therefore, there are three methods to handle transactions in the session object. StartTransaction begins the transaction process. Then the user has to either call Commit method to actually persist the changes to the database, or Abort to discard any changes since StartTransaction was called. If neither Commit nor Abort were called and then another transaction was started on the same session, then the new transaction is considered nested, and a call to Commit later will commit both transactions. The following shows a draft of the process.

hr = m_session.Open(m_ds);
hr = m_session.StartTransaction( );
…. Do update 1
…. Do update 2
…. Do update n
m_session.Commit( );

Error Handling

As we have seen so far, all methods return HRESULTs. We may check if this return value is a success indicator by using the SUCCEEDED(hr) macro. If we want to check for a failure then we use the FAILED(hr) macro.

hr = m_ds.Open( );
if(SUCCEEDED(hr))
{
    // The connection was opened
}
else
{
    // The connection failed
}

A useful little feature in Visual Studio is the ability to write the variable name in the Watch window to show its value at debug time. If we type hr then we can see its value. Moreover, if we type hr,hr then we get the constant name for the error code. The following figure shows E_FAIL instead of the plain number as the value of hr:

This is useful in many cases of debugging the failure of opening a connection or executing a command, but what if we needed a more detailed description for the error occurred. I will discuss three methods to obtain a description of the error.

Method 1: IErrorInfo interface

An OLE DB application may indicate that an error has occurred by calling the SetErrorInfo API method. The consumer application can check for this error through GetErrorInfo. GetErrorInfo returns a pointer to an IErrorInfo interface. By calling the IErrorInfo::GetDescription method, the user can get an error description. The following code snippet shows how to do that:

IErrorInfo* pErrInfo;
HRESULT hr = ::GetErrorInfo(0, &pErrInfo);
if(SUCCEEDED(hr))
{
    BSTR bstrErrDescription;
    pErrInfo->GetDescription(&bstrErrDescription);

    CString strDescription = bstrErrDescription;
    AfxMessageBox(LPCTSTR(strDescription);

    pErrInfo->Release();
    ::SysFreeString(bstrErrDescription);
}

GetDescription fills a BSTR variable. There are many ways to convert this type of a string to ANSI or Unicode versions. One way is just simply to assign it to a CString. Another way is to use the OLE2T method. So, I will leave it to the user. When finishing, we need to do a little cleanup by calling Release on the interface pointer and SysFreeString to free the BSTR returned from GetDescription.

Method 2: CDBErrorInfo class

USES_CONVERSION;
CDBErrorInfo einfo;
BSTR bstrDescription;
ULONG nRecords = 0;
HRESULT hr = einfo.GetErrorRecords(&nRecords);
if(SUCCEEDED(hr) && nRecords > 0)
{
LCID lcid = GetSystemDefaultLCID();
    for(ULONG nIndex = 0; nIndex < nRecords; nIndex++)
    {
        hr = einfo.GetAllErrorInfo(nIndex, lcid, &bstrDescription);
        if(SUCCEEDED(hr))
        {
            AfxMessageBox(OLE2T(bstrDescription));
            SysFreeString(bstrDescription);
        }
    }
}

As shown in the code, GetErrorRecords is called to fill the CDBErrorInfo with the error array (named records). We loop through the records and call GetAllErrorInfo on each record to obtain the description. This is another way of doing it and a similar method is implemented in the AtlTraceErrorRecords function which is described in method 3.

Method 3: AtlTraceErrorRecords

In case you wanted to see the resulting errors in the debugger window when returning from a query, you may just call AtlTraceErrorRecords. It uses the algorithm described in method 2 to trace the resulting errors. You may just call this method without going through the hassle of writing the code to get that information.

Creating Databases on the fly

You probably had cases when you needed to create the database for the user when he/she logs in the first time of using your application, or maybe restore the database upon failure by re-creating it programmatically. Fortunately, there is a method to do that for many database types. Some databases like Oracle won’t just let you create a database by issuing a CREATE DATABASE method at least through the OLE DB Provider as it requires many steps to do that, but others may allow you to create the database by just executing a few lines of code. I will describe the methods for some databases and apologize for not covering all of them.

Creating a Microsoft Access database programmatically

I suggest two approaches for creating a Microsoft Access database programmatically:

1. This may seem unusual but it does work. Create a blank Microsoft Access database and then copy and rename it whenever you want to create a new blank database. The following code snippet shows how to do that. Assume that the blank database copy is named db.mdb:

   BOOL CreateAccessDatabase(LPCTSTR szTemplatePath, 
                              LPCTSTR szNewDatabasePath)
   {
       return CopyFile(szTemplatePath, szNewDatabasePath, TRUE);
   }

   szTemplatePath is the path and name for the master copy database and szNewDatabasePath is the new path and name for the created database.

2. The second approach is to use the CreateDataSource method of the IDBDataSourceAdmin interface. This interface is implemented in Microsoft.Jet.OLEDB.4.0 provider and can be used to create or destroy a data source explicitly. The following function shows how to use this interface to create a blank .mdb file at a path specified in the szDatabasePath parameter. This method is explained in a Knowledge Base article, but I tried here to avoid using interface pointers as much as possible and use ATL classes instead.
   Collapse

   BOOL CreateAccessDatabase(LPCTSTR szDatabasePath)
   {
       CDataSource ds;
       IDBDataSourceAdmin* pIDBDataSourceAdmin = NULL;
   
       CLSID clsid = {0xdee35070L,0x506b,0x11cf, 
                     {0xb1,0xaa,0x0,0xaa,0x0,0xb8,0xde,0x95}};
       HRESULT hr = CoCreateInstance(clsid, NULL, CLSCTX_INPROC_SERVER, 
                    __uuidof(IDBInitialize), (void**)&ds.m_spInit);
       if (FAILED(hr))
           return FALSE;
   
       USES_CONVERSION;
   
       CDBPropSet rgPropertySet(DBPROPSET_DBINIT);
       rgPropertySet.AddProperty(DBPROP_INIT_DATASOURCE, 
                                 T2BSTR(szDatabasePath));
   
       hr = ds.m_spInit->QueryInterface(IID_IDBDataSourceAdmin, 
           (void**)&pIDBDataSourceAdmin);
       if(FAILED(hr))
       {
           ds.Close();
           return FALSE;
       }
   
       hr = pIDBDataSourceAdmin->CreateDataSource(1, 
            &rgPropertySet, NULL, IID_NULL, NULL);
       if(FAILED(hr))
       {
           pIDBDataSourceAdmin->Release();
           ds.Close();
           return FALSE;
       }
   
       pIDBDataSourceAdmin->Release();
   
       ds.Close();
   
       return TRUE;
   }

Creating a MySQL database programmatically

I will try to create a MySQL database by issuing a "CREATE DATABASE" command through the context of the default database named "mysql" which is setup automatically upon the installation of MySQL. I will use the CCommand class to do that, but will postpone the discussion of this class. The following method creates a MySQL database at a location specified in the szLocation parameter and with a name passed in the szDatabaseName parameter. Also, we need the User ID and Password to open a connection to the mysql database first.

Collapse

BOOL CreateMySQLDatabase(LPCTSTR szLocation, 
       LPCTSTR szDatabaseName, LPCTSTR szUserId, LPCTSTR szPassword)
{
    USES_CONVERSION;
    
    CDBPropSet rgPropertySet[1] = {DBPROPSET_DBINIT};
    rgPropertySet[0].AddProperty(DBPROP_INIT_LOCATION, T2BSTR(szLocation));
    rgPropertySet[0].AddProperty(DBPROP_INIT_DATASOURCE, T2BSTR(_T("mysql")));
    rgPropertySet[0].AddProperty(DBPROP_AUTH_USERID, T2BSTR(szUserId));
    rgPropertySet[0].AddProperty(DBPROP_AUTH_PASSWORD, T2BSTR(szPassword));

    CDataSource ds;
    HRESULT hr = ds.Open(_T("MySQLProv"), rgPropertySet, 1);
    if(FAILED(hr))
        return FALSE;

    CSession session;
    hr = session.Open(ds);
    if(FAILED(hr))
    {
        ds.Close();
        return FALSE;
    }

    CCommand<CNoAccessor, CNoRowset> cmd;
    CString strCommand;
    strCommand.Format(_T("CREATE DATABASE '%s';"), szDatabaseName);
    hr = cmd.Open(session, LPCTSTR(strCommand), 
                  NULL, NULL, DBGUID_DBSQL, false);
    if(FAILED(hr))
    {
        session.Close();
        ds.Close();
        return FALSE;
    }

    session.Close();
    ds.Close();

    return TRUE;
}

Creating a Microsoft SQL Server database programmatically

In a similar method of creating a MySQL database, we can open the "master" database and issue a "CREATE DATABASE" command through it. The difference is that we specify a server location as the data source and a database name as the catalog. The following method does just that:

Collapse

BOOL CreateSQLServerDatabase(LPCTSTR szServerName, 
            LPCTSTR szDatabaseName, LPCTSTR szUserId, LPCTSTR szPassword)
{
    USES_CONVERSION;
    
    CDBPropSet rgPropertySet[1] = {DBPROPSET_DBINIT};
    rgPropertySet[0].AddProperty(DBPROP_INIT_CATALOG, T2BSTR(_T("master")));
    rgPropertySet[0].AddProperty(DBPROP_INIT_DATASOURCE, T2BSTR(szServerName));
    rgPropertySet[0].AddProperty(DBPROP_AUTH_USERID, T2BSTR(szUserId));
    rgPropertySet[0].AddProperty(DBPROP_AUTH_PASSWORD, T2BSTR(szPassword));

    CDataSource ds;
    HRESULT hr = ds.Open(_T("SQLOLEDB"), rgPropertySet, 1);
    if(FAILED(hr))
        return FALSE;

    CSession session;
    hr = session.Open(ds);
    if(FAILED(hr))
    {
        ds.Close();
        return FALSE;
    }

    CCommand<CNoAccessor, CNoRowset> cmd;
    CString strCommand;
    strCommand.Format(_T("CREATE DATABASE '%s';"), szDatabaseName);
    hr = cmd.Open(session, LPCTSTR(strCommand), NULL, NULL, DBGUID_DBSQL, false);
    if(FAILED(hr))
    {
        session.Close();
        ds.Close();
        return FALSE;
    }

    session.Close();
    ds.Close();

    return TRUE;
}

We skipped a few issues such as the Integrated Security property in case the database of MySQL or Microsoft SQL Server need SSPI authentication. It is as simple as adding a boolean property DBPROP_INIT_INTEGRATED to be able to open the mysql or master databases.

Summary

Now we can open a connection to many major types of databases and start a session to do the work. We have seen also examples of creating databases programmatically.

About emadns

Emad has started programming since the DOS time using BASIC. He kept trying things out until he settled down with Visual C++. He has a firm belief in the power of C++. Although he worked with ASP.net and VB.net in a Web project, he still prefers C++ for desktop applications even for database programming. Emad is ready for any freelance project and can be reached at: emadns@yahoo.com Click here to view emadns's online profile.

strConnection = _T("Driver={Microsoft dBASE Driver (*.dbf)};DriverID=277;"
                   "Dbq=c:\\DatabasePath;");

CString strQuery = _T("Select Name, Address From Clients.dbf");

strConnection = _T("Driver={Microsoft Excel Driver (*.xls)};DriverId=790;"
    bq=C:\\DatabasePath\\DBSpreadSheet.xls;DefaultDir=c:\\databasepath;");

strConnection = _T("Driver={Microsoft Text Driver (*.txt; *.csv)};"
        "Dbq=C:\\DatabasePath\\;Extensions=asc,csv,tab,txt;");

CString strQuery = _T("Select Name, Address From Clients.csv");

strConnection = _T("Driver={Microsoft Visual Foxpro Driver};UID=;"
    ourceType=DBC;SourceDB=C:\\DatabasePath\\MyDatabase.dbc;Exclusive=No");

strConnection = _T("Driver={Microsoft Visual Foxpro Driver};UID=;"
    "SourceType=DBF;SourceDB=C:\\DatabasePath\\MyDatabase.dbc;Exclusive=No");

strConnection = _T("Driver={Microsoft Access Driver (*.mdb)};"
        "Dbq=c:\\DatabasePath\\dbaccess.mdb;Uid=;Pwd=;");

strConnection = _T("Driver={Microsoft Access Driver (*.mdb)};"
        "Dbq=C:\\VC Projects\\ADO\\Samples\\AdoTest\\dbTestSecurity.mdb;"
        "SystemDB=C:\\Program Files\\Microsoft Office\\Office\\SYSTEM.mdw;"
        "Uid=Carlos Antollini;Pwd=carlos");

strConnection = _T("Driver={Microsoft Access Driver (*.mdb)};"
        "Dbq=C:\\VC Projects\\ADO\\Samples\\AdoTest\\dbTestSecurity.mdb;"
        "SystemDB=C:\\Program Files\\Microsoft Office\\Office\\SYSTEM.mdw;");
if(pDB.Open(strConnection, "DatabaseUser", "DatabasePass"))
{
    DoSomething();
    pDB.Close();
}

strConnection = _T("Driver={Microsoft Access Driver (*.mdb)};"
        "Dbq=c:\\DatabasePath\dbaccess.mdb;Exclusive=1;");

strConnection = _T("Driver={SQL Server};Server=MyServerName;"
        "Trusted_Connection=no;"
        "Database=MyDatabaseName;Uid=MyUserName;Pwd=MyPassword;");

strConnection = _T("Driver={SQL Server};Server=MyServerName;"
     "Database=myDatabaseName;Uid=;Pwd=;");

strConnection = _T("Driver={SQL Server};Server=MyServerName;"
    "Database=MyDatabaseName;Trusted_Connection=yes;");

strConnection = _T("Driver={SQL Server};Server=(local);"
        "Database=MyDatabaseName;Uid=MyUserName;Pwd=MyPassword;");

strConnection = _T("Driver={SQL Server};Server=130.120.110.001;"
     "Address=130.120.110.001,1052;Network=dbmssocn;Database=MyDatabaseName;"
     "Uid=myUsername;Pwd=myPassword;");

strConnect = _T("Driver={Microsoft ODBC for Oracle};Server=OracleServer.world;"
        "Uid=MyUsername;Pwd=MyPassword;");

strConnect = _T("Driver={Microsoft ODBC Driver for Oracle};"
     "ConnectString=OracleServer.world;Uid=myUsername;Pwd=myPassword;");

strConnect = _T("Driver={MySQL ODBC 3.51 Driver};Server=localhost;"
     "Database=MyDatabase;User=MyUserName;Password=MyPassword;Option=4;");

strConnect = _T("Driver={mySQL ODBC 3.51 Driver};Server=MyRemoteHost;"
     "Port=3306;Option=4;Database=MyDatabase;Uid=MyUsername;Pwd=MyPassword;");

strConnect = _T("Driver={Client Access ODBC Driver (32-bit)};System=myAS400;"
      "Uid=myUsername;Pwd=myPassword;");

strConnect = _T("Driver={Sybase System 10};Srvr=MyServerName;Uid=MyUsername;"
       "Pwd=myPassword;");

strConnect = _T("ODBC;Driver=Sybase SQL Anywhere 5.0;"
    "DefaultDir=C:\\DatabasePath\;Dbf=C:\\SqlAnyWhere50\\MyDatabase.db;"
    "Uid=MyUsername;Pwd=MyPassword;Dsn=\"\";");

strConnect = _T("DSN=MyDSN;Uid=MyUsername;Pwd=MyPassword;");

strConnect = _T("Provider=sqloledb;Data Source=MyServerName;"
        "Initial Catalog=MyDatabaseName;"
        "User Id=MyUsername;Password=MyPassword;");

strConnect = _T("Provider=sqloledb;Data Source=MyServerName;"
        "Initial Catalog=MyDatabaseName;"
        "Integrated Security=SSPI;");

strConnect = _T("Provider=sqloledb;Data Source=MyServerName\MyInstanceName;"
    "Initial Catalog=MyDatabaseName;User Id=MyUsername;Password=MyPassword;");

strConnect = _T("Provider=sqloledb;Data Source=(local);"
        "Initial Catalog=myDatabaseName;"
        "User ID=myUsername;Password=myPassword;");

strConnect = _T("Provider=sqloledb;Network Library=DBMSSOCN;"
        "Data Source=130.120.110.001,1433;"
        "Initial Catalog=MyDatabaseName;User ID=MyUsername;"
        "Password=MyPassword;");

strConnection = _T("Provider=MySQLProv;Data Source=test");

strConnect = _T("Provider=IBMDA400;Data source=myAS400;User Id=myUsername;"
     "Password=myPassword;");

strConnect = _T("Provider=ADSDSOObject;User Id=myUsername;Password=myPassword;");

strConnect = _T("Provider=DB2OLEDB;Network Transport Library=TCPIP;"
        "Network Address=130.120.110.001;"
        "Initial Catalog=MyCatalog;Package Collection=MyPackageCollection;"
        "Default Schema=MySchema;User ID=MyUsername;Password=MyPassword;");

strConnect = _T("Provider=DB2OLEDB;APPC Local LU Alias=MyLocalLUAlias;"
        "APPC Remote LU Alias=MyRemoteLUAlias;Initial Catalog=MyCatalog;"
        "Package Collection=MyPackageCollection;Default Schema=MySchema;"
        "User ID=MyUsername;Password=MyPassword;");

strConnect = _T("Provider=MSDASQL;Driver={Microsoft Access Driver (*.mdb)};"
        "Dbq=c:\\DatabasePath\\MyDatabase.mdb;Uid=MyUsername;Pwd=MyPassword;");

strConnect = _T("Provider=MSDASQL;Driver={SQL Server};Server=MyServerName;"
        "Database=MyDatabaseName;Uid=MyUsername;Pwd=MyPassword;");

strConnect = _T("Provider=MSDASQL;PersistSecurityInfo=False;"
        "Trusted_Connection=Yes;"
        "Data Source=MyDSN;catalog=MyDatabase;");

strConnect = _T("Provider=MSOLAP;Data Source=MyOLAPServerName;"
     "Initial Catalog=MyOLAPDatabaseName;");

strConnect = _T("Provider=MSOLAP;Data Source=http://MyOLAPServerName/;"
     "Initial Catalog=MyOLAPDatabaseName;");

strConnect = _T("Provider=MSOLAP;Data Source=https://MyOLAPServerName/;"
     "Initial Catalog=MyOLAPDatabaseName;");

strConnect = _T("Provider=MSDAORA;Data Source=MyOracleDB;User Id=myUsername;"
     "Password=myPassword;");

strConnect = _T("Provider=OraOLEDB.Oracle;Data Source=MyOracleDB;"
        "User Id=myUsername;Password=myPassword;");

strConnect = _T("Provider=vfpoledb;"
     "Data Source=C:\\DatabasePath\\MyDatabase.dbc;");

strConnect = _T("provider=msidxs;Data Source=MyCatalog;");

strConnection = _T("File Name=c:\\DataBasePath\\DatabaseName.udl;");

About Carlos Antollini

Carlos Antollini is a software engineer working on Object Oriented, Visual C++, MFC, COM, ATL, ADO, Internet technologies, OLAP, MS-SQLServer and Cyrillic Languages. Carlos is originally from Argentina, he was living by a couple of years in Fort Lauderdale, Florida, working for Citibank. Then he started his own business. Carlos is the creator of piFive[^], a family of BI Analytic Platform software, that it deals next to, latinsys[^], his partner in businesses... Click here to view Carlos Antollini's online profile.

Introduction

Using ADO.NET with SQL Server and Access

As a developer, I find that an incredible amount of my time is spent dealing with databases in some form or another - be it displaying, manipulating or analyzing data; creating client or server applications. The advent of the .NET Framework has brought with it some major changes, and database access is certainly no exception. Although ADO has been around since 1996, with the arrival of ASP, its latest incarnation present in the .NET Framework - ADO.NET - really does represent its coming-of-age. This new architecture is so extensive, that I can only hope to give you a brief introduction. Although the article is using the SQL server data access components, much applies equally as well to using Microsoft Access. We'll be covering connecting to a SQL Server database, executing queries, calling stored procedures, filtering data, and reflecting changes in a database - and point you in the direction of further resources for more in-depth discussions on particular topics. I've provided code in both VB and C# if there are any major syntatical differences, and the rest in C#.

If you've got any comments, or if I've made any stupid mistakes, then feel free to drop me a line!

Connecting to the database

In order to use ADO.NET, we'll need to import two namespaces:

[VB]
Imports System.Data
Imports System.Data.SqlClient

[C#]
using System.Data;
using System.Data.SqlClient;

In order to establish a connection with SQL Server, we use the SqlConnection object. There are also generic OleDbConnection and OdbcConnection objects present in the System.Data.OleDb and System.Data.Odbc namespaces for use with other data sources such as Access (NB: support for ODBC was added in .NET 1.1). This would work in exactly the same way as illustrated here, but as Microsoft were kind enough to provide a data access class specifically optimized for SQL server, we might as well take advantage of it! If you're using Access, then you can literally replace every occurence of SqlSomething with OleDbSomething.

Opening a connection is very simple:

[VB]
' create a new SqlConnection object with the appropriate connection string
Dim sqlConn As New SqlConnection(connectionString) 'OleDbConnection i
' open the connection
sqlConn.Open()
// do some operations here...
// close the connection
sqlConn.Close()

[C#]
// create a new SqlConnection object with the appropriate connection string
SqlConnection sqlConn = new SqlConnection(connectionString)
// open the connection
sqlConn.Open();
// do some operations ...
// close the connection
sqlConn.Close();

with the connection string usually taking this form:

It is probably worth noting at this stage something about connection pooling. The idea behind connection pooling is simple - instead of incurring a large amount of overhead each time a connection to database server is established and closed, once a connection has been opened, it remains open for the lifetime of the process, available to be used again if needed. Pooling database connections can significantly enhance the performance and scalability of your application. The .NET data providers automatically pool connections for you. However, to take maximum advantage of these, you should take note of the following:

• Connections are only pooled if they have the same connection string; so ensure this is always constant.
• When finished with a SqlConnection object, call Dispose() or Close() to release the connection back to the pool.
• In order to keep the maximum number of connections available, you should keep connections open for a short as period as possible - remembering that thanks to connection pooling, re-opening a database connection will incur little overhead.

For more information on connection pooling, take a look at this MSDN page.

Once open, you can't do much with a SqlConnection object on its own - other than close the connection again, and query its connection status using the ConnectionState property, so we'll move on to how we go about querying the database.

Executing a SQL query and SqlDataReader

In order to execute a SQL query, or a stored procedure (covered later) on the server, we use the SqlCommand object. The constructor that we'll use here accepts the query to execute, and the connection that it should use:

[VB]
' create the command object
Dim sqlComm As New SqlCommand("SELECT userid,username FROM users ORDER BY username", sqlConn)

[C#]
// create the command object
SqlCommand sqlComm = new SqlCommand("SELECT userid,username FROM users ORDER BY username", sqlConn);

From here, we have a number of methods to choose from in order to execute the query.

If we're not interested in the result of a query (other than whether it executed successfully or not), then use ExecuteNonQuery(). For example,

[C#]
// create the command object
SqlCommand sqlComm = new SqlCommand("DELETE FROM users WHERE userid=1", sqlConn);
sqlComm.ExecuteNonQuery();

If you're performing a query, and are only interested in the first column of the first row returned, then use ExecuteScalar(), and cast the result to the appropriate data type. For example,

[C#]
// create the command object
SqlCommand sqlComm = new SqlCommand("SELECT COUNT(*) FROM users", sqlConn);
int userCount = (int)sqlComm.ExecuteScalar();

Using the DataReader

The most common case, however, is going to be when we're retrieving multiple rows and columns of data. In this case, we use the ExecuteReader method, which returns an instance of a SqlDataReader object. Like its cousin OleDbDataReader, this provides read-only forward access to rows returned by the SqlCommand object.

So, now we've got this SqlDataReader object, what can we do with it? If you're interested in whether the query returned any results at all, or how many rows were affected by the query (usually due to DELETE or UPDATE statements) then check the HasRows and RecordsAffected properties.

First, call the Read() method, which advances the reader to the first row (and then to the next row on subsequent calls), and returns a boolean value indicating whether there was actually a row to read. Now we're free to access any columns that we see fit - we've got three different ways to do this - and then remember to call Close() on the DataReader (and the database connection, if need be).

If you want to access them by name - probably the most common and readable option - you can use the reader's indexer:

[VB]
Dim sqlComm As New SqlCommand("SELECT userid,username FROM users ORDER BY username", sqlConn)
Dim r As SqlDataReader = sqlComm.ExecuteReader()
While r.Read()
    Dim username As String = CStr(r("username"))
    Dim userID As Integer = CInt(r("userid"))
    Debug.WriteLine((username + "(" + userID + ")"))
End While
r.Close()

[C#]
SqlCommand sqlComm = new SqlCommand("SELECT userid,username FROM users ORDER BY username", sqlConn);
SqlDataReader r = sqlComm.ExecuteReader();
while ( r.Read() ) {
    string username = (string)r["username"];
    int userID = (int)r["userid"];
    Debug.WriteLine(username + "(" + userID + ")");
}
r.Close();

Note that, as with the ExecuteScalar method, we have to cast the value to the type we need - and if the column username wasn't a string in the code above, we'd get an InvalidCastException. For strings, we could equally well have used r["username"].ToString() instead of the formal cast. If you're trying to squeeze every ounce of performance from your application, then its certainly worth noting that the DataReader performs a case-sensitive search for the column name before resorting to a case-insensitive one - so it does help to be consistent in the way you case the field names.

A more efficient method (note: we're still talking small efficiency gains here!) is to use the column's index (rather than its name) - but this requires you to be very careful when writing your SQL statements (and in later modifications) that you don't change this:

int integerColumnValue = (int)r[i];

And finally, you could use one of the many Get methods that the SqlDataReader offers:

int integerColumnValue = r.GetInt(i);

These include GetInt, GetString, GetDouble etc, all of which accept an integer parameter for the column index.

One thing to bear in mind. If your query has the possibility of returning columns that are NULL, then you'll need to be careful before casting or calling the Get methods - the indexers will not return null, but DBNull.Value, and the Get methods will throw an exception, so for those you will need to check using sqlDataReader.IsDbNull(columnIndex) first.

Data Binding

Alternatively, if you simply want to bind a control like the DataGrid or DataRepeater objects to the rows, you can use the SqlDataReader as a DataSource:

[VB]
Dim sqlComm As New SqlCommand("SELECT userId,username FROM users ORDER BY username", sqlConn)
Dim r As SqlDataReader = sqlComm.ExecuteReader(CommandBehavior.CloseConnection)
myDataGrid.DataSource = r
' Call to DataBind needed in ASP.NET
'myDataGrid.DataBind()
r.Close()

[C#]
SqlCommand sqlComm = new SqlCommand("SELECT userId,username FROM users ORDER BY username", sqlConn);
SqlDataReader r = sqlComm.ExecuteReader(CommandBehavior.CloseConnection);
myDataGrid.DataSource = r;
// Call to DataBind needed in ASP.NET
//myDataGrid.DataBind();
r.Close();

Note that in the above code we provide the ExecuteReader command with a parameter CommandBehavior.CloseConnection - this tells the data reader to call close on the database connection when we call its own Close method. If we hadn't used this, then we would need to call sqlConn.Close() at some point in our code!

Stored Procedures and other queries with parameter

Writing code that generates correct SQL code for calling stored procedures, or with UPDATE and INSERT statements can often be a real pain - especially when worrying about escaping strings, or getting date formats correct. Using the SqlCommand/OleDbCommand object allows us to provide a SQL statement with variables waiting to be filled - and we can then set the value of these variables seperately in our code, and let ADO.NET worry about sending it to the database correctly! First, we create the SqlCommand object as usual. If we are calling a stored procedure, then we just need to provide its name. If we are using a normal SQL statement, then we'll prefix the parameter names with @ (as they are in Stored Procedures).

[C#]
// stored procedure
SqlCommand com = new SqlCommand("UpdateUser",sqlConn);
// tell the command that this is a stored procedure!
com.CommandType = CommandType.StoredProcedure;
/*

we'll assume the Stored Procedure takes the following form at the server:

ALTER PROCEDURE UpdateUser(@id AS INTEGER, @username AS VARCHAR(30)) AS
UPDATE users SET username=@username WHERE userId=@id

*/

or

// normal SQL statement
OleDbCommand com = new OleDbCommand("UPDATE users SET username=@username WHERE userId=@id",sqlConn);

We specify the parameters by adding SqlParameter/OleDbParameter objects to the Command object's Parameters property. The constructor we'll use here accepts a string for the parameters name, and an OleDbType or SqlDbType for the data type. We then add this new Parameter object to the collection, and set its Value property:

[VB]
' add a parameter @username to the command, and set its value to the string "James"
com.Parameters.Add(New SqlParameter("@username", OleDbType.VarChar)).Value = "James"
' add a parameter @id to the command, and set its value to 1
com.Parameters.Add(New SqlParameter("@id", OleDbType.Integer)).Value = 1
' we can now execute the command...
com.ExecuteNonQuery()

[C#]
// add a parameter @username to the command, and set its value to the string "James"
com.Parameters.Add(new SqlParameter("@username",OleDbType.VarChar)).Value = "James";
// add a parameter @id to the command, and set its value to 1
com.Parameters.Add(new SqlParameter("@id",OleDbType.Integer)).Value = 1;
// we can now execute the command...
com.ExecuteNonQuery();

Stored Procedure Return Values

If your stored procedure uses the RETURN statement, or has parameters marked as out , then you can also use the Parameters collection to retrieve these values after a query has been executed.

To get the value of a RETURN statement, you need to do the following:

[C#]
// add a new parameter, with any name we want - its for our own use only
SqlParameter sqlParam = com.Parameters.Add("@ReturnValue", SqlDbType.Int);
// set the direction flag so that it will be filled with the return value
myParm.Direction = ParameterDirection.ReturnValue;

Then, after the stored procedure has been executed,

int returnValue = (int)com.Parameters["@ReturnValue"].Value

will retrieve the value that was set.

Now suppose you had a stored procedure that has an output parameter - maybe something like the following:

CREATE PROCEDURE AddUser @UserName VarChar(30), @Identity int OUT AS ...

then again, we can use

[C#]
// add a new parameter, we need to get the name right this time!
SqlParameter sqlParam = com.Parameters.Add("@Identity", SqlDbType.Int);
// set the direction flag so that it will be filled with the return value
myParm.Direction = ParameterDirection.Output;

and after the execution of the query, com.Parameters["@Identity"].Value will contain the value that was set in the stored procedure. If you need any further info, see Input and Output Parameters, and Return Values on MSDN.

Using the DataSet

Although the DataReader is very fast and simple, in many situations, we're going to need more than just forward-only access to the results of our queries - this is where the DataSet and SqlDataAdapter classes come in. The DataSet is essentially an in-memory database, complete with multiple tables, constraints, running queries and sorting, plus the ability to persist its state to an XML file. You can use SqlDataAdapter (and its cousins OleDbDataAdapter and OdbcDataAdapter) to populate the DataSet with rows from a SQL server query. Once populated, you can make changes to the DataSet, add rows, perform sorting etc, and then use the SqlDataAdapter again to reflect these changes in the original database using appropriate UPDATE and DELETE sql statements. Depending on the available resources on your database server and the web server, relocating these operations to a disconnected model can be greatly beneficial.

Lets first take a look at how we can fill a dataset:

[C#]
// create the data adapter
SqlDataAdapter dataAdapter = new SqlDataAdapter ("SELECT userId,username FROM users ORDER BY username", sqlConn);
// create the DataSet
DataSet dataSet = new DataSet();
// fill the DataSet using our DataAdapter
dataAdapter.Fill (dataSet);

Here the SQL query is actually executed when we call the Fill method of the SqlDataAdapter - and as soon as the query is completed, the connection for the database query is closed (so the DataSet acts as "disconnected" data store). Now that we've populated the DataSet, its Tables property will be populated with DataTable objects for each table in our query. So, the following code would have the same result as our small DataReader example earlier - except we're no longer reading the results straight from the database, and can enumerate the table rows as often as we like, in any order we like.

[C#]
foreach(DataRow dataRow in dataSet.Tables["users"].Rows) {
    Debug.WriteLine(dataRow["username"] + "(" + dataRow["userid"] + ")");
}

Sorting and Filtering

How about if we want to filter or sort the data further once we've retrieved it from the database? The simplest way (if we're not looking to DataBind to a control), is to use the Select method of the DataTable, which accepts two parameters - a filter expression and a sort expression - and returns an array of DataRow objects.

[C#]
DataRow[] matchingRows = dataSet.Tables["users"].Select("username like 'Bob%'","dateJoined DESC");

Another method is to use the DataView object, which is specifically designed for sorting and filtering rows, and can be used as a DataSource in its own right - so we could bind a DataGrid control to this customised "view". We can get an instance of a DataView object from the DefaultView property of our DataTable. Then, we can sets its Sort and RowFilter properties:

[C#]
DataView dataView = dataSet.Tables["users"].DefaultView;
dataView.RowFilter = "username like 'Bob%'";
dataView.Sort = "dateJoined DESC";

myDataGrid.DataSource = dataView;
//Call to DataBind needed in ASP.NET
//myDataGrid.DataBind();

Adding, Deleting & Updating Rows

Making modifications to the DataSet, and then updating the database to reflect this changes is simple, especially if we're just performing queries on one table. For the moment, lets assume that we're using a DataGrid to display our data - which means it will automatically add new rows to the DataSet for us, and allow rows to be edited or deleted without having to write any code whatsoever. All we'll need to do is "sync" the database with the modifications that take place, using the SqlDataAdapter/OleDbDataAdapter.

For this purpose, the data adapter exposes four properties - SelectCommand (which we have already set indirectly when we construct the SqlDataAdapter object), UpdateCommand, DeleteCommand and InsertCommand, and a method called Update. All we need to do is provide SqlCommand/OleDbCommand objects for these properties, and call Update - then the DataAdapter will use the appropriate commands to update the database with the changes made in the DataSet. In fact, our lives are made even easier by the existence of the SqlCommandBuilder class - which will mean we only have to write the one SELECT statement, and it will do the rest. Here's a demonstration:

[C#]
// create the data adapter
SqlDataAdapter dataAdapter = new SqlDataAdapter ("SELECT userId,username FROM users ORDER BY username", sqlConn);
// create an SqlCommandBuilder - this will automatically generate the
// commands, and set the appropriate properties in the dataAdapter
SqlCommandBuilder commandBuilder = new SqlCommandBuilder(dataAdapter);
// create the DataSet
DataSet dataSet = new DataSet();
// fill the DataSet using our DataAdapter into a table called users
dataAdapter.Fill (dataSet,"users");
// set the DataGrid source to the one table in our dataset
myDataGrid.DataSource = dataSet.Tables[0];

Then, when we've finished making our changes to the DataSet via the DataGrid, we call

dataAdapter.Update(dataSet);

and the database will now contain the changes we have made.

Doing without CommandBuilder & Wrapping Up

Although the CommandBuilder does a great job on simple tables, when we start using more complex SELECT statements - or simply want more control over what's going on - then its time to specify our own commands for each of the Command properties in the DataAdapter. First, we can specify the SelectCommand as usual, perhaps adding parameters for a Stored Procedure or WHERE clause.

[C#]
// create the data adapter - we'll specify the connection through our SqlCommand object
SqlDataAdapter dataAdapter = new SqlDataAdapter();
SqlCommand sqlSelectCommand = new SqlCommand("SELECT userid,username FROM users",sqlConn);
// assign to the SelectCommand property
dataAdapter.SelectCommand = sqlSelectCommand;

Now, for the INSERT, DELETE and UPDATE statements we write a generalized query with parameters - and then take advantage of another constructor for the SqlParameter object that lets us effectively bind a parameter to a column name (as obviously we won't actually know its value ourselves);

public SqlParameter (System.String parameterName, System.Data.SqlDbType dbType, System.Int32 size, System.String sourceColumn )

So, we can do the following:

[C#]
// create a new Command for our UPDATE statement
SqlCommand sqlUpdateCommand = new SqlCommand("UPDATE users SET username=@username WHERE userid=@id",sqlConn);
// add parameter to this command for @username, and bind it to the column "username"
sqlUpdateCommand.Parameters.Add("@username",SqlDbType.VarChar, 30, "username");
// create another parameter
SqlParameter sqlParam = new SqlParameter("@id",SqlDbType.Integer, 8, "userid");
sqlParam.SourceVersion = DataRowVersion.Original;
// add parameter
sqlUpdateCommand.Parameters.Add(sqlParam);
// assign to UpdateCommand
dataAdapter.UpdateCommand = sqlUpdateCommand;

Now, when the DataAdapter needs to perform an update, it will run the above command - setting @username to the value of the new username in the row that was modified. @userid, however, will be set to the original version of its value, as we set the SourceVersion property of this parameter to DataRowVersion.Original. This is generally a good idea - otherwise if we had for some reason modified the user id then we'd lose our "handle" on the row assuming userid was a primary key, and not update the correct row. If we wanted to access the new version, we would set SourceVersion to DataRowVersion.Current (its default value).

The DeleteCommand and InsertCommand properties of the DataAdapter can be set in a similar manner. Weaning Developers from the CommandBuilder on MSDN provides a good insight into further alternatives to the CommandBuilder.

Adding, Updating & Deleting Rows in a DataSet/DataTable

When we started examining the DataSet object, we skipped over the issue of actually modifying the data in our DataTable object - instead allowing the DataGrid to do the work for us. For sake of completeness, I shall give a brief summary as to how to do it "manually" here.

To add a new row to a DataTable, use the NewRow() method:

[C#]
// get a new row
DataRow newRow = myDataSet.Tables["users"].NewRow();
// set the appropriate fields here... for example
newRow["username"] = "myNewUsername";
// add to the DataTable:
myDataSet.Tables["users"].Rows.Add(newRow);

To update a row in the DataTable, you can simply modify a columns value:

myDataSet.Tables["users"].Rows[rowIndex]["realName"] = "James Crowley";

And finally, to delete one, use the Remove or RemoveAt method:

myDataSet.Tables["users"].Rows.RemoveAt(rowIndex)

or

myDataSet.Tables["users"].Rows.Remove(dataRowObject)

For more information on these, I suggest you take a look at the MSDN article Manipulating Data in a Data Table.

Conclusion

Well, that's pretty much it folks, so I'll just give a very quick overview!

• Establishing a Connection - Use the SqlConnection and OleDbConnection objects
• Running queries - Use the SqlCommand and OleDbCommand objects - using theirExecuteNonQuery, ExecuteScalar and ExecuteReader methods
• Stored procedures - Just the same as above, but add appropriate SqlParameter objects to its Parameters collection
• Fast forward-only data access - Use SqlDataReader and OleDbDataReader
• Disconnected data access - Use a DataAdapter's Fill method to populate a DataSets/DataTable object, and to then reflect changes in the database by calling Update

원본 : http://www.sarangnamu.net/basic/basic_v ··· ory%3D33

stdafx.h 내부

------------ 아래코드 삽입 -----------------------------

#pragma warning(push)                         //ado 버그로 인한 컴파일시 경고 뜨는것 방지.
#pragma warning(disable:4146)
#import "C:\Program Files\Common Files\System\ADO\msado15.dll" \
           rename("EOF", "EndOfFile") no_namespace
#pragma warning(pop)

-----------------------------------------------------
app 헤더
------------------
inline void TESTHR(HRESULT x) {if FAILED(x) _com_issue_error(x);};
------------------
app 함수쪽
------------------

InitInstance 에 다음과 같이 삽입

if(FAILED(::CoInitialize(NULL))) {

AfxMessageBox("::CoInitialize 실패했습니다.\n프로그램을 정상적으로 사용할 수 없어서 종료합니다.", MB_ICONSTOP);

}

-----------------


======================================================================

void CAdo_cnDlg::OnButton1()    
{                                            
_ConnectionPtr pConnection = NULL;    
_RecordsetPtr pRecordset=NULL;        


TESTHR(pConnection.CreateInstance(__uuidof(Connection)));

_bstr_t strCnn("Provider=SQLOLEDB.1;Password=xxxxxxx;Persist Security Info=True;User ID=xxxxxxxx;Initial Catalog=xxxx;Data Source=200.100.00.000");


   TESTHR(pConnection->Open(strCnn, L"", L"", -1));
   CString sQuery;
   sQuery.Format("select * from em_tran_test");                                                    
pRecordset = pConnection->Execute((_bstr_t)sQuery, NULL, adOptionUnspecified);

_variant_t vData;

while (!pRecordset->GetEndOfFile()) {

       vData = pRecordset->GetCollect(L"tran_date");     //tran_date 는 필드명임

    // 작업들

       pRecordset->MoveNext();

   }



//도움말에 의거하면 다음 세 스텝은 자동으로 호출되기 때문에 없어도 된다고 함..

   pRecordset->Close();

   pConnection->Close();

   pConnection.Release();



}

=====================================================================



[삽입]

CString sQuery;

sQuery.Format("Insert Into 테이블명 (필드1, 필드2, 필드3, 필드4) values('%s', %d, %lf, 1)", sVal, nVal, dVal);

pRecordset = pConnection->Execute((_bstr_t)sQuery, NULL, adOptionUnspecified); //쿼리문 실행



[삭제]

CString sQuery;

sQuery.Format("delete from 테이블명 where 조건");

pRecordset = pConnection->Execute((_bstr_t)sQuery, NULL, adOptionUnspecified); //쿼리문 실행



[수정]

CString sQuery;

sQuery.Format('Update 테이블명 set 필드1=%lf, 필드2='%s', 필드3=%d where 조건", dVal1, sVal, dVal2);

pRecordset = pConnection->Execute((_bstr_t)sQuery, NULL, adOptionUnspecified); //쿼리문 실행

고수닷넷 - snaiper님

OTL(Oracle, Odbc and DB2-CLI Template Library) 소개

이 라이브러리는 Oracle Call Interface 즉 OCI 를 쉽게 호출하기 위해 만들어졌으나, 그 외에 많은 발전을 거듭하여 이제는 ODBC, DB2-CLI 까지 지원하도록 설계되어 있습니다.즉 바꿔말하면 거의 모든 DB를 지원한다고 봐도 무방하다고 할 수 있겠습니다. 개인적으로 써 본 바로는 성능도 맘에 들고, 풀링도 가능한데다 SP(Stored Procedure) 콜도 꽤나 쉽고 간단합니다. 물론 select, update 등의 쿼리 작업이 간단한거는 두말하면 잔소리이 되겠지요. 나름대로 상용 프로젝트에서 많이 쓰인 것 같고, 뉴스그룹 쪽에서도 심심치 않게 얘기가 나오는 라이브러리이니 어느정도 신뢰성은 보장된 것 같습니다.

이 라이브러리는  http://otl.sourceforge.net  에서 받을 수 있고, 여러 개의 샘플 코드 또한 거기서 볼 수 있습니다. 물론 영어로 되어 있긴 하지만 튜토리얼도 마련되어 있습니다.참고로 헤더 하나라서 작업하기도 쉽습니다.

구성 클래스들은 뭐가 있을까?

OTL에서 중심 클래스는 크게 3가지로 나누어 집니다. 그 클래스는 otl_connect, otl_stream, otl_exception 입니다.

otl_connect 는 db 자체와 관련된 일을 합니다. 즉 접속, 접속 끊기, 세션 열기, 닫기, 커밋, 롤백, 쿼리 취소 기능을 제공하는 클래스입니다. db에 접속하기위해 가장 먼저해야 하는 일이 이 otl_connect 클래스의 인스턴스를 생성하는 것입니다. ADO 로 따지면 Connection 객체라고 볼 수 있겠습니다.

  다음에 설명하는 것은 otl_stream 입니다. 이 클래스는 무엇인가를 던지고, 받아오는 일을 하는 클래스입니다. 즉 MFC의 CRecordset 과 같은거라고 비유해서 설명할 수 있는데, 이 클래스는 otl_connect 로 db로의 접속을 열고 그 곳으로 쿼리를 던지고 그 결과를 받아오는 일을 합니다. 이건 주 기능이고 자잘하게 나온 데이터에 따라 맞는 데이터를 분석하고 변수와 바인딩하는 일, 넘겨줄 인자 파싱하는 일, 그리고 바인딩하는 법칙 조정하는 일 뭐 자잘하게 하는 것도 많습니다.

다음에 설명할 것은 otl_exception 이다. 이 클래스는 이름을 봐서도 알겠지만 뭔가 문제가 생겼을떄 던지는 예외 클래스입니다. 그럼 C++ 예외 처리로 즉 try , catch 로 잡아서 처리하면 되겠습니다. 그럼 뭐가 문제인지 자세한 설명이 넘어오는 것이죠.(아쉽게도 영어입니다.)  만약 std::exception 와 같이 사용하고 싶다면 매크로를 정의해주면 std::exception 에서 상속받아서 클래스가 만들어집니다.

간단한 소스 코드

std::string query= "SELECT LOG_NAME, TO_CHAR(LOG_TIME, 'YYYY/MM/DD HH24:MI:SS'),LOG_SEVERITY,"LOG_CATEGORY,LOG_DES,REQ_IP,REQ_ID,REQ_TYPE,

SUBJECT_DN,SUBJECT_ID FROM table_name ";

otl_stream selectStream(50, query.c_str(), db);

while (!selectStream.eof())

{

   table_name_data data;

   selectStream >> data.log_name >> data.log_time >> data.log_severity

                     >> data.log_category >> data.log_des

                     >> data.req_ip >> data.req_id >> data.owner >> data.table >> data.column;

}

catch (const std::exception& e) { ...}

  위 예제는 db 에서 select 하는 코드입니다. 보시다시피 쿼리 만들고 난 다음에 그걸로 otl_stream 객체를 만든 다음에 db 인스턴스, 즉 otl_connect 로 선언된 db 변수와 함께 생성하고 나서 루프를 돌면서 가져오면 됩니다. 나머지는 C++ cin,cout 객체 사용처럼 하시면 됩니다.

그럼 접속이 궁금하시겠죠? 그 코드는 다음과 같습니다.

otl_connect::otl_initialize();

otl_connect db;

db.rlogon("scott/tiger"); // scott/tiger@sid명도 됩니다.

... ( db 쿼리 코드)

otl_connect::otl_terminate();

보시다시피 Initialize 하고 otl_connect 의 rlogin 메소드를 부르는 식으로 되어 있습니다.

물론 다음과 같이 할 수 있습니다.(제가 사용하던 코드의 일부입니다.) 아시는 분은 아시겠지만 오라클 설정 파일에 있는 스트링을 그대로 가져다 쓴 것입니다. 이걸 잘 이용하면 굳이 일일히 TNS명 등록할 필요는 없겠죠?

std::ostringstream ost;

ost <<

   GetID() << '/' << GetPassword() << '@' <<

   "(DESCRIPTION="

   " (ADDRESS_LIST="

   "  (ADDRESS=(PROTOCOL=TCP)(HOST=" << GetHost() << ")(PORT=" << GetPort() << "))"

   " )"

   " (CONNECT_DATA=(SID=" << GetOraSID() << "))"

   ")";

std::auto_ptr<otl_connect> otlConn(new otl_connect(ost.str().c_str()));

이와 같이 간단하게 사용 예제를 보였는데 이를 사용하려면 다음과 같은 선언이 필요합니다.이를 사용하려면 다음과 같이 합니다.

#define OTL_ORA8I

#define OTL_STL

#define OTL_STLPORT

#define OTL_ANSI_CPP

#define OTL_EXCEPTION_DERIVED_FROM std::exception

#define OTL_DEFAULT_STRING_NULL_TO_VAL ""

#include "otlv4.h"

각각 설명하면 OTL_ORA8I는 오라클 8i OCI 를 사용한다는 것을 OTL_STL은 STL 을 사용한다라는 것을, OTL_STLPORT 는 stl port 를 사용한다라는 것을 나타냅니다.

그리고 OTL_ANSI_CPP 는 캐스팅과 같은 것을 일반 c++ 캐스팅을 따르겠다라는 얘기이다.  OTL_EXCEPTION_DERIVED_FROM std::exception는 db 처리시 문제가 생기면 otl_exception을 던지는데 이 클래스가 std::exception 이나 사용자 정의 CException 과 같은 클래스에서 상속받는것을 정해주는 것입니다. 뒤에 std::exception 이 보이시죠?

그리고 마지막은 DB 데이터가 널일 경우(VARCHAR2타입과 같이 string과 매핑되는) string 값이 자동으로 "" 이 값을 가지는 것을 명하는 심볼입니다.

참고 사항

위와 같이 간단히 예제와 함께 사용 방법을 설명해보았습니다. 물론 이보다 더 많은 기능과 함수들이 있습니다만, 너무 많은 관계로 이 글에서 설명하기란 무리인 것 같습니다. 나머지는 직접 사이트에 가서 예제와 일부 설명 문서를 참고하시면 좋겠습니다.

간단히 참고할 사항만 설명드리자면 otl_connect 에서 buffer size 라는 것이 있습니다. 무슨 버퍼를 말하는지 알쏭달쏭 하실 만한 분들이 많을 것 같아 말씀드리자면(문서에도 정확히 나와 있지 않더군요.) 제가 추적해본 결과는 fetch size 를 뜻하는 것으로 생각됩니다. 즉 얼마만큼 DB 데이터를 미리 가져오는가에 대한 설정인 것 같습니다. 물론 많으면 더욱 좋을 수도 있겠고 나쁠 수도 있겠지요. 잘 선택해야 되는 것입니다. 물론 하시다보면 반드시 1로 설정해야 할 때가 있습니다. 다른 것으로 선택하면 예외로 떨어지기도 하죠(1로 설정하라고 설명하긴 합니다.)

그리고 간단한 팁을 하나 설명드리면 DB 데이터를 리스트 컨트롤 등에 부릴 때가 많은데 이럴 때는 반드시 스트링 데이터가 되야 합니다. 하지만 OTL 특성상 각 데이터 타입에 맞게 변수도 int, string 등 다른 타입으로 선언되어야 합니다. 하지만 이걸 일괄적으로 string 으로 맞출 수 있지요. 그건 다음과 같이 하면 됩니다.

otl_stream selectStream;

selectStream.set_all_column_types(otl_all_num2str | otl_all_date2str);

selectStream.open(50, query.c_str(), db);

정리

OTL 이라는 편리한 라이브러리에 대한 소개를 해보았습니다. 저는 DB 접속하는 라이브러리를 이걸로 쓰고 있습니다. 많이 편리했던 관계로 여러분들도 한번 써보시라고 이렇게 소개해드립니다.

나중에 기회가 되는데로 OTL 에 대한 자세한 설명, 그리고 팁 등을 소개하겠습니다.

오전 9:07 2002-07-04

조경민 ADO for Performance

[ADO for Performance]

ADO 성능에 관련되어 MSDN 들과 여러 자료를 취합하여 정리합니다.

1. 매 작업시 Connection 객체를 새로 생성/ 소멸하도록 한다.

(Use ADO Like an Apartment Model)

=> 내부적으로 연결 Pooling을 지원하기 때문에 성능상 문제가 없다.

내가 계속 물고 있으면 리소스만 잡을 뿐이다.

2. 입력 수정 삭제의 경우 트랜젝션을 걸어두자.

=> 조회만 있을 경우 트랜잭션은 불필요

3. 옵션을 잘사용하자

adExecuteNoRecords 를 사용하면 레코드셋을 받을 필요없을때 유리하다

adReadOnly는 조회시 갱신이 없을때 유리하다.

4. Use Session (OLE DB) or Connection (ODBC) Pooling

=> Use MTS 또는 ODBC 3.0 Connection Pooling 커넥션 풀링 탭에서 설정

5. Disconnect Your Client Cursor from the Connection for R/O and Long-Use Scenarios

Sub DisconnectRS()

  Dim con As Connection

  Dim rs As Recordset

  Set con = New Connection

  Set rs = New Recordset

  con.CursorLocation = adUseClient

  con.Open "Provider=SQLOLEDB;Data Source=persons;" _

     & "Initial Catalog=Performance;User ID=sa;Password=;"

  Set rs = con.Execute("SELECT SomeText, SomeNumber, SomeTime, " &  _

     "SomeMoney FROM Nonsense")

  Set rs.ActiveConnection = Nothing <= 이부분

  Set con = Nothing <= 이부분

  '

  ' Process data

  '

  rs.Close

  Set rs = Nothing

End Sub

6. 조회시 SELECT * 하지말고 원하는 컬럼만 받도록하자

7. 가능하다면 Stored Procedure를 사용하자

8. 커서는 가능한 사용하지 말자

9. Command Object은 가능하면 재사용하자. 한번 전역 객체화시킨 후

  이를 계속 사용하는것이 좋다.

----------------------------------------------------------------------

참고 MSDN

----------------------------------------------------------------------

Using the ADO GetRows Method for Faster Data Retrieval

ADO에서 Recordset으로 반환보다 GetRow를 이용하면 더 빠르다고 한다.

Table A: Processing time (sec) for ADO 2.0 objects using the GetRows method

Total Records Recordset Command Parameter

10,000        3.07      2.86    2.91

20,000        5.88      5.66    5.77

40,000        11.42     11.20   11.37

Table B: Comparison of GetRows method to Recordset object by itself

with 40,000 records.

Method Type      Recordset Command Parameter

GetRows          11.42     11.20   11.37

Recordset object 15.55     14.72   14.66

약간더 빠른거 같다 ㅡ.ㅡ

------------------------------------------------------------------------

Optimize Data Access Using ADO

1. Avoid Updating Through Recordsets

레코드셋을 반환받아 커서이동하면서 업데이트하는 것보다는

스토어드프로시저를 이요하거나 특별한 경우엔 여러번의 추가 및 삭제를 이용하는

것이 더 효율적이다.

It's more efficient to update the database through stored procedures, especially when performing multiple inserts and deletes

2. Use Stored Procedures

스토어드프로시저는 빠르게 실행되도록 컴파일되어 서버에 쿼리 실행에대한 plan(계획)

이 짜여지게되어 효율적으로 처리하게 된다.

DB 조작 코드가 애플리케이션에 위치하지 않고 서버에 있으니 보다 유지보수 및

보안에 유리하다

they are compiled to be faster

Other advantages to using stored procedures include easier maintenance and security

3. Create the Minimum Recordset

레코드셋을 열때는 CursorType, CursorLocation, LockType를 지정하는것이 좋다.

Use the CursorType, CursorLocation, and LockType properties to create the most efficient recordset

With adoRS

  .ActiveConnection = "Pubs"

  .CursorType = adOpenStatic <= 여기

  .CursorLocation = adUseClient <= 여기

  .LockType = adLockReadOnly <= 여기

  .Source = "SELECT title_id, " & _

     "title, pub_id FROM Titles"

  .Open

End With

*MTS를 쓰게되면 Client 위치의 커서만 쓸수있다.

If you're going to pass recordsets to MTS components, you can only use disconnected (client-side) recordsets

4. Use Disconnected Recordsets

Dim adoRS As New ADODB.RecordsetWith adoRS

  .ActiveConnection = "Pubs"

  .CursorLocation = adUseClient   .CursorType = adOpenStatic

  .LockType = adLockBatchOptimistic

  .Source = "SELECT emp_id, " & _

     "lname,hire_date FROM Employee"

  .Open   Set .ActiveConnection = Nothing <= 여기

End With

There are many advantages to using disconnected recordsets. Scalability is improved, because adding 50 users doesn't mean 50 open database connections and 50 open cursors on the database server. Time-consuming operations executed on a disconnected recordset will not affect the database server and all modifications are batched. The recordset must be reconnected to the data source to update the database. Note that with a disconnected recordset, changes made on the database by other users will not be visible?concurrency issues must be handled with code.

5. Define Command Parameters

6. Encapsulate Data Access

3-tier식으로 해서, 클라이언트 애플리케이션은 DCOM으로 미들티어 비지니스로직

VB DLL, COM에게 로직을 호출하고, 이 미들티어는 OLEDB를 통해 DB서버로 연결하도록하자

8. Use MTS

Putting your components in MTS gives you the benefits of scalability, connection pooling, and transactions.

연결 풀링을 지원하게 된다.

SetComplete, SetAbort를 하게되면 모든 트랜젝션은 Commit되거나 Roll Back되게된다.

All transactions occur in an object's context, and all the transaction's operations are committed or rolled back using the SetComplete or SetAbort method

단점은

There are some trade-offs to consider when using MTS. Increased overhead is involved when referencing MTS components because they reside remotely. Additional time is spent marshalling the data to the MTS component if parameters are passed from the client to an MTS component. Consider these issues when making design decisions before creating components to run in MTS. Depending on your requirements, it could be well worth the effort.

9. Use Output Parameters

Use output parameters instead of returning a recordset if you have a stored procedure that returns a single value or row of data

With adoCommand

  .Parameters.Append .CreateParameter("RETURN_VALUE", _

     adInteger, adParamReturnValue, 0)

  .Parameters.Append .CreateParameter("@Zip", _

     adVarChar, adParamInput, 20, Null)

  .Parameters.Append .CreateParameter("@State", _

     adVarChar, adParamInputOutput, 2, Null)

  .Parameters.Append .CreateParameter("@City", _

     adVarChar, adParamInputOutput, 50, Null)   .Parameters("@Zip") = Zip   SQL = "usp_GetCityStateForZip"   .CommandText = SQL

  .CommandType = adCmdStoredProc

  .ActiveConnection = "Pubs"

  .Execute   'Check the return value after the update

  If .Parameters("RETURN_VALUE") = 0 Then _

     State = .Parameters("@State") & "

     City = .Parameters("@City") & "

  End If

End With

10. Retrieve Only Required Data

SELECT * FROM table 하지말자.

SELECT title_id, title, type, price FROM Titles이런게 더 좋지 않는가.

또

Dim adoCommand As New ADODB.CommandWith adoCommand

  .ActiveConnection = "Pubs"

  .CommandText = "usp_InsertNewTitle"

  .CommandType = adStoredProc

  .Execute , , adExecuteNoRecords  <= 이렇게 하면 레코드셋 반환필요없을시 유리

End With

------------------------------------------------------------------------

High-performance Visual Basic Apps

ASP 서보트 VB COM으로 웹페이지에서 ADO MTS를 써서 빠르게 하자.

- 스토어드 프로시저를 사용하여 웹 로그인 모듈을 맡게한다.

- Disconnected 레코드셋으로 하여 효율을 높인다.

Alter Procedure Account_VerifyLogin

(

    @EMail varchar(50),

    @Password varchar(50),

    @AccountID int output,

    @FullName varchar(100) output

)

As

    select @AccountID = AccountID,

        @FullName = FirstName + ' ' + LastName

    from accounts

    where email = @EMail

    and Password = @Password

    group by AccountID, FirstName + ' ' + LastName

    return @@ROWCOUNT    ? 0 means not found, 1 means it matched

한 후

VB ASP용 COM에서

dim email, password

Response.Buffer = true

Response.Clear

email = trim(Request("login"))

password = trim(Request("password"))

If email <> "" Then

  dim objAccount

  set objAccount = Server.CreateObject("FMStocks_Bus.Account")

  dim AccountID, FullName    

  if objAccount.VerifyLogin(email, password, FullName, _

    AccountID) then    

    Response.Cookies("Account")("AccountID") = AccountID

    set objAccount = nothing

    Response.Redirect("home.htm")    

  else

    set objAccount = nothing

    Response.Redirect("default.htm")    

  end if

else

  Response.Redirect ("default.htm")

end if

를 하고

스토어드 프로시저 호출하는 방법은 아래처럼 한다.

Public Function VerifyUser(ByVal email As String, _

    ByVal password As String, _

    ByRef AccountID As Variant, ByRef FullName As Variant) _

    As Boolean

    On Error GoTo errorHandler

    Dim strUserInfo As String

'The following code was taken from Database.cls RunSPWithString

'and modified to support 2 outputs and 1 return value

    ' Set up Command and Connection objects

    Dim cmd As ADODB.Command

    Set cmd = CtxCreateObject("ADODB.Command")

    'Run the procedure

    cmd.ActiveConnection = GetDSN()

    cmd.CommandText = "Account_VerifyLogin"

    cmd.CommandType = adCmdStoredProc

    With cmd

        .Parameters.Append .CreateParameter(, adInteger, _

          adParamReturnValue) 'rs(4).Value

        .Parameters.Append .CreateParameter("@EMail", _

          adVarChar, adParamInput, 50, email)

        .Parameters.Append .CreateParameter("@Password", _

          adVarChar, adParamInput, 50, password)

        .Parameters.Append .CreateParameter("@AccountID", _

          adInteger, adParamOutput, 4)

        .Parameters.Append .CreateParameter("@FullName", _

          adVarChar, adParamOutput, 50)

    End With    

    cmd.Execute , , ADODB.adExecuteNoRecords        

    If cmd.Parameters(0).Value = 0 Then

        VerifyUser = False

    Else

        AccountID = cmd.Parameters(3).Value

        FullName = cmd.Parameters(4).Value

        VerifyUser = True

    End If    

    Set cmd = Nothing

    CtxSetComplete

    Exit Function

errorHandler:

    RaiseError g_modName, "VerifyUser"

End Function

물론 RS는 Disconnected로 만드는게 좋다

Function RunSPWithRS(ByVal strSP As String, _

                     ParamArray params() As Variant) As adodb.Recordset

    On Error GoTo errorHandler

    Dim rs As adodb.Recordset, cmd As adodb.Command

    Set rs = CtxCreateObject("ADODB.Recordset")

    Set cmd = CtxCreateObject("ADODB.Command")      

    'Run the procedure

    cmd.ActiveConnection = GetDSN()

    cmd.CommandText = strSP

    cmd.CommandType = adCmdStoredProc    

    collectParams cmd, params

    rs.CursorLocation = adUseClient

    rs.Open cmd, , adOpenForwardOnly, adLockReadOnly

    Set cmd.ActiveConnection = Nothing

    Set cmd = Nothing

    Set rs.ActiveConnection = Nothing

    Set RunSPWithRS = rs

    Exit Function

errorHandler:

    RaiseError g_modName, "RunSPWithRS(" & strSP & ", ...)"

그리고 웹페이지에 뿌리도록 하자

%>

<table cellpadding=2  cellspacing=2 bordercolor=Gray bgcolor=White border=2 cols=2 rules=ALL>

<tr><td bgcolor=Gray align=Left width=50><Font face=Tahoma size=1 color=White><b>Ticker

    <td bgcolor=Gray align=Left width=150><Font face=Tahoma size=1 color=White><b>Company

</tr>

<%

    end if

   do until rs.EOF

%>

<tr><td><a href="TickerDetail.asp?ticker=<%=fldTicker.value%>">

<%=fldTicker.value%></a><td><%=fldCompany.value%></tr>

<%

    rs.MoveNext

  loop

    count = rs.RecordCount

    rs.Close

    set rs = nothing

%>

</table>

<%

    end if

    Response.Write "<p>" & count & " records found.<p>"

  end if

end sub

--------------------------------------------------------------------------

ADO Performance Best Practices

Get the best performance from your ADO application

                    Establishing the Connection

비동기적으로 Connection을 열도록 하면 애플리케이션이 기달릴필요없어 좋다.

First, try connecting asynchronously. With asynchronous connections

DSN을 이용한 ODBC를 사용하지 말자. ODBC는 더이상 버그가 있어도 개발안되고있다.

사용자 컴퓨터에 ODBC드라이버를 설치해야하며, 레지스트리를 뒤지기 때문에

느리다.

마이크로소프트는 기본 ODBC 프로바이더보단 OLE DB를 쓰길 권장한다.

Another way to improve connection performance is to avoid using ODBC with DSNs

Microsoft recommends that you use OLE DB instead of the default ODBC provider

그리고 꼭 좀 썼으면 커넥션 Close하고 Set Nothing 좀 해줘라.

미들티어에서나 ASP에서는 실제적으로 커넥션을 계속 열고 있을 수 없다.

일반적으로 미들티어안의 컴포넌트나 ASP페이지는 로드되었다가 MS IIS에 의해서

언로드되어 생성되었다가 소멸되는것이 종종 이뤄진다. ADO 기반 코드는 반드시

연결성립, 사용, 해제하는 connection하는 코드를 매번 실행해야 한다. connection

셋팅을 치ㅗ소한으로 줄이기 위해서는 connection/session pool을 하면 좋다.

만일 코드에 Command 객체의 ConnectionString 속성이 같은 server, initial catalog,

login ID, 다른 속성들이 풀안에 있다면 풀의 것을 쓰게 되므로 좋다. 만일

풀안에 같은것을 쓰게 되면 connect나 reconnect는 250ms안에 이뤄질수있다.

그러나 만일 ADO 코드에서 Connection Object를 해제하지 않거나 ConnectionString

만 바꾸는 식으로 한다면 OLE DB는 새로운 Connection을 매번 성립하게 된다.

그렇게되면 가용 풀을 금새 다 써버리게 된다. 그러지 않으려면 커넥션은 close한다음

set nothing 해주어야 한다. 또한 Recordset Open메소드안의 ConnectionString은

쓰지말아야한다.

In the middle tier and in ASP, you can't (in a practical sense) hold a connection open and still create a scalable component?at least not between invocations. Typically, a component or ASP page is loaded and discarded frequently as Microsoft IIS references and releases instances. Because the ADO-based code must establish, use, and release a connection each time the code is executed, strategies to minimize connection setup can help in clearly measurable terms. For these scenarios, the connection/session pool plays a big role in how quickly you can get connected. If you code your Command object's ConnectionString property correctly (i.e., by using the same server, initial catalog, login ID, and other parameters each time), the chances of a connection being open and available are good. If a matching connection is found in the pool, the time to connect (or reconnect) will be virtually nil (usually less than 250ms).

However, if your ADO (or VB) code doesn't release your Connection object or if you change the ConnectionString from instance to instance, OLE DB has to establish a new connection every time. If that happens, you'll soon run out of connections as the available pool dries up. To make sure the connection is freed, set the Connection object to Nothing after closing it. Also, don't use a ConnectionString in the Recordset Open method; open the Connection object independently so that the Connection object is easy to reference when you close it and set it to Nothing.

                   Building and Submitting a Query

스토어드 프로시저 써라.

Don't force SQL Server to recompile and construct a query plan for your query each time it's executed

여러 쿼리시 스토어드 프로시저나 서버사이드 스크립트로 서버와의 통신량을 줄여라.

Make as few round-trips to the server as possible

서버/클라이언트 애플리케이션의 경우 Command Parameter 속성을 reset한 후 필요시

re-execute해서 써라.

In client/server applications, construct Command objects once, not each time they're used

역시나 스토어드 프로시저써라.

Try to get ADO to generate a direct remote procedure call

adExecuteNoRecords 써서 레코드셋 반한필요없을때 그거 쓰면 좋다.

Whenever your query doesn't return a rowset, be sure to use the adExecuteNoRecords option to tell ADO to bypass any code required to set up and receive a rowset (in Recordset form)

Don't use Command objects when you're executing simple rowset-returning stored procedures.

Don't return a rowset unless you must.

레코드셋 열어서 추정 삭제 하지말고 execute 액션 쿼리를 가능하면 좀 써줘라.

Whenever possible, execute action queries (INSERT, UPDATE, and DELETE statements or stored procedures that perform these operations) instead of using updateable Recordset cursors

가능하면 쏘트하라고좀 하지말아라. 힘들어 한다.

Don't ask the server to sort unless necessary

인덱스걸린 필드를 쿼리시 잘 이용해 빠르게 쿼리하자.

Know your index structure before you write your queries

WHERE 클래스구문에 범위를 잘 지정하거나 위에서 몇개만 쿼리해 오던지하자.

Don't return too many rows

Limit the scope of the query with parameter-driven WHERE clauses or through judicious use of TOP N queries

SELECT * FROM 하지 말자니까.

Don't return too many columns

필요치 않으면 커서를 사용치 말자.

Avoid using cursors

Don't ask for scrolling, updateability, or cached data if you don't need it.

Tell ADO what you want it to do in more detail

Be sure to include the CommandType option when opening a Recordset or building a Command object. This prevents ADO from "guessing" your intentions. You can save round-trips to the server and make your code more stable.

                      Processing the Results

RS("Cows") 이 놈보단

RS.Fields("Cows").Value 이게 약간더 빠르다. 하지만 둘다 late binding이다.

RS!Cows도 비슷하게 late binding이다.

RS(0)) 를 하면 late binding이지만 ordinal서식 컬럼으로해서 좀더 빠르게 할 수

있지만, 읽기 어렵다.

아래 처럼하면 읽기도 쉽고 빠르게 할 수 있겠다.

Enum을 사용하여 읽기 쉽고 디자인타임에 바인드되며 빠를 수 있다.

SELECT CatName, CatType, CatSize from Cats Where...

Enum enuCatsQuery

CatName

CatType

CatSize

End Enum

StrMyName = Rs(enuCatsQuery.CatName)

late binding을 피하는 다른 방법은 아래와 같은데 코드는 노가다지만

성능은 드라마틱하다

필드 개체를 선언

Dim fldName as ADODB.Field

Dim fldType as ADODB.Field

Dim fldSize as ADODB.Field

필드 prebind rs 얻어온 후

If fldName is Nothing then

Set fldName = RS!CatName

Set fldType = RS!CatType

Set fldSize = RS!CatSize

End if

쓸때는 스트링에 복사해서 쓰면 된다.

strName = fldName

strType = fldType

strSize = fldSize

가장 성능좋은 코드를 위해서 기본을 기억하라. 커넥션 풀쓰고, 비동기 커넥션,

ADO round-trip(서버와 왕복패킷 전달)을 줄이고 , COM-Binding을 통한 early바인딩

하고 불필요한 recordset, command 객체를 쓰지말고 rowset대신 Return status나

output 파라미터를 사용해라.

For best code and coder performance, remember these basics: Leverage the connection pool and asynchronous connections, reduce the number of round-trips your ADO code makes, choose an early COM-binding technique, avoid expensive and unnecessary ADO objects such as the Recordset and Command objects, and use the Return Status or Output parameters instead of rowsets. Always try to write intelligent queries and capitalize on stored procedures whenever possible. And tell ADO what you want it to do in detail to prevent it from having to guess?specify an explicit ADO CommandType, and use options such as adExecuteNoRecords

-------------------------------------------------------------------------

MDAC Technical Articles  

MDAC Technical Articles  

Improving MDAC Application Performance

Suresh Kannan

August 1999

Introduction

This document provides suggestions and guidelines for improving the performance of your MDAC application. The suggestions offered here constitute good coding practice. Performance might not noticeably improve unless accessing data is a significant part of your application processing.

Where possible, sample code has been provided to illustrate key points. In studying the samples, it is important to keep in mind the following considerations:

The best way to improve the performance of your application is to understand where your bottlenecks are. If your goal is to improve the speed of an existing application, you should start by profiling and analyzing your application.

This document describes general guidelines for improving your application performance. Some suggestions work only under certain circumstances. Where possible, such constraints have been called out. After you implement a change suggested in this document, you should measure your application's performance to validate benefits. Some modifications could be detrimental to your application performance if they are inappropriate for your application scenario.

Although Microsoft® Visual Basic® for Applications has been used to illustrate most of the suggestions, you can also apply suggested techniques to clients writing to ADO in other languages, such as C, Microsoft Visual Basic Scripting Edition (VBScript), Microsoft Visual C++®, and Java.

General Considerations

Use Strongly Typed Variables

A strongly typed variable is explicitly declared to represent only one variable type. Once declared, it cannot be used to represent a different variable type, as can weakly typed variables.

Microsoft Visual Basic allows weakly typed variables through the use of the Variant type. The Variant type can store almost any other kind of variable or object. While this gives you automatic type conversions so that you don't have to pay attention to the variable types, it can make debugging difficult. Occasionally, this automatic conversion will transform the data into a type that you didn't expect or intend, and tracking down where that happened is very difficult.

Microsoft Visual Basic defaults to the Variant type for all variables that are declared without a specific type and for all variables that are not declared at all. However, for better performance (unless the Variant type is specifically required for a property or method), avoid using Variant variables. Instead, use the Option Explicit statement to require declarations for all variables, and provide a specific type declaration for all variables that you declare. Alternatively, you can use the Deftype statements to change the default type for variables that are created and not declared or that are declared without a specific type.

Declare All Variables

A variable that is not declared will be created by Visual Basic with a default type. As discussed above, normally it will be implicitly created as a Variant. However, if you've used the Deftype statements, you can change the default.

Following is an example where the variables are implicitly created:

Public Sub NoExplicitDeclare(cnn)

  Set rsl = cnn.Execute("SELECT * FROM Nonsense")

  txt = rs1!SomeText.Value

  Debug.Print txt

End Sub

This code has a problem that the compiler won't catch, so you probably won't find it until you run the code. What is supposed to happen is that rsl should be assigned to a Recordset returned from the open Connection object cnn. However, when you run it, you'll get an error on the line txt = rs1!SomeText.Value. Visual Basic detects that rs1 is an empty Variant and generates a run-time error, "Object Required." In a more complex algorithm, you might be led to believe that the Execute method encountered an error. The real problem is that the variable rsl is misspelled as rs1. This problem is masked because the syntax and the identifiers appear correct at first glance.

If you place the Option Explicit statement at the top of the module, Visual Basic will generate a compiler error, and after you've declared rsl as a Recordset object and txt as a String, Visual Basic will highlight rs1 with the error "Variable not defined."

Next is an example where the variables are explicitly created:

Option Explicit

Public Sub NoExplicitDeclare(cnn As Connection)

  Dim rsl As Recordset

  Dim txt As String

  Set rsl = cnn.Execute("SELECT * FROM Nonsense")

  txt = rsl!SomeText.Value

  Debug.Print txt

  rs1.Close

  Set rs1 = Nothing

End Sub

Use Strongly Typed Object Variables

Object variables represent pointers to COM objects. They can be declared in two ways, as follows:

Use the keywords As Object so that the variable can be used to represent any kind of object; this is a weakly typed object variable.

Use the As keyword with the specific type of the object; this is a strongly typed object variable.

Microsoft Visual Basic must use late binding for all weakly typed object variables. This means Visual Basic must indirectly make every method and property call as each call is encountered at run time. Visual Basic does this by using the IDispatch interface to retrieve the identifier of each method and property function at run time and then calling IDispatch again to actually invoke the method or property. Although Visual Basic caches some of the information from IDispatch, using the late binding approach is still the slowest way to invoke methods and properties.

To create a strongly typed object variable, use the As keyword with the Dim, Private, Public, or Static statements and use the name of an object from one of the object libraries selected in the Project References dialog box. Visual Basic will read the object library at compile time to avoid calling IDispatch to get the identifiers of the methods and properties of the object. This is called early binding. In early binding, Visual Basic still invokes the properties and methods of the object through the IDispatch interface.

If the objects described in the object library use dual interfaces, it means they support not only the IDispatch interface but also a table of function addresses that mirror the properties and methods available through IDispatch. Visual Basic can then call the functions that handle the methods and properties of the object directly, bypassing the IDispatch interface altogether. This is called vtable binding, and it is the fastest way to invoke the properties and methods of an object. A vtable is simply a table of function addresses. If the object does not support dual interfaces, Visual Basic will use early binding instead.

All objects in ADO use dual interfaces, so Visual Basic can work several times faster with your ADO objects if you make your object variables strongly typed with the Dim variable As type statement. The type of the object is typically the name of an object from the object library, although it can also be the name of an interface. If you use the name of the object, Visual Basic silently substitutes the name of the default interface for that object.

Visual Basic chooses early or late binding depending on how you declare the object variable and not on how you create the object. In other words, it doesn't matter whether you use the Set variable = New type statement or the CreateObject function to create the object. What matters is how you declare the object variable. If you declare it as a specific type, Visual Basic will use early binding. If you declare the object variable as Object, Variant, or leave it undeclared, Visual Basic will use late binding.

The code in the StronglyTyped subroutine below uses the faster vtable binding by declaring the variable con as a Connection object:

Sub StronglyTyped()

  Dim lngState As Long

  Dim con As Connection

  Set con = New Connection

  con.Open "Provider=SQLOLEDB;Data Source=persons;" _

     & "Initial Catalog=Performance;User ID=sa;Password=;"

  lngState = con.State

  con.Close

  Set con = Nothing

End Sub

In the NotStronglyTyped subroutine below, the type of the Connection variable isn't known at compile time, so it's declared As Object. Visual Basic uses the slower late binding method to call the methods and properties of the same Connection object used in the StronglyTyped subroutine.

Sub NotStronglyTyped()

  Dim lngState As Long

  Dim con As Object

  Set con = New Connection

  con.Open "Provider=SQLOLEDB;Data Source=persons;" _

     & "Initial Catalog=Performance;User ID=sa;Password=;"

  lngState = con.State

  con.Close

  Set con = Nothing

End Sub

Explicitly Create Objects

Although object variables represent pointers to COM objects, merely declaring an object variable does not automatically create an instance of a COM object. Visual Basic offers two ways to create an instance of a COM object: implicit and explicit. While implicit creation can save some time in development, it usually costs you much more time in debugging and doesn't help at all in performance.

To implicitly create an object, use the As New keyword of the Dim statement. This permits Visual Basic to create the object automatically when you use the object variable and when the object has not been created already. When you use the Dim...As New... feature, Visual Basic adds code before every object reference to determine at run time whether the object is instantiated. This code automatically creates the object if the object variable is either not initialized or set to Nothing.

By using Dim... As New..., you'll lose some of the control over object references. For simple procedures, this won't be an issue. Visual Basic will automatically release all objects after the procedure has ended. You will take a negligible performance hit for using the automatic object creation feature that As New provides. Only you can decide this trade-off is worthwhile for simple procedures, but once you decide, be very consistent.

The following example shows implicit object creation with the As New keywords:

Sub AutomaticCreation()

  Dim rsNS As New Recordset

  rsNS.ActiveConnection = "Provider=SQLOLEDB;Data Source=persons;" _

     & "Initial Catalog=Performance;User ID=sa;Password=;"

  rsNS.CursorLocation = adUseServer

End Sub

There is an important reason why you might want to avoid Dim...As New in some circumstances, even for simple procedures. There are times when having an object variable set to Nothing is a valid, testable state. The Dim...As New feature prevents an object variable from ever being set to Nothing when you test its state. For example, the following snippet will always print, "n is set" to the Debug window:

  Dim n As New Recordset

  If n Is Nothing Then

     Debug.Print "n is nothing"

  Else

     Debug.Print "n is set"

  End If

In the preceding example, the very act of testing to see whether the object is created creates the object! In cases where the object is set to Nothing to indicate a valid condition, as is the case with the NextRecordset method of the Recordset object, you will never be able to detect that the object is set to Nothing.

To explicitly create an object, do not use the As New feature. If you use the object variable and the object has not been created already, Visual Basic will raise an error. You must use the Set statement to create the object when you will use it.

The following example shows explicit object creation:

Sub ExplicitCreation()

  Dim rsNS As Recordset

  Set rsNS = New Recordset

  rsNS.ActiveConnection = "Provider=SQLOLEDB;Data Source=persons;" _

     & "Initial Catalog=Performance;User ID=sa;Password=;"

  rsNS.CursorLocation = adUseServer

  Set rsNS = Nothing

End Sub

Reuse Command Objects

If you are going to use a particular stored procedure, view, or SQL statement several times, don't create a new Command object each time. Use a static variable or a module-level variable to keep a reference to each Command object that you will reuse.

Note   This technique might not work well for VBScript running in Active Server Pages, or for applications written for Microsoft Component Transaction Services.

Bind Columns to Field Objects When Looping Through the Records

There are at least two common ways to get the values for fields in a Recordset. One way is to look up each field by its name or ordinal position from the Fields collection of the Recordset object each time you need the value. The other way is to reuse the Field objects in the Fields collection as you iterate through the records in the Recordset.

The code in ColumnsNotBound looks up fields by ordinal position. This incurs the overhead of looking up each field in the Fields collection for each record in the Recordset. For a Recordset with many records, this can get quite expensive.

Sub ColumnsNotBound()

  Dim rsNS As Recordset

  Dim strText As String

  Dim strMoney As String

  Set rsNS = New Recordset

  rsNS.Open "SELECT SomeText, SomeNumber, SomeTime, SomeMoney " _

     "FROM Nonsense", "Provider=SQLOLEDB;Data Source=persons;" _

     & "Initial Catalog=Performance;User ID=sa;Password=;"

  Do Until rsNS.EOF

     strText = rsNS.Fields(0).Value

     strMoney = rsNS.Fields(3).Value

     rsNS.MoveNext

  Loop

  rsNS.Close

  Set rsNS = Nothing

End Sub

The code in ColumnsBound, however, obtains references to the Field objects at the beginning and simply uses those same references when looping through the records.

Sub ColumnsBound()

  Dim rsNS As Recordset

  Dim strText As String

  Dim strMoney As String

  Dim fldText As Field

  Dim fldMoney As Field

  Set rsNS = New Recordset

  rsNS.Open "SELECT SomeText, SomeNumber, SomeTime, SomeMoney " _

     "FROM Nonsense", "Provider=SQLOLEDB;Data Source=persons;" _

     & "Initial Catalog=Performance;User ID=sa;Password=;"

  Set fldText = rsNS.Fields(0)

  Set fldMoney = rsNS.Fields(3)

  Do Until rsNS.EOF

     strText = fldText.Value

     strMoney = fldMoney.Value

     rsNS.MoveNext

  Loop

  rsNS.Close

  Set rsNS = Nothing

End Sub

Use Cursor-Based Updating Only If Needed

Try to avoid the use of cursor-based updating. Although using an SQL statement to update data is not feasible in many scenarios, you should use it where possible. Although updating data through the Recordset object is often more convenient, it is also much more expensive. Despite being cumbersome to use, updating data through an SQL statement is well worth the trouble. The routine that uses SQL will update several dozen records in a 5,000 record table about 30 times faster than the routine that uses a cursor.

The following example shows cursor-based updating:

Sub ADOUpdate()

  Dim cnNS As Connection

  Dim rsNS As Recordset

  Dim fldText As Field

  Dim fldNumber As Field

  Dim lngUpdateEach As Long

  lngUpdateEach = Rnd * 99 + 1

  Set cnNS = New Connection

  cnNS.ConnectionString = "Provider=SQLOLEDB;Data Source=persons;" _

     & "Initial Catalog=Performance;User ID=sa;Password=;"

  cnNS.Open

  Set rsNS = New Recordset

  Set rsNS.ActiveConnection = cnNS

  rsNS.CursorLocation = adUseClient

  rsNS.CursorType = adOpenStatic

  rsNS.LockType = adLockBatchOptimistic

  rsNS.Open "Nonsense", , , , adCmdTable

  Set fldText = rsNS!SomeText

  Set fldNumber = rsNS!SomeNumber

  rsNS.MoveFirst

  Do Until rsNS.EOF

     If fldNumber.Value Mod lngUpdateEach = 0 Then

        fldText.Value = UCase$(fldText.Value)

     End If

     rsNS.MoveNext

  Loop

  rsNS.UpdateBatch

  rsNS.Close

  cnNS.Close

  Set rsNS = Nothing

  Set cnNS = Nothing

End Sub

The next example uses an SQL statement to update data:

Sub SQLUpdate()

  Dim cnNS As Connection

  Dim lngUpdateEach As Long

  Dim cmNS As Command

  Set cmNS = New Command

  lngUpdateEach = Rnd * 99 + 1

  Set cnNS = New Connection

  cnNS.Open "Provider=SQLOLEDB;Data Source=persons;" _

     & "Initial Catalog=Performance;User ID=sa;Password=;"

  Set cmNS.ActiveConnection = cnNS

  cmNS.CommandText = "UPDATE Nonsense SET SomeText = UPPER(SomeText) " _

     & "WHERE SomeNumber % " & lngUpdateEach & " = 0"

  cmNS.CommandType = adCmdText

  cmNS.Prepared = True

  cmNS.Execute , , adExecuteNoRecords

  cnNS.Close

  Set cnNS = Nothing

  Set cmNS = Nothing

End Sub

Use a Stored Procedure with Output Parameters Instead of Singleton Selects

When you know that the result of your query will yield only a single row of data, instead of opening a Recordset for fetching that data, you can use a stored procedure with output parameters.

When you use a Recordset, the query results returned by the data source object include data and metadata. Often the metadata is much larger than the data or is a significant part of the query results. Because of this, you may want to use a stored procedure with output parameters instead.

The following example shows a singleton select statement:

Sub SingletonSelect()

  Dim rs As Recordset

  Dim strText As String

  Dim timTime As Date

  Dim n As Integer

  Set rs = New Recordset

  rs.ActiveConnection = "Provider=SQLOLEDB;Data Source=persons;" _

     & "Initial Catalog=Performance;User ID=sa;Password=;"

  rs.CursorLocation = adUseServer

  rs.CursorType = adOpenForwardOnly

  rs.LockType = adLockReadOnly

  For n = 1 To 50

     rs.Source = "SELECT sometext, sometime FROM nonsense " & _

        "WHERE ID = '" & CStr(CLng(Rnd * 5000)) & "'"

     rs.Open

     strText = rs.Fields(0).Value

     timTime = rs.Fields(1).Value

     rs.Close

  Next

  Set rs = Nothing

End Sub

The next example retrieves two output parameters from a stored procedure that selects a single record:

Sub SingletonSp()

  Dim cmd As Command

  Dim strText As String

  Dim timTime As Date

  Dim n As Integer

  Set cmd = New Command

  cmd.ActiveConnection = "Provider=SQLOLEDB;Data Source=persons;" _

     & "Initial Catalog=Performance;User ID=sa;Password=;"

  cmd.CommandText = "GetTextTimeUsingID"

  cmd.CommandType = adCmdStoredProc

  cmd.Parameters.Append _

     cmd.CreateParameter("inID", adInteger, adParamInput, 4)

  cmd.Parameters.Append _

     cmd.CreateParameter("outText", adChar, adParamOutput, 32)

  cmd.Parameters.Append _

     cmd.CreateParameter("outTime", adDate, adParamOutput, 8)

  For n = 1 To 50

     cmd(0).Value = Rnd * 5000

     cmd.Execute , , adExecuteNoRecords

     strText = cmd(1).Value

     timTime = cmd(2).Value

  Next

  Set cmd = Nothing

End Sub

Here is the code for the stored procedure:

  CREATE PROCEDURE [GetTextTimeUsingID]

  @inID int = 0,

  @outText char(32) OUTPUT,

  @outTime datetime OUTPUT

  AS

  IF @inID = 0 RETURN 2

  SELECT @outText = SomeText, @outTime = SomeTime FROM Nonsense

     WHERE ID = @inID

  RETURN

If You Must Use a Cursor, Use the Collect Methods for Singleton Selects

The Collect method is a hidden method of the Recordset object that lets you quickly get and set the Value property of a Field object without having to obtain a field reference first. This method is appropriate to use when you aren't interested in obtaining or setting any properties other than Field.Value.

Following is an example using the Collect method:

Sub Collect()

  Dim rs As Recordset

  Dim strText As String

  Dim timTime As Date

  Set rs = New Recordset

  rs.ActiveConnection = "Provider=SQLOLEDB;Data Source=persons;" _

     & "Initial Catalog=Performance;User ID=sa;Password=;"

  rs.Source = "SELECT SomeText, SomeNumber, SomeTime, SomeMoney " & _

     "FROM Nonsense WHERE ID = '2500'"

  rs.Open

  strText = rs.Collect(0)

  timTime = rs.Collect(2)

  rs.Close

  Set rs = Nothing

End Sub

Query Only for the Data You Need

Although it is easy to fall into the habit of using SELECT * queries, consider asking only for the columns you need. Also, consider adding restrictions, such as WHERE clauses, to your query to limit the records returned.

Choose CursorLocation, CursorType, and LockType Properties Carefully

If you don't need scrolling or updatability, don't ask for it. The ADO defaults of adUseServer, adOpenForwardOnly, and adLockReadOnly offer you the best performance for doing a forward-only scan through the records. Don't ask for a more functional cursor if your application doesn't require it.

If you do want scrolling, don't default to server cursors as ADO does. The ADO CursorLocation default is adUseServer primarily for backward compatibility reasons. However, for most scrolling scenarios, you will be much better off with a client cursor. Only in specific scenarios, such as extremely large data sets, will you be better off with a server cursor. When using a client cursor, don't ask for anything other than a LockType value of adLockReadOnly unless you really need it. If you ask for updatability, the client cursor engine needs to get additional metadata, which can be very expensive to retrieve.

Tune the Recordset.CacheSize Property

ADO uses the Recordset.CacheSize property to determine the number of rows to fetch and cache. This especially affects server-side cursors. While you are within the range of cached rows, ADO just returns data from the cache. When you scroll out of the range of cached rows, ADO releases the cache and fetches the next CacheSize rows. The default value for the CacheSize property is 1.

How do you determine what value you should use for the CacheSize property in your application? Unfortunately, there isn't a single optimal CacheSize value for all applications. You should try tuning your application with different CacheSize values, and use the value that offers you the best performance. For example, knowing that a small CacheSize value significantly improves performance for fetching data from an Oracle data store might be an important factor for you.

Release Unused ADO Objects

Whenever possible, release ADO objects as soon as you're done with them. This frees up the database and other resources that might be expensive to hold for an extended period. Explicitly close all objects rather than allowing the object to close itself as it is destroyed.

Describe Command Parameters Yourself

In many data stores, getting command parameter information is often as expensive as executing the command. Describe the command parameters yourself instead of getting the parameter information from the provider.

The following example shows how to get the parameter information from the provider:

Sub ProviderDescribedParameters()

  Dim cmd As Command

  Dim lngRetVal As Long

  Dim strText As String

  Dim timTime As Date

  Set cmd = New Command

  cmd.ActiveConnection = "Provider=SQLOLEDB;Data Source=persons;" _

     & "Initial Catalog=Performance;User ID=sa;Password=;"

  cmd.CommandText = "GetTextTimeUsingID"

  cmd.CommandType = adCmdStoredProc

  cmd.Parameters.Refresh

  cmd.Parameters(1).Value = 100

  cmd.Execute

  lngRetVal = cmd.Parameters(0).Value

  strText = cmd.Parameters(2).Value

  timTime = cmd.Parameters(3).Value

  Set cmd = Nothing

End Sub

The next example shows how to describe the parameters manually:

Sub UserDescribedParameters()

  Dim cmd As Command

  Dim lngRetVal As Long

  Dim strText As String

  Dim timTime As Date

  Set cmd = New Command

  cmd.ActiveConnection = "Provider=SQLOLEDB;Data Source=persons;" _

     & "Initial Catalog=Performance;User ID=sa;Password=;"

  cmd.CommandText = "GetTextTimeUsingID"

  cmd.CommandType = adCmdStoredProc

  cmd.Parameters.Append _

     cmd.CreateParameter("Return Value", adInteger, adParamReturnValue)

  cmd.Parameters.Append _

     cmd.CreateParameter("inID", adInteger, adParamInput)

  cmd.Parameters.Append _

     cmd.CreateParameter("outText", adChar, adParamOutput, 32)

  cmd.Parameters.Append _

     cmd.CreateParameter("outTime", adDate, adParamOutput, 8)

  cmd.Parameters(1).Value = 100

  cmd.Execute

  lngRetVal = cmd.Parameters(0).Value

  strText = cmd.Parameters(2).Value

  timTime = cmd.Parameters(3).Value

  Set cmd = Nothing

End Sub

Use Native OLE DB Providers

MDAC ships with native providers for several data stores, including SQL Server, Oracle, and Microsoft Jet (.mdb). With earlier versions, you had to go through the OLE DB Provider for ODBC, which in turn used the appropriate ODBC driver to access these data stores. The provider used as the default for connections is still the OLE DB Provider for ODBC, but you should use these native OLE DB providers so that you can access your data faster and with lower disk and memory footprint. The SQL Server provider is written to TDS, the Oracle provider to OCI, and the Microsoft Jet provider to the Microsoft Jet Engine API.

Disconnect Your Client Cursor from the Connection for R/O and Long-Use Scenarios

Disconnected Recordset objects are supported by the client cursor engine. Use this feature when you are performing a time-consuming operation that doesn't require expensive database resources to be held open. If you need to, you can later reconnect the Recordset to the connection to perform updates.

Following is an example that shows how to disconnect a Recordset:

Sub DisconnectRS()

  Dim con As Connection

  Dim rs As Recordset

  Set con = New Connection

  Set rs = New Recordset

  con.CursorLocation = adUseClient

  con.Open "Provider=SQLOLEDB;Data Source=persons;" _

     & "Initial Catalog=Performance;User ID=sa;Password=;"

  Set rs = con.Execute("SELECT SomeText, SomeNumber, SomeTime, " &  _

     "SomeMoney FROM Nonsense")

  Set rs.ActiveConnection = Nothing

  Set con = Nothing

  '

  ' Process data

  '

  rs.Close

  Set rs = Nothing

End Sub

Anticipate Non?Row Returning Commands

ADO includes an ExecuteOptionEnum option called adExecuteNoRecords. Use this option for commands that do not return rows. When this option is specified, ADO does not create a Recordset object, does not set any cursor properties, and specifies IID_NULL REFIID on ICommand::Execute. Also, because IID_NULL is specified on ICommand::Execute, the provider can optimize for this case by not verifying any rowset properties.

The following example demonstrates how the adExecuteNoRecords option is used:

Sub ExecuteNoRecords()

  Dim con As Connection

  Set con = New Connection

  con.Open strConnection

  con.Execute "INSERT INTO Nonsense VALUES('" & Greeking(32) & _

     "', " & CStr(CLng(Rnd * &H7FFFFFFF)) & _

     ", " & CStr(CLng(Now)) & _

     ", " & CStr(CCur(curStart)) & ",DEFAULT ,DEFAULT, DEFAULT, " & _

     "DEFAULT, DEFAULT, DEFAULT, DEFAULT, DEFAULT)", , _

     adExecuteNoRecords

  con.Close

  Set con = Nothing

End Sub

Use Connection.Execute for Single Execution

ADO has some optimizations for one-time command executions when the executions are done through the Execute method of the Connection object. This is a common user scenario in Microsoft Internet Information Server (IIS), Active Server Pages (ASP), and Microsoft Component Services (formerly Microsoft Transaction Server) environments, where the code typically opens a connection, executes a row returning or non?row returning command, processes results, and closes the connection. For such scenarios, use Connection.Execute instead of Recordset.Open or Command.Execute. When you use Connection.Execute, ADO doesn't preserve any command state information, which leads to an improvement in performance. However, if you need a more functional cursor or if you need to use Command.Parameters, you might still need to use Recordset.Open or Command.Execute.

Use ADO C++ Extensions (C++ Users)

ADO is an Automation server, which means it implements Automation interfaces. Therefore, it provides language-independent access to OLE DB. However, although ADO interfaces are accessible from C, C++, and Java, structures such as VARIANTs, which are easy to use from Visual Basic for Applications (VBA), are quite cumbersome to use from C, C++, and Java.

In versions of Microsoft Visual C++ earlier than 5.0, Automation interfaces were hard to use because of lack of language support for COM and VARIANTs. Now, with native compiler support for COM in Visual C++ 5.0, Automation interfaces are much easier to use from Visual C++. However, if you still want to fetch data into C types instead of VARIANTs, you can do so by using the ADO C++ extensions. Besides avoiding the VARIANT overhead, the C++ extensions offer good performance. When you use them, ADO doesn't need to get the column information from the provider. Instead, ADO uses the column information supplied at design time in the form of binding entries.

The following C++ code example shows how to use the ADO C++ extensions to get the values from three VARCHAR fields.

class CAuthor :

  public CADORecordBinding

{

BEGIN_ADO_BINDING(CCustomRs1)

  ADO_VARIABLE_LENGTH_ENTRY4(1, adVarChar, m_szau_id,

     sizeof(m_szau_id), FALSE)

  ADO_VARIABLE_LENGTH_ENTRY4(2, adVarChar, m_szau_fname,

     sizeof(m_szau_fname), FALSE)

  ADO_VARIABLE_LENGTH_ENTRY4(3, adVarChar, m_szau_lname,

     sizeof(m_szau_lname), FALSE)

END_ADO_BINDING()

protected:

  char m_szau_id[12];

  char m_szau_fname[21];

  char m_szau_lname[41];

};

void FetchAuthorData()

{

  CAuthor author;

  _RecordsetPtr pRs;

  IADORecordBinding *piAdoRecordBinding;

  pRs.CreateInstance(__uuidof(Recordset));

  pRs->Open("select au_id, au_fname, au_lname from Employees",

     "Provider=SQLOLEDB;Data Source=sureshk1;Database=pubs;"

     "User Id=sa;Password=;",  

     adOpenForwardOnly, adLockReadOnly, adCmdText);

  pRs->QueryInterface(__uuidof(IADORecordBinding),

     (LPVOID *)&piAdoRecordBinding);

  piAdoRecordBinding->BindToRecordset(&author);

  while (VARIANT_FALSE == pRs->EOF)

  {

     printf("%s %s %s", author.m_szau_id, author.m_szau_fname,

        author.m_szau_lname);

     pRs->MoveNext();

  }

  piAdoRecordBinding->Release();

}

Middle-Tier Considerations

Use Session (OLE DB) or Connection (ODBC) Pooling

A database connection is an expensive resource to open and close. Therefore, pooling this resource offers a huge performance improvement for middle-tier applications.

When you use MDAC, you don't have to worry about how to pool your database connections. MDAC takes care of it for you. Pooling is supported at two levels: OLE DB sessions and ODBC connections. If you use ADO, your database connections are pooled automatically by OLE DB session pooling. If you use ODBC, the new Connection Pooling tab in the ODBC Data Source Administrator lets you control the ODBC connection pooling settings and the ODBC Driver Manager takes care of pooling for you.

Use ADO Like an Apartment Model

Although the ADO implementation is free-threaded, don't use it in that way in the middle tier. Don't cache an instance of an ADO object, such as a Connection, globally and invoke methods on it concurrently from multiple threads. If each client request in your application model invokes the Connection.Execute method on a globally cached Connection object on the middle tier, your application will not scale. This is because of synchronization in the Connection.Execute method.

You will get much better throughput by using an application model where each client request creates a new instance of a Connection object, calls Connection.Open and then Connection.Execute, and releases the Connection object on the middle tier. Each request does have the additional overhead of creating a new instance of a Connection object and obtaining a connection from the connection pool. However, because your Connection.Execute call isn't synchronized, the throughput is much better.

ASP Considerations

Don't cache ADO objects in Global.asa. See "Use ADO Like an Apartment Model" earlier in this document.

Conclusion

Improving the performance of any application is both a science and an art. The recommendations listed here will help with many areas, but every application has different circumstances. The environments may differ from one installation to the next. A setting that makes the application run faster on one machine may make it run slower on a different machine. Even different database schemas will affect many of the suggestions in this article.

There is no substitute for advanced techniques like code profiling, performance monitoring, and good old trial and error. As with any scientific approach, you should vary only one element at a time and note whether performance improves or worsens with each variation. If performance deteriorates, before assuming that the change was the cause of the problem, revert the change and verify that you can reproduce the original behavior.

정리

ADO에 관련된 성능 향상을 위한 여러 문서를 정리하여 보았습니다.

많은 문서에서 ADO 성능 향상을 위해서 여러 방면으로 설명하고 있으나, 대부분 골지는 제가 처음에 정리한 몇가지 설명으로 충분할 듯 합니다.

ADO 데이타 바인딩 다이얼로그 얻기

조경민 오전 10:24 2001-12-24 bro@shinbiro.com

[ADO 데이타 바인딩 다이얼로그 얻기]

VC++에서 ADO를 이용해서 데이타베이스 간단한 레코드 폼을 얻고 싶을떄 다음처럼 하면 기본적인 코드가 자동으로 완성된다.

Project / Add to Project 한 후 Components and Controls에서

Visual C++ Components 안에 ADO Data Bound Dialog를 선택한다.

원하는 DB 연결을 바인드 위자드에서 선택하면 CRsCgDlg 라는 클래스가 생긴다. 바로 DoModal 시키면 레코드 폼이 나온다.

데이타필드가 에디트컨트롤로 자동으로 부여되고, 데이타 네비게이터 버튼도 있다.

다음은 RsCgDlg를 쓴 간단한 예제이다.

(추가/삭제/수정 및 네비게이션, 검색에 대한 코드를 넣었다. 쓸때 참고하면 좋겠다. )

// RsCgDlg.cpp : implementation file

//

#include "stdafx.h"

#include "Common.h"

#include "RsCgDlg.h"

#include "VirusRecordDlg.h"

#include "DBLogic.h"

#ifdef _DEBUG

#define new DEBUG_NEW

#undef THIS_FILE

static char THIS_FILE[] = __FILE__;

#endif

/////////////////////////////////////////////////////////////////////////////

// CRsCgDlg dialog

#define WM_SEARCH               (WM_USER+100)

CRsCgDlg::CRsCgDlg(CWnd* pParent /*=NULL*/)

   : CDialog(CRsCgDlg::IDD, pParent)

{

   m_strConnection.Format("Provider=Microsoft.Jet.OLEDB.4.0;Data Source=%s\\scanx.mdb;Persist Security Info=False",

       fnBroGetDir());

   m_strCmdText = _T("VIRUS");

   m_pRs = NULL;

   //{{AFX_DATA_INIT(CRsCgDlg)

   m_lDlgiD = 0;

   m_strDlgNAME = _T("");

   m_strDlgFNAME = _T("");

   m_lDlgSIZE = 0;

   //m_strDlgBIN = _T("");

   m_strDlgINFO = _T("");

   m_strDlgTREAT = _T("");

   m_nRec = 0;

   m_nTotRec = 0;

   //}}AFX_DATA_INIT

}

CRsCgDlg::~CRsCgDlg()

{

   m_pRs = NULL;

}

BOOL CRsCgDlg::OnInitDialog()

{

   CDialog::OnInitDialog();

   // 기본으로 전체를 얻어온다.

   Open( m_strCmdText );

   return TRUE;

}

void CRsCgDlg::RefreshBoundData()

{

   if (adFldOK == liDStatus)

       m_lDlgiD = m_liD;

   else

       m_lDlgiD = 0;

   if (adFldOK == lNAMEStatus)

       m_strDlgNAME = m_wszNAME;

   else

       m_strDlgNAME = _T("");

   if (adFldOK == lFNAMEStatus)

       m_strDlgFNAME = m_wszFNAME;

   else

       m_strDlgFNAME = _T("");

   if (adFldOK == lSIZEStatus)

       m_lDlgSIZE = m_lSIZE;

   else

       m_lDlgSIZE = 0;

   //if (adFldOK == lBINStatus)

   //  m_strDlgBIN = m_wszBIN[0];

   //else

   //  m_strDlgBIN = _T("");

   if (adFldOK == lINFOStatus)

       m_strDlgINFO = m_wszINFO;

   else

       m_strDlgINFO = _T("");

   if (adFldOK == lTREATStatus)

   {

       switch( m_wszTREAT[0] )

       {

       case SCANX_TREAT_IGNORE :

           m_strDlgTREAT = _T("무시/로그");

           break;

       case SCANX_TREAT_ALERT :

           m_strDlgTREAT = _T("알림/선택");

           break;

       case SCANX_TREAT_TERMINATE :

           m_strDlgTREAT = _T("강제종료");

           break;

       case SCANX_TREAT_DELETE :

           m_strDlgTREAT = _T("종료/삭제");

           break;

       }

       //m_strDlgTREAT = m_wszTREAT;

   }

   else

       m_strDlgTREAT = _T("");

   UpdateData(FALSE);

}

void CRsCgDlg::GenerateError(HRESULT hr, PWSTR pwszDescription)

{

   CString strError;

   strError.Format("Run-time error '%d (%x)'", hr, hr);

   strError += "\n\n";

   strError += pwszDescription;

   AfxMessageBox(strError);

}

void CRsCgDlg::DoDataExchange(CDataExchange* pDX)

{

   CDialog::DoDataExchange(pDX);

   //{{AFX_DATA_MAP(CRsCgDlg)

   DDX_Control(pDX, IDC_EDIT_SEARCH_NAME, m_edtSearchName);

   DDX_Text(pDX, IDC_NAME, m_strDlgNAME);

   DDX_Text(pDX, IDC_FNAME, m_strDlgFNAME);

   DDX_Text(pDX, IDC_FSIZE, m_lDlgSIZE);

   //DDX_Text(pDX, IDC_BIN, m_strDlgBIN);

   DDX_Text(pDX, IDC_INFO, m_strDlgINFO);

   DDX_Text(pDX, IDC_TREAT, m_strDlgTREAT);

   DDX_Text(pDX, IDC_STATIC_REC, m_nRec);

   DDX_Text(pDX, IDC_STATIC_TOTREC, m_nTotRec);

   //}}AFX_DATA_MAP

}

BEGIN_MESSAGE_MAP(CRsCgDlg, CDialog)

   //{{AFX_MSG_MAP(CRsCgDlg)

   ON_BN_CLICKED(IDC_MOVEFIRST, OnMovefirst)

   ON_BN_CLICKED(IDC_MOVELAST, OnMovelast)

   ON_BN_CLICKED(IDC_MOVENEXT, OnMovenext)

   ON_BN_CLICKED(IDC_MOVEPREV, OnMoveprev)

   ON_BN_CLICKED(IDC_SEARCHNAME, OnSearchname)

   ON_BN_CLICKED(IDC_ADD, OnAdd)

   ON_BN_CLICKED(IDC_CLOSE, OnClose)

   ON_BN_CLICKED(IDC_DEL, OnDel)

   ON_BN_CLICKED(IDC_EDIT, OnEdit)

   //}}AFX_MSG_MAP

   ON_MESSAGE( WM_SEARCH, OnSearch )

END_MESSAGE_MAP()

/////////////////////////////////////////////////////////////////////////////

// CRsCgDlg message handlers

void CRsCgDlg::OnMovefirst()

{

   try

   {

       // 처음으로 돌아간다.

       m_pRs->MoveFirst();

       m_nRec = 1;

       // 화면갱신

       RefreshBoundData();

       // 네비게이터 버튼 활성화 조정

       GetDlgItem(IDC_MOVEFIRST)->EnableWindow(FALSE);

       GetDlgItem(IDC_MOVEPREV)->EnableWindow(FALSE);

       GetDlgItem(IDC_MOVELAST)->EnableWindow(TRUE);

       GetDlgItem(IDC_MOVENEXT)->EnableWindow(TRUE);

   }

   catch (_com_error &e)

   {

       GenerateError(e.Error(), e.Description());

   }

}

void CRsCgDlg::OnMovelast()

{

   try

   {

       m_pRs->MoveLast();

       m_nRec = m_nTotRec;

       // 화면갱신

       RefreshBoundData();

       // 네비게이터 버튼 활성화 조정

       GetDlgItem(IDC_MOVELAST)->EnableWindow(FALSE);

       GetDlgItem(IDC_MOVENEXT)->EnableWindow(FALSE);

       GetDlgItem(IDC_MOVEFIRST)->EnableWindow(TRUE);

       GetDlgItem(IDC_MOVEPREV)->EnableWindow(TRUE);

   }

   catch (_com_error &e)

   {

       GenerateError(e.Error(), e.Description());

   }

}

void CRsCgDlg::OnMovenext()

{

   try

   {

       m_pRs->MoveNext();

       m_nRec++;

       // 화면갱신

       RefreshBoundData();

       // 네비게이터 버튼 활성화 조정

       m_pRs->MoveNext();

       if( m_pRs->EndOfFile )

       {

           GetDlgItem(IDC_MOVELAST)->EnableWindow(FALSE);

           GetDlgItem(IDC_MOVENEXT)->EnableWindow(FALSE);

       }

       m_pRs->MovePrevious();

       GetDlgItem(IDC_MOVEFIRST)->EnableWindow(TRUE);

       GetDlgItem(IDC_MOVEPREV)->EnableWindow(TRUE);

   }

   catch (_com_error &e)

   {

       GenerateError(e.Error(), e.Description());

   }

}

void CRsCgDlg::OnMoveprev()

{

   try

   {

       m_pRs->MovePrevious();

       m_nRec--;

       // 화면갱신

       RefreshBoundData();

       // 네비게이터 버튼 활성화 조정

       m_pRs->MovePrevious();

       if( m_pRs->BOF )

       {

           GetDlgItem(IDC_MOVEFIRST)->EnableWindow(FALSE);

           GetDlgItem(IDC_MOVEPREV)->EnableWindow(FALSE);

       }

       m_pRs->MoveNext();

       GetDlgItem(IDC_MOVELAST)->EnableWindow(TRUE);

       GetDlgItem(IDC_MOVENEXT)->EnableWindow(TRUE);

   }

   catch (_com_error &e)

   {

       GenerateError(e.Error(), e.Description());

   }

}

void CRsCgDlg::Open( LPCTSTR lpszSQL )

{

   HRESULT hr = NOERROR;

   IADORecordBinding *piAdoRecordBinding = NULL;

   try

   {

       m_nRec = 0;

       m_pRs = NULL;

       m_pRs.CreateInstance(__uuidof(Recordset));

       m_pRs->CursorLocation = adUseClient;

       m_pRs->Open((LPCTSTR)lpszSQL, (LPCTSTR)m_strConnection, adOpenStatic,

           adLockBatchOptimistic, adCmdTableDirect);

       if (FAILED(hr = m_pRs->QueryInterface(__uuidof(IADORecordBinding), (LPVOID *)&piAdoRecordBinding)))

           _com_issue_error(hr);

       if (FAILED(hr = piAdoRecordBinding->BindToRecordset(this)))

           _com_issue_error(hr);

       if( m_pRs->EndOfFile && m_pRs->BOF)

       {

           m_nTotRec = 0;

       }

       else

       {

           m_pRs->MoveLast();

           m_pRs->MoveFirst();

           m_nTotRec = m_pRs->GetRecordCount();

           m_nRec = 1;

       }

       // 추가 삭제 수정 버튼 Enable 할것인지 결정

       BOOL bEnableOpt = TRUE;

       if( m_nTotRec == 0 )

       {

           bEnableOpt = FALSE;

           m_strDlgNAME = _T("");

           m_strDlgFNAME = _T("");

           m_lDlgSIZE = 0;

           m_strDlgINFO = _T("");

           m_strDlgTREAT = _T("");

       }

       GetDlgItem(IDC_ADD)->EnableWindow(bEnableOpt);

       GetDlgItem(IDC_DEL)->EnableWindow(bEnableOpt);

       GetDlgItem(IDC_EDIT)->EnableWindow(bEnableOpt);

       if( m_nTotRec < 2)

       {

           // 레코드가 없거나 하나다

           GetDlgItem(IDC_MOVEFIRST)->EnableWindow(FALSE);

           GetDlgItem(IDC_MOVEPREV)->EnableWindow(FALSE);

           GetDlgItem(IDC_MOVENEXT)->EnableWindow(FALSE);

           GetDlgItem(IDC_MOVELAST)->EnableWindow(FALSE);

       }

       else

       {

           // 레코드가 있다.

           GetDlgItem(IDC_MOVEFIRST)->EnableWindow(FALSE);

           GetDlgItem(IDC_MOVEPREV)->EnableWindow(FALSE);

           GetDlgItem(IDC_MOVENEXT)->EnableWindow(TRUE);

           GetDlgItem(IDC_MOVELAST)->EnableWindow(TRUE);

       }

       if( m_nTotRec != 0 )

           RefreshBoundData();

       else

           UpdateData(FALSE);

   }

   catch (_com_error &e)

   {

       GenerateError(e.Error(), e.Description());

   }

   if (piAdoRecordBinding)

       piAdoRecordBinding->Release();  

}

LRESULT CRsCgDlg::OnSearch( WPARAM wparam, LPARAM lparam )

{

   CString sSearchName;

   m_edtSearchName.GetWindowText(sSearchName);

   CString sSQL;

   // 원하는 SQL문을 만들어낸다.

   sSQL.Format("SELECT * FROM VIRUS WHERE NAME like '%s", sSearchName );

   sSQL+="%'";

   Open(sSQL);

   CString sMsg;

   sMsg.Format("총 %lu개의 레코드가 검색되었습니다.", m_nTotRec );

   MessageBox( sMsg, "검색 완료" );

   return 1;

}

BOOL CRsCgDlg::PreTranslateMessage(MSG* pMsg)

{

   // TODO: Add your specialized code here and/or call the base class

   if( pMsg->message == WM_KEYDOWN && pMsg->wParam == VK_RETURN )

   {

       if( GetFocus() == &m_edtSearchName )

           PostMessage( WM_SEARCH );

   }

   return CDialog::PreTranslateMessage(pMsg);

}

void CRsCgDlg::OnSearchname()

{

   // TODO: Add your control notification handler code here

   PostMessage( WM_SEARCH );

}

void CRsCgDlg::OnAdd()

{

   // TODO: Add your control notification handler code here

   CVirusRecordDlg dlg;

   // 추가창 모드로 연다.

   dlg.m_nMode = MODE_INSERT;

   if( dlg.DoModal() == IDOK )

   {

       CString sBin,sFName;

       if( !CDBLogic::GetBin( dlg.m_sFilePath , &sBin) )

           return;

       try

       {

           m_pRs->AddNew();

           m_pRs->Fields->GetItem("NAME")->put_Value( _variant_t(dlg.m_sName) );

           m_pRs->Fields->GetItem("FNAME")->put_Value( _variant_t(dlg.m_sFileName) );

           m_pRs->Fields->GetItem("SIZE")->put_Value( _variant_t(dlg.m_sSize) );

           m_pRs->Fields->GetItem("BIN")->put_Value( _variant_t(sBin) );

           m_pRs->Fields->GetItem("INFO")->put_Value( _variant_t(dlg.m_sInfo) );

           m_pRs->Fields->GetItem("TREAT")->put_Value( _variant_t(CString(dlg.m_sTreat[0])) );

           m_pRs->Update();

           //m_pRs->Requery(adCmdTable);

           m_pRs->UpdateBatch(adAffectCurrent);

           //RefreshBoundData();  

       }

       catch (_com_error &e)

       {

           GenerateError(e.Error(), e.Description());

       }      

       Open( _T("VIRUS"));

   }

}

void CRsCgDlg::OnClose()

{

   // TODO: Add your control notification handler code here

   EndDialog(IDOK);

}

void CRsCgDlg::OnDel()

{

   // TODO: Add your control notification handler code here

   try

   {

       m_pRs->Delete(adAffectCurrent);

       m_pRs->UpdateBatch(adAffectCurrent);

   }

   catch (_com_error &e)

   {

       GenerateError(e.Error(), e.Description());

   }      

   //RefreshBoundData();  

   Open( _T("VIRUS"));

}

void CRsCgDlg::OnEdit()

{

   // TODO: Add your control notification handler code here

   CVirusRecordDlg dlg;

   // 추가창 모드로 연다.

   dlg.m_nMode = MODE_EDIT;

   dlg.m_sName = (LPCTSTR)(_bstr_t)(_variant_t)(m_pRs->Fields->GetItem("NAME")->Value);

   dlg.m_sInfo = (LPCTSTR)(_bstr_t)(_variant_t)(m_pRs->Fields->GetItem("INFO")->Value);

   dlg.m_sSize = (LPCTSTR)(_bstr_t)(_variant_t)(m_pRs->Fields->GetItem("SIZE")->Value);

   dlg.m_sTreatOnEdit = (LPCTSTR)(_bstr_t)(_variant_t)(m_pRs->Fields->GetItem("TREAT")->Value);

   dlg.m_sFileName = (LPCTSTR)(_bstr_t)(_variant_t)(m_pRs->Fields->GetItem("FNAME")->Value);

   if( dlg.DoModal() == IDOK )

   {

       CString sBin;

       if( dlg.m_bPathSelected )

       {

           if( !CDBLogic::GetBin( dlg.m_sFilePath , &sBin) )

               return;

       }

       try

       {

           m_pRs->Fields->GetItem("NAME")->put_Value( _variant_t(dlg.m_sName) );

           m_pRs->Fields->GetItem("INFO")->put_Value( _variant_t(dlg.m_sInfo) );

           m_pRs->Fields->GetItem("TREAT")->put_Value( _variant_t(CString(dlg.m_sTreat[0])) );

           if( !sBin.IsEmpty() )

           {

               m_pRs->Fields->GetItem("FNAME")->put_Value( _variant_t(dlg.m_sFileName) );

               m_pRs->Fields->GetItem("SIZE")->put_Value( _variant_t(dlg.m_sSize) );          

               m_pRs->Fields->GetItem("BIN")->put_Value( _variant_t(sBin) );

           }

           m_pRs->Update();

           //m_pRs->Requery(adCmdTable);

           m_pRs->UpdateBatch(adAffectCurrent);

           //RefreshBoundData();  

       }

       catch (_com_error &e)

       {

           GenerateError(e.Error(), e.Description());

       }      

       Open( _T("VIRUS"));

   }

}

정리

VC++에서 ADO를 이용해서 데이타베이스 간단한 레코드 폼을 얻고 싶을때 사용할 수 있는 간단한 ADO Data Bind ActiveX 를 사용하는 방법을 알아보았습니다.