If record exists in database, UPDATE a single column

Question

I have a bulk uploading object in place that is being used to bulk upload roughly 25-40 image files at a time. Each image is about 100-150 kb in size.

During the upload, I've created a for each loop that takes the file name of the image (minus the file extension) to write it into a column named "sku". Also, for each file being uploaded, the date is recorded to a column named DateUpdated, as well as some image path data.

Here is my c# code:

protected void graphicMultiFileButton_Click(object sender, EventArgs e)

 {

 //graphicMultiFile is the ID of the bulk uploading object ( provided by Dean Brettle: http://www.brettle.com/neatupload  )

   if (graphicMultiFile.Files.Length > 0)

          {
           foreach (UploadedFile file in graphicMultiFile.Files)
             {
                 //strip ".jpg" from file name (will be assigned as SKU)
                  string sku = file.FileName.Substring(0, file.FileName.Length - 4);

                 //assign the directory where the images will be stored on the server
                 string directoryPath = Server.MapPath("~/images/graphicsLib/" + file.FileName);

                 //ensure that if image existes on server that it will get overwritten next time it's uploaded:
                 file.MoveTo(directoryPath, MoveToOptions.Overwrite);

                 //current sql that inserts a record to the db
                SqlCommand comm;
                SqlConnection conn;
                string connectionString = ConfigurationManager.ConnectionStrings["DataConnect"].ConnectionString;
                conn = new SqlConnection(connectionString);
                 comm = new SqlCommand("INSERT INTO GraphicsLibrary (sku, imagePath, DateUpdated) VALUES (@sku, @imagePath, @DateUpdated)", conn);

                comm.Parameters.Add("@sku", System.Data.SqlDbType.VarChar, 50);
                comm.Parameters["@sku"].Value = sku;

               comm.Parameters.Add("@imagePath", System.Data.SqlDbType.VarChar, 300);
               comm.Parameters["@imagePath"].Value = "images/graphicsLib/" + file.FileName;

               comm.Parameters.Add("@DateUpdated", System.Data.SqlDbType.DateTime);
              comm.Parameters["@DateUpdated"].Value = DateTime.Now;

             conn.Open();
             comm.ExecuteNonQuery();
             conn.Close();


           }
         }
      }

After images are uploaded, managers will go back and re-upload images that have previously been uploaded.

This is because these product images are always being revised and improved.

For each new/improved image, the file name and extension will remain the same - so that when image 321-54321.jpg was first uploaded to the server, the new/improved version of that image will still have the image file name as 321-54321.jpg.

I can't say for sure if the file sizes will remain in the 100-150KB range. I'll assume that the image file size will grow eventually.

When images get uploaded (again), there of course will be an existing record in the database for that image. What is the best way to:

1. Check the database for the existing record (stored procedure or SqlDataReader or create a DataSet ...?)
2. Then if record exists, simply UPDATE that record so that the DateUpdated column gets today's date.
3. If no record exists, do the INSERT of the record as normal.

Things to consider:

If the record exists, we'll let the actual image be uploaded. It will simply overwrite the existing image so that the new version gets displayed on the web.

We're using SQL Server 2000 on hosted environment (DiscountAsp).

I'm programming in C#.

The uploading process will be used by about 2 managers a few times a month (each) - which to me is not a allot of usage.

Although I'm a jr. developer, I'm guessing that a stored procedure would be the way to go. Just seems more efficient - to do this record check away from the for each loop... but not sure. I'd need extra help writing a sproc, since I don't have too much experience with them.

Thank everyone...

Answer 1

OK - I'm answering my own question. Using a StoredProceedure as follows: (I've tested and so far is working as I need it to...)

ALTER PROCEDURE dbo.addOrUpdateImageRecord

(
@addToZip bit,
@sku varchar(50),
@imagePath varchar(300),
@DateCreated DateTime, 
@DateUpdated DateTime
)

AS

BEGIN SELECT sku FROM GraphicsLibrary WHERE sku=@sku END

if(@@RowCount=0)

BEGIN INSERT INTO GraphicsLibrary (addToZip, sku, imagePath, DateCreated) VALUES(@addToZip, @sku, @imagePath, @DateCreated) END

ELSE

UPDATE GraphicsLibrary SET DateUpdated = @DateUpdated WHERE sku = @sku

/* SET NOCOUNT ON */
RETURN

Answer 2

The loop will be faster in managed code (rather than in the stored procedure). I would use the following code with the following stored procedure:

if (graphicMultiFile.Files.Length > 0)          
        {       
            string connectionString = ConfigurationManager.ConnectionStrings["DataConnect"].ConnectionString;                
            foreach (UploadedFile file in graphicMultiFile.Files)             
            {                 
                string sku = file.FileName.Substring(0, file.FileName.Length - 4);                 
                string directoryPath = Server.MapPath("~/images/graphicsLib/" + file.FileName);                 
                file.MoveTo(directoryPath, MoveToOptions.Overwrite);                 

                SqlConnection conn = new SqlConnection(connectionString);       
                SqlCommand comm = new SqlCommand("exec addOrUpdateImageRecord @sku, @imagePath");                
                comm.Parameters.Add("@sku", System.Data.SqlDbType.VarChar, 50);                
                comm.Parameters["@sku"].Value = sku;               
                comm.Parameters.Add("@imagePath", System.Data.SqlDbType.VarChar, 300);               
                comm.Parameters["@imagePath"].Value = "images/graphicsLib/" + file.FileName;               
                conn.Open();             
                comm.ExecuteNonQuery();             
                conn.Close();           
            }         
        }

Here's the code for the stored procedure:

CREATE PROCEDURE dbo.addOrUpdateImageRecord(
  @sku varchar(50),
  @imagePath varchar(300))

AS

 DECLARE @ExistenceCheck int
 SELECT @ExistenceCheck = COUNT(*)
 FROM GraphicsLibrary 
 WHERE sku=@sku 

 IF(@ExistenceCheck=0)
 BEGIN 
  INSERT INTO GraphicsLibrary (sku, imagePath, DateCreated) 
  VALUES(@sku, @imagePath, GETDATE()) 
 END
 ELSE
 BEGIN
  UPDATE GraphicsLibrary 
  SET DateUpdated = GETDATE() 
  WHERE sku = @sku
 END


GO