[펌] [MS-SQL][Visual C++ 6.0_Stored_Procedures_TEST_예제_Program]
DB&XML 2009. 9. 3. 17:31 |[MS-SQL][Visual C++ 6.0_Stored_Procedures_TEST_예제_Program]
//--------------------------------------------------------------
[New Class...]
[Class type] : [Generic Class] | [Name] : [CADODB]
#include "ADODB.h"
class CADODB
{
private:
_RecordsetPtr m_RS;
_CommandPtr m_CMD;
_ConnectionPtr m_CONN;
public:
CADODB();
virtual ~CADODB();
CString db_Error_Message;
BOOL DBConnect(char* pWD, char* pID, char* pDataBase, char* pConnectIP);
BOOL IsOpen_DB();
BOOL IsOpen_RS();
void Close_RS();
BOOL IsEOF();
BOOL Next();
BOOL First();
int GetRecordCount();
int GetFieldCount();
void GetRS(_variant_t x, _bstr_t& ret);
void GetRS(_variant_t x, _variant_t& ret);
void GetRS(_variant_t x, float& ret);
void GetRS(_variant_t x, long& ret);
void GetRS(_variant_t x, double& ret);
void Open_Command(char* StoredProcedureString);
void Add_Parameters(_bstr_t _ParameterName,
DataTypeEnum _SqlDbType,
int _Size,
ParameterDirectionEnum _Direction,
_variant_t _User_Value);
BOOL RowReader();
long QueryExecute(_bstr_t _ParameterName);
};
//--------------------------------------------------------------
[ClassView] - [CADODB]
CADODB::CADODB()
{
m_RS = NULL;
m_CMD = NULL;
m_CONN = NULL;
}
CADODB::~CADODB()
{
if(m_RS != NULL)
{
if(IsOpen_RS())
{
m_RS->Close();
}
}
if(m_CONN != NULL)
{
if(IsOpen_DB())
{
m_CONN->Close();
}
}
}
BOOL CADODB::DBConnect(char* pWD, char* pID, char* pDataBase, char* pConnectIP)
{
CString strConnectionString;
strConnectionString.Format(_T("Provider=SQLOLEDB.1;Persist Security Info=True;
Password = %s;
User ID = %s;
Initial Catalog = %s;
Data Source = %s"),
pWD,
pID,
pDataBase,
pConnectIP);
m_CONN.CreateInstance("ADODB.Connection");
m_CONN->ConnectionString = _bstr_t(strConnectionString);
HRESULT hr = m_CONN->Open("", "", "", -1);
if(SUCCEEDED(hr))
{
return TRUE;
}
else
{
return FALSE;
}
}
BOOL CADODB::IsOpen_DB()
{
return ((m_CONN->GetState() == adStateOpen ) ? TRUE : FALSE);
}
BOOL CADODB::IsOpen_RS()
{
return ((m_RS->GetState() == adStateOpen ) ? TRUE : FALSE);
}
void CADODB::Close_RS()
{
if(IsOpen_RS())
{
m_RS->Close();
}
}
BOOL CADODB::IsEOF()
{
return m_RS->adoEOF;
}
BOOL CADODB::Next()
{
return (FAILED(m_RS->MoveNext()) ? FALSE : TRUE);
}
BOOL CADODB::First()
{
return (FAILED(m_RS->MoveFirst()) ? FALSE : TRUE);
}
int CADODB::GetRecordCount()
{
HRESULT hr;
ASSERT(m_RS != NULL);
try
{
int count = (int)m_RS->GetRecordCount();
if (count < 0)
{
hr = m_RS->MoveFirst();
count = 0;
while (!m_RS->adoEOF)
{
count++;
m_RS->MoveNext();
}
}
if (m_RS->adoEOF)
{
m_RS->MoveFirst();
}
return count;
}
catch (_com_error ex)
{
TRACE(_T("Warning: GetRecordCount ErrorMessage: %s;
File: %s;
Line: %d\n"),
ex.ErrorMessage(),
__FILE__,
__LINE__);
return -1;
}
}
int CADODB::GetFieldCount()
{
return (int)m_RS->Fields->GetCount();
}
void CADODB::GetRS(_variant_t x, _bstr_t& ret)
{
ret = m_RS->Fields->Item[x]->Value;
}
void CADODB::GetRS(_variant_t x, _variant_t& ret)
{
ret = m_RS->Fields->Item[x]->Value;
}
void CADODB::GetRS(_variant_t x, float& ret)
{
ret = m_RS->Fields->Item[x]->Value;
}
void CADODB::GetRS(_variant_t x, long& ret)
{
ret = m_RS->Fields->Item[x]->Value;
}
void CADODB::GetRS(_variant_t x, double& ret)
{
ret = m_RS->Fields->Item[x]->Value;
}
void CADODB::Open_Command(char* StoredProcedureString)
{
m_CMD.CreateInstance("ADODB.Command");
m_CMD->CommandText = StoredProcedureString;
m_CMD->CommandType = adCmdStoredProc;
}
void CADODB::Add_Parameters(_bstr_t _ParameterName,
DataTypeEnum _SqlDbType,
int _Size,
ParameterDirectionEnum _Direction,
_variant_t _User_Value)
{
_ParameterPtr AddParameter;
AddParameter.CreateInstance("ADODB.Parameter");
AddParameter->Name = _ParameterName;
AddParameter->Type = _SqlDbType;
AddParameter->Size = _Size;
AddParameter->Direction = _Direction;
m_CMD->Parameters->Append(AddParameter);
m_CMD->Parameters->Item[_ParameterName]->Value = _User_Value;
}
BOOL CADODB::RowReader()
{
try
{
m_CMD->ActiveConnection = m_CONN;
m_CMD->Execute(NULL, NULL, adCmdStoredProc);
m_RS.CreateInstance(__uuidof(Recordset));
m_RS->PutRefSource(m_CMD);
_variant_t vNull;
vNull.vt = VT_ERROR;
vNull.scode = DISP_E_PARAMNOTFOUND;
m_RS->CursorLocation = adUseClient;
m_RS->Open(vNull, vNull, adOpenStatic, adLockOptimistic, adCmdStoredProc);
return true;
}
catch (_com_error ex)
{
if(m_RS != NULL)
{
db_Error_Message = ex.ErrorMessage();
Close_RS();
}
else
{
db_Error_Message = ex.ErrorMessage();
}
return false;
}
}
long CADODB::QueryExecute(_bstr_t _ParameterName)
{
m_CMD->ActiveConnection = m_CONN;
try
{
m_CMD->Execute(NULL, NULL, adCmdStoredProc);
return long(m_CMD->Parameters->Item[_ParameterName]->Value);
}
catch (_com_error ex)
{
db_Error_Message = ex.ErrorMessage();
return -1;
}
}
//--------------------------------------------------------------
[include files] - [stdafx.h]
//------------------------------
#pragma warning(push)
#pragma warning(disable:4146)
#import "c:\Program Files\Common Files\System\ADO\msado15.dll" no_namespace rename("EOF","adoEOF")
#pragma warning(pop)
//--------------------------------------------------------------
//--------------------------------------------------------------
[ClassView] - [CADOVCApp]
BOOL CADOVCApp::InitInstance()
{
CoInitialize(NULL); //ADO 초기화
}
int CADOVCApp::ExitInstance()
{
CoUninitialize(); //ADO 제거
return CWinApp::ExitInstance();
}
//--------------------------------------------------------------
[include] - [ADOVCDlg.h]
#include "ADODB.h"
class CADOVCDlg : public CDialog
{
public:
void Execute_Message_Send(CString GetMessage);
void iMageList_LiST_View();
BOOL DBConnect();
CADODB m_ADO;
CStatusBar m_wndStatusBar;
//--------------------------------------------------------------
[ClassView] - [CADOVCDlg]
BOOL CADOVCDlg::OnInitDialog()
{
if(!DBConnect())
{
AfxMessageBox("Error DB Connect!!");
}
}
//--------------------------------------------------------------
[ClassView] - [CADOVCDlg]
void CADOVCDlg::Execute_Message_Send(CString GetMessage)
{
m_Message_List.AddString(GetMessage);
m_Message_List.SetTopIndex(m_Message_List.GetCount() - 1);
}
// Stored Procedure - [MS-SQL][Microsoft Visual C++ 6.0]
BOOL CADOVCDlg::DBConnect()
{
if(m_ADO.DBConnect("550107", "sa", "iMageDB", "SCORPION"))
{
return TRUE;
}
else
{
return FALSE;
}
}
void CADOVCDlg::OnBtnDelete()
{
CString msgString;
long ErrorNUM = 0;
BOOL b_GetValue = TRUE;
m_DB_Delete_NUM = GetDlgItemInt(IDC_EDIT_DELETE, &b_GetValue, FALSE);
msgString.Format(_T("[삭제] - [PF1_1_5_iMageList_DELETE]"));
m_wndStatusBar.SetPaneText(0, msgString);
msgString.Format(_T("[삭제]-[PF1_1_5_iMageList_DELETE] - [삭제번호] → [%d]"), m_DB_Delete_NUM);
Execute_Message_Send(msgString);
msgString.Format(_T("삭제번호 : [%d]\n\n삭제 하시겠습니까?"), m_DB_Delete_NUM);
if(AfxMessageBox(msgString, MB_YESNO) == IDYES)
{
m_ADO.Open_Command("PF1_1_5_iMageList_DELETE");
m_ADO.Add_Parameters("@DB_ERR", adInteger, 4, adParamOutput, 0l);
m_ADO.Add_Parameters("@iMageNUM_1", adInteger, 4, adParamInput, m_DB_Delete_NUM);
ErrorNUM = m_ADO.QueryExecute("@DB_ERR");
if(ErrorNUM == 0)
{
msgString.Format(_T("[삭제] → [Success]"));
Execute_Message_Send(msgString);
iMageList_LiST_View();
}
else
{
if (ErrorNUM == 544)
{
msgString.Format(_T("[iMageList][Delete ERROR] [Delete NUM] : [%d] %s"),
m_DB_Delete_NUM, m_ADO.db_Error_Message);
}
else
{
msgString.Format(_T("[PF1_1_5_iMageList_DELETE][Delete ERROR] %s"), m_ADO.db_Error_Message);
}
Execute_Message_Send(msgString);
}
}
}
void CADOVCDlg::OnBtnInsert()
{
CString msgString;
long ErrorNUM = 0;
BOOL b_GetValue = TRUE;
long Get_INPUT_Error_NUM = 0;
long Get_iMageNUM = 0;
GetDlgItemText(IDC_EDIT_PATH, m_DB_Insert_Path);
GetDlgItemText(IDC_EDIT_FILENAME, m_DB_Insert_FileName);
m_DB_Insert_FileSize = GetDlgItemInt(IDC_EDIT_FILESIZE, &b_GetValue, FALSE);
msgString.Format(_T("[저장] - [PF1_1_5_iMageList_iNSERT"));
m_wndStatusBar.SetPaneText(0, msgString);
msgString.Format(_T("[저장]-[PF1_1_5_iMageList_iNSERT] - [파일명] → [%s]"), m_DB_Insert_FileName);
Execute_Message_Send(msgString);
if(!m_ADO.IsOpen_DB())
{
DBConnect();
}
m_ADO.Open_Command("PF1_1_5_iMageList_iNSERT");
m_ADO.Add_Parameters("@DB_ERR", adInteger, 4, adParamOutput, 0l);
m_ADO.Add_Parameters("@iMageNUM_1", adInteger, 4, adParamOutput, 0l);
m_ADO.Add_Parameters("@ImagePath_2", adVarChar, 255, adParamInput, _variant_t(m_DB_Insert_Path));
m_ADO.Add_Parameters("@ImageFile_3", adVarChar, 255, adParamInput, _variant_t(m_DB_Insert_FileName));
m_ADO.Add_Parameters("@iMageSize_4", adInteger, 4, adParamInput, m_DB_Insert_FileSize);
ErrorNUM = m_ADO.QueryExecute("@DB_ERR");
if(ErrorNUM == 0)
{
msgString.Format(_T("[추가] → [Success]"));
Execute_Message_Send(msgString);
iMageList_LiST_View();
}
else
{
if (ErrorNUM == 541)
{
msgString.Format(_T("[iMageList][iNSERT ERROR] [iMageList FileName] : [%d] %s"),
m_DB_Insert_FileName, m_ADO.db_Error_Message);
}
else
{
msgString.Format(_T("[iMageList][iNSERT ERROR] %s"), m_ADO.db_Error_Message);
}
Execute_Message_Send(msgString);
}
}
void CADOVCDlg::OnBtnSearch()
{
CString msgString;
msgString.Format(_T("[조회] - [PF1_1_5_iMageList_LIST]"));
m_wndStatusBar.SetPaneText(0, msgString);
iMageList_LiST_View();
}
void CADOVCDlg::iMageList_LiST_View()
{
CString msgString;
CString db_Get_String;
CString str_temp;
int iROWCOUNT;
int iField;
BOOL b_GetValue = TRUE;
GetDlgItemText(IDC_EDIT_SEARCH_FILENAME, m_DB_Search_FileName);
m_DB_UserTOP = GetDlgItemInt(IDC_EDIT_USER_TOP, &b_GetValue, FALSE);
if(m_DB_Search_FileName.GetLength() == 0 || !b_GetValue)
{
m_DB_Search_FileName = '%';
}
else
{
m_DB_Search_FileName = m_DB_Search_FileName + '%';
}
msgString.Format(_T("[조회][Stored_Procedure - %s] - [검색문자 : %s]"),
_T("PF1_1_5_iMageList_LIST"), m_DB_Search_FileName);
Execute_Message_Send(msgString);
if(!m_ADO.IsOpen_DB())
{
DBConnect();
}
m_ADO.Open_Command("PF1_1_5_iMageList_LIST");
m_ADO.Add_Parameters("@USER_TOP", adInteger, 4, adParamInput, m_DB_UserTOP);
m_ADO.Add_Parameters("@iMageName", adVarChar, 255, adParamInput, _variant_t(m_DB_Search_FileName));
if(m_ADO.RowReader())
{
_bstr_t dbFieldGetValue;
iROWCOUNT = m_ADO.GetRecordCount();
if(iROWCOUNT <= 0) return;
iField = m_ADO.GetFieldCount();
msgString.Format(_T("Record : [%d]"), iROWCOUNT);
m_wndStatusBar.SetPaneText(1, msgString);
msgString.Format(_T("Field : [%d]"), iField);
m_wndStatusBar.SetPaneText(2, msgString);
m_ADO.GetRS(_variant_t((short)0), dbFieldGetValue);
m_DB_Search_List.DeleteAllItems();
for(int iROW = 0; iROW < iROWCOUNT; iROW++)
{
str_temp.Format(_T("%d"), (iROW + 1));
m_DB_Search_List.InsertItem(iROW, str_temp);
for(int jCOL = 0; jCOL < iField; jCOL++)
{
m_ADO.GetRS(_variant_t((short)jCOL), dbFieldGetValue);
db_Get_String.Format(_T(("%s"), (LPCTSTR)dbFieldGetValue);
m_DB_Search_List.SetItemText(iROW, (jCOL + 1), db_Get_String);
}
m_ADO.Next();
}
m_ADO.Close_RS();
}
else
{
msgString.Format(_T("Table ROW Read Error [%s]"), m_ADO.db_Error_Message);
Execute_Message_Send(msgString);
}
}
----------------------------------------------------------------
[MS-SQL] - [Stored Procedure]
----------------------------------------------------------------
[CREATE TABLE] - [iMageList]
--------------------------------
if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[iMageList]') AND OBJECTPROPERTY(id, N'IsUserTable') = 1)
drop table [dbo].[iMageList]
GO
CREATE TABLE [dbo].[iMageList]
(
[iMageNUM] [int] IDENTITY (1, 1) NOT NULL ,
[iMagePath] [varchar] (255) COLLATE Korean_Wansung_CI_AS NOT NULL ,
[iMageFile] [varchar] (255) COLLATE Korean_Wansung_CI_AS NOT NULL ,
[iMageSize] [int] NULL
) ON [PRIMARY]
GO
ALTER TABLE [dbo].[iMageList] WITH NOCHECK ADD
CONSTRAINT [PK_iMageList] PRIMARY KEY CLUSTERED
(
[iMageNUM]
) ON [PRIMARY]
GO
----------------------------------------------------------------
----------------------------------------------------------------
[iMageDB - iMageList] Table
+--------------------------------+
| [iMageDB] - [DataBase] |
+--------------------------------+
| iMageList |
+----------------+---------+-----+
| iMageNUM | int | 4 |
| iMagePath | varchar | 255 |
| iMageFile | varchar | 255 |
| iMageSize | int | 4 |
+----------------+---------+-----+
----------------------------------------------------------------
----------------------------------------------------------------
[Stored Procedures]
----------------------------------------------------------------
[PF1_1_5_iMageList_LIST]
----------------------------------------------------------------
CREATE PROCEDURE [PF1_1_5_iMageList_LIST]
(@USER_TOP [INT],
@iMageName [VARCHAR](255))
AS
SET ROWCOUNT @USER_TOP
SELECT
[dbo].[iMageList].iMagePath,
[dbo].[iMageList].iMageFile,
[dbo].[iMageList].iMageSize,
[dbo].[iMageList].iMageNUM
FROM [dbo].[iMageList]
WHERE ([dbo].[iMageList].iMageFile LIKE @iMageName)
ORDER BY [dbo].[iMageList].iMageNUM DESC
GO
----------------------------------------------------------------
[PF1_1_5_iMageList_DELETE]
----------------------------------------------------------------
CREATE PROCEDURE [PF1_1_5_iMageList_DELETE]
(@DB_ERR [INT] OUTPUT,
@iMageNUM_1 [INT])
AS
SET @DB_ERR = 0
SET XACT_ABORT OFF
BEGIN TRANSACTION
DELETE [iMageDB].[dbo].[iMageList]
WHERE ([iMageDB].[dbo].[iMageList].[iMageNUM] = @iMageNUM_1)
IF @@ERROR <> 0
BEGIN
ROLLBACK TRANSACTION
SET @DB_ERR = 544
RETURN
END
COMMIT TRANSACTION
RETURN
GO
----------------------------------------------------------------
[PF1_1_5_iMageList_iNSERT]
----------------------------------------------------------------
CREATE PROCEDURE [PF1_1_5_iMageList_iNSERT]
(@DB_ERR [INT] OUTPUT,
@iMageNUM_1 [INT] OUTPUT,
@iMagePath_2 [VARCHAR](255),
@iMageFile_3 [VARCHAR](255),
@iMageSize_4 [INT])
AS
SET @DB_ERR = 0
SET XACT_ABORT OFF
BEGIN TRANSACTION
INSERT INTO [iMageDB].[dbo].[iMageList]
([iMagePath],
[iMageFile],
[iMageSize])
VALUES
(@iMagePath_2,
@iMageFile_3,
@iMageSize_4)
IF @@ERROR <> 0
BEGIN
ROLLBACK TRANSACTION
SET @DB_ERR = 541
RETURN
END
SET @iMageNUM_1 = (SELECT MAX([dbo].[iMageList].iMageNUM) FROM [iMageDB].[dbo].[iMageList])
COMMIT TRANSACTION
RETURN
GO
----------------------------------------------------------------
----------------------------------------------------------------
[T-SQL]-[Data Insert]
----------------------------------------------------------------
INSERT INTO iMageList ([iMagePath], [iMageFile], [iMageSize]) VALUES ('iMageList\', 'CostumePlay_001.jpg', 34511)
INSERT INTO iMageList ([iMagePath], [iMageFile], [iMageSize]) VALUES ('iMageList\', 'CostumePlay_002.jpg', 83316)
INSERT INTO iMageList ([iMagePath], [iMageFile], [iMageSize]) VALUES ('iMageList\', 'CostumePlay_003.jpg', 95997)
INSERT INTO iMageList ([iMagePath], [iMageFile], [iMageSize]) VALUES ('iMageList\', 'CostumePlay_004.jpg', 192613)
INSERT INTO iMageList ([iMagePath], [iMageFile], [iMageSize]) VALUES ('iMageList\', 'CostumePlay_005.jpg', 33858)
INSERT INTO iMageList ([iMagePath], [iMageFile], [iMageSize]) VALUES ('iMageList\', 'CostumePlay_006.jpg', 67318)
INSERT INTO iMageList ([iMagePath], [iMageFile], [iMageSize]) VALUES ('iMageList\', 'CostumePlay_007.jpg', 64452)
INSERT INTO iMageList ([iMagePath], [iMageFile], [iMageSize]) VALUES ('iMageList\', 'imgGirl_001.jpg', 224121)
INSERT INTO iMageList ([iMagePath], [iMageFile], [iMageSize]) VALUES ('iMageList\', 'imgGirl_002.jpg', 58777)
INSERT INTO iMageList ([iMagePath], [iMageFile], [iMageSize]) VALUES ('iMageList\', 'imgGirl_003.jpg', 33838)
INSERT INTO iMageList ([iMagePath], [iMageFile], [iMageSize]) VALUES ('iMageList\', 'imgGirl_004.jpg', 191308)
INSERT INTO iMageList ([iMagePath], [iMageFile], [iMageSize]) VALUES ('iMageList\', 'imgGirl_005.jpg', 467618)
INSERT INTO iMageList ([iMagePath], [iMageFile], [iMageSize]) VALUES ('iMageList\', 'imgGirl_006.jpg', 184552)
INSERT INTO iMageList ([iMagePath], [iMageFile], [iMageSize]) VALUES ('iMageList\', 'imgGirl_007.jpg', 78885)
INSERT INTO iMageList ([iMagePath], [iMageFile], [iMageSize]) VALUES ('iMageList\', 'jpnGirl_001.jpg', 56778)
INSERT INTO iMageList ([iMagePath], [iMageFile], [iMageSize]) VALUES ('iMageList\', 'jpnGirl_002.jpg', 123445)
INSERT INTO iMageList ([iMagePath], [iMageFile], [iMageSize]) VALUES ('iMageList\', 'jpnGirl_003.jpg', 179880)
INSERT INTO iMageList ([iMagePath], [iMageFile], [iMageSize]) VALUES ('iMageList\', 'jpnGirl_004.jpg', 98878)
INSERT INTO iMageList ([iMagePath], [iMageFile], [iMageSize]) VALUES ('iMageList\', 'jpnGirl_005.jpg', 105567)
INSERT INTO iMageList ([iMagePath], [iMageFile], [iMageSize]) VALUES ('iMageList\', 'jpnGirl_006.jpg', 58890)
INSERT INTO iMageList ([iMagePath], [iMageFile], [iMageSize]) VALUES ('iMageList\', 'jpnGirl_007.jpg', 71103)
----------------------------------------------------------------