Option Strict Off
Option Explicit On

Imports System.Data.SqlClient
Imports System.Data
Public Class RdrSprCients

    Private Shared Dtable As DataTable
    Public Sub New()
        If IsNothing(Dtable) Then update()
    End Sub
    Public ReadOnly Property Dt() As DataTable
        Get
            Return Dtable
        End Get
    End Property
    Private Shared Sub CreateDt()

        Dtable = New DataTable

        Dim columnID As DataColumn = New System.Data.DataColumn("ID", GetType(Integer), Nothing, System.Data.MappingType.Element)
        columnID.AllowDBNull = False
        columnID.Unique = True
        Dtable.Columns.Add(columnID)

        Dim SurName As DataColumn = New System.Data.DataColumn("SurName", GetType(String), Nothing, System.Data.MappingType.Element)
        SurName.AllowDBNull = False
        SurName.MaxLength =  50 
        Dtable.Columns.Add(SurName)


        Dim Name1 As DataColumn = New System.Data.DataColumn("Name1", GetType(String), Nothing, System.Data.MappingType.Element)
        Name1.AllowDBNull = False
        Name1.MaxLength =  50 
        Dtable.Columns.Add(Name1)

        Dim Name2 As DataColumn = New System.Data.DataColumn("Name2", GetType(String), Nothing, System.Data.MappingType.Element)
        Name2.AllowDBNull = True
        Name2.MaxLength =  50 
        Dtable.Columns.Add(Name2)

        Dim pk( 0 ) As DataColumn
        pk( 0 ) = Dtable.Columns("ID")
        Dtable.PrimaryKey = pk
    End Sub
    Public Shared ReadOnly Property RdrSprDt() As DataTable
        Get
            Return Dtable
        End Get
    End Property

    Public Shared Sub update()
        Try
            If IsNothing(Dtable) Then
                CreateDt()
            Else
                Dtable.Dispose()
                CreateDt()
            End If
            Dim conn As New SqlConnection(ProjTur.Settings.Default.ConnectionString)
            If conn.State <> ConnectionState.Open Then conn.Open()
            Dim cmd As New SqlCommand("select Id,SurName,Name1,Name2 From TblClients order by SurName", conn)
            Dim rdr As SqlDataReader = cmd.ExecuteReader()
            With rdr
                While .Read
                    Dim m As DataRow 'строка 
                    m = Dtable.NewRow
                    m("id") = .GetInt32(0)
                    m("SurName") = .GetString(1)
                    m("Name1") = .GetString(2)
                    If .IsDBNull(3) Then
                        m("Name2") = ""
                    Else
                        m("Name2") = .GetString(3)
                    End If

                    Dtable.Rows.Add(m)
                End While
            End With
            rdr.Close()
            rdr = Nothing
            cmd = Nothing
            conn.Close()

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

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

    End Sub
    '  Public Shared Function ТLookup(ByVal arg As Integer, Optional ByVal mode As Boolean = False) As Object
    ' If IsNothing(arg) Or arg = 0 Then Return ""
    ' Dim DR As DataRow = Dtable.Rows.Find(arg)
    '  If mode = False Then Return
    'Return DR.Item("Surname") & " " & DR.Item("Name1") & " " & DR.Item("Name2")
    'End function
    Public Shared Function Lookup(ByVal arg As Integer, Optional ByVal FField As String = "Name") As String
        Dim conn1 As New SqlConnection(ProjTur.Settings.Default.ConnectionString)
        Dim RetVal As Object = ""
        If conn1.State <> ConnectionState.Open Then conn1.Open()
        Dim cmd As New SqlCommand("Select " & FField & " from TblClients where id=" & arg, conn1)
        cmd.CommandType = CommandType.Text
        Try
            RetVal = cmd.ExecuteScalar
        Catch ex As Exception
            MsgAddRow("Ошибка: " & ex.Message.ToString, MsgErr, Err.Number, "RdrSprClients.Lookup", arg)
        Finally
            conn1.Close()
            cmd = Nothing
        End Try
        Return RetVal

    End Function
End Class

 Dim rdr As SqlDataReader = cmd.ExecuteReader()
            With rdr
                While .Read
                     End While
            End With