Home
SteveMoyer edited this page May 3, 2011
·
20 revisions
The goal of this project is to make it easier to manage and spin up development and functional test datasets as an alternative to trying to use difficult to version database backups or sql files.
In the process of creating these data sets you will have to understand the structure of your database(s).
- Generate csv files of all data needed to run an app
- Modify csv files(if desired)
- Convert csv files to sql files
- Run csv files against empty(or to be emptied) target database
- Run app against filled database
- Step 1:
- Option 1. Export an entire database(only reasonable for small database)
- Option 2. Build up a dataset from scratch and export to CSV(if only most apps worked starting with no data)
- Option 3. Define and script a workable set of data from a database directly to csv(most likely with non-new databases)
- Incrementally build filter scripts to decide what data you want in your data set
- Step 2:
- Add csv files to version control
- Step 1: Convert Csv Files to Sql
- Step 2: Execute sql against database
- Step 1:
- Option 1: Load the data and add additional data through your application
- Option 2: Enter additional data directly into the csv files
- Step 2:
- Commit changes to version control
- Step 1: Reset database to stable data set state
- Step 2: Run functional tests
nDump.Console.exe Usage:
-h | /h | -? | /? display this help
xml file options:
-f | -dp data plan file (required)
-o supply and options file to populate
run options:
-e Export (requires -sourceconnection and -csv)
-i Import (requires -sql and -targetconnection)
-t transform (requires -csv and -sql)
-csv csv file directory
-sql sql file directory
-sourceconnection source database connection string
-targetconnection target database connection string
-nofilter export all data from the source database without
Inline Options Sample:
nDump.exe -dp dataPlan.xml -sourceconnection "server=.;Integrated Security=SSPI;Initial Catalog=mydb" -csv .\csv\ -sql .\sql\ -targetconnection "server=.;Integrated Security=SSPI;Initial Catalog=emptymydb" -e -t -i
Options File Sample:
nDump.exe -dp dataPlan.xml -o myOptions.xml
DataPlan XML file format:
<DataPlan xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance" xmlns:xsd="http://www.w3.org/2001/XMLSchema">
<DataSelects>
<SqlTableSelect>
<ExcludedColumns />
<HasIdentity>true</HasIdentity>
<Select>select * from users where userid in (select userid from dbex_user) </Select>
<TableName>users</TableName>
</SqlTableSelect>
<SqlTableSelect>
<ExcludedColumns />
<HasIdentity>true</HasIdentity>
<Select>select * from groups </Select>
<TableName>groups</TableName>
</SqlTableSelect>
<SqlTableSelect>
<ExcludedColumns />
<HasIdentity>false</HasIdentity>
<Select>select * from groupmembership where userid in (select userid from dbex_user) </Select>
<TableName>groupmembership</TableName>
</SqlTableSelect>
</DataSelects>
<SetupScripts>
<SqlTableSelect>
<ExcludedColumns />
<HasIdentity>false</HasIdentity>
<Select>select top 5 * into dbex_user from [user] </Select>
<TableName>dbex_user</TableName>
</SqlTableSelect>
</SetupScripts>
</DataPlan>