تقييم الموضوع :
  • 0 أصوات - بمعدل 0
  • 1
  • 2
  • 3
  • 4
  • 5
الدرس 20- تنفيذ جمل الإستعلام insert-update-delete
#1
كاتب الموضوع : Tarek Aldwire

السلام عليكم ورحمة الله وبركاته

اليوم سوف يكون الدرس متخصص بجمل الإستعلام من أنواعها...

أولا : تجهيز المشروع...

أولا قم بإنشاء قاعدة بيانات سمها mydatabase ثم قم بعمل جدول سمه mytable وضعه به عمود إسمه FirstName نوع بياناته nchar وعمود إسمه Age نوع بياناته bigint ...

الان قم بإنشاء الإتصال كما ذكرنا سابقا حيث يكون كود الإتصال كاملا...

قم باستيراد المجال : System.Data.SqlClient

أولا كود الإضافة insert ...

لنفترض الان أننا نريد أن نضيف للجدول mytable موظف إسمه ( tarek) عمره ( 15 ) ...


vb.net :


كود :
[COLOR=#0000BB]Dim cn [/color][color=#007700]As New [/color][COLOR=#0000BB]SqlConnection
cn[/COLOR][color=#007700].[/color][color=#0000BB]ConnectionString [/color][color=#007700]= [/color][COLOR=#DD0000]"Data Source=.\SQLEXPRESS;AttachDbFilename=C:\Users\TD\Documents\mydatabase.mdf;Integrated Security=True;Connect Timeout=30;User Instance=True"
[/COLOR][color=#0000BB]Dim mycmd [/color][color=#007700]As [/color][color=#0000BB]String [/color][color=#007700]= [/color][COLOR=#DD0000]"insert into mytable values ('tarek','15')"
[/COLOR][color=#0000BB]Dim cmd [/color][color=#007700]As [/color][color=#0000BB]SqlCommand [/color][color=#007700]= New [/color][color=#0000BB]SqlCommand[/color][color=#007700]([/color][color=#0000BB]mycmd[/color][color=#007700], [/color][color=#0000BB]cn[/color][COLOR=#007700])
[/COLOR][color=#0000BB]cn[/color][color=#007700].[/color][color=#0000BB]Open[/color][COLOR=#007700]()
[/COLOR][color=#0000BB]cmd[/color][color=#007700].[/color][color=#0000BB]ExecuteNonQuery[/color][COLOR=#007700]()
[/COLOR][color=#0000BB]cn[/color][color=#007700].[/color][color=#0000BB]Close[/color][COLOR=#007700]()


[/COLOR]

c#:

كود :
[COLOR=#0000BB]SqlConnection cn [/color][color=#007700]= new [/color][color=#0000BB]SqlConnection[/color][color=#007700](@[/color][color=#DD0000]"Data Source=.\SQLEXPRESS;AttachDbFilename=C:\Users\TD\Documents\mydatabase.mdf;Integrated Security=True;Connect Timeout=30;User Instance=True"[/color][COLOR=#007700]);
[/COLOR][color=#0000BB]string mycmd [/color][color=#007700]= [/color][color=#DD0000]"insert into mytable values ('tarek','15')"[/color][COLOR=#007700];
[/COLOR][color=#0000BB]SqlCommand cmd [/color][color=#007700]= new [/color][color=#0000BB]SqlCommand[/color][color=#007700]([/color][color=#0000BB]mycmd[/color][color=#007700], [/color][color=#0000BB]cn[/color][COLOR=#007700]);
[/COLOR][color=#0000BB]cn[/color][color=#007700].[/color][color=#0000BB]Open[/color][COLOR=#007700]();
[/COLOR][color=#0000BB]cmd[/color][color=#007700].[/color][color=#0000BB]ExecuteNonQuery[/color][COLOR=#007700]();
[/COLOR][color=#0000BB]cn[/color][color=#007700].[/color][color=#0000BB]Close[/color][COLOR=#007700]();


[/COLOR]
2- update ... لنفترض أننا نود تعديل عمر الموظف الذي يدعى tarek إلى 100 سنة يكون الكود:

vb.net :

كود :
[COLOR=#0000BB]Dim cn [/color][color=#007700]As New [/color][COLOR=#0000BB]SqlConnection
cn[/COLOR][color=#007700].[/color][color=#0000BB]ConnectionString [/color][color=#007700]= [/color][COLOR=#DD0000]"Data Source=.\SQLEXPRESS;AttachDbFilename=C:\Users\TD\Documents\mydatabase.mdf;Integrated Security=True;Connect Timeout=30;User Instance=True"
[/COLOR][color=#0000BB]Dim mycmd [/color][color=#007700]As [/color][color=#0000BB]String [/color][color=#007700]= [/color][COLOR=#DD0000]"update mytable set Age = 100 where FirstName = 'tarek'"
[/COLOR][color=#0000BB]Dim cmd [/color][color=#007700]As [/color][color=#0000BB]SqlCommand [/color][color=#007700]= New [/color][color=#0000BB]SqlCommand[/color][color=#007700]([/color][color=#0000BB]mycmd[/color][color=#007700], [/color][color=#0000BB]cn[/color][COLOR=#007700])
[/COLOR][color=#0000BB]cn[/color][color=#007700].[/color][color=#0000BB]Open[/color][COLOR=#007700]()
[/COLOR][color=#0000BB]cmd[/color][color=#007700].[/color][color=#0000BB]ExecuteNonQuery[/color][COLOR=#007700]()
[/COLOR][color=#0000BB]cn[/color][color=#007700].[/color][color=#0000BB]Close[/color][COLOR=#007700]()


[/COLOR]
c# :

كود :
[COLOR=#0000BB]SqlConnection cn [/color][color=#007700]= new [/color][color=#0000BB]SqlConnection[/color][color=#007700](@[/color][color=#DD0000]"Data Source=.\SQLEXPRESS;AttachDbFilename=C:\Users\TD\Documents\mydatabase.mdf;Integrated Security=True;Connect Timeout=30;User Instance=True"[/color][COLOR=#007700]);
[/COLOR][color=#0000BB]string mycmd [/color][color=#007700]= [/color][color=#DD0000]"update mytable set Age = 100 where FirstName = 'tarek'"[/color][COLOR=#007700];
[/COLOR][color=#0000BB]SqlCommand cmd [/color][color=#007700]= new [/color][color=#0000BB]SqlCommand[/color][color=#007700]([/color][color=#0000BB]mycmd[/color][color=#007700], [/color][color=#0000BB]cn[/color][COLOR=#007700]);
[/COLOR][color=#0000BB]cn[/color][color=#007700].[/color][color=#0000BB]Open[/color][COLOR=#007700]();
[/COLOR][color=#0000BB]cmd[/color][color=#007700].[/color][color=#0000BB]ExecuteNonQuery[/color][COLOR=#007700]();
[/COLOR][color=#0000BB]cn[/color][color=#007700].[/color][color=#0000BB]Close[/color][COLOR=#007700]();


[/COLOR]

3- delete والان لنفترض أنك تود حذفي ( tarek ) يكون الكود:

vb.net :
كود :
Dim cn [/COLOR][color=#007700]As New [/color][COLOR=#0000BB]SqlConnection
cn[/COLOR][color=#007700].[/color][color=#0000BB]ConnectionString [/color][color=#007700]= [/color][COLOR=#DD0000]"Data Source=.\SQLEXPRESS;AttachDbFilename=C:\Users\TD\Documents\mydatabase.mdf;Integrated Security=True;Connect Timeout=30;User Instance=True"
[/COLOR][color=#0000BB]Dim mycmd [/color][color=#007700]As [/color][color=#0000BB]String [/color][color=#007700]= [/color][COLOR=#DD0000]"delete from mytable where FirstName = 'tarek'"
[/COLOR][color=#0000BB]Dim cmd [/color][color=#007700]As [/color][color=#0000BB]SqlCommand [/color][color=#007700]= New [/color][color=#0000BB]SqlCommand[/color][color=#007700]([/color][color=#0000BB]mycmd[/color][color=#007700], [/color][color=#0000BB]cn[/color][COLOR=#007700])
[/COLOR][color=#0000BB]cn[/color][color=#007700].[/color][color=#0000BB]Open[/color][COLOR=#007700]()
[/COLOR][color=#0000BB]cmd[/color][color=#007700].[/color][color=#0000BB]ExecuteNonQuery[/color][COLOR=#007700]()
[/COLOR][color=#0000BB]cn[/color][color=#007700].[/color][color=#0000BB]Close[/color][COLOR=#007700]()



c#:

كود :
[COLOR=#0000BB]SqlConnection cn [/color][color=#007700]= new [/color][color=#0000BB]SqlConnection[/color][color=#007700](@[/color][color=#DD0000]"Data Source=.\SQLEXPRESS;AttachDbFilename=C:\Users\TD\Documents\mydatabase.mdf;Integrated Security=True;Connect Timeout=30;User Instance=True"[/color][COLOR=#007700]);
[/COLOR][color=#0000BB]string mycmd [/color][color=#007700]= [/color][color=#DD0000]"delete from mytable where FirstName = 'tarek'"[/color][COLOR=#007700];
[/COLOR][color=#0000BB]SqlCommand cmd [/color][color=#007700]= new [/color][color=#0000BB]SqlCommand[/color][color=#007700]([/color][color=#0000BB]mycmd[/color][color=#007700], [/color][color=#0000BB]cn[/color][COLOR=#007700]);
[/COLOR][color=#0000BB]cn[/color][color=#007700].[/color][color=#0000BB]Open[/color][COLOR=#007700]();
[/COLOR][color=#0000BB]cmd[/color][color=#007700].[/color][color=#0000BB]ExecuteNonQuery[/color][COLOR=#007700]();
[/COLOR][color=#0000BB]cn[/color][color=#007700].[/color][color=#0000BB]Close[/color][COLOR=#007700]();


[/COLOR]
يتبع...
}}}
تم الشكر بواسطة:
#2
تابع...

ملاحظة : يمكنك وضع شروط الحذف أو التعديل كيف ما تشاء ...مثلا...


كود :
[color=#000000][COLOR=#0000bb]delete from mytable where Age [/color][color=#007700]> [/color][COLOR=#0000bb]15  
[/COLOR][/COLOR]

حيث في هذه الحالة سوف يتم حذف كل شخص عمره فوق ال 15 ...

- إنشاء جدول...

جدول إسمه tb1 يحول على name من نوع nchar(10) و age من نوع bigint الكود:

vb.net :

كود :
[COLOR=#000000][COLOR=#0000bb]
Dim cn [/COLOR][color=#007700]As New [/color][COLOR=#0000bb]SqlConnection
cn[/COLOR][color=#007700].[/color][color=#0000bb]ConnectionString [/color][color=#007700]= [/color][COLOR=#dd0000]"Data Source=.\SQLEXPRESS;AttachDbFilename=C:\Users\TD\Documents\mydatabase.mdf;Integrated Security=True;Connect Timeout=30;User Instance=True"
[/COLOR][color=#0000bb]Dim mycmd [/color][color=#007700]As [/color][color=#0000bb]String [/color][color=#007700]= [/color][COLOR=#dd0000]"create table tb1 (name nchar(10),age bigint);"
[/COLOR][color=#0000bb]Dim cmd [/color][color=#007700]As [/color][color=#0000bb]SqlCommand [/color][color=#007700]= New [/color][color=#0000bb]SqlCommand[/color][color=#007700]([/color][color=#0000bb]mycmd[/color][color=#007700], [/color][color=#0000bb]cn[/color][COLOR=#007700])
[/COLOR][color=#0000bb]cn[/color][color=#007700].[/color][color=#0000bb]Open[/color][COLOR=#007700]()
[/COLOR][color=#0000bb]cmd[/color][color=#007700].[/color][color=#0000bb]ExecuteNonQuery[/color][COLOR=#007700]()
[/COLOR][color=#0000bb]cn[/color][color=#007700].[/color][color=#0000bb]Close[/color][COLOR=#007700]()  
[/COLOR][color=#0000bb][/color][/COLOR]


c# :


كود :
[color=#000000][COLOR=#0000bb]SqlConnection cn [/color][color=#007700]= new [/color][color=#0000bb]SqlConnection[/color][color=#007700](@[/color][color=#dd0000]"Data Source=.\SQLEXPRESS;AttachDbFilename=C:\Users\TD\Documents\mydatabase.mdf;Integrated Security=True;Connect Timeout=30;User Instance=True"[/color][COLOR=#007700]);
[/COLOR][color=#0000bb]string mycmd [/color][color=#007700]= [/color][color=#dd0000]"create table tb1 (name nchar(10),age bigint);"[/color][COLOR=#007700];
[/COLOR][color=#0000bb]SqlCommand cmd [/color][color=#007700]= new [/color][color=#0000bb]SqlCommand[/color][color=#007700]([/color][color=#0000bb]mycmd[/color][color=#007700], [/color][color=#0000bb]cn[/color][COLOR=#007700]);
[/COLOR][color=#0000bb]cn[/color][color=#007700].[/color][color=#0000bb]Open[/color][COLOR=#007700]();
[/COLOR][color=#0000bb]cmd[/color][color=#007700].[/color][color=#0000bb]ExecuteNonQuery[/color][COLOR=#007700]();
[/COLOR][color=#0000bb]cn[/color][color=#007700].[/color][color=#0000bb]Close[/color][COLOR=#007700]();  
[/COLOR][color=#0000bb][/color][/COLOR]


يتبع..
}}}
تم الشكر بواسطة:
#3
تابع...

لحذف الجدول الذي قمنا به ...

vb.net :


كود :
[color=#000000][COLOR=#0000bb]Dim cn [/color][color=#007700]As New [/color][COLOR=#0000bb]SqlConnection
cn[/COLOR][color=#007700].[/color][color=#0000bb]ConnectionString [/color][color=#007700]= [/color][COLOR=#dd0000]"Data Source=.\SQLEXPRESS;AttachDbFilename=C:\Users\TD\Documents\mydatabase.mdf;Integrated Security=True;Connect Timeout=30;User Instance=True"
[/COLOR][color=#0000bb]Dim mycmd [/color][color=#007700]As [/color][color=#0000bb]String [/color][color=#007700]= [/color][COLOR=#dd0000]"drop table tb1 cascade"
[/COLOR][color=#0000bb]Dim cmd [/color][color=#007700]As [/color][color=#0000bb]SqlCommand [/color][color=#007700]= New [/color][color=#0000bb]SqlCommand[/color][color=#007700]([/color][color=#0000bb]mycmd[/color][color=#007700], [/color][color=#0000bb]cn[/color][COLOR=#007700])
[/COLOR][color=#0000bb]cn[/color][color=#007700].[/color][color=#0000bb]Open[/color][COLOR=#007700]()
[/COLOR][color=#0000bb]cmd[/color][color=#007700].[/color][color=#0000bb]ExecuteNonQuery[/color][COLOR=#007700]()
[/COLOR][color=#0000bb]cn[/color][color=#007700].[/color][color=#0000bb]Close[/color][COLOR=#007700]()  
[/COLOR][/COLOR]

c# :



كود :
[color=#000000][COLOR=#0000bb]SqlConnection cn [/color][color=#007700]= new [/color][color=#0000bb]SqlConnection[/color][color=#007700](@[/color][color=#dd0000]"Data Source=.\SQLEXPRESS;AttachDbFilename=C:\Users\TD\Documents\mydatabase.mdf;Integrated Security=True;Connect Timeout=30;User Instance=True"[/color][COLOR=#007700]);
[/COLOR][color=#0000bb]string mycmd [/color][color=#007700]= [/color][color=#dd0000]"drop table tb1 cascade"[/color][COLOR=#007700];
[/COLOR][color=#0000bb]SqlCommand cmd [/color][color=#007700]= new [/color][color=#0000bb]SqlCommand[/color][color=#007700]([/color][color=#0000bb]mycmd[/color][color=#007700], [/color][color=#0000bb]cn[/color][COLOR=#007700]);
[/COLOR][color=#0000bb]cn[/color][color=#007700].[/color][color=#0000bb]Open[/color][COLOR=#007700]();
[/COLOR][color=#0000bb]cmd[/color][color=#007700].[/color][color=#0000bb]ExecuteNonQuery[/color][COLOR=#007700]();
[/COLOR][color=#0000bb]cn[/color][color=#007700].[/color][color=#0000bb]Close[/color][COLOR=#007700]();  
[/COLOR][/COLOR]


إضافة حقول إلى جدول ما ...

بفرض أننا نريد أن نضيف عامود brithday إلى mytable يحوي على يوم الميلاد ..

vb.net :

كود :
[COLOR=#000000][COLOR=#0000bb]
Dim cn [/COLOR][color=#007700]As New [/color][COLOR=#0000bb]SqlConnection
cn[/COLOR][color=#007700].[/color][color=#0000bb]ConnectionString [/color][color=#007700]= [/color][COLOR=#dd0000]"Data Source=.\SQLEXPRESS;AttachDbFilename=C:\Users\TD\Documents\mydatabase.mdf;Integrated Security=True;Connect Timeout=30;User Instance=True"
[/COLOR][color=#0000bb]Dim mycmd [/color][color=#007700]As [/color][color=#0000bb]String [/color][color=#007700]= [/color][COLOR=#dd0000]"alter table mytable add brithday datetime"
[/COLOR][color=#0000bb]Dim cmd [/color][color=#007700]As [/color][color=#0000bb]SqlCommand [/color][color=#007700]= New [/color][color=#0000bb]SqlCommand[/color][color=#007700]([/color][color=#0000bb]mycmd[/color][color=#007700], [/color][color=#0000bb]cn[/color][COLOR=#007700])
[/COLOR][color=#0000bb]cn[/color][color=#007700].[/color][color=#0000bb]Open[/color][COLOR=#007700]()
[/COLOR][color=#0000bb]cmd[/color][color=#007700].[/color][color=#0000bb]ExecuteNonQuery[/color][COLOR=#007700]()
[/COLOR][color=#0000bb]cn[/color][color=#007700].[/color][color=#0000bb]Close[/color][COLOR=#007700]()  
[/COLOR][/COLOR]

c#:



كود :
[color=#000000][COLOR=#0000bb]SqlConnection cn [/color][color=#007700]= new [/color][color=#0000bb]SqlConnection[/color][color=#007700](@[/color][color=#dd0000]"Data Source=.\SQLEXPRESS;AttachDbFilename=C:\Users\TD\Documents\mydatabase.mdf;Integrated Security=True;Connect Timeout=30;User Instance=True"[/color][COLOR=#007700]);
[/COLOR][color=#0000bb]string mycmd [/color][color=#007700]= [/color][color=#dd0000]"alter table mytable add brithday datetime"[/color][COLOR=#007700];
[/COLOR][color=#0000bb]SqlCommand cmd [/color][color=#007700]= new [/color][color=#0000bb]SqlCommand[/color][color=#007700]([/color][color=#0000bb]mycmd[/color][color=#007700], [/color][color=#0000bb]cn[/color][COLOR=#007700]);
[/COLOR][color=#0000bb]cn[/color][color=#007700].[/color][color=#0000bb]Open[/color][COLOR=#007700]();
[/COLOR][color=#0000bb]cmd[/color][color=#007700].[/color][color=#0000bb]ExecuteNonQuery[/color][COLOR=#007700]();
[/COLOR][color=#0000bb]cn[/color][color=#007700].[/color][color=#0000bb]Close[/color][COLOR=#007700]();  
[/COLOR][/COLOR]


إلى اللقاء في الدرس القادم إن شاء الله ...
}}}
تم الشكر بواسطة: خالد محمد احمد قعشه



التنقل السريع :


يقوم بقرائة الموضوع: بالاضافة الى ( 1 ) ضيف كريم