SqlDataReader.GetBytes Results in an System.InvalidCastException E

  • Thread starter Thread starter Hotwheels
  • Start date Start date
H

Hotwheels

Trying to write a routine to take bytes from a text field and put them into a
file.

I am using VS 2003 and SQL 2005. I created a table called TestText and
added two fields ID as a UID and MyText of type Text.

I then added one row with the ID 8ac11006-26ad-da11-9ec8-00059a3c7800 and
MyText value of "1234".

If I put dr.GetValue(0) in the watch area it show the correct result of
"1234".

So the DataReader is working correct.

What am I doing wrong with SqlDataReader.GetBytes Method?

Also how would I wirte the command if I just wanted return the length of bytes
example maybe "long retval = dr.GetBytes(0, 0, null, 0, 0);"

Thanks
Mike

private void SqlBlob2File(string DestFilePath, string id)
{

SqlConnection cn = new SqlConnection("server=localhost;integrated
security=yes;database=RelevantSales_MSCRM");
SqlCommand cmd = new SqlCommand("SELECT MyText FROM TestText WHERE ID =
'8ac11006-26ad-da11-9ec8-00059a3c7800'", cn);
cn.Open();
SqlDataReader dr = cmd.ExecuteReader();
bool bResult = dr.Read();
FileStream fs = new FileStream(DestFilePath, System.IO.FileMode.Create,
System.IO.FileAccess.Write);
try
{
byte[] value = new byte[8];
long retval = dr.GetBytes(0, 0, value, 0, value.Length);
fs.Write(value, 0, value.Length);

dr.Close();
cn.Close();

fs.Close();
}
catch (Exception ex)
{
MessageBox.Show(ex.Message);
dr.Close();
cn.Close();

fs.Close();
}
}
 
Hello Hotwheels,
Trying to write a routine to take bytes from a text field and put them
into a file.

I am using VS 2003 and SQL 2005. I created a table called TestText
and added two fields ID as a UID and MyText of type Text.

I then added one row with the ID 8ac11006-26ad-da11-9ec8-00059a3c7800
and MyText value of "1234".

If I put dr.GetValue(0) in the watch area it show the correct result
of "1234".

So the DataReader is working correct.

What am I doing wrong with SqlDataReader.GetBytes Method?

Also how would I wirte the command if I just wanted return the length
of bytes example maybe "long retval = dr.GetBytes(0, 0, null, 0, 0);"

Thanks
Mike
private void SqlBlob2File(string DestFilePath, string id)
{
SqlConnection cn = new SqlConnection("server=localhost;integrated
security=yes;database=RelevantSales_MSCRM");
SqlCommand cmd = new SqlCommand("SELECT MyText FROM TestText WHERE
ID =
'8ac11006-26ad-da11-9ec8-00059a3c7800'", cn);
cn.Open();
SqlDataReader dr = cmd.ExecuteReader();
bool bResult = dr.Read();
FileStream fs = new FileStream(DestFilePath,
System.IO.FileMode.Create,
System.IO.FileAccess.Write);
try
{
byte[] value = new byte[8];
long retval = dr.GetBytes(0, 0, value, 0, value.Length);
fs.Write(value, 0, value.Length);
dr.Close();
cn.Close();
fs.Close();
}
catch (Exception ex)
{
MessageBox.Show(ex.Message);
dr.Close();
cn.Close();
fs.Close();
}
}
Click to expand...

According to the debugger, what is the type of dr.GetValue(0)? If it's a
string, then you'd have serious trouble reading it as bytes...

An atricle doing just what you describe can be found here:
http://www.akadia.com/services/dotnet_read_write_blob.html

But it migth be that SQL is returning a big string, instead of a byte array...
 
Yup the Type of dr.GetValue(0) is string in the watch window.

Mike

Jesse Houwing said:
Hello Hotwheels,
Trying to write a routine to take bytes from a text field and put them
into a file.

I am using VS 2003 and SQL 2005. I created a table called TestText
and added two fields ID as a UID and MyText of type Text.

I then added one row with the ID 8ac11006-26ad-da11-9ec8-00059a3c7800
and MyText value of "1234".

If I put dr.GetValue(0) in the watch area it show the correct result
of "1234".

So the DataReader is working correct.

What am I doing wrong with SqlDataReader.GetBytes Method?

Also how would I wirte the command if I just wanted return the length
of bytes example maybe "long retval = dr.GetBytes(0, 0, null, 0, 0);"

Thanks
Mike
private void SqlBlob2File(string DestFilePath, string id)
{
SqlConnection cn = new SqlConnection("server=localhost;integrated
security=yes;database=RelevantSales_MSCRM");
SqlCommand cmd = new SqlCommand("SELECT MyText FROM TestText WHERE
ID =
'8ac11006-26ad-da11-9ec8-00059a3c7800'", cn);
cn.Open();
SqlDataReader dr = cmd.ExecuteReader();
bool bResult = dr.Read();
FileStream fs = new FileStream(DestFilePath,
System.IO.FileMode.Create,
System.IO.FileAccess.Write);
try
{
byte[] value = new byte[8];
long retval = dr.GetBytes(0, 0, value, 0, value.Length);
fs.Write(value, 0, value.Length);
dr.Close();
cn.Close();
fs.Close();
}
catch (Exception ex)
{
MessageBox.Show(ex.Message);
dr.Close();
cn.Close();
fs.Close();
}
}
Click to expand...

According to the debugger, what is the type of dr.GetValue(0)? If it's a
string, then you'd have serious trouble reading it as bytes...

An atricle doing just what you describe can be found here:
http://www.akadia.com/services/dotnet_read_write_blob.html

But it migth be that SQL is returning a big string, instead of a byte array...
Click to expand...
 
Hello Hotwheels,
Yup the Type of dr.GetValue(0) is string in the watch window.
Click to expand...

Then using a StreamWriter would be a better solution, or try GetChars instead
of GetBytes.

Jesse
Mike

Jesse Houwing said:
Hello Hotwheels,
Trying to write a routine to take bytes from a text field and put
them into a file.

I am using VS 2003 and SQL 2005. I created a table called TestText
and added two fields ID as a UID and MyText of type Text.

I then added one row with the ID
8ac11006-26ad-da11-9ec8-00059a3c7800 and MyText value of "1234".

If I put dr.GetValue(0) in the watch area it show the correct result
of "1234".

So the DataReader is working correct.

What am I doing wrong with SqlDataReader.GetBytes Method?

Also how would I wirte the command if I just wanted return the
length of bytes example maybe "long retval = dr.GetBytes(0, 0, null,
0, 0);"

Thanks
Mike
private void SqlBlob2File(string DestFilePath, string id)
{
SqlConnection cn = new SqlConnection("server=localhost;integrated
security=yes;database=RelevantSales_MSCRM");
SqlCommand cmd = new SqlCommand("SELECT MyText FROM TestText WHERE
ID =
'8ac11006-26ad-da11-9ec8-00059a3c7800'", cn);
cn.Open();
SqlDataReader dr = cmd.ExecuteReader();
bool bResult = dr.Read();
FileStream fs = new FileStream(DestFilePath,
System.IO.FileMode.Create,
System.IO.FileAccess.Write);
try
{
byte[] value = new byte[8];
long retval = dr.GetBytes(0, 0, value, 0, value.Length);
fs.Write(value, 0, value.Length);
dr.Close();
cn.Close();
fs.Close();
}
catch (Exception ex)
{
MessageBox.Show(ex.Message);
dr.Close();
cn.Close();
fs.Close();
}
}
Click to expand...
According to the debugger, what is the type of dr.GetValue(0)? If
it's a string, then you'd have serious trouble reading it as bytes...

An atricle doing just what you describe can be found here:
http://www.akadia.com/services/dotnet_read_write_blob.html

But it migth be that SQL is returning a big string, instead of a byte
array...
Click to expand...
Click to expand...
 
I tried those two opptions but they seem to not make a difference in that the
file does not convert right.

Do you have an example of using these I Might Try?

Thanks

Mike

Jesse Houwing said:
Hello Hotwheels,
Yup the Type of dr.GetValue(0) is string in the watch window.
Click to expand...

Then using a StreamWriter would be a better solution, or try GetChars instead
of GetBytes.

Jesse
Mike

Jesse Houwing said:
Hello Hotwheels,

Trying to write a routine to take bytes from a text field and put
them into a file.

I am using VS 2003 and SQL 2005. I created a table called TestText
and added two fields ID as a UID and MyText of type Text.

I then added one row with the ID
8ac11006-26ad-da11-9ec8-00059a3c7800 and MyText value of "1234".

If I put dr.GetValue(0) in the watch area it show the correct result
of "1234".

So the DataReader is working correct.

What am I doing wrong with SqlDataReader.GetBytes Method?

Also how would I wirte the command if I just wanted return the
length of bytes example maybe "long retval = dr.GetBytes(0, 0, null,
0, 0);"

Thanks
Mike
private void SqlBlob2File(string DestFilePath, string id)
{
SqlConnection cn = new SqlConnection("server=localhost;integrated
security=yes;database=RelevantSales_MSCRM");
SqlCommand cmd = new SqlCommand("SELECT MyText FROM TestText WHERE
ID =
'8ac11006-26ad-da11-9ec8-00059a3c7800'", cn);
cn.Open();
SqlDataReader dr = cmd.ExecuteReader();
bool bResult = dr.Read();
FileStream fs = new FileStream(DestFilePath,
System.IO.FileMode.Create,
System.IO.FileAccess.Write);
try
{
byte[] value = new byte[8];
long retval = dr.GetBytes(0, 0, value, 0, value.Length);
fs.Write(value, 0, value.Length);
dr.Close();
cn.Close();
fs.Close();
}
catch (Exception ex)
{
MessageBox.Show(ex.Message);
dr.Close();
cn.Close();
fs.Close();
}
}
According to the debugger, what is the type of dr.GetValue(0)? If
it's a string, then you'd have serious trouble reading it as bytes...

An atricle doing just what you describe can be found here:
http://www.akadia.com/services/dotnet_read_write_blob.html

But it migth be that SQL is returning a big string, instead of a byte
array...
Click to expand...
Click to expand...
Click to expand...
 
Hello Hotwheels,
I tried those two opptions but they seem to not make a difference in
that the file does not convert right.
Click to expand...

What do you mean? The File does not convert right? If it is a file, you should
probably store it as a blob, or convert it to varbinary in you rselect statement
so that th eDataReader is able to stream the bytes iinstead of reading unicode
text.

Jesse
Do you have an example of using these I Might Try?

Thanks

Mike

Jesse Houwing said:
Hello Hotwheels,
Yup the Type of dr.GetValue(0) is string in the watch window.
Click to expand...
Then using a StreamWriter would be a better solution, or try GetChars
instead of GetBytes.

Jesse
Mike

:

Hello Hotwheels,

Trying to write a routine to take bytes from a text field and put
them into a file.

I am using VS 2003 and SQL 2005. I created a table called
TestText and added two fields ID as a UID and MyText of type Text.

I then added one row with the ID
8ac11006-26ad-da11-9ec8-00059a3c7800 and MyText value of "1234".
If I put dr.GetValue(0) in the watch area it show the correct
result of "1234".

So the DataReader is working correct.

What am I doing wrong with SqlDataReader.GetBytes Method?

Also how would I wirte the command if I just wanted return the
length of bytes example maybe "long retval = dr.GetBytes(0, 0,
null, 0, 0);"

Thanks
Mike
private void SqlBlob2File(string DestFilePath, string id)
{
SqlConnection cn = new SqlConnection("server=localhost;integrated
security=yes;database=RelevantSales_MSCRM");
SqlCommand cmd = new SqlCommand("SELECT MyText FROM TestText WHERE
ID =
'8ac11006-26ad-da11-9ec8-00059a3c7800'", cn);
cn.Open();
SqlDataReader dr = cmd.ExecuteReader();
bool bResult = dr.Read();
FileStream fs = new FileStream(DestFilePath,
System.IO.FileMode.Create,
System.IO.FileAccess.Write);
try
{
byte[] value = new byte[8];
long retval = dr.GetBytes(0, 0, value, 0, value.Length);
fs.Write(value, 0, value.Length);
dr.Close();
cn.Close();
fs.Close();
}
catch (Exception ex)
{
MessageBox.Show(ex.Message);
dr.Close();
cn.Close();
fs.Close();
}
}
According to the debugger, what is the type of dr.GetValue(0)? If
it's a string, then you'd have serious trouble reading it as
bytes...

An atricle doing just what you describe can be found here:
http://www.akadia.com/services/dotnet_read_write_blob.html

But it migth be that SQL is returning a big string, instead of a
byte
array...
Click to expand...
Click to expand...
Click to expand...
 
Hello Hotwheels,
I tried those two opptions but they seem to not make a difference in
that the file does not convert right.

Do you have an example of using these I Might Try?
Click to expand...

try

select cast(fieldwithdata as varbinary(max)) from ...

to get the data. My guess is that that would allow you to open in with GetBytes.


Jesse
Thanks

Mike

Jesse Houwing said:
Hello Hotwheels,
Yup the Type of dr.GetValue(0) is string in the watch window.
Click to expand...
Then using a StreamWriter would be a better solution, or try GetChars
instead of GetBytes.

Jesse
Mike

:

Hello Hotwheels,

Trying to write a routine to take bytes from a text field and put
them into a file.

I am using VS 2003 and SQL 2005. I created a table called
TestText and added two fields ID as a UID and MyText of type Text.

I then added one row with the ID
8ac11006-26ad-da11-9ec8-00059a3c7800 and MyText value of "1234".
If I put dr.GetValue(0) in the watch area it show the correct
result of "1234".

So the DataReader is working correct.

What am I doing wrong with SqlDataReader.GetBytes Method?

Also how would I wirte the command if I just wanted return the
length of bytes example maybe "long retval = dr.GetBytes(0, 0,
null, 0, 0);"

Thanks
Mike
private void SqlBlob2File(string DestFilePath, string id)
{
SqlConnection cn = new SqlConnection("server=localhost;integrated
security=yes;database=RelevantSales_MSCRM");
SqlCommand cmd = new SqlCommand("SELECT MyText FROM TestText WHERE
ID =
'8ac11006-26ad-da11-9ec8-00059a3c7800'", cn);
cn.Open();
SqlDataReader dr = cmd.ExecuteReader();
bool bResult = dr.Read();
FileStream fs = new FileStream(DestFilePath,
System.IO.FileMode.Create,
System.IO.FileAccess.Write);
try
{
byte[] value = new byte[8];
long retval = dr.GetBytes(0, 0, value, 0, value.Length);
fs.Write(value, 0, value.Length);
dr.Close();
cn.Close();
fs.Close();
}
catch (Exception ex)
{
MessageBox.Show(ex.Message);
dr.Close();
cn.Close();
fs.Close();
}
}
According to the debugger, what is the type of dr.GetValue(0)? If
it's a string, then you'd have serious trouble reading it as
bytes...

An atricle doing just what you describe can be found here:
http://www.akadia.com/services/dotnet_read_write_blob.html

But it migth be that SQL is returning a big string, instead of a
byte
array...
Click to expand...
Click to expand...
Click to expand...
 
Hi,

Maybe it is easier as you tell the goal you want to achieve too.

Do you want to read a part of text, do you want to retrieve an image etc.

Cor
 
What I am trying to do is download the Attachments dirctly from Microsoft CRM
Interaction Table ActivityMimeAttachment. The code below does work for 70%
of the Attachments that have a type of 'application/octet-stream'. But it
does not work for the other 30% that have a type of image/jpeg,
application/pdf, image/gif, application/vnd.ms-excel, application/msword,
text/plain. Is there someplace where these MimeTypes are Defined So I can
convert them.

Help

private void SqlBlob2File(string DestFilePath, string id)
{

SqlConnection cn = new SqlConnection("server=localhost;integrated
security=yes;database=RelevantSales_MSCRM");
SqlCommand cmd = new SqlCommand("SELECT Body FROM ActivityMimeAttachment
WHERE ActivityMimeAttachmentID = '852161B4-D911-DB11-9EE5-0011434DCFE4'", cn);
cn.Open();
SqlDataReader dr = cmd.ExecuteReader();
bool bResult = dr.Read();

FileStream fs = new FileStream(DestFilePath, System.IO.FileMode.Create,
System.IO.FileAccess.Write);
try
{
byte[] value;
string s8Data;

s8Data = dr.GetString(0);
value = System.Convert.FromBase64String(s8Data);

fs.Write(value, 0, value.Length);

dr.Close();
cn.Close();

fs.Close();
}
catch (Exception ex)
{
MessageBox.Show(ex.Message);
dr.Close();
cn.Close();

fs.Close();
}
}

Cor Ligthert said:
Hi,

Maybe it is easier as you tell the goal you want to achieve too.

Do you want to read a part of text, do you want to retrieve an image etc.

Cor


Hotwheels said:
Trying to write a routine to take bytes from a text field and put them
into a
file.

I am using VS 2003 and SQL 2005. I created a table called TestText and
added two fields ID as a UID and MyText of type Text.

I then added one row with the ID 8ac11006-26ad-da11-9ec8-00059a3c7800 and
MyText value of "1234".

If I put dr.GetValue(0) in the watch area it show the correct result of
"1234".

So the DataReader is working correct.

What am I doing wrong with SqlDataReader.GetBytes Method?

Also how would I wirte the command if I just wanted return the length of
bytes
example maybe "long retval = dr.GetBytes(0, 0, null, 0, 0);"

Thanks
Mike

private void SqlBlob2File(string DestFilePath, string id)
{

SqlConnection cn = new SqlConnection("server=localhost;integrated
security=yes;database=RelevantSales_MSCRM");
SqlCommand cmd = new SqlCommand("SELECT MyText FROM TestText WHERE ID =
'8ac11006-26ad-da11-9ec8-00059a3c7800'", cn);
cn.Open();
SqlDataReader dr = cmd.ExecuteReader();
bool bResult = dr.Read();
FileStream fs = new FileStream(DestFilePath, System.IO.FileMode.Create,
System.IO.FileAccess.Write);
try
{
byte[] value = new byte[8];
long retval = dr.GetBytes(0, 0, value, 0, value.Length);
fs.Write(value, 0, value.Length);

dr.Close();
cn.Close();

fs.Close();
}
catch (Exception ex)
{
MessageBox.Show(ex.Message);
dr.Close();
cn.Close();

fs.Close();
}
}
Click to expand...
Click to expand...
 
Hotwheels,

For the bytes arrays you need a memorystreem to convert them to images

http://msdn.microsoft.com/en-us/library/system.io.memorystream(VS.80).aspx

Cor


Hotwheels said:
What I am trying to do is download the Attachments dirctly from Microsoft
CRM
Interaction Table ActivityMimeAttachment. The code below does work for
70%
of the Attachments that have a type of 'application/octet-stream'. But it
does not work for the other 30% that have a type of image/jpeg,
application/pdf, image/gif, application/vnd.ms-excel, application/msword,
text/plain. Is there someplace where these MimeTypes are Defined So I can
convert them.

Help

private void SqlBlob2File(string DestFilePath, string id)
{

SqlConnection cn = new SqlConnection("server=localhost;integrated
security=yes;database=RelevantSales_MSCRM");
SqlCommand cmd = new SqlCommand("SELECT Body FROM ActivityMimeAttachment
WHERE ActivityMimeAttachmentID = '852161B4-D911-DB11-9EE5-0011434DCFE4'",
cn);
cn.Open();
SqlDataReader dr = cmd.ExecuteReader();
bool bResult = dr.Read();

FileStream fs = new FileStream(DestFilePath, System.IO.FileMode.Create,
System.IO.FileAccess.Write);
try
{
byte[] value;
string s8Data;

s8Data = dr.GetString(0);
value = System.Convert.FromBase64String(s8Data);

fs.Write(value, 0, value.Length);

dr.Close();
cn.Close();

fs.Close();
}
catch (Exception ex)
{
MessageBox.Show(ex.Message);
dr.Close();
cn.Close();

fs.Close();
}
}

Cor Ligthert said:
Hi,

Maybe it is easier as you tell the goal you want to achieve too.

Do you want to read a part of text, do you want to retrieve an image etc.

Cor


Hotwheels said:
Trying to write a routine to take bytes from a text field and put them
into a
file.

I am using VS 2003 and SQL 2005. I created a table called TestText and
added two fields ID as a UID and MyText of type Text.

I then added one row with the ID 8ac11006-26ad-da11-9ec8-00059a3c7800
and
MyText value of "1234".

If I put dr.GetValue(0) in the watch area it show the correct result of
"1234".

So the DataReader is working correct.

What am I doing wrong with SqlDataReader.GetBytes Method?

Also how would I wirte the command if I just wanted return the length
of
bytes
example maybe "long retval = dr.GetBytes(0, 0, null, 0, 0);"

Thanks
Mike

private void SqlBlob2File(string DestFilePath, string id)
{

SqlConnection cn = new SqlConnection("server=localhost;integrated
security=yes;database=RelevantSales_MSCRM");
SqlCommand cmd = new SqlCommand("SELECT MyText FROM TestText WHERE ID
=
'8ac11006-26ad-da11-9ec8-00059a3c7800'", cn);
cn.Open();
SqlDataReader dr = cmd.ExecuteReader();
bool bResult = dr.Read();
FileStream fs = new FileStream(DestFilePath,
System.IO.FileMode.Create,
System.IO.FileAccess.Write);
try
{
byte[] value = new byte[8];
long retval = dr.GetBytes(0, 0, value, 0, value.Length);
fs.Write(value, 0, value.Length);

dr.Close();
cn.Close();

fs.Close();
}
catch (Exception ex)
{
MessageBox.Show(ex.Message);
dr.Close();
cn.Close();

fs.Close();
}
}
Click to expand...
Click to expand...
Click to expand...
 
Back
Top