Articles

Company News

2 April 2009

Search Plus released! BV Commerce add-on that adds an option of searching by category and by custom pages More info

Writing Simple Database Installation

You completed developing database-driven application and now you have to develop an installation form or a web page. There are many ways to make database installation. Some developers prefer distributing the database file, as the easiest way asking user or writing a code for database attaching. But often - if you are developing web application, for example - user won't have enough privileges to do it. The most universal way is to write a small code that will make Microsoft SQL Server run a SQL installation script. Here you will find steps to create such installation.

Genareate SQL script containing database objects (tables, views, stored procedures etc) using Microsoft SQL Server.
Start SQL Populator.
Choose desired tables.
Check 'Add GO after each statement'. Select SQL script file generated by SQL Server. Choose 'Append file'.
Script file is done.

Now you all you need to do is to write simple code to run the script. Here is sample code in VB.Net:

'This procedure tries to connect to Microsoft SQL server and starts scripting running procedure.
Private sub Install(ByVal ConnectionString as string)
      Dim conn As New SqlConnection(ConnectionString)
      conn.Open()
          If conn.State = ConnectionState.Open Then
              RunDataBaseScript(conn)
              conn.Close()
          Else
              msg.message = "Could not connect to server."
              Return
          End If
End Sub

'This procedure sets script execution time limit to maximum and starts ExecuteSQLScript procedure.
Private Sub RunDataBaseScript(ByRef conn As SqlConnection)
          Server.ScriptTimeout = Integer.MaxValue
          ExecuteSQLScript(conn)
End Sub

'This procedure reads sql instruction till Go statement and removers unnecessary symbols from script using regular expressions.
Private Sub ExecuteSQLScript(ByRef conn As SqlConnection)
    Dim fsreader As New StreamReader(Server.MapPath(ScriptFilePath))
        Dim query As String
        Dim strline As String = fsreader.ReadLine()
        While (Not strline Is Nothing)
            strline = Regex.Replace(strline, "^[ \t]+", "")
            Dim i As Integer = strline.IndexOf("--")
            If i <> -1 Then
                strline = strline.Substring(0, i)
            End If
            If strline <> "" Then
                If strline.Substring(0, 1) <> "-" Then
                    If strline <> "GO" Then
                        query &= " " & strline
                    Else
                        ExecuteNonQuery(query, conn)
                    End If
                End If
            End If
            strline = fsreader.ReadLine()
        End While
    End Sub

'This procedure runs SQL script.
Private Sub ExecuteNonQuery(ByRef query As String, ByRef conn As SqlConnection)
        Dim command As SqlCommand = New SqlCommand(query, conn)
        command.ExecuteNonQuery()
        command.Dispose()
        command = Nothing
        query = ""
End Sub