Database Programming Summary __.net

Summary of database programming

There are a number of current mainstream databases, including Oracle, MS SQL Server, Sybase, Informix, MySQL, DB2, Interbase/firebird, PostgreSQL, SQLite, sap/db, time STen, MS access, and so on. Database programming is the operation of database creation, reading and writing, and so on. Database programming is divided into database client programming and database server-side programming. Database client programming mainly uses ODBC API, ADO, Ado.net, OCI, OTL and other methods, database server programming mainly uses OLE DB method. Database programming needs to master some methods to access the database, but also pay attention to how to design efficient database, database management and Operation optimization, database statement optimization.

first, access to the database technical methods

Database programming is divided into database client programming and database server-side programming. Database client programming mainly uses ODBC API, ADO, Ado.net, OCI, OTL and other methods, database server programming mainly uses OLE DB method.

1, several is the database access method comparison

ODBC API is a kind of programming method that is suitable for the bottom development of database, the ODBC API provides a lot of operation to the data source, the ODBC API can operate the cursor flexibly, support various options, and in all ODBC related programming, API programming has the highest execution speed.

DAO provides a good object model for database programming. However, all calls to the database, as well as the output data, must pass through the Access/jet database engine, which is a serious bottleneck for the use of database applications.

OLE DB Provides COM interfaces that are more robust and flexible than traditional database interfaces, have strong error handling capabilities, and can communicate with non relational data sources.

The main advantages of ADO are ease of use, speed, low memory costs, and small disk remains.

Ado.net is to read the database data into memory using the concept of dataset, then manipulate the data in memory, and finally write back the dataset data to the source database.

OTL is the abbreviation for Oracle, ODBC and DB2-CLI Template Library, a template library that manipulates relational databases in C + + compilation, where direct Oracle operations are performed primarily through the OCI interface provided by Oracle, Operation DB2 database is done via CLI interface, as for MS Database and some other databases, OTL only provides the way ODBC is operated. Of course, Oracle and DB2 can also be manipulated by OTL indirectly using ODBC. Has the following advantages: Cross-platform, high efficiency, and the C language directly invoke the API is equivalent to the development of high efficiency, at least more simple than ado.net use, more concise, easy to deploy, do not need ADO components, no need for the. NET Framework.

2, VC database programming several methods

VC database programming several methods, including ODBC connection, MFC ODBC connection, DAO connection, OLE DB, OLE DB Templates connection, ADO, Oracle Private Methods (OCI (Oracle call Interface) access, or Acle Object OLE C + + Class Library.

<1.> General Methods
1. ODBC connection
ODBC (Open Database Connectivity) is part of the Msoa and is a standard database interface. It provides a unified interface for access to relational databases and enables consistent access to heterogeneous data sources.

ODBC data access consists of the following parts:  
  <1> handle (Handles): ODBC uses handles to identify ODBC environments, connections, statements, and descriptors.  
  <2> Buffer cache (Buffers):  
  <3> data type (data   types)  
  <4> conformance level (conformance   Levels)  
  General steps for designing clients with ODBC:  
  <1> assign ODBC environment  
  <2> Assign connection handle   br>  <3> Connection Data source  
  <4> Construction and Execute SQL statements  
  <5> get query results  
  <6 > Disconnect Data source  
  <7> release ODBC environment  
    ODBC   API is a kind of programming method suitable for database bottom development, ODBC   AP I provide a large number of operations on the data source, the ODBC   API has the flexibility to manipulate cursors, support various options, and API programming has the highest execution speed in all ODBC-related programming. Therefore, ODBC   API programming belongs to the low-level programming.  

2. MFC ODBC connection
MFC ODBC is an MFC encapsulation of ODBC to simplify calls to the ODBC API to implement an object-oriented database programming interface.
The encapsulation of MFC ODBC mainly developed the CDatabase class and CRecordset class
(1) CDatabase class
The CDatabase class is used by an application to establish a connection to a data source. The CDatabase class contains a M_HDBC variable that represents the connection handle for the data source. If you want to establish an instance of the CDatabase class, call the constructor of the class, call the Open function, initialize the environment variable by calling, and perform a connection to the data source. Closes the data source through the close function.
The CDatabase class provides functions and transaction operations that operate on a database.
(2) CRecordset class
The CRecordset class defines member variables that receive or send data from a database to a database to manipulate the data set.
The member variable m_hstmt of the CRecordset class represents the SQL statement handle that defines the recordset, m_nfields the number of fields in the Recordset, and m_nparams the number of parameters used by the recordset.
The CRecordset Recordset implements a connection to the data source through a pointer to the CDatabase instance, that is, the CRecordset member variable m_pdatabase.
MFC ODBC programming is more suitable for the development of interface database applications, but due to the limited database operation functions provided by the CDatabase class and the CRecordset class, the supported cursor types are limited, which limits efficient database development. At the programming level, it belongs to advanced programming.

application Example:

1. Open the Database

CDatabase database;
Database. OpenEx (_t ("Dsn=zhuxue"), Cdatabase::noodbcdialog);//zhuxue for data source name

2. Associated recordsets

CRecordset Recset (&database);

3. Query records

CString ssql1= "";
SSQL1 = "SELECT * FROM TableName";
Recset. Open (CRecordset::forwardOnly, SSQL1, crecordset::readonly);

int ti=0;
CDBVariant Var;//var can be converted to other types of values

while (!recset. IsEOF ())
{
Reading Excel Internal values
Recset. GetFieldValue ("id", VAR);
Jiangxiang[ti].id=var.m_ival;
Recset. GetFieldValue ("name", Jiangxiang[ti].name);
ti++;
Recset. MoveNext ();
}

Recset. Close ();//Closing recordsets

4. Execute SQL statement

CString ssql= "";
ssql+= "Delete * from department audit";/clear Table
Database. ExecuteSQL (sSQL);

sSQL can also be insert, UPDATE, and other statements

5. Read field names

sSQL = "SELECT * from Sheet1"; The file read has a Sheet1 table definition, or a table generated for this program.

Executing query statements
Recset. Open (CRecordset::forwardOnly, sSQL, crecordset::readonly);
int Excelcolcount=recset. Getodbcfieldcount ()//Number of columns
CString excelfield[30];
Gets the total number of fields in the field collection of a recordset
for (i=0;i<excelcolcount;i++)
{
CODBCFieldInfo FieldInfo;
Recset. GetODBCFieldInfo (I,fieldinfo);
Excelfield[i].name =fieldinfo.m_strname;//Field Name

}

6. Open Excel File

CString sdriver = "MICROSOFT EXCEL DRIVER (*. XLS) "; Excel installation Driver
CString Ssql,sexcelfile; Sexcelfile file path to Excel

TRY
{
Creating a string for access
Ssql.format ("driver={%s};D sn="; Firstrowhasnames=1; Readonly=false; create_db=/"%s/";D bq=%s ", Sdriver, Sexcelfile, sexcelfile);

Create a database (both Excel table files)
if (database. OpenEx (Ssql,cdatabase::noodbcdialog))
{

You can use Excel as a database operation

}

}
catch (E)
{
TRACE1 ("Excel driver not installed:%s", sdriver);
AfxMessageBox ("Read failed, please check whether the data area is defined Sheet1");
}

3. DAO connection
DAO (Data Access Object) is a COM automation interface for a group of Microsoft Access/jet database engines. DAO communicates directly with the Access/jet database. DAO can also communicate with other databases through the Jet database engine. DAO also encapsulates the structure unit of an Access database, which allows you to modify the structure of an Access database directly without using the SQL data Definition Language (DDL).
The DAO's architecture is as follows:

DAO encapsulated classes:
(1) CDaoWorkspace: Encapsulation of the DAO workspace (Database Processing transaction manager)
(2) CDaoDatabase: Encapsulation of DAO database objects, responsible for database connections.
(3) CDaoRecordset: Encapsulates a DAO recordset object, representing a selected set of records.
(4) CDaoTableDef: Encapsulates a table-defined object that represents a basic table or additional table definition.
(5) CDaoQueryDef: Encapsulates the query object, including the definition of all queries.
(6) Cdaoexception:dao the class used to receive database operation exceptions.
(7) CDaoFieldExchange
DAO provides a good object model for database programming. However, all calls to the database, as well as the output data, must pass through the Access/jet database engine, which is a serious bottleneck for the use of database applications.
DAO is a high-level database interface relative to ODBC.

4. OLE DB connection
OLE DB extends ODBC in two ways: one provides the OLE interface for database programming, COM, and the other provides an interface that can be used for relational and non relational data sources.
OLE DB Provides COM interfaces that are more robust and flexible than traditional database interfaces, have strong error handling capabilities, and can communicate with non relational data sources.
Like the ODBC API, OLE DB is also the underlying database programming interface, and OLE DB combines ODBC's operational capabilities for relational databases and extends it to access a non relational database.
OLE DB accesses the database in the following principles:
OLE DB Program Structure:
OLE DB is made up of customers (Consumer) and servers (Provider). A customer is an application that uses data that accesses and controls data provider data through the OLE DB interface. An OLE DB server is a software component that provides an OLE DB interface. Depending on what is provided, it can be divided into data providers (Provider) and service providers (services Provider).
The schematic diagram of the program structure is as follows:
<1> Data Provider
The data provider has its own data and renders the data in tabular form for use by the user.
<2> services Provider
A service provider is a combination of data providers and consumers. It is the middleware in the OLE DB architecture, which is the consumer of the OLE DB data source and the provider of the data-use program
<3> Data Use procedures
Data use programs to use and control data stored in a data provider.
General steps for OLE DB development programs:
<1> Initializing COM environment
<2> Connection Data source
<3> Open Dialog
<4> Execution Command
<5> processing Results
<6> Erase Objects

application Example:

Writing database applications using OLE DB
1 overview
The presence of OLE DB provides a unified way for users to access all different kinds of data sources. OLE DB can be converted in a different data source. With Ole DB, a client developer needs to focus on a few details when making data access without having to understand the access protocols of a large number of different databases.
OLE DB is an ActiveX interface that accesses data through a COM interface. This OLE DB interface is quite generic enough to provide a uniform means of accessing data, regardless of the method used to store the data. At the same time, OLE DB allows developers to continue leveraging the benefits of the underlying database technology without having to move the data out to take advantage of these benefits.
2 using ATL with OLE DB data use programs
Designing a database application with objects and interfaces that use OLE DB directly requires writing a large amount of code. To simplify programming, Visual C + + provides an ATL template for designing OLE DB data applications and data providers.
Using an ATL template makes it easy to combine OLE DB with MFC to simplify complex programming such as database parameter queries. MFC provides database classes that make OLE DB programming more object-oriented. The ATL templates that Viual C + + provides for OLE DB can be divided into templates for data providers and templates for data-use programs.
Creating a data application using an ATL template typically has the following steps:
1), create application framework
2), join the ATL generated template class
3), the use of the resulting data access objects in the application

3 Use of OLE DB data using the program without ATL
Using ATL template to produce data use program is simpler, but its applicability is not wide and can not adapt to the change of database dynamically. Here we introduce the use of MFC OLE DB classes directly to generate data usage.
Use of templates
The OLE DB data consumer templates are made up of templates, including the following templates, which are described below for some common classes.
1), Session class
CDataSource class
The CDataSource class corresponds to the data source object of OLE DB. This class represents the connection between the OLE DB data provider and the data source. The session object can be generated only after the connection to the data source has been established, and the Open is invoked to open the connection to the data source.
CSession class
The object created by CSession represents a separate session of database access. A data source object generated with the CDataSource class can create one or more sessions, and a Session object on the data source object needs to be invoked to open the function open (). Also, session objects can be used to create transaction operations.
CEnumeratorAccessor class
The CEnumeratorAccessor class is an accessor used to access information about the available data providers in the rowset generated by the enumerator query, providing the currently available data provider and the visible accessors.
2), accessor class
Cacessor class
The CAccessor class represents the type of the accessor. This class can be used when the user knows the type and structure of the database. It supports multiple accessors for a rowset, and the buffer that holds the data is assigned by the user.
CDynamicAccessor class
The CDynamicAccessor class is used to dynamically create accessors while the program is running. When the system is running, information about the columns can be obtained dynamically from the rowset, and accessors can be created dynamically based on this information.
CManualAccessor class
CManualAccessor class to bind a column to a variable when the program is run or to bundle arguments with variables.
3), Row set class
CRowset class
The CRowset class encapsulates rowset objects and corresponding interfaces, and provides methods for querying, setting data, and so on. You can use functions such as Move () to record movement, read data with the GetData () function, and update the data with insert (), Delete (), SetData ().
CBulkRowset class
The CBulkRowset class is used to fetch multiple row handles in one call or to manipulate multiple rows.
CArrayRowset class
The CArrayRowset class provides data access using an array subscript.
4), Command class
CTable class
The CTable class is used for simple access to the database, with the name of the data source to obtain the rowset, thus obtaining the data.
CCommand class
The CCommand class is used to support a command's data source. You can use the open () function to execute SQL commands, or you can prepare () functions to prepare the commands first, and you can increase the flexibility and robustness of the program for the data source that supports the command.
In the StdAfx.h header file, add the following code.
#include <atlbase.h>
extern CComModule _module;
#include <atlcom.h>
#include <atldbcli.h>
#include <atldbsch.h>//If you are using schema templates
In the Stdafx.cpp file, add the following code.
#include <atlimpl.cpp>
CComModule _module;
Determines what type of accessor and rowset to use.
Get Data
After you open the data source, the session, the rowset object, you can get the data. The data type you get depends on the access program you are using, and you may need to bind the columns. Follow these steps.
1. Open the rowset object with the correct command.
2. If you use CManualAccessor, bind to the corresponding column before using it. To bind a column, you can use the function GetColumnInfo, as follows:
Get the column information
ULONG ulcolumns = 0;
dbcolumninfo* pcolumninfo = NULL;
Lpolestr pstrings = NULL;
if (Rs. GetColumnInfo (&ulcolumns, &pcolumninfo, &pstrings)!= S_OK)
Afxthrowoledbexception (Rs.m_prowset, iid_icolumnsinfo);
struct mybind* pbind = new Mybind[ulcolumns];
Rs. CreateAccessor (Ulcolumns, &pbind[0], sizeof (mybind) *ulcolumns);
for (ULONG l=0; l<ulcolumns; l++)
Rs. AddBindEntry (l+1, Dbtype_str, sizeof (TCHAR) *40, &pbind[l].szvalue, NULL, &pbind[l].dwstatus);
Rs. Bind ();
3, use while loop to fetch data. In the loop, call MoveNext to test whether the cursor's return value is S_OK, as follows:
while (Rs. MoveNext () = = S_OK)
{
ADD code to fetch data here
If you are is not using a auto accessor, call Rs. GetData ()
}
4. Within the while loop, data can be obtained through different access programs.
1 If you are using the CAccessor class, you can access them directly by using their data members. as follows:
2 If you are using a CDynamicAccessor or CDynamicParameterAccessor class, you can get the data by GetValue or GetColumn function. You can use GetType to get the data type you are using. as follows:
while (Rs. MoveNext () = = S_OK)
{
Use the dynamic accessor functions to retrieve your
Data
ULONG ulcolumns = Rs. getColumnCount ();
for (ULONG i=0; i<ulcolumns; i++)
{
Rs. GetValue (i);
}
}
3 If you are using CManualAccessor, you can specify your own data members and bind them. can be accessed directly. As shown below:
while (Rs. MoveNext () = = S_OK)
{
Use the ' data members ' specified in the calls to
AddBindEntry.
wsprintf ("%s", Szfoo);
}
Determining the data type of a rowset
Determines the data type at run time, using a dynamic or manual access program. If you are using a manual access program, you can use the GetColumnInfo function to get column information for the rowset. Data types can be obtained from here.
4 Summary
Since there are a variety of data sources, the only way to manage this data is through some of the same mechanisms, such as OLE DB. The Advanced OLE DB architecture is divided into two parts: the customer and the provider. The customer uses data generated by the provider.
Like most other COM-based architectures, OLE DB developers need to implement many interfaces, most of which are template files.
When you build a client object, you can create a simple customer by pointing to a data source through the ATL Object Wizard. The ATL Object Wizard examines the data source and creates a client proxy for the database. From there, you can use the standard browsing functions through the OLE DB client templates.
When you build a provider, the wizard provides a good start by simply generating a simple provider to enumerate the files in a directory. The provider template then contains the full complement of OLE DB support. With this support, users can create OLE DB providers to implement rowset positioning policies, read and write data, and create bookmarks.

Application Case:

Read and write SQL Server using OLE DB in Visual C + +

OLE db is always considered to be the most efficient but the hardest way to operate a database. But with my recent experience with OLE DB, OLE DB is a high efficiency, but it's not difficult at all. I am afraid the main reason is that there is too little information available in Chinese, I wrote this article to help future contacts with OLE DB. This article contains the following:

1. OLE db Write database;

2. OLE DB read database;

3. OLE DB handles binary data (text, ntext, image, and so on).

First take a look at the SQL Server to write the code, a certain VC based readers should be able to read it smoothly. OLE DB writes a database, that's that simple.

Note:

1. Template classes used in the following code eautoreleaseptr<t> and ccomptr<t> in ATL