MY QUESTION: how do I store a file in a sql server using c++ from a windows form application?
MY PROBLEM: I am reading in binary data from a file then opening a connection to a SQL Server 2014. When I send my sql command to the database with the binary data, I get an error message "Failed to convert parameter value from a Byte to a Byte[].". I see many people using this method in C# applications. How do I adapt this for c++?
MY CODE:
void AddFileToDatabase() {
unsigned char* binaryData; // holds the files binary data
int sizeOfBinaryData = 0; // the size of binaryData in bytes
// Open the test file in binary mode and read the contents into 'binaryData'
std::ifstream inFile("a.txt", std::ios::in | std::ios::binary); // open the test file 'a.txt'
if(inFile.is_open()) {
inFile.seekg(0,std::ios::end); // move to the end of the file
sizeOfBinaryData = (int)inFile.tellg(); // get the file size
inFile.seekg(0,std::ios::beg); // move to the start of the file
binaryData = new unsigned char[sizeOfBinaryData]; // create space for the binary data
inFile.read((char*)binaryData,sizeOfBinaryData); // read in the binary data
inFile.close(); // close the file
}
// Connect to the database
System::Data::SqlClient::SqlConnection^ Connection = gcnew System::Data::SqlClient::SqlConnection("server=MYserver,MYport;Integrated security=SSPI;Database=MYdatabase;"); // create a connection to the database
Connection->Open(); // open the connection to the database
// Setup the sql command
System::Data::SqlClient::SqlCommand^ Command = gcnew System::Data::SqlClient::SqlCommand("INSERT INTO MYtable VALUES(@binaryValue);", Connection); // create the sql command (the column type in MYtable is 'varbinary(MAX)')
System::Byte^ data = gcnew System::Byte(reinterpret_cast<unsigned char>(binaryData)); // store the binary data in a System::Byte type so the sql Command will accept it as a parameter
Command->Parameters->Add("@binaryValue", System::Data::SqlDbType::VarBinary, sizeOfBinaryData)->Value = data; // add the binary data to the sql command
// Attempt to insert the binary data into the database
try {
Command->ExecuteNonQuery(); // insert binay data into database
System::Windows::Forms::MessageBox::Show("IT WORKED!!!","Success", System::Windows::Forms::MessageBoxButtons::OK,System::Windows::Forms::MessageBoxIcon::Information); // tell us if we are big winners
}
catch(System::Exception^ ex) {
System::Windows::Forms::MessageBox::Show(ex->Message,"Error", System::Windows::Forms::MessageBoxButtons::OK,System::Windows::Forms::MessageBoxIcon::Error); // tell us why we failed
}
delete[] binaryData; // clean up
}
MY SOLUTION: The solution here is to change 'data' from type 'System::Byte^' to 'array^'
SOLUTION CODE:
void AddFileToDatabase() {
// Open the test file in binary mode and read the contents into 'binaryData'
unsigned char* binaryData; // holds the files binary data
int sizeOfBinaryData = 0; // the size of stream in bytes
FILE* in; fopen_s(&in,"a.txt","rb"); // open the file in binary mode
fseek(in,0,SEEK_END); // move to the end of the file
sizeOfBinaryData = ftell(in); // get the file size
fseek(in,0,SEEK_SET); // move to the start of the file
binaryData = new unsigned char[sizeOfBinaryData]; // create space for the binary data
fread(binaryData,sizeof(unsigned char),sizeOfBinaryData,in); // read in the binary data
fclose(in); // close the file
// Connect to the database
System::Data::SqlClient::SqlConnection^ Connection = gcnew System::Data::SqlClient::SqlConnection("server=MYserver,MYport;Integrated security=SSPI;Database=MYdatabase;"); // create a connection to the database
Connection->Open(); // open the connection to the database
// Setup the sql command
System::Data::SqlClient::SqlCommand^ Command = gcnew System::Data::SqlClient::SqlCommand("INSERT INTO Files(BYTEcolumn,SIZEcolumn) VALUES(@binaryValue,'" + sizeOfBinaryData + "');", Connection); // create the sql command
array<System::Byte>^ data = gcnew array<System::Byte>(sizeOfBinaryData); // create a Byte array
for(int i = 0; i < sizeOfBinaryData; i++) {
data[i] = binaryData[i]; // store the binary data in a System::Byte array type so the sql Command will accept it as a parameter
}
Command->Parameters->Add("@binaryValue", System::Data::SqlDbType::VarBinary, sizeOfBinaryData)->SqlValue = data; // add the binary data to the sql command
// Attempt to insert the binary data into the database
try {
Command->ExecuteNonQuery(); // insert binay data into database
System::Windows::Forms::MessageBox::Show("IT WORKED!!!","Success", System::Windows::Forms::MessageBoxButtons::OK,System::Windows::Forms::MessageBoxIcon::Information); // tell us if we are big winners
}
catch(System::Exception^ ex) {
System::Windows::Forms::MessageBox::Show(ex->Message,"Error", System::Windows::Forms::MessageBoxButtons::OK,System::Windows::Forms::MessageBoxIcon::Error); // tell us why we failed
}
delete[] binaryData; // clean up
}
and here is how to retrieve the file from the database.
void GetFileFromDatabase() {
// Connect to the database
System::Data::SqlClient::SqlConnection^ Connection = gcnew System::Data::SqlClient::SqlConnection("server=MYserver,MYport;Integrated security=SSPI;Database=MYdatabase;"); // create a connection to the database
Connection->Open(); // open the connection to the database
// Setup the sql command
System::Data::SqlClient::SqlCommand^ Command = gcnew System::Data::SqlClient::SqlCommand("SELECT SIZEcolumn,BYTEcolumn FROM Files WHERE IDcolumn = MYid;", Connection); // create the sql command
// try to get the file from the database
System::Data::SqlClient::SqlDataReader^ Reader; // setup a sql data reader object to capture the data from the database
try {
Reader = Command->ExecuteReader(); // send the request to the database and put the result in the sql data reader
if(Reader) { // if the sql data reader has been set
if(!Reader->IsClosed) { // if the sql data reader is not closed
if(Reader->HasRows) { // if there is data in the sql data reader
int sizeOfBinaryData = 0; // storage for the size of the file
while(Reader->Read()) { // loop through the data
for(int i = 0;i < Reader->FieldCount; i++) { // cycle through the Size and Bytes fields of the data
if(!Reader->IsDBNull(i)) { // if the current data field is not emplty
if(Reader->GetFieldType(i)->ToString() == "System.Int32") { // if the current field is the size of the next file
sizeOfBinaryData = Reader->GetInt32(i); // get the size of the next file
}else if(Reader->GetFieldType(i)->ToString() == "System.Byte[]") { // if the current field is the byte data
if(sizeOfBinaryData) { // if the size of the byte data is more than 0
array<System::Byte>^ data = gcnew array<System::Byte>(sizeOfBinaryData); // create an Byte array to store the data
Reader->GetBytes(i,0,data,0,sizeOfBinaryData); // read the byte data into the Byte array
unsigned char* binaryData = new unsigned char[sizeOfBinaryData]; // create an unsigned char array so fwrite() will accept the array type
for(int i = 0; i < sizeOfBinaryData; i++) {
binaryData[i] = data[i]; // copy the byte data to the unsigned char array
}
// create a new file from the byte data
FILE* out; fopen_s(&out,"b.txt","wb"); // open the file where you want to store the data
fwrite(binaryData,sizeof(unsigned char),sizeOfBinaryData,out); // write the data to the new file
fclose(out); // close the file
delete[] binaryData; // clean up
}
}
}
}
}
System::Windows::Forms::MessageBox::Show("You got a new file. Good for you!","Success", System::Windows::Forms::MessageBoxButtons::OK,System::Windows::Forms::MessageBoxIcon::Error); // SUCCESS message
}
}
Reader->Close(); // close the sql data reader
}
}catch(System::Exception^ ex) {
System::Windows::Forms::MessageBox::Show(ex->Message,"Error", System::Windows::Forms::MessageBoxButtons::OK,System::Windows::Forms::MessageBoxIcon::Error); // give us error data
}
}
Thanks everyone for your interest.