Create Database Template (request)

[sbc]

What I have not seen is a template that generates a script that can create a database.

Say you have an Access database. You want to create a script that will create an identical (or close to) copy, but for SQL Server. Or you want SQLite, PostgreSQL etc.

What I want to do is copy over an MSDE database (minus records), to a new MSDE instance (which is not set up yet on the new machine). The servers cannot see each other, and so you want to create an installer that installs MSDE, adds the users (i.e. local machine ASPNET, or IUSR_MachineName account), runs a script to add the database and copies all the files you need for your application. Once installed the application can run straight away, no configuration needed. A template that could generate a script that can create the database could save a lot of time.

Of course, you can use Enterprise Manager to create the script, but what if you don't have it (i.e. use MSDE) and have to use the osql tool to manage the database? When I look at the script generated, it seems a bit verbose (instead of specifiying the filename, growth etc, just create it using the defaults). All I want is

CREATE Database [foo]
CREATE Table [bar] (
...
)
CREATE INDEX...

etc

If it is simple, and uses SQL-92 compatible syntax it should be pretty portable too.

[ashish]

Hi,

I have created one template which creates tables, stored procedures,
PKs, FKs etc.

Currently I have tested this only for MS SQL 2000.

Link for Template is :

http://www.mygenerationsoftware.com/Tem ... 972a8ec71a

Feel free for any question or problem.

[sbc]

How about adding a UI to select the database? Template seems to work fine for me.
Interface code:

Code: Select all

Public Class GeneratedGui
   Inherits DotNetScriptGui

   Public Sub New(context As ZeusContext)
      MyBase.New(context)
   End Sub

   '-----------------------------------------
   ' The User Interface Entry Point
   '-----------------------------------------
   Public Overrides Sub Setup
      ui.Width  = 250
      ui.Height = 140
      ui.Title = \"Create Table Script\"
      Dim lblDBSelect As GuiLabel = ui.AddLabel(\"lblDBSelect\", \"Select Database\", \"Select Database\")
      Dim cmbDatabases As GuiComboBox = ui.AddComboBox(\"cmbDatabase\", \"Select a database.\")
      ' Attach the onchange event to the cmbDatabases control.
      setupDatabaseDropdown(cmbDatabases)
      cmbDatabases.AttachEvent(\"onchange\", \"cmbDatabases_onchange\")
      ui.ShowGui = true
   End Sub
   
   Public Sub setupDatabaseDropdown(cmbDatabases)
      cmbDatabases.BindData(MyMeta.Databases)
      If Not MyMeta.DefaultDatabase Is Nothing Then
         cmbDatabases.SelectedValue = MyMeta.DefaultDatabase.Name
      End If
   End Sub

End Class

And in template code, replace For Each table in MyMeta.DefaultDatabase.Tables with For Each table in MyMeta.Databases(input.Item(\"cmbDatabase\")).Tables

[ashish]

Thanks, I forgot to give DB selection....

I have upated template with db selection facility. I am to hear that my template worked for you.