طريقة عمل شاشة المستخدمين بالفيجوال بيسك و SQLITE

المشاركة الأصلية كتبت بواسطة: ziade بتاريخ: 17-12-2024 01:46 مساءًشكرا جزيلا استاذ ياسر

 '///////////////////////
 Private Sub InsertData()
     ' Ouvrir la connexion à la base de données  
     Connection.Open()
     Try
         If Connection.State = ConnectionState.Open Then
             Command.Connection = Connection
 
             ' Vérifier si l'utilisateur existe déjà  
             Command.CommandText = "SELECT COUNT(*) FROM USERS WHERE user_name = @userName"
             Command.Parameters.Clear() ' Nettoyer les paramètres précédents  
             Command.Parameters.AddWithValue("@userName", TextBox1.Text.Trim()) ' Trim pour enlever les espaces  
 
             Dim userExists As Integer = Convert.ToInt32(Command.ExecuteScalar()) ' Exécuter la commande et récupérer le résultat  
 
             If userExists > 0 Then
                 MessageBox.Show("هذا المستخدم موجود مسبقا", "خطأ ", MessageBoxButtons.OK, MessageBoxIcon.Warning)
             Else
                 ' Insérer les nouvelles données  
                 Command.CommandText = "INSERT INTO USERS (user_name, user_password, user_role) VALUES (@userName, @userPassword, @userRole)"
                 Command.Parameters.Clear() ' Nettoyer les anciens paramètres  
                 Command.Parameters.AddWithValue("@userName", TextBox1.Text.Trim())
                 Command.Parameters.AddWithValue("@userPassword", TextBox2.Text.Trim())
                 Command.Parameters.AddWithValue("@userRole", ComboBox1.Text.Trim())
                 Command.ExecuteNonQuery() ' Exécuter l'insertion  
                 MessageBox.Show("تمت اضافة مستخدم", "تسجيل ناجح", MessageBoxButtons.OK, MessageBoxIcon.Information)
             End If
         End If
     Catch ex As Exception
         MessageBox.Show("خظأ أثناء الادراج : " & ex.Message, "Erreur", MessageBoxButtons.OK, MessageBoxIcon.Error)
     Finally
         Connection.Close() ' Toujours fermer la connexion  
     End Try
 End Sub
 '////////////////////////// 

Imports System.Data.SQLite
 
 Public Class USERS
 
     Private DbCommand As String = ""
     Private bindingSrc As BindingSource
     Private DbName As String = "POINTAGE.db"
     Private DbPath As String = Application.StartupPath & "" & DbName
     Private ConnString As String = "Data Source=" & DbPath & ";Version=3"
     Private Connection As New SQLiteConnection(ConnString)
     Private Command As New SQLiteCommand("", Connection)
     Dim Pos As Point
 
     Private Sub ShowDataGridView()
         DataGridView1.Rows.Clear()
         Connection.Open()
         If Connection.State = ConnectionState.Open Then
             Command.Connection = Connection
             Command.CommandText = "SELECT * FROM USERS"
             Dim reader As SQLiteDataReader = Command.ExecuteReader()
             Using reader
                 While reader.Read()
                     Me.DataGridView1.Rows.Add(reader.GetInt32(0),
                                               reader.GetString(1),
                                               reader.GetString(2),
                                               reader.GetString(3))
                 End While
             End Using
         End If
         Connection.Close()
     End Sub
 
     Private Sub InsertData()
         Connection.Open()
         Try
             If Connection.State = ConnectionState.Open Then
                 Command.Connection = Connection
 
                 ' Vérifier si l'utilisateur existe déjà
                 Command.CommandText = "SELECT COUNT(*) FROM USERS WHERE user_name = @userName"
                 Command.Parameters.Clear()
                 Command.Parameters.AddWithValue("@userName", TextBox1.Text.Trim())
 
                 Dim userExists As Integer = Convert.ToInt32(Command.ExecuteScalar())
 
                 If userExists > 0 Then
                     MessageBox.Show("هذا المستخدم موجود مسبقا", "خطأ", MessageBoxButtons.OK, MessageBoxIcon.Warning)
                 Else
                     ' Insérer les nouvelles données
                     Command.CommandText = "INSERT INTO USERS (user_name, user_password, user_role) VALUES (@userName, @userPassword, @userRole)"
                     Command.Parameters.Clear()
                     Command.Parameters.AddWithValue("@userName", TextBox1.Text.Trim())
                     Command.Parameters.AddWithValue("@userPassword", TextBox2.Text.Trim())
                     Command.Parameters.AddWithValue("@userRole", ComboBox1.Text.Trim())
                     Command.ExecuteNonQuery()
                     MessageBox.Show("تمت اضافة مستخدم", "تسجيل ناجح", MessageBoxButtons.OK, MessageBoxIcon.Information)
                 End If
             End If
         Catch ex As Exception
             MessageBox.Show("خظأ أثناء الادراج : " & ex.Message, "Erreur", MessageBoxButtons.OK, MessageBoxIcon.Error)
         Finally
             Connection.Close()
         End Try
     End Sub
 
     Private Sub FromDataGridView()
         Dim iRowIndex As Integer
         For i As Integer = 0 To DataGridView1.SelectedCells.Count - 1
             iRowIndex = DataGridView1.SelectedCells.Item(i).RowIndex
             tbx0.Text = DataGridView1.Rows(iRowIndex).Cells(0).Value
             TextBox1.Text = DataGridView1.Rows(iRowIndex).Cells(1).Value
             TextBox2.Text = DataGridView1.Rows(iRowIndex).Cells(2).Value
             ComboBox1.Text = DataGridView1.Rows(iRowIndex).Cells(3).Value
         Next
     End Sub
 
     Private Sub UpdateData()
         Connection.Open()
         If Connection.State = ConnectionState.Open Then
             Command.Connection = Connection
             Command.CommandText = "UPDATE USERS SET 
                                   user_name = @userName, 
                                   user_password = @userPassword, 
                                   user_role = @userRole 
                                   WHERE user_id = @userId"
             Command.Parameters.Clear()
             Command.Parameters.AddWithValue("@userName", TextBox1.Text.Trim())
             Command.Parameters.AddWithValue("@userPassword", TextBox2.Text.Trim())
             Command.Parameters.AddWithValue("@userRole", ComboBox1.Text.Trim())
             Command.Parameters.AddWithValue("@userId", tbx0.Text.Trim())
             Command.ExecuteNonQuery()
         End If
         Connection.Close()
     End Sub
 
     Private Sub DeleteData()
         Dim result1 As DialogResult = MessageBox.Show("هل تريد حقا حذف هذا المسئول ؟", "حذف مسئول", MessageBoxButtons.YesNo)
         If result1 = DialogResult.Yes Then
             Connection.Open()
             If Connection.State = ConnectionState.Open Then
                 Command.Connection = Connection
                 Command.CommandText = "DELETE FROM USERS WHERE user_id = @userId"
                 Command.Parameters.Clear()
                 Command.Parameters.AddWithValue("@userId", tbx0.Text.Trim())
                 Command.ExecuteNonQuery()
             End If
             Connection.Close()
         End If
     End Sub
 
     Private Sub USERS_Closed(sender As Object, e As EventArgs) Handles Me.Closed
         main.Show()
     End Sub
 
     Private Sub USERS_Load(sender As Object, e As EventArgs) Handles MyBase.Load
         ShowDataGridView()
     End Sub
 
     Private Sub Button3_Click(sender As Object, e As EventArgs) Handles Button3.Click
         InsertData()
         ShowDataGridView()
     End Sub
 
     Private Sub Button2_Click(sender As Object, e As EventArgs) Handles Button2.Click
         TextBox1.Text = ""
         TextBox2.Text = ""
         ComboBox1.SelectedIndex = -1
         ShowDataGridView()
     End Sub
 
     Private Sub DataGridView1_CellClick(sender As Object, e As DataGridViewCellEventArgs) Handles DataGridView1.CellClick
         FromDataGridView()
     End Sub
 
     Private Sub Button4_Click(sender As Object, e As EventArgs) Handles Button4.Click
         UpdateData()
         ShowDataGridView()
     End Sub
 
     Private Sub Button1_Click(sender As Object, e As EventArgs) Handles Button1.Click
         DeleteData()
         ShowDataGridView()
     End Sub
 
     Private Sub Button5_Click(sender As Object, e As EventArgs) Handles Button5.Click
         DataGridView1.Rows.Clear()
         Connection.Open()
         If Connection.State = ConnectionState.Open Then
             Command.Connection = Connection
             Command.CommandText = "SELECT * FROM USERS WHERE user_name LIKE @userName"
             Command.Parameters.Clear()
             Command.Parameters.AddWithValue("@userName", "%" & TextBox1.Text.Trim() & "%"
             Dim reader As SQLiteDataReader = Command.ExecuteReader()
             Using reader
                 While reader.Read()
                     Me.DataGridView1.Rows.Add(reader.GetInt32(0),
                                               reader.GetString(1),
                                               reader.GetString(2),
                                               reader.GetString(3))
                 End While
             End Using
         End If
         Connection.Close()
     End Sub
 
     Private Sub Button6_Click(sender As Object, e As EventArgs) Handles Button6.Click
         DataGridView1.Rows.Clear()
         Connection.Open()
         If Connection.State = ConnectionState.Open Then
             Command.Connection = Connection
             Command.CommandText = "SELECT * FROM USERS WHERE user_password LIKE @userPassword"
             Command.Parameters.Clear()
             Command.Parameters.AddWithValue("@userPassword", "%" & TextBox2.Text.Trim() & "%"
             Dim reader As SQLiteDataReader = Command.ExecuteReader()
             Using reader
                 While reader.Read()
                     Me.DataGridView1.Rows.Add(reader.GetInt32(0),
                                               reader.GetString(1),
                                               reader.GetString(2),
                                               reader.GetString(3))
                 End While
             End Using
         End If
         Connection.Close()
     End Sub
 
     Private Sub Button7_Click(sender As Object, e As EventArgs) Handles Button7.Click
         DataGridView1.Rows.Clear()
         Connection.Open()
         If Connection.State = ConnectionState.Open Then
             Command.Connection = Connection
             Command.CommandText = "SELECT * FROM USERS WHERE user_role LIKE @userRole"
             Command.Parameters.Clear()
             Command.Parameters.AddWithValue("@userRole", "%" & ComboBox1.Text.Trim() & "%"
             Dim reader As SQLiteDataReader = Command.ExecuteReader()
             Using reader
                 While reader.Read()
                     Me.DataGridView1.Rows.Add(reader.GetInt32(0),
                                               reader.GetString(1),
                                               reader.GetString(2),
                                               reader.GetString(3))
                 End While
             End Using
         End If
         Connection.Close()
     End Sub
 End Class
 

المشاركة الأصلية كتبت بواسطة: khelifi72 بتاريخ: 06-01-2025 01:43 صباحاً

السلام عليكم 
اليكم الكود كاملا مأخوذ من درس الاستاذ ياسر مع بعض التغييرات وشكرا مسبقا

CODE

Imports System.Data.SQLite
 
 Public Class USERS
 
     Private DbCommand As String = ""
     Private bindingSrc As BindingSource
     Private DbName As String = "POINTAGE.db"
     Private DbPath As String = Application.StartupPath & "" & DbName
     Private ConnString As String = "Data Source=" & DbPath & ";Version=3"
     Private Connection As New SQLiteConnection(ConnString)
     Private Command As New SQLiteCommand("", Connection)
     Dim Pos As Point
 
     Private Sub ShowDataGridView()
         DataGridView1.Rows.Clear()
         Connection.Open()
         If Connection.State = ConnectionState.Open Then
             Command.Connection = Connection
             Command.CommandText = "SELECT * FROM USERS"
             Dim reader As SQLiteDataReader = Command.ExecuteReader()
             Using reader
                 While reader.Read()
                     Me.DataGridView1.Rows.Add(reader.GetInt32(0),
                                               reader.GetString(1),
                                               reader.GetString(2),
                                               reader.GetString(3))
                 End While
             End Using
         End If
         Connection.Close()
     End Sub
 
     Private Sub InsertData()
         Connection.Open()
         Try
             If Connection.State = ConnectionState.Open Then
                 Command.Connection = Connection
 
                 ' Vérifier si l'utilisateur existe déjà
                 Command.CommandText = "SELECT COUNT(*) FROM USERS WHERE user_name = @userName"
                 Command.Parameters.Clear()
                 Command.Parameters.AddWithValue("@userName", TextBox1.Text.Trim())
 
                 Dim userExists As Integer = Convert.ToInt32(Command.ExecuteScalar())
 
                 If userExists > 0 Then
                     MessageBox.Show("هذا المستخدم موجود مسبقا", "خطأ", MessageBoxButtons.OK, MessageBoxIcon.Warning)
                 Else
                     ' Insérer les nouvelles données
                     Command.CommandText = "INSERT INTO USERS (user_name, user_password, user_role) VALUES (@userName, @userPassword, @userRole)"
                     Command.Parameters.Clear()
                     Command.Parameters.AddWithValue("@userName", TextBox1.Text.Trim())
                     Command.Parameters.AddWithValue("@userPassword", TextBox2.Text.Trim())
                     Command.Parameters.AddWithValue("@userRole", ComboBox1.Text.Trim())
                     Command.ExecuteNonQuery()
                     MessageBox.Show("تمت اضافة مستخدم", "تسجيل ناجح", MessageBoxButtons.OK, MessageBoxIcon.Information)
                 End If
             End If
         Catch ex As Exception
             MessageBox.Show("خظأ أثناء الادراج : " & ex.Message, "Erreur", MessageBoxButtons.OK, MessageBoxIcon.Error)
         Finally
             Connection.Close()
         End Try
     End Sub
 
     Private Sub FromDataGridView()
         Dim iRowIndex As Integer
         For i As Integer = 0 To DataGridView1.SelectedCells.Count - 1
             iRowIndex = DataGridView1.SelectedCells.Item(i).RowIndex
             tbx0.Text = DataGridView1.Rows(iRowIndex).Cells(0).Value
             TextBox1.Text = DataGridView1.Rows(iRowIndex).Cells(1).Value
             TextBox2.Text = DataGridView1.Rows(iRowIndex).Cells(2).Value
             ComboBox1.Text = DataGridView1.Rows(iRowIndex).Cells(3).Value
         Next
     End Sub
 
     Private Sub UpdateData()
         Connection.Open()
         If Connection.State = ConnectionState.Open Then
             Command.Connection = Connection
             Command.CommandText = "UPDATE USERS SET 
                                   user_name = @userName, 
                                   user_password = @userPassword, 
                                   user_role = @userRole 
                                   WHERE user_id = @userId"
             Command.Parameters.Clear()
             Command.Parameters.AddWithValue("@userName", TextBox1.Text.Trim())
             Command.Parameters.AddWithValue("@userPassword", TextBox2.Text.Trim())
             Command.Parameters.AddWithValue("@userRole", ComboBox1.Text.Trim())
             Command.Parameters.AddWithValue("@userId", tbx0.Text.Trim())
             Command.ExecuteNonQuery()
         End If
         Connection.Close()
     End Sub
 
     Private Sub DeleteData()
         Dim result1 As DialogResult = MessageBox.Show("هل تريد حقا حذف هذا المسئول ؟", "حذف مسئول", MessageBoxButtons.YesNo)
         If result1 = DialogResult.Yes Then
             Connection.Open()
             If Connection.State = ConnectionState.Open Then
                 Command.Connection = Connection
                 Command.CommandText = "DELETE FROM USERS WHERE user_id = @userId"
                 Command.Parameters.Clear()
                 Command.Parameters.AddWithValue("@userId", tbx0.Text.Trim())
                 Command.ExecuteNonQuery()
             End If
             Connection.Close()
         End If
     End Sub
 
     Private Sub USERS_Closed(sender As Object, e As EventArgs) Handles Me.Closed
         main.Show()
     End Sub
 
     Private Sub USERS_Load(sender As Object, e As EventArgs) Handles MyBase.Load
         ShowDataGridView()
     End Sub
 
     Private Sub Button3_Click(sender As Object, e As EventArgs) Handles Button3.Click
         InsertData()
         ShowDataGridView()
     End Sub
 
     Private Sub Button2_Click(sender As Object, e As EventArgs) Handles Button2.Click
         TextBox1.Text = ""
         TextBox2.Text = ""
         ComboBox1.SelectedIndex = -1
         ShowDataGridView()
     End Sub
 
     Private Sub DataGridView1_CellClick(sender As Object, e As DataGridViewCellEventArgs) Handles DataGridView1.CellClick
         FromDataGridView()
     End Sub
 
     Private Sub Button4_Click(sender As Object, e As EventArgs) Handles Button4.Click
         UpdateData()
         ShowDataGridView()
     End Sub
 
     Private Sub Button1_Click(sender As Object, e As EventArgs) Handles Button1.Click
         DeleteData()
         ShowDataGridView()
     End Sub
 
     Private Sub Button5_Click(sender As Object, e As EventArgs) Handles Button5.Click
         DataGridView1.Rows.Clear()
         Connection.Open()
         If Connection.State = ConnectionState.Open Then
             Command.Connection = Connection
             Command.CommandText = "SELECT * FROM USERS WHERE user_name LIKE @userName"
             Command.Parameters.Clear()
             Command.Parameters.AddWithValue("@userName", "%" & TextBox1.Text.Trim() & "%"
             Dim reader As SQLiteDataReader = Command.ExecuteReader()
             Using reader
                 While reader.Read()
                     Me.DataGridView1.Rows.Add(reader.GetInt32(0),
                                               reader.GetString(1),
                                               reader.GetString(2),
                                               reader.GetString(3))
                 End While
             End Using
         End If
         Connection.Close()
     End Sub
 
     Private Sub Button6_Click(sender As Object, e As EventArgs) Handles Button6.Click
         DataGridView1.Rows.Clear()
         Connection.Open()
         If Connection.State = ConnectionState.Open Then
             Command.Connection = Connection
             Command.CommandText = "SELECT * FROM USERS WHERE user_password LIKE @userPassword"
             Command.Parameters.Clear()
             Command.Parameters.AddWithValue("@userPassword", "%" & TextBox2.Text.Trim() & "%"
             Dim reader As SQLiteDataReader = Command.ExecuteReader()
             Using reader
                 While reader.Read()
                     Me.DataGridView1.Rows.Add(reader.GetInt32(0),
                                               reader.GetString(1),
                                               reader.GetString(2),
                                               reader.GetString(3))
                 End While
             End Using
         End If
         Connection.Close()
     End Sub
 
     Private Sub Button7_Click(sender As Object, e As EventArgs) Handles Button7.Click
         DataGridView1.Rows.Clear()
         Connection.Open()
         If Connection.State = ConnectionState.Open Then
             Command.Connection = Connection
             Command.CommandText = "SELECT * FROM USERS WHERE user_role LIKE @userRole"
             Command.Parameters.Clear()
             Command.Parameters.AddWithValue("@userRole", "%" & ComboBox1.Text.Trim() & "%"
             Dim reader As SQLiteDataReader = Command.ExecuteReader()
             Using reader
                 While reader.Read()
                     Me.DataGridView1.Rows.Add(reader.GetInt32(0),
                                               reader.GetString(1),
                                               reader.GetString(2),
                                               reader.GetString(3))
                 End While
             End Using
         End If
         Connection.Close()
     End Sub
 End Class