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.
使用VB。NET中，我成功地创建了一个新的数据库，并将数据从Excel文件导入到SQL Server 2012 Express数据库中。使用默认模式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.
我想为刚刚创建的数据库(例如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
2 个解决方案
#1
0
Here you go the below code snippet shows that you are creating with dbo schema
下面的代码片段显示您正在使用dbo模式创建。
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
相反，如果您希望用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.
请记住，在使用数据库的默认模式(通常是dbo)以外的模式创建表之后，您必须在执行诸如select、update、delete、insert等操作时指定模式名。
#2
0
Here is the code I find to successfully create a new schema in a SQL Server Express Database
下面是我在SQL Server Express数据库中成功创建新模式的代码
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
#1
0
Here you go the below code snippet shows that you are creating with dbo schema
下面的代码片段显示您正在使用dbo模式创建。
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
相反，如果您希望用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.
请记住，在使用数据库的默认模式(通常是dbo)以外的模式创建表之后，您必须在执行诸如select、update、delete、insert等操作时指定模式名。
#2
0
Here is the code I find to successfully create a new schema in a SQL Server Express Database
下面是我在SQL Server Express数据库中成功创建新模式的代码
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