Imports System.Data.SqlClient
Imports System.IO
Imports System.Data
Imports Sunisoft.IrisSkin
Public Class Form1
Dim CON As New SqlConnection("Server=ABDELAZIZ;Database=SCHOOL;Integrated Security=false;user id=sa;password=GH987654GH;")
Dim InfoCommand As SqlCommand
Dim InfoTable As DataTable
Private Sub Form1_Load(sender As Object, e As EventArgs) Handles MyBase.Load
' Try
' CON.Open()
InfoTable = New DataTable
Using InfoAdapter As New SqlDataAdapter("select * from STUDENT", CON)
InfoAdapter.Fill(InfoTable)
End Using
'
DataGridView1.DataSource = InfoTable
TextBox1.DataBindings.Add("Text", InfoTable, "ID")
TextBox2.DataBindings.Add("Text", InfoTable, "FirstName")
TextBox3.DataBindings.Add("Text", InfoTable, "LastName")
DateTimePicker1.DataBindings.Add("Text", InfoTable, "BirthDate")
TextBox5.DataBindings.Add("Text", InfoTable, "Mobile")
TextBox6.DataBindings.Add("Text", InfoTable, "Adresse")
PictureBox1.DataBindings.Add("Image", InfoTable, "Carte", True,
Windows.Forms.DataSourceUpdateMode.OnPropertyChanged)
DataGridView1.Columns(0).HeaderText = "الترقيم"
DataGridView1.Columns(1).HeaderText = "الاسم"
DataGridView1.Columns(2).HeaderText = "اللقب"
DataGridView1.Columns(3).HeaderText = "تاريخ الميلاد"
DataGridView1.Columns(3).DefaultCellStyle.Format = "yyyy-MM-dd"
DataGridView1.Columns(4).HeaderText = "هاتف الولي"
DataGridView1.Columns(5).HeaderText = "العنوان"
DataGridView1.Columns(6).HeaderText = "الصورة"
DataGridView1.RowHeadersWidth = 30
DataGridView1.Columns(0).Width = 70
DataGridView1.Columns(1).Width = 200
DataGridView1.Columns(2).Width = 200
DataGridView1.Columns(3).Width = 150
DataGridView1.Columns(4).Width = 150
DataGridView1.Columns(5).Width = 150
DataGridView1.Columns(0).DefaultCellStyle.Alignment = DataGridViewContentAlignment.MiddleCenter
DataGridView1.Columns(1).DefaultCellStyle.Alignment = DataGridViewContentAlignment.MiddleCenter
DataGridView1.Columns(2).DefaultCellStyle.Alignment = DataGridViewContentAlignment.MiddleCenter
DataGridView1.Columns(3).DefaultCellStyle.Alignment = DataGridViewContentAlignment.MiddleCenter
DataGridView1.Columns(4).DefaultCellStyle.Alignment = DataGridViewContentAlignment.MiddleCenter
DataGridView1.Columns(5).DefaultCellStyle.Alignment = DataGridViewContentAlignment.MiddleCenter
With Me.DataGridView1
DataGridView1.RowsDefaultCellStyle.BackColor = Color.Aquamarine
.AlternatingRowsDefaultCellStyle.BackColor = Color.White
.RowsDefaultCellStyle.SelectionBackColor = Color.WhiteSmoke
.RowsDefaultCellStyle.SelectionForeColor = Color.Red
.AlternatingRowsDefaultCellStyle.SelectionBackColor = Color.WhiteSmoke
.ColumnHeadersDefaultCellStyle.Alignment = DataGridViewContentAlignment.MiddleCenter
DataGridView1.Font = New Font("Arial", 10, FontStyle.Bold)
End With
record()
ShowRelated()
Button2.Enabled = False
Button5.Enabled = False
Button13.Enabled = False
'إضافة السكاين
Dim skn As New Sunisoft.IrisSkin.SkinEngine
skn.SerialNumber = "kUb2DF5pvGF3X9dKPFvIdkXQ0sE8LkAVp9fMme9wCnjZ+ArdRVlxKw=="
skn.SkinFile = Application.StartupPath & "\SILVER.ssk"
skn.AddForm(Me)
'تعبئة الكومبوبوكس
PopulateComboBox(InfoTable)
End Sub
'تعبئة الكومبوبوكس
Private Sub PopulateComboBox(Dt As DataTable)
ComboBox1.DataSource = Dt
ComboBox1.DisplayMember = "FirstName"
ComboBox1.ValueMember = "ID"
End Sub
Private Sub record()
TextBox7.Text = DataGridView1.Rows.Count() - 1
End Sub
Public Sub ShowRelated()
Try
If Me.BindingContext(InfoTable).Position = -1 Then
TextBox8.Text = "التّلميذ رقم 0 من 0"
Else
TextBox8.Text = "التّلميذ المسجّل رقم :" & Me.BindingContext(InfoTable).Position + 1 &
" من " & Me.BindingContext(InfoTable).Count
End If
Catch ex As Exception
End Try
End Sub
Private Sub Button1_Click(sender As Object, e As EventArgs) Handles Button1.Click
If MsgBox("هل تريد إضافة سجل جديد ؟ ", MsgBoxStyle.Exclamation + MsgBoxStyle.MsgBoxRight + MsgBoxStyle.OkCancel, "إضافة سجل جديد") = MsgBoxResult.Cancel Then Exit Sub
Using Command As New SqlCommand("SELECT max(ID) From STUDENT", CON)
If CON.State = ConnectionState.Closed Then CON.Open()
If Command.ExecuteScalar Is DBNull.Value Then
TextBox1.Text = 1
clear_Text()
TextBox2.Focus()
Button1.Enabled = False
Button2.Enabled = True
Button3.Enabled = False
Button4.Enabled = False
Button5.Enabled = True
Button6.Enabled = False
PictureBox1.Image = My.Resources.Nopicture
Else
TextBox1.Text = Command.ExecuteScalar().ToString() + 1
clear_Text()
TextBox2.Focus()
Button1.Enabled = False
Button2.Enabled = True
Button3.Enabled = False
Button4.Enabled = False
Button5.Enabled = True
Button6.Enabled = False
PictureBox1.Image = My.Resources.Nopicture
End If
CON.Close()
End Using
End Sub
Private Sub clear_Text()
TextBox2.Text = ""
TextBox3.Text = ""
TextBox5.Text = ""
TextBox6.Text = ""
End Sub
Private Sub Button2_Click(sender As Object, e As EventArgs) Handles Button2.Click
If TextBox1.Text.Trim = "" Then
ErrorProvider1.SetError(TextBox1, "الرجاء عدم ترك حقل رقم المتمدرس فارغا")
MessageBox.Show("الرجاء عدم ترك حقل رقم المتمدرس فارغا", "إدخال خاطىء", MessageBoxButtons.OK, MessageBoxIcon.Information)
TextBox1.Focus()
Exit Sub
End If
If TextBox2.Text.Trim = "" Then
ErrorProvider1.SetError(TextBox2, "الرجاء عدم ترك حقل اسم المتمدرس فارغا")
MessageBox.Show("الرجاء عدم ترك حقل اسم المتمدرس فارغا", "إدخال خاطىء", MessageBoxButtons.OK, MessageBoxIcon.Information)
TextBox2.Focus()
Exit Sub
End If
If TextBox3.Text.Trim = "" Then
ErrorProvider1.SetError(TextBox3, "الرجاء عدم ترك حقل لقب المتمدرس فارغا")
MessageBox.Show("الرجاء عدم ترك حقل لقب المتمدرس فارغا", "إدخال خاطىء", MessageBoxButtons.OK, MessageBoxIcon.Information)
TextBox3.Focus()
Exit Sub
End If
If TextBox5.Text.Trim = "" Then
ErrorProvider1.SetError(TextBox5, "الرجاء عدم ترك حقل هاتف و لي أمر المتمدرس فارغا")
MessageBox.Show("الرجاء عدم ترك حقل هاتف و لي أمر المتمدرس فارغا", "إدخال خاطىء", MessageBoxButtons.OK, MessageBoxIcon.Information)
TextBox5.Focus()
Exit Sub
End If
If TextBox6.Text.Trim = "" Then
ErrorProvider1.SetError(TextBox6, "الرجاء عدم ترك حقل عنوان المتمدرس فارغا")
MessageBox.Show("الرجاء عدم ترك حقل عنوان المتمدرس فارغا", "إدخال خاطىء", MessageBoxButtons.OK, MessageBoxIcon.Information)
TextBox6.Focus()
ElseIf MsgBox("هل تريد حفظ السجل الحالي", MsgBoxStyle.Information + MsgBoxStyle.YesNo, "تأكيد الحفظ") = MsgBoxResult.No Then
Exit Sub
ElseIf TextBox1.Text = "" Then
MsgBox("الرجاء كتابة الرقم التسلسلي", MsgBoxStyle.MsgBoxRtlReading, "تنبيه")
End If
'التأكّد قبل الحفظ
Dim Checknum As String = ""
Using Check As New SqlCommand With {.Connection = CON, .CommandText = "select ID from STUDENT where ID = @ID "}
Check.Parameters.Add("@ID", SqlDbType.Int).Value = Convert.ToInt32(TextBox1.Text.Trim)
If CON.State = ConnectionState.Closed Then CON.Open()
Checknum = Check.ExecuteScalar
CON.Close()
End Using
If Checknum <> "" Then
MsgBox("عفوًا .. هذا التّلميذ سبق تسجيله في النّظام و لا يمكن تكرار تسجيله بنفس رقم القيد ", MsgBoxStyle.Critical + MsgBoxStyle.MsgBoxRight, " خطأ في التسجيل ")
Exit Sub
Else
Using Command As New SqlCommand With {.Connection = CON}
With Command
.CommandText = "INSERT INTO STUDENT(ID,FirstName,LastName,BirthDate,Mobile,Adresse,Carte)values(@ID,@FirstName,@LastName,@BirthDate,@Mobile,@Adresse,@Carte)"
.Parameters.Add("@ID", SqlDbType.Int).Value = TextBox1.Text
.Parameters.Add("@FIRSTNAME", SqlDbType.NVarChar).Value = TextBox2.Text
.Parameters.Add("@LASTNAME", SqlDbType.NVarChar).Value = TextBox3.Text
.Parameters.Add("@BIRTHDATE", SqlDbType.NVarChar).Value = DateTimePicker1.Value.ToString("yyyy/MM/dd")
.Parameters.Add("@MOBILE", SqlDbType.NVarChar).Value = TextBox5.Text
.Parameters.Add("@ADRESSE", SqlDbType.NVarChar).Value = TextBox6.Text
Dim ms As New MemoryStream
PictureBox1.Image.Save(ms, PictureBox1.Image.RawFormat)
.Parameters.Add("@Carte", SqlDbType.Image).Value = ms.ToArray()
End With
If CON.State = ConnectionState.Closed Then CON.Open()
If Command.ExecuteNonQuery() = 1 Then
MsgBox("تمت عملية الإضافة بنجاح", MsgBoxStyle.MsgBoxRtlReading, "تنفيذ إجراء الإضافة")
RE_DATE()
record()
ShowRelated()
Else
MsgBox("تأكد من إدخال جميع البيانات وبالصورة الصحيحة", MsgBoxStyle.MsgBoxRtlReading, "إدخال خاطئ أو غير مكتمل")
End If
CON.Close()
End Using
End If
Button1.Enabled = True
Button2.Enabled = False
Button3.Enabled = True
Button4.Enabled = False
Button5.Enabled = True
Button6.Enabled = True
End Sub
Private Sub Button3_Click(sender As Object, e As EventArgs) Handles Button3.Click
If MsgBox(" هل أنت متأكّد من أنك تريد تعديل بيانات هذا النّلميذ : " & TextBox2.Text & " " & TextBox3.Text & " ؟ ", MsgBoxStyle.YesNo + MsgBoxStyle.Critical, "تحذير") = MsgBoxResult.No Then
Exit Sub
Else
Button1.Enabled = False
Button2.Enabled = False
Button3.Enabled = False
Button4.Enabled = False
Button13.Enabled = True
Button5.Enabled = True
Button6.Enabled = True
End If
End Sub
Private Sub Button13_Click(sender As Object, e As EventArgs) Handles Button13.Click
If TextBox1.Text = "" Then
MsgBox("الرجاء كتابة الرقم التسلسلي", MsgBoxStyle.MsgBoxRtlReading, "تنبيه")
Else
Using Command As New SqlCommand
With Command
.CommandText = "UPDATE STUDENT SET FIRSTNAME= @FIRSTNAME ,LASTNAME= @LASTNAME,BIRTHDATE= @BIRTHDATE,MOBILE= @MOBILE,ADRESSE= @ADRESSE,Carte=@Carte where ID = @ID"
.Connection = CON
.Parameters.Add("@ID", SqlDbType.Int).Value = TextBox1.Text
.Parameters.Add("@FIRSTNAME", SqlDbType.NVarChar).Value = TextBox2.Text
.Parameters.Add("@LASTNAME", SqlDbType.NVarChar).Value = TextBox3.Text
.Parameters.Add("@BIRTHDATE", SqlDbType.NVarChar).Value = DateTimePicker1.Value.ToString("yyyy/MM/dd")
.Parameters.Add("@MOBILE", SqlDbType.NVarChar).Value = TextBox5.Text
.Parameters.Add("@ADRESSE", SqlDbType.NVarChar).Value = TextBox6.Text
Dim ms As New MemoryStream
PictureBox1.Image.Save(ms, PictureBox1.Image.RawFormat)
.Parameters.Add("@Carte", SqlDbType.Image).Value = ms.ToArray()
If CON.State = ConnectionState.Closed Then CON.Open()
If Command.ExecuteNonQuery() = 1 Then
MsgBox("تمت عملية التعديل بنجاح", MsgBoxStyle.MsgBoxRtlReading, "تنفيذ اجراء التعديل")
RE_DATE()
Else
MsgBox("لم تتم عملية التعديل بنجاح", MsgBoxStyle.MsgBoxRtlReading, "تنفيذ اجراء التعديل")
End If
CON.Close()
End With
End Using
Button1.Enabled = True
Button2.Enabled = True
Button3.Enabled = True
Button4.Enabled = True
Button13.Enabled = False
Button5.Enabled = False
Button6.Enabled = True
End If
End Sub
Private Sub Button4_Click(sender As Object, e As EventArgs) Handles Button4.Click
Using Commandcheck As New SqlCommand("SELECT max(ID) From STUDENT", CON)
If CON.State = ConnectionState.Closed Then CON.Open()
If Commandcheck.ExecuteScalar Is DBNull.Value Then
MsgBox("قاعدة البيانات فارغة .. لا توجد بيانات لتنفيذ الأمر ")
CON.Close()
Exit Sub
End If
End Using
If MsgBox(" هل أنت متأكّد من أنك تريد حذف بيانات هذا النّلميذ : " & TextBox2.Text & " " & TextBox3.Text & " ؟ ", MsgBoxStyle.YesNo + MsgBoxStyle.Critical, "تحذير") = MsgBoxResult.No Then
Exit Sub
ElseIf TextBox1.Text = "" Then
MsgBox("الرجاء كتابة الرقم التسلسلي", MsgBoxStyle.MsgBoxRtlReading, "تنبيه")
Else
Using Command As New SqlCommand("DELETE FROM STUDENT where ID = @ID", CON)
Command.Parameters.Add("@ID", SqlDbType.Int).Value = TextBox1.Text
If CON.State = ConnectionState.Closed Then CON.Open()
If Command.ExecuteNonQuery() = 1 Then
MsgBox("تمت عملية الحذف بنجاح", MsgBoxStyle.MsgBoxRtlReading, "تنفيذ اجراء الحذف")
RE_DATE()
record()
Else
MsgBox("لم تتم عملية الحذف بنجاح", MsgBoxStyle.MsgBoxRtlReading, "تنفيذ اجراء الحذف")
End If
CON.Close()
End Using
End If
End Sub
Private Sub Button8_Click(sender As Object, e As EventArgs) Handles Button8.Click
Me.BindingContext(InfoTable).Position -= 1
ShowRelated()
End Sub
Private Sub Button7_Click(sender As Object, e As EventArgs) Handles Button7.Click
If Me.BindingContext(InfoTable).Position = 0 Then
MsgBox("!!! لقد وصلت إلى السجل الأول")
Else
Me.BindingContext(InfoTable).Position = 0
ShowRelated()
End If
End Sub
Private Sub Button9_Click(sender As Object, e As EventArgs) Handles Button9.Click
Me.BindingContext(InfoTable).Position += 1
ShowRelated()
End Sub
Private Sub Button10_Click(sender As Object, e As EventArgs) Handles Button10.Click
If (Me.BindingContext(InfoTable).Position = Me.BindingContext(InfoTable).Count - 1) Then
MsgBox(" !!! لقد وصلت إلى السجل الأخير")
Else
Me.BindingContext(InfoTable).Position = Me.BindingContext(InfoTable).Count - 1
ShowRelated()
End If
End Sub
Private Sub Button5_Click(sender As Object, e As EventArgs) Handles Button5.Click
Button1.Enabled = True
Button2.Enabled = False
Button3.Enabled = True
Button4.Enabled = True
Button5.Enabled = False
Button13.Enabled = False
Button6.Enabled = True
Button7_Click(sender, e)
End Sub
Private Sub Button11_Click(sender As Object, e As EventArgs) Handles Button11.Click
Try
If Button1.Enabled = False Then
ElseIf TextBox2.Text = "" Then
MsgBox("فضلاً قم بعرض البيانات أولاً")
Exit Sub
End If
Dim opn As New OpenFileDialog
opn.Title = "إختر الصّورة لو سمحت "
opn.Filter = "الصور|*.jpg;*.jpeg;*.png;*.gif"
opn.Multiselect = False
If opn.ShowDialog() = DialogResult.OK Then
PictureBox1.Image = Image.FromFile(opn.FileName)
End If
Catch ex As Exception
MessageBox.Show(ex.Message, "خطأ فى تحميل الصورة", MessageBoxButtons.OK, MessageBoxIcon.Error)
End Try
End Sub
Private Sub form1_Closing(ByVal sender As Object, ByVal e As System.ComponentModel.CancelEventArgs) Handles MyBase.Closing
e.Cancel = True
End Sub
'اجراء اعادة التعبئة
Private Sub RE_DATE()
Using ADP As New SqlDataAdapter("select * from STUDENT", CON)
Dim Dt As New DataTable
ADP.Fill(Dt)
DataGridView1.DataSource = Dt
PopulateComboBox(Dt)
End Using
End Sub
Public Function SELECT_DT(ByVal TXT_ As String) As DataTable
'تحديث البيانات
Dim DT As New DataTable
DT.Clear()
Dim ADP As New SqlDataAdapter(TXT_, CON)
ADP.Fill(DT)
Return DT
End Function
Private Sub DataGridView1_SelectionChanged(sender As Object, e As EventArgs) Handles DataGridView1.SelectionChanged
End Sub
Private Sub showphoto()
If DataGridView1.CurrentRow.DataBoundItem IsNot Nothing Then
Dim Row As DataRow = CType(DataGridView1.CurrentRow.DataBoundItem, DataRowView).Row
Dim Imagee As Byte() = DirectCast(Row.Item(6), Byte())
If Imagee.Length > 0 Then
Using Ms As IO.MemoryStream = New IO.MemoryStream(Imagee)
PictureBox1.Image = Image.FromStream(Ms)
Imagee = Nothing
End Using
End If
Else
PictureBox1.Image = My.Resources.Nopicture
End If
End Sub
Private Sub Button6_Click(sender As Object, e As EventArgs) Handles Button6.Click
If MsgBox("هل تريد الخروج من البرنامج", MsgBoxStyle.YesNo + MsgBoxStyle.Critical, "تنبيه") = MsgBoxResult.No Then
Exit Sub
Else
Me.Dispose()
End If
End Sub
Private Sub DataGridView1_CurrentCellChanged(sender As Object, e As EventArgs) Handles DataGridView1.CurrentCellChanged
If DataGridView1.RowCount > 0 Then
If DataGridView1.CurrentRow IsNot Nothing Then
Dim r As DataGridViewRow = Me.DataGridView1.CurrentRow
TextBox1.Text = r.Cells(0).Value.ToString
TextBox2.Text = r.Cells(1).Value.ToString
TextBox3.Text = r.Cells(2).Value.ToString
TextBox5.Text = r.Cells(4).Value.ToString
TextBox6.Text = r.Cells(5).Value.ToString
showphoto()
ShowRelated()
End If
End If
End Sub
End Class
