How to copy files from one folder to another and also how to delete files from sql

[sqldev12]

Hi,

I need to create a .Net application ( C# or Vb.NEt) and it must be a console application which will have to accept 2 parameters

Archive or Clean
Job Library key



If the first parameter is Archive then got to FileArchive table and get the job library key and take this job library key and go to the job library table and then go to the input folder and copy all the files and move them to the Output folder. While moving these files make sure if the file name has a date stamp attached to it in the end if it doesn’t then add a date stamp to the end of the file name (the date stamp will be the previous day as all the files have previous day’s date).

Example of job library table :-

tUTL_JobLibrary_Key JobName DatabaseName

40 jjlputl_test_filearchive_com UTL



Example for File Archive table:-

tUTL_JobLibrary_Key InputFolderName OutputFolderName

40 \\jjledw7\App\Sujith\Old \\jjledw7\App\Sujith\New



If the first parameter is Clean then go to the filecleanup table and get the job library key and take this job library key and go to the job library table and Go to the Output folder and then get the output file and also the extension get the number of days from the Number_of_Days_To_keep column and then keep that number of days files and delete remaining ones.( there will be different files with the same name and different datestamp though so keep the files with the given number of days to keep and delete remaining ones.)

Example of job library table :-

tUTL_JobLibrary_Key JobName DatabaseName

40 jjlputl_test_filecleanup_com UTL

Example for File CleanUp table:-

tUTL_JobLibrary_Key OutputFolderName OutputFilename OutputFileNameExt NumberOfDaysToKeep

41 \\jjledw7\App\Sujith\Old Test_20131221 txt 2



I know we don’t need the job library key as we just bring the job name which does nothing in this process but the job name is the way we automate our jobs and if anytime this job fails that job name helps us to locate the job and its error.
please let me know if i am unclear about it or if you need any more details.

Thanks a lot for your help

[Arjay]

What are you asking for help with? Do you know how to read from SQL? Do you know how to delete files? Have you search bing or google for both? If you haven't, please do so because there are many examples of how do to each.

[sqldev12]

Hi Arjay,

I used google and found some examples and built some code by myself.It is shown as below :-

Imports System.Data.SqlClient
Imports System.IO
Imports System.Windows.Forms

Module Module1
'Create ADO.NET objects.
Private myConn As SqlConnection
Private myCmd As SqlCommand
Private myReader As SqlDataReader
Private results As String

Sub Main(ByVal sArgs() As String)
If sArgs.Length <> 2 Then
Console.WriteLine("Please input 2 parameters: 'Archive'/'Clean' And Job Library key")
Else
Dim operation As String = sArgs(0)
Dim jobLibkey As Integer = CType(sArgs(1), Integer)

Select Case operation
Case "Archive"
Console.WriteLine("Archive")
myConn = New SqlConnection("Initial Catalog=UTL;" &
"Data Source=JJLEDW4;Integrated Security=SSPI;")
Try
'Create a Command object.
myCmd = myConn.CreateCommand
myCmd.CommandText = "SELECT InputFolderName, OutputFolderName, JobName FROM Utility.tUTL_FileArchive AS A, Logging.tUTL_JobLibrary AS B WHERE A.tUTL_JobLibrary_Key=B.tUTL_JobLibrary_Key AND A.tUTL_JobLibrary_Key=@JobLibKey"
' Add CustomerID parameter for WHERE clause.
myCmd.Parameters.Add("@JobLibKey", SqlDbType.Int)
myCmd.Parameters("@JobLibKey").Value = jobLibkey
'Open the connection.
myConn.Open()

myReader = myCmd.ExecuteReader()

'Concatenate the query result into a string.
Do While myReader.Read()
'results = results & myReader.GetString(0) & vbTab & _
' myReader.GetString(1) & vbTab & _
' myReader.GetString(2) & vbLf
Dim inputFilePath As String = Application.StartupPath & myReader.GetString(0) & "\" & myReader.GetString(2)
Dim outputFilePath As String = Application.StartupPath & myReader.GetString(1) & "\" & myReader.GetString(2)
If File.Exists(inputFilePath) Then
File.Move(inputFilePath, outputFilePath)
End If
Loop
''MsgBox("Done!")
Catch ex As Exception
MsgBox(ex.ToString())
Finally
'Close the reader and the database connection.
myReader.Close()
myConn.Close()
End Try
Case "Clean"
Console.WriteLine("Clean")
Case Else
Console.WriteLine("Error")
End Select
End If

'Console.ReadKey()
End Sub

End Module


But this code doesnt seem to be working. Can you let me know where do i need to make the changes?

Thanks

[Arjay]

Well, the first thing you need to do is use [ code] [/ code] tags.

The next thing you need to figure out is what language are you programming. Is it C# or VB.Net? If it's C#, your code is VB.Net. This is the C# forum, if you want to code in VB.Net, then let me know and I'll move the thread to the VB.Net forum.

[sqldev12]

I want it to be done in either C#.Net or VB.Net , I posted this question in both the forums, As per the examples i started coding in VB.net So far.

Thanks

[Arjay]

Can't help you with the VB.Net code. I'd suggest you post C# code, but since you already have posted in the VB.Net forum, there's no need to double post.

What you really need to do is take the problem step by step and debug your code. Start by opening a connection to the database, then do the query and get the results back. Finally copy the files. Debug each step of the way. If you can't open the database for example, then look at what you have for your connection string and compare it to what the examples are from a google search. Same for running the query.

Heck, I'd start by running the query in the SQL Management Studio just to make sure the query will run. Of course to test in mgmt. studio, you'll need to hard code the jobkey.

SELECT InputFolderName, OutputFolderName, JobName
FROM Utility.tUTL_FileArchive AS A, Logging.tUTL_JobLibrary AS B
WHERE A.tUTL_JobLibrary_Key= B.tUTL_JobLibrary_Key AND A.tUTL_JobLibrary_Key= 'somejobkeyvalue' --@JobLibKey

[sqldev12]

Hi Arjay,

This query runs fine in management studio. It returns me back the input foldername,output foldername and job key if i give the job library key =40 which is what i am testing for.

Thanks

[Arjay]

Great, then the next step is to set a breakpoint (F9) on the connection open command and run the debugger (F5). Then step through your code one line at a time.

[sqldev12]

Hi Arjay,

I have added a breakpoint at myconn.Open() and hit f5 and command promt flashes and go away.

[dglienna]

hit F8 after the breakpoint to see where the next line goes. Probably an error trap.