Option Strict Off
Option Explicit On

Imports System.Data.SqlClient
Imports System.Data

Public Class RdrSprPost

    Public Shared Dt As New DataTable("RdrSprPost")
    Public Shared Sub CreateDt()
        Dim columnPost As DataColumn = New System.Data.DataColumn("Post", GetType(String), Nothing, System.Data.MappingType.Element)
        columnPost.AllowDBNull = False
        columnPost.MaxLength =  50 
        Dt.Columns.Add(columnPost)
        Dim columnPostPadeg As DataColumn = New System.Data.DataColumn("PostPadeg", GetType(String), Nothing, System.Data.MappingType.Element)
        columnPostPadeg.AllowDBNull = False
        columnPostPadeg.MaxLength =  50 
        Dt.Columns.Add(columnPostPadeg)

        Dim columnid As DataColumn = New System.Data.DataColumn("id", GetType(Integer), Nothing, System.Data.MappingType.Element)
        columnid.AllowDBNull = False
        columnid.ReadOnly = True
        columnid.Unique = True
        columnid.Caption = "id"
        Dt.Columns.Add(columnid)

        Dim pk( 0 ) As DataColumn
        pk( 0 ) = Dt.Columns("id")
        Dt.PrimaryKey = pk

        'Dt.Constraints.Add(New System.Data.UniqueConstraint("Constraint1", New System.Data.DataColumn() {columnid}, True))
        update()
    End Sub
    Public Shared ReadOnly Property RdrSprDt() As DataTable
        Get
            Return Dt
        End Get
    End Property
    Public Shared Sub update()
        Try
            Dt.Clear()
            If conn.State <> ConnectionState.Open Then conn.Open()
            Dim cmd As New SqlCommand("select Id,Post,PostPadeg From SprPosts", conn)
            Dim rdr As SqlDataReader = cmd.ExecuteReader()
            While rdr.Read
                Dim m As DataRow 'строка 
                m = Dt.NewRow
                m("id") = rdr.GetInt32( 0 )
                m("Post") = rdr.GetString( 1 )
                m("PostPadeg") = rdr.GetString( 2 )
                Dt.Rows.Add(m)
            End While
            rdr.Close()
            conn.Close()

            rdr = Nothing

        Catch ex As Exception
            MsgAddRow("Ошибка: " & ex.Message.ToString, MsgErr, Err.Number, "RdrSprSity.update")
        End Try

    End Sub
    Public Shared Sub Delete(ByVal arg As String)
        Try
            If DevExpress.XtraEditors.XtraMessageBox.Show("Действительно удалить ?", My.Application.Info.ProductName, MessageBoxButtons.OKCancel, MessageBoxIcon.Question) _
            = DialogResult.Cancel Then Return
            If conn.State <> ConnectionState.Open Then conn.Open()
            Dim cmd As New SqlCommand("delete From SprPosts where id=" & arg, conn)
            MsgBox(cmd.ExecuteNonQuery().ToString)
            MsgAddRow("Сведения о должности '" & arg & "' удалены!", MsgOk)
            update()
            conn.Close()
        Catch ex As Exception
            MsgAddRow("Ошибка: " & ex.Message.ToString, MsgErr, Err.Number, "RdrSprSity.delete")
        End Try

    End Sub

    Public Shared Function Lookup(ByVal arg As String, Optional ByVal mode As Boolean = False) As String

        Dim DR As DataRow = Dt.Rows.Find(arg)
        If mode = False Then Return DR.Item("Post")

        Return DR.Item("PostPadeg")
     
    End Function

End Class