Deploying a SQL Database to a Remote Host by using ASP.NET 2.0 callbacks (vb code behind)

The Default.aspx.vb code:

' **************************************************************************
' Sample code for deploying a SQL Database to a Remote Host executing a T-SQL
' code using an ASP.NET 2.0 callbacks. Written as a sample with use in conjuction
' with the SQL Server Database Publishing Wizard.
' For more information visit http://mastering-asp-dot-net.blogspot.com.
' -------------------------------------------------------------------------------------------
' Copyright (C) Sergey Sukhotinsky, phystech@gmail.com. All rights reserved.
' **************************************************************************
' Note: Please ensure that you delete this page with the code behind once
' your database has been published to the remote server.
' **************************************************************************
Imports System.Data.SqlClient

Partial Class _Default
Inherits System.Web.UI.Page
Implements System.Web.UI.ICallbackEventHandler

' Get the connection string named "DB" from "web.config" file.
' Replace the "DB" with the name of the your datyabase connection.
Private strConnectionString As String = _
ConfigurationManager.ConnectionStrings("DB").ConnectionString

' Declare shared connection var. The connection must remain the same for all the callbacks.
Shared conn As SqlConnection = Nothing
Shared cmd As SqlCommand

' ASP.NET 2.0 callbacks related stuff. Google with "ASP.NET 2.0 callbacks" to find out how it works.
Private cs As ClientScriptManager = Page.ClientScript
Private _callbackArg As String


Protected Sub Page_Load(ByVal sender As Object, ByVal e As System.EventArgs) Handles Me.Load
Dim CallbackRef As String = _
Page.ClientScript.GetCallbackEventReference(Me, "arg", "ProcessCallBack", "context")
End Sub


Public Sub RaiseCallbackEvent(ByVal eventArgument As String) Implements ICallbackEventHandler.RaiseCallbackEvent
_callbackArg = eventArgument
End Sub


Function GetCallbackResult() As String Implements ICallbackEventHandler.GetCallbackResult
'
Dim strCallbackCommand As String = Left(_callbackArg, 2)
Dim strCallbackCommandText As String = Mid(_callbackArg, 3)

Select Case strCallbackCommand

Case Is = "01" ' Open the connection
Dim strCallbackMsg As String = "11 Connection opened; need the same shared connection through all the requests of the batch; we are creating the database, not using it."
Try
conn = New SqlConnection(strConnectionString)
conn.Open()
cmd = conn.CreateCommand
Catch ex As Exception
strCallbackMsg = String.Format("21 Could not open the connection. Error was {0}", ex.ToString())
End Try
Return strCallbackMsg

Case Is = "02" ' Close out the connection
If (conn IsNot Nothing) Then
Try
conn.Close()
conn.Dispose()
Catch ex As Exception
Me.Response.Write(String.Format("Could not close the connection. Error was {0}", ex.ToString()))
End Try
End If
Return "12 Connection closed"

Case Is = "03" ' Execute T-SQL against the target database
cmd.CommandText = strCallbackCommandText
cmd.CommandTimeout = 600
Try
cmd.ExecuteNonQuery()
Catch e As SqlException
' Handle exception.
Return "14 " & e.Message
Finally
End Try
Return "13 Succeded"

Case Else
Return "00_Unrecognized command"
End Select
End Function

End Class

Deploying a SQL Database to a Remote Host by using ASP.NET 2.0 callbacks (web.config)

The web.config code:




connectionString="Data Source=.\SQLEXPRESS;
AttachDbFilename=|DataDirectory|\DB.mdf;
Integrated Security=True;User Instance=True"
providerName="System.Data.SqlClient" />

Deploying a SQL Database to a Remote Host by using ASP.NET 2.0 callbacks (screen shot)

Deploying a SQL Database to a Remote Host by using ASP.NET 2.0 callbacks

Though The Database Publishing Wizard can provide separate scripts for diferent objects and current PCs are pretty powerful, the copy / paste approach is unlikely to handle more than tenths of megabytes of script. But for many developers it would be okay in many cases. In next versions I hope I'll overcome the limitation.

You are uploading an .aspx page to a remote host and pointing Internet Explore to the page; then the application creates the database.

The difference to http://www.codeplex.com/sqlhost is that you need no uploading a SQL script to the host; you are supplying the .aspx page with the SQL script through the ASP.NET 2.0 Callbacks mechanism.

The drawback is that you can’t paste big SQL scripts into the text box; the advantage is that you can track the process of Script execution. Also when you upload a content of a table, you may not repeat the lengthy

INSERT [dbo].[SomeLengthyName] ([ApplicationGuidId], [UserGuidId], [SomeOtherGuidId], [YetAnotherGuidId], [index], SomeIntValue VALUES ('497705fc-6bb3-4f9d-90d8-d1a067c47348', [OtherGuid], [OtherGuid], [OtherGuid], [OtherGuid], 33, 7)

It’s a bit overkill to send two hundred bytes to just populate a field with, say number one 7. The command could be kind of “repeat previous” – two bytes plus two bytes - number of chars; this gives 4 bytes instead of 200 and plus the rest of a string. But that’s yet to be implemented.

So, have a look at a screen shot in a previous this blog’s message and if you decided to give a try to the idea, try it with you local database first:
- create some ASP.NET 2.0 project on your PC using VS.NET 2.0.
- create SQL database; I named it “DB”;
- edit connectionString in your “web.config” file to point to the database;
- copy Default.aspx code from one of the previous messages of this blog; replace your Default.aspx code with it;
- copy Default.aspx.vb code-behind from one of the previous messages of this blog; replace your Default.aspx.vb code-behind with it;
- launch the application;
- get The Database Publishing Wizard’s .sql file to some text editor, copy it to clipboard;
- paste the SQL script into the application’s textbox;
- click “Process” button.

The rest you can discover on you own, examining the controls and the code. Good Luck.