Visual Studio: Copy files post build
Introduction
Learn an alternate method to provide a new local database available within the application folder rather than dynamically creating a new database at runtime.
Visual Studio provides the ability to include a file in a project, set a property “Copy to Output Directory” with options Do not copy, Copy always, Copy if newer which is commonly used to copy a local database to the application folder (debug or release). This method is great for development, not production to provide a new blank or populated database to the application folder. Instead, steps will be presented to perform a runtime version of copy to output directory.
Base database
Decide if a blank database is needed with no tables, a database with tables, no data or a database with tables that are populated or a variation of these options. Place this database in the root folder of the project which may be included in the project or not, either way the following steps do not require the database to be part of the project.
Database folder
Decide on a folder name under the application folder (debug or release) were the database will be stored until needed e.g. name the folder databases or database.
Under project properties, Compile tab, click the button "Build Events". In Post-build input add the following.
if not exist $(TargetDir)\Database mkdir $(TargetDir)\Database
xcopy "$(ProjectDir)*.accdb" "$(TargetDir)Database" /Y
- The first command will create the database folder if non existing.
- Next command copies the database in the project root folder to the database folder indicated in the first command.
Copy database code
The following code provides the ability to copy the database in the database folder to the runtime application folder.
- FileName property represents the database path and filename to copy to the root folder of the application
- Change the database name to the name of the database used in the current application.
- ProductionDatabaseFileName represents where to copy the database too.
- Exists property indicates if the database exists in the root folder of the application folder
- CopyDatabase method performs a copy from the database folder to the application folder. The parameter overwrite if true overwrites the database in the application folder if it exists while passing false or nothing if the database exists in the application folder will not be overwritten.
Imports System.IO
Namespace HelperClasses
Public Class FileHelper
Inherits DatabaseException
Private Shared databaseFile As New FileInfo(FileName)
''' <summary>
''' Safe copy of database
''' </summary>
''' <returns></returns>
Public Shared ReadOnly Property FileName() As String
Get
Return Path.Combine(AppDomain.CurrentDomain.BaseDirectory, "Database", "Database1.accdb")
End Get
End Property
''' <summary>
''' Database name to use in application folder
''' </summary>
''' <returns></returns>
Public Shared ReadOnly Property ProductionDatabaseFileName() As String
Get
Return Path.Combine(AppDomain.CurrentDomain.BaseDirectory, databaseFile.Name)
End Get
End Property
''' <summary>
''' Determines if the database exists in the application folder
''' </summary>
''' <returns></returns>
Public Shared ReadOnly Property Exists() As Boolean
Get
Return File.Exists(ProductionDatabaseFileName)
End Get
End Property
''' <summary>
''' Copy clean database to application folder
''' </summary>
''' <param name="overwrite"></param>
Public Shared Sub CopyDatabase(Optional overwrite As Boolean = False)
mHasException = True
Try
If File.Exists(ProductionDatabaseFileName) Then
If overwrite Then
File.Delete(ProductionDatabaseFileName)
Else
mHasException = False
Exit Sub
End If
End If
databaseFile.CopyTo(ProductionDatabaseFileName)
mHasException = False
Catch ex As Exception
mLastException = ex
mHasException = True
End Try
End Sub
End Class
End Namespace
Base usage
The following copies the database in the database folder to the application folder.
FileHelper.CopyDatabase(True)
Preferred usage
- Here is an error occurs because the database is in use this is captured.
- All other errors are captured and reported
FileHelper.CopyDatabase(True)
If Not FileHelper.IsSuccessFul Then
If FileHelper.DatbaseInuse Then
MessageBox.Show("Please close the database and try again")
Else
MessageBox.Show(FileHelper.LastExceptionMessage)
End If
End If
Missing database
If the database is missing for any reason the copy operation can capture this and copy the database from the database folder to the application folder if this is fine with business requirements.
Private Sub Form1_Shown(sender As Object, e As EventArgs) Handles Me.Shown
If Not FileHelper.Exists Then
FileHelper.CopyDatabase(True)
End If
customerBindingSource.DataSource = DataOperations.LoadCustomers()
DataGridView1.DataSource = customerBindingSource
End Sub
Demonstration code
In the source code provided, in form shown (above) is used to ensure the database is presented. In a button data is loaded from the database in the application folder with the option to create a new database table which is very basic yet shows what is possible. Once data is loaded and changes are made pressing the button again copies the fresh database to the application folder and presents the fresh data. While running the project try having the database in the application folder open outside of the application.
Imports AccessDemo.Classes
Imports AccessDemo.HelperClasses
Public Class Form1
Private customerBindingSource As New BindingSource
Private Sub CopyDatabaseButton_Click(sender As Object, e As EventArgs) _
Handles CopyDatabaseButton.Click
DataGridView1.DataSource = Nothing
FileHelper.CopyDatabase(True)
If Not FileHelper.IsSuccessFul Then
If FileHelper.DatbaseInuse Then
MessageBox.Show("Please close the database and try again")
Else
MessageBox.Show(FileHelper.LastExceptionMessage)
End If
Else
If CreateTableCheckBox.Checked Then
If Not DataOperations.CreateTable() Then
MessageBox.Show($"{DataOperations.LastExceptionMessage}")
End If
End If
customerBindingSource.DataSource = DataOperations.LoadCustomers()
DataGridView1.DataSource = customerBindingSource
End If
End Sub
Private Sub Form1_Shown(sender As Object, e As EventArgs) Handles Me.Shown
If Not FileHelper.Exists Then
FileHelper.CopyDatabase(True)
End If
customerBindingSource.DataSource = DataOperations.LoadCustomers()
DataGridView1.DataSource = customerBindingSource
End Sub
End Class
Back end data operations
Imports System.Data.OleDb
Imports AccessDemo.HelperClasses
Namespace Classes
''' <summary>
''' Code to validate the copy database from sub folder to
''' application folder works.
''' </summary>
Public Class DataOperations
Inherits DatabaseException
Private Shared ConnectionString As String =
"Provider=Microsoft.ACE.OLEDB.12.0;" &
"Data Source=Database1.accdb"
''' <summary>
''' Standard read joined tables
''' </summary>
''' <returns></returns>
Public Shared Function LoadCustomers() As DataTable
Dim dt As New DataTable
Dim selectStatement =
<SQL>
SELECT
Cust.CustomerIdentifier,
Cust.CompanyName,
Cust.ContactId,
CT.ContactTitle,
Con.FirstName,
Con.LastName,
Cust.City,
Cust.PostalCode,
Cust.CountryIdentifier,
Cust.ContactTypeIdentifier,
CO.Name AS CountryName
FROM ((Customers AS Cust
INNER JOIN Contacts AS Con ON Cust.ContactId = Con.ContactId)
INNER JOIN ContactType AS CT ON Cust.ContactTypeIdentifier = CT.ContactTypeIdentifier)
INNER JOIN Countries AS CO ON Cust.CountryIdentifier = CO.CountryIdentifier;
</SQL>.Value
Using cn As New OleDbConnection With {.ConnectionString = ConnectionString}
Using cmd As New OleDbCommand With {.CommandText = selectStatement, .Connection = cn}
cn.Open()
dt.Load(cmd.ExecuteReader())
dt.Columns("CustomerIdentifier").ColumnMapping = MappingType.Hidden
dt.Columns("ContactId").ColumnMapping = MappingType.Hidden
dt.Columns("CountryIdentifier").ColumnMapping = MappingType.Hidden
dt.Columns("ContactTypeIdentifier").ColumnMapping = MappingType.Hidden
End Using
End Using
Return dt
End Function
''' <summary>
''' Simple example for creating a table.
''' Note inside of the access table the boolean column
''' will not show true/false but numeric values yet
''' does when read into a DataTable.
''' </summary>
''' <returns></returns>
Public Shared Function CreateTable() As Boolean
mHasException = False
Dim tableName = "Person"
Using cn = New OleDbConnection(ConnectionString)
Using cmd = New OleDbCommand("", cn)
cmd.CommandText =
$"CREATE TABLE {tableName} ([Id] COUNTER, [FirstName] TEXT(25)," &
"[LastName] TEXT(255), [ActiveAccount] YESNO, [JoinYear] INT)"
Try
cn.Open()
cmd.ExecuteNonQuery()
cmd.CommandText = $"INSERT INTO {tableName} " &
"(FirstName,LastName,ActiveAccount,JoinYear) VALUES ('Karen','Payne',?,?)"
cmd.Parameters.AddWithValue("?", 0)
cmd.Parameters.AddWithValue("?", Now.Year)
cmd.ExecuteNonQuery()
'
' Code that can be used to verify all is well,
' uncomment, place a breakpoint on the return statement,
' when breakpoint is hit hover over dt to see the row
'
'cmd.CommandText = $"SELECT FirstName,LastName,ActiveAccount,JoinYear FROM {tableName}"
'Dim dt As New DataTable
'dt.Load(cmd.ExecuteReader())
Return True
Catch e1 As Exception
mHasException = True
mLastException = e1
Return False
End Try
End Using
End Using
End Function
End Class
End Namespace
Summary
Code and logic provided to allow an application to not create a new database when a database is needed. There are variations, which can be worked through according to business requirements.
See also
Visual Studio: Copying files to Debug or Release folder
Source code
Clone or download from the following GitHub repository, build and run.