Win32 API programming with C - Working with the database

The Microsoft Open Database Connectivity (ODBC) interface is a C programming language interface that makes it possible for applications to access data from a variety of database management systems (DBMSs). ODBC is a low-level, high-performance interface that is designed specifically for relational data stores.

In the previous article, we saved the text from our Edit control to a .txt file on disk, in this article we will see how we can save that text to the database.

Prerequisites

Here's what we will need for this:

• SQL Server or SQL Server Express installed and accessible.

• ODBC drivers for SQL Server installed.

• An ODBC Data Source Name (DSN) configured for the database we intend to connect to. Let's assume the DSN is named MySqlServerDsn.

• The Win32 Development Tools and SDK installed.

How to add a new ODBC data source

We will add a new ODBC data source named MySqlServerDsn.

Open ODBC data source administrator and click Add.

Choose SQL Server and click Finish.

Enter the Name and SQL Server instance you want to connect to.

Choose Windows or SQL Server authentication and click Next.

Choose default database and click Next and Finish. Click Test Data Source to make sure everything is ok.

Once we have our DSN we can start writing code to connect to our database. For the purpose of this article I have added a new database called MyDatabase and a new table called Articles which look like this:

Include Headers and Link Libraries

We need to include necessary headers and link against required libraries. Add this at the top of your source file.

#include <sql.h>
#include <sqlext.h>
#pragma comment (lib, "odbc32.lib")

Define global variables

SQLHENV env;              // environment handle
SQLHDBC dbc;              // connection handle
SQLHSTMT stmt;            // statement handle
SQLRETURN ret;              // ODBC API return status

Write save to database method

BOOL SaveToDatabase(HWND hEdit, HWND hwnd)
{
    // Allocate an environment handle
    SQLAllocHandle(SQL_HANDLE_ENV, SQL_NULL_HANDLE, &env);
    // Set the ODBC version environment attribute
    SQLSetEnvAttr(env, SQL_ATTR_ODBC_VERSION, (void*)SQL_OV_ODBC3, 0);
    // Allocate a connection handle
    SQLAllocHandle(SQL_HANDLE_DBC, env, &dbc);
    // Connect to the DSN
    SQLDriverConnect(dbc, NULL, (SQLCHAR*)"DSN=MySqlServerDsn;", SQL_NTS, NULL, 0, NULL, SQL_DRIVER_COMPLETE);
    // Allocate a statement handle
    SQLAllocHandle(SQL_HANDLE_STMT, dbc, &stmt);

    // Preparing an INSERT SQL statement
    char* sqlInsert = "INSERT INTO dbo.Articles(Id, Text) VALUES (?,?);";
    SQLPrepare(stmt, (SQLCHAR*)sqlInsert, SQL_NTS);

    // prepare parameters
    SQLINTEGER id = 1;
    SQLCHAR articleText[256];   // size as needed
    strcpy(articleText, ReadTextFromEdit(hEdit));

    // Bind parameters
    SQLBindParameter(stmt, 1, SQL_PARAM_INPUT, SQL_C_SLONG, SQL_INTEGER, 0, 0, &id, 0, NULL);
    SQLBindParameter(stmt, 2, SQL_PARAM_INPUT, SQL_C_CHAR, SQL_VARCHAR, sizeof(articleText), 0, articleText, 0, NULL);

    // Execute the statement
    ret = SQLExecute(stmt);
    if (ret == SQL_SUCCESS || ret == SQL_SUCCESS_WITH_INFO) {
        MessageBox(hwnd, "Record inserted successfully.", "Success", MB_OK | MB_ICONINFORMATION);
    }
    else {
        MessageBox(hwnd, "Error executing sql statement.", "Error", MB_OK | MB_ICONERROR);
    }

    // Clean up
    SQLFreeHandle(SQL_HANDLE_STMT, stmt);
    SQLDisconnect(dbc);
    SQLFreeHandle(SQL_HANDLE_DBC, dbc);
    SQLFreeHandle(SQL_HANDLE_ENV, env);
}

Modify our Window Procedure code so when we click the button we call this method instead of opening the file save dialog.

LRESULT CALLBACK WndProc(HWND hwnd, UINT msg, WPARAM wParam, LPARAM lParam)
{
    switch (msg)
    {
        // ...
        case WM_COMMAND:
            switch (LOWORD(wParam)) {
                case ID_MYBUTTON:
                    HWND hEdit = GetDlgItem(hwnd, IDC_MY_EDIT);
                    SaveToDatabase(hEdit, hwnd);
                    break;
            break;
        // ...
        default:
            return DefWindowProc(hwnd, msg, wParam, lParam);
        }
    return 0;
}