How to know if a column is primary key

[coutphil]

(corelab.mysql 2.80.7)

Yo !

I'd like to know if there is a way to get the table structure ? I'm presently working on a backup system from scratch with VB.NET because mysqldump is not working very well in version 2.80. SO i need to know which column is primary key and if it allows Null or if its an autoincrement fields.

Let see an example with 'tbl_id' has primary key and auto-increment

Code: Select all

Dim oMysql As New CMySQL
        Dim oConn As New   MySqlConnection(oMysql.CompanyConnectString("MyDataBase"))
        Dim sSelectSQL As String
        Dim ocmdSelect As New MySqlCommand
        Dim odaSelect As New MySqlDataAdapter(ocmdSelect)
        Dim odtSelect As New DataTable("Table_Name")


        Try
            sSelectSQL = "select tbl_id, tbl_name, tbl_database_name,tbl_comment from Table_Name where Tbl_Database_Name = '" & psDatabase_Name & "'"
            ocmdSelect.CommandText = sSelectSQL
            ocmdSelect.Connection = oConn
            oConn.Open()

            odaSelect.Fill(odtSelect)
            Me.dtTableNameSelect = odtSelect

        Catch ex As Exception
            Call New SICCEH.CErrorHandling().HandleError("CMySQLBackup.New", ex)
        End Try

I got all the data from 'Table_Name' in a System.data.DataTable call dtTableNameSelect

Next function will go through the datatable to get information about column.

Code: Select all

    
Public Function GetColumnInfo()
        Dim odtColumns As DataColumn
        Dim sColumnName As String
        Dim sColumnType As String
        Dim bAutoIncrement As Boolean
        Dim bContainKey As Boolean

        For Each odtColumns In Me.dtTableNameSelect.Columns

                sColumnName = odtColumns.ColumnName
                sColumnType = odtColumns.DataType.Name
                bAutoIncrement = odtColumns.AutoIncrement

        Next


    End Function

I dont know why and how but the column 'tbl_id' is not auto-increment (false) according to the datatable and its not the primary key (false).

If there is an easier way to get table structure please give me an example.

thnx

[Alexey]

Use GetSchemaTable() method of MySqlDataReader object.
For more information please refer to MySQLDirect .NET help documentation.