Trouble trying to insert Excel file as BLOB into SQL2000 database

[Guest]

I'm having trouble programatically inserting an Excel file into an Image column in our CRM package's SQL 2000 database. The function appears to work ok, but when I attempt to access the file through the application's front end the file appears to be corrupt. The front-end application has a way of inserting files to the column, when I analyze this in SQL Profiler the contents of the Byte array appear to be quite different than the one my code is generating. I'm not sure if this is an ADO.NET issue or a problem with the way I'm parsing the XLS file into the Byte array.

I've been banging my head on this one for a couple days now, any help would be much appreciated

Thanks

Andr


public static void AddFile(string sAccountKey

tr

FileStream fs = new FileStream(sFilePath, FileMode.Open, FileAccess.Read)
byte[] file = new byte[fs.Length]
fs.Read(file, 0, file.Length)
fs.Close();
string sDescription = "900 Special Billing Invoice " + Menu.datEndDate.ToShortDateString()
SqlConnection cn = new SqlConnection(Menu.sConnect)
SqlCommand cmd = cn.CreateCommand()
cmd = cn.CreateCommand()
cn.Open()
cmd.CommandText = sSQL
cmd.Parameters.Add("@AccountKey", SqlDbType.Int, 4)
cmd.Parameters["@AccountKey"].Value = myReportDS.Account[0].AccountKey
cmd.Parameters.Add("@Description", SqlDbType.VarChar, 50).Value = sDescription
cmd.Parameters.Add("@ObjectType", SqlDbType.Image, file.Length).Value = file
string sSQL = "INSERT INTO tblSupportAccountsOLEObjects (AccountKey, Description, ObjectType) "
"VALUES (@AccountKey, @Description, @ObjectType);"
cmd.CommandText = sSQL
cmd.CommandTimeout = 0
cmd.ExecuteNonQuery()
cn.Close();

catch (Exception ex

MessageBox.Show("Exception at Print900.AddFile(string sAccountKey)\r\n" + ex.Source + "\r\n" + ex.Message + "\r\n" + ex.StackTrace, "An error has occured.", MessageBoxButtons.OK, MessageBoxIcon.Error)
return

}

 

[Val Mazur]

Hi Andre,

I was storing image files in a SQLS erver databsse and it is workinmg OK. It
should work exact same way with the Excel files, because XLS is just a
different type of the binary. Following is a code, which works for sure with
the images. Try to use it with Excel to see if it helps


'Insering BLOB data into the database

Dim loConnection As SqlClient.SqlConnection
Dim loCommand As SqlClient.SqlCommand
Dim loProductImage As SqlClient.SqlParameter
Dim loImageValue() As Byte
Dim loFile As IO.FileStream
Dim lnByteCount As Integer

'Instantiate new connection object
loConnection = New SqlClient.SqlConnection()

With loConnection
'Prepare connection string and open connection to database
.ConnectionString = "Data Source='ExampleServer';" & _
"Initial Catalog=Examples;Integrated Security=SSPI"

.Open()

'Open file with product image as a stream
loFile = New System.IO.FileStream("C:\Test\Product1.jpg", _
IO.FileMode.Open)

'Get length of stream (lengt of file) in bytes
lnByteCount = loFile.Length()

'Reallocate storage space for an array variable to the
'size of image file
ReDim loImageValue(lnByteCount - 1)

'Load stream into array of bytes.
'lnByteCount will get real number of bytes which
'were retrieved from stream
lnByteCount = loFile.Read(loImageValue, 0, lnByteCount)

'Close stream
loFile.Close()
loFile = Nothing

'Instantiate new command object which we will
'use to insert image into table
loCommand = New SqlClient.SqlCommand()
loCommand.CommandType = CommandType.Text
loCommand.CommandText = "INSERT INTO tblProductImages " & _
"(Product_Image) VALUES (@ProductImage)"
loCommand.Connection = loConnection

'Add parameter, which will accept image value,
'to command object
loProductImage = loCommand.Parameters.Add("@ProductImage ", _
Data.SqlDbType.Image)

'Set type of parameter to Image
loProductImage.SqlDbType = SqlDbType.Image

'Load parameter with actual data from array of bytes
loProductImage.Value = loImageValue

'Execute prepared query to provide actual inserting of
'prepared data
loCommand.ExecuteNonQuery()
loCommand.Dispose()
loCommand = Nothing

.Close()
.Dispose()
End With

loConnection = Nothing


------------------------------------------------------------------------------------------------------------------------------------------
'Loading image from table with image data type of column

Dim loConnection As SqlClient.SqlConnection
Dim loCommand As SqlClient.SqlCommand
Dim loDataReader As SqlClient.SqlDataReader
Dim loBuffer() As Byte
Dim lnRealLength As Long
Dim loStream As System.IO.MemoryStream

'Instantiate new connection object
loConnection = New SqlClient.SqlConnection()

'Prepare connection string and open connection to database
With loConnection
.ConnectionString = "Data Source='ExampleServer';" & _
"Initial Catalog=Examples;Integrated Security=SSPI"

.Open()

'instantiate new command object which
'we will use to read image from table
loCommand = New SqlClient.SqlCommand()
loCommand.CommandText = "SELECT Product_Image " & _
"FROM tblProductImages WHERE Product_ID=1"
loCommand.CommandType = CommandType.Text
loCommand.Connection = loConnection

'Execute prepared command to provide actual
'reading of previously saved binary data
loDataReader = _
loCommand.ExecuteReader( _
CommandBehavior.SequentialAccess)

'instantiate new memory stream to load
'data from binary field
loStream = New System.IO.MemoryStream()

'read actual value from field
Do While loDataReader.Read()
'Get actual length of data in bytes
lnRealLength = loDataReader.GetBytes( _
0, 0, Nothing, 0, Integer.MaxValue)

'Reallocate storage space for an array variable to the
'size of data
ReDim loBuffer(lnRealLength)

'Load data from field into array
lnRealLength = loDataReader.GetBytes( _
0, 0, loBuffer, 0, lnRealLength)

'Load array into prepared stream to allow
'to show it in PictureBox control
loStream.Write(loBuffer, 0, lnRealLength)

'Load image into PictureBox control
PictureBox1.Image = Image.FromStream(loStream)
Loop

'Close all opened resources and objects
loStream.Close()
loStream = Nothing
loDataReader.Close()
.Close()
loCommand.Dispose()
loCommand = Nothing
loConnection.Dispose()
loConnection = Nothing
End With


--
Val Mazur
Microsoft MVP

I'm having trouble programatically inserting an Excel file into an Image
column in our CRM package's SQL 2000 database. The function appears to
work ok, but when I attempt to access the file through the application's
front end the file appears to be corrupt. The front-end application has a
way of inserting files to the column, when I analyze this in SQL Profiler
the contents of the Byte array appear to be quite different than the one
my code is generating. I'm not sure if this is an ADO.NET issue or a
problem with the way I'm parsing the XLS file into the Byte array.

I've been banging my head on this one for a couple days now, any help
would be much appreciated.

Thanks,

Andre



public static void AddFile(string sAccountKey)
{
try
{
FileStream fs = new FileStream(sFilePath, FileMode.Open, FileAccess.Read);
byte[] file = new byte[fs.Length];
fs.Read(file, 0, file.Length);
fs.Close();
string sDescription = "900 Special Billing Invoice " +
Menu.datEndDate.ToShortDateString();
SqlConnection cn = new SqlConnection(Menu.sConnect);
SqlCommand cmd = cn.CreateCommand();
cmd = cn.CreateCommand();
cn.Open();
cmd.CommandText = sSQL;
cmd.Parameters.Add("@AccountKey", SqlDbType.Int, 4);
cmd.Parameters["@AccountKey"].Value = myReportDS.Account[0].AccountKey;
cmd.Parameters.Add("@Description", SqlDbType.VarChar, 50).Value =
sDescription;
cmd.Parameters.Add("@ObjectType", SqlDbType.Image, file.Length).Value =
file;
string sSQL = "INSERT INTO tblSupportAccountsOLEObjects (AccountKey,
Description, ObjectType) " +
"VALUES (@AccountKey, @Description, @ObjectType);";
cmd.CommandText = sSQL;
cmd.CommandTimeout = 0;
cmd.ExecuteNonQuery();
cn.Close();
}
catch (Exception ex)
{
MessageBox.Show("Exception at Print900.AddFile(string sAccountKey)\r\n" +
ex.Source + "\r\n" + ex.Message + "\r\n" + ex.StackTrace, "An error has
occured.", MessageBoxButtons.OK, MessageBoxIcon.Error);
return;
}
}

 

[Guest]

Val

Thanks for taking the time to help me

I've run some tests on the code I posted. It does look like it's working, I am able to run a SELECT query on the inserted data and extract the binary to a file name of my choosing. Therefore, my conclusion is that there's something about the format which the front-end CRM software isn't liking

One thing I noticed while following the code execution in SQL Profiler is that the binary I'm inserting into the database programmatically using the code snippet looks very different in Profiler than if I add a file manually using our front-end CRM application. Also, upon further investigation I noticed that there is no column in the SQL table for the binary's data type / file extension. Yet, somehow the CRM front end can tell if it's an XLS, DOC, XML, etc file type and displays the appropriate icon. Is it possible to embed the document's file type into the binary? When I do it in using the code posted yesterday, I have to know that it's an Excel file and save it with an .XLS extension

Thanks again for any feedback you might have

Andre

 

[Val Mazur]

Hi,

I do not know how CRM works, but it is possible that it stores some sort of
header, which defines type of the file. From my experience, you could get
type of the file from the header of the binary file itself. For example
Excel and Word documents are stored as OLE objects, where there is a header,
which describes type of the document, size etc.

 

[Guest]

Hi Val,
I have an asp.net application that stores excel files as blobs in a sql database.
I don't have any problem storing the file as blob, but I have been getting error messages when I try to open the saved excel file from the database trhough the front end. The error is like like this - filename.xls cannot be acessed. The file may be ready only or the server may not be responding and so on... The filename is not even the name of the file that I am trying to open and it changes every time. I keep clicking cancel several times befoer it finally shows the excel file on the browser. I don't have problem with any other files types like doc or txt or jpeg. It is just excel which is giving me these errors. Any help would really be appreicated.
thanks
Selva

Hi Andre,

I was storing image files in a SQLS erver databsse and it is workinmg OK. It
should work exact same way with the Excel files, because XLS is just a
different type of the binary. Following is a code, which works for sure with
the images. Try to use it with Excel to see if it helps


'Insering BLOB data into the database

Dim loConnection As SqlClient.SqlConnection
Dim loCommand As SqlClient.SqlCommand
Dim loProductImage As SqlClient.SqlParameter
Dim loImageValue() As Byte
Dim loFile As IO.FileStream
Dim lnByteCount As Integer

'Instantiate new connection object
loConnection = New SqlClient.SqlConnection()

With loConnection
'Prepare connection string and open connection to database
.ConnectionString = "Data Source='ExampleServer';" & _
"Initial Catalog=Examples;Integrated Security=SSPI"

.Open()

'Open file with product image as a stream
loFile = New System.IO.FileStream("C:\Test\Product1.jpg", _
IO.FileMode.Open)

'Get length of stream (lengt of file) in bytes
lnByteCount = loFile.Length()

'Reallocate storage space for an array variable to the
'size of image file
ReDim loImageValue(lnByteCount - 1)

'Load stream into array of bytes.
'lnByteCount will get real number of bytes which
'were retrieved from stream
lnByteCount = loFile.Read(loImageValue, 0, lnByteCount)

'Close stream
loFile.Close()
loFile = Nothing

'Instantiate new command object which we will
'use to insert image into table
loCommand = New SqlClient.SqlCommand()
loCommand.CommandType = CommandType.Text
loCommand.CommandText = "INSERT INTO tblProductImages " & _
"(Product_Image) VALUES (@ProductImage)"
loCommand.Connection = loConnection

'Add parameter, which will accept image value,
'to command object
loProductImage = loCommand.Parameters.Add("@ProductImage ", _
Data.SqlDbType.Image)

'Set type of parameter to Image
loProductImage.SqlDbType = SqlDbType.Image

'Load parameter with actual data from array of bytes
loProductImage.Value = loImageValue

'Execute prepared query to provide actual inserting of
'prepared data
loCommand.ExecuteNonQuery()
loCommand.Dispose()
loCommand = Nothing

.Close()
.Dispose()
End With

loConnection = Nothing


------------------------------------------------------------------------------------------------------------------------------------------
'Loading image from table with image data type of column

Dim loConnection As SqlClient.SqlConnection
Dim loCommand As SqlClient.SqlCommand
Dim loDataReader As SqlClient.SqlDataReader
Dim loBuffer() As Byte
Dim lnRealLength As Long
Dim loStream As System.IO.MemoryStream

'Instantiate new connection object
loConnection = New SqlClient.SqlConnection()

'Prepare connection string and open connection to database
With loConnection
.ConnectionString = "Data Source='ExampleServer';" & _
"Initial Catalog=Examples;Integrated Security=SSPI"

.Open()

'instantiate new command object which
'we will use to read image from table
loCommand = New SqlClient.SqlCommand()
loCommand.CommandText = "SELECT Product_Image " & _
"FROM tblProductImages WHERE Product_ID=1"
loCommand.CommandType = CommandType.Text
loCommand.Connection = loConnection

'Execute prepared command to provide actual
'reading of previously saved binary data
loDataReader = _
loCommand.ExecuteReader( _
CommandBehavior.SequentialAccess)

'instantiate new memory stream to load
'data from binary field
loStream = New System.IO.MemoryStream()

'read actual value from field
Do While loDataReader.Read()
'Get actual length of data in bytes
lnRealLength = loDataReader.GetBytes( _
0, 0, Nothing, 0, Integer.MaxValue)

'Reallocate storage space for an array variable to the
'size of data
ReDim loBuffer(lnRealLength)

'Load data from field into array
lnRealLength = loDataReader.GetBytes( _
0, 0, loBuffer, 0, lnRealLength)

'Load array into prepared stream to allow
'to show it in PictureBox control
loStream.Write(loBuffer, 0, lnRealLength)

'Load image into PictureBox control
PictureBox1.Image = Image.FromStream(loStream)
Loop

'Close all opened resources and objects
loStream.Close()
loStream = Nothing
loDataReader.Close()
.Close()
loCommand.Dispose()
loCommand = Nothing
loConnection.Dispose()
loConnection = Nothing
End With


--
Val Mazur
Microsoft MVP

I'm having trouble programatically inserting an Excel file into an Image
column in our CRM package's SQL 2000 database. The function appears to
work ok, but when I attempt to access the file through the application's
front end the file appears to be corrupt. The front-end application has a
way of inserting files to the column, when I analyze this in SQL Profiler
the contents of the Byte array appear to be quite different than the one
my code is generating. I'm not sure if this is an ADO.NET issue or a
problem with the way I'm parsing the XLS file into the Byte array.

I've been banging my head on this one for a couple days now, any help
would be much appreciated.

Thanks,

Andre



public static void AddFile(string sAccountKey)
{
try
{
FileStream fs = new FileStream(sFilePath, FileMode.Open, FileAccess.Read);
byte[] file = new byte[fs.Length];
fs.Read(file, 0, file.Length);
fs.Close();
string sDescription = "900 Special Billing Invoice " +
Menu.datEndDate.ToShortDateString();
SqlConnection cn = new SqlConnection(Menu.sConnect);
SqlCommand cmd = cn.CreateCommand();
cmd = cn.CreateCommand();
cn.Open();
cmd.CommandText = sSQL;
cmd.Parameters.Add("@AccountKey", SqlDbType.Int, 4);
cmd.Parameters["@AccountKey"].Value = myReportDS.Account[0].AccountKey;
cmd.Parameters.Add("@Description", SqlDbType.VarChar, 50).Value =
sDescription;
cmd.Parameters.Add("@ObjectType", SqlDbType.Image, file.Length).Value =
file;
string sSQL = "INSERT INTO tblSupportAccountsOLEObjects (AccountKey,
Description, ObjectType) " +
"VALUES (@AccountKey, @Description, @ObjectType);";
cmd.CommandText = sSQL;
cmd.CommandTimeout = 0;
cmd.ExecuteNonQuery();
cn.Close();
}
catch (Exception ex)
{
MessageBox.Show("Exception at Print900.AddFile(string sAccountKey)\r\n" +
ex.Source + "\r\n" + ex.Message + "\r\n" + ex.StackTrace, "An error has
occured.", MessageBoxButtons.OK, MessageBoxIcon.Error);
return;
}
}