Category: C++


Presenting in Spring… #sqlserver #sqlclr

Filed under: C#, C++, Community, SQL Server — Leave a comment
02/04/2012

Having been sitting on the fence for a while, I’m finally leaping off, and presenting at some community events. Following a false start with SQLBits (I submitted, but wasn’t voted in, and given the number of attendees I’m a little relieved about that!), I’ll be presenting at the following events over the next couple of months.

Hope to see you there!

24th April (Tues) – SQL Server in the Evening (6:30-6:50) – First Timers Slot (http://sqlserverfaq.com/events/392/Sessions-including-SQL-Server-Parallel-Data-Warehouse-at-the-sixth-SQL-Server-community-event-615pm-April-24th-Reading-Berkshire.aspx)

I’ll talk about using the CLR within SQL Server, why and when it should be used and then how.

25th April (Weds) – DevEvening (http://www.devevening.co.uk/)

26th May (Sat)– DDD Southwest (http://dddsouthwest.com/)
NOTE: This session isn’t confirmed yet, and is still reliant on being voted in. You can vote by going to the DDD Southwest site, linked above! )

Both DevEvening and DDD Southwest will be the same session, summarised below:

Going Native with SQL Server 2012 and C++

I’ll be going through the delights of creating a module to interact with SQL Server 2012, a function in T-SQL (briefly), then using C# to create a SQL CLR module, and then looking into the performance gains by making a C++ application querying the SQL Native Client (ODBC). All three sections will do the same job, and we’ll cover the advantages and disadvantages of each.

We’ll cover the following:

  • T-SQL, SQL CLR (C#)
  • SQL Server Native Client
  • Advantages and Disadvantages
  • Performance Opportunities
  • How to use it to connect to SQL Server from C++
  • How to query a database
  • Comparison between T-SQL, SQL CLR & C++ solutions

Slides and follow-up articles will be coming soon.

Tags: , ,
Comment

Going Native with SQL Server – Introduction

Filed under: C++, SQL Server — 1 Comment
19/12/2011

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.

 1: // The bare basics to query SQL Server, using the Native Client, in C++
 2: //
 3: #include "stdafx.h"
 4: #include <iostream>
 5: using namespace std;
 6:
 7: #define _SQLNCLI_ODBC_
 8: #include "sqlncli.h"
 9: #include "sqlext.h"
 10:
 11: int _tmain(int argc, _TCHAR* argv[])
 12: {
 13:     // Define Handles
 14:     SQLHANDLE hEnv, hDBCCount, hStmtCount;
 15:     SQLINTEGER iRowCount, iRowCountInd;
 16:
 17:     char sConnString[120] = "Driver={SQL Server Native Client 10.0};Server=localhost;Database=AdventureWorks2008R2;Trusted_Connection=yes;";
 18:
 19:     // Step 1 - Assigning an Environment Variable
 20:     SQLAllocHandle(SQL_HANDLE_ENV, SQL_NULL_HANDLE, &hEnv);
 21:
 22:     // Step 1 - Declaring the use of ODBCv3
 23:     SQLSetEnvAttr(hEnv, SQL_ATTR_ODBC_VERSION, (SQLPOINTER) SQL_OV_ODBC3, 0);
 24:
 25:     // Step 1 - Creating a Connection Handle
 26:     SQLAllocHandle(SQL_HANDLE_DBC, hEnv, &hDBCCount);
 27:
 28:     // Step 1 - Setting Connection Attributes
 29:     SQLSetConnectAttr(hDBCCount, SQL_ATTR_AUTOCOMMIT, (SQLPOINTER) SQL_AUTOCOMMIT_OFF, SQL_IS_INTEGER);
 30:
 31:     // Step 1 - Initiating the connection to SQL Server
 32:     SQLDriverConnect(hDBCCount, NULL, (SQLTCHAR *) sConnString, SQL_NTS, NULL, 0, NULL, SQL_DRIVER_NOPROMPT);
 33:
 34:     // Step 2 - Creating a Handle for the Statement
 35:     SQLAllocHandle(SQL_HANDLE_STMT, hDBCCount, &hStmtCount);
 36:
 37:     // Step 3 - Connecting to AdventureWorks2008R2
 38:     SQLExecDirect(hStmtCount, (SQLTCHAR *)"USE AdventureWorks2008R2;", SQL_NTS);
 39:     cout << "USE AdventureWorks2008R2;" << endl;
 40:
 41:     // Step 3 - Executing Query against Person.Person table
 42:     SQLExecDirect(hStmtCount, (SQLCHAR *)"select count(1) from Person.Person;" , SQL_NTS);
 43:     cout << "select count(1) from Person.Person;" << endl;
 44:
 45:     // Step 4a - Assigning a variable to the return column
 46:     SQLBindCol(hStmtCount, 1,SQL_C_ULONG, &iRowCount, 0, &iRowCountInd);
 47:
 48:     // Step 4a - Retrieving the data from the return dataset
 49:     SQLFetch(hStmtCount);
 50:
 51:     cout << "Rows = " << iRowCount << endl;
 52:
 53:     // Step 4a - Remove the Cursor
 54:     SQLCloseCursor(hStmtCount);
 55:
 56:     // Step 5 - Closing down and Cleaning up
 57:     SQLDisconnect(hDBCCount);
 58:     SQLFreeHandle(SQL_HANDLE_DBC,hDBCCount);
 59:     SQLFreeHandle(SQL_HANDLE_ENV, hEnv);
 60:
 61:     return 0;
 62: }

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.

Tags: , ,
Comment