If Fileupload is blank do not update Database Varbinary

Question

User-1901014284 posted

Hi,

I have a fileUpload function which is used to update database records following the previous upload. Within the fileUpload the document uploaded is saved into the database as Data (Varbinary(MAX)) as well as the ContentType (varchar(50)) and Extension (varchar(8)). On the fileUpload the document may not always be replaced just some of the data that is also inserted into the table (e.g dates, reference number, Name etc.) during the upload I would like to check if the fileUpload is blank and if so not to updated the Data, ContentType and Extension columns within the database as the document has not been updated. I have added my code below but am unsure how I can do this.

C# Code:

protected void btnUpdate_Click(object sender, EventArgs e)
        {
            using (SqlCommand cmd = new SqlCommand("UpdateDoc", con))
            {
                using (SqlDataAdapter sda = new SqlDataAdapter())
                {
                    string DocName = Path.GetFileName(FileUpload2.PostedFile.FileName);
                    string contentType = FileUpload2.PostedFile.ContentType;
                    string extension = Path.GetExtension(DocName);
                    using (Stream fs = FileUpload2.PostedFile.InputStream)
                    {
                        using (BinaryReader br = new BinaryReader(fs))
                        {
                            byte[] bytes = br.ReadBytes((Int32)fs.Length);

                            id = IDtxt.Text;

                            System.Globalization.DateTimeFormatInfo dateInfo = new System.Globalization.DateTimeFormatInfo();
                            dateInfo.ShortDatePattern = "{0:dd/MM/yyyy}";
                            DateTime date = Convert.ToDateTime(Datetxt.Text, dateInfo);


                            cmd.CommandType = CommandType.StoredProcedure;
                            //SqlCommand cmd = new SqlCommand("UPDATE Portal_HS_PolicyDocs SET DocRefNo=@DocRefNo, Date=@Date, ContentType=@ContentType, Data=@Data, Extension=@Extension WHERE ID=@Id");
                            cmd.Parameters.AddWithValue("@Id", id);
                            cmd.Parameters.AddWithValue("@RefNo", SqlDbType.Text).Value = RefNotxt.Text;
                            cmd.Parameters.AddWithValue("@Date", SqlDbType.DateTime).Value = date;
 = uploadedbyID;
                            cmd.Parameters.AddWithValue("@ContentType", contentType);
                            cmd.Parameters.AddWithValue("@Data", bytes);
                            cmd.Parameters.AddWithValue("@Extension", extension);

                            con.Open();
                            cmd.ExecuteNonQuery();
                            con.Close();
                        }
                    }


Stored Procedure Code:

ALTER PROCEDURE UpdateDoc
	@ID int,
	@RefNo varchar(50),
	@Date datetime,
	@ContentType varchar(50),
	@Extension varchar(8),
	@Data varbinary(MAX)
	
	AS

	Update [dbo].[Table]
	SET [RefNo] = @RefNo
	,[Date] = @Date
	,[ContentType] = @ContentType
	,[Extension] = @Extension
	,[Data] = @Data
WHERE ID = @ID

Any help would be greatly appreciated.

Jonny

Answer 1

User475983607 posted

during the upload I would like to check if the fileUpload is blank and if so not to updated the Data, ContentType and Extension columns within the database as the document has not been updated.

Pretty simple.

if (FileUpload1.HasFile)
{
   //Do file stuff
}
else
{
  //No upload file found
}

Reference documentaiton

https://docs.microsoft.com/en-us/dotnet/api/system.web.ui.webcontrols.fileupload?view=netframework-4.8

Answer 2

User-1901014284 posted

Thank you mgebhard,

but the else section is where i am struggling with, for example if

if (FileUpload1.HasFile)
{
   cmd.Parameters.AddWithValue("@ContentType", contentType);
   cmd.Parameters.AddWithValue("@Data", bytes);
   cmd.Parameters.AddWithValue("@Extension", extension);
}

Then allow the File to be uploaded as well as the parameters. If a file has not been uploaded then I would like all data to be committed to the database excluding the 3 parameters mentioned above and I am not sure how to do this under the 

else
{
   //No upload file found
}

section of the code.

Many thanks.

Jonny

Answer 3

User475983607 posted

Then allow the File to be uploaded as well as the parameters.

I think there is a misunderstanding.  At this point, the user has already decided to upload a file or not.   The best you can do is check if the file exists.

Perhaps you need to rethink the approach?

Answer 4

User753101303 posted

Hi,

You could pass DBNull.Value explicitely or use @Data varbinary(max)=NULL so that it is NULL by default. Your SP could then test that to update only the needed columns (likely using two separate UPDATE statements).

Another option could be to use explicitely two separate SP, each one updating the relevant columns...

Not sure which approach you prefer ?

ie it could be something such as :

ALTER PROCEDURE UpdateDoc
	@ID int,
	@RefNo varchar(50),
	@Date datetime,
	@ContentType varchar(50)=NULL,
	@Extension varchar(8)=NULL,
	@Data varbinary(MAX)=NULL
	AS
	Update [dbo].[Table]
	SET [RefNo] = @RefNo
	,[Date] = @Date
        WHERE ID = @ID

        IF @ContentType IS NOT NULL OR @Extension IS NOT NULL OR @Data IS NOT NULL
	   Update [dbo].[Table]
	   SET [ContentType] = @ContentType
	   ,[Extension] = @Extension
	   ,[Data] = @Data
           WHERE ID = @ID -- And more likely fails anyway if not all 3 are not NULL ?

Answer 5

User-1901014284 posted

Hi both,

Thank you for your responses, the problem I have is the user may need to only change the Expiry Date of a document which stored in the DB under the date column which is why the document would not need to be re-uploaded into the system after the Expiry Date has been updated. At the moment when the Expiry Date is updated and the FileUpload is left blank the current VarBinary(MAX) is replaced with a blank document labelled the same name as my DB table with the FileType just as File. 

Again many thanks for your help, it is greatly appreciated.

Jonny

Answer 6

User753101303 posted

The source issue is that it happens because you were updating blindly all fields even if no file at all is uploaded (and so it is exposed as a zero length file)

As explained earlier you could change the current SP to test which information you really have or have two SPs so that you can update only the columns you want based on the which information you got. Have you tried past suggestions ?

Answer 7

User-1901014284 posted

Hi PatriceSc,

Again thank you for your response, I have tried as you have mentioned previously by splitting the SP and using the code you have provided above but the same issue occurs.

Many thanks

Jonny

Answer 8

User475983607 posted

jonnygareth30

Again thank you for your response, I have tried as you have mentioned previously by splitting the SP and using the code you have provided above but the same issue occurs.

It does not make sense why you cannot code this. It seems like you are jamming everything down one stored procedure. 

How about creating a second stored procedure that updates only the expiration date or fields you want.  Then in the if condition, call one or the other proc. 

Answer 9

User-1901014284 posted

Thank you both for your responses,

I now have this working, my mistake was I had not removed the DocName from the second SP so this was still being updated in both SP's causing the issue.

Again thank you very much for all your help it is greatly appreciated.

Jonny