Import to MySQL via a loop (ASP.NET C#)

[proximity]

Hi,

I have a directory with a load of files in it, with each file named E_1, E_2, E_3 etc. There will never be more than 10 files.
Now, I want a user to click a button that will do the following to each file. So, do E_1, then delete it, do E_2 then delete it etc . . . .

//open the connection
conn.Open();

//delete any existing data
string sql_A1 = "TRUNCATE TEMP_TABLE;";
MySqlScript script_A1 = new MySqlScript(conn, sql_A1);
script_A1.Execute();

MySqlBulkLoader r = new MySqlBulkLoader(conn);
r.TableName = "TEMP_TABLE";
r.FieldTerminator = "\t";
r.LineTerminator = "\n";
r.FileName = FILE_TO_IMPORT; //this will be E_1, E_2, E_3 etc
r.NumberOfLinesToSkip = 0;

//load the file then delete the original
r.Load();
File.Delete(FILE_TO_IMPORT);

//run stored procedure to process the file in temp table
string rkl = "sp_process_temp_file"; //stored procedure name
MySqlCommand cmd_rkl = new MySqlCommand(rkl, conn);
cmd_rkl.CommandType = CommandType.StoredProcedure;
cmd_rkl.Connection = conn;

conn.Open();
cmd_rkl.ExecuteNonQuery();
conn.Close();
conn.Dispose();

Go back and do E_2 . . . . .

Is this possible? Note that all the above code works without issue. I just can't figure out how to loop it!

Thanks for any help received . . . .

 

[jbenson001]

You will need to read the file names from the directory.
Look into using the DirectoryInfo and FileInfo classes.

Once you have a list of file objects, then you can loop through and do what you need.

 

[proximity]

Hi, I solved it using your pointer. Here is my code for anyone else having a spot of bother. What it will do is loop through the directory for all the required files, load into temp table, run a stored procedure to process the data and drop it into a normalized table, then delete the original file. It will then go back and repeat until all files in the directory are done._

protected void ImportFiles()
{
// Get list of files
string[] files = Directory.GetFiles(@"\\myfolderlocation\", "E_*", SearchOption.AllDirectories);

// set up the loop
foreach (string myfiles in files)
{
//open the connection
conn.Open();

//delete existing data
string sql_A1 = "TRUNCATE temp_table;"; //delete old data
MySqlScript script_A1 = new MySqlScript(conn, sql_A1);
script_A1.Execute();

MySqlBulkLoader r = new MySqlBulkLoader(conn);
r.TableName = "temp_table";
r.FieldTerminator = "\t";
r.LineTerminator = "\n";
r.FileName = myfiles ;
r.NumberOfLinesToSkip = 0;

//load the file then delete the original
r.Load();
File.Delete(myfiles );

//process temp data
string rkl = "sp_process_temp_data"; //stored procedure name
MySqlCommand cmd_rkl = new MySqlCommand(rkl, conn);
cmd_rkl.CommandType = CommandType.StoredProcedure;
cmd_rkl.Connection = conn;

cmd_rkl.ExecuteNonQuery();
conn.Close();
conn.Dispose();

lblMessage.Text = "Job Complete";
}

}