Hi,

I'm writing a small app. that needs to write some string and binary data to
an access database with ado.net.

At this point i'm using oledb to make the connection. After that I'm
composing the SQL query  as a string. This works fine for the string data but
won't work for the binary field.

Q: What is the best approach for writing data to an access database that
need to write binary data?

Thanks,
Edwin

Hi there,

Please find code snippet below. The table structure for below example is as
follow:
FileId (Autonumber), Primary Key
FileName(Text(255))
FileData(OLE Object)

-- BEGIN CODE --
private const string ConnectionString =
    "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=c:\\TEMP\\Images.mdb;";

private void InsertFile(string strFilename)
{
    System.IO.FileStream stream = System.IO.File.OpenRead(strFilename);

    string query = "INSERT INTO Files (FileName, FileData) VALUES (?, ?)";

    byte[] buffer = new byte[stream.Length];
    stream.Read(buffer, 0, (int) stream.Length);
    stream.Close();

    OleDbConnection connection = new OleDbConnection(ConnectionString);
    OleDbCommand command = new OleDbCommand(query, connection);
    OleDbParameter parameter = null;

    command.CommandType = CommandType.Text;
    command.CommandTimeout = 120;

    // FileName
    parameter = new OleDbParameter("?", OleDbType.VarChar);
    parameter.Direction = ParameterDirection.Input;
    parameter.Value = System.IO.Path.GetFileName(strFilename);
    command.Parameters.Add(parameter);

    // FileData
    parameter = new OleDbParameter("?", OleDbType.VarBinary);
    parameter.Direction = ParameterDirection.Input;
    parameter.Value = buffer;
    command.Parameters.Add(parameter);

    try
    {
        connection.Open();
        command.ExecuteNonQuery();
    }
    catch (Exception ex)
    {
        throw ex;
    }
    finally
    {
        if (connection.State != ConnectionState.Closed)
            connection.Close();
    }
}

private const int Timeout = 120;

private void SaveFile(int id, string path)
{
    string query = "SELECT [FileData], [FileName] FROM [Files] WHERE [FileId] =
?";

    OleDbConnection connection = new OleDbConnection(ConnectionString);
    OleDbCommand command = new OleDbCommand(query, connection);
    OleDbDataReader reader = null;

    OleDbParameter parameter = new OleDbParameter("?", OleDbType.Integer);
    parameter.Direction = ParameterDirection.Input;
    parameter.Value = id;
    command.Parameters.Add(parameter);
    command.CommandTimeout = Timeout;

    try
    {
        connection.Open();
        reader = command.ExecuteReader(CommandBehavior.CloseConnection);

        if (reader.Read())
        {
            System.IO.FileStream stream =
                System.IO.File.Create(System.IO.Path.Combine(path, (string)
reader["FileName"]));

            byte[] buffer = (byte[]) reader["FileData"];

            stream.Write(buffer, 0, (int) buffer.Length);
            stream.Flush();
            stream.Close();
        }
    }
    catch (Exception ex)
    {
        throw ex;
    }
    finally
    {
        if (connection.State != ConnectionState.Closed) 
            connection.Close();
    }
}
-- END CODE --

Hope this helps

Show quoteHide quote

Update Command Question
TableAdapter's TableMappings?
Basic question about datasets
Images inline with blog contents - HOW??
db errors