How to copy files from one folder to another and also how to delete files from sql
CodeGuru Home VC++ / MFC / C++ .NET / C# Visual Basic VB Forums Developer.com
Results 1 to 10 of 10

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

  1. #1
    Join Date
    Dec 2013
    Posts
    6

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

    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

  2. #2
    Arjay's Avatar
    Arjay is offline Moderator / MS MVP Power Poster
    Join Date
    Aug 2004
    Posts
    11,196

    Re: How to copy files from one folder to another and also how to delete files from sq

    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.

  3. #3
    Join Date
    Dec 2013
    Posts
    6

    Re: How to copy files from one folder to another and also how to delete files from sq

    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

  4. #4
    Arjay's Avatar
    Arjay is offline Moderator / MS MVP Power Poster
    Join Date
    Aug 2004
    Posts
    11,196

    Re: How to copy files from one folder to another and also how to delete files from sq

    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.

  5. #5
    Join Date
    Dec 2013
    Posts
    6

    Re: How to copy files from one folder to another and also how to delete files from sq

    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

  6. #6
    Arjay's Avatar
    Arjay is offline Moderator / MS MVP Power Poster
    Join Date
    Aug 2004
    Posts
    11,196

    Re: How to copy files from one folder to another and also how to delete files from sq

    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.

    Code:
    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

  7. #7
    Join Date
    Dec 2013
    Posts
    6

    Re: How to copy files from one folder to another and also how to delete files from sq

    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

  8. #8
    Arjay's Avatar
    Arjay is offline Moderator / MS MVP Power Poster
    Join Date
    Aug 2004
    Posts
    11,196

    Re: How to copy files from one folder to another and also how to delete files from sq

    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.

  9. #9
    Join Date
    Dec 2013
    Posts
    6

    Re: How to copy files from one folder to another and also how to delete files from sq

    Hi Arjay,

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

  10. #10
    Join Date
    Jan 2006
    Location
    Chicago, IL
    Posts
    14,946

    Re: How to copy files from one folder to another and also how to delete files from sq

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

    CodeGuru Article: Bound Controls are Evil-VB6
    2013 Samples: MS CODE Samples

    CodeGuru Reviewer
    2006 Dell CSP
    2006, 2007 & 2008 MVP Visual Basic
    If your question has been answered satisfactorily, and it has been helpful, then, please, Rate this Post!

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  


Azure Activities Information Page

Windows Mobile Development Center


Click Here to Expand Forum to Full Width

This is a CodeGuru survey question.


Featured


HTML5 Development Center