April 16, 2022 at 5:21 am
Hi everyone
I have been working on a C# script to automate data collection. I have been testing it and I have discovered one scenario that failed so my script needs to be amended. I am not super experienced with coding so I am not quite sure how to make the fix. I have included my script below. I will explain the scenario that failed and hence needs to be fixed.
***Description of Script***
This script takes files in a local folder "C:\Users\Me\Documents\Data\To_Be_Processed\" and loads them into the DB. There is a stored procedure dbo.UpdateTable that handles the import. There is a second stored procedure dbo.UpdateImportHistory that updates the table ImportHistory with the file name of the file to be imported to make sure it isn't loaded again. There is another script that only downloads files from a SFTP server if the file doesn't exist in table ImportHistory. Hence, files in "C:\Users\Me\Documents\Data\To_Be_Processed\" will only contain new files to be imported.
If the filename exists in the table ImportHistory then that means that the file has already been loaded earlier. If the file has been loaded before then that results in an error (b/c the table that holds the file names has a constraint on it to ensure that only unique file names can be added to that table) when a second attempt is made to load the same file. Once the error is made then the rollback begins.
This means that the scenario where filename exists in the ImportHistory table has been addressed.
Finally, the script moves files from "C:\Users\Me\Documents\Data\To_Be_Processed\" to "C:\Users\Me\Documents\Data\Processed_Files\".
***Scenario To Be Fixed***
Suppose filename does not exist in the table ImportHistory (hence "new" file) but the filename exists in "C:\Users\Me\Documents\Data\Processed_Files\". This is a very odd scenario but it could happen so the script should handle it. Currently, the file gets imported (problem) but the file does not get moved (expected). The reason why the file doesn't get moved is that there is a check in the code that looks for duplicate destination filename. The problem is that the DB still got updated even though the file name exists in the destination folder. In this scenario, a rollback should have happened. How can I amend my code to take this scenario into account?
***Script***
public void Main()
{
try
{
string filePathTemp = @"C:\Users\Me\Documents\Data\Temp_Files\";
string filePathSource = @"C:\Users\Me\Documents\Data\To_Be_Processed\";
string filePathDestination = @"C:\Users\Me\Documents\Data\Processed_Files\";
string[] fileEntries = Directory.GetFiles(filePathTemp, "*");
string connectionString = @"Data Source=localhost;Initial Catalog=myDB;Integrated Security=True";
SqlConnection con = new SqlConnection(connectionString);
con.Open();
foreach (string fullFileName in fileEntries)
{
if (fullFileName.Contains("NewFile"))
{
SqlCommand cmd = con.CreateCommand();
SqlTransaction transaction;
transaction = con.BeginTransaction();
cmd.Connection = con;
cmd.Transaction = transaction;
string fileName = "";
try
{
string SQL = "dbo.UpdateTable";
cmd = new SqlCommand(SQL, con, transaction);
cmd.CommandType = CommandType.StoredProcedure;
SqlParameter param;
param = cmd.Parameters.Add("@FILENAME", SqlDbType.VarChar, 200);
param.Value = fullFileName;
cmd.ExecuteNonQuery();
SQL = "dbo.UpdateImportHistory";
cmd = new SqlCommand(SQL, con, transaction);
fileName = fullFileName.Substring(filePathTemp.Length, fullFileName.Length - filePathTemp.Length).Trim();
fileName = (fileName.Substring(0, fileName.Length - 4) + ".zip").Trim();
cmd.CommandType = CommandType.StoredProcedure;
param = cmd.Parameters.Add("@FILENAME", SqlDbType.VarChar, 100);
param.Value = fileName;
cmd.ExecuteNonQuery();
transaction.Commit();
if (File.Exists(filePathDestination + fileName))
{
bool fireAgain = false;
Dts.Events.FireInformation(0, null,
string.Format("File {0} exists in the destination folder. Further investigation is needed.", fileName),
null, 0, ref fireAgain);
}
else
{
File.Move(filePathSource + fileName, filePathDestination + fileName);
}
}
catch (Exception ex1)
{
bool fireAgain = false;
Dts.Events.FireInformation(0, null,
string.Format("Commit Exception Type: {0}", ex1.GetType()),
null, 0, ref fireAgain);
fireAgain = false;
Dts.Events.FireInformation(0, null,
string.Format(" Message: {0}", ex1.Message),
null, 0, ref fireAgain);
try
{
transaction.Rollback();
fireAgain = false;
Dts.Events.FireInformation(0, null,
string.Format("Rollback successful: {0}", fileName),
null, 0, ref fireAgain);
}
catch (Exception ex2)
{
fireAgain = false;
Dts.Events.FireInformation(0, null,
string.Format("Rollback Exception Type: {0}", ex2.GetType()),
null, 0, ref fireAgain);
fireAgain = false;
Dts.Events.FireInformation(0, null,
string.Format(" Message: {0}", ex2.Message),
null, 0, ref fireAgain);
}
}
}
}
con.Close();
Dts.TaskResult = (int)ScriptResults.Success;
}
catch (Exception ex3)
{
Dts.Events.FireError(0, "Exception from Script Task", ex3.Message + "\r" + ex3.StackTrace, String.Empty, 0);
Dts.TaskResult = (int)ScriptResults.Failure;
}
}
Thank you!
April 16, 2022 at 2:52 pm
You're using C# to do things which could also (better, some might say) be done in SQL Server. You could use xp_DirTree to read filenames from a folder and use a CURSOR (ha, they do have legitimate purposes, like rbar'ing through a list of files) to loop through the filenames and run your procs
Aus dem Paradies, das Cantor uns geschaffen, soll uns niemand vertreiben können
April 18, 2022 at 5:56 am
Anyone able to help me with this?
Thank you
April 18, 2022 at 8:37 am
This chunk of C# is sufficiently complex that I doubt anyone here will produce a modified version for you. But I will provide some comments:
If you haven't even tried to resolve your issue, please don't expect the hard-working volunteers here to waste their time providing links to answers which you could easily have found yourself.
April 21, 2022 at 3:20 pm
A few things occur to me.
April 22, 2022 at 4:55 am
Thank you both for your replies. I will review. Coding isn't my strength so it will take a bit of time to process the suggestions. Thank you again!!
Viewing 6 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply