I can not read a small file from NTEXT field in the database

[Guest]

I try to follow Steve's paper to build a database, and store a small text
file into SQL Server database and retrieve it later. Only difference between
my table and Steve's table is that I use NTEXT datatype for the file instead
of using IMAGE datatype. I can not use SqlDataReader to read the data. I need
your help, Thanks.
-David

(1) I have a table TestFile for testing:
ID int
FileName navrchar(255)
FileData ntext
ContentType nvarchar 32
FileSize int
UploadDate datetime

(2) I use the stored procedure to store the file. It works:
System.Text.Encoding encoding = System.Text.Encoding.UTF8;
File1.PostedFile.InputStream.Read(bytContent, 0, iLength);
this.sqlCommand1.Parameters["@FileName"].Value=sFileName;
this.sqlCommand1.Parameters["@FileSize"].Value=iLength;
this.sqlCommand1.Parameters["@FileData"].Value=encoding.GetString(bytContent);
this.sqlCommand1.Parameters["@ContentType"].Value=sContentType;

--------------------

(3) The problem is how to read the file from the table. The following is my
read process:

SqlDataReader dr;
cmdGetFile.Parameters["@ID"].Value=Request["ID"].ToString();
Response.Write(Request["ID"].ToString()); //test message
dbConn.Open();
dr =cmdGetFile.ExecuteReader();
if(dr.Read())
{
Response.ContentType = dr["ContentType"].ToString();
Response.OutputStream.Write((byte[])dr["FileData"], 0,
(int)dr["FileSize"]);
Response.AddHeader("Content-Disposition", "Attachment;filename=" +
dr["FileName"].ToString());
}
else
{
Response.Write("File Not Found."+ dr.Read().ToString());
}

dr.Close();
dbConn.Close();

 

[Mark Fitzpatrick]

Why did you use ntext to store a file instead of Image? That's the purpose
of Image, basically a binary large object. That wouldn't be a ntext datatype
because that's a double-byte character field and not a binary large object.
A text file should still be a blob, unless of course it's just plain text
with no other file information associated with it.

You are ensuring that the database is actually performing the query
correctly? If you're working with SQL Server locally, fire up the SQL
Profiler to see what is actually going on. You may find that there is a
problem with actually executing the query, or at the very least you can see
the exact call to the db and then run that yourself in the management studio
to determine if there really are results coming back.

 

[Guest]

Thank you.

I store an XML file as a metadata for many complicated image files stored in
remote file systems (thety are in different formats, use different image
access/processing tools, and so on). Image tools will retrieve files and
process them based on the metadata. Those files are in special format.

Do you have some good way to handle it?

I will try to use binary format. However, is it searchable?

David

Why did you use ntext to store a file instead of Image? That's the purpose
of Image, basically a binary large object. That wouldn't be a ntext datatype
because that's a double-byte character field and not a binary large object.
A text file should still be a blob, unless of course it's just plain text
with no other file information associated with it.

You are ensuring that the database is actually performing the query
correctly? If you're working with SQL Server locally, fire up the SQL
Profiler to see what is actually going on. You may find that there is a
problem with actually executing the query, or at the very least you can see
the exact call to the db and then run that yourself in the management studio
to determine if there really are results coming back.


--

Hope this helps,
Mark Fitzpatrick
Former Microsoft FrontPage MVP 199?-2006

I try to follow Steve's paper to build a database, and store a small text
file into SQL Server database and retrieve it later. Only difference
between
my table and Steve's table is that I use NTEXT datatype for the file
instead
of using IMAGE datatype. I can not use SqlDataReader to read the data. I
need
your help, Thanks.
-David

(1) I have a table TestFile for testing:
ID int
FileName navrchar(255)
FileData ntext
ContentType nvarchar 32
FileSize int
UploadDate datetime

(2) I use the stored procedure to store the file. It works:
System.Text.Encoding encoding = System.Text.Encoding.UTF8;
File1.PostedFile.InputStream.Read(bytContent, 0, iLength);
this.sqlCommand1.Parameters["@FileName"].Value=sFileName;
this.sqlCommand1.Parameters["@FileSize"].Value=iLength;
this.sqlCommand1.Parameters["@FileData"].Value=encoding.GetString(bytContent);
this.sqlCommand1.Parameters["@ContentType"].Value=sContentType;

--------------------

(3) The problem is how to read the file from the table. The following is
my
read process:

SqlDataReader dr;
cmdGetFile.Parameters["@ID"].Value=Request["ID"].ToString();
Response.Write(Request["ID"].ToString()); //test message
dbConn.Open();
dr =cmdGetFile.ExecuteReader();
if(dr.Read())
{
Response.ContentType = dr["ContentType"].ToString();
Response.OutputStream.Write((byte[])dr["FileData"], 0,
(int)dr["FileSize"]);
Response.AddHeader("Content-Disposition", "Attachment;filename=" +
dr["FileName"].ToString());
}
else
{
Response.Write("File Not Found."+ dr.Read().ToString());
}

dr.Close();
dbConn.Close();

 

[Mark Fitzpatrick]

Which version of SQL Server are you on? If it's SQL 2005 you could use the
new XML datatype instead.

Are you simply storing the contents of the XML file, or the XML file itself?
Keep in mind, that a file is a collection of contents, plus other
information, which is why a blob format is better for true files. If you're
just storing the contents of a file, then the ntext or XML datatypes would
be fine.

One of the things I've found though when returning no results is to really
ensure that the query is executing like you think. Your issue may be very
simple. It could be that the querystring variable is coming up null because
of a goof in the URL, or that the where clause in the stored procedure has
some invalid condition.


--

Hope this helps,
Mark Fitzpatrick
Former Microsoft FrontPage MVP 199?-2006

Thank you.

I store an XML file as a metadata for many complicated image files stored
in
remote file systems (thety are in different formats, use different image
access/processing tools, and so on). Image tools will retrieve files and
process them based on the metadata. Those files are in special format.

Do you have some good way to handle it?

I will try to use binary format. However, is it searchable?

David

Why did you use ntext to store a file instead of Image? That's the
purpose
of Image, basically a binary large object. That wouldn't be a ntext
datatype
because that's a double-byte character field and not a binary large
object.
A text file should still be a blob, unless of course it's just plain text
with no other file information associated with it.

You are ensuring that the database is actually performing the query
correctly? If you're working with SQL Server locally, fire up the SQL
Profiler to see what is actually going on. You may find that there is a
problem with actually executing the query, or at the very least you can
see
the exact call to the db and then run that yourself in the management
studio
to determine if there really are results coming back.


--

Hope this helps,
Mark Fitzpatrick
Former Microsoft FrontPage MVP 199?-2006

I try to follow Steve's paper to build a database, and store a small
text
file into SQL Server database and retrieve it later. Only difference
between
my table and Steve's table is that I use NTEXT datatype for the file
instead
of using IMAGE datatype. I can not use SqlDataReader to read the data.
I
need
your help, Thanks.
-David

(1) I have a table TestFile for testing:
ID int
FileName navrchar(255)
FileData ntext
ContentType nvarchar 32
FileSize int
UploadDate datetime

(2) I use the stored procedure to store the file. It works:
System.Text.Encoding encoding = System.Text.Encoding.UTF8;
File1.PostedFile.InputStream.Read(bytContent, 0, iLength);
this.sqlCommand1.Parameters["@FileName"].Value=sFileName;
this.sqlCommand1.Parameters["@FileSize"].Value=iLength;
this.sqlCommand1.Parameters["@FileData"].Value=encoding.GetString(bytContent);
this.sqlCommand1.Parameters["@ContentType"].Value=sContentType;

--------------------

(3) The problem is how to read the file from the table. The following
is
my
read process:

SqlDataReader dr;
cmdGetFile.Parameters["@ID"].Value=Request["ID"].ToString();
Response.Write(Request["ID"].ToString()); //test message
dbConn.Open();
dr =cmdGetFile.ExecuteReader();
if(dr.Read())
{
Response.ContentType = dr["ContentType"].ToString();
Response.OutputStream.Write((byte[])dr["FileData"], 0,
(int)dr["FileSize"]);
Response.AddHeader("Content-Disposition", "Attachment;filename=" +
dr["FileName"].ToString());
}
else
{
Response.Write("File Not Found."+ dr.Read().ToString());
}

dr.Close();
dbConn.Close();