Timothy's Space 君看一叶舟，出没风波里

看了很多VC数据库的书，里面很少设计到用VC和存储过程操作的资料，网上找了又找，结果还是很少，收集了一下一些代码片断，以供参考。

[arrow]代码片断1：

_variant_t _vValue;
_variant_t RecordsAffected;
VARIANT vtRetval;
vtRetval.vt = VT_I2;
_CommandPtr pCmd = NULL;
_ParameterPtr pPrm = NULL;
_variant_t vtEmpty(DISP_E_PARAMNOTFOUND, VT_ERROR);
_variant_t vtEmpty2(DISP_E_PARAMNOTFOUND, VT_ERROR);
CString tempsql;
pCmd.CreateInstance( __uuidof( Command ) );
pPrm.CreateInstance( __uuidof( Parameter ) );
pCmd ->ActiveConnection = pConn;
pCmd ->CommandType = adCmdStoredProc;
pCmd ->CommandText = _bstr_t(L”sp_addumpdevice”);
pPrm = pCmd ->CreateParameter( _bstr_t(“retval”),adInteger,adParamReturnValue,sizeof(int),vtRetval);
pCmd ->Parameters ->Append( pPrm );
if(strcmp(sblx,”0″)==0)
{
pPrm = pCmd ->CreateParameter( _bstr_t(“sblx”),adChar,adParamInput,4,”disk” );
pPrm ->Value =”disk”;
}
else
{

pPrm = pCmd ->CreateParameter( _bstr_t(“sblx”),adChar,adParamInput,4,”tape”);
pPrm ->Value =”tape”;

}
pCmd ->Parameters ->Append( pPrm );

pPrm = pCmd ->CreateParameter( _bstr_t(“sbmc”),adVarChar,adParamInput,20,_bstr_t(sbmc));
pPrm ->Value =_bstr_t(sbmc);
pCmd ->Parameters ->Append( pPrm );

pPrm = pCmd ->CreateParameter( _bstr_t(“bflj”),adVarChar,adParamInput,50,_bstr_t(bflj));
pPrm ->Value =_bstr_t(bflj);
pCmd ->Parameters ->Append( pPrm );
pCmd ->Execute( &vtEmpty, &vtEmpty2,adExecuteNoRecords);

[arrow]代码片断2：

m_pParam =m_pCommand->CreateParameter(“id”,adInteger,adParamInput,-1,(_variant_t)”10″);
//给参数设置各属性
m_pCommand->Parameters->Append(m_pParam);//加入到Command对象的参数集属性中

m_pParam1=m_pCommand->CreateParameter(“Name”,adVarChar,adParamInput,20,(_variant_t)”songwenfeng”);
m_pCommand->Parameters->Append(m_pParam1);

m_pParam2=m_pCommand->CreateParameter(“sdate”,adVarChar,adParamInput,32,(_variant_t)”2004-6-8″);
m_pCommand->Parameters->Append(m_pParam2);

[arrow]代码片断3：

_ParameterPtr para[3];
_CommandPtr pCmd;

pCmd.CreateInstance(“ADODB.Command”);
para[0].CreateInstance(“ADODB.Parameter”);
para[1].CreateInstance(“ADODB.Parameter”);
para[2].CreateInstance(“ADODB.Parameter”);
pCmd->ActiveConnection=m_pConn;

pCmd->CommandText=”存储过程名”
para[0]=pCmd->CreateParameter(“”, adBSTR,adParamInput,
sizeof(char[50]),vVar); //字符串型输入参数
pCmd->Parameters->Append(para[0]);
para[1]=pCmd->CreateParameter(“”, adInteger,adParamInput,
sizeof(int),olevariantVar); //整型输入参数
pCmd->Parameters->Append(para[1]);
para[2]=pCmd->CreateParameter(“”, adBSTR,adParamOutput,
sizeof(char[50]),”"); //字符串型输出参数
pCmd->Parameters->Append(para[2]);
pCmd->Execute( NULL, NULL, adCmdStoredProc);

[arrow]代码片断4：

使用vc++ ,sql server。
假设一个表：name sex age.
现在想求出有多少个男性，多少个女性。
如果不是用存储过程，就必须一条一条的比较。
现在想问一下：如何写这个存储过程，如何在vc中调用这个存储过程，如何取得存储过程的结果。
—————————————————————

_CommandPtr cmmd;
_ParameterPtr param;
HRESULT hr = cmmd.CreateInstance(__uuidof(Command));
if(FAILED(hr))
{ AfxMessageBox(“NewNetDatabase()中创建_CommandPtr对象失败”);
return “创建对象失败”;
}
cmmd->ActiveConnection = m_pConnection;//需要使用的ADO连接
cmmd->CommandType=adCmdStoredProc;
cmmd->CommandText=_bstr_t(“pkg_shenrole.AddRoleInfo”);//这个就是存储过程名称
param = cmmd->CreateParameter(“”,adBigInt, adParamInput, sizeof(long),(long)(nRoleId));
cmmd->Parameters->Append(param);

#include
#include
#include
#include
BOOL UseHttpSendReqEx(HINTERNET hRequest, DWORD dwPostSize,CString strLocalFile);
BOOL Upload(CString bstrLocalFile,CString bstrServerIP,CString strServerPort,CString bstrRemoteFile);
#define BUFFSIZE 500

void main( int argc, char **argv )
{

if (argc < 5)
{
printf("Usage: Bigpost \n”);
printf(“ is the local file to POST\n”);
printf(“ is the server’s IP to POST to\n”);
printf(“ is the server’s Port to POST to\n”);
printf(“ is the virtual path to POST to\n”);
exit(0);
}
Upload(argv[1],argv[2],argv[3],argv[4]);
}
BOOL UseHttpSendReqEx(HINTERNET hRequest, DWORD dwPostSize,CString strLocalFile)
{
DWORD dwRead;
BYTE* buffer;
printf(“Local file:%s\n”,strLocalFile);
FILE* fLocal;
if((fLocal=fopen(strLocalFile,”rb”))==NULL){
printf(“Can’t open the file:%s,maybe it doesn’t exist!\n”,strLocalFile);
return false;
}
fseek(fLocal,0L,SEEK_END);
dwRead=ftell(fLocal);
rewind(fLocal);
buffer=(BYTE *)malloc(dwRead);
if(!buffer){
printf(“not enough memory!\n”);
return false;
}
printf(“length of file:%d\n”,dwRead);
dwRead=fread(buffer,1,dwRead,fLocal);
dwPostSize=dwRead;

INTERNET_BUFFERS BufferIn;
DWORD dwBytesWritten;
BOOL bRet;
BufferIn.dwStructSize = sizeof( INTERNET_BUFFERS ); // Must be set or error will occur
BufferIn.Next = NULL;
BufferIn.lpcszHeader = NULL;
BufferIn.dwHeadersLength = 0;
BufferIn.dwHeadersTotal = 0;
BufferIn.lpvBuffer = NULL;
BufferIn.dwBufferLength = 0;
BufferIn.dwBufferTotal = dwPostSize; // This is the only member used other than dwStructSize
BufferIn.dwOffsetLow = 0;
BufferIn.dwOffsetHigh = 0;

if(!HttpSendRequestEx( hRequest, &BufferIn, NULL, 0, 0))
{
printf( “Error on HttpSendRequestEx %d\n”,GetLastError() );
return FALSE;
}
bRet=TRUE;
if(bRet=InternetWriteFile( hRequest, buffer, dwPostSize, &dwBytesWritten))
printf( “\r%d bytes sent.”, dwPostSize);
if(!bRet)
{
printf( “\nError on InternetWriteFile %lu\n”,GetLastError() );
return FALSE;
}

if(!HttpEndRequest(hRequest, NULL, 0, 0))
{
printf( “Error on HttpEndRequest %lu \n”, GetLastError());
return FALSE;
}
fclose(fLocal);
free(buffer);
return TRUE;
}

BOOL Upload(CString strLocalFile,CString strServerIP,CString strServerPort,CString strRemoteFile){
DWORD dwPostSize=0;
int intServerPort=atoi(strServerPort);
HINTERNET hSession = InternetOpen( “HttpSendRequestEx”, INTERNET_OPEN_TYPE_PRECONFIG,
NULL, NULL, 0);
if(!hSession)
{
printf(“Failed to open session\n”);
return FALSE;
}
HINTERNET hConnect = InternetConnect(hSession, strServerIP, intServerPort,
NULL, NULL, INTERNET_SERVICE_HTTP,NULL, NULL);
if (!hConnect){
printf( “Failed to connect\n” );
return FALSE;
}else{
HINTERNET hRequest = HttpOpenRequest(hConnect, “PUT”, strRemoteFile,
NULL, NULL, NULL, INTERNET_FLAG_NO_CACHE_WRITE, 0);
if (!hRequest){
printf( “Failed to open request handle\n” );
}else{
if(UseHttpSendReqEx(hRequest, dwPostSize,strLocalFile))
{
char pcBuffer[BUFFSIZE];
DWORD dwBytesRead;

printf(“\nThe following was returned by the server:\n”);
do
{ dwBytesRead=0;
if(InternetReadFile(hRequest, pcBuffer, BUFFSIZE-1, &dwBytesRead))
{
pcBuffer[dwBytesRead]=0×00; // Null-terminate buffer
printf(“%s”, pcBuffer);
}
else
printf(“\nInternetReadFile failed”);
}while(dwBytesRead>0);
printf(“\n”);
}
if (!InternetCloseHandle(hRequest))
printf( “Failed to close Request handle\n” );
}
if(!InternetCloseHandle(hConnect))
printf(“Failed to close Connect handle\n”);
}
if( InternetCloseHandle( hSession ) == FALSE ){
printf( “Failed to close Session handle\n” );
return FALSE;
}
printf( “\nFinished.\n” );
return TRUE;
}

char cr[3];
char lable[12];
for(int n=0;n<=25;n++)
{
CString str;
str.Format(“%c:”,n+’A');
strcpy(cr,str);
int dt = GetDriveType(cr);
int succeed = GetVolumeInformation(cr,lable,12,NULL,NULL,NULL,NULL,0);
switch(dt)
{
case DRIVE_UNKNOWN:
{
str.Format(“(%s) %s is DRIVE_UNKNOWN!”,lable,cr);
break;
}
case DRIVE_REMOVABLE:
{
if(succeed)
{
str.Format(“(%s) %s is DRIVE_REMOVABLE!”,lable,cr);
}
else
{
str.Format(“%s is DRIVE_REMOVABLE!”,cr);
}
break;
}
case DRIVE_FIXED:
{
str.Format(“(%s) %s is DRIVE_FIXED!”,lable,cr);
break;
}
case DRIVE_REMOTE:
{
str.Format(“(%s) %s is DRIVE_REMOTE!”,lable,cr);
break;
}
case DRIVE_CDROM:
{
str.Format(“(%s) %s is DRIVE_CDROM!”,lable,cr);
break;
}
case DRIVE_RAMDISK:
{
str.Format(“(%s) %s is DRIVE_RAMDISK!”,lable,cr);
break;
}
default:
{
str.Empty();
break;
}
}
if(!str.IsEmpty())
{
AfxMessageBox(str);
}
}

char* szFileName = “C:\\EnochShen.exe”;
DWORD dwSize = GetFileVersionInfoSize(szFileName,NULL);
LPVOID pBlock = malloc(dwSize);
GetFileVersionInfo(szFileName,0,dwSize,pBlock);

char* pVerValue = NULL;
UINT nSize = 0;
VerQueryValue(pBlock,TEXT(“\\VarFileInfo\\Translation”),
(LPVOID*)&pVerValue,&nSize);

CString strSubBlock,strTranslation,strTemp;
strTemp.Format(“000%x”,*((unsigned short int *)pVerValue));
strTranslation = strTemp.Right(4);
strTemp.Format(“000%x”,*((unsigned short int *)&pVerValue[2]));
strTranslation += strTemp.Right(4);
//080404b0为中文，040904E4为英文

//文件描述
strSubBlock.Format(“\\StringFileInfo\\%s\\FileDescription”,strTranslation);
VerQueryValue(pBlock,strSubBlock.GetBufferSetLength(256),(LPVOID*)&pVerValue,&nSize);
strSubBlock.ReleaseBuffer();
strTemp.Format(“文件描述: %s”,pVerValue);
AfxMessageBox(strTemp);

//内部名称
strSubBlock.Format(“\\StringFileInfo\\%s\\InternalName”,strTranslation);
VerQueryValue(pBlock,strSubBlock.GetBufferSetLength(256),(LPVOID*)&pVerValue,&nSize);
strSubBlock.ReleaseBuffer();
strTemp.Format(“文件描述: %s”,pVerValue);
AfxMessageBox(strTemp);

//合法版权
strSubBlock.Format(“\\StringFileInfo\\%s\\LegalTradeMarks”,strTranslation);
VerQueryValue(pBlock,strSubBlock.GetBufferSetLength(256),(LPVOID*)&pVerValue,&nSize);
strSubBlock.ReleaseBuffer();
strTemp.Format(“合法版权: %s”,pVerValue);
AfxMessageBox(strTemp);

//原始文件名
strSubBlock.Format(“\\StringFileInfo\\%s\\OriginalFileName”,strTranslation);
VerQueryValue(pBlock,strSubBlock.GetBufferSetLength(256),(LPVOID*)&pVerValue,&nSize);
strSubBlock.ReleaseBuffer();
strTemp.Format(“原始文件名: %s”,pVerValue);
AfxMessageBox(strTemp);

//产品名称
strSubBlock.Format(“\\StringFileInfo\\%s\\ProductName”,strTranslation);
VerQueryValue(pBlock,strSubBlock.GetBufferSetLength(256),(LPVOID*)&pVerValue,&nSize);
strSubBlock.ReleaseBuffer();
strTemp.Format(“产品名称: %s”,pVerValue);
AfxMessageBox(strTemp);

//产品版本
strSubBlock.Format(“\\StringFileInfo\\%s\\ProductVersion”,strTranslation);
VerQueryValue(pBlock,strSubBlock.GetBufferSetLength(256),(LPVOID*)&pVerValue,&nSize);
strSubBlock.ReleaseBuffer();
strTemp.Format(“产品版本: %s”,pVerValue);
AfxMessageBox(strTemp);

free(pBlock);

HKEY hKey;
char szFileName[256];
GetModuleFileName(NULL,szFileName,256);
RegOpenKey(HKEY_LOCAL_MACHINE,”SOFTWARE\\Microsoft\\windows\\currentversion\\run”,&hKey);
if(m_bAutoRun)
{
RegSetValueEx(hKey,”RunmeAtStartup”,0,REG_SZ,(BYTE *)szFileName,sizeof(szFileName));
}
else
{
RegDeleteValue(hKey,”RunmeAtStartup”);
}
RegCloseKey(hKey);