Using If statement for parameterised queries

Question

I have a web form where registered users can and upload files. When the user uploads the file it gets saved. If another file with the exact name is uploaded then the filename gets a time and date stamp to stop overwriting old file. I then store the filename in a database but I can't seem to figure out how will I save the new filename in the database. My code is as follows

if(FileUpload1.HasFile)
{

    string dir = "DirectoryPath";
    string fileName = Path.Combine(dir, FileUpload1.FileName);

    if (!File.Exists(fileName))
    {
      FileUpload1.SaveAs(fileName);
    }
    else
    {
      string newFileName =
      Path.Combine(Path.GetDirectoryName(fileName),
      string.Concat(Path.GetFileNameWithoutExtension(fileName),
      DateTime.Now.ToString("_yyyy_MM_dd_HH_mm_ss"),
      Path.GetExtension(fileName)));
      FileUpload1.SaveAs(newFileName);
    }
}

To Save it in the database

using (SqlConnection connection = new SqlConnection("MyConnectionString"))
{
  string myQuery = "INSERT INTO MyTable(FileName) VALUES(@Filename)";
  SqlCommand cmd = new SqlCommand(myQuery, connection);
  cmd.Parameters.AddWithValue("@Filename"); //What argument would I pass here?
  connection.Open();
  cmd.ExecuteNonQuery();
}

If I do cmd.Parameters.AddWithValue("@Filename",FileUpload1.FileName); then the original name gets stored in the database even if the same file is uploaded twice. Would I put the parameterised queries within the else block? Thanks in advance for your help

Save the origianl fileName (no matter if duplicate or not) in database and add a GUID column. Use GUID as fileName to save file on hardDisk. On the otherHand, you could use a FileStream to store the file in the database directly — Pilgerstorfer Franz
– Pilgerstorfer Franz, Commented Feb 18, 2015 at 15:05
Check my answer and tell if that is what exactly you want :) — mybirthname
– mybirthname, Commented Feb 18, 2015 at 15:19
According to my original comment I did a small example project -let me know if you were able to integrated my solution into your project — Pilgerstorfer Franz
– Pilgerstorfer Franz, Commented Feb 18, 2015 at 20:04

Pilgerstorfer Franz · Accepted Answer · 2015-02-18 20:02:54Z

According to my given comment to your question I did an example that demonstrates what could be done.

create a table with a guid as primary key
add fileNames as they are given from the user
use the guid to save the uploaded files on hardDisk

fileUploadDemo.aspx

<h2>FileUpload Demo</h2>
<form id="form1" runat="server">
<div>
    <asp:FileUpload ID="FileUpload1" runat="server" />
    <asp:Button ID="btnUpload" runat="server" Text="Upload" OnClick="btnUpload_Click" />
    <asp:Label ID="lblStatus" EnableViewState="false" runat="server"></asp:Label>
</div>
</form>

fileUpload.aspx

fileUploadDemo.aspx.cs

protected void btnUpload_Click(object sender, EventArgs e)
{
    if (FileUpload1.HasFile)
    {
        try
        {
            SqlConnectionStringBuilder conBuild = new SqlConnectionStringBuilder();
            conBuild.InitialCatalog = "dbFileUploadDemo";
            conBuild.DataSource = @"localhost\sqlexpress";
            conBuild.IntegratedSecurity = true;

            string uploadDirectory = @"e:\uploads";
            Guid idFile = Guid.NewGuid();

            using (SqlConnection con = new SqlConnection(conBuild.ConnectionString))
            {
                con.Open();
                SqlCommand com = new SqlCommand("insert into tblFiles (idFile, fileName) values (@idFile, @fileName)", con);
                com.Parameters.AddWithValue("fileName", FileUpload1.FileName);
                com.Parameters.AddWithValue("idFile", idFile);
                com.ExecuteNonQuery();

                string fileName = Path.Combine(uploadDirectory, idFile.ToString());
                FileUpload1.SaveAs(fileName);
            }
            lblStatus.Text = "File uploaded";
        }
        catch (Exception ex)
        {
            // insert logging and exception handling here
            Debug.WriteLine(ex.Message);

            lblStatus.Text = "Error!";
        }
    }
    else
    {
        lblStatus.Text = "Please select file!";
    }
}

database dbFileUploadDemo

database example from ms sql server 2012

upload location on hard disk upload directory on your hard disk

Of course there are several things left to do. But I think this might do what you want and provide a good example.

Midineo · Accepted Answer · 2015-02-18 15:22:03Z

1

You should pass the new file name to your command as like this :

cmd.Parameters.AddwithValue ("@fileName" , newfilename);

answered Feb 18, 2015 at 15:22

Midineo

1417 bronze badges

2 Comments

Izzy Over a year ago

But if the file is being uploaded for the first time then?

pseudocoder Over a year ago

I think he's suggesting you rename the file every time, which I agree with. But you also need to record the original file name for when the file is requested for download. See my answer...

Tim · Accepted Answer · 2015-02-18 15:04:32Z

0

I think you need something like:

else
{
...
cmd.Parameters.AddWithValue("@Filename",newfilename); 
...
}

answered Feb 18, 2015 at 15:04

Tim

4,10910 gold badges39 silver badges64 bronze badges

1 Comment

Izzy Over a year ago

Would I save the file first before running this block? or during the save process?

mybirthname · Accepted Answer · 2015-02-18 15:18:47Z

0

Easy solution is to always put the current DateTime to the fileName.

cmd.Parameters.AddWithValue("@Filename",FileUpload1.FileName + DateTime.Now.ToString());

In this case every time the fileName will be unique. If you want to put DateTime only if the fileName already exist you should first check for file existence.

int filesCount = 0;
using (SqlConnection connection = new SqlConnection("MyConnectionString"))
{
  string query = @"Select count(*) From MyTable Where FileName=@FileName";
  SqlCommand cmd = new SqlCommand(query , connection);
  cmd.Parameters.AddWithValue("@Filename", FileUpload1.FileName); 

  connection.Open();
  filesCount  = Convert.ToInt32(cmd.ExecuteScalar());
}

After that in your query you will set 2 different parameters depending on fileCount value.

using (SqlConnection connection = new SqlConnection("MyConnectionString"))
{
  string myQuery = "INSERT INTO MyTable(FileName) VALUES(@Filename)";
  SqlCommand cmd = new SqlCommand(myQuery, connection);

  if(count == 0)
      cmd.Parameters.AddWithValue("@Filename", FileUpload1.FileName); 
  else
      cmd.Parameters.AddWithValue("@Filename",FileUpload1.FileName + DateTime.Now.ToString());

  connection.Open();
  cmd.ExecuteNonQuery();
}

For me it is better to use my first suggestion.

answered Feb 18, 2015 at 15:18

mybirthname

18.2k3 gold badges35 silver badges59 bronze badges

2 Comments

Pilgerstorfer Franz Over a year ago

I don' t think this is a good solution. You change the original filename, you will have to modify dateTimeString as it will contain special characters aso...

Izzy Over a year ago

@mybirthname Rather than reading back the file count wouldn't I be better of to put my query within the else block?

pseudocoder · Accepted Answer · 2015-02-18 15:29:50Z

0

When you use the technique you describe for storing uploaded files, you must have two filename fields: one for the original filename and one for the filename as stored.

As a side note, since you are renaming the file sometimes and must have the facility to rename files all the time, I think it makes sense just to rename the file every time. I would recommend using a GUID for the file name.

I also think it's a good idea to create a folder structure based on upload date or perhaps file count, so you never get too many files in one directory.

answered Feb 18, 2015 at 15:29

pseudocoder

4,4022 gold badges28 silver badges41 bronze badges

Comments

Vimal Vataliya · Accepted Answer · 2015-02-19 05:27:03Z

string fileName = "";
        if (FileUpload1.HasFile)
        {

            string dir = "DirectoryPath";
            fileName = Path.Combine(dir, FileUpload1.FileName);

            if (!File.Exists(fileName))
            {
                FileUpload1.SaveAs(fileName);
            }
            else
            {
                fileName = Path.Combine(Path.GetDirectoryName(fileName), string.Concat(Path.GetFileNameWithoutExtension(fileName), DateTime.Now.ToString("_yyyy_MM_dd_HH_mm_ss"), Path.GetExtension(fileName)));
                FileUpload1.SaveAs(fileName);
            }
        }
        if (fileName != "")
        {
            using (SqlConnection connection = new SqlConnection("MyConnectionString"))
            {
                string myQuery = "INSERT INTO MyTable(FileName) VALUES(@Filename)";
                SqlCommand cmd = new SqlCommand(myQuery, connection);
                cmd.Parameters.AddWithValue("@Filename",fileName); 
                connection.Open();
                cmd.ExecuteNonQuery();
            }
        }

Collectives™ on Stack Overflow

Using If statement for parameterised queries

6 Answers 6

Comments

2 Comments

1 Comment

2 Comments

Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

6 Answers 6

Comments

2 Comments

1 Comment

2 Comments

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related