使用 VB.NET 的 SQL Server 数据库新架构

Question

使用 VB.NET，我成功地创建了一个新数据库并将数据从 Excel 文件导入到 SQL Server 2012 Express 数据库的表中。新表是使用默认架构 dbo 创建的.

我想为刚刚创建的数据库创建一个新模式(即 cad )并将表分配给这个新模式。我在使用 VB.NET 时遇到了困难。下面是创建与 Excel 文件同名的数据库的代码。接下来是将 cad 模式添加到该数据库中。创建后，我可以导入 Excel 数据并将新架构包含在连接字符串中。

SQL_Server = Me.TxtServer.Text                          'assing variable for the SQL server
SQL_DBNam = Me.TxtDbName.Text                           'assign variable for the database name in the server
SQL_Table = Me.TxtInsertedTableName.Text                'assign variable for the table name in the database in the server
SQL_Schema = Me.TxtSchema.Text
'save the schema to registry in case it had been altered
SaveSetting("CAD SQUAD", SD_LogNam, "SQLSchema", SQL_Schema)

''connect to excel file...............xls................xls................xls....................xls..........

''32bit excel
'Dim ExcelConnection As New System.Data.OleDb.OleDbConnection("Provider=Microsoft.jet.OLEDB.4.0;Data Source=" & fullName & ";Extended Properties=Excel 8.0;")

''64bit excel
Dim ExcelConnection As New System.Data.OleDb.OleDbConnection("Provider=Microsoft.ACE.OLEDB.12.0;Data Source=" & fullName & ";Extended Properties=Excel 8.0;")

Try 'try 1 ------------------------------connection to excel file-----------------------------------------------
    ExcelConnection.Open()

    ''MsgBox("Excel connection open for file " & fullName)

    ''assign string to select all items from excel sheet
    Dim expr As String = "SELECT * FROM [" & XLS_Sheet & "]"

    ''MsgBox("String to connect to EXCEL table = " & expr)

    Dim objCmdSelect As OleDbCommand = New OleDbCommand(expr, ExcelConnection)
    Dim objDR As OleDbDataReader
    Dim DBExists As Byte = 0

    ''connect to SQL server (leave 'Database' parameter blank as it does not yet exist)
    ''check which type of connection
    IE_SrvStr = ServerConnString(CheckBox_TrustCon.Checked, "")

    'MsgBox("Server connect string: " & IE_SrvStr)

    IE_SrvConn = New SqlConnection(IE_SrvStr)


    Try 'try 2
        'open server connection
        IE_SrvConn.Open()

        'check if database exists (moved from above)==========================================================
        Dim DBObj As SqlCommand
        Dim DBStr As String

        Dim DB_Cmd As SqlCommand = New SqlCommand("SELECT case when exists (select 1 from sys.Databases where Name = @DbName) then 1 else 0 end as DbExists", IE_SrvConn)
        DB_Cmd.Parameters.AddWithValue("@DbName", SQL_DBNam)
        '==================================================

        DBExists = CByte(DB_Cmd.ExecuteScalar())

        If DBExists = 1 Then

            ListBoxEvent.Items.Add("Database " & SQL_DBNam & " already exists...")
            ListBoxEvent.SelectedIndex = ListBoxEvent.Items.Count - 1
            'MsgBox("Database " & SQL_DBNam & " already exists... OK to continue")
        Else
            ''create database
            DBObj = IE_SrvConn.CreateCommand()
            DBStr = "CREATE DATABASE " & SQL_DBNam

            ListBoxEvent.Items.Add("Database " & SQL_DBNam & " created successfuly...")
            ListBoxEvent.SelectedIndex = ListBoxEvent.Items.Count - 1
            'MsgBox(SQL_DBNam & " Database Created... OK to continue!")

            ''execute
            DBObj.CommandText = DBStr
            DBObj.ExecuteNonQuery()

        End If

        IE_SrvConn.Close()
            Try 'try 3


                'and open server
                IE_SrvConn.Open()

                ''check if table exists+++++++++++++++++++++++++++++++++++++++++++++++++++++

                Dim restrictions(3) As String
                restrictions(2) = SQL_Table
                Dim dbTbl As DataTable = IE_SrvConn.GetSchema("Tables", restrictions)

                If dbTbl.Rows.Count = 0 Then
                    'Table does not exist
                    'DoesTheTableExist = False

                    Dim TBObj As New SqlCommand
                    Dim TBStr As String

                    TBObj = IE_SrvConn.CreateCommand()
                    ''the .cad schema is what I want to assing to the table but 
                    ''it errors out: shcema not available or you do not have permissions 
                    ''when using the .dbo it works fine
                    'TBStr = "CREATE TABLE " & SQL_DBNam & ".cad" & ". " & SQL_Table & "(" & _

                      TBStr = "CREATE TABLE " & SQL_DBNam & ".dbo" & ". " & SQL_Table & "(" & _
                      "LayIdx int NOT NULL PRIMARY KEY, " & _
                      "New_LayNam VARCHAR(255), " & _
                      .
                      .
                      .

                      "LayDescription VARCHAR(255)" & _
                      ") "

                    'MsgBox("Table parameters: " & TBStr)
                    ' Execute
                    TBObj.CommandText = TBStr
                    'MsgBox("TBOBJ.CommandText = initiated command!")
                    TBObj.ExecuteNonQuery()
                    'MsgBox("TBOBJ.ExecuteNonQuery()-executed! now see if table is available...")
                Else
                    ''table exists; (option: ask if want to delete it and replace with new....)
                    ListBoxEvent.Items.Add("Table " & SQL_Table & " already exists...")
                    ListBoxEvent.SelectedIndex = ListBoxEvent.Items.Count - 1
                    'MsgBox("Table " & SQL_Table & " exists...OK to Continue!")
                End If

                dbTbl.Dispose()

                ''check if record exists, means table has already been populated

                'MsgBox("Find records on the table...")

                Dim tblRecs As String = "SELECT * FROM " & SQL_Table
                Dim tblCmd As SqlCommand = New SqlCommand(tblRecs, IE_SrvConn)

                Using RReader As SqlDataReader = tblCmd.ExecuteReader()
                    If RReader.HasRows Then
                        RReader.Close()
                        ListBoxEvent.Items.Add("Table data is already imported...")
                        ListBoxEvent.SelectedIndex = ListBoxEvent.Items.Count - 1
                        'MsgBox("Table is already populated...OK to Finish!")
                    Else
                        ''propulate table
                        RReader.Close()
                        'MsgBox("SQL_Table exists but has not records... OK to Import Data!")

                        'importing from excel
                        Using bulkCopy As SqlBulkCopy = New SqlBulkCopy(IE_SrvConn)

                            bulkCopy.DestinationTableName = SQL_Table

                            Try 'try 4
                                objDR = objCmdSelect.ExecuteReader
                                bulkCopy.WriteToServer(objDR)

                                ExcelConnection.Close()
                                IE_SrvConn.Close()
                                ListBoxEvent.Items.Add("Data import successful!")
                                ListBoxEvent.SelectedIndex = ListBoxEvent.Items.Count - 1
                            Catch ex As Exception
                                MsgBox("Error importing to table " & SQL_Table & ": " & ex.ToString)
                            End Try 'try 4 close

                        End Using

                    End If

                End Using


            Catch ex As Exception
                MsgBox("Error creating table: " & SQL_Table & " in database: " & SQL_DBNam & " [" & ex.Message & "]")
                Exit Sub
            Finally
                IE_SrvConn.Close() 'Whether there is error or not. Close the connection.
                ExcelConnection.Close()
                ListBoxEvent.Items.Add("Closing connection to server " & SQL_Server)
                ListBoxEvent.SelectedIndex = ListBoxEvent.Items.Count - 1
                'MsgBox("Connection to Server " & SQL_Server & " closed!")
            End Try 'try 3 close

Answer 1

下面的代码片段显示您正在使用 dbo 模式创建

  TBStr = "CREATE TABLE " & SQL_DBNam & ".dbo" & ". " & SQL_Table & "(" & _
                      "LayIdx int NOT NULL PRIMARY KEY, " & _
                      "New_LayNam VARCHAR(255), " & _
                      .
                      .
                      .

                      "LayDescription VARCHAR(255)" & _
                      ") "

相反，如果您希望使用 cad 架构创建它，那么您必须使用如下语句

  TBStr = "CREATE TABLE " & SQL_DBNam & ".cad" & ". " & SQL_Table & "(" & _
                      "LayIdx int NOT NULL PRIMARY KEY, " & _
                      "New_LayNam VARCHAR(255), " & _
                      .
                      .
                      .

                      "LayDescription VARCHAR(255)" & _
                      ") "

remember, once the table is created with a schema other than the default schema of the database (usually dbo), then you have to specify the schema name while doing any operations such as select, update, delete, insert on the table.