Access database ADO.NET and Binary fields

[Guest]

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

 

[Guest]

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