databasecontrol.ascx.vb

C#语言制作asp.net网上商店的

Imports Microsoft.VisualBasic
Imports System
Imports System.Data
Imports System.Configuration
Imports System.Collections
Imports System.Web
Imports System.Web.Security
Imports System.Web.UI
Imports System.Web.UI.WebControls
Imports System.Web.UI.WebControls.WebParts
Imports System.Web.UI.HtmlControls
Imports System.Data.SqlClient
Imports System.IO
Imports System.Text.RegularExpressions
Imports NetShopForge.Common.Utility
Partial Class Install_Control_DatabaseControl
    Inherits System.Web.UI.UserControl


    'Timeout of batches (in seconds)
    Private Const timeout As Integer = 600


    Private Function GetConnString(ByVal tableName As String) As String
        Dim sCString As String = "Server=" & txtServer.Text & ";Initial Catalog=" & tableName & ";"
        If chkTrusted.Checked Then
            sCString &= "Integrated Security=true;"
        Else
            sCString &= "user id=" & txtUsername.Text & "; Password=" & txtPassword.Text & ";"

        End If
        Return sCString

    End Function
    Private Sub BindDBList()
        'get the list of DBs from the username bits
        Using conn As SqlConnection = New SqlConnection()

            'the DBList query
            Dim sql As String = "SELECT * FROM sysdatabases WHERE name NOT IN ('master','tempdb','model','msdb') ORDER BY [NAME]"

            conn.ConnectionString = GetConnString("master")

            Try
                conn.Open()
                Using cmd As SqlCommand = New SqlCommand(sql, conn)
                    Using rdr As SqlDataReader = cmd.ExecuteReader(CommandBehavior.CloseConnection)
                        ddlDB.DataSource = rdr
                        ddlDB.DataTextField = "name"
                        ddlDB.DataValueField = "name"
                        ddlDB.DataBind()

                        rdr.Close()
                    End Using
                End Using
            Catch x As Exception
                ResultMessageControl1.ShowFail(x.Message)
            End Try
        End Using
    End Sub
    Protected Sub chkTrusted_CheckedChanged(ByVal sender As Object, ByVal e As EventArgs)
        pnlSqlMode.Visible = Not chkTrusted.Checked
    End Sub
    Protected Sub btnCreate_Click(ByVal sender As Object, ByVal e As EventArgs) Handles btnCreate.Click
        'get the list of DBs from the username bits
        Using conn As SqlConnection = New SqlConnection()

            'the DBList query
            Dim sql As String = "CREATE DATABASE " & txtCreateDB.Text & " COLLATE SQL_Latin1_General_CP1_CI_AS"

            conn.ConnectionString = GetConnString("master")

            Try
                conn.Open()
                Using cmd As SqlCommand = New SqlCommand(sql, conn)
                    cmd.ExecuteNonQuery()

                    BindDBList()

                    'select the one that was just created
                    ddlDB.SelectedValue = txtCreateDB.Text
                End Using
            Catch x As Exception
                Dim sMessage As String = x.Message
                If x.Message.Contains("permission denied") Then
                    sMessage = "The account that you're connecting to SQL Server with is not allowed to create a Database. " & "To correct this, you can use SQL Authentication, or you can manually create your database using " & "the SQL Management Studio Express. If you don't have this, you can download it from Microsoft"
                End If

                ResultMessageControl1.ShowFail(sMessage)
            End Try
        End Using
    End Sub
    Protected Sub btnSetServer_Click(ByVal sender As Object, ByVal e As EventArgs)
        BindDBList()
        pnlDB.Visible = True
        pnlInstall.Visible = True
    End Sub
 

    Private Sub RunScriptFile(ByVal fileName As String, ByVal connectionString As String)
 
  

        Dim conn As SqlConnection = Nothing
        Try
            My.Response.Write([String].Format("Opening file {0}<BR>", fileName))

            ' read file
            Dim sr As New StreamReader(New FileStream(fileName, FileMode.Open))
            Try
                My.Response.Write("Connecting to SQL Server database...<BR>")

                ' Create new connection to database
                conn = New SqlConnection(connectionString)

                conn.Open()

                While Not sr.EndOfStream
                    Dim sb As New StringBuilder()
                    Dim cmd As SqlCommand = conn.CreateCommand()

                    While Not sr.EndOfStream
                        Dim s As String = sr.ReadLine()
                        If Not (s Is Nothing) And s.ToUpper().Trim().Equals("GO") Then
                            Exit While
                        End If

                        sb.AppendLine(s)
                    End While

                    ' Execute T-SQL against the target database
                    cmd.CommandText = sb.ToString()
                    cmd.CommandTimeout = timeout

                    cmd.ExecuteNonQuery()
                End While
            Finally
                sr.Dispose()
            End Try
            My.Response.Write("T-SQL file executed successfully")
        Catch ex As Exception
            My.Response.Write([String].Format("An error occured: {0}", ex.ToString()))
        Finally
            ' Close out the connection
            '
            If Not (conn Is Nothing) Then
                Try
                    conn.Close()
                    conn.Dispose()
                Catch e As Exception
                    My.Response.Write([String].Format("Could not close the connection.  Error was {0}", e.ToString()))
                End Try
            End If
        End Try


    End Sub

    

    Private Sub SetWebConfig()
        Dim configPath As String = Server.MapPath("~/Web.Config")
        Dim sConfig As String = NetShopForge.Common.Utility.GetFileText("../Web.Config")

        'write them back
        NetShopForge.Common.Utility.UpdateFileText(configPath, "STOREDATASOURCEUNSET", GetConnString(ddlDB.SelectedValue).Replace("&", "&amp;"))
        NetShopForge.Common.Utility.UpdateFileText(configPath, "MEMBERSHIPDATASOURCEUNSET", GetConnString(ddlDB.SelectedValue).Replace("&", "&amp;"))
    End Sub

    Private Function GetExecutionPlan(ByVal filePath As String) As String()

        Dim sql As String = NetShopForge.Common.Utility.GetFileText(filePath)
        Dim SqlLine As String()
        Dim regex As Regex = New Regex("^GO", RegexOptions.IgnoreCase Or RegexOptions.Multiline)

        SqlLine = regex.Split(sql)

        Return SqlLine
    End Function

    Protected Sub Page_Load(ByVal sender As Object, ByVal e As System.EventArgs) Handles Me.Load

    End Sub

    Protected Sub btnInstall_Click(ByVal sender As Object, ByVal e As System.EventArgs) Handles btnInstall.Click
        pnlServer.Visible = False
        pnlDB.Visible = False
        pnlInstall.Visible = False

        'get the scripts from the InstallScripts directory
        Dim fileName As String = Server.MapPath("~/App_data/script.sql")

        My.Response.Write("<h4>Installing CSK 2.0 Database into Server " & txtServer.Text & "; Database " & ddlDB.SelectedValue & "...</h4>")

        Dim connectionString As String = GetConnString(ddlDB.SelectedValue)
        Using conn As SqlConnection = New SqlConnection(connectionString)

            Dim haveError As Boolean = False

            'open the database.  
            Try
                conn.Open()
            Catch
                ResultMessageControl1.ShowFail("未与信任 SQL Server 连接相关联. If you are using a Trusted " & "Connection, this is because you have not given the ASP.NET worker process the right priviliges to your database. " & "Please <a href=help.aspx>READ THIS first</a>.")
                haveError = True
                Return
            End Try

            'run CSK Scripts
            Try
                RunScriptFile(fileName, connectionString)
            Catch ex As Exception
                ResultMessageControl1.ShowFail("Could not add the CommerceServerKit objects to the selected CSK Database.<br> " & "Connection String:" & connectionString & "<BR>" & "The following error occurred:<br>" & ex.Message)
                haveError = True
                Return
            End Try

            'Set web.config file
            Try
                ' SetWebConfig()
            Catch ex As Exception
                ResultMessageControl1.ShowFail("Could not updatethe CommerceServerKit web.config file with the correct settings.<br> " & "Please update the CSK web.config file manually. <BR>" & "The following error occurred:<br>" & ex.Message)
                haveError = True
                Return
            End Try

            If (Not haveError) Then
                Response.Write("<br><br><h2><a href='../Default.aspx'>Click Here To Access Your Portal</a></h2><br><br>")
            End If

        End Using
    End Sub

End Class