MySQL Forums
Forum List  »  MySQL for Visual Studio

Column List
Posted by: Eric Adolphson
Date: November 10, 2018 03:28PM

I am using Visual Studio 2017, MySQL Server 5.7.2.2, MySQL for Visual Studio 2.0.5, MySQL Connector/Net 6.9.9, and MySQL WorkBench 6.3.10. I am having problems getting the list of columns for a table.

In MySQL Workbench, I can do it using one of the three commands:
1) SHOW COLUMNS FROM `tablename`
2) SELECT column_name FROM information_schema.columns WHERE table_name = 'companyroles'
3) SELECT * FROM information_schema.columns WHERE table_name = 'tablename'

Option 1 gives basic info on all the columns in my table, column name, type…
Option 2 gives a table with just the column_name column, populated with my table’s column names. It is the simplest.
Option 3 gives full info on all the columns in my table, like option 1 on steroids.

I can use any of the three options. In Visual Studio I have:

Public Const errCannotOpen As Integer = -2147467259
Public Const errCannotModify As Integer = -2147217911

Public Const MaxTries As Integer = 10
Public Const WaitMilliSeconds As Integer = 1000

Private Const efCloseExp As String = "Error closing connection. {0}{1}Server: {2}{1}Database: {3}"

Public Shared Function TableInfo(MySqlConn As MySql.Data.MySqlClient.MySqlConnection, TableName As String) As DataTable

' gets the table info for desired table in a datatable
'
' vars passed:
' MySqlConn - MySql connection to use
' TableName - table name to find
'
' returns:
' DataTable - populated with info about the desired table
' Nothing -
' Table was not found
' Error in find

' SHOW COLUMNS FROM `<tablename>`
'Const ShowColumnsFormat As String = "SHOW COLUMNS FROM `{0}`"
' SELECT column_name FROM information_schema.columns WHERE table_name = '<tablename>'
'Const GetColumnListFormat As String = "SELECT column_name FROM information_schema.columns WHERE table_name = '{0}'"
' SELECT * FROM information_schema.columns WHERE table_name = '<tablename>'
Const GetColumnListFormat As String = "SELECT * FROM information_schema.columns WHERE table_name = '{0}'"

Dim MySqlCommand As MySql.Data.MySqlClient.MySqlCommand = Nothing ' sql command
Try
If MySqlConn Is Nothing Then ' if no connection
Return Nothing ' return no scheme table
End If
If TableName = String.Empty Then ' if no table name
Return Nothing ' return no scheme table
End If

Dim TryCount As Integer = 0
Dim SqlText As String = String.Format(GetColumnListFormat, TableName.ToLower) ' get sql command text
Do
Try
Dim ErrMsg As String = String.Format("Error getting schema for table: {0} in database: {1} on server: {2}.",
TableName, MySqlConn.Database, MySqlConn.DataSource) ' get ErrMsg
Dim ReaderObj As MySql.Data.MySqlClient.MySqlDataReader = ExecuteReader(MySqlConn, MySqlCommand, SqlText, ErrMsg, "Error Getting Table Schema")
If ReaderObj Is Nothing Then ' if something went wrong
Return Nothing ' return no scheme table
Else ' else got a return value
If ReaderObj.HasRows Then ' if got rows in return object
Return ReaderObj.GetSchemaTable ' return the schema table
Else ' else no rows
Return Nothing ' return no scheme table
End If
End If
Catch exMySql As MySql.Data.MySqlClient.MySqlException
If (exMySql.ErrorCode = errCannotOpen) AndAlso (TryCount < MaxTries) Then
TryCount += 1
System.Threading.Thread.Sleep(WaitMilliSeconds)
Else
Return Nothing ' return no scheme table
End If
Catch ex As Exception
Return Nothing ' return no scheme table
End Try
Loop
Catch ex As Exception
Return Nothing ' return no scheme table
Finally
Try
If MySqlCommand IsNot Nothing Then ' if got a command
If MySqlCommand.Connection.State <> ConnectionState.Closed Then ' if connection not closed
MySqlCommand.Connection.Close() ' close the connection
End If
MySqlCommand.Dispose() ' free sql command
MySqlCommand = Nothing ' set sql command to nothing
End If
Catch ex1 As Exception
MessageBox.Show(String.Format(efCloseExp, ex1.Message, vbCrLf, MySqlConn.DataSource, MySqlConn.Database),
"Cannot Close Connection Error", MessageBoxButtons.OK, MessageBoxIcon.Error)
End Try
End Try
End Function

Public Shared Function ExecuteReader(ByVal MySqlConn As MySql.Data.MySqlClient.MySqlConnection,
ByRef MySqlCommand As MySql.Data.MySqlClient.MySqlCommand,
ByVal SqlText As String,
ByVal ErrMsg As String,
ByVal ErrTitle As String) As MySql.Data.MySqlClient.MySqlDataReader

' executes a mySql reader command
'
' NOTE: if the sub MySqlCommand.Connection.Close() is called, data in the returned ReaderObject will be freed.
' so, pass MySqlCommand as a ByRef param, and the calling sub/func needs to close the connection.
'
'
' vars passed:
' MySqlConn - MySql connection to use
' MySqlCommand *** passed ByRef *** - mySql command to use
' SqlText - SqlText to use
' ErrMsg - error message to display
' ErrTitle - error title for error in executing non query
'
' returns:
' reader object - MySql.Data.MySqlClient.MySqlDataReader
' nothing - something went wrong

Try
If MySqlConn Is Nothing Then ' if no connection
Return Nothing ' return nothing
End If
If MySqlCommand IsNot Nothing Then ' if got a command
If MySqlCommand.Connection.State <> ConnectionState.Closed Then ' if connection not closed
MySqlCommand.Connection.Close() ' close the connection
End If
MySqlCommand.Dispose() ' free sql command
MySqlCommand = Nothing ' set sql command to nothing
End If
Catch ex As Exception
Return Nothing
End Try

Dim ReaderObject As MySql.Data.MySqlClient.MySqlDataReader = Nothing ' reader return value
Dim Done As Boolean = False
Dim TryCount As Integer = 0
Do
Try
MySqlCommand = New MySql.Data.MySqlClient.MySqlCommand(SqlText, MySqlConn) ' get the sql command
MySqlCommand.Connection.Open() ' open the connection
If MySqlCommand.Connection.State <> ConnectionState.Open Then ' if did not open
Return Nothing ' return nothing
End If

ReaderObject = MySqlCommand.ExecuteReader ' get the data reader object
Done = True ' done with loop
Catch exMySql As MySql.Data.MySqlClient.MySqlException
If (exMySql.ErrorCode = errCannotOpen OrElse exMySql.ErrorCode = errCannotModify) AndAlso TryCount < MaxTries Then
TryCount += 1 ' increment try count
System.Threading.Thread.Sleep(WaitMilliSeconds) ' wait for a time
Else ' else got an unhandled error
Try
If MySqlCommand IsNot Nothing Then ' if got a command
If MySqlCommand.Connection.State <> ConnectionState.Closed Then ' if connection not closed
MySqlCommand.Connection.Close() ' close the connection
End If
MySqlCommand.Dispose() ' free sql command
MySqlCommand = Nothing ' set sql command to nothing
End If
Catch ex1 As Exception
MessageBox.Show(String.Format(efCloseExp, ex1.Message, vbCrLf, MySqlConn.DataSource, MySqlConn.Database),
"Cannot Close Connection Error", MessageBoxButtons.OK, MessageBoxIcon.Error)
Done = True
End Try
Done = True ' ok to exit
MessageBox.Show(String.Format(efMySqlExp, ErrMsg, vbCrLf, CStr(exMySql.ErrorCode), MySqlConn.DataSource, MySqlConn.Database, exMySql.Message),
ErrTitle, MessageBoxButtons.OK, MessageBoxIcon.Error) ' show error
Return Nothing
End If
Catch ex As Exception
Done = True
Return Nothing
Try
If MySqlCommand IsNot Nothing Then ' if got a command
If MySqlCommand.Connection.State <> ConnectionState.Closed Then ' if connection not closed
MySqlCommand.Connection.Close() ' close the connection
End If
MySqlCommand.Dispose() ' free sql command
MySqlCommand = Nothing ' set sql command to nothing
End If
Catch ex2 As Exception
MessageBox.Show(String.Format(efCloseExp, ex2.Message, vbCrLf, MySqlConn.DataSource, MySqlConn.Database),
"Cannot Close Connection Error", MessageBoxButtons.OK, MessageBoxIcon.Error)
Done = True
End Try
MessageBox.Show(String.Format(efOtherExp, ErrMsg, vbCrLf, MySqlConn.DataSource, MySqlConn.Database, ex.Message),
ErrTitle, MessageBoxButtons.OK, MessageBoxIcon.Error) ' show error
Finally
' NOTE: NO FINALLY section. On good exit, connection needs to be closed by calling sub/func
End Try
Loop Until Done
Return ReaderObject
End Function

When I use Option 1 in Visual Studio, my TableInfo function returns a table with 6 rows (should 5 for my table and in MySQL Workbench results) and 22 columns (should be 6 per MySQL Workbench results). Interesting note: First column value in each row corresponds to the column names returned by MySQL Workbench (Field, Type, Null, Key, Default, Extra)

When I use Option 2 in Visual Studio, my TableInfo function returns a table with 1 row (should 5 for my table and in MySQL Workbench results) and 22 columns (should be 1 per MySQL Workbench results).

When I use Option 2 in Visual Studio, my TableInfo function returns a table with 21 rows (should 5 for my table and in MySQL Workbench results) and 22 columns (should be 21 per MySQL Workbench results).

All three options in Visual Studio, the data in the table return does not contain any of the column names in my table. Even though some of the table columns returned are named “Column Name”, none of my column names appear, but values like "TABLE_CATALOG" do appear.

Any assistance would be greatly appreciated.

Options: ReplyQuote


Subject
Views
Written By
Posted
Column List
274
November 10, 2018 03:28PM


Sorry, only registered users may post in this forum.

Content reproduced on this site is the property of the respective copyright holders. It is not reviewed in advance by Oracle and does not necessarily represent the opinion of Oracle or any other party.