网上经常有人问如何把图像存入数据库中，原先我也是不得要领。经过多方指点和自己在开发过程中的摸索，终于解决这一问题。
下面给出用VC，VB如何操作图像文件存取数据库的原码，帮助一些还没有掌握方法的朋友，也请这方面的高手多多指教。（均用ADO连接数据库）
1. VC把一个文件存入数据库
CFile imagefile; if(0 == imagefile.Open("d://user//bmp.bmp",CFile::modeRead)) return; _RecordsetPtr pRs = NULL; _ConnectionPtr pConnection = NULL; _variant_t varChunk; HRESULT hr; BYTE* pbuf; long nLength = imagefile.GetLength(); pbuf = new BYTE[nLength+2]; if(pbuf == NULL) return; //allocate memory error; imagefile.Read(pbuf,nLength); //read the file into memory
BYTE *pBufEx; pBufEx = pbuf; //build a SAFFERRAY SAFEARRAY* psa; SAFEARRAYBOUND rgsabound[1]; rgsabound[0].lLbound = 0; rgsabound[0].cElements = nLength; psa = SafeArrayCreate(VT_UI1, 1, rgsabound);
for (long i = 0; i < nLength; i++) SafeArrayPutElement (psa, &i, pBufEx++); VARIANT varBLOB; varBLOB.vt = VT_ARRAY | VT_UI1; varBLOB.parray = psa;
_bstr_t strCnn("Provider=SQLOLEDB.1;Integrated Security=SSPI;Persist Security Info=False;Initial Catalog=CUSTOM;Data Source=SERVER"); try { //Open a connection pConnection.CreateInstance(__uuidof(Connection)); hr = pConnection->Open(strCnn,"","",NULL); //Connect a DataBase pRs.CreateInstance(__uuidof(Recordset)); pRs->Open("CustomInfo",_variant_t((IDispatch *) pConnection,true),adOpenKeyset,adLockOptimistic,adCmdTable); //Open a Table // pRs->AddNew(); pRs->Fields->GetItem("Image")->AppendChunk(varBLOB); pRs->Update(); pRs->Close(); pConnection->Close(); } catch(_com_error &e) { // Notify the user of errors if any. _bstr_t bstrSource(e.Source()); _bstr_t bstrDescription(e.Description()); CString sError; sError.Format("Source : %s /n Description : %s/n",(LPCSTR)bstrSource,(LPCSTR)bstrDescription); AfxMessageBox(sError); }
2. VC把数据库中IMAGE字段取出存为文件
_RecordsetPtr pRs = NULL; _ConnectionPtr pConnection = NULL; _variant_t varChunk; HRESULT hr; VARIANT varBLOB; _bstr_t strCnn("Provider=SQLOLEDB.1;Integrated Security=SSPI;Persist Security Info=False;Initial Catalog=CUSTOM;Data Source=SERVER"); try { //Open a connection pConnection.CreateInstance(__uuidof(Connection)); hr = pConnection->Open(strCnn,"","",NULL); pRs.CreateInstance(__uuidof(Recordset)); pRs->Open("CustomInfo",_variant_t((IDispatch *) pConnection,true),adOpenKeyset,adLockOptimistic,adCmdTable); //read data long lDataLength = pRs->Fields->GetItem("Image")->ActualSize; varBLOB = pRs->GetFields()->GetItem("Image")->GetChunk(lDataLength); if(varBLOB.vt == (VT_ARRAY | VT_UI1)) { BYTE *pBuf = NULL; pBuf = (BYTE*)GlobalAlloc(GMEM_FIXED,lDataLength); SafeArrayAccessData(varBLOB.parray,(void **)pBuf); //Build a File in Windows Temp Directory char tmpPath[_MAX_PATH+1]; GetTempPath(_MAX_PATH,tmpPath); CString strFileName = "temp.bmp"; strFileName = tmpPath+strFileName; CFile outFile(strFileName,CFile::modeCreate|CFile::modeWrite); LPSTR buffer = (LPSTR)GlobalLock((HGLOBAL)pBuf); outFile.WriteHuge(buffer,lDataLength); GlobalUnlock((HGLOBAL)pBuf); outFile.Close(); SafeArrayUnaccessData (varBLOB.parray); }
pRs->Close(); pConnection->Close(); } catch(_com_error &e) { // Notify the user of errors if any. _bstr_t bstrSource(e.Source()); _bstr_t bstrDescription(e.Description()); CString sError; sError.Format("Source : %s /n Description : %s/n",(LPCSTR)bstrSource,(LPCSTR)bstrDescription); AfxMessageBox(sError); }
3. VB把文件存入数据库IMAGE字段
Sub savepic(FileName As String, IndexNumber As Long) Dim DcnNWind As New ADODB.Connection Dim rs As ADODB.Recordset Set rs = New ADODB.Recordset DcnNWind.CursorLocation = adUseClient DcnNWind.Open "Provider=SQLOLEDB.1;Integrated Security=SSPI;Persist Security Info=False;Initial Catalog=CUSTOM;Data Source=SERVER" rs.CursorType = adOpenKeyset rs.LockType = adLockOptimistic rs.Open "CustomInfo", DcnNWind, , adCmdTable rs.Move (IndexNumber) Call FileToBlob(rs.Fields("Image"), FileName, FileLen(FileName)) rs.UpdateBatch adAffectCurrentEnd Sub
Private Sub FileToBlob(fld As ADODB.Field, FileName As String, Optional ChunkSize As Long ) Dim fnum As Integer, bytesLeft As Long, bytes As Long Dim tmp() As Byte If (fld.Attributes And adFldLong) = 0 Then Err.Raise 1001, , "Field doesn't support the GetChunk method." End If fnum = FreeFile Open FileName For Binary As fnum bytesLeft = LOF(fnum) Do While bytesLeft bytes = bytesLeft If bytes > ChunkSize Then bytes = ChunkSize ReDim tmp(1 To bytes) As Byte Get #1, , tmp fld.AppendChunk tmp bytesLeft = bytesLeft - bytes Loop Close #fnumEnd Sub
4. VB把文件从IMAGE字段中读到文件中。
Sub loadpic(IndexNumber As Long) Dim DcnNWind As New ADODB.Connection Dim rs As ADODB.Recordset Set rs = New ADODB.Recordset DcnNWind.CursorLocation = adUseClient DcnNWind.Open "Provider=SQLOLEDB.1;Integrated Security=SSI;Persist Security Info=False;Initial Catalog=CUSTOM;Data Source=SERVER" rs.CursorType = adOpenKeyset rs.LockType = adLockOptimistic rs.Open "CustomInfo", DcnNWind, , adCmdTable rs.Move (IndexNumber) Call BlobToFile(rs.Fields("Image"), "c:/windows/temp/tmp.bmp", rs.Fields("Image").ActualSize)End Sub
Private Sub BlobToFile(fld As ADODB.Field, FileName As String, Optional ChunkSize As Long ) Dim fnum As Integer, bytesLeft As Long, bytes As Long Dim tmp() As Byte If (fld.Attributes And adFldLong) = 0 Then Err.Raise 1001, , "Field doesn't support the GetChunk method." End If If Dir$(FileName) <> "" Then Kill FileName fnum = FreeFile Open FileName For Binary As fnum bytesLeft = fld.ActualSize Do While bytesLeft bytes = bytesLeft If bytes > ChunkSize Then bytes = ChunkSize tmp = fld.GetChunk(bytes) Put #fnum, , tmp bytesLeft = bytesLeft - bytes Loop Close #fnumEnd Sub
