Imports System.Data.SqlClient
Imports System.Globalization

Public Class classuser

    Public Function SQLExicute(ByVal sqlstatement As String) As Boolean
        Try
            Dim cmd As New SqlCommand
            cmd.CommandText = sqlstatement
            sqlcon.Open()
            cmd.Connection = sqlcon
            cmd.ExecuteNonQuery()
            Return True
        Catch ex As Exception
            Return False
            msgbox(ex.Message)
        Finally
            sqlcon.close()
        End Try
    End Function
    '----------------------------------------------------------------------------------------
    ' importer les données depuis SQL Server
    Public Function SQLReturn(ByVal sqlstatement As String) As DataSet
        Dim ds As New DataSet
        Try
            If sqlcon.State = ConnectionState.Open Then
                sqlcon.Close()
            End If
            Dim sda As New SqlDataAdapter(sqlstatement, sqlcon)
            sda.Fill(ds)
        Catch ex As Exception
            MsgBox(ex.Message)
        Finally
            sqlcon.Close()
        End Try
        Return ds
    End Function
	'------------------------------------------------------------------------------------------
    '
    ' Remplir ComboBox
    '
    Public Sub FillComboBox(ByVal cbo As ComboBox, ByVal TableName As String, ByVal col_Name As String, ByVal col_value As String)
        Try
            Dim VarSQL As String
            VarSQL = ""
            VarSQL = " select " + col_Name + "," + col_value + " From " + TableName + " "
            Dim sda As New SqlDataAdapter(VarSQL, sqlcon)
            Dim ds As New DataSet()
            sda.Fill(ds)
            cbo.DataSource = ds.Tables(0)
            cbo.DisplayMember = col_Name
            cbo.ValueMember = col_value
        Catch ex As Exception
            MsgBox("ERROR: " & ex.Message)
        End Try
    End Sub
'----------------------------------------------------------------------------------
    Public Shared sqlcon As New SqlConnection
    Public Sub FillClient()
        Dim cmd As New SqlCommand()
        cmd.Connection = classuser.sqlcon
        cmd.CommandType = CommandType.Text
        cmd.CommandText = "SELECT idclient, Client_ste FROM Client"
        Dim objDs As New DataSet()
        Dim dAdapter As New SqlDataAdapter()
        dAdapter.SelectCommand = cmd
        sqlcon.Open()
        dAdapter.Fill(objDs)
        sqlcon.Close()
        PrTeste.C1.ValueMember = "IDclient"
        PrTeste.C1.DisplayMember = "Client_ste"
        PrTeste.C1.DataSource = objDs.Tables(0)
    End Sub
'--------------------------------------------------------------------------------------------------
    Public Sub FillTVX(ByVal idclient As Integer)
        Try
            Dim cmd As New SqlCommand()
            cmd.Connection = sqlcon
            cmd.CommandType = CommandType.Text
            cmd.CommandText = "SELECT id_affaire, affaire FROM traveaux WHERE idclient =@idclient"
            cmd.Parameters.AddWithValue("@idclient", idclient)
            Dim objDs As New DataSet()
            Dim dAdapter As New SqlDataAdapter()
            dAdapter.SelectCommand = cmd
            sqlcon.Open()
            dAdapter.Fill(objDs)
            sqlcon.Close()
            If objDs.Tables(0).Rows.Count > 0 Then
                PrTeste.C2.ValueMember = "id_affaire"
                PrTeste.C2.DisplayMember = "affaire"
                PrTeste.C2.DataSource = objDs.Tables(0)
            End If
        Catch ex As Exception

        End Try
    End Sub
	
End Class