How to store a file in sql server from winforms c++

Question

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
}

Answer 1

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.