Over a million developers have joined DZone.
{{announcement.body}}
{{announcement.title}}

Going Native with SQL Server – Introduction

DZone's Guide to

Going Native with SQL Server – Introduction

· Database Zone
Free Resource

Whether you work in SQL Server Management Studio or Visual Studio, Redgate tools integrate with your existing infrastructure, enabling you to align DevOps for your applications with DevOps for your SQL Server databases. Discover true Database DevOps, brought to you in partnership with Redgate.

This is the first in a series of blog posts I’m planning to do, in preparation for a potential SQLBits session in March 2012.

This article will introduce how, at the most basic level, SQL Server can be communicated with using C++ and Native code, rather than using the .NET Framework.

The code shown below follows through the basic process, defined in the general flowchart for ODBC Applications as seen on MSDN. This was created in VS2010.

Using this process, we connect to a server (a local copy of SQL Server, with AdventureWorks 2008 R2, and does a straightforward query against it to do a Row Count of the Person table.

// The bare basics to query SQL Server, using the Native Client, in C++
//
#include "stdafx.h"
#include <iostream>
using namespace std;

#define _SQLNCLI_ODBC_
#include "sqlncli.h"
#include "sqlext.h"

int _tmain(int argc, _TCHAR* argv[])
{
    // Define Handles
    SQLHANDLE hEnv, hDBCCount, hStmtCount;
    SQLINTEGER iRowCount, iRowCountInd;

    char sConnString[120] = "Driver={SQL Server Native Client 10.0};Server=localhost;Database=AdventureWorks2008R2;Trusted_Connection=yes;";

    // Step 1 - Assigning an Environment Variable
    SQLAllocHandle(SQL_HANDLE_ENV, SQL_NULL_HANDLE, &hEnv);

    // Step 1 - Declaring the use of ODBCv3
    SQLSetEnvAttr(hEnv, SQL_ATTR_ODBC_VERSION, (SQLPOINTER) SQL_OV_ODBC3, 0);

    // Step 1 - Creating a Connection Handle
    SQLAllocHandle(SQL_HANDLE_DBC, hEnv, &hDBCCount);

    // Step 1 - Setting Connection Attributes
    SQLSetConnectAttr(hDBCCount, SQL_ATTR_AUTOCOMMIT, (SQLPOINTER) SQL_AUTOCOMMIT_OFF, SQL_IS_INTEGER);

    // Step 1 - Initiating the connection to SQL Server
    SQLDriverConnect(hDBCCount, NULL, (SQLTCHAR *) sConnString, SQL_NTS, NULL, 0, NULL, SQL_DRIVER_NOPROMPT);

    // Step 2 - Creating a Handle for the Statement
    SQLAllocHandle(SQL_HANDLE_STMT, hDBCCount, &hStmtCount);

    // Step 3 - Connecting to AdventureWorks2008R2
    SQLExecDirect(hStmtCount, (SQLTCHAR *)"USE AdventureWorks2008R2;", SQL_NTS);
    cout << "USE AdventureWorks2008R2;" << endl;

    // Step 3 - Executing Query against Person.Person table
    SQLExecDirect(hStmtCount, (SQLCHAR *)"select count(1) from Person.Person;" , SQL_NTS);
    cout << "select count(1) from Person.Person;" << endl;

    // Step 4a - Assigning a variable to the return column
    SQLBindCol(hStmtCount, 1,SQL_C_ULONG, &iRowCount, 0, &iRowCountInd);

    // Step 4a - Retrieving the data from the return dataset
    SQLFetch(hStmtCount);

    cout << "Rows = " << iRowCount << endl;

    // Step 4a - Remove the Cursor
    SQLCloseCursor(hStmtCount);

    // Step 5 - Closing down and Cleaning up
    SQLDisconnect(hDBCCount);
    SQLFreeHandle(SQL_HANDLE_DBC,hDBCCount);
    SQLFreeHandle(SQL_HANDLE_ENV, hEnv);

    return 0;
}

Over the coming weeks, I’ll be expanding on this to get better performance for more complex processes and going through what each of these sections to.

I hope you find these articles interesting.

It’s easier than you think to extend DevOps practices to SQL Server with Redgate tools. Discover how to introduce true Database DevOps, brought to you in partnership with Redgate

Topics:

Published at DZone with permission of Nick Haslam, DZone MVB. See the original article here.

Opinions expressed by DZone contributors are their own.

THE DZONE NEWSLETTER

Dev Resources & Solutions Straight to Your Inbox

Thanks for subscribing!

Awesome! Check your inbox to verify your email so you can start receiving the latest in tech news and resources.

X

{{ parent.title || parent.header.title}}

{{ parent.tldr }}

{{ parent.urlSource.name }}