views:

42

answers:

2

Hi,

We have a silverlight ASP .NET web application which needs to be deployed on client's server along with Sql Server database. Once they deploy on their server, many workstation can access it and run silverlight client.

I was thinking to create a small deployment project, add necessary script files to the resources, and create an msi Once after installation is completed, we can execute the sql scripts to add database and its tables. I am not sure if this is feasible, is there a better way of doing it? Also, if there are any future updates to the app / db, how can it be done on the server (silent update/install)?

Any links / steps / procedure is highly appreciated.

Thanks!

A: 

You can create the scripts using the MSI, and then you should be able to run them as part of the installation using sqlcmd or osql command line utility, you'll obviously need to let the user capture server name, db name and credentials as part of the install.

With updates to the db schema, you can do it through code in your application, which means you'll need to maintain a database version somewhere for the app to know when to run the script, or just script the relevant alter statements, and run it on the server as part of deployment, once again using one of the command line utilities.

baldy
A: 

Thanks for the reply Baldy. This is exactly what I thought and did a small test. I have a simple ASP .NET web application with one label, one class library project which has an overrides method Install (creates a batch file and executes it), wand a WebSetup project which actually installs and during installation it will execute the Install method from class library project. Here's the code -

1) ClassLibrary Project - MyCustomAction

<RunInstaller(True)> _ 
Public Class SetupAction
Inherits Installer

Public Overrides Sub Install(ByVal stateSaver As System.Collections.IDictionary)
    MyBase.Install(stateSaver)
    Try
        My.Computer.FileSystem.WriteAllText("E:\SetupTest.txt", Environment.NewLine + "File created from MyCustomAction project", True)
        'Shell("SQLCMD -S Dev1 -d Prac -i ""E:\Copy of CreateTable1.sql""", AppWinStyle.MinimizedFocus, True, 5000)
        File.WriteAllText("E:\Test.bat", "SQLCMD -S Dev1 -d Prac -i ""E:\CreateTable1.sql""")
        Process.Start("E:\Test.bat")
    Catch ex As Exception
        My.Computer.FileSystem.WriteAllText("E:\ErrorLog.txt", Environment.NewLine + "Exception: " + ex.Message, True)
    Finally

    End Try
End Sub
End Class

2) ASP .NET Project - MyApplication

Partial Public Class _Default
Inherits System.Web.UI.Page

Protected Sub Page_Load(ByVal sender As Object, ByVal e As System.EventArgs) Handles Me.Load
    Label1.Text = "Current Time: " + Now.ToString
End Sub

End Class

3) WebSetup Project - MyApplicationSetup

  • I have added the project output from the above projects to it
  • I have added a new "Tnstall" CustomAction referencing it to the output of MyCustomAction class library project
  • When I build the msi installer and install the Web Setup application it intalls (copies the output files), and also creates SetupTest.txt, Test.bat files but it neither executes the Shell command line statement nor Process.Start() successfully.
  • Once the bat file is created, if I manually double click it does execute the sql Script file.

As a side note, if I run CustomAction code in a separate Windows App, it executes perfectly fine. So, looks like while installation, its not able to execute the command line commands (though I do see cmd.exe / SQLCMD.exe in the task manager). I am not sure if this would be a permission issue, but I am in the admin group and have necessary permissions.

It may not be appropriate to write these comments in "My Answer" section, but wanted to give a detailed explanation of the situation. I am really stuck with this and would be very helpful if anyone can throw pointers on improving / alternate methods. Thanks in advance and really appreciate the help.

Vishal
I think I got the solution and it seem to work. The installer is able to install the output files, and execute the Shell() command to create the table.It turned out to be a Sql Server Permission issue for NT Authority\System login. I had to map this login to the database, grant create table permission along with datareader and datawriter. Hope this helps someone. Thanks!
Vishal