MFC connects to a SQL Server database

C + + Connect SQL database in step

When we are doing the C + + connection to the SQL database, we should first make the appropriate system configuration, then initialize the C + + SQL connection, then the operation of the data connection, and then apply the SQL execution language to do the operation.

C + + Connect SQL Database First step system configuration

1. Set up SQL Server to log on as a method, and the SA user in system security to set the login function as "enabled" and must have a password.

2. Requires data source configuration in ODBC, data source select \ "SQL Server", log on using "SQL Server authentication using input user login ID and password", and fill in the login name (SA) and password, note that the password cannot be empty, This means that your SA user must have a password. Otherwise, the security policy of the system itself cannot be passed. The test is completed by the configuration.

C + + Connect SQL database Second step c + + SQL connection initialization

1. Introduce ADO into the stdafx.h header file in your built-in C + + project

The specific code is as follows

#import "C:\Program Files\Common Files\system\ado\msado15.dll" no_namespace rename ("EOF", "adoeof") Rename ("BOF", " Adobof ")

2. After defining the _CONNECTIONPTR variable, call the open method of the connection object to establish a connection to the server.

The data type _connectionptr is actually a concrete instance class obtained by the class template _com_ptr_t. The _connectionptr class encapsulates the IDispatch interface pointers for connection objects and some of the necessary operations. The connection object can be manipulated with this pointer.

For example, connect the SQL Server database with the following code:

Connect to MS SQL Server
Initializing pointers
_connectionptr pmyconnect = NULL;
HRESULT hr = pmyconnect.createinstance (__uuidof (Connection));
if (FAILED (HR))
Return
Initialize link parameters
_bstr_t strconnect = "provider=sqloledb;
SERVER=CQH-PC; Server or Local computer name
Database=mytest; Database refers to databases in your system
Uid=sa; User name
PWD=PSW "; Password

Performing a connection
Try
{
The Open method connection string must be four BSTR or _bstr_t type
Pmyconnect->open (StrConnect, "", "", NULL);
}
catch (_com_error &e)
{
MessageBox (E.description (), "Warning", mb_ok| Mb_iconinformation);
}//A link error occurred

C + + Connect SQL database Third step simple data connection

Define the _RECORDSETPTR variable, call the open of its Recordset object to open a dataset
The initialization process is an instance of the following
_RecordsetPtr precordset;
if (FAILED (Precordset.createinstance (__uuidof (Recordset))))
{
Return
}

Perform actions
Try
{
Precordset->open (_variant_t ("userinfo"),
_variant_t ((idispatch*) pmyconnect), adOpenKeyset, adLockOptimistic, adCmdTable);
}
catch (_com_error &e)
{
MessageBox ("Cannot open UserInfo table \", "System hints", mb_ok| Mb_iconinformation);
}

C + + Connect SQL database Fourth step Execute SQL statement

Here is the key, I think as long as you understand the SQL statement then everything will be more convenient than using the above method simple, more efficient point.

First of all

M_pconnection.createinstance (_uuidof (Connection));
Initialize the connection pointer
M_precordset.createinstance (__uuidof (Recordset));
Initialize the recordset pointer
CString strsql= "SELECT * from Tb_goods";//SQL statement executed specifically

M_precordset=m_pconnection->execute (_bstr_t (strSQL), NULL, adCmdText);//import query data into M_precordset data container

Now that your SQL statement has been executed, the data within M_precordset is the result of your execution.

Get Records:

while (!m_precordset->adoeof)//traverse and read the record of the name column and output
{
CString temp = (TCHAR *) (_bstr_t) M_precordset->getfields ()->getitem ("name")->value; AfxMessageBox (temp);
Precordset->movenext ();
}

Insert Record:

Remember to initialize the pointer and perform the following actions
CString strSQL;
strSQL. Format ("INSERT into Tb_goods (no,name, price) values ('%d ', '%s ',%d)", m_intno,m_strname,m_intprice);
M_precordset=m_pconnection->execute (_bstr_t (strSQL), null,adcmdtext);

To modify a record:

CString strSQL;
strSQL. Format ("Update tb_goods set name= '%s ', price=%d where no=%d", M_strname,m_intprice,m_intno);
M_precordset=m_pconnection->execute (_bstr_t (strSQL), null,adcmdtext);

To delete a record:

CString strSQL;
strSQL. Format ("Delete from Tb_goodswhere no= '%d '", m_intno); M_precordset=m_pconnection->execute (_bstr_t (strSQL), null,adcmdtext);

*******************************************************************************************

Basic process of ADO database development
(1) Initialize the COM library and introduce the ADO library definition file
(2) Connect database with Connection object
(3) Make use of established connection, execute SQL command through connection, command object, or use Recordset object to obtain result recordset for query and processing.
(4) Close the connection release object after the use is complete.

Preparatory work:
For everyone to test the examples provided in this article, we use the Access database, or you can find this test.mdb directly in the sample code we provide.
Below we will describe the above steps in detail and give the relevant code.
Initialization of the "1" COM library
We can use AfxOleInit () to initialize the COM library, which is usually done in the overloaded function of CWinApp::InitInstance (), see the following code:

BOOL cadotest1app::initinstance ()
{
AfxOleInit ();
......

"2" introduces ADO type library with #import instruction
We add the following statement to the stdafx.h: (stdafx.h where can I find this file? You can find it in the header files of FileView)

#import "C:\Program Files\Common Files\system\ado\msado15.dll" No_namespace rename ("EOF", "adoeof")
How does this statement work? Its final function is similar to our familiar # include, when compiled, the system will generate a Msado15.tlh,ado15.tli two C + + header files to define the ADO library.

A few notes:
(1) Msado15.dll in your environment is not necessarily in this directory, please modify the actual situation
(2) The following warning will appear at compile time, which Microsoft has explained on MSDN and advises us to ignore this warning.
MSADO15.TLH (405): Warning c4146:unary minus operator applied to unsigned type, result still unsigned

"3" Creates a connection object and connects to the database
First we need to add a pointer to the connection object:
_connectionptr m_pconnection;
The following code shows how to create an Connection object instance and how to connect to the database and make exception snaps.

BOOL Cadotest1dlg::oninitdialog ()
{
CDialog::OnInitDialog ();
HRESULT HR;
Try
{
hr = m_pconnection.createinstance ("ADODB. Connection ")/////Create Connection Object
if (SUCCEEDED (HR))
{
hr = m_pconnection->open (" provider= Microsoft.jet.oledb.4.0;data Source=test.mdb "," "," ", admodeunknown);///Connect database
/// The above sentence in the concatenated string of Provider is for the ACCESS2000 environment, for ACCESS97, need to change: provider=microsoft.jet.oledb.3.51;
}
}
catch (_com_error E)///catch exception
{
CString errormessage;
ErrorMessage. Format ("Connection database failed!\r\n error message:%s", E.errormessage ());
AfxMessageBox (errormessage);///Display error message
}

In this code we connect to the database through the open method of the Connection object, the following is the prototype of the method
HRESULT Connection15::open (_bstr_t ConnectionString, _bstr_t UserID, _bstr_t Password, long Options)
ConnectionString is the connection string, UserID is the user name, password is the login password, options is the connection option, to specify the connection object to update the data permissions,
The options can be several constants:
adModeUnknown: Default. The current license is not set
adModeRead: Read-only
adModeWrite: Write only
adModeReadWrite: can read and write
adModeShareDenyRead: Block other connection objects from opening connections with Read permissions
adModeShareDenyWrite: Block other connection objects from opening a connection with write permission
Admodeshareexclusive: Block other connection objects from opening connections with read and write permissions
adModeShareDenyNone: Prevents other connection objects from opening the connection with any permissions

We give some common connection methods for your reference:
(1) connection to the ACCESS2000 database through the Jet database engine

m_pconnection->open ("Provider=Microsoft.Jet.OLEDB.4.0;Data Source=c:\\test.mdb", "" "," ", AdModeUnknown);

(2) Connect to any ODBC-enabled database through a DSN data source:

m_pconnection->open ("Data source=adotest; Uid=sa; pwd=; "," "," ", adModeUnknown);

(3) Do not connect to the SQL Server database through DSN:
m_pconnection->open ("Driver={sql Server}; Server=127.0.0.1;database=vckbase; Uid=sa; pwd=139 "," "," ", adModeUnknown);

Where server is the name of the SQL Server, database is the name of the library

Connection object There are many methods besides the open method, we first introduce two useful properties of connection object ConnectionTimeout and state
The connectiontimeout is used to set the timeout for the connection and needs to be called before Open, for example:
m_pconnection->connectiontimeout = 5;///Set timeout time is 5 seconds
m_pconnection->open ("Data source=adotest;", "", "", adModeUnknown);

The State property indicates the status of the current connection object, 0 is off, 1 means it is already open, and we can do this by reading this property, for example:

if (m_pconnection->state)
m_pconnection->close (); If the connection is already open, close it

I want to put the records read from the database in a text box!

Then an edit box is placed on the interface.

I added a variable to the class C***dlg
Public
Cbaseeditbox m_list

Next I connected the database in a button OnOK event and wanted to put the resulting records in M_list.

Read the fields in the library and add them to the list box
while (!m_precordset->adoeof)
{
var = m_precordset->getcollect ("UserName");
if (var.vt! = vt_null)
StrName = (LPCSTR) _bstr_t (VAR);
var = m_precordset->getcollect ("Password");
if (var.vt! = vt_null)
Strage = (LPCSTR) _bstr_t (VAR);

M_list. AddString (StrName + "-->" +strage);

m_precordset->movenext ();
}

The default list points to the first item while moving the record pointer and displaying
M_list. SetCurSel (0);

To add a button event:
void Cadodlg::onsearch ()
{
CoInitialize (NULL); Initialize COM
_connectionptr Pconn (__uuidof (Connection));
_RecordsetPtr pRst (__uuidof (Recordset));
CString strsql= "Select *from student";
_bstr_t bstrsrc (strSQL);
Pconn->connectionstring= "Provider=msdasql;driver={sql Server}; Server=127.0.0.1;database=test; Uid=sa; Pwd=123 ";
Pconn->open ("", "", "",-1); /*-1 can be replaced by adconnectunspecified, which indicates a synchronous connection. Adconnectasync, which is asynchronous. If you are using the second connection Word then apply Pconn->open ("", "" "," 642145 ",-1); */
Prst=pconn->execute (Bstrsrc,null,adcmdtext);

while (!prst->rseof)
{
M_result. AddString ((_bstr_t) prst->getcollect ("SName")); /*m_result is one of my associated list space variables */
Prst->movenext ();
}
Prst->close ();
Pconn->close ();
Prst.release ();
Pconn.release ();

CoUninitialize ();
}

The above is the use of the connection object to execute the SQL statement, we can also use the command object to implement,
First add the command smart pointer:
_commandptr Pcmd (__uuidof (Command));
To set up an active connection:
Pcmd->put_activeconnection (_variant_t ((idispatch*) pconn));
To set the command text:
pcmd->commandtext= "SELECT * from Student";
Returns the Recordset:
Prst=pcmd->execute (Null,null,adcmdtext);

MFC connects to a SQL Server database