<#include stdafx.h>
<#include stdlib.h>
<#include stdio.h>
<#include string.h>
<#include ctype.h>
<#include tchar.h>
<#include windows.h>
<#include srv.h>
<#include time.h>
<#include vcclr.h>

#define UC (char *)

#define XP_NOERROR              0
#define XP_ERROR                1
#define MAXCOLNAME				25
#define MAXNAME					25
#define MAXTEXT					255

#ifdef __cplusplus
extern "C" {
#endif

RETCODE __declspec(dllexport) xp_XMLTemplate_WriteFile(SRV_PROC *srvproc);

#ifdef __cplusplus
}
#endif

// Macros -- return codes
#define XP_NOERROR      0
#define XP_ERROR        1

#define MAX_SERVER_ERROR 20000
#define XP_PARAM_ERROR MAX_SERVER_ERROR+1
#define XP_FATAL_ERROR MAX_SERVER_ERROR+2
#define SRV_SEVERITY_FATAL 16

void printError (SRV_PROC *srvproc, CHAR* szErrorMsg, int SRV_ERROR_NUMBER, int SRV_SEVERITY);
void printUsage (SRV_PROC *srvproc);
char* convertStringToChar (System::String* sStringToconvert);

// It is highly recommended that all Microsoft® SQL Server (7.0 
// and greater) extended stored procedure DLLs implement and export 
// __GetXpVersion. For more information see SQL Server 
// Books Online
ULONG __declspec(dllexport) __GetXpVersion()

{
    return ODS_VERSION;
}

using namespace System;
using namespace System::Data;
using namespace System::Data::OleDb;
using namespace System::Xml;
using namespace Microsoft::Data::SqlXml;

typedef System::String __gc* GCString;

__gc class StringData
{
public:
	GCString sServer;
	GCString sDatabase;
	GCString sXMLFilename;
	GCString sXMLInput;
	GCString sConnectionString;
	GCString sTemp1;
	GCString sTemp2;
	GCString sTemp3;
	GCString sTemp4;
	GCString sXMLTemplateText;
	GCString sSQL;
	GCString sParam1;
	GCString sParam2;
	GCString sParam3;
	GCString sParam4;
	GCString sParam5;
	GCString sParam6;
	GCString sParam7;
	GCString sParam8;
	GCString sParam9;
	GCString sParam10;
	GCString sErrorMessage;
};

class CXTWFile
{
public:
	CXTWFile() {};
	~CXTWFile() {};

	SRV_PROC*	srvproc2;
    PBYTE*      ppbData;
    int         nParams;
	int			nParam;
	int			i;
//	Array* szParam = Array::CreateInstance( __typeof(TCHAR), 15, 250 );

	TCHAR		szParam[15][250];

	bool bParamIsTemplateID;

	SRVRETCODE CXTWFileMain()
	{
		SRVRETCODE  rc = XP_NOERROR;
		// Initialize managed class which store our string variables.
		// Can't declare them here, as they are managed data types and
		// this is an unmanaged class
		StringData* sd = new StringData;

		try 
		{
			// Count up the number of input parameters
			nParams = srv_rpcparams(srvproc2);
			if (nParams == -1)
			{
				printUsage(srvproc2);
				return (XP_ERROR);
			}

			// Build an array of data pointers for the input parameters.
			ppbData = (PBYTE*) malloc(nParams * sizeof(PBYTE));
			if (ppbData == NULL)
			{
				printError(srvproc2, 
							"Memory allocation error.", 
							XP_FATAL_ERROR, 
							SRV_SEVERITY_FATAL);
				return (XP_ERROR);
			}
			memset(ppbData, (int)NULL, nParams * sizeof(PBYTE));

			// Assign parameters to TCHAR
			// Terminate parameter strings with NULL.
			i=0;
			for (i=1;i<=14;i++)
			{
				memcpy(szParam[i], srv_paramdata(srvproc2, i), srv_paramlen(srvproc2, i));
				szParam[i][srv_paramlen(srvproc2, i)] = '\0';
			}

			// Convert the TCHAR data types to String data types
			sd->sXMLFilename = szParam[1];
			sd->sXMLInput = szParam[2];
			sd->sServer = szParam[3];
			sd->sDatabase = szParam[4];
			sd->sParam1 = szParam[5];
			sd->sParam2 = szParam[6];
			sd->sParam3 = szParam[7];
			sd->sParam4 = szParam[8];
			sd->sParam5 = szParam[9];
			sd->sParam6 = szParam[10];
			sd->sParam7 = szParam[11];
			sd->sParam8 = szParam[12];
			sd->sParam9 = szParam[13];
			sd->sParam10 = szParam[14];

			// Check if the parameter passed was the ID of the template, or a SQL query
			// If a sql query, then execute the query and write to an xml file.
			// Ensure you use the FOR XML clause in your SQL Query
			// If TRUE, we are using an XML Template which needs to be retrieved from
			// the database
			bParamIsTemplateID = Char::IsNumber(sd->sXMLInput, 0);

		// start debugging code to print data in the SQL Query analyser results window
			// Conversion to wchar_t* :
			//const wchar_t __pin* p = PtrToStringChars(sd->sXMLFilename);

			// Conversion to char* :
			// Convert wchar_t* to char* using a conversion functionssuch as: 
			//char* ch = (char *)malloc((sd->sXMLFilename -> Length + 1) * 2);
			//wcstombs(ch, p, (sd->sXMLFilename -> Length + 1) * 2);
			//printError (srvproc2, ch, XP_FATAL_ERROR, SRV_SEVERITY_FATAL);
		// end debugging code

			// If we are using an XML Template, retrieve it from the database using the 
			// ID passed in the parameters
			if (bParamIsTemplateID)
			{
				sd->sTemp1 = "Provider=SQLOLEDB;Data Source=";
				sd->sTemp2 = ";Initial Catalog=Kalahari";
				sd->sTemp3 = ";Trusted_Connection=yes;";
				sd->sConnectionString = String::Concat(sd->sTemp1,
							sd->sServer,
							sd->sTemp2,
							sd->sTemp3);

				// Read the template text from the database
				OleDbConnection* spOLECONN = new OleDbConnection(sd->sConnectionString);
				spOLECONN->Open();
				OleDbCommand* spOLECMD = spOLECONN->CreateCommand();
				sd->sTemp1 = "SELECT XMLTemplate FROM XMLTemplate (NOLOCK) WHERE XMLTemplateID = ";
				sd->sSQL = String::Concat(sd->sTemp1,
											sd->sXMLInput);
				spOLECMD->CommandText = sd->sSQL;
				// Returns a single scalar value from the query
				sd->sXMLTemplateText = Convert::ToString(spOLECMD->ExecuteScalar());
			}
			
			// create an instance of an ADO.Net SQLXML object, and connect to the database	
			sd->sTemp1 = "Provider=SQLOLEDB;Data Source=";
			sd->sTemp2 = ";Initial Catalog=";
			sd->sTemp3 = ";Trusted_Connection=yes;";
			sd->sConnectionString = String::Concat(sd->sTemp1,
						sd->sServer,
						sd->sTemp2,
						sd->sDatabase,
						sd->sTemp3);

			SqlXmlCommand* spCMD = new SqlXmlCommand(sd->sConnectionString);

			if (bParamIsTemplateID)
			{
				// Use the template, not the query
				spCMD->CommandText = sd->sXMLTemplateText;
				spCMD->CommandType = SqlXmlCommandType::Template;

				// Create the parameters to the XML Template
				SqlXmlParameter* spPARAM1 = spCMD->CreateParameter();
				spPARAM1->Name = "@Param1";
				spPARAM1->Value = sd->sParam1;

				SqlXmlParameter* spPARAM2 = spCMD->CreateParameter();
				spPARAM2->Name = "@Param2";
				spPARAM2->Value = sd->sParam2;

				SqlXmlParameter* spPARAM3 = spCMD->CreateParameter();
				spPARAM3->Name = "@Param3";
				spPARAM3->Value = sd->sParam3;

				SqlXmlParameter* spPARAM4 = spCMD->CreateParameter();
				spPARAM4->Name = "@Param4";
				spPARAM4->Value = sd->sParam4;

				SqlXmlParameter* spPARAM5 = spCMD->CreateParameter();
				spPARAM5->Name = "@Param5";
				spPARAM5->Value = sd->sParam5;

				SqlXmlParameter* spPARAM6 = spCMD->CreateParameter();
				spPARAM6->Name = "@Param6";
				spPARAM6->Value = sd->sParam6;

				SqlXmlParameter* spPARAM7 = spCMD->CreateParameter();
				spPARAM7->Name = "@Param7";
				spPARAM7->Value = sd->sParam7;

				SqlXmlParameter* spPARAM8 = spCMD->CreateParameter();
				spPARAM8->Name = "@Param8";
				spPARAM8->Value = sd->sParam8;

				SqlXmlParameter* spPARAM9 = spCMD->CreateParameter();
				spPARAM9->Name = "@Param9";
				spPARAM9->Value = sd->sParam9;

				SqlXmlParameter* spPARAM10 = spCMD->CreateParameter();
				spPARAM10->Name = "@Param10";
				spPARAM10->Value = sd->sParam10;
			}
			else
			{
				// Use the SQL query passed in the parameter
				// Ensure the SQL Query used the FOR XML clause
				spCMD->CommandText = sd->sXMLInput;
				spCMD->CommandType = SqlXmlCommandType::Sql;
			}
			// Create the data set object, and execute the XML Template/SQL Query
			DataSet* spDS = new DataSet();
			SqlXmlAdapter* spDA = new SqlXmlAdapter(spCMD);
			spDA->Fill (spDS);

			// Save the result of the XML Template/SQL Query to the XML file
			spDS->WriteXml (sd->sXMLFilename);
		}
		// Error handling
		catch (System::Exception* ex)
		{
			sd->sTemp1 = "An Error has occurred. Error Description: ";
			sd->sTemp2 = ex->Message;
			sd->sErrorMessage = String::Concat(sd->sTemp1, sd->sTemp2);
			// check for specific errors
			sd->sTemp3 = "Object reference not set to an instance of an object.";
			int iPos = sd->sErrorMessage->IndexOf(sd->sTemp3);
			if (iPos > 0)
			{
				sd->sTemp4 = " Check your parameters!  Exec sproc without parameters to see syntax.";
				sd->sErrorMessage = String::Concat(sd->sErrorMessage, sd->sTemp4);
			}
			// check for specific errors
			sd->sTemp3 = "Streaming not supported over multiple column result";
			iPos = sd->sErrorMessage->IndexOf(sd->sTemp3);
			if (iPos > 0)
			{
				sd->sTemp4 = " Check that you are using the FOR XML clause in your query!";
				sd->sErrorMessage = String::Concat(sd->sErrorMessage, sd->sTemp4);
			}
			// display the error message
			printError (srvproc2, convertStringToChar(sd->sErrorMessage), XP_FATAL_ERROR, SRV_SEVERITY_FATAL);
			// If error, free dynamically allocated memory.
			for (nParam = 0; nParam < nParams; nParam++)
			{
				if (ppbData[nParam])
				{
					free(ppbData[nParam]);
				}
			}
			free(ppbData);
			// End the stored proc
			return (999);
		}

		// If success:
		// Free dynamically allocated memory.
		for (nParam = 0; nParam < nParams; nParam++)
		{
			if (ppbData[nParam])
			{
				free(ppbData[nParam]);
			}
		}

		free(ppbData);

		// Indicate that we're done.
		if (rc == XP_NOERROR)
		{
			srv_senddone(srvproc2, (SRV_DONE_COUNT | SRV_DONE_MORE), 0, 1);
		}
		else
		{
			if (rc == XP_ERROR) printError (srvproc2, "XP encountered an error.", XP_FATAL_ERROR, SRV_SEVERITY_FATAL);
		}
		// end the stored proc
		return (rc);
	}
};



RETCODE __declspec(dllexport) xp_XMLTemplate_WriteFile(SRV_PROC *srvproc)
{
	RETCODE rc=0;
	CXTWFile* iX = new CXTWFile;
	(SRV_PROC*)(iX->srvproc2) = srvproc;
	rc = (RETCODE)(iX->CXTWFileMain());
	delete iX;
	return rc;
	
}

// send szErrorMsg to client
void printError (SRV_PROC *pSrvProc, CHAR* szErrorMsg, int SRV_ERROR_NUMBER, int SRV_SEVERITY)
    {
    srv_sendmsg(pSrvProc, SRV_MSG_ERROR, SRV_ERROR_NUMBER, SRV_SEVERITY, 1,
            NULL, 0, (DBUSMALLINT) __LINE__, 
            szErrorMsg,
            SRV_NULLTERM);

    srv_senddone(pSrvProc, (SRV_DONE_ERROR | SRV_DONE_MORE), 0, 0);
    }

// send XP usage info to client
void printUsage (SRV_PROC *pSrvProc)
    {
	// prints the syntax if executed with no parameters
    srv_sendmsg(pSrvProc, SRV_MSG_ERROR, XP_PARAM_ERROR, SRV_INFO, 1,
            NULL, 0, (DBUSMALLINT) __LINE__, 
            "usage: exec xp_XMLTemplate_WriteFile @XMLFileName, @XMLTemplate, @Server, @Database, @Param1, @Param2, ... @Param10",
            SRV_NULLTERM);
    srv_senddone(pSrvProc, (SRV_DONE_ERROR | SRV_DONE_MORE), 0, 0);

    }

char* convertStringToChar (System::String* sStringToconvert)
{

	// Conversion to wchar_t* :
	const wchar_t __pin* p = PtrToStringChars(sStringToconvert);

    // Conversion to char* :
    // Convert wchar_t* to char* using a conversion functionssuch as: 
    //WideCharToMultiByte()
    //wcstombs()
	char* ch = (char *)malloc((sStringToconvert->Length + 1) * 2);
	wcstombs(ch, p, (sStringToconvert->Length + 1) * 2);

	return ch;

}