This project has moved and is read-only. For the latest updates, please go here.

DatabaseViewer UI

DatabaseViewer.exe is a very simple Windows Form.

UI

  • Select database provider from dropdown (e.g. System.Data.SqlClient). All the providers installed on your system are shown. 
  • Enter a connection string. Right click for a menu of sample connection strings for many databases.
  • Enter database schema (owner for Oracle). For many databases this is optional, but you should enter it for Oracle or it will read the Oracle system schema, which is very slow.
  • Click “Read Schema” button.

The treeview shows the database schema (tables, views, stored procedures, functions, users). Not all the information is displayed.

Tree Quick Scripting

On many of the database items shown in the tree you can right click for a scripting menu. In each case the script is copied to the windows clipboard. Simply paste into your database script tool (or Notepad).

imageimage

 

Code Generation



For code generation, click “Code gen” gears icon

imageUIcodegenWindow

  • Enter file path to write the files to (directory must exist, folder picker available)
  • Select what you want to generate:
    • C# code (classes, mapping etc.- see below)
    • Table DDL (“CREATE TABLE” statements plus create constraints)
    • SQL Procedures (CRUD-style stored procedures)
    • Data (Insert statements containing the table data – the first 200 rows of the table are read)
  • For C# code, enter namespace (should be a valid .Net namespace)
  • For C# code, check if you want to read stored procedures (see below)
  • For C# code, select which type of project (POCO, NHibernate with Hbm, Fluent NHibernate, EF Code First).
  • For SQL, select the SQL dialect. It doesn’t have to be the same as the original database (you can read a SQL-Server schema and output to Oracle). Note, SQLite and SQL-Server CE do not have stored procedures.
  • For SQL stored procedures and Data, select the table you want. 
    image
  • Click “Generate” button. One or more files will be written to your specified file path.

C# code Generation

This writes a C# class for each table, with each column as an automatic property. Relations between classes reflect the foreign key constraints. Composite keys are handled by creating key classes. Overrides for ToString, Equals and GetHashCode are added (the last two are recommended for NHibernate). The properties are decorated with DataAnnotations validation attributes.

It also writes mapping in a "mapping" subdirectory for NHibernate (hbm or Fluent) or EF Code First. The mapping is simple, and you probably will want to change this. It's just to get you started. If you don't need ORM mapping, select "Poco" (Plain old CLR objects).

The code generation also writes out simple proxy classes for stored procedures, creating ADO command objects with all the parameters done for you. If you check the “Read stored procedures”, it tries to work out the result sets and generates typed result classes. Using a stored procedure can be as simple as:

using (var con = new SqlConnection(connectionstring)) {
  var sproc = new SelectProductsForCategory(con);
  IEnumerable<SelectProductsForCategoryResult> listOfProducts = sproc.Execute(5);
} 



It also writes Visual Studio csproj files. For EF Code First (,Net 4 only) there is just one; for other types, there are two project files, one for VS2008 v3.5, the other for VS2010 v4, with the same name as the namespace. If you selected NHibernate Hbm mapping, the mapping files are correctly included as embedded resources. In practice, you'll probably include the class files in your own project.

SQL Table DDL

This writes the “CREATE TABLE” commands in the specified dialect. Constraints are also written.

It does not write triggers, views or stored procedures.

It is suitable for simple conversion of your database schema onto another platform (one of Sql Server, Sql Server CE,  Oracle, MySQL, SQLite, PostgreSql and IBM DB2). It only translates simple common SQL types, not database-specific types.

SQL Procedures

This writes CRUD-style stored procedures for a specified table– select by id, select all, select paged, insert, update and delete.

If you select Oracle dialect, they are all in a package.

Obviously this doesn’t apply to SQLite or SqlServer CE, because they doesn’t have stored procedures.

Schema compare

This compares two schemas- for instance, your development database against a test database. compare

(If you do schema compare in code, you can de-serialize an old schema to compare against).

From the main screen, click the “Compare” toolbar icon. Add the second connection string, and click the compare button.

If differences are found, the migration script is shown in a window. You can save the script to a file, or copy it to paste into your database admin UI.

Note that it will not compare different platforms, such as a SqlServer database against an Oracle database. In code this is possible, but rarely practical.

Last edited Feb 22, 2012 at 6:25 AM by Martinjw, version 7

Comments

No comments yet.