SQL Server database new schema using VB.NET

Question

Using VB.NET, I successfully created a new database and imported data into a table from an Excel file into a SQL Server 2012 Express database. The new table is created with the default schema dbo.

I would like to create a new schema for the database just created (i.e. cad) and assign the table to this new schema. I am having difficulty doing this using VB.NET. Here is the code that creates the database with the same name as the Excel file. Next would be to add the cad schema to this database. Once created I can then import the Excel data and include the new schema in the connection string.

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

I dont see any place in the above code, where the table is getting created, can you please check if you have posted the whole code or else can you point to the line number where the table is being created. — Surendra, Jul 26 '14 at 21:30

score 0 · Answer 1 · answered Jul 27 '14 at 21:48

Here you go the below code snippet shows that you are creating with dbo schema

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

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

instead if you want it to be created with cad schema then you have to use the statement as below

  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.

Thank you for your reply, I did include the .cad schema when calling to create the table. This module creates a database and then a table under that database, therefore the cad schema does not yet exist as part of the security on the newly created database, therefore when I create the table using the .cad schema I received an error: [The specified schema name "cad" either does not exist or you do not have permission to use it]. It seems the cad schema needs to be created after creating the database and before creating the table. This is what I am having trouble with. — CadSquad, Jul 28 '14 at 01:25
One thought this might work is using VB.NET to create a script in SQL to add the .cad schema and then run the script after the code creates the database. Next creating the table using .cad should work as the schema already exists in the database. How to go about doing this? — CadSquad, Jul 29 '14 at 19:18

score 0 · Answer 2 · answered Oct 03 '14 at 18:31

Here is the code I find to successfully create a new schema in a SQL Server Express Database

Imports Microsoft.SqlServer.Management.Smo
Imports Microsoft.SqlServer.Management.Common
Imports System.Data.SqlClient

Module DBSchema
    Public Sub TableSchema(SrvName As String, DBaseName As String, SchName As String, SchOwner As String)
        Dim connectionString As String = "Data Source=" & SrvName & ";Initial Catalog=" & DBaseName & ";Integrated Security=SSPI;"
        Dim connection As New SqlConnection(connectionString)
        ' do not explicitly open connection, it will be opened when Server is initialized
        connection.Open()
        Dim serverConnection As New ServerConnection(connection)
        Dim server As New Server(serverConnection)
        Console.Write("server connection defined...")
        ' after this line, the default database will be switched to Master
        Dim database As Database = server.Databases(DBaseName)
        ' if you want to execute a script against this database, you have to open 
        ' another connection and re-initiliaze the server object
        server.ConnectionContext.Disconnect()
        connection = New SqlConnection(connectionString)
        serverConnection = New ServerConnection(connection)
        server = New Server(serverConnection)
        'Create the schema on the instance of SQL Server.
        Dim sch As Schema
        sch = New Schema(database, SchName)
        sch.Owner = SchName
        sch.Create()
        connection.Close()
    End Sub
End Module

SQL Server database new schema using VB.NET

2 Answers2